Mysql

MuYi

Mysql

数据库概念

为什么要使用数据库

持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以“固化”，而持久化的实现过程大多通过各种关系数据库来完成。 持久化的主要作用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数据文件中。

数据库与数据库管理系统

    DB：数据库(Database)
    即存储数据的“仓库”，其本质是一个文件系统。它保存了一系列有组织的数据。
    DBMS：数据库管理系统(Database Management System)
    是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数	  据库管理系统访问数据库中表内的数据。
    SQL：结构化查询语言（Structured Query Language）
    专门用来与数据库通信的语言。

RDBMS与非RDBMS

关系型数据库(RDBMS)
实质
    这种类型的数据库是最古老的数据库类型，关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。
    关系型数据库以行(row)和列(column)的形式存储数据，以便于用户理解。这一系列的行和列被称为表(table)，一组表组成了一个库(database)。
    SQL就是关系型数据库的查询语言。
优势
    复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
    事务支持使得对于安全性能很高的数据访问要求得以实现。
非关系型数据库(非RDBMS)
介绍
非关系型数据库，可看成传统关系型数据库的功能阉割版本，基于键值对存储数据，不需要经过SQL层的解析，性能非常高。同时，通过减少不常用的功能，进一步提高性能。

关系型数据库设计规则

一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
表具有一些特性，这些特性定义了数据在表中如何存储，类似Java和Python中 “类”的设计。
表、记录、字段
    E-R（entity-relationship，实体-联系）模型中有三个主要概念是：实体集、属性、联系集。
    一个实体集（class）对应于数据库中的一个表（table），一个实体（instance）则对应于数据库表中的一行（row），也称为一条记录（record）。一个属性（attribute）对应于数据库表中的一列（column），也称为一个字段（field）。
表的关联关系
    表与表之间的数据记录有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。
    四种：一对一关联、一对多关联、多对多关联、自我引用

mysql环境

MySQL的编码设置

MySQL5.7中步骤1：查看编码命令

show variables like 'character_%';
show variables like 'collation_%';

步骤2：修改mysql的数据目录下的my.ini配置文件

default-character-set=utf8 #默认字符集 [mysqld]
# 大概在76行左右，在其下添加
...
character-set-server=utf8
collation-server=utf8_general_ci

步骤3：重启服务

步骤4：查看编码命令

show variables like 'character_%';
show variables like 'collation_%';

MySQL 8.0开始，数据库的默认编码改为utf8mb4

基本SELECT语句

SQL分类

DDL:数据定义语言。CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE
DML:数据操作语言。INSERT \ DELETE \ UPDATE \ SELECT （重中之重）
DCL:数据控制语言。COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE

SQL语言的规则与规范

基本规则

SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
每条命令以 ; 或 \g 或 \G 结束
关键字不能被缩写也不能分行
关于标点符号

必须保证所有的()、单引号、双引号是成对结束的
必须使用英文状态下的半角输入方式
字符串型和日期时间类型的数据可以使用单引号（’ '）表示
列的别名，尽量使用双引号（" "），而且不建议省略as

SQL大小写规范

MySQL在Windows环境下是大小写不敏感的
MySQL在Linux环境下是大小写敏感的
数据库名、表名、表的别名、变量名是严格区分大小写的
关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
推荐采用统一的书写规范：
数据库名、表名、表别名、字段名、字段别名等都小写
SQL 关键字、函数名、绑定变量等都大写

注 释

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

数据导入指令

在命令行客户端登录mysql，使用source指令导入

mysql> source d:\mysqldb.sql

基本的SELECT语句

SELECT…

SELECT 1; #没有任何子句
SELECT 1 + 1,3 * 2 FROM DUAL; #dual：伪表

SELECT 标识选择哪些列 FROM 标识从哪个表中选择

SELECT *  FROM departments;

SELECT department_id, location_id FROM departments;

列的别名

重命名一个列
便于计算
紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
AS 可以省略
建议别名简短，见名知意

SELECT last_name AS name, commission_pct comm 
FROM employees;

SELECT last_name "Name", salary*12 "Annual Salary"
FROM employees;

去除重复行

SELECT DISTINCT department_id,salary FROM employees;

注意

DISTINCT需要放到所有列名的前面，如果写成SELECT salary, DISTINCT department_id FROM employees会报错。 DISTINCT其实是对后面所有列名的组合进行去重。如果你想要看都有哪些不同的部门（department_id），只需要写DISTINCT department_id即可，后面不需要再加其他的列名了

distinct不会过滤掉null值，返回结果包含null值

count（）会过滤掉null项

SELECT COUNT(DISTINCT PRODUCT_NAME,COUNTRY) FROM psur_list

二：使用group by

空值参与运算

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

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

在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的

显示表结构

DESCRIBE employees;
或
DESC employees;

过滤数据

SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件

SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90;

运算符

算术运算符

1
1

加法与减法运算符结论:

    一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
    一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
    加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；
    在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）

