倬倬吃三碗
0%

MySQL笔记（上）

发表于 更新于 分类于

1. SQL概述

1.1 SQL分类

SQL语言在功能上分为3大类：

  1. DDL（Data Defination Languages，数据定义语言）
1
CREATE,DROP,ALTER等
  1. DML（Data Manipulation Language，数据操作语言）
1
INSERT,DELETE,UPDATE,SELECT等
  1. DCL（Data Control Language,数据控制语言）
1
GRANT,REVOKE,COMMIT,ROLLBACK,SAVEPOINT等

1.2 SQL大小写规范

  • MySQL在win环境下大小写不敏感
  • MySQL在Linux环境下大小写敏感
  • 规范：
    • 数据库名，表名，表别名，字段名，字段别名都小写
    • SQL关键字，函数名，绑定变量都大写

1.3 注释

1
2
3
单行注释: #注释文字
多行注释：-- 注释文字 --
多行注释： /* 注释文字 */

1.2 基本的SELECT语句

1
2
SELECT *
FROM departments;

1.3 列的别名

1
2
SELECT last_name AS name,commission_pct comm
FROM employees;

1.4 去除重复行

1
2
SELECT DISTINCT department_id
FROM employees;

1.5 空值参与运算

MySQL中，空值不等于空字符串，空串的长度为0，空值的长度为空

所有运算符或列值遇到null值，运算的结果都为null

1
2
3
SELECT employee_id,salary,commission_pct,
12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;

1.6 着重号

通过着重号避免与关键字冲突

1
SELECT * FROM `ORDER`;

1.3 显示表结构

1
2
3
DESCRIBE employees;
或 
DESC employees;

1.4 课后练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY
#理解1：计算12月的基本工资
SELECT employee_id,last_name,salary * 12 "ANNUAL SALARY"
FROM employees;

#理解2：计算12月的基本工资和奖金
SELECT employee_id,last_name,salary * 12 * (1 + IFNULL(commission_pct,0)) "ANNUAL SALARY"
FROM employees;


# 2.查询employees表中去除重复的job_id以后的数据
SELECT DISTINCT job_id
FROM employees;

# 3.查询工资大于12000的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary > 12000;

# 4.查询员工号为176的员工的姓名和部门号
SELECT last_name,department_id
FROM employees
WHERE employee_id = 176;

# 5.显示表 departments 的结构，并查询其中的全部数据 
DESCRIBE departments;

SELECT * FROM departments;

2. 运算符

2.1 算术运算符

2.2 比较运算符

2.3 非符号运算符

2.4 逻辑运算符

2.5 位运算符

2.6 运算符优先级

数字越大优先级越高

2.7 课后练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
# 1.选择工资不在5000到12000的员工的姓名和工资

SELECT last_name,salary
FROM employees
#where salary not between 5000 and 12000;
WHERE salary < 5000 OR salary > 12000;

# 2.选择在20或50号部门工作的员工姓名和部门号
SELECT last_name,department_id
FROM employees
# where department_id in (20,50);
WHERE department_id = 20 OR department_id = 50;

# 3.选择公司中没有管理者的员工姓名及job_id

SELECT last_name,job_id,manager_id
FROM employees
WHERE manager_id IS NULL;

SELECT last_name,job_id,manager_id
FROM employees
WHERE manager_id <=> NULL;

# 4.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;


SELECT last_name,salary,commission_pct
FROM employees
WHERE NOT commission_pct <=> NULL;


# 5.选择员工姓名的第三个字母是a的员工姓名

SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';


# 6.选择姓名中有字母a和k的员工姓名

SELECT last_name
FROM employees
WHERE last_name LIKE '%a%k%' OR last_name LIKE '%k%a%';
#where last_name like '%a%' and last_name LIKE '%k%';

# 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息

SELECT first_name,last_name
FROM employees
WHERE first_name LIKE '%e';

SELECT first_name,last_name
FROM employees
WHERE first_name REGEXP 'e$'; # 以e开头的写法：'^e'

# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
SELECT last_name,job_id
FROM employees
#方式1：推荐
WHERE department_id BETWEEN 80 AND 100;
#方式2：推荐，与方式1相同
#where department_id >= 80 and department_id <= 100;
#方式3：仅适用于本题的方式。
#where department_id in (80,90,100);

SELECT * FROM departments;

# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id

SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id IN (100,101,110);

3. 排序与分页

3.1 排序

  • 使用ORDER BY排序
    • ASC（asend）：升序
    • DESC（decend）：降序

3.2 单列排序

1
2
3
4
5
6
7
SELECT last_name,job_id,department_id,hire_date
FROM employees
ORDER BY hire_date;	#默认升序

SELECT last_name,job_id,department_id,hire_date
FROM employees
ORDER BY hire_date DESC; #降序

3.3 多行排序

1
2
3
SELECT last_name,department_id,salary
FROM employees
ORDER BY department_id,salary DESC; #先按department_id升序排，再按salary降序排

3.4 分页

1
2
3
4
5
6
7
8
--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录：
SELECT * FROM 表名 LIMIT 20,10;

使用LIMIT好处：

​ 减少数据表的网络传输量，提升查询效率

4. 多表查询

4.1 内连接

1
2
3
4
5
6
7
8
9
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);

4.2 左外连接

1
2
3
4
5
6
7
8
9
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

4.3 右外连接

1
2
3
4
5
6
7
8
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

4.4 UNION

