MySQL笔记

36105 字约 120 分钟

2025-01-23

一、数据库概述

1. 数据库与数据库管理系统

数据库管理系统(DBMS)可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体用户的数据。

MySQL是数据库管理系统，而不是数据库本身。

2. RDBMS 与非RDBMS

2.1 关系型数据库(RDBMS)

关系型数据库模型是把复杂的数据结构归结为简单的二元关系 (即二维表格形式)。
关系型数据库以行(row) 和列(column) 的形式存储数据，以便于用户理解。这一系列行和列被称为表(table)，一组表组成了一个库(database)。
SQL 就是关系型数据库的查询语言。
表和表之间的数据记录有关系(relationship)，现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。关系型数据库，就是建立在关系模型基础上的数据库。

2.2 非关系型数据库

非关系型数据库，可看成传统关系型数据库的功能阉割版本 ，基于键值对存储数据，不需要经过SQL层的解析， 性能非常高 。同时，通过减少不常用的功能，进一步提高性能。

3. 关系型数据库设计规则

关系型数据库的典型数据结构就是数据表，这些数据表的组成都是结构化的(Structured)。
将数据放到表中，再把表放到库中。
一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。

3.1 表、记录、字段

E-R(entity-relationship，实体-联系)模型是概念模型，其有三个主要概念是： 实体集、属性、联系 。一个实体集(class)对应于数据库中的一个表(table)，一个实体(instance)则对应于数据库表中的一行(row)，也称为一条记录(record)。一个属性(attribute)对应于数据库表中的一列(column)，也称为一个字段(field)。

ORM思想 (Object Relational Mapping)体现：
数据库中的一个表 <---> Java中的一个类
表中的一条数据 <---> 类中的一个对象(或实体)
表中的一个列 <----> 类中的一个字段、属性
联系是指不同实体集之间的关联关系，由表的关联关系体现。

3.2 表的关联关系

3.2.1 一对一

A表中的一条记录对应着B表中的一条记录，同时，B表中的一条记录也只对应着A表中的一条记录。

设计学生表：学号，姓名、手机号、班级、系别、身份证号码、家庭住址、籍贯、紧急联系人
- 把学生表拆成两个表，两个表是一一对应的关系（通过唯一的字段（学号）来连接两张表）
- 基础信息表（常用信息）：学号、姓名、手机号、班级、系别
- 档案信息表（不常用信息）：学号、身份证号码、家庭住址、籍贯、紧急联系人
建表原则：
- 外键唯一：主表的主键和从表的外键（唯一)，形成主外键关系，外键唯一。
- 外键是主键：主表的主键和从表的主键，形成主外键关系。

3.2.2 一对多

A表中的一条记录对应着B表中的多条记录，但是，B表中的一条记录只对应着A表中的一条记录。

部门表和员工表（一个员工只有一个部门，一个部门有多个员工）
- 员工表：员工编号、姓名、所属部门
- 部门表：部门编号、名称、简介
建表原则：在从表(员工表)创建一个字段，字段作为外键指向主表(部门表)的主键。
一对多关系中，一的那方看成主表，多的那方看为从表。

3.2.3 多对多

A表中的一条记录对应着B表中的多条记录，而且，B表中的一条记录也对应着A表中的多条记录。

要表示多对多关系，必须创建第三个表，该表通常称为联接表，它将多对多关系划分为两个一对多关系。将这两个表的主键都插入到第三个表中。

学生-课程
- 学生信息表：一行代表一个学生的信息(学号、姓名、手机号码、班级...）
- 课程信息表：一行代表一个课程的信息（课程编号、授课老师、简介...)
- 选课信息表：一个学生可以选多门课，一门课可以被多个学生选择。
```
选课信息表：
        学号    课程编号
        1		1001
        2		1001
        1		1002
```
  选课信息表是从表，学生信息表和课程信息表都是主表。

二、基本的SELECT语句

1. SQL语言的分类

SQL语言在功能上主要分为如下3大类：

DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。

主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。
DDL的操作一旦执行，就不可回滚，DDL操作自动会执行COMMIT,SET autocommit = FALSE也会自动提交。

DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。

主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。
因为查询语句使用的非常的频繁，所以很多人把查询语句单拎出来一类：DQL（数据查询语言）。
默认情况，DML语句一旦执行，也是不可回滚的。但是，如果在执行DML之前，执行了SET autocommit = FALSE，则执行的DML操作就可以实现回滚。

DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。

主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。
还有单独将 COMMIT 、 ROLLBACK 取出来称为TCL（Transaction Control Language，事务控制语
言）。

2. SQL语言的规则和规范

2.1 规则

每条命令以 ; 或 \g 或 \G 结束。
\g 或 \G 都是cmd命令行中提交SQL语句的标志。没有必要使用，使用;就行。
字符串型和日期时间类型的数据可以使用单引号（' '）表示。
列的别名，尽量使用双引号（" "），而且不建议省略 AS。
数据库名、表名、字段名只能包含 A–Z、a–z、0–9、 _。
保持字段名和类型的一致性。对于不同表中同名的字段，其数据类型也该相同。

2.2 规范

MySQL 在Windows环境下是大小写不敏感的。

MySQL在Linux环境下是大小写敏感的。

推荐采用统一的书写规范：

数据库名、表名、表别名、字段名、字段别名等都小写。

SQL 关键字、函数名、绑定变量等都大写。

2.3 注释

单行注释：#注释文字(MySQL特有的方式)
单行注释：-- 注释文字(--后面必须包含一个空格。)
多行注释：/* 注释文字 */ 不能嵌套

2.4 命名规则

3. SELECT

3.1 SELECT ... FROM

SELECT 字段1,字段2 FROM 表名;：从某个表查询所有记录的某些字段。

SELECT * FROM 表名;：从某个表查询所有记录的所有字段。

除非需要使用表中所有的字段数据，最好不要使用通配符*，因为*会降低查找效率。

3.2 别名

3.2.1 字段别名

SELECT 字段名1 AS "别名1", 字段名2 AS "别名2" FROM 表名;：给结果集中的字段名取别名。

AS （alias，别名的缩写）可以省略；别名中没有空格或特殊字符时，双引号也可以省略。

3.2.2 表的别名

表也可以取别名。SELECT s.name, s.age FROM student AS s，表的别名为s。

SELECT * FROM employees e;

这里的通配符也可以写成e.*，即指定字段来自哪个表。

3.3 去除重复行DISTINCT

SELECT DISTINCT 字段名1 FROM 表名;：去除重复行，相同结果只保留一个。

DISTINCT 是对后面所有字段的组合进行去重。字段1相同，字段2不同的记录会被保留。

3.4 空值（NULL）参与运算

一般情况下，运算中有NULL，则运算的结果都为NULL。

在 MySQL 里面，空值不等于''、0、'NULL'。

IFNULL(字段名,0)：字段的值为NULL则看为0。
有NULL参与的逻辑运算的结果不一定为NULL。

3.5 反引号（``）

如果字段名、表名和关键字相同，使用一对``（着重号）表明此处是字段名或是表名。

SELECT * FROM `ORDER`;

3.6 查询常数

SELECT 查询还可以对常数进行查询。即在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。

SELECT '尚硅谷' as corporation, last_name FROM employees;
一般来说我们只会从表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，并且用常数列作为这个表的标记，这个时候就需要用到查询常数。
如果我们想查询员工表中的员工编号、员工的姓，并且在查询的结果前面加上公司名（比如说公司名就叫尚硅谷），这个时候我们就可以使用查询常数的方式来完成。

3.7 显示表的结构

DESCRIBE 表名;：显示表中字段的相关信息，如类型，是否允许为空。

3.8 虚表

SELECT 1 FROM DUAL;：DUAL就是虚表。

在没有表的情况下指定一个虚拟的表名，用来执行SELECT...FROM语句。
MySQL 没有完全遵循SELECT...FROM语句标准，SELECT 1;也会执行。

3.9 WHERE子句

WHERE子句用于提取那些满足指定条件的记录。

SELECT * FROM employees WHERE department_id>90 AND department_id<100;

可以在WHERE子句使用 AND 或者 OR 指定一个或多个条件。
WHERE子句的结果为0或NULL时，将没有记录返回（这个将在《MySQL学习2》的3.2节详细说明）。

三、运算符

1. 算术运算符

加减：
在MySQL中加号只表示数值相加。如果遇到字符串，先尝试转成数值，如果转失败，就按0计算。
比如SELECT 100 + '1' FROM DUAL;的结果为101， SELECT 100 + 'A' FROM DUAL;的结果为100。
除法：
/ 的结果无论是否为整数，都会保留小数点后4位。SELECT 12 / 3 FROM DUAL;的结果为4.0000。
DIV 的结果只取整数部分，不是四舍五入。
在除法运算和求余运算中，如果除数为0，将是非法除数，返回结果为NULL。
求余：
MySQL取余运算和Java一样，商都向0靠近，余数可以为负。

2. 比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。

比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。

2.1 常见的比较运算符

等号：
如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
等号两边都是字符串，则比较字符串对应位置的字符是否相等。
安全等于：
可以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1。只有一个操作数为NULL时，其返回值为0。
不等于：
两种写法都行，不能对NULL进行判断。

2.2 特殊的比较运算符

2.2.1 BETWEEN 下界 AND 上界

SELECT last_name, salary FROM employees WHERE salary NOT BETWEEN 2500 AND 3500;

对于数值，是闭区间，[下界，上界]。

2.2.2 IN

IN运算符用于判断给定的值是否在列表中，如果在则返回1，否则返回0。如果字段的值为NULL，则结果为NULL。

列表的写法像Python中的元组。

SELECT manager_id FROM employees WHERE manager_id IN (100,101,201);

2.2.3 NOT IN

NOT IN运算符用于判断给定的值是否在列表中，如果不在则返回1，否则返回0。如果字段的值为NULL，则结果为NULL。

2.2.4 IS NULL

判断给定的值是否为NULL，如果为NULL则返回1，否则返回0。

SELECT NULL IS NULL;

2.2.5 IS NOT NULL

判断给定的值是否为NULL，如果不是NULL则返回1，否则返回0。

SELECT 'a' IS NOT NULL;

2.2.6 LIKE

LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果字段的值或匹配条件为NULL，则返回结果为NULL。

expr LIKE 匹配条件

通配符：

%：匹配0个或多个字符。
_：只能匹配一个字符。

转义字符

通常是\，也可以自己指定。
如果使用\表示转义，可以省略ESCAPE。如果不是\，则要加上ESCAPE。
SELECT job_id FROM jobs WHERE job_id LIKE 'IT$_%' ESCAPE '$';

SELECT last_name FROM employees WHERE last_name LIKE '_o%';：匹配中第二个字母为o的last_name。

2.2.7 REGEXP 运算符（Regular Expression）

REGEXP运算符用来匹配字符串，语法格式为：expr REGEXP 匹配条件如果expr满足匹配条件，返回1。expr或匹配条件有一个为NULL，则返回NULL。

SELECT 'beijing' REGEXP 'jing';

通配符：

^：匹配以该字符后面的字符开头的字符串。以a开头，'^a'
$：匹配以该字符前面的字符结尾的字符串。以a结尾，'a$'
.：匹配任何一个单字符。
[]：匹配在方括号内的任何一个字符。
[a-z]：匹配任何一个小写字母。当备选字符连续时，可用 - 表示范围的区间。
[0-9]：匹配任何一个数字。
*：匹配零个或多个在它前面的字符。
例如，x*匹配任何数量的 x，[0-9]*匹配任何数量的数字。

3. 逻辑运算符

OR可以和AND一起使用。
由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。（先与后或）

3.1 逻辑非（NOT或!）

逻辑非运算符表示当给定的值为0时返回1；当给定的值为非0值时返回0；当给定的值为NULL时，返回NULL。

3.2 逻辑与（AND 或 &&）

对于“逻辑与”，操作数有0，结果则为0。无0后，有NULL则NULL。只有1，则为1。

0>NULL>1，谁先出现就是谁。说明一下，这个优先级是仅通过一个操作数是否能确定结果的值得到的，对于0，另一个操作数无论为什么都为0（已确定）。对于1，还需要借助另一个操作数才能确定结果的值。而在“逻辑与”和“逻辑或”中，NULL的优先级都是处于中间地位。
这里的1也可以替换为非0值。

3.3 逻辑或（OR 或 ||）

对于“逻辑或”，操作数有1，结果则为1。无1后，有NULL则NULL。只有0，则为0。

“逻辑或”的优先级恰好和“逻辑与”的优先级相反。1>NULL>0，谁先出现就是谁。

3.4 逻辑异或（XOR）

对于“逻辑异或”，操作数有NULL，结果则为NULL。

无NULL后，一个操作数为1，另一个操作数为0，才为1，其余情况都为0。

对于“逻辑异或”，NULL的优先级最大。

4. 位运算（不常用）

和Java中的位运算类似。

四、排序与分页

如果没有使用排序操作，默认情况下查询返回的数据是按照添加的顺序显示的。

1. ORDER BY排序

1.1 排序规则

使用ORDER BY排序

ASC: 升序。（不写的话，默认升序）

DESC:降序。

例子：SELECT last_name, job_id FROM employees ORDER BY department_id DESC;

注意事项：

字段的别名可以在ORDER BY中使用，不能在WHERE中使用。
这和SQL语句执行顺序有关。SQL先执行FROM、WHERE，后执行SELECT，所以不认识在SELECT中定义的别名。但ORDER BY的执行顺序在SELECT之后，所以可以使用别名代替字段指定排序规则。
排序的字段不必是SELECT中查询的字段。
WHERE需要声明在FROM后面，ORDER BY之前。
可以在ORDER BY后定义多个排序规则。
SELECT last_name, job_id FROM employees ORDER BY department_id DESC,job_id;：先按department_id降序排列，若有department_id相同的记录，再按job_id升序排列。
排序的字段如果为NULL，则排在最上面（升序情况）。

2. 分页查询

场景需求：每页显示10条记录，查询第3页的数据。

SELECT 字段名 FROM 表名 LIMIT [位置偏移量,] 条数

第一条记录偏移量为0。
LIMIT子句必须放在整个SELECT语句的最后！
也可以使用LIMIT 3 OFFSET 4;，意思是获取从第5条记录开始后面的3条记录，和LIMIT 4,3;返回的结果相同。

-- 前10条记录：
SELECT * FROM 表名 LIMIT 0,10; 
-- 偏移量为0时，可以省略不写，等价为下一行的SQL语句
SELECT * FROM 表名 LIMIT 10;

五、多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段。

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments;
-- 如果查询缺少连接条件，结果会是两个表的笛卡尔积，总记录条数为employees记录条数 * departments记录条数。

-- 正确写法
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id; -- 连接条件