乘法与除法运算符结论:

    一个数乘以整数1和除以整数1后仍得原数；
    一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；
    一个数除以整数后，不管是否能除尽，结果都为一个浮点数；
    一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
    乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
    在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。

# 取模运算： % mod
SELECT 12 % 3,12 % 5, 12 MOD -5,-12 % 5,-12 % -5
FROM DUAL; # 结果的符号只与被模数有关
+--------+--------+-----------+---------+----------+
| 12 % 3 | 12 % 5 | 12 MOD -5 | -12 % 5 | -12 % -5 |
+--------+--------+-----------+---------+----------+
|      0 |      2 |         2 |      -2 |       -2 |
+--------+--------+-----------+---------+----------+

比较运算符

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

2
2

等号运算符

等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0。
在使用等号运算符时，遵循如下规则：

如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。

安全等于运算符（为NULL而生）

安全等于运算符（<=>）与等于运算符（=）的作用是相似的， 唯一的区别是‘<=>’可以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，而不为NULL。

不等于运算符

不等于运算符（<>和!=）用于判断两边的数字、字符串或者表达式的值是否不相等，如果不相等则返回1，相等则返回0。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL，或两边都为NULL，则结果为NULL。

空运算符

空运算符（IS NULL或者ISNULL）判断一个值是否为NULL，如果为NULL则返回1，否则返回0。

非空运算符

非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。

最小值运算符

语法格式为：LEAST(值1，值2，…，值n)。其中，“值n”表示参数列表中有n个值。在有两个或多个参数的情况下，返回最小值。

当参数是整数或者浮点数时，LEAST将返回其中最小的值；当参数为字符串时，返回字母表中顺序最靠前的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。

最大值运算符

语法格式为：GREATEST(值1，值2，…，值n)。其中，n表示参数列表中有n个值。当有两个或多个参数时，返回值为最大值。假如任意一个自变量为NULL，则GREATEST()的返回值为NULL。

当参数中是整数或者浮点数时，GREATEST将返回其中最大的值；当参数为字符串时，返回字母表中顺序最靠后的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。

BETWEEN AND运算符

BETWEEN运算符使用的格式通常为SELECT D FROM TABLE WHERE C BETWEEN A AND B，此时，当C大于或等于A，并且C小于或等于B时，结果为1，否则结果为0。

IN运算符

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

NOT IN运算符

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

LIKE运算符

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

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

LIKE运算符通常使用如下通配符

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

#练习：查询last_name中包含字符'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';
#练习：查询last_name中以字符'a'开头的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE 'a%';
#练习：查询第3个字符是'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

ESCAPE

回避特殊符号的：使用转义符。
如果使用\表示转义，要省略ESCAPE。如果不是\，则要加上ESCAPE。
#练习：查询第2个字符是_且第3个字符是'a'的员工信息
#需要使用转义字符: \ 
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%';
#或者  (了解)
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';

REGEXP运算符

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

（1）‘^’匹配以该字符后面的字符开头的字符串。
（2）‘$’匹配以该字符前面的字符结尾的字符串。
（3）‘.’匹配任何一个单字符。
（4）“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一 个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
（5）‘*’匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的‘x’字符，“[0-9]*”匹配任何数量的数字， 而“*”匹配任何数量的任何字符。

逻辑运算符

4
4

逻辑非运算符

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

逻辑与运算符

逻辑与（AND或&&）运算符是当给定的所有值均为非0值，并且都不为NULL时，返回1；当给定的一个值或者多个值为0时则返回0；否则返回NULL。

逻辑或运算符

逻辑或（OR或||）运算符是当给定的值都不为NULL，并且任何一个值为非0值时，则返回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为NULL时，返回NULL。

注意：OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。

逻辑异或运算符

逻辑异或（XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。

位运算符

5
5

按位与运算符

按位与（&）运算符将给定值对应的二进制数逐位进行逻辑与运算。当给定值对应的二进制位的数值都为1时，则该位返回1，否则返回0。

按位或运算符

按位或（|）运算符将给定的值对应的二进制数逐位进行逻辑或运算。当给定值对应的二进制位的数值有一个或两个为1时，则该位返回1，否则返回0。

按位取反运算符

按位取反（~）运算符将给定的值的二进制数逐位进行取反操作，即将1变为0，将0变为1。

按位右移运算符

按位右移（>>）运算符将给定的值的二进制数的所有位右移指定的位数。右移指定的位数后，右边低位的数值被移出并丢弃，左边高位空出的位置用0补齐。

按位左移运算符

按位左移（<<）运算符将给定的值的二进制数的所有位左移指定的位数。左移指定的位数后，左边高位的数值被移出并丢弃，右边低位空出的位置用0补齐。

排序与分页

排序数据 1排序规则

使用 ORDER BY 子句排序

ASC（ascend）:升序
DESC（descend）:降序
ORDER BY子句在SELECT语句的结尾。
# 单列排序
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary; # 如果在ORDER BY 后没有显式指名排序的方式的话，则默认按照升序排列。
# 我们可以使用列的别名，进行排序
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal;
# 多列排序
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;