合并查询结果

1
2
3
4
5
6
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

#UNION返回两个结果的并集，去除重复记录
#UNION ALL返回两个结果的并集，不去重
1
2
3
4
5
6
7
8
9
# 查询部门编号>90或邮箱中包换a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id > 90;

#查询中国用户中男性的信息以及美国用户中男性的信息
SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

4.5 USING

使用USING指定数据表中的同名字段进行等值连接，USING必须配合JOIN一起使用

1
2
3
4
5
6
7
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

4.6 关联条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#把关联条件写在where后面
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;

#把关联条件写在on后面，只能和JOIN一起使用
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;

SELECT last_name,department_name
FROM employees CROSS JOIN departments
ON employees.department_id = departments.department_id;

SELECT last_name,department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;

#把关联字段写在using()中，只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同，而且只能表示=
#查询员工姓名与基本工资
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);

#n张表关联，需要n-1个关联条件
#查询员工姓名，基本工资，部门名称
SELECT last_name,job_title,department_name FROM employees,departments,jobs
WHERE employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;

SELECT last_name,job_title,department_name
FROM employees INNER JOIN departments INNER JOIN jobs
ON employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;

4.7 课后练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
# 1.显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name,e.department_id,d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

# 2.查询90号部门员工的job_id和90号部门的location_id

SELECT e.job_id,d.location_id
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` = 90;


DESC departments;

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name ,e.`commission_pct`, d.department_name , d.location_id , l.city
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE e.`commission_pct` IS NOT NULL; #也应该是35条记录


SELECT *
FROM employees
WHERE commission_pct IS NOT NULL; #35条记录


# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name 

SELECT e.last_name , e.job_id , e.department_id , d.department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE l.`city` = 'Toronto';

#sql92语法：
SELECT e.last_name , e.job_id , e.department_id , d.department_name
FROM employees e,departments d ,locations l
WHERE e.`department_id` = d.`department_id` 
AND d.`location_id` = l.`location_id`
AND l.`city` = 'Toronto';


# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

SELECT d.department_name,l.street_address,e.last_name,e.job_id,e.salary
FROM departments d LEFT JOIN employees e
ON e.`department_id` = d.`department_id`
LEFT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_name` = 'Executive';


DESC departments;

DESC locations;

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees	Emp#	manager	Mgr#
kochhar		101	king	100

SELECT emp.last_name "employees",emp.employee_id "Emp#",mgr.last_name "manager", mgr.employee_id "Mgr#"
FROM employees emp LEFT JOIN employees mgr
ON emp.manager_id = mgr.employee_id;


# 7.查询哪些部门没有员工

SELECT d.department_id
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`department_id` IS NULL;

#本题也可以使用子查询：暂时不讲

# 8. 查询哪个城市没有部门 

SELECT l.location_id,l.city
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL;

SELECT department_id
FROM departments
WHERE department_id IN (1000,1100,1200,1300,1600);


# 9. 查询部门名为 Sales 或 IT 的员工信息

