数据库上

（内容过长，建议复制之后用typora软件，这样会有索引，看起来超级方便）

一、MySQL介绍

sql：结构化查询语言，专门用来和数据库进行通信的语言

二、MySQL常用命令

2.1登录的常用命令

（1）登录命令

mysql -h 主机名 -P 端口号 -u 用户名 -p密码 （输入密码不能加空格，端口号P大写）

mysql -h 主机名 -P 端口号 -u 用户名 -p （下一行输入密码） （输入密码不能加空格，端口号P大写）

mysql -u 用户名 -p密码 简写

（2）常用命令

查看所有库：show databases;

使用库：use 库名;

查看当前库下所有的表:show tables;

查看指定表结构：desc 表名；

创建数据库：create databases 库名;

创建表：

create table 表名{
id int,
name varchar(20)
}

删除表：

drop table 表名；

查看指定表中所有的数据：

select * from 表名

给指定表插入一条数据：

insert into 表名(列名1，列名2....)values(字段名1，字段名2...)

修改表中指定数据：

update 表名 set 列名=修改的值 where 指明条件

删除表中的一条数据：

delete from 表名 where 删除条件

（3）MySql语法规范

a、要求关键字大写

b、sql结尾用；或者\g；通常使用；

c、可以进行缩进和换行

2.2 SQL语言分类

2.1.1 DML(Data manipulation Language)

数据操纵语言，用于添加删除，修改，查询数据记录，检查完整性，内韩DQL语言

包含的SQL语句：

• INSERT

• UPDATE

• DELETE

• SELECT：是SQL语言的基础，尤其重要

2.1.2 DDL

数据定义语言，用于库和表的创建，修改，删除

• CREATE TABLE：创建数据库表

• ALTER TABLE：更改表结构

• DROP TABLE：删除表

• CREATE INDEX：在表上建立索引

• DROP INDEX：删除索引

2.1.4 DCL

数据控制语言，用于定义用户的访问权限和安全级别

• GRANT：授予访问权限

• REVOKE：撤销访问权限

• COMMIT：提交事务

• ROLLBACK：事务回滚

• SAEPOINT：设置保存点

• LOCK：对数据库特定的部分进行锁定

三、DQL语法

DQL包含在DML语句中

3.1基本语句

关键字 SELECT 列名 FROM 表名；

#新建一个表，
CREATE TABLE department (
stuff_id INT,
stuff_name VARCHAR (20),
stuff_salary INT,
department_id INT
);
​
#插入行
INSERT INTO department (stuff_id, stuff_name)
VALUES
(1, 'ljq');

#更改信息
UPDATE department
SET stuff_salary = NULL
WHERE
stuff_id = 7;
​
#显示表
SELECT
*
FROM
department;
​
#删除表
DROP TABLE department;
​
#取别名
SELECT
stuff_id AS 编号,
stuff_name AS 姓名,
stuff_salary  年薪,
department_id AS "部门"
FROM
department;
​
#去重
SELECT
DISTINCT stuff_id,stuff_name
FROM
department;
​
#清空表中数据
TRUNCATE TABLE jqfirst_test;

3.2 条件查询

3.2.1 基本语法

SELECT
列名 
FROM 
表名
WHERE  -- HAVING 为查询后过滤
条件表达式
排序关键字 ORDER BY 排序字段名 -- 注意：ORDER BY 子句在SELECT语句的结尾
DESC ASC -- DESC 降序 ASC 升序

WHERE ：where针对于原表，作用在GROUP BU 之前 ，不能直接使用聚合函数

HAVING：针对于分组后的数据，作用在GROUP BY 之后，可以使用聚合函数

3.2.2 条件表达式

1. 简单的条件运算：> 、>、=、!=（可以使用，但推荐使用<>）、<=>（安全等于，可以判空，也可以做等值运算）

2. 逻辑运算符：&&、||、！、mysql支持，但是推荐使用：AND、OR、NOT

   #条件查询
   SELECT
   stuff_id,
   sruff_name
   FROM
   department
   WHERE
   stuff_id = 22 AND stuff_name = 'LN';
   ​
   #查询员工姓名不是LQQ并且工资大于4000
   SELECT 
   *
   FROM
   department
   WHERE
    stuff_name != 'LQQ' AND stuff_salary > 4000;
   
   #逻辑条件查询练习，注意：NOT表示取反
   SELECT
   *
   FROM
   department
   WHERE
   NOT (
   (
   stuff_name != 'LQQ'
   AND stuff_salary > 4000
   AND stuff_salary < 100000
   )
   OR department_id = 1
   ); 

3. 模糊条件：LIKE、BETWEEN……AND……、IN、IS NULL

   • LIKE用法：做模糊匹配

     #查询员工姓名中包含字符a的员工信息
     SELECT 
     *
     FROM
     department
     WHERE
     stuff_name LIKE '%X%';
     ​
     #查询员工中第二个字母为Q,第三个字母为Q的员工信息
     SELECT 
     *
     FROM
     department
     WHERE
     stuff_name LIKE '_Q%' AND stuff_name LIKE '__Q';
     ​
     #查名字有下划线的员工信息
     SELECT
     *
     FROM
     department
     WHERE
     stuff_name LIKE '%\_%';-- 在mysql中不推荐使用这种方式
     stuff_name LIKE '%$_%' ESCAPE '$';-- 手动指定通配符 声明$是一个转义字符，mysql推荐使用。

   • IN用法：筛选集合中的元素（开发中尽量少使用，效率低）

     #查询编号为1，4，9的员工
     SELECT
     *
     FROM
     department
     WHERE
     stuff_id IN(1,4,9);

   • BETWEEN……AND…… ：用来查找一个区间的值

     #查询工资为5000到20000之间的员工信息
     SELECT
     *
     FROM
     department
     WHERE
     stuff_salary BETWEEN 5000 AND 20000;

   • IS NULL：筛选空值的指定元素

     #查询工资为空的员工
     SELECT
     *
     FROM
     department
     WHERE
     stuff_salary IS NULL;

3.2.3 排序

• 一般格式

#查询工工资在4500到10000的员工信息，并排序
SELECT
*
FROM
department
WHERE
stuff_salary BETWEEN 4500
AND 20000 
ORDER BY
stuff_salary
DESC   -- 默认为ASC升序

后可使用聚合函数，时针对ORDER BY 得出的结果进行排序的

• 通过别名进行排序

#通过别名来排序
#查询研发部员工的年薪
SELECT
stuff_name 员工姓名,
department_id 部门,
stuff_salary * 12 年薪
FROM
department
WHERE
department_id = 2
ORDER BY
年薪

• 使用多字段排序

#使用多字段排序
#查询研发部员工的年薪按照降序排序，年薪相同的时候将员工的姓名按照升序排列
SELECT
stuff_name 员工姓名,
department_id 部门,
stuff_salary * 12 年薪
FROM
department
WHERE
department_id = 2 OR department_id = 1
ORDER BY
年薪 DESC,stuff_name ASC;

 

3.2.4 MySQL中常用函数

函数：MySQL函数封装了一系列处理逻辑，用于调用解决一定的计算问题以及简单的逻辑问题（日期的格式化，字符类型操作）

语法：

SELECT
函数() 别名
FROM
表名
WHERE
函数()
ORDER BY
函数()

分类：

• 单行函数：如：concat,length,用于字符处理的函数

• 组函数：统计函数，聚合函数，组函数

 

3.2.4.1 字符处理函数

• LENGTH(str) ：计算字节数

  #计算出员工姓名中字节数大于1的员工，并按照字节数升序排序
  SELECT
  stuff_id,
  stuff_name
  FROM
  department
  WHERE
  LENGTH(stuff_name) > 1
  ORDER BY
  LENGTH(stuff_name) ASC;

• CONCAT(str1,str2,…)：拼接字符串

  #查询员工信息表，将员工姓名和员工的id拼接显示
  SELECT
  CONCAT(stuff_id,'_',stuff_name) 
  FROM
  department

• UPPER(str) ：转成大写

  LOWER(str)：转成小写

  #查询员工信息表，将员工共姓名转成大写
  SELECT
  LOWER(stuff_name)
  FROM
  department;
  ​
  SELECT UPPER('transform successful');

• SUBSTR()、SUBSTRIG()：截取字符，通过索引查找，MySQL中索引是从1开始的

  SELECT
  SUBSTR('一拳超人埼玉' FROM 3 FOR 2);

  #查询员工信息表，将员工id和员工姓名拼接，将员工姓名只显示第二个字符，并将其大写
  #将员工id只显示第一个字符，并小写
  SELECT
  CONCAT(
  LOWER(SUBSTR(stuff_id FROM 1 FOR 1)),
  '+',
  UPPER(SUBSTR(stuff_name FROM 2 FOR 1))
  ) AS 员工姓名地址
  FROM
  department;