可以使用不在SELECT列表中的列排序。
在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

2.分页

1实现规则

MySQL中使用LIMIT实现分页
格式：
偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推）；第二个参数“行数”指示返回的记录条数。
-- 前10条记录： 
SELECT * FROM 表名 LIMIT 0,10; 
或者
SELECT * FROM 表名 LIMIT 10;
-- 第11至20条记录： 
SELECT * FROM 表名 LIMIT 10,10;
-- 第21至30条记录： 
SELECT * FROM 表名 LIMIT 20,10;
MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;”返回的结果相同。
分页显式公式：*（当前页数-1）每页条数，每页条数
注意：LIMIT子句必须放在整个SELECT语句的最后！

多表查询

1.笛卡尔积

（或交叉连接）的理解，为了避免笛卡尔积， 可以在WHERE加入有效的连接条件。

#查询员工姓名和所在部门名称 
SELECT last_name,department_name FROM employees,departments; 
SELECT last_name,department_name FROM employees CROSS JOIN departments;

2.多表查询分类

区分重复的列名

多个表中有相同列时，必须在列名之前加上表名前缀
使用别名可以简化查询。
列名前使用表名前缀可以提高查询效率

需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。

连接n个表,至少需要n-1个连接条件。

#非等值连接的例子
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`;

自连接vs非自连接

#自连接的例子：
#练习：查询员工id,员工姓名及其管理者的id和姓名
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp ,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;

内连接 vs 外连接

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表

基本语法

使用JOIN…ON子句创建连接的语法结构

INNER JOIN

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

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

左外连接(LEFT OUTER JOIN)

SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

右外连接(RIGHT OUTER JOIN)

SELECT 字段列表 
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

UNION的使用

UNION操作符

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

UNION ALL操作符

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

果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

7种SQL JOINS的实现

6
6

自然连接

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;
# NATURAL JOIN : 它会帮你自动查询两张连接表中`所有相同的字段`，然后进行`等值连接`。
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING连接

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
# 指定数据表里的同名字段进行等值连接，只能配合JOIN一起使用。
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

单行函数

7
7
8
8
9
9
10
10
11
11
12
12
13
13
14
14
15
15

聚合函数

1 AVG和SUM函数

mysql> SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
    -> FROM employees
    -> WHERE job_id LIKE '%REP%';
+-------------+-------------+-------------+-------------+
| AVG(salary) | MAX(salary) | MIN(salary) | SUM(salary) |
+-------------+-------------+-------------+-------------+
| 8272.727273 |    11500.00 |     6000.00 |   273000.00 |
+-------------+-------------+-------------+-------------+

1.2 MIN和MAX函数

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

mysql> SELECT MIN(hire_date), MAX(hire_date)
    -> FROM employees;
+----------------+----------------+
| MIN(hire_date) | MAX(hire_date) |
+----------------+----------------+
| 1987-06-17     | 2000-04-21     |
+----------------+----------------+

1.3 COUNT函数

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

mysql> SELECT COUNT(*)
    -> FROM employees
    -> WHERE department_id = 50;
+----------+
| COUNT(*) |
+----------+
|       45 |
+----------+

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

mysql> SELECT COUNT(commission_pct)
    -> FROM employees
    -> WHERE department_id = 50;
+-----------------------+
| COUNT(commission_pct) |
+-----------------------+
|                     0 |
+-----------------------+

用count(*)，count(1)，count(列名)

MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。

nnodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。

count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

2. GROUP BY

可以使用GROUP BY子句将表中的数据分成若干组

SELECT column, group_function(column)
FROM table 
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

明确：WHERE一定放在FROM后面

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

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

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

SELECT AVG(salary) 
FROM employees
GROUP BY department_id;

使用多个列分组

SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;

GROUP BY中使用WITH ROLLUP

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

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

注意：

当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

HAVING

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

非法使用聚合函数 ： 不能在WHERE子句中使用聚合函数。

mysql> SELECT department_id, AVG(salary)
    -> FROM employees
    -> WHERE AVG(salary) > 8000
    -> GROUP BY department_id;
ERROR 1111 (HY000): Invalid use of group function

结论：

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

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

WHERE和HAVING的对比

16
16

SELECT的执行过程

#方式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执行顺序

关键字的顺序是不能颠倒

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

SELECT 语句的执行顺序

#FROM ...,...-> ON -> (LEFT/RIGNT  JOIN) -> 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 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

SQL的执行原理

ELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
添加外部行。如果我们使用的是左连接、右连接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。

当我们拿到了查询数据表的原始数据，也就是最终的虚拟表vt1，就可以在此基础上再进行WHERE阶段 。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表vt2。

然后进入第三步和第四步，也就是GROUP和HAVING阶段 。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表vt3和vt4。

当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到SELECT和DISTINCT阶段 。

首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1和vt5-2。

当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是ORDER BY阶段 ，得到虚拟表vt6。

最后在 vt6 的基础上，取出指定行的记录，也就是LIMIT阶段 ，得到最终的结果，对应的是虚拟表vt7。

当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。

同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

子查询

#方式一：
SELECT salary
FROM employees
WHERE last_name = 'Abel';

SELECT last_name,salary
FROM employees
WHERE salary > 11000;

#方式二：自连接
SELECT e2.last_name,e2.salary 
FROM employees e1,employees e2 
WHERE e1.last_name = 'Abel' 
AND e1.`salary` < e2.`salary`

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

子查询的基本使用

子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用 。
注意事项

子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询，多行操作符对应多行子查询

子查询的分类

分类方式1：

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

分类方式2：

我们按内查询是否被执行多次，将子查询划分为​​相关(或关联)子查询​​​和​​不相关(或非关联)子查询​​。
17
17
SELECT last_name, job_id, salary 
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees
);

HAVING中的子查询

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
);

CASE中的子查询

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;

子查询中的空值问题

mysql> SELECT last_name, job_id
    -> FROM employees
    -> WHERE job_id = (
    ->     SELECT job_id
    ->     FROM employees
    ->     WHERE last_name = 'Haas'
    -> );
Empty set (0.01 sec)

子查询不返回任何行

非法使用子查询

mysql> SELECT employee_id, last_name
    -> FROM employees
    -> WHERE salary = ( # 多行子查询使用单行比较符
    ->     SELECT MIN(salary)
    ->     FROM employees
    ->     GROUP BY department_id
    -> );
ERROR 1242 (21000): Subquery returns more than 1 row

多行子查询

18
18
#方式1：
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 
    ) dept_avg_sal 
);

#方式2：
SELECT department_id 
FROM employees 
GROUP BY department_id
HAVING AVG(salary) <= ALL (
    SELECT AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id 
);

MySQL中聚合函数是不能嵌套使用的

相关子查询

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

SELECT last_name, salary, department_id
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);


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`;