SELECT e.employee_id,e.last_name,e.department_id
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_name` IN ('Sales','IT');

5. 单行函数

  • 操作数据对象
  • 接受参数返回一个结果
  • 只对一行进行变换
  • 每行返回一个结果
  • 可以嵌套
  • 参数可以是一列或一个值

5.1 基本函数

三角函数

5.2 日期函数

获取月份，星期等函数

时间和秒钟转换函数

计算日期和时间函数

日期格式化与解析

5.3 流程控制函数

流程控制函数用于根据不同条件执行不同的处理流程

5.4 加密与解密函数

5.5 其他函数

5.6 课后练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
# 1.显示系统时间(注：日期+时间)
SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP() #大家只需要掌握一个函数就可以了
FROM DUAL;

# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id,last_name,salary,salary * 1.2 "new salary"
FROM employees;


# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,LENGTH(last_name) "name_length"
FROM employees
#order by last_name asc;
ORDER BY name_length ASC;


# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT

SELECT CONCAT(employee_id,',',last_name,',',salary) "OUT_PUT"
FROM employees;


# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序

SELECT employee_id,DATEDIFF(CURDATE(),hire_date)/365 "worked_years",DATEDIFF(CURDATE(),hire_date) "worked_days",
TO_DAYS(CURDATE()) - TO_DAYS(hire_date) "worked_days1"
FROM employees
ORDER BY worked_years DESC;

# 6.查询员工姓名，hire_date , department_id，满足以下条件：
#雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空
SELECT last_name,hire_date,department_id
FROM employees
WHERE department_id IN (80,90,110)
AND commission_pct IS NOT NULL
#and hire_date >= '1997-01-01';  #存在着隐式转换
#and  date_format(hire_date,'%Y-%m-%d') >= '1997-01-01';  # 显式转换操作，格式化：日期---> 字符串
#and  date_format(hire_date,'%Y') >= '1997';   # 显式转换操作，格式化
AND hire_date >= STR_TO_DATE('1997-01-01','%Y-%m-%d');# 显式转换操作，解析：字符串 ----> 日期

# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name,hire_date
FROM employees
WHERE DATEDIFF(CURDATE(),hire_date) >= 10000;


# 8.做一个查询，产生下面的结果
#<last_name> earns <salary> monthly but wants <salary*3> 

SELECT CONCAT(last_name,' earns ',TRUNCATE(salary,0), ' monthly but wants ',TRUNCATE(salary * 3,0)) "Dream Salary"
FROM employees;


# 9.使用case-when，按照下面的条件：
/*job                  grade
AD_PRES              	A
ST_MAN               	B
IT_PROG              	C
SA_REP               	D
ST_CLERK             	E

产生下面的结果:
*/

SELECT last_name "Last_name",job_id "Job_id",CASE job_id WHEN 'AD_PRES' THEN 'A'
							 WHEN 'ST_MAN' THEN 'B'
							 WHEN 'IT_PROG' THEN 'C'
							 WHEN 'SA_REP' THEN 'D'
							 WHEN 'ST_CLERK' THEN 'E'
							 END "Grade"
FROM employees;


SELECT last_name "Last_name",job_id "Job_id",CASE job_id WHEN 'AD_PRES' THEN 'A'
							 WHEN 'ST_MAN' THEN 'B'
							 WHEN 'IT_PROG' THEN 'C'
							 WHEN 'SA_REP' THEN 'D'
							 WHEN 'ST_CLERK' THEN 'E'
							 ELSE "undefined" END "Grade"
FROM employees;

6. 聚合函数

聚合函数作于一组数据，并对一组数据返回一个值

常见的聚合函数：AVG(),SUM(),MAX(),MIN(),COUNT()

6.1 常见的聚合函数

可以对数值型数据使用AVG和SUM函数

1
2
3
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

可以对任意数据类型使用MIN和MAX函数

1
2
SELECT MIN(hire_date),MAX(hire_date)
FROM employees;

COUNT()返回表中的记录总数，适用于*任意数据类型

1
2
3
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

COUNT(expr)返回expr不为空的记录总数

1
2
3
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;

6.2 GROUP BY

使用GROUP BY将表中数据分为若干组

1
2
3
4
5
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

SELECT列表中所有为包含再组函数中的列都应该包含在GROUP BY子句中

包含在GROUP BY中的列不必包含再SELECT列表中

1
2
3
4
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id;
#department_id没有包含再组函数中

多列分组

1
2
3
4
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;
#先按department_id分，再按job_id分

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。 

1
2
3
4
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

6.3 HAVING

过滤分组： HAVING

  • 行已经被分组
  • 使用了聚合函数
  • 满足HAVING子句中条件的分组将被显示
  • HAVING不能单独使用，必须要跟GROUP BY一起使用
1
2
3
4
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

非法使用聚合函数：

  • 不能再WHERE子句中使用聚合函数

WHERE和HAVING对比：

  • WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
  • 如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后接，而 HAVING 是先连接后筛选。

总结：

  • WHERE，先筛选数据再关联效率高，执行效率低，不能使用分组中的计算函数进行筛选
  • HAVING，在最后的结果集中进行筛选，执行效率低，可以使用分组中的计算函数

6.4 SELECT的执行过程

查询的结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

SELECT语句的执行顺序

1
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

eg

1
2
3
4
5
6
7
SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个 虚拟表 ，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

6.5 SQL运行原理

暂略

7. 子查询

实际问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#Main Query:谁的工资比Abel高？
#sub Query:Abel的工资是多少？
#自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE  e1.salary < e2.salary
AND e1.last_name = 'Abel';


#子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
	SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
);

  • 子查询在著查询之前一次执行完成
  • 子查询的结果为著查询使用

7.1 单行子查询

单行比较操作符

eg

1
2
3
4
5
6
7
8
9
#查询工资大于149号员工工资的员工信息
#先查149号工资，再查员工信息
SELECT last_name
FROM employees
WHERE salary > (
	SELECT salary
    FROM employees
    WHERE job_id = 149
);

Having中的子查询

  • 首先执行子查询
  • 再向主查询中的HAVING子句返回结果
1
2
3
4
5
6
7
8
9
#查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary) #部门id和其最低工资
FROM employees
GROUP BY department_id #部门的最低工资，按部门聚合
HAVING MIN(salary) > (
	SELECT MIN(salary) #子查询，50号部门的最低工资
    FROM employees
    WHERE department_id = 50
);

CASE中的子查询

1
2
3
4
5
6
7
8
9
#显式员工的employee_id,last_name和location。
#其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。

SELECT employee_id,last_name,CASE department_id 
WHEN (SELECT department_id FROM departments WHERE location_id = 1800)
THEN 'Canada'
ELSE 'USA'
END "location"
FROM employees;

非法使用子查询

1
2
3
4
5
6
7
8
9
SELECt employee_id,last_name
FROM employees
WHERE salary = (
	SELECT MIN(salary)
    FROM employees
    GROUP BY department_id
)

#多行子查询使用单行比较符

7.2多行子查询

  • 也被称为集合比较子查询
  • 内查询返回多行
  • 使用多行比较操作符

多行比较操作符

IN

1
2
3
4
5
6
7
8
#查询每个部门中拿最低薪水的员工信息
SELECT employee_idmlast_name
FROM employees
WHERE salary IN (
	SELECT MIN(salary)
    FROM employees
    GROUP BY department_id
);

ANY

1
2
3
4
5
6
7
8
9
10
#返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

SELECT employeed_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
	SELECT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
);

ALL

1
2
3
4
5
6
7
8
9
#返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id 以及salary
SELECT employeed_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
	SELECT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
);

eg

1
2
3
4
5
6
7
8
9
10
#查询平均工资最低的部门id

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
	SELECT AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
);

7.3 相关子查询

子查询依赖于外部查询时，如使用了主查询中的列，则每执行一次外部查询子查询都要重新计算

  • 丛主查询中获取候选列
  • 子查询使用主查询的数据
  • 如果满足子查询的条件则返回该行

相关子查询

1
2
3
4
5
6
7
8
9
10
11
#查询员工中工资大于本部门平均公司的员工的last_name,salary和其department_id

#相关子查询
SELECT last_name,salary,department_id
FROM employees outer
WHERE salary > (
	SELECT AVG(salary)
    FROM employees
    WHERE department_id = outer.department_id
);

在FROM中使用子查询

1
2
3
4
5
6
7
#查询员工中工资大于本部门平均公司的员工的last_name,salary和其department_id

#在FROM中使用子查询
SELECT last_name,salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2
WHERE e1.'department_id' = e2.'department_id'
AND e2.dept_avg_sal < e1.'salary';

在GROUP BY中使用子查询

1
2
3
4
5
6
7
8
9
#查询员工的id,salary,按照department_name 排序

SELECT employee_id,salary
FROM empoloyees e
ORDER BY (
	SELECT department_name
    FROM departments d
    WHERE e.'department_id' = d.'department_id'
)

EXISTS与NOT EXISTS关键字

  • 如果子查询中不存在满足条件的行：
    • 条件返回FALSE
    • 继续在子查询中查找
  • 如果在子查询中存在满足条件的行：
    - 不在子查询中继续查找
    - 条件返回TRUE
  • NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE
1
2
3
4
5
6
7
8
9
#查询公司管理者的employee_id,last_name,job_id,department_id

SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS(
	SELECT * 
    FROM employees e2
    WHERE e2.manager_id = e1.employee_id
);
1
2
3
4
5
6
7
8
9
#查询departments表中，不存在于employees表中的部门的department_id和department_name

SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
	SELECT 'X'
    FROM employees
    WHERE department_id = d.department_id
);

相关更新

1
2
3
4
5
6
UPDATE table alias1
SET column = (
	SELECT expression
    FROM table2 alias2
    WHERE alias1.column = alias2.column
);

相关删除

1
2
3
4
5
6
DELETE FROM table1 alias1
WHERE column operator (
	SELECT expression
    FROM table2.alias2
    WHERE alias1.column = alias2.column
);

7.4 课后练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528

# 第09章_子查询的课后练习



#1.查询和Zlotkey相同部门的员工姓名和工资

SELECT last_name,salary
FROM employees
WHERE department_id IN (
			SELECT department_id
			FROM employees
			WHERE last_name = 'Zlotkey'
			);

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。

SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
		SELECT AVG(salary)
		FROM employees
		);

#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary > ALL(
		SELECT salary
		FROM employees
		WHERE job_id = 'SA_MAN'
		);


#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

SELECT employee_id,last_name
FROM employees 
WHERE department_id IN (
			SELECT DISTINCT department_id
			FROM employees
			WHERE last_name LIKE '%u%'
			);


#5.查询在部门的location_id为1700的部门工作的员工的员工号

SELECT employee_id
FROM employees
WHERE department_id IN (
			SELECT department_id
			FROM departments
			WHERE location_id = 1700
			);


#6.查询管理者是King的员工姓名和工资

SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id IN (
			SELECT employee_id
			FROM employees
			WHERE last_name = 'King'
			);



#7.查询工资最低的员工信息: last_name, salary

SELECT last_name,salary
FROM employees
WHERE salary = (
		SELECT MIN(salary)
		FROM employees
		);


#8.查询平均工资最低的部门信息
#方式1：
SELECT *
FROM departments
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) = (
						SELECT MIN(avg_sal)
						FROM (
							SELECT AVG(salary) avg_sal
							FROM employees
							GROUP BY department_id
							) t_dept_avg_sal

						)
			);
#方式2：

SELECT *
FROM departments
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) <= ALL(
						SELECT AVG(salary)
						FROM employees
						GROUP BY department_id
						)
			);

#方式3： LIMIT

SELECT *
FROM departments
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) =(
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY department_id
						ORDER BY avg_sal ASC
						LIMIT 1		
						)
			);

#方式4：

SELECT d.*
FROM departments d,(
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id
		ORDER BY avg_sal ASC
		LIMIT 0,1
		) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.department_id
		
#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
#方式1：
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) = (
						SELECT MIN(avg_sal)
						FROM (
							SELECT AVG(salary) avg_sal
							FROM employees
							GROUP BY department_id
							) t_dept_avg_sal

						)
			);

#方式2：

SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) <= ALL(
						SELECT AVG(salary)
						FROM employees
						GROUP BY department_id
						)
			);

#方式3： LIMIT

SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) =(
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY department_id
						ORDER BY avg_sal ASC
						LIMIT 1		
						)
			);

#方式4：

SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d,(
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id
		ORDER BY avg_sal ASC
		LIMIT 0,1
		) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.department_id

#10.查询平均工资最高的 job 信息

#方式1：
SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) = (
					SELECT MAX(avg_sal)
					FROM (
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY job_id
						) t_job_avg_sal
					)
		);

#方式2：
SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) >= ALL(
				     SELECT AVG(salary) 
				     FROM employees
				     GROUP BY job_id
				     )
		);

#方式3：
SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) =(
				     SELECT AVG(salary) avg_sal
				     FROM employees
				     GROUP BY job_id
				     ORDER BY avg_sal DESC
				     LIMIT 0,1
				     )
		);

#方式4：
SELECT j.*
FROM jobs j,(
		SELECT job_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY job_id
		ORDER BY avg_sal DESC
		LIMIT 0,1		
		) t_job_avg_sal
WHERE j.job_id = t_job_avg_sal.job_id
		
#11.查询平均工资高于公司平均工资的部门有哪些?

SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (
			SELECT AVG(salary)
			FROM employees
			);


#12.查询出公司中所有 manager 的详细信息

#方式1：自连接  xxx worked for yyy
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

#方式2：子查询

SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
			SELECT DISTINCT manager_id
			FROM employees
			);

#方式3：使用EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
	       SELECT *
	       FROM employees e2
	       WHERE e1.`employee_id` = e2.`manager_id`
	     );

	
#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?

#方式1：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) = (
						SELECT MIN(max_sal)
						FROM (
							SELECT MAX(salary) max_sal
							FROM employees
							GROUP BY department_id
							) t_dept_max_sal
						)
			);

SELECT *
FROM employees
WHERE department_id = 10;

#方式2：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) <= ALL (
						SELECT MAX(salary)
						FROM employees
						GROUP BY department_id
						)
			);

#方式3：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) = (
						SELECT MAX(salary) max_sal
						FROM employees
						GROUP BY department_id
						ORDER BY max_sal ASC
						LIMIT 0,1
						)
			);
			
#方式4：
SELECT MIN(salary)
FROM employees e,(
		SELECT department_id,MAX(salary) max_sal
		FROM employees
		GROUP BY department_id
		ORDER BY max_sal ASC
		LIMIT 0,1
		) t_dept_max_sal
WHERE e.department_id = t_dept_max_sal.department_id


#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
#方式1：
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id = ANY (
			SELECT DISTINCT manager_id
			FROM employees
			WHERE department_id = (
						SELECT department_id
						FROM employees
						GROUP BY department_id
						HAVING AVG(salary) = (
									SELECT MAX(avg_sal)
									FROM (
										SELECT AVG(salary) avg_sal
										FROM employees
										GROUP BY department_id
										) t_dept_avg_sal
									)
						)
			);

#方式2：
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id = ANY (
			SELECT DISTINCT manager_id
			FROM employees
			WHERE department_id = (
						SELECT department_id
						FROM employees
						GROUP BY department_id
						HAVING AVG(salary) >= ALL (
								SELECT AVG(salary) avg_sal
								FROM employees
								GROUP BY department_id
								)
						)
			);

#方式3：
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
			SELECT DISTINCT manager_id
			FROM employees e,(
					SELECT department_id,AVG(salary) avg_sal
					FROM employees
					GROUP BY department_id
					ORDER BY avg_sal DESC
					LIMIT 0,1
					) t_dept_avg_sal
			WHERE e.`department_id` = t_dept_avg_sal.department_id
			);


#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
#方式1：
SELECT department_id
FROM departments
WHERE department_id NOT IN (
			SELECT DISTINCT department_id
			FROM employees
			WHERE job_id = 'ST_CLERK'
			);

#方式2：
SELECT department_id
FROM departments d
WHERE NOT EXISTS (
		SELECT *
		FROM employees e
		WHERE d.`department_id` = e.`department_id`
		AND e.`job_id` = 'ST_CLERK'
		);



#16. 选择所有没有管理者的员工的last_name

SELECT last_name
FROM employees emp
WHERE NOT EXISTS (
		SELECT *
		FROM employees mgr
		WHERE emp.`manager_id` = mgr.`employee_id`
		);

#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
#方式1：
SELECT employee_id,last_name,hire_date,salary
FROM employees
WHERE manager_id IN (
		SELECT employee_id
		FROM employees
		WHERE last_name = 'De Haan'
		);

#方式2：
SELECT employee_id,last_name,hire_date,salary
FROM employees e1
WHERE EXISTS (
		SELECT *
		FROM employees e2
		WHERE e1.`manager_id` = e2.`employee_id`
		AND e2.last_name = 'De Haan'
		); 


#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）

#方式1：使用相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
		SELECT AVG(salary)
		FROM employees e2
		WHERE department_id = e1.`department_id`
		);

#方式2：在FROM中声明子查询
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal


#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）

SELECT department_name
FROM departments d
WHERE 5 < (
	   SELECT COUNT(*)
	   FROM employees e
	   WHERE d.department_id = e.`department_id`
	  );


#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）

SELECT * FROM locations;

SELECT country_id
FROM locations l
WHERE 2 < (
	   SELECT COUNT(*)
	   FROM departments d
	   WHERE l.`location_id` = d.`location_id`
	 );

/* 
子查询的编写技巧（或步骤）：① 从里往外写  ② 从外往里写

如何选择？
① 如果子查询相对较简单，建议从外往里写。一旦子查询结构较复杂，则建议从里往外写
② 如果是相关子查询的话，通常都是从外往里写。

*/

8. 创建和管理表

8.1 MySQL中的数据类型

常用数据类型

8.2 创建和管理数据库

创建数据库

1
2
3
4
5
6
7
8
#1.创建数据库
CREATE DATABASE 数据库名;

#2.创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;

#3.判断数据库是否存在再创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;

查看数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#查看当前所有数据库
SHOW DATABASES;

#查看当前正在使用的数据库
SELECT DATABASE();

#查看指定库下所有的表
SHOW TABLES FROM 数据库名;

#查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;

#使用，切换数据库
USE 数据库名;

修改数据库

1
2
3
4
5
6
#修改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集;

#删除指定的数据库
DROP DATABASE 数据库名;
DROP DATABASE IF EXISTS;

8.3 创建表

直接创建

1
2
3
4
5
CREATE TABLE dept(
    deptno INT(2) PRIMARY KEY AUTO_INCREMENT,
    dname VARCHAR(14),
    loc VARCHAR(13)
)；

使用AS subquery利用查询创建

1
2
3
4
5
6
7
CREATE TABLE emp1 FROM SELECT * FROM employees;

CREATE TABLE dept80
AS
SELECT employee_id,last_name,salary*12 ANNSAL，hire_date
FROM employees
WHERE department_id = 80;

查看表结构

1
2
3
4
5
SHOW CREATE TABLE 表名\G

DESCRIBE 表名;

DESC 表名;

8.4 修改表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#添加一列
ALTER TABLE dept80
ADD job_id varchar(15);

#修改一列
ALTER TABLE dept80
MODIFY last_name VARCHAR(30);

ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;

#重命名一列
ALTER TABLE dept80
CHANGE department_name new_dept_name VARCHAR(15);

#删除一列
ALTER TABLE dept 80
DROP salary;

8.5删除和清空表

1
2
3
4
5
6
7
#删除
DELETE FROM dept80;
DROP TABLE IF EXISTS dept80;;
DROP TABLE dept80;

#清空
TRUNCATE TABLE dept80;

DROP TABLE不能回滚

TRUNCATE清空所有数据并释放存储空间

8.6 MySQL8新特性-DDL的原子化

在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即 DDL操作要么成功要么回滚 。DDL操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log（该表是隐藏的表，通过show tables无法看到）
中，用于回滚操作。通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中。 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE DATABASE mytest;
USE mytest;
CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(255)
);

#5.7
DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2
mysql> SHOW TABLES;
Empty set (0.00 sec

#8.0
mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2
mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| book1 |
+------------------+
1 row in set (0.00 sec)

9. 增删改

9.1 插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#1.为表的所有字段按默认顺序插入数据
INSERT INTO departments
VALUES (70,'Pub',100,1000);

#2.为表的指定字段插入数据
INSERT INTO departments(department_id,department_name)
VALUES(88,'IT');

#3.插入多条记录
INSERT INTO emp(emp_id,emp_name)
VALUES
(101,'rio'),
(102,'saito'),
(103,'mai');

#4.将查询结果插入到表中
INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;

INSERT INTO sales_reps(id,name,salary,commission_pct)
SELECT employee_id,last_name,salary,commission_pct
FROM employees
WHERE job_id LIKE '%REP%'

9.2 更新数据

1
2
3
4
5
6
7
8
9
10
11
12
UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

#1.使用WHERE子句指定更新
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

#2.省略WHERE指定，全更新
UPDATE employees
SET department_id = 110;

9.3 删除数据

1
2
3
4
5
6
7
8
DELETE FROM table_name [WHERE <condition>];

#1.使用WHERE子句指定删除
DELETE FROM departments
WHERE department_name = 'Finance';

#2.省略WHERE，删除全部数据
DELETE FROM copy_emp;

9.4 MySQL8新特性-计算列

在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。

1
2
3
4
5
6
7
8
9
10
CREATE TABLE tb1(
id INT,
a INT,
b INT,
C INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

INSERT INTO tb1(a,b) VALUES (100,200);#a=100,b=200,c=300

UPDATE tb1 SET a = 500;#a=500,b=200,c=700

9.5 课后练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
#1. 创建数据库dbtest11
CREATE DATABASE IF NOT EXISTS dbtest11 CHARACTER SET 'utf8';

#2. 运行以下脚本创建表my_employees
USE dbtest11;

CREATE TABLE my_employees(
	id INT(10),
	first_name VARCHAR(10),
	last_name VARCHAR(10),
	userid VARCHAR(10),
	salary DOUBLE(10,2)
);

CREATE TABLE users(
	id INT,
	userid VARCHAR(10),
	department_id INT
);
#3.显示表my_employees的结构

DESC my_employees;

DESC users;

#4.向my_employees表中插入下列数据
ID	FIRST_NAME	LAST_NAME	USERID		SALARY
1	patel		Ralph		Rpatel		895
2	Dancs		Betty		Bdancs		860
3	Biri		Ben		Bbiri		1100
4	Newman		Chad		Cnewman		750
5	Ropeburn	Audrey		Aropebur	1550

INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895);

INSERT INTO my_employees VALUES
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);

SELECT * FROM my_employees;

DELETE FROM my_employees;

#方式2：
INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel',895 UNION ALL
SELECT 2,'Dancs','Betty','Bdancs',860 UNION ALL
SELECT 3,'Biri','Ben','Bbiri',1100 UNION ALL
SELECT 4,'Newman','Chad','Cnewman',750 UNION ALL
SELECT 5,'Ropeburn','Audrey','Aropebur',1550;
			
#5.向users表中插入数据
1	Rpatel		10
2	Bdancs		10
3	Bbiri		20
4	Cnewman		30
5	Aropebur	40
INSERT INTO users VALUES
(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20),
(4,'Cnewman',30),
(5,'Aropebur',40)

SELECT * FROM users;



#6. 将3号员工的last_name修改为“drelxer”
UPDATE my_employees
SET last_name = 'drelxer'
WHERE id = 3;

#7. 将所有工资少于900的员工的工资修改为1000
UPDATE my_employees
SET salary = 1000
WHERE salary < 900;

#8. 将userid为Bbiri的users表和my_employees表的记录全部删除

#方式1：
DELETE FROM my_employees
WHERE userid = 'Bbiri';

DELETE FROM users
WHERE userid = 'Bbiri';

#方式2：

DELETE m,u
FROM my_employees m
JOIN users u
ON m.userid = u.userid
WHERE m.userid = 'Bbiri';

SELECT * FROM my_employees;
SELECT * FROM users;

#9. 删除my_employees、users表所有数据
DELETE FROM my_employees;
DELETE FROM users;

#10. 检查所作的修正
SELECT * FROM my_employees;
SELECT * FROM users;

#11. 清空表my_employees
TRUNCATE TABLE my_employees;
##########################################
#练习2：
# 1. 使用现有数据库dbtest11
USE dbtest11;

# 2. 创建表格pet
CREATE TABLE pet(
NAME VARCHAR(20),
OWNER VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth YEAR,
death YEAR
);

DESC pet;

# 3. 添加记录
INSERT INTO pet VALUES
('Fluffy','harold','Cat','f','2003','2010'),
('Claws','gwen','Cat','m','2004',NULL),
('Buffy',NULL,'Dog','f','2009',NULL),
('Fang','benny','Dog','m','2000',NULL),
('bowser','diane','Dog','m','2003','2009'),
('Chirpy',NULL,'Bird','f','2008',NULL);

SELECT *
FROM pet;

# 4. 添加字段:主人的生日owner_birth DATE类型。
ALTER TABLE pet
ADD owner_birth DATE;

# 5. 将名称为Claws的猫的主人改为kevin
UPDATE pet
SET OWNER = 'kevin'
WHERE NAME = 'Claws' AND species = 'Cat';

# 6. 将没有死的狗的主人改为duck
UPDATE pet
SET OWNER = 'duck'
WHERE death IS NULL AND species = 'Dog';

# 7. 查询没有主人的宠物的名字；
SELECT NAME
FROM pet
WHERE OWNER IS NULL;

# 8. 查询已经死了的cat的姓名，主人，以及去世时间；
SELECT NAME,OWNER,death
FROM pet
WHERE death IS NOT NULL;

# 9. 删除已经死亡的狗
DELETE FROM pet
WHERE death IS NOT NULL 
AND species = 'Dog';

# 10. 查询所有宠物信息
SELECT * 
FROM pet;

##################################
#练习3：
# 1. 使用已有的数据库dbtest11
USE dbtest11;
# 2. 创建表employee，并添加记录
CREATE TABLE employee(
id INT,
NAME VARCHAR(15),
sex CHAR(1),
tel VARCHAR(25),
addr VARCHAR(35),
salary DOUBLE(10,2)

);

INSERT INTO employee VALUES
(10001,'张一一','男','13456789000','山东青岛',1001.58),
(10002,'刘小红','女','13454319000','河北保定',1201.21),
(10003,'李四','男','0751-1234567','广东佛山',1004.11),
(10004,'刘小强','男','0755-5555555','广东深圳',1501.23),
(10005,'王艳','男','020-1232133','广东广州',1405.16);

SELECT * FROM employee;

# 3. 查询出薪资在1200~1300之间的员工信息。
SELECT *
FROM employee
WHERE salary BETWEEN 1200 AND 1300;

# 4. 查询出姓“刘”的员工的工号，姓名，家庭住址。
SELECT id,NAME,addr
FROM employee
WHERE NAME LIKE '刘%';

# 5. 将“李四”的家庭住址改为“广东韶关”
UPDATE employee
SET addr = '广东韶关'
WHERE NAME = '李四';

# 6. 查询出名字中带“小”的员工
SELECT *
FROM employee
WHERE NAME LIKE '%小%';

10. 约束

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中
存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：

  • 实体完整性（Entity Integrity） ：例如，同一个表中，不能存在两条完全相同无法区分的记录
  • 域完整性（Domain Integrity） ：例如：年龄范围0-120，性别范围“男/女”
  • 引用完整性（Referential Integrity） ：例如：员工所在部门，在部门表中要能找到这个部门
  • 用户自定义完整性（User-defined Integrity） ：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

10.1 非空约束

  • 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型
  • 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
  • 一个表可以有很多列都分别限定了非空空字符串’’不等于NULL，0也不等于NULL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#添加非空约束
CREATE TABLE student(
sid int,
sname varchar(20) not null,
tel char(11) ,
cardid char(18) not null
);

ALTER TABLE emp
MODIFY sex VARCHAR(30) NOT NULL;

#删除非空约束
ALTER TABLE emp
MODIFY sex VARCHAR(30) NULL;

10.2 唯一性约束

  • 同一个表可以有多个唯一约束。
  • 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
  • 唯一性约束允许列值为空。
  • 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
    MySQL会给唯一约束的列上默认创建一个唯一索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#添加唯一性约束
create table student(
sid int,
sname varchar(20),
tel char(11) unique,
cardid char(18) unique key
);

CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
-- 使用表级约束语法
CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);

#修改唯一性约束
ALTER TABLE USER
MODIFY NAME VARCHAR(20) UNIQUE;

ALTER TABLE USER
ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);

#复合唯一性约束
create table student_course(
id int,
sid int,
cid int,
score int,
unique key(sid,cid) #复合唯一
);

#删除唯一性约束
ALTER TABLE USER
DROP INDEX uk_name_pwd;

10.3 PRIMARY KEY约束

  • 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
  • 主键约束对应着表中的一列或者多列（复合主键）
  • 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
  • MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
  • 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
  • 需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
create table temp(
id int primary key,
name varchar(20)
);

#列级约束
CREATE TABLE emp4(
id INT PRIMARY KEY AUTO_INCREMENT ,
NAME VARCHAR(20)
);

#表级约束
CREATE TABLE emp5(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

#添加主键约束
ALTER TABLE student ADD PRIMARY KEY (sid);
ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd);#复合主键

#删除主键约束
ALTER TABLE student DROP PRIMARY KEY;

复合主键？因为单个主键可能会存在重名等情况，所以采用复合主键保证唯一性。

10.4 自增列：AUTO_INCREMENT

  • 一个表最多只能有一个自增长列
  • 当需要产生唯一标识符或顺序值时，可设置自增长
  • 自增长列约束的列必须是键列（主键列，唯一键列）
  • 自增约束的列的数据类型必须是整数类型
  • 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。
1
2
3
4
5
6
7
8
9
10
11
#创建自增约束
create table employee(
eid int primary key auto_increment,
ename varchar(20)
);

#添加自增约束
alter table employee modify eid int auto_increment;

#删除自增约束
alter table employee modify eid int;

10.5 MySQL8新特性-自增变量的持久化

MySQL 8.0将自增主键的计数器持久化到 重做日志 中。每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。

10.6 FOREIGN KEY约束

主表和从表

主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表
例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。
例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是
主表，选课表是从表。

特点

（1）从表的外键列，必须引用/参考主表的主键或唯一约束的列
为什么？因为被依赖/被参考的值必须是唯一的
（2）在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如student_ibfk_1;），也可以指定外键约束名。
（3）创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
（4）删表时，先删从表（或先删除外键约束），再删除主表
（5）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
（6）在“从表”中指定外键约束，并且一个表可以建立多个外键约束
（7）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can’t create table’database.tablename’(errno: 150)”。
例如：都是表示部门编号，都是int类型。
（8）当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高）
（9）删除外键约束后，必须 手动 删除对应的索引 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#添加外键约束
create table dept( #主表
did int primary key, #部门编号
dname varchar(50) #部门名称
);

create table emp(#从表
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) #在从表中指定外键约束
#emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
);

#添加外键约束
create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int #员工所在的部门
);#这两个表创建时，没有指定外键的话，那么创建顺序是随意

alter table emp add foreign key (deptid) references dept(did);

约束等级

  • Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
  • Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子
    表的外键列不能为not null
  • No action方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
  • Restrict方式 ：同no action， 都是立即检查外键约束
  • Set default方式 （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别
1
2
3
4
5
6
7
8
9
10
11
create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) on update cascade on delete set null
#把修改操作设置为级联修改等级，把删除操作设置为set null等级
);

10.7 CHECK约束

检查某个字段的值是否符号xx要求，一般指的是值的范围

5.7不支持check

1
2
3
4
5
6
CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 20),
PRIMARY KEY(id)
);

10.8 DEFAULT约束

给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。 

1
2
3
4
5
6
create table employee(
eid int primary key,
ename varchar(20) not null,
gender char default '男',
tel char(11) not null default '' #默认是空字符串
);

11. 视图

  • 视图是一种 虚拟表 ，本身是 不具有数据 的，占用很少的内存空间，它是 SQL 中的一个重要概念。
  • 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
  • 视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。
  • 视图，是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便。

11.1 创建视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#创建普通视图
CREATE VIEW empvu80
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

#创建多表联合视图
CREATE VIEW empview
AS
SELECT employee_id emp_id,last_name NAME,department_name
FROM e.department_id = d.department_id

CREATE VIEW emp_dept
AS
SELECT ename,dname
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did;

CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS
SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

#利用视图格式化数据
CREATE VIEW emp_depart
AS
SELECT CONCAT(last_name,'(',department_name,')') AS emp_dept
FROM employees e JOIN department d
WHERE e.department_id = d.department_id

11.2 更新视图

MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。

要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。另外当视图定义出现如
下情况时，视图不支持更新操作：

  • 在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
  • 视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
  • 在定义视图的SELECT语句中使用了 JOIN联合查询 ，视图将不支持INSERT和DELETE操作；
  • 在定义视图的SELECT语句后的字段列表中使用了数学表达式或子查询，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值；
  • 在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING 、UNION 等，视图将不支持INSERT、UPDATE、DELETE；
  • 在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持INSERT、UPDATE、DELETE；
  • 视图定义基于一个不可更新视图；
  • 常量视图。

11.3 修改，删除视图

修改视图

1.使用**CREATE OR REPLACE VIEW **子句修改视图 

1
2
3
4
5
6
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

2.ALTER VIEW

1
2
3
ALTER VIEW #视图名称
AS
#查询语句

删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。 

1
DROP VIEW IF EXISTS 视图名称;

11.4 视图总结

  • 操作简单
  • 减少数据冗余
  • 数据安全
  • 用户不需要查询数据表，可以直接通过视图获取数据表中的信息。
  • 适应灵活多变的需求
  • 能够分解复杂的查询逻辑

如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂， 可读性不好 ，容易变成系统的潜在隐患。