-- 如果SELECT中出现了多个表中都存在的字段，则必须指明此字段所在的表。从SQL优化的角度，建议字段前都加上其所在的表名。
SELECT e.column1, table2.column2
FROM table1 e, table2
WHERE e.column1 = table2.column1; #连接条件

可以在FROM中给表取别名，但是取别名后，在这个SQL语句只能使用此表的别名，不能使用原表名。

1. 多表查询的分类

1.1 等值连接 vs 非等值连接

SELECT e.department_id, d.department_id, d.location_id
FROM employees e,departments d
WHERE e.department_id = d.department_id; -- 等值连接是指连接条件是等号

SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal; -- 等值连接是指连接条件不是等号
-- WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal;

1.2 自身连接

自连接查询，通过表的别名，实现其与自身进行连接查询。

比如查询每门课程的间接先修课（先修课的先修课）。

SELECT fisrt.id,second.preid FROM course first,course second WHERE first.preid = second.id

id代表课程号，preid代表先修课的id。

自连接的SELECT若出现同名字段则需要指明来自哪个表，但是结果集中可以存在同名字段。

SELECT employee_id #会报错，同名字段需要指定来自哪个表
FROM employees e1 LEFT JOIN employees e2
ON e1.manager_id = e2.employee_id;

SELECT * #不会报错，结果集中会存在2个employee_id，一个来自e1，一个来自e2
FROM employees e1 LEFT JOIN employees e2
ON e1.manager_id = e2.employee_id;

1.3 内连接 vs 外连接

内连接：只返回满足连接条件的记录。

外连接：在返回满足连接条件的基础上，还会返回不满足条件的记录，在缺少的字段位置补上NULL。分为左外连接和右外连接，满外连接。

如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。右外连接恰好相反。
满外连接：
满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
MySQL不支持FULL JOIN，但是可以用 UNION代替。

1.3.1 SQL92标准的外连接

使用(+)创建外连接，(+) 所在的表就认为是从表。

#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);

Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。

1.3.2 SQL99标准的外连接

使用JOIN...ON子句创建连接的语法结构。JOIN后跟表名，ON后面跟连接条件。

#通过JOIN...ON实现内连接
SELECT last_name,department_name,city
FROM employees e JOIN departments d # 内连接本来是INNER JOIN,但是可以简写为JOIN。
ON e.department_id = d.department_id
JOIN location l #连接多个表
ON d.location_id = l.location_id;

#左外连接
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d # OUTER可以省略
ON e.department_id = d.department_id;

#右外连接
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 ;

1.3.3 MySQL实现满外连接

#满外连接 = 左边第二个图+右边第一个图
SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

1.4 UNION关键字

使用UNION或UNION ALL关键字可以将多条SELECT语句的结果合并到一个结果集。合并时，两个表查询的字段个数和类型必须相同，并且相互对应。

UNION操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。
UNION连接的SELECT语句必须拥有相同数量的列，列也必须拥有相似的数据类型。
最终结果集的字段名取自第一个SELECT语句的结果集的字段名。

#查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees,2 WHERE email LIKE '%a%'
UNION
SELECT * FROM employees,1 WHERE department_id>90;

2. 连接条件对比

表的连接条件可以有三种方式：WHERE, ON, USING

WHERE：可以配合FROM...或FROM...JOIN...使用。

不可以配合FROM...LEFT/RIGHT JOIN...使用。

ON ：只能和JOIN一起使用，只能写连接条件。

虽然连接条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。

USING：只能和JOIN一起使用，作用是将两个表中指定的同名字段进行等值连接。

#四者等价
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e JOIN departments d
USING (department_id);

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

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

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

六、单行函数

使用SQL语言时，不同的DBMS之间的差异性很大。因此，SQL函数的的代码可移植性是很差的。

本文所涉及的函数适用于MySQL。

按函数所需记录的条数，可以将函数分为单行函数和多行函数。常见的单行函数有求绝对值，四舍五入。而，多行函数有求最大值。

单行函数可以嵌套，聚合函数不能嵌套使用。

1. 数值函数

1.1 基本函数

RAND(x)只要x不变，其结果也不会变。所以对同一个x，产生的随机数也相同。
ROUND(x,y)的y可以是负数。若是负数，则往小数点前看。比如ROUND(123.24,-1)的结果是120，往前看一位就是3（不足5，舍去）。
truncate是截断的意思，不会四舍五入。

1.2 三角函数（不常用）

RADIANS(x)得到的值是小数，比如SELECT RADIANS(180);的结果是3.1415926535。
SELECT DEGREES(PI()/3);得到的角度值是59.9999，也有精度损失。

注意事项：ATAN2(m,n)函数返回两个参数的反正切值。与ATAN(x)函数相比，ATAN2(m,n)需要两个参数，例如有两个点point(x1,y1)和point(x2,y2)，使用ATAN(x)函数计算反正切值为ATAN((y2-y1)/(x2-x1))，使用ATAN2(m,n)计算反正切值则为ATAN2(y2-y1,x2-x1)。由使用方式可以看出，当x2-x1等于0时，ATAN(X)函数会报错，而ATAN2(M,N)函数则仍然可以计算。

1.3 指数和对数

1.4 进制的转换

2. 字符串函数

注意：MySQL中，字符串的位置是从1开始的。
INSERT(str,idx,len,replacestr)并不是插入字符串，而是替换。INSERT('helloworld',2,3,'lc')的结果是hlcoworld，将索引从2开始，长度为3的字串ell替换为lc。

例题：查询书名达到10个字符的书，不包括里面的空格。

SELECT * FROM books WHERE CHAR_LENGTH(REPLACE(name,' ',''))>=10;

把空格替换为空字符串。

SELECT TRIM(LEADING '*' FROM '***sdasdbc**');的结果是sdasdbc**。
FROM、LEADING、TRAILING都是关键字，不是数据类型。

FIND_IN_SET(s1,s2)也是找首次出现的位置。FIND_IN_SET('bb','aa,bb,cc,aa')的返回值为2。

3. 日期时间函数

3.1 获取日期、时间

SELECT
CURDATE(),CURTIME(),NOW(),SYSDATE(),UTC_DATE(),UTC_DATE(),UTC_TIME(),UTC_TIME()
FROM DUAL;

UTC_TIME()返回的是格林威治时间，比北京时间慢了8小时。

3.2 日期与时间戳的转换

Unix时间戳(Unix timestamp)，是一种时间表示方式，定义为从格林威治时间1970年01月01日00时00分00秒起至现在的总秒数。

3.3 获取月份、星期、星期数、天数

date参数可以通过CURDATE()获取，或者直接使用NOW()，或者提供形式如'2024-2-21 23:32:12'或'2024-2-21'的字符串。
time参数可以通过CURTIME()获取，或者直接使用NOW()，或者提供形式如'2024-2-21 23:32:12'或'23:32:12'的字符串。

3.4 日期的操作函数

EXTRACT(type FROM date)函数中type的取值与含义：

extract是提取的意思，在这里是返回指定日期的特定部分。
microsecond是微秒，不是毫秒。
HOUR_MICROSECOND是指返回小时~微秒的所有内容（小时、分钟、秒、微秒），其他带下划线的同理。
不建议使用DAY_XX这4个拼接，因为有bug。

3.5 时间和秒钟转换的函数

这里的转换没考虑年的问题。
time参数可以通过CURTIME()获取，或者直接使用NOW()，或者提供形式如'2024-2-21 23:32:12'或'23:32:12'的字符串。

3.6 计算日期和时间的函数（常用）

上述函数中type的取值：

例子：SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);

INTERVAL是个关键字，expr（即例子的1），可以为负。
SELECT DATE_SUB(NOW(), INTERVAL '1_1' YEAR_MONTH);。当type为XX_XX，expr需要用单引号引起来，expr也需要是xx_xx的形式。

ADDTIME(time1,time2)的time可以写成20（在time1的基础上加20秒），或者'1:1:1'（在time1的基础上加1小时1分1秒）。
MAKEDATE(year,n)返回year这一年的第n天。
SELECT PERIOD_ADD(201703, 15);，这里的time需要以YYYYMM给出，结果是201806。

3.7 日期的格式化与解析

格式化：日期-->字符串

解析：字符串-->日期

DATE_FORMAT()和TIME_FORMAT()的返回值为指定格式的字符串。
TIME_FORMAT()只支持时分秒的格式化，对年月日不起作用的，而DATE_FORMAT()对于年月日时分秒或者两者分开都起作用。

上述fmt参数常用的格式符：

SELECT DATE_FORMAT(NOW(),'%H:%i:%s');的结果为16:13:05。
SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y') FROM DUAL;将'09/01/2009'按照'%m/%d/%Y'解析出年月份，得到日期。

GET_FORMAT函数中date_type和format_type参数取值如下：

GET_FORMAT的作用是通过data_type和format_type参数得到其他3个函数所需的fmt。
这样就无需记住fmt的构成，而可以通过GET_FORMAT直接获取常用的格式。
例如，SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')) FROM DUAL;。

3.8 例子：日期比较

在比较日期时，确保比较两者的日期格式是一致的，以避免错误。

3.8.1 直接比较

可以使用比较运算符（如">"、"<"、">="、"<="、"="）直接比较日期值。

SELECT * FROM table_name WHERE date_column > '2023-01-01';

'2023-01-01'字符串会隐式转换为date类型。

3.8.2 DATEDIFF()

SELECT * FROM table_name WHERE DATEDIFF(date_column,'2023-01-01') > 0;

3.8.3 使用日期格式化

将日期类型转换为特定的格式的字符串，然后进行字符串比较。

SELECT * 
FROM table_name 
WHERE DATE_FORMAT(date_column1, '%Y-%m-%d') < DATE_FORMAT(date_column2, '%Y-%m-%d'); 
#两个字符串类型比较

3.8.4 使用日期解析

SELECT * 
FROM table_name 
WHERE STR_TO_DATE(date_column, '%m-%d-%Y') > '01-01-2022';
#两个日期类型比较，这里可以改变fmt

4. 流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

CASE WHEN...THEN函数只返回第一个符合条件的值，剩下的WHEN...THEN部分将会被自动忽略。CASE WHEN...THEN的结尾必须有END关键字，没有匹配的值则返回NULL。

    SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪'
                                    WHEN salary>=10000 THEN '潜力股'
                                    WHEN salary>=8000 THEN '屌丝'
                                    ELSE '草根' END "描述"
    FROM employees;

5. 加密与解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。但实际上，可以在客户端就进行加密，而不是在存入数据库时才开始加密。

PASSWORD(str)、ENCODE(value,password_seed)、DECODE(value,password_seed)在MySQL 8.0已经弃用。

6.MySQL信息函数

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。

每个MySQL连接，都有一个连接ID，可以通过CONNECTION_ID()查看。
CONNECTION_ID()获取的是连接ID，而非连接数量。
每套字符集有一系列与之对应的比较规则。

7.其他函数

FORMAT(value,n)相当于四舍五入，但是如果n的值小于或者等于0，则只保留整数部分。
SELECT INET_ATON('192.168.1.100');的结果是一个整数3232235876，
以“192.168.1.100”为例，计算方式为192乘以256³，加上168乘以256²，加上1乘以256，再加上100。
SELECT INET_NTOA(3232235876);得到的是192.168.1.100 。

七、聚合函数（多行函数）

聚合函数是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

1.常见的聚合函数

1.1 AVG/SUM

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

SELECT AVG(salary),SUM(salary) #salary是一个字段，有很多条记录
FROM employees;

1.2 MAX/MIN

可以对数值、字符串、日期类型的数据使用 MIN 和 MAX 函数。

SELECT MAX(salary),MIN(salary)
FROM employees;

#字符串按字典序排序，MIN()取字典序最小的字符串，MAX()取字典序最大的字符串。
SELECT MAX(last_name),MIN(last_name) 
FROM employees;

SELECT MIN(hire_date), MAX(hire_date) #越早的日期越小
FROM employees;

1.3 COUNT

作用：计算指定字段在查询结果中出现的个数。

计算表中有多少条记录，如何实现？

方式1：COUNT(*)

方式2：COUNT(1)

括号里是个常数就行。
没有记录则返回 0 。

方式3：COUNT(具体字段)

计算指定字段的出现次数时，是不计算NULL值的，所以方式3可能会漏算。
AVG(具体字段)相当于SUM(具体字段)/COUNT(具体字段)，MAX、MIN、AVG和SUM直接忽略NULL，不参与运算。
只有方式1、2，才会不会忽略NULL的情况。
推荐使用方式1、2，效率更高。

2.GROUP BY的使用

2.1 GROUP BY说明

使用 GROUP BY语句，我们可以对数据进行分组并进行聚合计算，每个分组只显示一个结果。

若使用GROUP BY语句时，没有使用聚合函数，那么每个分组只会显示每个分组的第一条记录，而且不同分组的记录都是按照GROUP BY里的字段升序排列。

2.2 使用一个字段分组

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ; #把department_id相同的归为同一组

SELECT中非聚合函数的字段必须包含在 GROUP BY 中，但包含在 GROUP BY 子句中的字段不必包含在SELECT中。

2.3 使用多个字段分组

SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id,job_id ; #把department_id和job_id都相同的归为同一组

2.4 WITH ROLLUP关键字

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录用于汇总。

SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;

比如求每个部门的平均值，就会多一条记录求所有部门的平均值。

3.HAVING的使用

3.1 使用HAVING

HAVING的作用：对每个分组进行过滤。

如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错。

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

若有GROUP BY，HAVING必须声明在GROUP BY的后面。
开发中，使用HAVING的前提是SQL中使用了GROUP BY。

#查找价格最低的书

#错误写法，在没有使用GROUP BY的前提下，在HAVING中使用了聚合函数，若不使用聚合函数不会出错。
#这种写法相当于有一行GROUP BY NULL（GROUP BY NULL的效果等价于LIMIT 1,只取所有记录中的第一条记录，但是这时的MIN()、SUM()、MAX()、COUNT(1)并不是取这条记录某个字段的最小值、总和、最大值、条数，而是books表所有记录某个字段的最小值、总和、最大值、条数，这会导致可能出现price = MIN(price)永远为0，没有记录返回）。
SELECT * FROM books
[GROUP BY NULL]
HAVING price = MIN(price);

id price
1   3
2   2
3   4

SELECT * FROM books [GROUP BY NULL] 的结果是id=1、price=3这条记录。这条记录的price和MIN(2,3,4)（这里比较奇怪，是和books的所有price比较）比较始终返回 0，没有记录返回。

#查找价格最低的书
#正确写法
SELECT * FROM books
WHERE price = (SELECT price FROM books  ORDER BY price DESC LIMIT 0,1);

SELECT * FROM books
WHERE price >= ALL(SELECT price FROM books);

3.2 WHERE和HAVING对比

当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。

当过滤条件中没有聚合函数时，则此过滤条件可以声明在WHERE中或HAVING中。但是，建议声明在WHERE中。

对比：