在ORDER BY 中使用子查询：

SELECT employee_id,salary
FROM employees e
ORDER BY (
    SELECT department_name 
    FROM departments d
    WHERE e.`department_id` = d.`department_id` 
);

结论：在SELECT中，除了GROUP BY 和 LIMIT之外，其他位置都可以声明子查询！

EXISTS与NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
如果在子查询中不存在满足条件的行：

条件返回 FALSE
继续在子查询中查找

如果在子查询中存在满足条件的行：

不在子查询中继续查找
条件返回 TRUE

NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

方式一：

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
);

方式二：自连接

SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2 
WHERE e1.employee_id = e2.manager_id;

方式三：

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

SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT *
    FROM employees e
    WHERE d.`department_id` = e.`department_id`
);

许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多

创建和管理表

创建数据库

CREATE DATABASE 数据库名;

创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

断数据库是否已经存在，不存在则创建数据库

CREATE DATABASE IF NOT EXISTS 数据库名;

注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的

使用数据库

查看当前所有的数据库

SHOW DATABASES; #有一个S，代表多个数据库

查看当前正在使用的数据库

SELECT DATABASE(); #使用的一个 mysql 中的全局函数

查看指定库下所有的表

SHOW TABLES FROM 数据库名;

查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
或者： 
SHOW CREATE DATABASE 数据库名\G

使用/切换数据库

USE 数据库名;

修改数据库

更改数据库字符集

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

删除数据库

DROP DATABASE 数据库名;
DROP DATABASE IF EXISTS 数据库名;

创建表 必须具备：CREATE TABLE权限

CREATE TABLE [IF NOT EXISTS] 表名( 
    字段1 数据类型 [约束条件] [默认值], 
    字段2 数据类型 [约束条件] [默认值], 
    字段3 数据类型 [约束条件] [默认值], 
    ……
    [表约束条件] 
);

使用 AS subquery 选项，将创建表和插入数据结合起来

CREATE TABLE table [(column, column...)]
AS subquery;

指定的列和子查询中的列要一一对应 通过列名和默认值定义列

CREATE TABLE emp1 AS SELECT * FROM employees; 

CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表

查看数据表结构

SHOW CREATE TABLE 表名\G

修改表

追加一个列

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

修改一个列

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

重命名一个列

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

删除一个列

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

重命名表

RENAME TABLE emp TO myemp;

ALTER table dept
RENAME [TO] detail_dept; -- [TO]可以省略

删除表

RUNCATE TABLE语句：

删除表中所有的数据
释放表的存储空间

TRUNCATE TABLE detail_dept;

TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚 COMMIT:提交数据。一旦执行COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。 ROLLBACK:回滚数据。一旦执行ROLLBACK,则可以实现数据的回滚。回滚到最近的一次COMMIT之后。

SET autocommit = FALSE;

DELETE FROM emp2;
#TRUNCATE TABLE emp2; 

SELECT * FROM emp2;
ROLLBACK;
SELECT * FROM emp2;

DDL 和 DML 的说明