• LPAD(str,len,padstr) 指定的长度内在字符串的左边用指定的字符填充，直至填满，len代表填充之后的字符串长度

  RPAD(str,len,padstr) 在右边添加

  SELECT
  LPAD('沾湿黑礼服',10,'*');
  SELECT
  RPAD('沾湿黑礼服',10,'*');

• TRIM([remstr FROM] str) 去掉字符串首部和尾部与指定字符匹配的所有字符

  #去空格
  SELECT
  TRIM('      天刚刚破晓      ');

• INSTR(str,substr) 返回指定字符串在字符串中首次出现的位置

  SELECT
  INSTR('werT33423REW','wer');

• TELACE(str,from_str,to_str)：替换

  #将员工地址替换为国家名
  SELECT
  REPLACE(department_id,'1','china'),
  REPLACE(department_id,'2','japan'),
  REPLACE(department_id,'0','amarica')
  FROM
  department;

• 函数可以嵌套使用

  #查询员工信息表，将员工姓名和员工地址拼接，其中员工共姓名转成小写写，员工id大写
  SELECT
  CONCAT(UPPER(stuff_id),'+',LOWER(stuff_name))
  FROM
  department;

   

3.2.4.2 数学函数

• ROUND(X)：四舍五入

  ROUND(X,D)：指定小数点后面保留小数的位数，同时四舍五入

  #查询员工信息表，计算员工的月绩效奖金（salar/21.75*0.75），并小数点后一位四舍五入
  SELECT
  stuff_id,
  stuff_name,
  ROUND(stuff_salary/21.75*0.75,1)
  FROM
  department;

• TRUNCATE(X,D)：截断 保留小数点后指定的位数，不进行四舍五入

• MOD(N,M)：模运算 求N对M的模，即N为被除数，M为除数

• CEIL(X)：向上取整，返回大于等于当前数的最小整数

  SELECT
  CEIL(-10.1);

• FLOOR(X)：向下取整，返回等于小于当前数的最大整数

3.2.4.3 日期函数

用于格式化时间，解析时间

• NOW()：当前系统的日期时间

• STR_TO_DATE(str,format)：将指定的字符串解析成指定格式的日期类型（

  %Y：四位年份

  %y：两位的年份

  %m：月份（01……12）两位数表示

  %c：月份，一位数表示

  %d：日，用两位数表示

  %H：小时（24小时制）

  %h：小时（12小时制）

  %i：分钟（0-59）

  %s：秒（0-59）

  ）

  SELECT
  STR_TO_DATE('2019-07-24','%Y-%m-%d'),-- mysql默认的日期类型
  STR_TO_DATE('2019-07-24','%Y年%m月%d日');-- 解析不出，sql会将年月日当作字符串进行处理

• DATE_FORMAT(date,format)：将日期转换成字符串

  SELECT
  DATE_FORMAT(NOW(),'%Y年%M月%D日 %H时%i分%s秒');

  id#查询员工信息表中的入职日期，并将姓名和日期拼接
  eELECT
  CONCAT(
  stuff_name,
  '_',
  STR_TO_DATE(join_date, '%Y-%m-%d')
  )
  /*
  CONCAT(
  stuff_name,
  '_',
  join_date
  )
  */ -- 也可以这样，但是不推荐，这样的效率很低，再开发中不要这样写
  FROM
  department;

• YEAR()：显示当前年份

• MONTH()：显示指定时间月份

• DAY()：显示指定日期（只有天）

  ……等

• CURRENT_TIME()：只显示当前时间

• CURRENT_DATE()：只显示当前日期

3.2.4.4 组函数（聚合函数）

统计多行数据，形成一行结果

特点：

1. 组函数在使用的过程中，如果配合列来使用，则列必须放到GROUP BY()之后

2. 聚合函数不能直接使用再WHERE语句之后

3. 在聚合函数中出了COUNT之外，都会对NULL进行忽视

语法：

SELECT [column],组函数 FROM

• COUTN(expr)：统计个数，不支持NULL值，非空类型都支持

  SELECT
  COUNT(2) -- 统计2列的所有行，统计所有行的时候一般不推荐使用COUNT(column)   colum表示列名
  -- 推荐使用COUNT(*)或者COUNT(1)，因为COUNT()对NULL无效，而且后者通过索引进行统计，效率高
  FROM
  department;

  #统计员工信息表中部门2的总人数
  SELECT
  COUNT(department_id)
  FROM
  department
  WHERE
  department_id = 2;

• SUM(expr)：求和，支持数值类型

  #计算部门2所有员工的总工资
  SELECT
  SUM(stuff_salary)
  FROM
  department
  WHERE
  department_id = 2;

• AVG([DISTINCT] expr)：平均值，支持数值类型

  #计算部门2的平均工资
  SELECT
  AVG(stuff_salary)
  FROM
  department
  WHERE
  department_id = 2;

• MAX(expr)：求最大，可排序的所有类型

  MIN(expr)：求最小，可排序的所有类型

  #求员工工资的最高薪资和最低薪资和工资大于平均薪资的人数以及最高薪资的个数
  #以及员工的总工资，员工的平均工资
  SELECT
  MAX(stuff_salary) 最高薪资,
  MIN(stuff_salary) 最低薪资,
  SUM(stuff_salary) 工资总和,
  AVG(stuff_salary) 平均工资,
  (
  SELECT
  COUNT(stuff_salary)
  FROM
  department
  WHERE
  stuff_salary > (
  SELECT
  AVG(stuff_salary)
  FROM
  department
  )
  )大于平均薪资的人数
  FROM
  department;
  ​

3.2.4.5 分组函数

• GROUP BY，一般表达式

SELECT
聚合函数
列名（注意：最好写GROUP BY使用的列名）
FROM
表名
WHERE
条件
GROUP BY 分组字段（可以写表达式，但不能输聚合函数）

• GROUP BY 分组字段（可以写表达式，但不能输聚合函数）

#统计工资大于20000和小于20000的人数
SELECT
COUNT(stuff_id),
stuff_salary
FROM
department
GROUP BY stuff_salary < 20000;

• WHERE ：WHERE针对于原表，作用在GROUP BU 之前 ，不能直接使用聚合函数

  HAVING：针对于分组后的数据，作用在GROUP BY 之后，可以使用聚合函数

  分组后筛选的，可以直接使用HAVING，但推荐使用分组前筛选，这会使得sql优化时效率更高

#查找各部门工资大于6000的人数
SELECT
COUNT( stuff_id),
department_id
FROM
department
WHERE
stuff_salary > 6000
GROUP BY department_id;
​
​
#平均工资大于6000的部门
SELECT
AVG(stuff_salary),
department_id
FROM
department
GROUP BY department_id
HAVING
AVG(stuff_salary) > 6000;
​
#GROUP BY 和 HAVING 可以使用别名来查询，WHERE不可以使用别名（因为其针对于原表数据进行筛选）
#查询各部门员工人数超过两个的部门
SELECT
department_id 部门,
COUNT(1) 计数
FROM
department
GROUP BY 部门
HAVING 计数 > 2;
​
#查询员工人数超过两个的最高和最低薪资
SELECT
department_id,
MAX(stuff_salary) 最高工资,
MIN(stuff_salary) 最低工资
FROM
department
GROUP BY department_id
HAVING COUNT(*) > 2;
​
#统计各部门员工工资大于6000的人数,并将人数超过2的部门按照人数排序
SELECT
department_id,
COUNT(stuff_id) 人数
FROM
department
WHERE
stuff_salary > 6000 
GROUP BY
department_id
HAVING
人数 >= 2
ORDER BY
COUNT(stuff_id);
​
#查询大于平均工资的人数,并按照部门分组
SELECT
COUNT(stuff_id),
department_id
FROM
department
WHERE
stuff_salary > (
SELECT
AVG(stuff_salary)
FROM
department
)
GROUP BY
department_id; -- 这个问题其实可以用HAVING轻松解决

3.3 条件语句

3.3.1 IF()

IF(expr1,expr2,expr3)：如果expr1成立就显示expr2的值，否则显示expr3的值，类似于java中的三元运算符

#查询员工的岗位级别，员工年薪大于40万的为t1,20-40为t2，否则为t3
SELECT
stuff_id,
stuff_name,
IF(stuff_salary*12 <= 200000,'t3',IF(stuff_salary*12<=400000,'t2','t1')),
stuff_salary*12
FROM
department;

3.3.2 CASE WHEN

一般格式

CASE 常量或者是语句（也可以不写）
WHEN 
常量或者是语句 
THEN 显示的常量或者是语句（是语句的时候使用‘；’号结尾）
……
ELSE 不满足时执行的语句或显示的变量（类似于Java中的switch(表达式)）
END