如果需要通过多表查询从关联表中获取需要的数据，WHERE是先筛选后连接，而HAVING是先连接后筛选。这一点，就决定了在关联查询中，WHERE 比HAVING更高效。
可以在一个查询里面同时使用WHERE和HAVING。包含聚合函数的条件用HAVING，普通条件用 WHERE。这样，既利用了WHERE条件的高效快速，又发挥了HAVING可以使用包含聚合函数的条件的优点。

4.MySQL的SELECT的执行过程

4.1 SELECT书写顺序

SELECT ...,....,...
FROM ... (LEFT/RIGHT)JOIN ...
ON 多表的连接条件
(LEFT/RIGHT)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：分页

4.2 SELECT实际执行顺序

FROM #指定要检索的表或子查询。
JOIN...ON 
WHERE  #应用筛选条件，得到包括满足条件的行。
GROUP BY #按照指定的列进行分组。
HAVING #对分组后的结果进行筛选。
SELECT #指定要检索的列。
DISTINCT #去除重复的行。
ORDER BY #按照指定的列对结果进行排序。
LIMIT #分页查询，限制条数

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语句执行这些步骤的时候，每个步骤都会产生一个虚拟表（virtual table），然后将这个虚拟表传入下一个步骤中作为输入。

JOIN和ON两者的先后顺序存疑。

第2部分

一、子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。

子查询适用于从同一个表中先计算得出一个数据结果，然后与这个数据结果进行比较的场景。

在查询中，除了GROUP BY和 LIMIT之外，其他位置都可以声明子查询。

1.需求分析与问题解决

1.1 实际问题

1.2 子查询的基本使用

#子查询写法
SELECT last_name,salary #主查询（外查询）
FROM employees
WHERE salary > (
                SELECT salary  #子查询（内查询）在主查询之前执行完成，子查询的结果被主查询（外查询）使用
                FROM employees
                WHERE last_name = 'Abel'
        		);

注意事项：
子查询要包含在括号内。
为了美观，将子查询放在比较条件的右侧。
单行操作符对应单行子查询，多行操作符对应多行子查询

1.3 子查询的分类

分类方式1：

我们按内查询的结果返回一条还是多条记录，将子查询分为`单行子查询` 、 `多行子查询` 。

分类方式2：

我们按内查询是否被执行多次，将子查询划分为 相关(或关联)子查询 和 不相关(或非关联)子查询 。

子查询可独立执行，不依赖于父查询表的查询，称为不相关子查询。

构造子查询的查询条件时需要用到父查询的某一个属性列，这样的查询称为相关子查询。

不相关子查询总是独立于外部查询，整个过程中子查询只执行一次。
相关子查询依赖于外部查询，其子查询执行的次数等于外部查询表中元组的个数。

2. 单行子查询

2.1 比较符

这里的<>也可以写成!=。

#题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name, job_id, salary
FROM employees
WHERE job_id =(
    			SELECT job_id
                FROM employees
                WHERE employee_id = 141
              )
AND salary >(
    			SELECT salary
				FROM employees
				WHERE employee_id = 143
            );

2.2 HAVING中的子查询

#题目：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > # HAVING的作用：对每个分组进行条件过滤。
				(SELECT MIN(salary)
				FROM employees
				WHERE department_id = 50);

2.3 CASE中的子查询

# 题目：显式员工的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;

2.4 单行子查询没记录的情况

SELECT last_name, job_id
FROM employees
WHERE job_id =
		(
            SELECT job_id
            FROM employees
            WHERE last_name = 'Haas' #没有这个人
        );

当子查询没有“返回记录”时（没有“返回记录”并不是返回NULL，返回NULL的是查出了“返回记录”，只是字段值为NULL而已），主查询也将没有记录。

3.多行子查询

3.1 多行比较操作符

题目：返回比job_id为‘IT_PROG’部门中任一一个员工工资低的、其它job_id的员工的员工号、姓名、job_id以及salary。

任一：和子查询的结果中某个值比较，满足条件即可，不是“任意”的意思。
>Any表示至少大于一个值，即大于最小值。 >All表示大于每一个值。

题目：返回比job_id为‘IT_PROG’部门所有员工工资都低的、其它job_id的员工的员工号、姓名、job_id以及salary。

题目：查询平均工资最低的部门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.2 多行子查询的空值问题

当时使用NOT IN遇到多行子查询结果中有NULL时，主查询将没有结果。

这实际上是WHERE的问题，只是在多行子查询中再次遇到了。WHERE子句的结果为0或NULL时，将没有记录返回。

SELECT 1
WHERE 3 NOT IN (1,2,NULL);
# 3 NOT IN (1,2,NULL) 相当于 3!=1 and 3!=2 and 3!=NULL，而3!=1 and 3!=2 and 3!=NULL，为1 and 1 and NULL。在逻辑与中，NULL的优先级比1更高，3!=1 and 3!=2 and 3!=NULL的最终结果为NULL，WHERE子句的结果为0或NULL时，将没有记录返回。

SELECT 1
WHERE 1 NOT IN (1,2,NULL);
# 1 NOT IN (1,2,NULL) 相当于 1!=1 and 3!=2 and 3!=NULL，而3!=1 and 3!=2 and 3!=NULL，为0 and 1 and NULL。在逻辑与中，0的优先级比NULL更高，1!=1 and 3!=2 and 3!=NULL的最终结果为0，WHERE子句的结果为0或NULL时，将没有记录返回。

由上例可知，只要NOT IN遇到列表中有NULL时，主查询都没有结果。

4.相关查询

如果子查询的执行依赖外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

4.1 执行流程

4.2 在FROM和ORDER BY中使用子查询

子查询里的department_id都依赖于外部表中员工的department_id。
先确定员工来自哪个部门，然后再比较工资。

from型的子查询：将子查询作为from的一部分，子查询要用()括起来，并且要给这个子查询取别名，把它当成一张“临时的虚拟的表”来使用。

在ORDER BY中使用子查询：

这里也可以用多表查询。
在ORDER BY中使用子查询的逻辑是，对于每个员工，查出其department_name，然后根据其department_name的大小确定其在结果记录中的位置。
若子查询没有查出记录，ORDER BY排序时将其当成NULL进行排序。
排序的字段如果为NULL，则排在最上面（升序情况）。

4.3 EXISTS和NOT EXISTS关键字

#查询公司管理者的employee_id，last_name，job_id，department_id信息
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS 
			(SELECT *  #这里的*可以被（字段、常量，比如用e2.manager_id、‘X’,2）替换
			 FROM employees e2
			 WHERE e2.manager_id = e1.employee_id
            );

EXISTS：在相关子查询中存在符合要求的行。
EXISTS找到一条满足子查询要求的行就会退出，返回1，再去判断外表中的下一条记录。

#查询departments表中，不存在于employees表中的部门的department_id和department_name
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
                  FROM employees e
                  WHERE e.department_id = d.department_id
                 );

NOT EXISTS：在相关子查询中不存在符合要求的行。
NOT EXISTS找到一条满足子查询要求的行就会退出，返回0，再去判断外表中的下一条记录。

5.思考

解答：自连接方式好！题目中可以使用子查询，也可以使用自连接。一般情况建议使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。

二、创建和管理数据库和表

在MySQL中，一个完整的数据存储过程总共有4步，分别是创建数据库、确认字段、创建数据表、插入数据。

1.创建和管理数据库

1.1 创建数据库

注意：数据库不能改名。
但一些可视化工具可以改名。其实际执行的步骤是：
建立新的数据库。
把所有表复制到新库。
删除旧的数据库。

1.2 查看数据库

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，即USE 数据库名;
如果需要在数据库A中使用数据库B的表，可以在表前面加上“数据库B.”（前提是有调用其他数据库表的权限）。

1.3 修改数据库的字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等

1.4 删除数据库

DROP DATABASE IF EXISTS 数据库名;

要删除的数据库若不存在，则忽略这条语句。

2.创建和管理表

2.1 创建表

方式1：不基于现有的表。

CREATE TABLE [IF NOT EXISTS] 表名( 
    字段1, 数据类型 [约束条件] [默认值],
    字段2, 数据类型 [约束条件] [默认值],
    字段3, 数据类型 [约束条件] [默认值],
    ……
    [表约束条件] #括号里的最后一项
);

-- 创建表
CREATE TABLE emp (
    -- int类型
    emp_id INT,
    -- 最多保存20个中英文字符
    emp_name VARCHAR(20),
    -- 总位数不超过15位
    salary DOUBLE,
    -- 日期类型
    birthday DATE 
);

用户需要有创建表的权限。

方式2：基于现有的表。

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

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE 1=2; #结果为0，不会插入查到的数据。

以employees里的employee_id、last_name、ANNSAL、 hire_date字段的别名（没有别名的话，就用字段名）创建新表dept80，并且WHERE子句的结果是为1时，还会在新表中插入查到的数据。
新表的字段的数据类型和原表完全相同。
旧表、新表是两张表。
此方法会继承NOT NULL和DEFAULT的约束，不会继承主键、外键、唯一约束。

2.2 查看表结构

在MySQL中创建表之后，可以查看数据表的结构。

MySQL支持使用DESCRIBE/DESC 表名;语句查看数据表结构，也支持使用SHOW CREATE TABLE 表名; 语句查看数据表结构。

DESC jobs;的结果如下：

SHOW CREATE TABLE jobs;的结果如下：