DDL的操作一旦执行，就不可回滚。指令SET autocommit = FALSE对DDL操作失效。(因为在执行完DDL操作之后，一定会执行一次COMMIT。而此COMMIT操作不受SET autocommit = FALSE影响的。)
DML的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行DML之前，执行了 SET autocommit = FALSE，则执行的DML操作就可以实现回滚。

数据处理之增删改

插入数据

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

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

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 目标表名 (tar_column1 [, tar_column2, …, tar_columnn]) 
SELECT (src_column1 [, src_column2, …, src_columnn]) 
FROM 源表名
[WHERE condition]

在 INSERT 语句中加入子查询。 不必书写VALUES子句。 子查询中的值列表应与 INSERT 子句中的列名对应。

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%';

更新数据

UPDATE table_name 
SET column1=value1, column2=value2, … , column=valuen 
[WHERE condition]

删除数据

DELETE FROM table_name [WHERE <condition>];

MySQL 8.0中新增列计算，

CREATE TABLE 和 ALTER TABLE 中都支持增加计算列

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);

查询数据表tb1中的数据，结果如下：

mysql> SELECT * FROM tb1;
+------+------+------+------+
| id   | a    | b    | c    |
+------+------+------+------+
| NULL |  100 |  200 |  300 |
+------+------+------+------+

更新数据中的数据，语句如下：

mysql> UPDATE tb1 SET a = 500;
mysql> SELECT * FROM tb1;
+------+------+------+------+
| id   | a    | b    | c    |
+------+------+------+------+
| NULL |  500 |  200 |  700 |
+------+------+------+------+

MySQL数据类型

19
19
20
20
DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。
定点数在MySQL内部是以 字符串 的形式进行存储，这就决定了它一定是精准的。
当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。

TIMESTAMP和DATETIME的区别：
    TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
    底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
    两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
    TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。
    一般存注册时间、商品发布时间等，不建议使用DATETIME存储，而是使用​​时间戳​​，因为DATETIME虽然直观，但不便于计算。


CHAR 或 VARCHAR
情况1：存储很短的信息。比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个byte用于存储信息长度，本来打算节约存储的，结果得不偿失。

情况2：固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。

情况3：十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。

情况4：具体存储引擎中的情况：

    ​​MyISAM​​​数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化，从而使​​数据检索更快​​，用空间换时间。
    ​​MEMORY​​存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理的。
    InnoDB 存储引擎，建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，其他考虑varchar。这样节省空间，对磁盘I/O和数据存储总量比较好。

约束

为什么需要约束

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

约束的分类

    **根据约束数据列的限制，**约束可分为：

    单列约束：每个约束只约束一列
    多列约束：每个约束可约束多列数据

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

    列级约束：只能作用在一个列上，跟在列的定义后面
    表级约束：可以作用在多个列上，不与列一起，而是单独定义

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

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

查看某个表已有的约束

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

非空约束NOT NULL

限定某个字段/某列的值不允许为空

默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型
非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
一个表可以有很多列都分别限定了非空
空字符串’'不等于NULL，0也不等于NULL

建表时

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

建表后

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

删除非空约束

# 方式一：
ALTER TABLE 表名称 
MODIFY 字段名 数据类型 NULL; 
# 方式二：
ALTER TABLE 表名称 
MODIFY 字段名 数据类型;

唯一性约束UNIQUE

用来限制某个字段/某列的值不能重复

同一个表可以有多个唯一约束。
唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
唯一性约束允许列值为空。
在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。

添加（复合）唯一约束

建表时

CREATE TABLE 表名称( 
    字段名 数据类型, 
    字段名 数据类型 UNIQUE [KEY], 
    字段名 数据类型 
);
CREATE TABLE 表名称( 
    字段名 数据类型, 
    字段名 数据类型, 
    字段名 数据类型, 
    [CONSTRAINT 约束名] UNIQUE [KEY](字段列表)  #多个字段之间用逗号隔开
);

建表后指定唯一键约束

#字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的
#方式1： 
ALTER TABLE 表名称 
ADD [CONSTRAINT 约束名] UNIQUE [KEY](字段列表); #多个字段之间用逗号隔开
#方式2： 
ALTER TABLE 表名称 
MODIFY 字段名 字段类型 UNIQUE [KEY];

可以向声明为unique的字段上添加null值。而且可以多次添加null

删除唯一约束

添加唯一性约束的列上也会自动创建唯一索引。
删除唯一约束只能通过删除唯一索引的方式删除。
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和(字段列表)中排在第一个的列名相同。也可以自定义唯一性约束名。

ALTER TABLE 表名称
DROP INDEX 索引名;

注意：可以通过show index from 表名称;查看表的索引

PRIMARY KEY约束

用来唯一标识表中的一行记录
特点
主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值
一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
主键约束对应着表中的一列或者多列（复合主键）
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

建表时指定主键约束

create table 表名称( 
    字段名 数据类型 primary key, #列级模式 
    字段名 数据类型, 
    字段名 数据类型 
);
create table 表名称( 
    字段名 数据类型, 
    字段名 数据类型, 
    字段名 数据类型, 
    [constraint 约束名] primary key(字段列表) #表级模式，多个字段之间用逗号隔开
);