SELECT
stuff_id,
stuff_name,
CASE department_id
WHEN 0 THEN 'japan'
WHEN 1 THEN 'amanrica'
WHEN 2 THEN 'china'
ELSE 'unkonw'
END
FROM
department

#查询员工的岗位级别，员工年薪大于40万的为T1,20-40为T2，否则为T3
SELECT
stuff_id,
stuff_name,
CASE 
WHEN stuff_salary*12 > 400000 THEN 'T1'
WHEN stuff_salary*12 <= 400000 AND stuff_salary*12>200000 THEN 'T2'
ELSE 'T3'
END,
stuff_salary*12
FROM
department;3.2.4.5 分组函数

3.4 连接查询

连续查询，当我们需要查询的字段不是同一张表时，需要连接查询

#查询员工信息，显示员工姓名、部门名字、部门编号
SELECT
stuff_name,
department_name,
department.department_id
FROM
department,department_type
WHERE
department.department_id = department_type.department_id;

sql分类：

1. 按年份划分为：sql92和sql99标准

2. 按功能：

3.4.1 SQL92连接查询

3.4.1.1 等值连接

#查询各部门员工信息中工资大于10000的人
#并统计人数大于2的部门，显示员工的部门编号和部门名称
SELECT-- 查询出员工对应的部门信息
员工信息.department_id,
部门.department_name,
COUNT(1) 计数
FROM
department 员工信息,
department_type 部门
WHERE
员工信息.department_id = 部门.department_id 
AND stuff_salary > 10000   -- 晒选出工资大于20000的人
GROUP BY 员工信息.department_id
HAVING COUNT(1) >= 2;

#查询员工的信息，和其领导的信息
SELECT
stuff.stuff_id,
stuff.stuff_name,
manager.stuff_id,
manager.stuff_name
FROM
department manager,department stuff -- 自己和自己连接
WHERE
stuff.manager = manager.stuff_id;
​

 

 

3.4.1.2 非等值连接

#通过员工工资查询员工对应的工资级别
SELECT
员工.role,
综合.stuff_name,
综合.stuff_id
FROM
stuff_role 员工, department 综合
WHERE
综合.stuff_salary BETWEEN 员工.MIN AND 员工.MAX;

#查询员工的部门信息以及角色信息，显示部门名称、角色名称、员工编号姓名
SELECT
department_type.department_id,
department_type.department_name,
department.stuff_name,
department.stuff_id,
stuff_role.role
FROM
department,department_type,stuff_role
WHERE
department_type.department_id = department.department_id
AND
department.stuff_salary BETWEEN stuff_role.MIN AND stuff_role.MAX;

3.4.2 SQL99

sql优化中：连接查询时能使用ON就用ON，这样效率高于WHERE

3.4.2.1 内连接

• 等值连接

  #语法
  SELECT
  查询的列
  FROM
  表1 (INNER/LEFT(OUTER) JOIN/RIGHT JOIN) JOIN 表
  ON 连接条件
  WHERE 筛选条件
  GROUP BY 分组条件
  OREDR BY 排序

  #查询员工的部门信息表
  SELECT 
  d.stuff_id,
  d.stuff_name,
  dt.department_name
  FROM
  department d JOIN department_type dt
  ON 
  d.department_id = dt.department_id
  
  #查询各部门员工工资大于5000的人数,显示人数大于1的部门，显示员工编号、姓名、部门名称
  SELECT
  d.stuff_id,
  d.stuff_name,
  dt.department_name
  FROM
  department d JOIN department_type dt
  ON d.department_id = dt.department_id
  WHERE
  d.stuff_salary > 10000
  GROUP BY d.department_id
  HAVING COUNT(*)>1;

• 非等值连接

  #计算员工薪资所对应的级别
  SELECT
  d.stuff_id ,
  d.stuff_name,
  sr.role
  FROM
  department d JOIN stuff_role sr
  ON d.stuff_salary BETWEEN sr.MIN AND sr.MAX;

• 自连接

  #自连接
  #查询员工对应的领导信息
  SELECT
  d1.stuff_id,
  d1.stuff_name,
  d2.stuff_id,
  d2.stuff_name
  FROM
  department d1 JOIN department d2
  ON d1.manager = d2.stuff_id;

• 交叉连接 CROSS JOIN 从一张表中去循环取数据，然后和另一张表匹配，连接本身字段就会增加数据，会形成笛卡尔积，作用是维护用户数据的完整性

  #交叉连接
  SELECT
  d.stuff_name,
  d.stuff_id,
  dt.department_name,
  dt.department_id
  FROM
  department d CROSS JOIN department_type dt -- 返回两张表所有的排列组合
  #ON dt.department_id = d.department_id; -- 若加上这句，则在上句结果中选出满足条件的数据

3.4.2.2 外连接

• 左外连接是以左表为基准，将左表全部输出。输出右表中符合条件的数据

  #查询员工信息，显示部门信息
  SELECT
  d.stuff_id,
  d.stuff_name,
  dt.department_name
  FROM
  department d LEFT JOIN department_type dt 
  ON d.department_id = dt.department_id;

• 右外连接是以右表为基准，将右表全部输出。输出左表中符合条件的数据

  #查询员工信息，显示部门信息
  SELECT
  d.stuff_id,
  d.stuff_name,
  dt.department_name
  FROM
  department d RIGHT JOIN department_type dt 
  ON d.department_id = dt.department_id;

• 联合查询：UNION 与 UNION ALL 将多条查询的结果集统一合并到一起，集中显示(列数要相等)

  #两个表列数不相等，会报错，提示列数不相等
  SELECT
  *
  FROM
  department
  UNION
  SELECT
  *
  FROM
  department_type;

   

• 全连接：FULL JOIN 。 MySQL不支持这个功能，可以通过左连接+右连接+联合查询实现

  #联合查询+实现全连接
  #查询员工信息表和部门表中的全部数据
  SELECT
  d.*,
  dt.*
  FROM
  department d LEFT JOIN department_type dt
  ON d.department_id = dt.department_id
  UNION
  SELECT
  d.*,
  dt.*
  FROM
  department d RIGHT JOIN department_type dt
  ON d.department_id = dt.department_id;

3.5 子查询

• 在SQL语句中使用SELECT语句，我们称为子查询，也叫内查询，之前我们只有一个SELECT时，叫做主查询

  #查询工资高于LNN的员工信息
  SELECT
  *
  FROM
  department
  WHERE
  stuff_salary > (
  SELECT
  stuff_salary
  FROM
  department
  WHERE
  stuff_name = 'LNN'
  );

• 分类：

  1. 按照出现位置

     • SELECT语句之后---标量子查询(SELECT之后正能有标量子查询)

     • FROM之后---表子查询

     • WHERE/HAVING之后---标量子查询、列子查询、行子查询

     • EXISTS之后---相关子查询

  2. 按照结果集的行列数分类

     • 标量子查询---结果只能由一行多列

       #查询工资高于LNN的第2部门的员工信息,并显示部门名称
       SELECT
       department.stuff_id,
       department.stuff_name,
       department_type.department_id,
       department_type.department_name
       FROM
       department
       JOIN department_type ON department.department_id = department_type.department_id
       WHERE
       stuff_salary > (
       SELECT
       stuff_salary
       FROM
       department
       WHERE
       stuff_name = 'LNN' 
       ) AND department.department_id = 2

        

     • 列子查询---结果集一般有一列多行

       #查询工资大于部门2任意员工工资的员工信息
       #①查询部门2的工资
       #②查询到除各部门员工工资大于部门2 的任意工资
       SELECT
       *
       FROM
       department
       WHERE
       stuff_salary > ANY (
       (
       SELECT
       stuff_salary
       FROM
       department
       WHERE
       department_id = 2
       )
       )
       AND department_id <> 2;
       ​
       -- 将上述案例用MIN()替代
       SELECT
       *
       FROM
       department
       WHERE
       stuff_salary >= (
       SELECT
       MIN(stuff_salary)
       FROM
       department
       WHERE
       department_id = 2
       )
       AND department_id <> 2;

       #查询工资小于部门2所有员工工资的员工信息
       SELECT
       *
       FROM
       department
       WHERE
       stuff_salary < ALL (
       (
       SELECT
       stuff_salary
       FROM
       department
       WHERE
       department_id = 2
       )
       )
       AND department_id <> 2;
       ​
       -- 将上述案例中ALL用MAX()替代
       SELECT
       *
       FROM
       department
       WHERE
       stuff_salary <= (
       SELECT
       MAX(stuff_salary)
       FROM
       department
       WHERE
       department_id = 2
       )
       AND department_id <> 2;

        

     • 行子查询---结果集一般有一行多列

       #查询部门表，计算员工人数，员工数为null的要显示0
       SELECT
       dt.department_id,
       (SELECT
       COUNT(*)
       FROM department d WHERE d.department_id = dt.department_id
       )
       FROM
       department_type dt;

     • 表子 查询---FROM之后

       #查询各个部门平均工资的等级
       SELECT
       b1.department_id,
       sr.role
       FROM
       stuff_role sr
       JOIN (
       SELECT
       department_id,
       AVG(stuff_salary) 平均工资
       FROM
       department
       GROUP BY
       department_id
       )b1
       ON b1.平均工资 BETWEEN sr.MIN AND sr.MAX;

       1. 表子查询中先执行的是子查询

       2. 表子查询中一定要记得其别名

     • 相关查询---EXIST之后的查询

       EXIST()判断结果集是否存在，其可以用IN()来代替

       #输出有员工的部门
       SELECT
       dt.department_name
       FROM
       department_type dt
       WHERE
       EXISTS (
       SELECT
       *
       FROM
       department d
       WHERE
       d.department_id = dt.department_id
       )
       ​
       #用IN()改写
       SELECT
       dt.department_name
       FROM
       department_type dt
       WHERE
       dt.department_id IN (
       SELECT
       d.department_id
       FROM
       department d
       WHERE
       d.department_id = dt.department_id
       )

     • 分页查询

       分页查询的管理子limit、oracle中的rownum叫做伪列

       #一般结构
       SELECT
       列表
       FROM 表名
       [连接类型] JOIN … ON …
       WHERE
       GROUP BY
       HAVING
       ORDER BY
       LIMIT index,size -- index:索引的起始位置（这里从0开始，比较违反mysql的规定）
       -- size:显示数据的长度

       #查询第一也显示五条数据
       SELECT
       *
       FROM
       department
       LIMIT 0,5;