#Create Table的记录中有换行，所以只显示了第一行，复制到txt中查看就行。
CREATE TABLE `jobs` (
  `job_id` varchar(10) NOT NULL DEFAULT '',
  `job_title` varchar(35) NOT NULL,
  `min_salary` int(6) DEFAULT NULL,
  `max_salary` int(6) DEFAULT NULL,
  PRIMARY KEY (`job_id`),
  UNIQUE KEY `job_id_pk` (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.3 修改表（ALTER TABLE）

2.3.1 添加一个字段

语法格式如下：

ALTER TABLE 表名 ADD 字段名1 字段类型 [FIRST|AFTER 字段名2];

例子：

ALTER TABLE dept80 ADD job_id varchar(15) FIRST;

FIRST表示添加为表的第一个字段；AFTER 字段名2表示添加未字段2的下一个字段（从左到右）。
不写[FIRST|AFTER 字段名]，则默认添加为表的最后一个字段。

2.3.2 修改一个字段（数据类型、长度、默认值）

语法格式：

ALTER TABLE 表名 MODIFY 字段名字段类型 [DEFAULT 默认值] [FIRST|AFTER 字段名];

例子：

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

2.3.3 重命名字段

语法格式：

ALTER TABLE 表名 CHANGE 列名新列名新数据类型;

重命名字段的同时，还可以改数据类型。
若不想修改数据类型，则写原数据类型，不能省略。

例子：

ALTER TABLE dept80 CHANGE department_name dept_name varchar(15);

2.3.4 删除一个字段

语法格式：

ALTER TABLE 表名 DROP [COLUMN] 字段名;

例子：

ALTER TABLE dept80 DROP job_id;

在ADD、MODIFY、CHANGE、DROP 后的COLUMN关键字可以省略。

2.4 重命名表

2.5 删除表

DROP TABLE [IF EXISTS] 表名1 [,表名2,表名3,...,表名n];

DROP TABLE语句不能撤销。
MySQL 8新特性—DDL的原子化：DDL操作要么成功，要么回滚，比如 DROP TABLE 表名1,表名2;，如果表1存在、表2不存在，则表1不能被删除。

2.6 清空表

TRUNCATE TABLE [IF EXISTS] 表名;

TRUNCATE语句不能回滚，而使用DELETE语句删除数据，可以回滚。

2.7 COMMIT和ROLLBACK

COMMIT：提交数据。

一旦执行COMMIT，数据将被永久地保存在了数据库中，意味着数据不可以回滚。

ROLLBACK：回滚数据。

一旦执行ROLLBACK，则可以实现数据的回滚。回滚到最近的一次COMMIT之后。

三、数据处理之增删改

1. 插入数据

1.1 VALUES的方式

1.1.1 没有指明字段

INSERT INTO 表名 VALUES (value1,value2,...);

(value1,value2,....)需要为表的每一个字段指定值，并且值的顺序必须和表中字段定义时的顺序相同。
字符、字符串和日期型数据应用单引号引起来。

1.1.2 给表的指定字段插入数据

INSERT INTO 表名(column1 [, column2,..., columnn])
VALUES (value1 [,value2,..., valuen]);

在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。
在表名(column1 [, column2,..., columnn])中按任意顺序列出字段名，但是一旦列出，(value1,...,valuen)需要与column1,...columnn列一一对应。

1.1.3 同时插入多条记录

INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开，基本语法格式如下：

#没有指明字段
INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

#给表的指定字段插入数据
INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

#例子
INSERT INTO emp(emp_id,emp_name)
VALUES (1001,'shkstart'),
	   (1002,'atguigu'),
       (1003,'Tom');

同时插入多行记录的INSERT语句比多个单行插入的INSERT语句更快。

1.2 将查询结果插入到表中

基本语法格式：

INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

#例子

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE department_id = 90;

注意：
不必书写VALUES子句。
子查询中的值列表应与 INSERT 子句中的列名对应。

2. (修改)更新数据

基本语法格式：

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen #可以修改多个字段
[WHERE condition]

使用WHERE子句指定需要更新的数据，如果省略WHERE子句，则表中的所有数据都将被更新。
如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;
修改数据时，是可能存在不成功的情况的。（可能是由于约束的影响造成的）。

3. 删除数据

基本语法格式：

DELETE FROM table_name [WHERE <condition>];

如果省略WHERE子句，则表中的全部数据将被删除。
删除数据时，是可能存在不成功的情况的。（可能是由于约束的影响造成的）。

4. 计算列

4.1 计算列的定义

简单来说，计算列就是某一列的值是通过别的列计算得来的。

例如，a列值为1、b列值为2，c列不需要手动插入，c的值为a+b的结果，那么c就是计算列，是通过别的列计算得来的。

4.2 如何使用计算列

创建表时，在字段数据类型后面加上GENERATED ALWAYS AS (expression) VIRTUAL。

CREATE TABLE tb1(
    id INT,
    a INT,
    b INT,
    c INT GENERATED ALWAYS AS (a + b) [VIRTUAL | STORED]
);

VIRTUAL：计算列的值不会存储在数据库中，而是在查询时根据指定的表达式动态计算得出。
STORED：在插入或更新行时计算并存储列值。存储的列需要存储空间。
没有显式给出VIRTUAL或STORED时，默认是VIRTUAL。

四、MySQL数据类型

1. MySQL中的数据类型

浮点数和定点数类型的特点是可以处理小数。

2. 整数类型

2.1 可选属性

2.1.1 显示宽度

指定数据类型时，可以在其后添加(数值)，如tinyint(2)，这里的2表示当数据宽度小于2位的时候，可以在数字前面需要用字符填满宽度。

该项功能需要配合“ZEROFILL”使用，表示用“0”填满宽度，否则“显示宽度”无效，无需填充。

设置tinyint(4) ZEROFILL ，你插入1时他会显示“0001”，插入1234时，显示“1234”。

设置tinyint(4) ，你插入1时他会显示“1”，插入123时，显示“123”。

在 int(M) 中，M的值跟 int(M) 所占多少存储空间并无任何关系。

在MySQL 8中已不推荐指定“显示宽度”，使用由系统为每一种类型指定的默认宽度值。

2.1.2 UNSIGNED

UNSIGNED : 无符号类型（非负）。

CREATE TABLE test_int3(
	f1 INT UNSIGNED #f1的取值为无符号的INT
);

插入负数时，会自动替换为插入0。

2.1.3 ZEROFILL

ZEROFILL : 0填充。

如果某字段是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性。

3. 浮点类型

3.1 类型说明

MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL。

REAL默认就是DOUBLE。如果你把 SQL 模式设定为启用“ REAL_AS_FLOAT ”，那么，REAL是FLOAT。

SET sql_mode = “REAL_AS_FLOAT”;

FLOAT（单精度浮点数）：占4个字节。

DOUBLE（双精度浮点数）：占8个字节。

问题：为什么浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于有符号数取值范围大于等于零的部分呢？

MySQL存储浮点数的格式为：符号(S)、尾数(M)和阶码(E) 。因此，无论有没有符号，MySQL 的浮点数都会存储表示符号的部分。因此，所谓的无符号数取值范围，其实就是有符号数取值范围大于等于零的部分。

3.2 数据精度说明

MySQL允许使用非标准语法（其他数据库未必支持，因此如果涉及到数据迁移，则最好不要这么用）FLOAT(M,D) 或 DOUBLE(M,D) 。M称为精度，D称为标度。M=整数位+小数位，D=小数位。其中，D<=M<=255，0<=D<=30。

例如，定义为FLOAT(5,2)的一个列可以显示为-999.99-999.99。如果超过这个范围会报错。

浮点类型，也可以加 UNSIGNED ，但是不会改变数据范围，例如：FLOAT(3,2) UNSIGNED仍然只能表示0-9.99的范围。

当插入的值不符合M和D的要求时，MySQL的处理方案如下：

如果存储时，整数部分超出了范围，MySQL就会报错，不允许存这样的值。
如果存储时，小数点部分若超出范围，就分以下情况：
- 若小数四舍五入后，整数部分没有超出范围，保存四舍五入后的结果。
- 若小数四舍五入后，整数部分超出范围，则MySQL报错。

在MySQL 8 中，已不推荐使用(M,D)，而采用系统的默认值。
其实也不一定是四舍五入，因为浮点数是不准确的。并且，要避免使用“=”来判断两个浮点数是否相等。

4. 定点数类型(DECIMAL)

使用DECIMAL(M,D)的方式表示高精度小数。其中，M被称为精度，D被称为标度。0<=M<=65，0<=D<=30，D<M。
定点数在MySQL内部是以字符串的形式进行存储，这就决定了它一定是精准的。
当DECIMAL类型不指定M和D时，其默认为DECIMAL(10,0)。当插入的值不符合M和D的要求时，DECIMAL类型会进行四舍五入处理（但是只会看小数点后D+1位，而不考虑后面的数字，比如对于DECIMAL(10,0)，10.49由于4不够进位，直接舍去，并没有从9所在位置开始四舍五入）。

浮点类型取值范围大，但是不精准；DECIMAL型取值范围相对小，但没有误差。

5. 位类型（BIT）

BIT类型中存储的是二进制值，类似010110。

BIT类型可以写成BIT(M)。如果没有指定(M)，则默认为1。这个1表示只能存1位的二进制值。

这里(M)是表示二进制的位数，1<=M<=64。

CREATE TABLE test_bit1(
    f1 BIT,
    f2 BIT(5),
    f3 BIT(64)
);

INSERT INTO test_bit1(f2)
VALUES(23);  #这里的23是10进制。在向BIT类型的字段中插入数据时，一定要确保插入的数据在BIT类型支持的范围内。

#使用SELECT命令查询位字段时，可以用 BIN() 或 HEX() 函数进行读取。
SELECT BIN(f2) FROM test_bit1; #结果显示为10111。

#使用b+0查询数据时，可以直接查询出存储的十进制数据的值，应该有隐式转换。
SELECT f2+0 FROM test_bit1; #结果显示为23。

6. 日期类型

MySQL有多种表示日期和时间的数据类型，不同的版本可能有所差异。MySQL8.0版本支持的日期和时间类型主要有：YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。

YEAR 类型通常用来表示年
DATE 类型通常用来表示年、月、日
TIME 类型通常用来表示时、分、秒
DATETIME 类型通常用来表示年、月、日、时、分、秒
TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒

TIME类型有正有负的原因：TIME 类型，不光可以表示一天之内的时间，还可以用来表示一个时间间隔，这个时间间隔可以超过 24 小时。
日期类型在INSERT的时候建议用单引号引起来，不引起来的话存在从数值类型到日期类型的隐式转换。

6.1 YEAR类型

在MySQL中，YEAR有以下几种存储格式：

以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。
以2位字符串格式表示YEAR类型，最小值为00，最大值为99。
- 当INSERT的值为01到69时，表示2001到2069。
- 当INSERT的值为70到99时，表示1970到1999。
- 当INSERT的值是整数的0或00时，那么是0000年。
- 当INSERT的值是日期/字符串的'0'或'00'时，是2000年。
从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用。YEAR默认格式就是“YYYY”，没必要写成YEAR(4)。
```
CREATE TABLE test_year(
    f1 YEAR, #两种写法等价，推荐上面这种
    f2 YEAR(4) 
);

INSERT INTO test_year(f1)
VALUES('2023');
```

6.2 DATE类型

DATE类型表示日期，没有时间部分，格式为 YYYY-MM-DD 。

以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期。
也就是短横线可以省略，但没必要。
以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期。
此格式中，年份为两位数值或字符串满足YEAR类型的格式条件为：当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99时，会被转化为1970到1999。
在这里，'000301'和000301都表示2000-03-01，所以别用YY格式。
INSERT的值为用CURRENT_DATE() 或者NOW()函数，会插入当前系统的日期。
```
INSERT INTO test_date1
VALUES (CURRENT_DATE()),
```

6.3 TIME类型

TIME类型用来表示时间，不包含日期部分。

在MySQL中，向TIME类型的字段插入数据时，也可以使用几种不同的格式。

可以使用带有冒号的字符串，比如'D HH:MM:SS' 、'HH:MM:SS'、'HH:MM'、'D HH:MM'、'D HH'或'SS'格式，都能被正确地插入TIME类型的字段中。
其中D表示天，其最小值为0，最大值为34。如果使用带有D格式的字符串插入TIME类型的字段时，D会被转化为小时，计算格式为D*24+HH。
12:10是'HH:MM'，表示12:10:00。
为了好记，建议直接使用完整格式。
可以使用不带有冒号的字符串或者数字，格式为'HHMMSS'或者HHMMSS 。
1210，将最右边的两位解析成秒，表示00:12:10，而不是12:10:00。
INSERT的值为用CURRENT_TIME() 或者NOW()函数，会插入当前系统的时间。

6.4 DATETIME类型（常用）

在向DATETIME类型的字段插入数据时，同样需要满足一定的格式条件。

以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时，最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59。
以 YY-MM-DD HH:MM:SS 格式或者 YYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时，两位数的年份规则符合YEAR类型的规则，00到69表示2000到2069；70到99表示1970到1999。
使用函数CURRENT_TIMESTAMP()和 NOW()，可以向DATETIME类型的字段插入系统的当前日期和时间。

6.5 TIMESTAMP类型(常用)

TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是YYYY-MM-DD HH:MM:SS ，需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。

TIMESTAMP底层存储的是毫秒值----------当前时间距离1970-1-1 0:0:0的毫秒值，和DATETIEM底层存储方式不同。
UTC代表是格林威治时间，比北京时间慢了8小时（东八区时间）。
TIMESTAMP会将INSERT的时间转换为对应的UTC时间，然后再存储。因此，TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间（改时区的方法：SET time_zone = '+9:00';，这里把时区改成了东九区时间）。
比如INSERT的值为2024-2-10 10:10:10，实际上存储的是2024-2-10 02:10:10对应的毫秒值（因为我们是东八区，比UTC时间快了8小时）。
两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。

使用函数CURRENT_TIMESTAMP()和 NOW()，可以向DATETIME类型的字段插入系统的当前日期和时间。

如果向TIMESTAMP类型的字段插入的时间超出了TIMESTAMP类型的范围，则MySQL会抛出错误信息。

7.文本字符串类型

MySQL中，文本字符串总体上分为CHAR 、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT 、LONGTEXT 、ENUM、SET等类型。

7.1 CHAR和VARCHAR

7.1.1 基础知识

CHAR(M) ：代表最多可以存储M个字符（中文字符、英文字符都可以），0 <= M <= 255 。

CHAR是固定长度，如果只存储M-2个字符，底层也会占用M个字符的存储空间。
如果不指定(M)，则默认是1个字符。
如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除右侧的空格。
INSERT INTO test_varchar3
VALUES(' 尚   '); #会删去右侧的空格。

SELECT * 
FROM test_varchar3
WHERE c1 = ' 尚   ' OR c1 LIKE ' 尚   '; #有右侧的空格查不到。

SELECT * 
FROM test_varchar3
WHERE c1 = ' 尚' OR c1 LIKE ' 尚'; #条件的里字符串也需要删去右侧的空格。

VARCHAR(M) ：代表最多可以存储M个字符（中文字符、英文字符都可以），M的最大值由字符集编码方式确定。

M不可以省略。
M最大值的计算方式：（65535-3）/字符集单字符最大字节数。
MySQL默认规定一条记录最大的长度是65535字节，所有字段加在一起所占的字节数不能超过65535，当只有一个VARCHAR字段时，取得最大值。
减去3的原因：
NULL值列表占用的字节数：用1字节标识是否为NULL。
当字段都定义成NOT NULL的时候，这时候就不需要NULL值列表了。所以在设计表的时候，通常建议将字段设置为 NOT NULL，这样可以节省空间。
每个字段对应一个二进制位，当为NULL时，置为1。
NULL值列表并不是固定只占1字节。NULL值列表占用的字节数会根据字段数向上取整，1个字节可以表示8个字段，10个字段需要1 0位，需要用2个字节表示，其他情况以此类推。
长度标识字节数：存储实际存储的字符，当字符长度小于等于255(2⁸-1)时，占1字节；大于255，小于等于65535(2¹⁶-1)，占2字节。
UTF-8编码：一个英文字符占一个字节，中文3字节，单字符最大可占用3个字节。
VARCHAR是可变长度，底层会存储实际长度 + 1~3个字节。取1的时候字段被设置为NOT NULL且字符长度小于等于255。
检索VARCHAR类型的字段数据时，会保留数据右侧的空格。
INSERT INTO test_varchar3
VALUES(' 尚   '); #不会删去右侧的空格。

SELECT * 
FROM test_varchar3
WHERE c1 = ' 尚   ' OR c1 LIKE ' 尚   '; #能查到。

SELECT * 
FROM test_varchar3
WHERE c1 = ' 尚' OR c1 LIKE ' 尚'; #没有右侧的空格，查不到内容。

7.1.2 哪些情况使用CHAR或VARCHAR更好

长度固定的数据（身份证、手机号）可以使用CHAR，字段长度不固定用VARCHAR。

查询速度CHAR大于VARCHAR。

7.2 TEXT类型

在MySQL中，TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT类型。

注意这里的L是字节数。

对于TEXT类型，系统按照数据的实际长度分配空间，不需要预先定义长度。

MySQL不允许TEXT类型的字段做主键。
TEXT类型不能添加默认值。
在保存和查询数据时，并没有删除TEXT类型的数据右侧的空格。

7.3 ENUM类型

ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定。

CREATE TABLE test_enum(
		season ENUM('春','夏','秋','冬','unknow') #INSERT的值从这里多选一。
);

# 忽略大小写
INSERT INTO test_enum
VALUES('UNKNOW');  #添加成功

# 允许按照角标的方式获取指定索引位置的枚举值，第一个值的索引为1，若添加不存在的索引，则添加空字符串。
INSERT INTO test_enum
VALUES('1'),(3);

# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);

7.4 SET类型

SET表示一个字符串对象，可以多个成员。SET类型在存储数据时成员个数越多，其占用的存储空间越大。

CREATE TABLE test_set(
	s SET ('A', 'B', 'C')
);
#可以在一个''中添加多个值，并用英文逗号隔开。ENUM不可以这样。
INSERT INTO test_set (s) VALUES ('A'), ('A,B'); #查找的时候也是显示：A,B

#插入重复的SET类型成员时，MySQL会自动删除重复的成员
INSERT INTO test_set (s) VALUES ('A,B,C,A');

#向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。
INSERT INTO test_set (s) VALUES ('A,B,C,D');

8.二进制字符串类型

MySQL中的二进制字符串类型主要存储一些二进制数据，比如可以存储图片、音频和视频等二进制数据。

MySQL中支持的二进制字符串类型主要包括BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和LONGBLOB类型。

8.1 BINARY与VARBINARY（不常用）

BINARY (M)：固定长度的二进制字符串。M表示最多能存储的字节数，取值范围是0~255。

如果未指定(M)，表示只能存储1个字节。
如果字段值不足M个字节，将在右边填充'\0'以补齐指定长度，查询时不会删除尾部的'\0'。
CREATE TABLE test_binary11(
	f2 BINARY(3),
);

INSERT INTO test_binary11(f2)
VALUES(' a'); #在右侧补上了一个'\0'

SELECT * FROM test_binary11 WHERE f2 = ' a\0'; 
#并且在查询时并没有删除，所以等号右侧得加上\0才能查到结果。

VARBINARY (M)：可变长度的二进制字符串。M表示最多能存储的字节数，总字节数不能超过行的字节长度限制65535，另外还要考虑额外的字节开销，VARBINARY类型的数据除了存储数据本身外，还需要1或2个字节来存储长度标识字节数。VARBINARY类型必须指定(M) ，否则报错。

这里的M最大值：（65535-3），计算方法和VARCHAR那里的类似，只是不除以3。
不会使用'\0'填充，查询时不会删除尾部的'\0'。

8.2 BLOB类型

MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB4种类型，它们可容纳值的最大长度不同。可以存储一个二进制的大对象，比如图片 、音频和视频等。

需要注意的是，在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到服务器的磁盘上，并将图片、音频和视频的访问路径存储到MySQL中。

L为字节数。

TEXT和BLOB的使用注意事项：

删除BLOB和TEXT类型的值会在数据表中留下很多内存碎片。为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理 。
不建议用SELECT *查询TEXT和BLOB类型的数据，否则会传输大量的BLOB或TEXT值。
把BLOB或TEXT列分离到单独的表中。这样做可以减少主表中的碎片，可以发挥固定长度数据行的性能优势，而且在主表上运行SELECT *查询的时候不会传输大量的BLOB或TEXT值。

9. JSON

JSON(JavaScript Object Notation)是一种轻量级的数据交换格式 ，其完全独立于编程语言。JSON可以将数据转换为字符串，然后就可以在网络之间传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。

独立于编程语言是指JSON不是只适用于某种语言，而是多种编程语言都可以使用JSON格式。
在传输的时候，是以JSON格式字符串进行传输，然后转为编程语言的对象。

9.1 JSON数组格式

[{"name":"lc"},{"name":"cluo"},32,"字符串"]  //数组元素的类型无需一致。
//用下标取出数组元素，下标从0开始。
//{}代表是一个对象。
//在JavaScript中，使用“对象名.键”取出对应的值。
//在JavaScript中，可以通过数组[0].name会取出"lc"。
//在JAVA中，将JSON格式的字符串转化为JAVA中的对象，然后可以通过“对象名.属性”或者GET方法取出属性值。

9.2 JSON对象格式(常用)

//对象的属性名必须用""包裹。
//对象的属性值只有字符串需要用""包裹，属性值可以是字符串、数组、对象、数字,null、true、false。

{"name":"yoxi","age":37,"dog":{"dname":"欢欢"},"friends":["cluo","suya"]}
//dog的属性值又是一个JSON对象，friends的属性值是一个数组。

9.3 如何在MySQL中存储JSON

CREATE TABLE test_json(
	js json
);

INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing","city":"beijing"}}');
#在存储的时候，我们会将JSON对象以字符串的形式存储，所以需要加上''。

SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province'
AS province, js -> '$.address.city' AS city 
FROM test_json;
WHERE js -> '$.name' = 'songhk';

在MySQL中，使用 json字段名->'$.json属性名' 可以取得属性值。

第3部分

一、约束（constraint）

1.约束的概述

1.1 为什么需要约束

为了保证数据的完整性，防止错误和无效信息的输入输出。

如果数据库中存储有不正确的数据值，则该数据库称为已丧失数据完整性。

为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：

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

1.2 什么是约束

约束是对表中字段的强制规定。

1.3 创建、删除约束

可以在创建表的时候规定约束（通过CREATE TABLE语句）。
在表创建之后，通过 ALTER TABLE 语句添加或删除约束。

1.4 约束的分类

根据约束的作用范围，约束可分为：

列级约束：是对某一字段的约束，直接跟在该字段的定义之后。
表级约束：可以应用于一个字段，也可以应用在一个表中的多个字段。不定义在某一字段后面，而是单独定义。

根据约束起的作用，约束可分为：

NOT NULL：非空约束，规定某个字段不能为空
UNIQUE：唯一约束，规定某个字段在整个表中是唯一的
PRIMARY KEY：主键(非空且唯一)约束
FOREIGN KEY：外键约束
CHECK：检查约束
DEFAULT：默认值约束

注意： MySQL不支持CHECK约束，但可以使用CHECK约束，而没有任何效果。

1.5 查看某个表已有的约束

#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名称';

2.非空约束

2.1 关键字

NOT NULL

2.2 特点

默认情况下，所有的类型的值都可以是NULL。
非空约束是列级约束。
空字符串''不等于NULL，0也不等于NULL。

2.3 添加非空约束

建表时

CREATE TABLE 表名称(
    字段名 数据类型,
    字段名 数据类型 NOT NULL,
    字段名 数据类型 NOT NULL
);

建表后
```
ALTER TABLE 表名
MODIFY 字段名 数据类型 NOT NULL;
```
修改一个字段的字段的时候，可以补上约束。

2.4 删除非空约束

ALTER TABLE 表名
MODIFY 字段名 数据类型 NULL;

把NOT NULL改成NULL，就相当于删除了非空约束。

3.唯一性约束

3.1 关键字

UNIQUE

3.2 特点

唯一性约束允许列值为NULL，而且可以多次添加NULL值。
NULL值可以不唯一。
有列级约束和表级约束两种写法。
如果创建唯一约束时未显式指出约束名，如果是单列，就默认和列名相同；如果是复合唯一，那么默认和字段列表中排在第一个的字段名相同。
添加唯一性约束的列上也会自动创建唯一索引。

3.3 添加唯一性约束

建表时

CREATE TABLE 表名称(
    字段名1 数据类型,
    字段名2 数据类型 UNIQUE, #默认约束名与字段名相同
    字段名3 数据类型,
    字段名4 数据类型
);
CREATE TABLE 表名称(
    字段名1 数据类型,
    字段名2 数据类型,
    字段名3 数据类型,
	[CONSTRAINT 约束名] UNIQUE(字段名1,字段名2)  #表级约束
    #不显式给出约束名，则以“字段名1”给约束命名。
    #字段列表中写的是多个字段名，多个字段名用逗号分隔，表示复合唯一，即多个字段的组合是唯一的，只有有一个字段不同就视为不同。
);

建表后指定唯一约束

#方式1：
ALTER TABLE 表名称 ADD CONSTRAINT 约束名 UNIQUE(字段列表);

3.4 删除唯一性约束

删除唯一约束只能通过删除唯一索引的方式删除。
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。

ALTER TABLE USER
DROP INDEX 约束名;

注意：可以通过 SHOW INDEX FROM 表名称; 查看表的索引。

4. 主键约束

用来唯一标识表中的一行记录。

4.1 关键字

PRIMARY KEY

4.2 特点

主键约束的列不能重复，也不允许出现空值。
一个表最多只能有一个主键约束。
有列级约束和表级约束两种写法。
主键约束对应着表中的一列或者多列（复合主键）。
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
MySQL的主键名总是PRIMARY，自定义的命名不会生效。
当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引。如果删除了主键约束，主键索引就自动删除了。
不要修改主键的值。

4.3 添加主键约束

建表时

CREATE TABLE 表名称(
	字段名 数据类型 PRIMARY KEY, #列级模式
	字段名 数据类型,
	字段名 数据类型
);

CREATE TABLE 表名称(
	字段名 数据类型,
	字段名 数据类型,
	字段名 数据类型,
	[CONSTRAINT 约束名] PRIMARY KEY(字段名) #表级模式，自定义命名不会生效，所以没必要起名字。
);

#复合主键
CREATE TABLE 表名称(
    字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型,
    PRIMARY KEY(字段名1,字段名2) #表示字段1和字段2的组合是唯一的,只有有一个字段不同就视为不同。
);

建表后增加主键约束

ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); 
#字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键。

4.4 删除主键约束（实际开发中，不会做这件事）

ALTER TABLE 表名称 DROP PRIMARY KEY;

删除主键约束的时候，无需指定主键名。

5. 自增约束（AUTO_INCREMENT）

添加记录时，让某个字段的值自增。

5.1 关键字

AUTO_INCREMENT

5.2 特点

一个表最多只能有一个自增长列。
自增长列约束的列必须是主键约束的列或唯一约束的列。
自增约束的列的数据类型必须是整数类型。
开发中，一旦有列声明了AUTO_INCREMENT，在INSERT的时候，就不要给此列赋值。
在MySQL中，AUTO_INCREMENT的初始值默认是1，每新增一条记录，字段值自动加1。设置自增属性的时候，还可以指定第一条插入记录的自增字段的值。
在建表时设置AUTO_INCREMENT=n，指定一个自增的初始值。
```
CREATE TABLE test_auto1(
id INT PRIMARY KEY AUTO_INCREMENT,
a INT
)AUTO_INCREMENT = 20; #注意在这里设定初试值
```
如果自增列手动指定了具体值，直接赋值为具体值。
具体值不能为0，自增列指定为0和NULL时，会在当前最大值的基础上自增。

5.3 添加自增约束

建表时

CREATE TABLE 表名称(
    字段名 数据类型 PRIMARY KEY AUTO_INCREMENT,
    字段名 数据类型 UNIQUE NOT NULL,
    字段名 数据类型 UNIQUE,
);

建表后

ALTER TABLE 表名称 MODIFY 字段名 数据类型 AUTO_INCREMENT;

5.4 删除自增约束

ALTER TABLE 表名称 MODIFY 字段名数据类型;

去掉AUTO_INCREMENT相当于删除。

5.5 MySQL 8.0新特性—自增变量的持久化

将id字段设置为AUTO_INCREMENT，假如前面7条记录的id是1~7，然后删除id是7的这条记录，再添加一条新记录时，这条记录的id将会是8。

没有重用被删除的7。

但是在MySQL 5.7中，若删除7后，重启数据库，再添加一条记录时，这条记录的id将会是7。

在MySQL 5.7中，对于自增主键的分配规则，是由一个计数器来决定的，而该计数器只在内存中维护，当数据库重启时，该计数器会用被初始化。用现有记录中最大值6初始化，所以下一条记录的id将会是7。

但是在MySQL 8.0中，若删除7后，重启数据库，再添加一条记录时，这条记录的id仍将会是8。

无论是否重启，此条记录的id都不会变。
MySQL 8.0将自增主键的计数器持久化到重做日志中。每次计数器发生改变，都会将其写入重做日志中（记录了id最大到了多少(最大为7)，而不是看现有记录中的最大值6，所以下次就会从8开始）。如果数据库重启，会用重做日志中的信息来初始化计数器的内存值。

6. 外键约束

6.1 什么是主表和从表

主表（父表）：被引用的表，被参考的表。

从表（子表）：引用别人的表，参考别人的表。

例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。

6.2 什么是外键

在关系型数据库中，外键（Foreign Key）是一种用于建立表与表之间关联关系的约束。外键定义了一个从表中的列（或多个列）与另一个主表的主键或唯一键之间的关系。这种关系用于维护表之间的引用完整性，确保在主表中存在相应的值。

一个表的外键必须是另一个表的主键或唯一键，因为被引用/被参考的值必须是唯一的。

6.3 特点

约束关系是针对双方的：
- 添加了外键约束后，主表的修改和删除数据受约束。
- 添加了外键约束后，从表的添加和修改数据受约束。
- 在从表上建立外键，要求主表必须存在。
- 删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除。
在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名，也可以自定义外键约束名。

6.4 添加外键约束

建表时

CREATE TABLE 主表名称(
    字段1 数据类型 PRIMARY KEY,
    字段2 数据类型
);
CREATE TABLE 从表名称(
    字段1 数据类型 PRIMARY KEY,
    字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY(从表的某个字段) REFERENCES 主表名(被参考字段) [ON UPDATE CASCADE ON DELETE RESTRICT]
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样

建表后
```
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [ON UPDATE CASCADE][ON DELETE RESTRICT];
```
一般情况下，表与表的关联都是提前设计好的。因此，会在创建表的时候就把外键约束定义好。不过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键约束，那么，就要用修改表的方式来补充定义。
CASCADE方式：在主表上UPDATE/DELETE记录时，同步UPDATE/DELETE掉从表匹配的记录。
RESTRICT方式：在主表上UPDATE/DELETE记录时，若从表有匹配的记录，则不允许此操作。
对于外键约束，最好是采用： ON UPDATE CASCADE ON DELETE RESTRICT的方式。
若不显式指定，默认为RESTRICT方式。

6.5 删除外键约束

#第一步：先查看约束名，并删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看外键约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
#第二步：查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;

6.6 为什么不用MySQL的外键约束

在MySQL里，外键约束开销大。所以对于具有高并发SQL操作的业务，不适合使用外键约束。而且，MySQL允许不使用系统自带的外键约束，而是在应用层面(在Java程序中进行限定 )完成检查数据一致性的逻辑。

7. CHECK约束

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

7.1 关键字

CHECK

7.2 特点

MySQL5.7 可以使用CHECK约束，但CHECK约束对数据验证没有任何作用。添加数据时，没有任何错误或警告。CHECK约束在MySQL 8.0.16正式生效。

7.3 添加CHECK约束

建表时

CREATE TABLE employee(
    eid INT PRIMARY KEY,
    ename VARCHAR(5),
    gender CHAR CHECK ((gender = '男') OR (gender = '女')) #列级约束写法
);

#表级约束写法
CREATE TABLE Persons (
    ID INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Age INT,
    City VARCHAR(255),
    [CONSTRAINT CHK_Person] CHECK (Age>=18 AND City='Sandnes')
);

建表后

ALTER TABLE 表名
ADD [CONSTRAINT 约束名] CHECK (约束表达式);

7.4 删除CHECK约束

ALTER TABLE 表名 DROP CHECK 约束名;

8. DEFAULT约束

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

8.1 关键字

DEFAULT

8.2 添加DEFAULT约束

建表时

#唯一键和主键列上一般不加默认值约束。
CREATE TABLE 表名称(
    字段名 数据类型 PRIMARY KEY,
    字段名 数据类型 UNIQUE KEY NOT NULL,
    字段名 数据类型 UNIQUE KEY,
    字段名 数据类型 NOT NULL DEFAULT 默认值,
);

建表后

ALTER TABLE 表名称 MODIFY 字段名 数据类型 DEFAULT 默认值;

8.3 删除DEFAULT约束

ALTER TABLE 表名称 MODIFY 字段名数据类型 ;

9. 总结

AUTO_INCREMENT、DEFAULT、NOT NULL中途添加和删除都用MODIFY。

以上约束都只是列级约束，没有表级约束的写法。

使用MODIFY添加和删除NOT NULL和DEFAULT的注意事项：

#添加时
#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了
ALTER TABLE 表名称 MODIFY 字段名 数据类型 DEFAULT 默认值; #不保留NOT NULL
ALTER TABLE 表名称 MODIFY 字段名 数据类型 DEFAULT 默认值 NOT NULL; #保留NOT NULL

#删除时，若有非空约束和默认值约束，删除其中一个，则需显式写出另一个。
ALTER TABLE 表名称 MODIFY 字段名 数据类型 ;#删除默认值约束，也不保留非空约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL; #删除默认值约束，保留非空约束

而对于主键约束、唯一约束、外键约束、CHECK约束，中途添加用ADD，删除用DROP。

此三个约束（主键约束、唯一约束、CHECK约束）有列级约束和表级约束（2种写法）。

二、视图

1. 什么是视图

视图是一种虚拟表，本身是不存储数据的，占用很少的内存空间。

视图建立在已有表的基础上，这些表被称为基表。

当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。

删除视图本身，不会导致基表中数据的删除。

向视图提供数据的语句为SELECT语句，可以将视图理解为存储起来的SELECT语句。

视图的应用场景：针对于小型项目，不推荐使用视图。针对于大型项目，可以考虑使用视图。

2. 创建视图

在CREATE VIEW语句中嵌入子查询

完整版

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] #不指定默认为UNDEFINED
VIEW 视图名称[(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]

精简版（常用）

CREATE VIEW 视图名称
AS 查询语句

2.1 创建单表视图

#确定视图字段名的方式1：查询语句中字段的别名会作为视图的字段名
CREATE VIEW vu_80
AS
SELECT employee_id id, last_name `name`, salary s 
FROM employees
WHERE department_id = 80;

#确定视图字段名的方式2：通过视图名后的字段列表命名
CREATE VIEW emp_year_salary(ename,year_salary) #字段列表中的字段个数与SELECT语句字段格式相同
AS
SELECT ename,salary*12*(1+IFNULL(commission_pct,0))
FROM t_employee;

2.2 创建多表联合视图

CREATE VIEW vu_emp
AS  #视图的字段会去掉“e.”和“d.”，即显示department_name，而不是d.department_name
SELECT e.employee_id emp_id,e.last_name `name`,d.department_name 
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

CREATE VIEW emp_depart
AS
SELECT CONCAT(last_name,'(',department_name,')') AS emp_dept  
FROM employees e JOIN departments d
ON e.department_id = d.department_id

2.3 基于视图创建视图

只是把SELECT语句中FROM的对象换成了另一个视图。

CREATE VIEW vu_emp4
AS
SELECT employee_id,last_name
FROM vu_emp4;

SELECT * FROM vu_emp4; #视图是一张虚拟表，也可以用SELECT查询。

3. 查看视图

查看数据库有哪些表和视图
SHOW TABLES FROM 数据库;
查看视图的结构（常用）
DESC 视图名;
展现的内容和DESC 表名;类似。
查看视图的属性信息
SHOW TABLE STATUS LIKE '视图名称';
显示数据表的存储引擎、版本、数据行数和数据大小等。
执行结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表。
查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;

4. 更新视图的数据

MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。

语法和表的INSERT、UPDATE和DELETE的一样，只是FROM的对象变成了视图。

例子：

UPDATE vu_tel  
SET tel = '13789091234' 
WHERE ename = '孙洪亮';

DELETE 
FROM vu_tel 
WHERE ename = '孙洪亮';

更新视图数据也存在失败的情况，例如：在定义视图的SELECT语句后的字段列表中使用DISTINCT 、聚合函数、GROUP BY、HAVING等，视图将不支持INSERT、UPDATE、DELETE。但是，这些情况并不需要了解。
因为视图作为虚拟表，主要用于 方便查询，不建议更新视图的数据。对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。

5. 修改、删除视图

5.1 修改视图

5.1.1 方式1

OR REPLACE是一个可选的关键字，用于在创建视图时指定如果该对象已经存在，则替换该视图。

CREATE OR REPLACE VIEW empvu80(id_number, name, sal, department_id) 
AS  #OR REPLACE
SELECT employee_id, CONCAT(first_name,last_name), salary, department_id
FROM employees
WHERE department_id = 80;

5.1.2 方式2

把CREATE VIEW换成ALTER VIEW。

ALTER VIEW 视图名称
AS
查询语句

5.2 删除视图

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

DROP VIEW IF EXISTS 视图名称1,视图名称2,视图名称3,...;

说明：基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败。这样的视图c需要手动删除或修改，否则影响使用。

6. 视图的优缺点

6.1 优点

操作简单
将经常使用的查询操作定义为视图，可以使开发人员无需关心视图对应的数据表的结构、表与表之间的关联关系，也不需要关心数据表之间的业务逻辑和查询条件。
减少数据冗余
视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。
数据安全
对权限不同的用户，展现不同的视图，这可以保护一些敏感字段。
适应灵活多变的需求
当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较大，可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。

6.2 缺点

维护问题
- 若基表的结构发生变化（比如删除字段），就需要及时对相关的视图进行维护。
- 嵌套视图的维护会变得比较复杂，可读性不好，容易变成系统的潜在隐患。
- 实际项目中，如果视图过多，会导致数据库维护成本的问题。

三、存储过程与存储函数

前面学习的单行函数和聚合函数相当于库函数，而存储函数相当于用户自定义函数。

存储过程可以没有返回值，而存储函数必须有返回值。

1. 存储过程概述

1.1 理解

存储过程的英文是Stored Procedure 。它的思想很简单，就是一组经过预先编译的SQL语句的封装。

执行过程：存储过程预先存储在MySQL服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列SQL语句全部执行。

和视图、函数的对比：

视图是虚拟表 ，使用时一般只涉及查询操作，而存储过程是程序化的SQL语句，使用时可以涉及增删改查，实现更复杂的数据处理。

一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是没有返回值的。

有没有返回值是看RETURN语句。

1.2 分类

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：

没有参数（无参数无返回）。
仅仅带IN类型（有参数无返回）。
仅仅带OUT类型（无参数有返回）
既带IN又带OUT（有参数有返回）
带INOUT（有参数有返回）

4和5的区别是：INOUT只有一个变量，而IN和OUT是两个变量。

2. 创建存储过程

2.1 创建存储过程的语法

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型, ...)
[characteristics ...]
BEGIN
存储过程执行体
END

说明：

IN、OUT、INOUT的含义
- IN：当前参数为输入参数。
  存储过程只是读取这个参数的值。
  参数种类有IN、OUT、INOUT。
  如果没有定义参数种类，默认就是IN 。
- OUT：当前参数为输出参数。
  执行完成之后，调用存储过程的客户端或者应用程序可以读取这个参数返回的值了。
- INOUT：当前参数既可以为输入参数，也可以为输出参数。
形参类型可以是MySQL数据库中的任意数据类型（前面所学的INT、CHAR那些）。
characteristics表示对存储过程的约束条件，其取值信息如下：
```
LANGUAGE SQL
| [NOT] DETERMINISTIC   # “|”代表或，{}里的内容，我估计是多选一。
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
```
- LANGUAGE SQL：说明存储过程执行体是由SQL语句组成的。
- [NOT] DETERMINISTIC：指明存储过程执行的结果是否确定。
  确定：每次执行存储过程时，相同的输入会得到相同的输出。
  不确定的：每次执行存储过程时，相同的输入可能得到不同的输出。
  不指定时，默认为NOT DETERMINISTIC，当有RAND()、时间相关操作时，可能会使用NOT DETERMINISTIC。
- { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用SQL语句的限制。
  - CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
    例如：SET @x = 1;
    不指定时，默认为CONTAINS SQL。
  - NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
  - READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
    读数据是指：SELECT语句。
  - MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
    包含增删改的SQL语句。
- SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
  - DEFINER表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
  - INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
  不指定时，默认为DEFINER。
- COMMENT 'string'：注释信息，可以用来描述存储过程。
存储过程执行体可以有多条SQL语句，如果仅仅一条SQL语句，则可以省略BEGIN和END。
- BEGIN…END：BEGIN…END中间可以包含多个语句，每个语句都以（;）号为结束符。
- DECLARE：DECLARE用来声明变量，在BEGIN…END中使用，而且需要在其他语句使用之前进行变量的声明。
- SET：赋值语句，用于对变量进行赋值。
- SELECT 字段1,字段2... INTO 变量1,变量2...：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
需要设置新的结束标记。
```
DELIMITER 新的结束标记
```
DELIMITER是“定界符”的意思。
因为SQL语句结束符号默认为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。
```
DELIMITER $ #将结束符改成‘$’，且无需以‘;’结尾。

CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM emps;
END $

DELIMITER ;  #存储过程定义结束后，将结束符改成‘;’
```
如果使用的是Navicat可视化工具，那么在编写存储过程的时候，Navicat会自动设置DELIMITER 为其他符号，我们不需要再进行DELIMITER的操作。

2.2 例子

2.2.1 无参类型

#创建存储过程avg_employee_salary()，返回所有员工的平均工资
DELIMITER //

CREATE PROCEDURE avg_employee_salary ()
BEGIN
SELECT AVG(salary) AS avg_salary FROM emps;
END //

DELIMITER ;

#调用存储过程 
CALL avg_employee_salary ();

2.2.2 OUT类型

#创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出
DELIMITER //

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE) #变量用什么数据类型接收，要看表中对应字段用的什么数据类型
BEGIN
SELECT MIN(salary) INTO ms FROM emps;
#SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
END //

DELIMITER ;

#调用存储过程
CALL show_min_salary(@ms); #只有一个@的变量是用户自定义变量

#查看变量的值
SELECT @ms;

2.2.3 IN类型

#创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名
DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
SELECT salary FROM emps WHERE ename = empname;
END //
DELIMITER ;

#调用方式1
CALL show_someone_salary('Abel');
#调用方式2
SET @empname = 'Abel';
CALL show_someone_salary(@empname);

2.2.4 IN、OUT类型

#创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资。
DELIMITER //

CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE)
BEGIN
SELECT salary INTO empsalary FROM emps WHERE ename = empname;
END //