# 列级约束
CREATE TABLE emp4( 
    id INT PRIMARY KEY AUTO_INCREMENT, # 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 表名称 
ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键

删除主键约束

alter table 表名称 
drop primary key;

说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。

自增列：AUTO_INCREMENT

一个表最多只能有一个自增长列
当需要产生唯一标识符或顺序值时，可设置自增长
自增长列约束的列必须是键列（主键列，唯一键列）
自增约束的列的数据类型必须是整数类型
如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

建表时

create table 表名称( 
    字段名 数据类型 primary key auto_increment, 
    字段名 数据类型 unique key not null, 
    字段名 数据类型 unique key, 
    字段名 数据类型 not null default 默认值
);
create table 表名称( 
    字段名 数据类型 default 默认值, 
    字段名 数据类型 unique key auto_increment, 
    字段名 数据类型 not null default 默认值, 
    primary key(字段名) 
);

建表后

alter table 表名称 modify 字段名 数据类型 auto_increment;

删除自增约束

#alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束 
alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除

MySQL 8.0新特性—自增变量的持久化
在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。
在MySQL 5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。

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

FOREIGN KEY约束

限定某个表的某个字段的引用完整性。
FOREIGN KEY
主表和从表/父表和子表
主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表

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

建表时

create table 主表名称( 
    字段1 数据类型 primary key, 
    字段2 数据类型 
);
create table 从表名称( 
    字段1 数据类型 primary key, 
    字段2 数据类型, 
    [CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段) 
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列

建表后

ALTER TABLE 从表名 
ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用 字段) [on update xx][on delete xx];

总结：约束关系是针对双方的

添加了外键约束后，主表的修改和删除数据受约束
添加了外键约束后，从表的添加和修改数据受约束
在从表上建立外键，要求主表必须存在
删除主表时，要求从表先删除，或将从表中外键引用该主表的关系先删除

约束等级

Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
No action方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
Restrict方式：同no action， 都是立即检查外键约束
Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别
如果没有指定等级，就相当于Restrict方式。

对于外键约束，最好是采用:ON UPDATE CASCADE ON DELETE RESTRICT的方式

create table emp( 
    eid int primary key, #员工编号 
    ename varchar(5), #员工姓名 
    deptid int, #员工所在的部门
    foreign key (deptid) references dept(did) ON UPDATE CASCADE ON DELETE RESTRICT #把修改操作设置为级联修改等级，把删除操作设置为Restrict等级 
);

删除外键约束

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

DEFAULT约束

建表时

create table 表名称(
    字段名 数据类型 default 默认值 , 
    字段名 数据类型 not null default 默认值, 
    字段名 数据类型 not null default 默认值, 
    primary key(字段名),
    unique key(字段名) 
);
# 说明：默认值约束一般不在唯一键和主键列上加

建表后

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

删除默认值约束

alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束 
alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束，保留非空约束

视图

常见的数据库对象

21
21

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。

视图的理解

    视图是一种虚拟表，本身是不具有数据的，占用很少的内存空间，它是 SQL 中的一个重要概念。
    视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
    视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。
    向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的SELECT语句

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

    视图，是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便。

创建视图

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

精简版
CREATE VIEW 视图名称 
AS 查询语句

查看视图

语法1：查看数据库的表对象、视图对象
SHOW TABLES;

语法2：查看视图的结构
DESC / DESCRIBE 视图名称;

语法3：查看视图的属性信息
# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等） 
SHOW TABLE STATUS LIKE '视图名称'\G

语法4：查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;

更新视图的数据

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；
视图定义基于一个不可更新视图；
常量视图
视图作为虚拟表，主要用于方便查询，不建议更新视图的数据。对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。

修改视图

方式1：使用CREATE OR REPLACE VIEW 子句修改视图
方式2：ALTER VIEW

ALTER VIEW 视图名称 
AS
查询语句


视图优点
1. 操作简单

将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表与表之间的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简化了开发人员对数据库的操作。

2. 减少数据冗余

视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。

3. 数据安全

MySQL将用户对数据的访问限制在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表。

同时，MySQL可以根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接通过视图获取数据表中的信息。这在一定程度上保障了数据表中数据的安全性。

4. 适应灵活多变的需求 当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较大，可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。
5. 能够分解复杂的查询逻辑 数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。

视图不足

如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂， 可读性不好 ，容易变成系统的潜在隐患。因为创建视图的 SQL 查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。

存储过程与函数

存储过程

一组经过预先编译的 SQL 语句的封装。

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

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

1、参数前面的符号的意思
IN：当前参数为输入参数，也就是表示入参；

存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN，表示输入参数。

OUT：当前参数为输出参数，也就是表示出参；

执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。

INOUT：当前参数既可以为输入参数，也可以为输出参数。
2、形参类型可以是 MySQL数据库中的任意类型。

3、characteristics表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

LANGUAGE SQL 
| [NOT] DETERMINISTIC 
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 
| SQL SECURITY { DEFINER | INVOKER } 
| COMMENT 'string'
LANGUAGE SQL：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
[NOT] DETERMINISTIC：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。

CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
默认情况下，系统会指定为CONTAINS SQL。

SQL SECURITY { DEFINER | INVOKER }：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。

DEFINER表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。

COMMENT 'string'：注释信息，可以用来描述存储过程。
4、存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END

5、需要设置新的结束标记
DELIMITER 新的结束标记

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

调用存储过程

CALL 存储过程名(实参列表)

格式：

1、调用in模式的参数：

CALL sp1('值');

2、调用out模式的参数：

SET @name; 
CALL sp1(@name); 
SELECT @name;

3、调用inout模式的参数：

SET @name=值; 
CALL sp1(@name); 
SELECT @name;

存储函数的使用

语法格式：

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

1、参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数。

2、RETURNS type 语句表示函数返回数据的类型；

RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

3、characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。

4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END。

调用存储函数

SELECT 函数名(实参列表)

DELIMITER // 
CREATE FUNCTION count_by_id(dept_id INT) 
RETURNS INT 
  LANGUAGE SQL 
  NOT DETERMINISTIC 
  READS SQL DATA 
  SQL SECURITY DEFINER 
  COMMENT '查询部门平均工资' 
BEGIN
  RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id); 