• 特点：

  • 使用子查询时必须放在一堆小括号中

  • 标量子查询一般都需要搭配条件运算符使用>、<、=、<>、>=、<=、<=>

  • 列子查询一般需要搭配多行操作符使用 INT/NOT,ANY/SOME

  • 子查询一般放在条件语句的右侧

  • 子查询的执行顺序高于主查询

3.6 添加数据

DML语法

INSERT INTO

#一般语法
INSERT INTO 表名(column1,column2) VALUES(value1,value2)…

#多行插入
INSERT INTO department (
stuff_id,
stuff_name,
stuff_salary,
department_id,
manager,
join_date
)
VALUES
(17, 'LPP', 3000, NULL, 0, NOW()),
(18, 'LPP', 3000, NULL, 0, NOW()),
(19, 'LPP', 3000, NULL, 0, NOW());
​
#只能做单行插入的方式
INSERT INTO department
SET stuff_id = 20,
 stuff_name = 'LSS',
 stuff_salary = 3333,
 department_id = 2,
 manager = 1,
 join_date = NOW();
​
#给一个stuff_salary 和department_id 空值
#方法一
INSERT INTO department (
stuff_id,
stuff_name,
stuff_salary,
department_id,
manager,
join_date
)
VALUES
(12, 'LPP', 3000, NULL, 0, NOW());
​
#方法二
INSERT INTO department (
stuff_id,
stuff_name,
stuff_salary,
manager,
join_date
)
VALUES
(13, NULL, 3000, 0, NOW());
​
#如果是添加全部字符段可以使用如下方式
INSERT INTO department
VALUES
(14, 'LKA', 3000, NULL, 0, NOW());
​
#添加子查询的结果,子查询结果中的列必须和添加的列相同
INSERT INTO department (
stuff_id,
stuff_name,
stuff_salary,
department_id,
manager,
join_date
) SELECT
16,
'LKA',
3000,
NULL,
0,
NOW();
​
-- 方法二
INSERT INTO department SELECT
15,
'LKA',
3000,
NULL,
0,
NOW();
​

3.7 更新数据

DML语法

UPDATE … SET …

#一般格式
UPDATE
表名
SET 需要修改的列名 = value
WHERE 列名 = 修改的数据匹配列

#修改stuff_id = 15 中的 stuff_name 的数据中的值修改为'LBB'
UPDATE department
SET stuff_name = 'LBB'
WHERE
stuff_id = 15
​
#修改表 中所有的 stuff_name 的数据中的值修改为'LBB'
UPDATE department
SET stuff_name = 'LBB'

如果要回滚数据，需要在DML前，进行自动提交设置：SET AUTOCOMMIT = TRU

#多列更新
UPDATE department
SET stuff_name = 'XJJ',
stuff_salary = 5000
WHERE 
stuff_id = 19;

3.8 删除数据

DML语法

• DELETE FROM

  DELETE FROM 表名
  WHERE [条件]

  #删除id = 13 和 id = 19的一条数据
  DELETE
  FROM department
  WHERE stuff_id = 13 OR stuff_id = 19;

  #删除id = 13 和 id = 19的一条数据
  DELETE
  FROM department
  WHERE stuff_id = 13 OR stuff_id = 19;

  #连表删除
  ​
  #删除部门和信息表中2号部门及其2号部门对应的员工信息
  DELETE d,dt
  FROM department d JOIN department_type dt
  ON d.department_id = dt.department_id
  WHERE dt.department_id = 3;

• TRUNCATE

  1. 用于清空表

  2. 效率比DELETE稍微高一点

  3. TRUNCATE 不能使用WHERE进行筛选

  4. 如果又自增长列，使用DELETE之后再次进行插入，那么自增长列会从断点开始，TRUNCATE删除后再插入是从1开始

  5. TRUNCATE 没有返回值，DELETE 有一个返回值为INT的返回值

  6. TRUNCATE 删除是不能回滚的，DELETE 删除课可以回滚

​

#删除id = 13 和 id = 19的一条数据
DELETE
FROM department
WHERE stuff_id = 13 OR stuff_id = 19;

#连表删除
​
#删除部门和信息表中2号部门及其2号部门对应的员工信息
DELETE d,dt
FROM department d JOIN department_type dt
ON d.department_id = dt.department_id
WHERE dt.department_id = 3;

四、DDL语法

4.1 对于库的操作

#创建库(判断库不存在时)
CREATE DATABASE IF NOT EXISTS crteatetest
#删除库(判断库存在时)
DROP DATABASE IF EXISTS crteatetest;

• 库创建好之后一般不进行修改

4.2 对于表的操作

• 一般形式

CREATE TABLE stu_info(
列名 字段类型 约束,
列名 字段类型 约束,
……
)

• 创建和删除表

#创建表
CREATE TABLE IF NOT EXISTS stu_info(
snum INT, -- 学号
sname VARCHAR(20), -- 学生姓名
age INT, -- 年龄
gender CHAR, -- 性别
address VARCHAR(50), -- 地址
subject_id INT, -- 学科
tnum INT -- 教师编号
)
​
​
#删除表
DROP TABLE IF EXISTS stu_info

• 修改表

  • 对表的重命名

    #对表的重命名
    #方法一
    ALTER TABLE stu_info RENAME stuinfo
    #方法二
    RENAME TABLE stuifo TO stu_info

  • 对结构修改

    • 添加列

      1. 一般形式

         ALTER TABLE 需要添加的表名[ADD column] 新列名 列类型

      2. 可以在首位添加列，也可以指定位置添加列

         #添加列
         ALTER TABLE stu_info ADD COLUMN cla_id INT;
         #在首位添加列
         ALTER TABLE stu_info ADD COLUMN tel INT FIRST;
         #指定位置添加
         ALTER TABLE stu_info ADD COLUMN person_id VARCHAR(20) AFTER sname;

    • 删除列

      #一般形式
      ALTER TABLE 需要删除的表名[DROP column] 列名

      #删除列
      ALTER TABLE stu_info DROP COLUMN tel;

    • 修改列

      #一般形式
      ALTER TABLE 需要修改的表名[CHANGE column] 旧列名 新列名 类型

      #修改列名
      ALTER TABLE stu_info CHANGE COLUMN cla_id class_id  INT;

    • 修改列类

      #一般形式
      ALTER TABLE 需要修改的表名[CHANGE column] 列名 类型

      #修改列类型
      ALTER TABLE stu_info MODIFY class_id VARCHAR(20);
      ​
      #在指定列之后修改
      ALTER TABLE stu_info MODIFY class_id VARCHAR(20) AFTER age;

  • 复制表

    #一般格式 
    ​
    #复制为新表(只能复制表的结构)
    CREATE TABLE 新表名 LIKE 被复制的表名;
    ​
    #复制为新表及其数据
    CREATE TABLE 新表名
    SELECT * FROM 被复制的表名
    ​
    #将指定的字段和数据复制到新的表中
    CREATE TABLE 新表名
    SELECT 列名1，列名2 FROM 被复制的表

    #复制表结构
    CREATE TABLE stu_info0 LIKE stu_info;
    ​
    #复制指定列的表结构
    CREATE TABLE stu_info1 
    SELECT snum,sname,address,person_id
    FROM stu_info si
    WHERE NULL;