DELIMITER ;

SET @empname = 'Abel';
CALL show_someone_salary2(@empname,@empsalary);

SELECT @empsalary; #查看@empsalary的值

2.2.5 INOUT类型

#创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，输出领导的姓名。
DELIMITER //

CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
BEGIN
SELECT ename INTO empname FROM emps
WHERE eid = (SELECT MID FROM emps WHERE ename=empname);
END //

DELIMITER ;

SET @empname = 'Abel';
CALL show_mgr_name(@empname);
SELECT @empname; #查看@empname的值

3. 调用存储过程

存储过程有多种调用方法。存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，例如CALL 数据库名.存储过程名(参数)。

3.1 无参调用格式

格式：CALL 存储过程名();

例子：CALL avg_employee_salary ();

3.2 IN类型调用格式

格式：CALL 存储过程名(IN的参数);

例子：CALL show_someone_salary('Abel');

参数也可以以变量的形式传进去。
SET @empname = 'Abel';
CALL show_someone_salary(@empname);

3.3 OUT类型调用格式

格式：CALL 存储过程名(OUT的参数);

例子：

#调用存储过程
CALL show_min_salary(@ms); #只有一个@的变量是用户自定义变量
#查看变量的值
SELECT @ms;

3.4 INOUT类型调用格式