END // 
DELIMITER ;

SET @dept_id = 50; 
SELECT count_by_id(@dept_id);

对比存储函数和存储过程

22
22

存储函数可以放在查询语句中使用，存储过程不行，存储过程的功能更加强大，包括能够执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的。

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

查看

  1. 使用SHOW CREATE语句查看存储过程和函数的创建信息
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
  1. 使用SHOW STATUS语句查看存储过程和函数的状态信息
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

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

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

修改

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。

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

其中，characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同

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

删除

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

存储过程使用的
1优点

**1、存储过程可以一次编译多次使用。**存储过程只在创建时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。

**2、可以减少开发工作量。**将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。

**3、存储过程的安全性强。**我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。

**4、可以减少网络传输量。**因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。

## **5、良好的封装性。**在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。

缺点
**1、可移植性差。**存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。

**2、调试困难。**只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。

**3、存储过程的版本管理很困难。**比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。

**4、它不适合高并发的场景。**高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护， 增加数据库的压力 ，显然就不适用了

变量、流程控制与游标

变量

系统变量分类

系统变量分为全局系统变量（需要添加global关键字）以及会话系统变量（需要添加session关键字），有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。**如果不写，默认会话级别。**静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系统变量。
全局系统变量针对于所有会话（连接）有效，但不能跨重启
会话系统变量仅针对于当前会话（连接）有效。会话期间，当前会话对某个会话系统变量值的修改，不会影响其他会话同一个会话系统变量的值。
会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改。

查看系统变量

查看所有或部分系统变量

#查看所有全局变量 
SHOW GLOBAL VARIABLES; 
#查看所有会话变量 
SHOW SESSION VARIABLES; 
#或
SHOW VARIABLES;

#查看满足条件的部分系统变量。 
SHOW GLOBAL VARIABLES LIKE '%标识符%';
#查看满足条件的部分会话变量 
SHOW SESSION VARIABLES LIKE '%标识符%';

查看指定系统变量

作为 MySQL 编码规范，MySQL 中的系统变量以​​两个“@”​​开头，其中“@@global”仅用于标记全局系统变量，“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。

#查看指定的系统变量的值 
SELECT @@global.变量名; 
#查看指定的会话变量的值 
SELECT @@session.变量名; 
#或者 
SELECT @@变量名;

修改系统变量的值

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

方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值

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

用户变量

用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL 中的用户变量以一个“@”开头。根据作用范围不同，又分为会话用户变量和局部变量。
会话用户变量：作用域和会话变量一样，只对当前连接会话有效。
局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在存储过程和函数中使用。

会话用户变量 变量的定义

#方式1：“=”或“:=” 
SET @用户变量 = 值; 
SET @用户变量 := 值; 
#方式2：“:=” 或 INTO关键字 
SELECT @用户变量 := 表达式 [FROM 等子句]; 
SELECT 表达式 INTO @用户变量 [FROM 等子句];

查看用户变量的值 （查看、比较、运算等）

SELECT @用户变量

局部变量

定义：可以使用DECLARE语句定义一个局部变量

作用域：仅仅在定义它的 BEGIN … END 中有效

位置：只能放在 BEGIN … END 中，而且只能放在第一句

BEGIN
    #声明局部变量 
    DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值]; 
    DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
    #为局部变量赋值 
    SET 变量名1 = 值; 
    SELECT 值 INTO 变量名2 [FROM 子句]; 
    #查看局部变量的值 
    SELECT 变量1,变量2,变量3; 
END

定义变量

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

变量赋值

方式1：一般用于赋简单的值

SET 变量名=值; 
SET 变量名:=值;