sql：结构化查询语言，专门用来和数据库进行通信的语言

二、MySQL常用命令

2.1登录的常用命令

（1）登录命令

mysql -h 主机名 -P 端口号 -u 用户名 -p密码 （输入密码不能加空格，端口号P大写）

mysql -h 主机名 -P 端口号 -u 用户名 -p （下一行输入密码） （输入密码不能加空格，端口号P大写）

mysql -u 用户名 -p密码 简写

（2）常用命令

查看所有库：show databases;

使用库：use 库名;

查看当前库下所有的表:show tables;

查看指定表结构：desc 表名；

创建数据库：create databases 库名;

创建表：

create table 表名{
id int,
name varchar(20)
}

删除表：

drop table 表名；

查看指定表中所有的数据：

select * from 表名

给指定表插入一条数据：

insert into 表名(列名1，列名2....)values(字段名1，字段名2...)

修改表中指定数据：

update 表名 set 列名=修改的值 where 指明条件

删除表中的一条数据：

delete from 表名 where 删除条件

（3）MySql语法规范

a、要求关键字大写

b、sql结尾用；或者\g；通常使用；

c、可以进行缩进和换行

2.2 SQL语言分类

2.1.1 DML(Data manipulation Language)

数据操纵语言，用于添加删除，修改，查询数据记录，检查完整性，内韩DQL语言

包含的SQL语句：

• INSERT

• UPDATE

• DELETE

• SELECT：是SQL语言的基础，尤其重要

2.1.2 DDL

数据定义语言，用于库和表的创建，修改，删除

• CREATE TABLE：创建数据库表

• ALTER TABLE：更改表结构

• DROP TABLE：删除表

• CREATE INDEX：在表上建立索引

• DROP INDEX：删除索引

2.1.4 DCL

数据控制语言，用于定义用户的访问权限和安全级别

• GRANT：授予访问权限

• REVOKE：撤销访问权限

• COMMIT：提交事务

• ROLLBACK：事务回滚

• SAEPOINT：设置保存点

• LOCK：对数据库特定的部分进行锁定

三、DQL语法

DQL包含在DML语句中

3.1基本语句

关键字 SELECT 列名 FROM 表名；

#新建一个表，
CREATE TABLE department (
stuff_id INT,
stuff_name VARCHAR (20),
stuff_salary INT,
department_id INT
);
​
#插入行
INSERT INTO department (stuff_id, stuff_name)
VALUES
(1, 'ljq');

#更改信息
UPDATE department
SET stuff_salary = NULL
WHERE
stuff_id = 7;
​
#显示表
SELECT
*
FROM
department;
​
#删除表
DROP TABLE department;
​
#取别名
SELECT
stuff_id AS 编号,
stuff_name AS 姓名,
stuff_salary  年薪,
department_id AS "部门"
FROM
department;
​
#去重
SELECT
DISTINCT stuff_id,stuff_name
FROM
department;
​
#清空表中数据
TRUNCATE TABLE jqfirst_test;

3.2 条件查询

3.2.1 基本语法

SELECT
列名 
FROM 
表名
WHERE  -- HAVING 为查询后过滤
条件表达式
排序关键字 ORDER BY 排序字段名 -- 注意：ORDER BY 子句在SELECT语句的结尾
DESC ASC -- DESC 降序 ASC 升序

WHERE ：where针对于原表，作用在GROUP BU 之前 ，不能直接使用聚合函数

HAVING：针对于分组后的数据，作用在GROUP BY 之后，可以使用聚合函数

3.2.2 条件表达式

1. 简单的条件运算：> 、>、=、!=（可以使用，但推荐使用<>）、<=>（安全等于，可以判空，也可以做等值运算）

2. 逻辑运算符：&&、||、！、mysql支持，但是推荐使用：AND、OR、NOT

   #条件查询
   SELECT
   stuff_id,
   sruff_name
   FROM
   department
   WHERE
   stuff_id = 22 AND stuff_name = 'LN';
   ​
   #查询员工姓名不是LQQ并且工资大于4000
   SELECT 
   *
   FROM
   department
   WHERE
    stuff_name != 'LQQ' AND stuff_salary > 4000;
   
   #逻辑条件查询练习，注意：NOT表示取反
   SELECT
   *
   FROM
   department
   WHERE
   NOT (
   (
   stuff_name != 'LQQ'
   AND stuff_salary > 4000
   AND stuff_salary < 100000
   )
   OR department_id = 1
   ); 

3. 模糊条件：LIKE、BETWEEN……AND……、IN、IS NULL

   • LIKE用法：做模糊匹配

     #查询员工姓名中包含字符a的员工信息
     SELECT 
     *
     FROM
     department
     WHERE
     stuff_name LIKE '%X%';
     ​
     #查询员工中第二个字母为Q,第三个字母为Q的员工信息
     SELECT 
     *
     FROM
     department
     WHERE
     stuff_name LIKE '_Q%' AND stuff_name LIKE '__Q';
     ​
     #查名字有下划线的员工信息
     SELECT
     *
     FROM
     department
     WHERE
     stuff_name LIKE '%\_%';-- 在mysql中不推荐使用这种方式
     stuff_name LIKE '%$_%' ESCAPE '$';-- 手动指定通配符 声明$是一个转义字符，mysql推荐使用。

   • IN用法：筛选集合中的元素（开发中尽量少使用，效率低）

     #查询编号为1，4，9的员工
     SELECT
     *
     FROM
     department
     WHERE
     stuff_id IN(1,4,9);

   • BETWEEN……AND…… ：用来查找一个区间的值

     #查询工资为5000到20000之间的员工信息
     SELECT
     *
     FROM
     department
     WHERE
     stuff_salary BETWEEN 5000 AND 20000;

   • IS NULL：筛选空值的指定元素

     #查询工资为空的员工
     SELECT
     *
     FROM
     department
     WHERE
     stuff_salary IS NULL;

3.2.3 排序

• 一般格式

#查询工工资在4500到10000的员工信息，并排序
SELECT
*
FROM
department
WHERE
stuff_salary BETWEEN 4500
AND 20000 
ORDER BY
stuff_salary
DESC   -- 默认为ASC升序

后可使用聚合函数，时针对ORDER BY 得出的结果进行排序的

• 通过别名进行排序

#通过别名来排序
#查询研发部员工的年薪
SELECT
stuff_name 员工姓名,
department_id 部门,
stuff_salary * 12 年薪
FROM
department
WHERE
department_id = 2
ORDER BY
年薪

• 使用多字段排序

#使用多字段排序
#查询研发部员工的年薪按照降序排序，年薪相同的时候将员工的姓名按照升序排列
SELECT
stuff_name 员工姓名,
department_id 部门,
stuff_salary * 12 年薪
FROM
department
WHERE
department_id = 2 OR department_id = 1
ORDER BY
年薪 DESC,stuff_name ASC;

 

3.2.4 MySQL中常用函数

函数：MySQL函数封装了一系列处理逻辑，用于调用解决一定的计算问题以及简单的逻辑问题（日期的格式化，字符类型操作）

语法：

SELECT
函数() 别名
FROM
表名
WHERE
函数()
ORDER BY
函数()

分类：

• 单行函数：如：concat,length,用于字符处理的函数

• 组函数：统计函数，聚合函数，组函数

 

3.2.4.1 字符处理函数

• LENGTH(str) ：计算字节数

  #计算出员工姓名中字节数大于1的员工，并按照字节数升序排序
  SELECT
  stuff_id,
  stuff_name
  FROM
  department
  WHERE
  LENGTH(stuff_name) > 1
  ORDER BY
  LENGTH(stuff_name) ASC;

• CONCAT(str1,str2,…)：拼接字符串

  #查询员工信息表，将员工姓名和员工的id拼接显示
  SELECT
  CONCAT(stuff_id,'_',stuff_name) 
  FROM
  department

• UPPER(str) ：转成大写

  LOWER(str)：转成小写

  #查询员工信息表，将员工共姓名转成大写
  SELECT
  LOWER(stuff_name)
  FROM
  department;
  ​
  SELECT UPPER('transform successful');

• SUBSTR()、SUBSTRIG()：截取字符，通过索引查找，MySQL中索引是从1开始的

  SELECT
  SUBSTR('一拳超人埼玉' FROM 3 FOR 2);

  #查询员工信息表，将员工id和员工姓名拼接，将员工姓名只显示第二个字符，并将其大写
  #将员工id只显示第一个字符，并小写
  SELECT
  CONCAT(
  LOWER(SUBSTR(stuff_id FROM 1 FOR 1)),
  '+',
  UPPER(SUBSTR(stuff_name FROM 2 FOR 1))
  ) AS 员工姓名地址
  FROM
  department;