格式：CALL 存储过程名(INOUT的参数);

例子：

SET @empname = 'Abel';
CALL show_mgr_name(@empname);
SELECT @empname; #查看@empname的值

4. 存储函数的使用

4.1 创建存储函数

语法格式：

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
函数体 #函数体中肯定有 RETURN 语句
END

说明：

参数列表：FUNCTION的参数无需加IN、OUT、INOUT，但理解成IN(即传入的参数的值不会发生变化)。
RETURNS type语句指定了函数返回值的类型。
函数必须要有RETURNS type语句，且函数体必须包含一个RETURN value语句。
characteristic创建函数时指定对函数的约束。
取值与创建存储过程时相同，这里不再赘述。
函数体也用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END。

4.2 调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的，都是使用SELECT 函数命名(实参列表)。

4.3 例子

#1.创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为字符串型。
DELIMITER //

CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //

DELIMITER ;

#调用存储函数
SELECT email_by_name();

#2.创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，并返回，数据类型为字符串型。
DELIMITER //

CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE employee_id = emp_id);
END //

DELIMITER ;
#调用存储函数
SET @emp_id = 102;
SELECT email_by_id(@emp_id);

若在创建存储函数中报错“you might want to use the less safe log_bin_trust_function_creators variable”，有两种处理方法：
加上必要的函数特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”
使用此条命令（SET GLOBAL log_bin_trust_function_creators = 1;）后，再创建存储函数。

4.4 存储过程和存储函数的对比

5. 存储过程和存储函数的查看、修改、删除

5.1 查看

使用SHOW CREATE语句查看存储过程和函数的创建信息。
```
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名;
```
使用SHOW STATUS语句查看存储过程和函数的状态信息。
```
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE '存储过程名或函数名']
```
这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。
[LIKE '存储过程名或函数名']：只展示匹配的存储过程或函数，可以省略。当省略不写时，会列出数据库中存在的所有的存储过程或函数的信息。

从information_schema.Routines表中查看存储过程和函数的信息。

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名'[AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}]; #{}代表多选一

#例子
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='count_by_id' AND ROUTINE_TYPE = 'FUNCTION';

如果存在存储过程和函数同名的情况，最好指明ROUTINE_TYPE查询条件来指明查询的是存储过程还是函数。

5.2 修改

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

其中，characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同（少了LANGUAGE SQL | [NOT] DETERMINISTIC）。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

例子：

#修改存储函数CountProc的定义。将读写权限改为READS SQL DATA，并加上注释信息“FIND NAME”。
ALTER FUNCTION CountProc
READS SQL DATA
COMMENT 'FIND NAME'

5.3 删除

删除存储过程和函数，可以使用DROP语句，其语法结构如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名;

6. 存储过程的优点和缺点

6.1 优点

存储过程可以一次编译多次使用，存储过程只在创建时进行编译，之后的使用都不需要重新编译。
存储过程的安全性强，在定义存储过程的时候可以设置对用户的使用权限。
将存储过程预先存储在MySQL服务器，可以减少网络传输量。

6.2 缺点

可移植性差，在MySQL、Oracle和SQL Server里编写的存储过程，在换成其他数据库时都需要重新编写。
调试困难。
存储过程的版本管理困难。比如数据表索引发生变化了，可能会导致存储过程失效。
不适合高并发的场景。

7. 注意事项

无论是存储过程还是存储函数，都需要将步骤1、2作为一条SQL整体执行，不能先执行1，再执行2。

DELIMITER $ #将结束符改成‘$’，且无需以‘;’结尾。步骤1

CREATE PROCEDURE select_all_data() #步骤2
BEGIN
SELECT * FROM emps;
END $

DELIMITER ;  #存储过程定义结束后，将结束符改成‘;’，步骤3

第4部分

一、变量、流程控制与游标

1. 变量

在MySQL数据库的存储过程和存储函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。

在 MySQL 数据库中，变量分为系统变量以及用户自定义变量 。

1.1 系统变量

由系统定义的变量，属于服务器层面。启动MySQL服务，生成MySQL实例期间，MySQL将为系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。

1.1.1 系统变量分类

系统变量分为全局系统变量（需要添加GLOBAL关键字）以及会话系统变量（需要添加SESSION关键字）。

全局系统变量也称为全局变量。如果不写关键字，默认是会话系统变量。
在MySQL服务启动时，服务器自动为全局变量赋默认值，这些默认值可以在my.ini文件中更改。
全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话。
会话变量仅针对于当前会话（连接）有效。会话1对某个会话变量值的修改，不会影响会话2中同名的会话变量的值。
会话1对某个全局变量值的修改，会影响会话2中此全局变量的值。

每一个MySQL客户端成功连接MySQL服务器后，都会产生对应的会话。会话期间，MySQL服务实例会在MySQL服务器中生成与该会话对应的会话变量，其中许多会话变量的初始值是对同名全局变量值的复制。