方式2：一般用于赋表中的字段值

SELECT 字段名或表达式 INTO 变量名 FROM 表;

使用变量（查看、比较、运算等）

SELECT 局部变量名;
23
23
定义条件与处理程序

定义条件是事先定义程序执行过程中可能遇到的问题，处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。

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

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

定义条件

DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）

定义处理程序

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

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

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

错误类型（即条件）可以有如下取值：

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

处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像“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';

流程控制

分支结构之IF

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

分支结构之CASE

CASE 语句的语法结构1：

#情况一：类似于switch 
CASE 表达式 
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号) 
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号) 
... 
ELSE 结果n或语句n(如果是语句，需要加分号) 
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

CASE 语句的语法结构2：

#情况二：类似于多重if 
CASE 
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号) 
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号) 
... 
ELSE 结果n或语句n(如果是语句，需要加分号) 
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

循环结构之LOOP

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

[loop_label:] LOOP 
  循环执行的语句 
END LOOP [loop_label]

循环结构之WHILE

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

循环结构之REPEAT

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

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

跳转语句之LEAVE语句

LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。可以把 LEAVE 理解为 break。

LEAVE 标记名

跳转语句之ITERATE语句

ITERATE label

游标

什么是游标

游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过程开发的能力

使用游标步骤

游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。

第一步，声明游标

DECLARE cursor_name CURSOR FOR select_statement;

第二步，打开游标

OPEN cursor_name

第三步，使用游标（从游标中取得数据）

FETCH cursor_name INTO var_name [, var_name] ...

注意：游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时候，MySQL 会提示错误。

第四步，关闭游标

CLOSE cursor_name

当我们使用完游标后需要关闭掉该游标。因为游标会占用系统资源 ，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。

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

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

MySQL 8.0的新特性—全局变量的持久化

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

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

SET PERSIST global max_connections = 1000;

触发器

触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE、DELETE事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

触发器的创建

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

说明：

表名：表示触发器监控的对象。
BEFORE|AFTER：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。
BEFORE|AFTER：表示触发的时间。BEFORE 表示在事件之前触发;AFTER 表示在事件之后触发。
INSERT|UPDATE|DELETE：表示触发的事件。

INSERT 表示插入记录时触发；
UPDATE 表示更新记录时触发；
DELETE 表示删除记录时触发。

DELIMITER // 
CREATE TRIGGER before_insert 
BEFORE INSERT ON test_trigger 
FOR EACH ROW 
BEGIN
  INSERT INTO test_trigger_log (t_log) 
  VALUES('before_insert'); 
END // 
DELIMITER ;

查看、删除触发器

方式1：查看当前数据库的所有触发器的定义

SHOW TRIGGERS\G

方式2：查看当前数据库中某个触发器的定义

SHOW CREATE TRIGGER 触发器名

方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。

SELECT * FROM information_schema.TRIGGERS;

删除触发器

DROP TRIGGER IF EXISTS 触发器名称;

触发器的优缺点

1优点

1、触发器可以确保数据的完整性。
2、触发器可以帮助我们记录操作日志。
3、触发器还可以用在操作数据前，对数据进行合法性检查。

2缺点

1、触发器最大的一个问题就是可读性差。
比如触发器中的数据插入操作多了一个字段，系统提示错误。可是，如果你不了解这个触发器，很可能会认为是更新语句本身的问题，或者是表的结构出了问题。
2、相关数据的变更，可能会导致触发器出错。
特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率。

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

MySQL8其它新特性

窗口函数

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

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

语法结构

窗口函数的语法结构是

函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

或者是：

函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

    OVER 关键字指定函数窗口的范围。

    如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所有满足WHERE条件的记录进行计算。
    如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口。

    窗口名：为窗口设置一个别名，用来标识窗口。
    PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行。
    ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
    FRAME子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。


SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id, category_id, category, NAME, price, stock
FROM goods;

窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表数据的基础上进行统计和排序非常有用。

公用表表达式

公用表表达式（或通用表表达式）简称为CTE（Common Table Expressions）。CTE是一个命名的临时结果集，作用范围是当前语句。CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。所以，可以考虑代替子查询。

依据语法结构和执行方式的不同，公用表表达式分为普通公用表表达式和递归公用表表达式2 种。

普通公用表表达式

WITH CTE名称 
AS （子查询） 
SELECT|DELETE|UPDATE 语句;

举例：查询员工所在的部门的详细信息。

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;

递归公用表表达式

WITH RECURSIVE 
CTE名称 AS （子查询） 
SELECT|DELETE|UPDATE 语句;

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

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

代码实现

WITH RECURSIVE cte 
AS(SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100
-- 种子查询，找到第一代领导 
UNION ALL 
SELECT a.employee_id,a.last_name,a.manager_id,n+1 FROM employees AS a JOIN cte 
ON (a.manager_id = cte.employee_id) -- 递归查询，找出以递归公用表表达式的人为领导的人 
)
SELECT employee_id,last_name FROM cte WHERE n >= 3;

Default footer