• LPAD(str,len,padstr) 指定的长度内在字符串的左边用指定的字符填充，直至填满，len代表填充之后的字符串长度

  RPAD(str,len,padstr) 在右边添加

  SELECT
  LPAD('沾湿黑礼服',10,'*');
  SELECT
  RPAD('沾湿黑礼服',10,'*');

• TRIM([remstr FROM] str) 去掉字符串首部和尾部与指定字符匹配的所有字符

  #去空格
  SELECT
  TRIM('      天刚刚破晓      ');

• INSTR(str,substr) 返回指定字符串在字符串中首次出现的位置

  SELECT
  INSTR('werT33423REW','wer');

• TELACE(str,from_str,to_str)：替换

  #将员工地址替换为国家名
  SELECT
  REPLACE(department_id,'1','china'),
  REPLACE(department_id,'2','japan'),
  REPLACE(department_id,'0','amarica')
  FROM
  department;

• 函数可以嵌套使用

  #查询员工信息表，将员工姓名和员工地址拼接，其中员工共姓名转成小写写，员工id大写
  SELECT
  CONCAT(UPPER(stuff_id),'+',LOWER(stuff_name))
  FROM
  department;

   

3.2.4.2 数学函数

• ROUND(X)：四舍五入

  ROUND(X,D)：指定小数点后面保留小数的位数，同时四舍五入

  #查询员工信息表，计算员工的月绩效奖金（salar/21.75*0.75），并小数点后一位四舍五入
  SELECT
  stuff_id,
  stuff_name,
  ROUND(stuff_salary/21.75*0.75,1)
  FROM
  department;

• TRUNCATE(X,D)：截断 保留小数点后指定的位数，不进行四舍五入

• MOD(N,M)：模运算 求N对M的模，即N为被除数，M为除数

• CEIL(X)：向上取整，返回大于等于当前数的最小整数

  SELECT
  CEIL(-10.1);

• FLOOR(X)：向下取整，返回等于小于当前数的最大整数

3.2.4.3 日期函数

用于格式化时间，解析时间

• NOW()：当前系统的日期时间

• STR_TO_DATE(str,format)：将指定的字符串解析成指定格式的日期类型（

  %Y：四位年份

  %y：两位的年份

  %m：月份（01……12）两位数表示

  %c：月份，一位数表示

  %d：日，用两位数表示

  %H：小时（24小时制）

  %h：小时（12小时制）

  %i：分钟（0-59）

  %s：秒（0-59）

  ）

  SELECT
  STR_TO_DATE('2019-07-24','%Y-%m-%d'),-- mysql默认的日期类型
  STR_TO_DATE('2019-07-24','%Y年%m月%d日');-- 解析不出，sql会将年月日当作字符串进行处理

• DATE_FORMAT(date,format)：将日期转换成字符串

  SELECT
  DATE_FORMAT(NOW(),'%Y年%M月%D日 %H时%i分%s秒');

  id#查询员工信息表中的入职日期，并将姓名和日期拼接
  eELECT
  CONCAT(
  stuff_name,
  '_',
  STR_TO_DATE(join_date, '%Y-%m-%d')
  )
  /*
  CONCAT(
  stuff_name,
  '_',
  join_date
  )
  */ -- 也可以这样，但是不推荐，这样的效率很低，再开发中不要这样写
  FROM
  department;

• YEAR()：显示当前年份

• MONTH()：显示指定时间月份

• DAY()：显示指定日期（只有天）

  ……等

• CURRENT_TIME()：只显示当前时间

• CURRENT_DATE()：只显示当前日期

3.2.4.4 组函数（聚合函数）

统计多行数据，形成一行结果

特点：

1. 组函数在使用的过程中，如果配合列来使用，则列必须放到GROUP BY()之后

2. 聚合函数不能直接使用再WHERE语句之后

3. 在聚合函数中出了COUNT之外，都会对NULL进行忽视

语法：

SELECT [column],组函数 FROM

• COUTN(expr)：统计个数，不支持NULL值，非空类型都支持

  SELECT
  COUNT(2) -- 统计2列的所有行，统计所有行的时候一般不推荐使用COUNT(column)   colum表示列名
  -- 推荐使用COUNT(*)或者COUNT(1)，因为COUNT()对NULL无效，而且后者通过索引进行统计，效率高
  FROM
  department;

  #统计员工信息表中部门2的总人数
  SELECT
  COUNT(department_id)
  FROM
  department
  WHERE
  department_id = 2;

• SUM(expr)：求和，支持数值类型

  #计算部门2所有员工的总工资
  SELECT
  SUM(stuff_salary)
  FROM
  department
  WHERE
  department_id = 2;

• AVG([DISTINCT] expr)：平均值，支持数值类型

  #计算部门2的平均工资
  SELECT
  AVG(stuff_salary)
  FROM
  department
  WHERE
  department_id = 2;

• MAX(expr)：求最大，可排序的所有类型

  MIN(expr)：求最小，可排序的所有类型

  #求员工工资的最高薪资和最低薪资和工资大于平均薪资的人数以及最高薪资的个数
  #以及员工的总工资，员工的平均工资
  SELECT
  MAX(stuff_salary) 最高薪资,
  MIN(stuff_salary) 最低薪资,
  SUM(stuff_salary) 工资总和,
  AVG(stuff_salary) 平均工资,
  (
  SELECT
  COUNT(stuff_salary)
  FROM
  department
  WHERE
  stuff_salary > (
  SELECT
  AVG(stuff_salary)
  FROM
  department
  )
  )大于平均薪资的人数
  FROM
  department;
  ​

3.2.4.5 分组函数

• GROUP BY，一般表达式

SELECT
聚合函数
列名（注意：最好写GROUP BY使用的列名）
FROM
表名
WHERE
条件
GROUP BY 分组字段（可以写表达式，但不能输聚合函数）

• GROUP BY 分组字段（可以写表达式，但不能输聚合函数）

#统计工资大于20000和小于20000的人数
SELECT
COUNT(stuff_id),
stuff_salary
FROM
department
GROUP BY stuff_salary < 20000;

• WHERE ：WHERE针对于原表，作用在GROUP BU 之前 ，不能直接使用聚合函数

  HAVING：针对于分组后的数据，作用在GROUP BY 之后，可以使用聚合函数

  分组后筛选的，可以直接使用HAVING，但推荐使用分组前筛选，这会使得sql优化时效率更高

#查找各部门工资大于6000的人数
SELECT
COUNT( stuff_id),
department_id
FROM
department
WHERE
stuff_salary > 6000
GROUP BY department_id;
​
​
#平均工资大于6000的部门
SELECT
AVG(stuff_salary),
department_id
FROM
department
GROUP BY department_id
HAVING
AVG(stuff_salary) > 6000;
​
#GROUP BY 和 HAVING 可以使用别名来查询，WHERE不可以使用别名（因为其针对于原表数据进行筛选）
#查询各部门员工人数超过两个的部门
SELECT
department_id 部门,
COUNT(1) 计数
FROM
department
GROUP BY 部门
HAVING 计数 > 2;
​
#查询员工人数超过两个的最高和最低薪资
SELECT
department_id,
MAX(stuff_salary) 最高工资,
MIN(stuff_salary) 最低工资
FROM
department
GROUP BY department_id
HAVING COUNT(*) > 2;
​
#统计各部门员工工资大于6000的人数,并将人数超过2的部门按照人数排序
SELECT
department_id,
COUNT(stuff_id) 人数
FROM
department
WHERE
stuff_salary > 6000 
GROUP BY
department_id
HAVING
人数 >= 2
ORDER BY
COUNT(stuff_id);
​
#查询大于平均工资的人数,并按照部门分组
SELECT
COUNT(stuff_id),
department_id
FROM
department
WHERE
stuff_salary > (
SELECT
AVG(stuff_salary)
FROM
department
)
GROUP BY
department_id; -- 这个问题其实可以用HAVING轻松解决

3.3 条件语句

3.3.1 IF()

IF(expr1,expr2,expr3)：如果expr1成立就显示expr2的值，否则显示expr3的值，类似于java中的三元运算符

#查询员工的岗位级别，员工年薪大于40万的为t1,20-40为t2，否则为t3
SELECT
stuff_id,
stuff_name,
IF(stuff_salary*12 <= 200000,'t3',IF(stuff_salary*12<=400000,'t2','t1')),
stuff_salary*12
FROM
department;

3.3.2 CASE WHEN

一般格式

CASE 常量或者是语句（也可以不写）
WHEN 
常量或者是语句 
THEN 显示的常量或者是语句（是语句的时候使用‘；’号结尾）
……
ELSE 不满足时执行的语句或显示的变量（类似于Java中的switch(表达式)）
END

SELECT
stuff_id,
stuff_name,
CASE department_id
WHEN 0 THEN 'japan'
WHEN 1 THEN 'amanrica'
WHEN 2 THEN 'china'
ELSE 'unkonw'
END
FROM
department