MySQL客户端是指用于与MySQL数据库服务器进行通信的应用程序或工具。它允许用户连接到MySQL数据库，执行查询、插入、更新和删除数据，以及管理数据库结构和权限等操作。MySQL客户端可以是命令行工具，也可以是图形用户界面（GUI）工具。
部分会话变量与全局变量重名。采用就近原则，同名变量的值优先取会话变量的值。
在MySQL中，有些系统变量只能是全局的，例如max_connections用于限制服务器的最大连接数；有些系统变量作用域既可以是全局又可以是会话，例如character_set_client用于设置客户端的字符集；有些系统变量的作用域只能是当前会话，例如pseudo_thread_id用于标记当前会话的MySQL连接ID。

1.1.2 查看系统变量

查看所有系统变量

#查看所有全局变量
SHOW GLOBAL VARIABLES;

#查看所有会话变量
SHOW SESSION VARIABLES;

#不添加GLOBAL/SESSION关键词，默认为SESSION
SHOW VARIABLES;

查看部分系统变量

#查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%变量名%';

#查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%变量名%';

#例子
SHOW GLOBAL VARIABLES LIKE 'admin_%';

查看指定的系统变量

#查看指定的系统变量的值
SELECT @@global.变量名;

#查看指定的会话变量的值
SELECT @@session.变量名;

SELECT @@变量名; #这种方法首先查看会话系统变量，如果会话系统变量不存在，则查找全局系统变量。

1.1.3 修改系统变量的值

修改MySQL配置文件，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）。

在MySQL服务运行期间，使用“SET”命令重新设置系统变量的值。

修改方式2只针对当前的数据库实例有效，一旦重启MySQL服务，就失效了。

#为某个全局变量赋值
#方式1：
SET @@global.变量名=变量值;
#方式2：
SET GLOBAL 变量名=变量值;

#为某个会话变量赋值
#方式1：
SET @@session.变量名=变量值;
#方式2：
SET SESSION 变量名=变量值;

1.2 用户自定义变量

1.2.1 用户变量分类

用户变量是用户自己定义的。根据作用范围不同，又分为会话用户变量（以 一个“@” 开头）和局部变量 。

会话用户变量：作用域为当前会话。

局部变量：只在BEGIN和END语句块中有效，只能在存储过程和存储函数中使用。

1.2.2 会话用户变量

声明变量

在会话的任意位置都可以声明。

#方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;

#方式2：“:=” 或 INTO关键字,将表中字段的值赋给变量
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];

查看用户变量的值

SELECT @用户变量;

SELECT @big; #查看某个未声明的变量时，将得到NULL值

举例

SET @a = 1;
SELECT @a;

SELECT @num := COUNT(*) FROM employees;
SELECT @num;

SELECT AVG(salary) INTO @avgsalary FROM employees;
SELECT @avgsalary;

1.2.3 局部变量

声明变量

使用DECLARE声明
在BEGIN...END中使用
在BEGIN...END的前几行声明

DECLARE 变量名 类型 [default 值]; # 如果没有DEFAULT子句，初始值为NULL

变量赋值

#方式1：赋常量
SET 变量名=值;
SET 变量名:=值;

#方法2：赋表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;

在BEGIN...END中查看变量
```
SELECT 局部变量名;
```

2. 定义条件与处理程序（用于BEGIN...END中）

定义条件是事先定义程序执行过程中可能遇到的问题，处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。

相当于JAVA中的异常处理。

说明：定义条件和处理程序在存储过程、存储函数中都是支持的。

2.1 案例分析

创建一个名称为“UpdateDataNoCondition”的存储过程。代码如下：

DELIMITER //

CREATE PROCEDURE UpdateDataNoCondition()
    BEGIN
        SET @x = 1;
        UPDATE employees SET email = NULL WHERE last_name = 'Abel'; #emial为非空字段，会报错
        SET @x = 2;
        UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
        SET @x = 3;
    END //

DELIMITER ;

在存储过程中未定义条件和处理程序，且当存储过程中执行的SQL语句报错时，MySQL数据库会抛出错误，并退出当前SQL逻辑，不再向下继续执行。

2.2 定义条件（不是必须的操作）

定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。

它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中。

 #方式1
 DECLARE 错误名称 CONDITION FOR 数值错误码;
 
 #方式2
 DECLARE 错误名称 CONDITION FOR SQLSTATE '字符串错误码';

错误码的说明：

MySQL_error_code 和sqlstate_value都可以表示MySQL的错误。
- MySQL_error_code是数值类型错误代码。
- sqlstate_value是长度为5的字符串类型错误代码。
例如，在ERROR 1418 (HY000)中，1418是MySQL_error_code，'HY000'是sqlstate_value。

举例1：定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应。

#使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;

#使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

2.3 定义处理程序

可以为某种类型的错误定义处理程序。定义处理程序时，使用DECLARE语句的语法如下：

DECLARE 处理方式 HANDLER FOR 错误类型 处理语句;

处理方式：有3个取值：CONTINUE、EXIT、UNDO。

CONTINUE：表示遇到错误不处理，继续执行。
EXIT：表示遇到错误马上退出。
：表示遇到错误后撤回之前的操作。
MySQL中暂时不支持UNDO。

错误类型的取值：

SQLSTATE '字符串错误码'：sqlstate_value类型的错误代码；
MySQL_error_code：数值类型错误代码；
错误名称 ：表示DECLARE ... CONDITION定义的错误条件名称。
SQLWARNING：匹配所有以01开头的sqlstate_value错误代码；
NOT FOUND：匹配所有以02开头的sqlstate_value错误代码；
SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value错误代码；

处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像“SET @变量 = 值 ”这样的简单语句，也可以是使用BEGIN ... END编写的复合语句。

例子：

#方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
#方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
#方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

2.4 案例解决

在存储过程中，定义处理程序，当遇到MySQL_error_code值为1048时，执行CONTINUE操作，并且将@proc_value的值设置为-1。

DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
    BEGIN
    #定义处理程序
        DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1;
        SET @x = 1;
        UPDATE employees SET email = NULL WHERE last_name = 'Abel';
        SET @x = 2;
        UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
        SET @x = 3;
    END //
DELIMITER ;

3. 流程控制（用于BEGIN...END中）

流程控制语句的作用就是控制存储过程中 SQL 语句的执行顺序。

流程就分为三大类：

顺序结构：程序从上往下依次执行。
分支结构：程序按条件进行选择执行，从多条路径中选择一条执行。
循环结构：满足一定条件下，重复执行一组语句。

MySQL的流程控制语句主要有3类。

条件判断语句：IF语句和CASE语句
循环语句：LOOP、WHILE和REPEAT语句
跳转语句：ITERATE和LEAVE语句

注意：只能用于BEGIN...END中。前面学的流程控制函数是在SELECT语句中使用的。

3.1 分支结构之IF

IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF;

例子：声明存储过程“update_salary_by_eid2”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于8000元并且入职时间超过5年，就涨薪500元；否则就涨薪100元。

DELIMITER //

CREATE PROCEDURE update_salary_by_eid2(IN emp_id INT)
    BEGIN
        DECLARE emp_salary DOUBLE;
        DECLARE hire_year DOUBLE;
        SELECT salary,DATEDIFF(CURDATE(),hire_date)/365 INTO emp_salary,hire_year 
        FROM employees 
        WHERE employee_id = emp_id;
        IF emp_salary < 8000 AND hire_year > 5
        THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
        ELSE
        UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
        END IF;
    END //
    
DELIMITER ;

3.2 分支结构之CASE

存储过程和存储函数里的CASE结尾是“END CASE”，而流程控制函数里的CASE语句以END结尾。

3.2.1 情况一：类似于SWITCH

CASE 表达式
WHEN 值1 THEN 语句1;
WHEN 值2 THEN 语句2;
...
ELSE 语句n;
END CASE;

3.2.2 类似于多重IF

CASE
WHEN 条件1 THEN 语句1;
WHEN 条件2 THEN 语句2;
...
ELSE 语句n;
END CASE;

3.2.3 例子

声明存储过程update_salary_by_eid5，定义IN参数emp_id，输入员工编号。判断该员工的入职年限，如果是0年，薪资涨50；如果是1年，薪资涨100；如果是2年，薪资涨200；如果是3年，薪资涨300；如果是4年，薪资涨400；其他的涨薪500。

DELIMITER //
    CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT)
    BEGIN
        DECLARE emp_sal DOUBLE;
        DECLARE hire_year DOUBLE;
        SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
        SELECT ROUND(DATEDIFF(CURDATE(),hire_date)/365) INTO hire_year FROM employees
        WHERE employee_id = emp_id;
        CASE hire_year
        WHEN 0 THEN UPDATE employees SET salary=salary+50 WHERE employee_id = emp_id;
        WHEN 1 THEN UPDATE employees SET salary=salary+100 WHERE employee_id = emp_id;
        WHEN 2 THEN UPDATE employees SET salary=salary+200 WHERE employee_id = emp_id;
        WHEN 3 THEN UPDATE employees SET salary=salary+300 WHERE employee_id = emp_id;
        WHEN 4 THEN UPDATE employees SET salary=salary+400 WHERE employee_id = emp_id;
        ELSE UPDATE employees SET salary=salary+500 WHERE employee_id = emp_id;
        END CASE;
    END //
DELIMITER ;

3.3 循环结构之LOOP

LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程。

LOOP语句的基本格式如下：

[loop_label:] LOOP
	循环体
END LOOP [loop_label];

其中，loop_label是此循环的标签名，该参数可以省略。

例子：

DELIMITER //
    CREATE PROCEDURE test_loop()
    BEGIN
        DECLARE id INT DEFAULT 0;
        add_loop:LOOP
            SET id = id +1;
            IF id >= 10 THEN LEAVE add_loop;
            END IF;
        END LOOP add_loop;
    END //
DELIMITER ;

3.4 循环结构之WHILE

WHILE语句创建一个带条件判断的循环过程。

[while_label:] WHILE 循环条件 DO
	循环体
END WHILE [while_label];

3.5 循环结构之REPEAT

REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT循环首先会执行一次循环，然后在UNTIL中判断是否满足条件，如果满足条件就退出，不满足则继续循环。

执行循环，直到满足条件。

[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式 END REPEAT [repeat_label];

例子：

DELIMITER //
CREATE PROCEDURE test_repeat()
    BEGIN
        DECLARE i INT DEFAULT 0;
        REPEAT
            SET i = i + 1;
        UNTIL i >= 10 END REPEAT;
        SELECT i;
    END //
DELIMITER ;

3.6 三种循环结构对比

这三种循环都可以省略名称，但如果循环中添加了跳转语句（LEAVE或ITERATE）则必须添加标签名。
LOOP：一般用于实现简单的"死"循环；WHILE：先判断后执行；REPEAT：先执行后判断，无条件至少执行一次

3.7 跳转语句之LEAVE语句

LEAVE语句：可以用在循环语句内，或者在BEGIN...END内使用，表示跳出循环或者跳出BEGIN...END。

相当于JAVA中的break。
在BEGIN之前可以加标签名，以供LEAVE使用。标签名:BEGIN

LEAVE 标签名;

例子：

DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
    begin_label: BEGIN
        IF num<=0
        	THEN LEAVE begin_label; #则使用LEAVE语句退出BEGIN...END
        ELSEIF num=1
        	THEN SELECT AVG(salary) FROM employees;
        ELSEIF num=2
        	THEN SELECT MIN(salary) FROM employees;
        ELSE
        	SELECT MAX(salary) FROM employees;
        END IF;
        SELECT COUNT(*) FROM employees;
    END //
DELIMITER ;

3.8 跳转语句之ITERATE语句

ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。

相当于JAVA中的continue。

ITERATE 标签名;

例子：

DELIMITER //
CREATE PROCEDURE test_iterate()
    BEGIN
        DECLARE num INT DEFAULT 0;
        my_loop:LOOP
            SET num = num + 1;
            IF num < 10
                THEN ITERATE my_loop;
            ELSEIF num > 15
                THEN LEAVE my_loop;
            END IF;
            SELECT '尚硅谷：让天下没有难学的技术';
        END LOOP my_loop;
    END //
DELIMITER ;

4. 游标（用于BEGIN...END中）

4.1 游标的作用

在 SQL 中，游标是一种临时的数据库对象，是可以指向数据行的指针，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作。

游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。
MySQL中游标可以在存储过程和函数中使用。

4.2 使用游标

声明先后顺序：声明变量和条件 ==> 声明处理程序 ==> 声明游标

4.2.1 声明游标

在MySQL中，使用DECLARE关键字来声明游标，其语法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement;
#这里select_statement代表的是SELECT语句，返回一个用于创建游标的结果集。
#此时还没有开始遍历数据。

这个语法适用于MySQL，SQL Server，DB2和MariaDB。

如果是用Oracle或者PostgreSQL，需要写成：

DECLARE cursor_name CURSOR IS select_statement;

例子：

DECLARE cur_emp CURSOR FOR
SELECT employee_id,salary FROM employees;

4.2.2 打开游标

定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的语法如下：

OPEN cursor_name;

一打开游标，SELECT语句的查询结果集就会送到游标工作区，为后面游标的逐条读取结果集中的记录做准备。

例子：

OPEN cur_emp;

4.2.3 使用游标（从游标中取得数据）

语法格式：

FETCH cursor_name INTO var_name [, var_name];

作用是使用cursor_name这个游标来读取当前行，并且将数据保存到var_name这个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。
注意：
var_name必须在声明游标之前就定义好。
游标的查询结果集中的字段数，必须跟INTO后面的变量数一致，否则，在存储过程执行的时候，MySQL 会提示错误。

例子：

FETCH cur_emp INTO emp_id, emp_sal ;

4.2.4 关闭游标

CLOSE cursor_name;

因为游标会占用系统资源，所以使用完游标后需要关闭游标。

例子：

CLOSE cur_emp;

4.3 例子

创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary(DOUBLE类型)；声明OUT参数total_count(INT类型)。功能是实现累加薪资最高的几个员工的薪资值，直到薪资总和达到limit_total_salary参数的值，返回累加的人数给total_count。

DELIMITER //
    CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT
    total_count INT)
        BEGIN
            DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资
            DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值
            DECLARE emp_count INT DEFAULT 0; #记录循环个数
            #定义游标
            DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
            #打开游标
            OPEN emp_cursor;
            REPEAT
                #使用游标（从游标中获取数据）
                FETCH emp_cursor INTO cursor_salary;
                SET sum_salary = sum_salary + cursor_salary;
                SET emp_count = emp_count + 1;
            UNTIL sum_salary >= limit_total_salary END REPEAT;
            SET total_count = emp_count;
            #关闭游标
            CLOSE emp_cursor;
        END //
DELIMITER ;

4.4 游标的小结

游标是 MySQL 的一个重要的功能，为逐条读取结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储过程和存储程序中使用，效率高，程序也更加简洁。

但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。

建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率。

5. 补充：MySQL 8.0的新特性—全局变量的持久化

在MySQL数据库中，全局变量可以通过SET GLOBAL语句来设置。例如，设置服务器语句超时的限制，可以通过设置系统变量max_execution_time来实现：

使用SET GLOBAL语句设置的变量值只会临时生效。数据库服务重启后，服务器又会从MySQL配置文件中读取变量的默认值。 MySQL 8.0版本新增了SET PERSIST命令。例如，设置服务器的最大连接数为1000：

SET PERSIST max_connections = 1000; #不能加GLOBAL，只能修改全局变量

MySQL会将该命令的配置保存到数据目录下的mysqld-auto.cnf文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。

RESET PERSIST;可以清空mysqld-auto.cnf文件中的内容，那么重启MySQL服务就能将变量恢复到默认值。

二、触发器

1. 触发器概述

MySQL的触发器和存储过程一样，都是存储在MySQL服务器的一段程序。

触发器是由增删改事件来触发某个操作。

2. 触发器的创建

2.1 创建触发器的语法

CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;

说明：

表名：表示触发器监控的对象。
BEFORE|AFTER：表示触发的时间。
- BEFORE：表示在事件之前触发。
- AFTER：表示在事件之后触发。
INSERT|UPDATE|DELETE：表示触发的事件。
- INSERT表示插入记录时触发。
- UPDATE：表示更新记录时触发。
- DELETE：表示删除记录时触发。
FOR EACH ROW：每操作成功一行就会触发一次；不写的话，表示是表级触发器，则无论操作多少行，都只触发一次。
触发器执行的语句块：可以是单条SQL语句，也可以多条SQL语句。若是多条SQL，则写在BEGIN…END中。

2.2 例子

定义触发器“salary_check_trigger”，基于员工表“employees”的INSERT事件，在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资，如果大于领导薪资，则报sqlstate_value为'HY000'的错误，从而使得添加失败。

DELIMITER //
CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees FOR EACH ROW  
BEGIN
    DECLARE mgrsalary DOUBLE;                                       
    SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id;
    IF NEW.salary > mgrsalary 
    	THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误'; #报错语法
    END IF;
END //
DELIMITER ;

DECLARE salary_error CONDITION FOR SQLSTATE 'HY000';
给错误代码取名字，原句可以改写为THEN SIGNAL salary_error SET MESSAGE_TEXT = '薪资高于领导薪资错误';
当触发条件是INSERT语句时，新插入的那条记录就是NEW。可以使用NEW.字段名取得新记录的字段。
当触发条件是DELETE语句时，删除的那条记录就是OLD。可以使用OLD.字段名取得新记录的字段。
当触发条件是UPDATE语句时，修改前的那条记录为OLD，修改后的那条记录为NEW。取字段的方法同理。

3. 查看、删除触发器

3.1 查看触发器

查看当前数据库的所有触发器的定义
SHOW TRIGGERS;
查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名;
从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息
SELECT * FROM information_schema.TRIGGERS;

3.2 删除触发器

DROP TRIGGER IF EXISTS 触发器名称;

4. 触发器的优缺点

4.1 触发器的优点

触发器可以帮助我们记录“操作日志”。
触发器还可以用在操作数据前，对数据进行合法性检查。

4.2 触发器的缺点

触发器最大的一个问题就是可读性差。
表结构的变更，都可能会导致触发器出错。

5. 注意事项

如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的UPDATE和DELETE语句定义的触发器并不会被激活。

间接操作表并不会引起触发器。

三、MySQL8其他特性

1. 新特性1：窗口函数

窗口函数的作用类似于在查询中对数据进行分组（GROUP BY），不同的是，GROUP BY每个分组只会显示一条记录，而窗口函数的每个分区会显示全部记录。

1.1 窗口函数分类

窗口函数可以分为静态窗口函数和动态窗口函数。

静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同；
动态窗口函数的窗口大小会随着记录的不同而变化。

1.2 语法结构

窗口函数的语法结构是：

窗口函数 OVER([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC ROWS BETWEEN 开始行 AND 结束行])

OVER关键字指定函数窗口的范围。
- 如果省略后面括号中的内容，则把所有的行当作一个分区。
- 如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口。
PARTITION BY子句：指定窗口函数按照哪些字段进行分区。分区后，可以在每个分区中分别执行窗口函数。
ORDER BY子句：分区内按照字段排序。
ROWS BETWEEN 开始行 AND 结束行用于指定窗口函数的取值范围，开始行和结束行的取值如下：
- UNBOUNDED PRECEDING：窗口从数据表的第一行开始。
- UNBOUNDED FOLLOWING：窗口到数据表的最后一行结束。
- CURRENT ROW：窗口包括当前行。
- n PRECEDING：窗口从当前行向前n行开始，n为整数。
- n FOLLOWING：窗口从当前行向后n行结束，n为整数。
  ROWS BETWEEN 1 PRECEDING AND CURRENT ROW表示上一行到当前行。
  ROWS BETWEEN 开始行 AND 结束行可以省略。
  当ORDER BY后面缺少窗口范围，窗口规范默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
  当ORDER BY和窗口范围都缺失, 窗口规范默认是ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

1.3 分类讲解

1.3.1 序号函数

ROW_NUMBER()函数：对分区的数据按从小到大编号，序号不会重复。
例子1：查询goods数据表中每个商品分类下价格降序排列的各个商品信息。
```
SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,id, category_id, category, `name`, price, stock
FROM goods;
```
例子2：查询goods数据表中每个商品分类下价格最高的3种商品信息。
```
SELECT *
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num,id, category_id, category, `name`, price, stock
FROM goods) t
WHERE row_num <= 3;
```
注意：在使用ROW_NUMBER() OVER函数时候，先执行WHERE，再执行OVER()中的分区以及排序，毕竟窗口函数在SELECT语句中。
所以想取得每个商品分类下价格最高3种，则需要先在FROM中使用子查询，然后在用WHERE。
RANK()函数：对分区的数据按从小到大编号，序号可以重复。
例子1：使用RANK()函数获取goods数据表中各类别的价格从高到低排序的各商品信息。
```
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,id, category_id, category, `name`, price, stock
FROM goods;
```
DENSE_RANK()函数：对分区的数据按从小到大编号，序号可以重复。

ROW_NUMBER()：对于每个分区，序号都不会重复，比如1、2、3、4（即便2、3的排序字段相同）。
RANK()：对于每个分区，序号可以重复、但会跳过某些序号，比如1、2、2、4（并列第二、但跳过了3）。
DENSE_RANK()：对于每个分区，序号可以重复、但不会跳过某些序号，比如1、2、2、3（并列第二、并且也没有跳过3）。
这三个函数编号都是从1开始。

1.3.2 分布函数

PERCENT_RANK()函数：对分区的每条记录A，计算小于记录A的RANK值的记录数占总记录-1的比例。
计算方式：(rank - 1) / (rows - 1)
其中，rank的值为使用RANK()函数产生的序号，rows的值为某个分区的记录数。
例子1：计算goods数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值。
```
SELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,
PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,
id, category_id, category, `name`, price, stock
FROM goods
WHERE category_id = 1;
```
比如序号为1的前面没有RANK比它小（除去它自己，还有5个值，0/5 = 0），序号为2的前面有1个RANK比它小（除去它自己，还有5个值，1/5 = 0.2）。
CUME_DIST()函数：对分区的每条记录A，计算小于或等于记录A的NEW_ROW_NUMBER的记录数占总记录的比例。
计算方式：NEW_ROW_NUMBER/rows
对于排序字段相同的记录，取这些记录中的最大的ROW_NUMBER作为NEW_ROW_NUMBER。
rows的值为某个分区的记录数，ROW_NUMBER是指用ROW_NUMBER()得到的序号。
如果按升序排列，则统计：小于等于当前值的行数/总行数。
如果是降序排列，则统计：大于等于当前值的行数/总行数。
例子1：查询goods数据表中小于或等于当前价格的比例。
```
SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,id, category, NAME, price
FROM goods;
```
排序字段为price，有两条89.90的字段，一条的ROW_NUMBER为4，另一条的ROW_NUMBER为5，则NEW_ROW_NUMBER为5，5/6恰好是0.833333333...

1.3.3 前后函数

LAG(expr,n)函数：返回当前行之前的第n行expr字段的值。

例子：查询goods数据表中前一个商品价格与当前商品价格的差值。

SELECT id, category, `name`, price, pre_price, price - pre_price AS diff_price
FROM (
SELECT id, category, `name`, price,LAG(price,1)OVER(PARTITION BY category_id ORDER BY price) AS pre_price
FROM goods
) t;

LAG(price,1)：上1行的price字段的值。

LEAD(expr,n)函数：返回当前行之后的第n行expr字段的值。

1.3.4 首尾函数

FIRST_VALUE(expr)函数：返回分区中第一条记录expr字段的值。
LAST_VALUE(expr)函数：返回分区中最后一条记录expr字段的值。
例子：按照价格排序，查询每个品种最后一个商品的价格信息。
```
SELECT id, category, NAME, price, stock,LAST_VALUE(price) OVER(PARTITION BY category_id ORDER BY price rows between unbounded preceding and unbounded following) AS last_price
FROM goods;
```
LAST_VALUE(expr)默认的统计范围是rows between unbounded preceding and current row，也就是取当前行之前（包括当前行）的数据，那LAST_VALUE(expr)返回的是当前行的expr字段，和不使用这个函数没区别。
所以需要改成between unbounded preceding and unbounded following，这样就是取每个分区的最后一个记录的expr字段。

1.3.5 其他函数

NTH_VALUE(expr,n)函数：返回分区中第n行的expr字段的值。如果指定的行不存在，此函数将返回 NULL。
```
SELECT id, category, NAME, price, stock,NTH_VALUE(price,3) OVER(PARTITION BY category_id ORDER BY price) AS last_price
FROM goods;
```
默认的统计范围是rows between unbounded preceding and current row，也就是取当前行之前（包括当前行）的数据，则第1~N-1行理论上都会返回NULL，但是也可能出现1~N-1行不NULL的情况，那就是当1~N-1行中expr字段取值和第N行相同的情况。
```
SELECT id, category, NAME, price, stock,NTH_VALUE(price,4) OVER(PARTITION BY category_id ORDER BY price) AS last_price
FROM goods; #理论上第四行才不为NULL
```
改成between unbounded preceding and unbounded following，这样就是取每个分区的第n条记录的expr字段。
NTH_VALUE(expr, 1) 等效于FIRST_VALUE(expr)。
注意：
NTILE(n)函数：将每个分区中的数据分为n个桶，记录桶编号。
比如每个分区有6个数据，使用NTILE(3)，则会有3个桶，其6条记录的编号为1、1、2、2、3、3。

1.3.6 聚合函数

聚合函数max()， min()，sum()，count() ，avg()作为窗口函数使用的时候，需要注意以下事项：

需要在括号中指定聚合的字段；
当聚合函数只用了partition by，没使用order by时，聚合函数是按照每个分区直接计算最终结果；
```
SELECT id, category, NAME, price,
SUM(price) OVER(PARTITION BY category_id) AS third_price
FROM goods;
```
当聚合函数同时使用partition by和order by时，聚合函数按照每个分区然后从第一行到当前行计算结果，比如当前行是第三行，求SUM(price) OVER(PARTITION BY category_id ORDER BY price) AS third_price，其结果是第一行的price + 第二行的price+ 第三行的price。
```
SELECT id, category, NAME, price,
SUM(price) OVER(PARTITION BY category_id ORDER BY price) AS third_price
FROM goods;
```
SUM(price) OVER()才是窗口函数中的聚合函数，而SUM(price)只是普通的聚合函数。

2. 新特性2：公用表表达式

公用表表达式（或通用表表达式）简称为CTE（Common Table Expressions），CTE是一个命名的临时结果集。

CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。

这里的复用是指可以在一个查询中多次引用。

公用表表达式分为普通公用表表达式和递归公用表表达式2种。

2.1 普通公用表表达式

普通公用表表达式的语法结构是：

WITH CTE名称
AS (子查询)
一条 SELECT|DELETE|UPDATE 语句;

#例子
WITH
cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

例子：查询有员工的部门的详细信息。

WITH emp_dept_id
AS (	SELECT DISTINCT department_id 
    	FROM 
    	employees)
SELECT *
FROM departments d JOIN emp_dept_id e
ON d.department_id = e.department_id;

2.2 递归公用表表达式

递归公用表表达式除了普通公用表表达式的特点以外，它还有自己调用自己的特点。它的语法结构是：

WITH RECURSIVE CTE名称 
AS (子查询)
一条 SELECT|DELETE|UPDATE 语句;

RECURSIVE的中文意思：递归的。

递归公用表表达式由2部分组成，分别是种子查询和递归查询，中间通过关键字UNION [ALL]进行连接。

这里的种子查询，意思就是获得递归的初始值。这个查询只会运行一次，以创建初始数据集。递归查询会一直执行，直到没有任何新的查询数据产生，递归返回。

例子：针对于我们常用的employees表，包含employee_id，last_name和manager_id三个字段。如果a是b的管理者，那么把b叫做a的下属，如果同时b又是c的管理者，那么c就是b的下属，也是a的下下属。

查询所有具有下下属身份的人员信息（假如老大是董事长，老二是总经理，我们要查询的就是所有非董事长和总经理的员工）。

如果用递归公用表表达式，就非常简单了。具体的思路如下：

用递归公用表表达式中的种子查询，找出初代管理者。字段n表示代次，初始值为 1，表示是第一代管理者。
用递归公用表表达式中的递归查询，查出以这个递归公用表表达式中的人为管理者的人，并且代次的值加 1。直到没有人以这个递归公用表表达式中的人为管理者了，递归返回。
在最后的查询中，选出所有代次大于等于3的人，他们肯定是第三代及以上代次的下属了，也就是下下属了。这样就得到了我们需要的结果集。

WITH RECURSIVE cte
AS
(    #cte的字段名为employee_id,last_name,manager_id,n（与第一个SELECT字段相同）
	SELECT employee_id,last_name,manager_id,1 AS n 
	FROM employees 
    WHERE employee_id = 100
-- 种子查询，找到第一代领导，得到初始cte
UNION ALL
	SELECT e.employee_id,e.last_name,e.manager_id,n+1 
    FROM employees AS e JOIN cte 
	ON (e.manager_id = cte.employee_id)  #用上一轮的cte得到新的cte，然后用新的cte再执行，最后UNION ALL不同轮得到的cte，并不是边执行边UNION ALL。
)
SELECT employee_id,last_name FROM cte WHERE n >= 3; #选出所有代次大于等于3的人，这里的cte是最终的结果集，是各个中间结果集的并集。

递归查询没有显式的递归终止条件，只有当递归子查询返回空结果集（没有数据行返回）或是超出了递归次数的最大限制时，才停止递归。