#查询员工的岗位级别，员工年薪大于40万的为T1,20-40为T2，否则为T3
SELECT
stuff_id,
stuff_name,
CASE 
WHEN stuff_salary*12 > 400000 THEN 'T1'
WHEN stuff_salary*12 <= 400000 AND stuff_salary*12>200000 THEN 'T2'
ELSE 'T3'
END,
stuff_salary*12
FROM
department;3.2.4.5 分组函数

3.4 连接查询

连续查询，当我们需要查询的字段不是同一张表时，需要连接查询

#查询员工信息，显示员工姓名、部门名字、部门编号
SELECT
stuff_name,
department_name,
department.department_id
FROM
department,department_type
WHERE
department.department_id = department_type.department_id;

sql分类：

1. 按年份划分为：sql92和sql99标准

2. 按功能：

3.4.1 SQL92连接查询

3.4.1.1 等值连接

#查询各部门员工信息中工资大于10000的人
#并统计人数大于2的部门，显示员工的部门编号和部门名称
SELECT-- 查询出员工对应的部门信息
员工信息.department_id,
部门.department_name,
COUNT(1) 计数
FROM
department 员工信息,
department_type 部门
WHERE
员工信息.department_id = 部门.department_id 
AND stuff_salary > 10000   -- 晒选出工资大于20000的人
GROUP BY 员工信息.department_id
HAVING COUNT(1) >= 2;

#查询员工的信息，和其领导的信息
SELECT
stuff.stuff_id,
stuff.stuff_name,
manager.stuff_id,
manager.stuff_name
FROM
department manager,department stuff -- 自己和自己连接
WHERE
stuff.manager = manager.stuff_id;
​

 

 

3.4.1.2 非等值连接

#通过员工工资查询员工对应的工资级别
SELECT
员工.role,
综合.stuff_name,
综合.stuff_id
FROM
stuff_role 员工, department 综合
WHERE
综合.stuff_salary BETWEEN 员工.MIN AND 员工.MAX;

#查询员工的部门信息以及角色信息，显示部门名称、角色名称、员工编号姓名
SELECT
department_type.department_id,
department_type.department_name,
department.stuff_name,
department.stuff_id,
stuff_role.role
FROM
department,department_type,stuff_role
WHERE
department_type.department_id = department.department_id
AND
department.stuff_salary BETWEEN stuff_role.MIN AND stuff_role.MAX;

3.4.2 SQL99

sql优化中：连接查询时能使用ON就用ON，这样效率高于WHERE

3.4.2.1 内连接

• 等值连接

  #语法
  SELECT
  查询的列
  FROM
  表1 (INNER/LEFT(OUTER) JOIN/RIGHT JOIN) JOIN 表
  ON 连接条件
  WHERE 筛选条件
  GROUP BY 分组条件
  OREDR BY 排序

  #查询员工的部门信息表
  SELECT 
  d.stuff_id,
  d.stuff_name,
  dt.department_name
  FROM
  department d JOIN department_type dt
  ON 
  d.department_id = dt.department_id
  
  #查询各部门员工工资大于5000的人数,显示人数大于1的部门，显示员工编号、姓名、部门名称
  SELECT
  d.stuff_id,
  d.stuff_name,
  dt.department_name
  FROM
  department d JOIN department_type dt
  ON d.department_id = dt.department_id
  WHERE
  d.stuff_salary > 10000
  GROUP BY d.department_id
  HAVING COUNT(*)>1;

• 非等值连接

  #计算员工薪资所对应的级别
  SELECT
  d.stuff_id ,
  d.stuff_name,
  sr.role
  FROM
  department d JOIN stuff_role sr
  ON d.stuff_salary BETWEEN sr.MIN AND sr.MAX;

• 自连接

  #自连接
  #查询员工对应的领导信息
  SELECT
  d1.stuff_id,
  d1.stuff_name,
  d2.stuff_id,
  d2.stuff_name
  FROM
  department d1 JOIN department d2
  ON d1.manager = d2.stuff_id;

• 交叉连接 CROSS JOIN 从一张表中去循环取数据，然后和另一张表匹配，连接本身字段就会增加数据，会形成笛卡尔积，作用是维护用户数据的完整性

  #交叉连接
  SELECT
  d.stuff_name,
  d.stuff_id,
  dt.department_name,
  dt.department_id
  FROM
  department d CROSS JOIN department_type dt -- 返回两张表所有的排列组合
  #ON dt.department_id = d.department_id; -- 若加上这句，则在上句结果中选出满足条件的数据

3.4.2.2 外连接

• 左外连接是以左表为基准，将左表全部输出。输出右表中符合条件的数据

  #查询员工信息，显示部门信息
  SELECT
  d.stuff_id,
  d.stuff_name,
  dt.department_name
  FROM
  department d LEFT JOIN department_type dt 
  ON d.department_id = dt.department_id;

• 右外连接是以右表为基准，将右表全部输出。输出左表中符合条件的数据

  #查询员工信息，显示部门信息
  SELECT
  d.stuff_id,
  d.stuff_name,
  dt.department_name
  FROM
  department d RIGHT JOIN department_type dt 
  ON d.department_id = dt.department_id;

• 联合查询：UNION 与 UNION ALL 将多条查询的结果集统一合并到一起，集中显示(列数要相等)

  #两个表列数不相等，会报错，提示列数不相等
  SELECT
  *
  FROM
  department
  UNION
  SELECT
  *
  FROM
  department_type;

   

• 全连接：FULL JOIN 。 MySQL不支持这个功能，可以通过左连接+右连接+联合查询实现

  #联合查询+实现全连接
  #查询员工信息表和部门表中的全部数据
  SELECT
  d.*,
  dt.*
  FROM
  department d LEFT JOIN department_type dt
  ON d.department_id = dt.department_id
  UNION
  SELECT
  d.*,
  dt.*
  FROM
  department d RIGHT JOIN department_type dt
  ON d.department_id = dt.department_id;

3.5 子查询

• 在SQL语句中使用SELECT语句，我们称为子查询，也叫内查询，之前我们只有一个SELECT时，叫做主查询

  #查询工资高于LNN的员工信息
  SELECT
  *
  FROM
  department
  WHERE
  stuff_salary > (
  SELECT
  stuff_salary
  FROM
  department
  WHERE
  stuff_name = 'LNN'
  );

• 分类：

  1. 按照出现位置

     • SELECT语句之后---标量子查询(SELECT之后正能有标量子查询)

     • FROM之后---表子查询

     • WHERE/HAVING之后---标量子查询、列子查询、行子查询

     • EXISTS之后---相关子查询

  2. 按照结果集的行列数分类

     • 标量子查询---结果只能由一行多列

       #查询工资高于LNN的第2部门的员工信息,并显示部门名称
       SELECT
       department.stuff_id,
       department.stuff_name,
       department_type.department_id,
       department_type.department_name
       FROM
       department
       JOIN department_type ON department.department_id = department_type.department_id
       WHERE
       stuff_salary > (
       SELECT
       stuff_salary
       FROM
       department
       WHERE
       stuff_name = 'LNN' 
       ) AND department.department_id = 2

        

     • 列子查询---结果集一般有一列多行

       #查询工资大于部门2任意员工工资的员工信息
       #①查询部门2的工资
       #②查询到除各部门员工工资大于部门2 的任意工资
       SELECT
       *
       FROM
       department
       WHERE
       stuff_salary > ANY (
       (
       SELECT
       stuff_salary
       FROM
       department
       WHERE
       department_id = 2
       )
       )
       AND department_id <> 2;
       ​
       -- 将上述案例用MIN()替代
       SELECT
       *
       FROM
       department
       WHERE
       stuff_salary >= (
       SELECT
       MIN(stuff_salary)
       FROM
       department
       WHERE
       department_id = 2
       )
       AND department_id <> 2;

       #查询工资小于部门2所有员工工资的员工信息
       SELECT
       *
       FROM
       department
       WHERE
       stuff_salary < ALL (
       (
       SELECT
       stuff_salary
       FROM
       department
       WHERE
       department_id = 2
       )
       )
       AND department_id <> 2;
       ​
       -- 将上述案例中ALL用MAX()替代
       SELECT
       *
       FROM
       department
       WHERE
       stuff_salary <= (
       SELECT
       MAX(stuff_salary)
       FROM
       department
       WHERE
       department_id = 2
       )
       AND department_id <> 2;

        

     • 行子查询---结果集一般有一行多列

       #查询部门表，计算员工人数，员工数为null的要显示0
       SELECT
       dt.department_id,
       (SELECT
       COUNT(*)
       FROM department d WHERE d.department_id = dt.department_id
       )
       FROM
       department_type dt;

     • 表子 查询---FROM之后

       #查询各个部门平均工资的等级
       SELECT
       b1.department_id,
       sr.role
       FROM
       stuff_role sr
       JOIN (
       SELECT
       department_id,
       AVG(stuff_salary) 平均工资
       FROM
       department
       GROUP BY
       department_id
       )b1
       ON b1.平均工资 BETWEEN sr.MIN AND sr.MAX;

       1. 表子查询中先执行的是子查询

       2. 表子查询中一定要记得其别名

     • 相关查询---EXIST之后的查询

       EXIST()判断结果集是否存在，其可以用IN()来代替

       #输出有员工的部门
       SELECT
       dt.department_name
       FROM
       department_type dt
       WHERE
       EXISTS (
       SELECT
       *
       FROM
       department d
       WHERE
       d.department_id = dt.department_id
       )
       ​
       #用IN()改写
       SELECT
       dt.department_name
       FROM
       department_type dt
       WHERE
       dt.department_id IN (
       SELECT
       d.department_id
       FROM
       department d
       WHERE
       d.department_id = dt.department_id
       )

     • 分页查询

       分页查询的管理子limit、oracle中的rownum叫做伪列

       #一般结构
       SELECT
       列表
       FROM 表名
       [连接类型] JOIN … ON …
       WHERE
       GROUP BY
       HAVING
       ORDER BY
       LIMIT index,size -- index:索引的起始位置（这里从0开始，比较违反mysql的规定）
       -- size:显示数据的长度

       #查询第一也显示五条数据
       SELECT
       *
       FROM
       department
       LIMIT 0,5;

• 特点：

  • 使用子查询时必须放在一堆小括号中

  • 标量子查询一般都需要搭配条件运算符使用>、<、=、<>、>=、<=、<=>

  • 列子查询一般需要搭配多行操作符使用 INT/NOT,ANY/SOME

  • 子查询一般放在条件语句的右侧

  • 子查询的执行顺序高于主查询

3.6 添加数据

DML语法

INSERT INTO

#一般语法
INSERT INTO 表名(column1,column2) VALUES(value1,value2)…

#多行插入
INSERT INTO department (
stuff_id,
stuff_name,
stuff_salary,
department_id,
manager,
join_date
)
VALUES
(17, 'LPP', 3000, NULL, 0, NOW()),
(18, 'LPP', 3000, NULL, 0, NOW()),
(19, 'LPP', 3000, NULL, 0, NOW());
​
#只能做单行插入的方式
INSERT INTO department
SET stuff_id = 20,
 stuff_name = 'LSS',
 stuff_salary = 3333,
 department_id = 2,
 manager = 1,
 join_date = NOW();
​
#给一个stuff_salary 和department_id 空值
#方法一
INSERT INTO department (
stuff_id,
stuff_name,
stuff_salary,
department_id,
manager,
join_date
)
VALUES
(12, 'LPP', 3000, NULL, 0, NOW());
​
#方法二
INSERT INTO department (
stuff_id,
stuff_name,
stuff_salary,
manager,
join_date
)
VALUES
(13, NULL, 3000, 0, NOW());
​
#如果是添加全部字符段可以使用如下方式
INSERT INTO department
VALUES
(14, 'LKA', 3000, NULL, 0, NOW());
​
#添加子查询的结果,子查询结果中的列必须和添加的列相同
INSERT INTO department (
stuff_id,
stuff_name,
stuff_salary,
department_id,
manager,
join_date
) SELECT
16,
'LKA',
3000,
NULL,
0,
NOW();
​
-- 方法二
INSERT INTO department SELECT
15,
'LKA',
3000,
NULL,
0,
NOW();
​

3.7 更新数据

DML语法

UPDATE … SET …

#一般格式
UPDATE
表名
SET 需要修改的列名 = value
WHERE 列名 = 修改的数据匹配列

#修改stuff_id = 15 中的 stuff_name 的数据中的值修改为'LBB'
UPDATE department
SET stuff_name = 'LBB'
WHERE
stuff_id = 15
​
#修改表 中所有的 stuff_name 的数据中的值修改为'LBB'
UPDATE department
SET stuff_name = 'LBB'

如果要回滚数据，需要在DML前，进行自动提交设置：SET AUTOCOMMIT = TRU

#多列更新
UPDATE department
SET stuff_name = 'XJJ',
stuff_salary = 5000
WHERE 
stuff_id = 19;

3.8 删除数据

DML语法

• DELETE FROM

  DELETE FROM 表名
  WHERE [条件]

  #删除id = 13 和 id = 19的一条数据
  DELETE
  FROM department
  WHERE stuff_id = 13 OR stuff_id = 19;

  #删除id = 13 和 id = 19的一条数据
  DELETE
  FROM department
  WHERE stuff_id = 13 OR stuff_id = 19;

  #连表删除
  ​
  #删除部门和信息表中2号部门及其2号部门对应的员工信息
  DELETE d,dt
  FROM department d JOIN department_type dt
  ON d.department_id = dt.department_id
  WHERE dt.department_id = 3;

• TRUNCATE

  1. 用于清空表

  2. 效率比DELETE稍微高一点

  3. TRUNCATE 不能使用WHERE进行筛选

  4. 如果又自增长列，使用DELETE之后再次进行插入，那么自增长列会从断点开始，TRUNCATE删除后再插入是从1开始

  5. TRUNCATE 没有返回值，DELETE 有一个返回值为INT的返回值

  6. TRUNCATE 删除是不能回滚的，DELETE 删除课可以回滚

​

#删除id = 13 和 id = 19的一条数据
DELETE
FROM department
WHERE stuff_id = 13 OR stuff_id = 19;

#连表删除
​
#删除部门和信息表中2号部门及其2号部门对应的员工信息
DELETE d,dt
FROM department d JOIN department_type dt
ON d.department_id = dt.department_id
WHERE dt.department_id = 3;

四、DDL语法

4.1 对于库的操作

#创建库(判断库不存在时)
CREATE DATABASE IF NOT EXISTS crteatetest
#删除库(判断库存在时)
DROP DATABASE IF EXISTS crteatetest;

• 库创建好之后一般不进行修改

4.2 对于表的操作

• 一般形式

CREATE TABLE stu_info(
列名 字段类型 约束,
列名 字段类型 约束,
……
)

• 创建和删除表

#创建表
CREATE TABLE IF NOT EXISTS stu_info(
snum INT, -- 学号
sname VARCHAR(20), -- 学生姓名
age INT, -- 年龄
gender CHAR, -- 性别
address VARCHAR(50), -- 地址
subject_id INT, -- 学科
tnum INT -- 教师编号
)
​
​
#删除表
DROP TABLE IF EXISTS stu_info

• 修改表

  • 对表的重命名

    #对表的重命名
    #方法一
    ALTER TABLE stu_info RENAME stuinfo
    #方法二
    RENAME TABLE stuifo TO stu_info

  • 对结构修改

    • 添加列

      1. 一般形式

         ALTER TABLE 需要添加的表名[ADD column] 新列名 列类型

      2. 可以在首位添加列，也可以指定位置添加列

         #添加列
         ALTER TABLE stu_info ADD COLUMN cla_id INT;
         #在首位添加列
         ALTER TABLE stu_info ADD COLUMN tel INT FIRST;
         #指定位置添加
         ALTER TABLE stu_info ADD COLUMN person_id VARCHAR(20) AFTER sname;

    • 删除列

      #一般形式
      ALTER TABLE 需要删除的表名[DROP column] 列名

      #删除列
      ALTER TABLE stu_info DROP COLUMN tel;

    • 修改列

      #一般形式
      ALTER TABLE 需要修改的表名[CHANGE column] 旧列名 新列名 类型

      #修改列名
      ALTER TABLE stu_info CHANGE COLUMN cla_id class_id  INT;

    • 修改列类

      #一般形式
      ALTER TABLE 需要修改的表名[CHANGE column] 列名 类型

      #修改列类型
      ALTER TABLE stu_info MODIFY class_id VARCHAR(20);
      ​
      #在指定列之后修改
      ALTER TABLE stu_info MODIFY class_id VARCHAR(20) AFTER age;

  • 复制表

    #一般格式 
    ​
    #复制为新表(只能复制表的结构)
    CREATE TABLE 新表名 LIKE 被复制的表名;
    ​
    #复制为新表及其数据
    CREATE TABLE 新表名
    SELECT * FROM 被复制的表名
    ​
    #将指定的字段和数据复制到新的表中
    CREATE TABLE 新表名
    SELECT 列名1，列名2 FROM 被复制的表

    #复制表结构
    CREATE TABLE stu_info0 LIKE stu_info;
    ​
    #复制指定列的表结构
    CREATE TABLE stu_info1 
    SELECT snum,sname,address,person_id
    FROM stu_info si
    WHERE NULL;

  •