MySQL基础篇

1. 数据库概述

1.1 为什么使用数据库

数据持久化的解决方案之一。也就是数据保存到可掉电设备中

可以看出持久化的方式很多，为什么选择数据库来持久化？

数据库的优势在于它们提供了一种结构化和可管理的方式来存储、组织和检索数据

• 结构化和组织：数据库中的数据是严格按照规则和模式进行存储的，如数据类型，约束等等
• 可管理和检索：数据库提供SQL语句来对数据库进行管理和检索

1.2 数据库与数据库管理系统

DB：数据库（Database）	存储数据的“仓库”
DBMS：数据库管理系统（Database Management System）	一种操纵和管理数据库的大型软件
SQL：结构化查询语言（Structured Query Language）	与数据库通信的语言

使用SQL语句，通过DBMS来对DB进行增删改查实现对数据的管理

1.3 数据库与数据库管理系统的关系

数据库管理系统(DBMS)可以管理多个数据库（纯纯字面意思：管理）

上常见的数据库管理软件有Oracle、MySQL、MS SQL Server、DB2、PostgreSQL、Access、Sybase、Informix这几种

Oracle 更适合大型跨国企业的使用，因为他们对费用不敏感，但是对性能要求以及安全性有更高的要求。

MySQL 由于其体积小、速度快、总体拥有成本低，可处理上千万条记录的大型数据库，尤其是开放源码这一特点，使得很多互联网公司、中小型网站选择了MySQL作为网站数据库（Facebook，Twitter，YouTube，阿里巴巴/蚂蚁金服，去哪儿，美团外卖，腾讯）。

1.4 RDBMS 与 非RDBMS

关系型数据库和非关系型数据库。毋庸置疑RDBMS是绝对主流，如Oracle，Mysql等

1.4.1 RDBMS

关系型数据库，把复杂的数据结构归结为简单的二元关系，也就是二维表格

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

优势：

• 复杂查询 可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
• 事务支持 使得对于安全性能很高的数据访问要求得以实现

1.4.2 非RDBMS

非关系型数据库，字面意思，除了关系型数据库，其余数据库统称为非关系型数据库

NoSQL数据库支持，包括键值对，文档等多种数据模型。由于NoSQL通常采用简单数据模型和分布式设计，因此具有较高的性能，能满足大规模数据存储和高并发访问需求

我们常用NoSQL数据库指非关系型数据库，包括了键值型数据库、文档型数据库、搜索引擎和列存储、图形数据库。

1. 键值型数据库

   键值型数据库通过 Key-Value 键值的方式来存储数据，其中 Key 和 Value 可以是简单的对象，也可以是复杂的对象。Key 作为唯一的标识符，优点是查找速度快，在这方面明显优于关系型数据库，缺点是无法像关系型数据库一样使用条件过滤（比如 WHERE），如果你不知道去哪里找数据，就要遍历所有的键，这就会消耗大量的计算。

   键值型数据库典型的使用场景是作为 内存缓存 。 Redis 是基于kv键值对的内存数据库

   

2. 文档型数据库

   此类数据库可存放并获取文档，可以是XML、JSON等格式。在数据库中文档作为处理信息的基本单位，一个文档就相当于一条记录。文档数据库所存放的文档，就相当于键值数据库所存放的“值”。MongoDB是最流行的文档型数据库。此外，还有CouchDB等

3. 搜索引擎数据库

   虽然关系型数据库采用了索引提升检索效率，但是针对全文索引效率却较低。搜索引擎数据库是应用在搜索引擎领域的数据存储形式，由于搜索引擎会爬取大量的数据，并以特定的格式进行存储，这样在检索的时候才能保证性能最优。核心原理是“倒排索引”。

   典型产品：Elasticsearch

1.5 RDBMS中基本概念

实体集（class）对应于数据库中的表（table），实体（instance）对应于数据库表中的行（row），也称为一条记录（record）。一个属性（attribute）对应于数据库表中的一列（column），也称为一个字段（field）。

ORM思想 (Object Relational Mapping)对象关系模型体现：
数据库中的表 <---> Java的类
表中的一条数据 <---> 类中的一个对象（或实体）
表中的一个列 <----> 类中的一个字段、属性(field)

这是JDBC的思想了----

2. MySQL环境搭建

MySQL环境搭建

3. SQL语句

• SQL 有两个重要的标准，分别是 SQL92 和 SQL99，它们分别代表了 92 年和 99 年颁布的 SQL 标准，我们今天使用的 SQL 语言依然遵循这些标准

• 不同的数据库生产厂商都支持SQL语句，但都有特有内容

  

3.1 SQL分类

• DDL（Data Definition Languages、数据定义语言）

  **DDL用于定义数据库结构或模式，包括创建、修改和删除数据库对象（如表、视图、索引等）的语句。**主要的语句关键字包括 CREATE 、 DROP 、 ALTER、TRUNCATE、COMMENT等

• DML（Data Manipulation Language、数据操作语言）

  DML用于对数据库增删改查的语句。主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。SELECT是SQL语言的基础，最为重要。

• DCL（Data Control Language、数据控制语言）

  DCL用于控制数据库访问权限和安全性的语句。主要的语句关键字包括 GRANT 、 REVOKE 等。

3.2 SQL语言规则和规范

3.2.1 SQL规则

• SQL 可以写在一行或者多行。推荐各子句分行写，提高可读性
• 每条命令以 ; 或 \g 或 \G 结束
• 关于标点符号
  • 必须保证所有的()、单引号、双引号是成对结束的
  • 必须使用英文状态下的半角输入
  • 字符串型和日期时间类型的数据可以使用单引号（ ）表示
  • 列的别名，尽量使用双引号（" "），而且不建议省略as

3.2.2 SQL大小写规范

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

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

  数据库名、表名、表的别名、变量名是严格区分大小写的

  关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。

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

• 数据库名、表名、表别名、字段名、字段别名等都小写
• SQL 关键字、函数名、绑定变量等都大写

3.2.3 注释

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

3.2.4 命名规则

• 数据库、表名不得超过30个字符，变量名限制为29个
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符（包含英文字母、数字和下划线）
• 数据库名、表名、字段名等对象名中间不要包含空格（废话，字符都不包含空格）
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名（废话）
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用 `` `（着重号）引起来

3.2.5 数据导入指令

mysql> source d:\mysqldb.sql

3.3 基本select

select 列名(*指代全部列) from 表名 where 过滤条件

3.3.1 列的别名

在select语句的列名后，紧跟别名或者在列名和别名之间加上as，别名加不加双引号均可，如下列子

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

SELECT last_name AS name, commission_pct comm
FROM employees;

3.3.2 去除重复行

使用distinct关键字

SELECT DISTINCT department_id
FROM employees;

需要注意两点：

• DISTINCT 需要放到所有列名的前面，如果写成 SELECT salary, DISTINCT department_id FROM employees 会报错。
• DISTINCT 其实是对后面所有列名的组合进行去重，也就是只有所有列名相同的组合才会去除

3.3.3 null参与运算

所有运算符或列值遇到null值，运算的结果都为null（null代表未知）

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

其中salary可能为空，那么select的运算结果可能就是空咯。

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

3.3.4 着重号

在命名规范中我们有提到，建议表中的字段、表名等不要和保留字、数据库系统或常用方法冲突。如果真的相同，请在SQL语句中使用一对 `` `（着重号）引起来。

#假设有数据库表名为ORDER（其实大写表名不太符合表命名规范捏）
mysql> SELECT * FROM ORDER;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER' at line 1

正确的写法是

mysql> SELECT * FROM `ORDER`;

3.3.5 查询常数

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

比如说，我们想对 employees 数据表中的员工姓名进行查询，同时增加一列字段 corporation ，这个字段固定值为“华为”，可以这样写：

SELECT '华为' as corporation, last_name FROM employees;

3.3.6 显示表结构

使用DESCRIBE 或 DESC 命令，表示表结构

DESCRIBE employees;
或
DESC employees;

输出结果分析

其中，各个字段的含义分别解释如下：

• Field：表示字段名称。
• Type：表示字段类型。
• Null：表示该列是否可以存储NULL值。
• Key：表示该列是否已编制索引。
• Default：表示该列是否有默认值，如果有，那么值是多少。
• Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

4. 运算符

4.1 算术运算符

我认为在数据库中很少进行算数运算，而是在后端将数据取出，通过后端代码运算完成并更新数据库

1. 加法和减法

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

2. 乘法和除法

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

3. 求模运算

被模数%模数，结果符号和被模数相同（和第一个数相同）

mysql> select 10%3,10%5,10%-3,-10%3,-10%-3;
+------+------+-------+-------+--------+
| 10%3 | 10%5 | 10%-3 | -10%3 | -10%-3 |
+------+------+-------+-------+--------+
|    1 |    0 |     1 |    -1 |     -1 |
+------+------+-------+-------+--------+

4.2 比较运算符

• 等于运算符

  等号两端相等返回1，否则返回0

  特殊的如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。

  如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。

  所以引入安全等于

• 安全等于运算符

  <=>类似=，唯一区别是安全等于可以判断NULL值是否相等

• 不等于运算符

  不等于号 不相等返回1，否则返回0

  特殊的如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。

  如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。

此外还有非符号类型的运算符

• 空运算符IS NULL

  值为NULL返回1，否则返回0

• 非空运算符IS NOT NULL

  值非空返回1，否则返回0

• 最小值运算符LEAST

  语法格式为：LEAST(值1，值2，…，值n) ，返回最小值

• 最大值运算符GREATEST

  语法格式为：GREATEST(值1，值2，…，值n)，返回最大值

  在这个最大最小运算符中，数值之间就不用说了，字符的话返回字母表中顺序靠前的，如果出现NULL，就返回NULL。但是那些特殊的就别jb记住了，没卵用，记了也忘，纯纯恶心自己懂吧。

• BETWEEN AND：在之间

4.3 逻辑运算符

逻辑运算符用于判断表达式真假

MySql中表达式非0且非null视为真（1）、0视为假（0）、null为位置状态。逻辑运算符运算返回结果有1、0或null

• 逻辑非：真变假，假变真
• 逻辑与：当且仅当表达式都为真才返回真，否则为假。null在数据库中表示未知状态，所以0 && null = 0、1 && null = null
• 逻辑或：当且仅当表达式都为假才返回假，否则为真。null在数据库中表示未知状态，所以0 || null = null、1 || null = 1
• 逻辑异或：异真同假，两个非NULL的值都是0或者都不等于0时，则返回0，否则返回1。特殊的null在数据库中表示未知状态，所以0 XOR null = null、1 XOR null = 1

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

4.4 位运算符

位运算符是在二进制码上进行计算的运算符。也就是先将计算表达式转化为二进制码，使用位运算符计算，将得到的二进制码转回十进制

4.5 正则表达式

正则表达式是用来匹配文本字符串的模式匹配规则

MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式。下表列出了REGEXP操作符中常用字符匹配列表。

5 排序和分页

5.1 排序

使用ORDER BY进行排序

• ORDER BY 字段 ASC 升序（ascend）
• ORDER BY 字段 DESC 降序（descend）

ORDER BY子句在SELECT语句的结尾，没指定升降序，默认DESC降序

5.2 升序

使用 LIMIT 起始行偏移量 行数 实现分页

• 起始行偏移量指定从哪一条记录开始显示，为可选参数。不写默认为0，也就是第一条记录
• 行数指定显示的记录数

MySQL 8.0中可以使用LIMIT 3 OFFSET 4，意思是获取从第5条记录开始后面的3条记录，等价于LIMIT 4,3

分页显示的公式

#（当前页数-1）*每页记录数，每页记录数
SELECT * FROM table 
LIMIT(PageNo - 1)*PageSize,PageSize;

约束返回结果的数量可以减少网络传输量并提升查询效率 。比如使用 LIMIT 1 ，这样 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

6 多表查询

多表查询又称关联查询

学习多表查询前，先了解笛卡尔积。多表记录之间所有可能的组合称为笛卡尔积

语法：通常用join表示笛卡尔积（逗号也是笛卡尔积）

SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees JOIN departments;

笛卡尔积很少使用，通常是多表查询时省略连接条件产生。

多表查询通常在join后使用where加上连接条件，当多表之间用相同的列名时，要求连接条件加上表名

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

• where作为过滤条件进行多表连接时，是先筛选后连接

• 阿里巴巴开发规范明确指出：数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定（列名前使用表名提高查询效率）

• 值得注意：如果查询过程中使用表的别名，那么过滤条件就只能使用别名！

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

6.1 多表查询分类

6.1.1 等值连接vs非等值连接

• 等值连接就是多表连接的连接条件where使用的是等于号过滤。

• 非等值连接就是多表连接的连接条件where使用的是非等于号过滤

  #非等值连接
  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;
  

6.1.2 自连接vs非自连接

• 自连接就是表和自身连接（需要设置别名）
• 非自连接就是不同表连接

6.1.3 内连接vs外连接

• 内连接就是只返回满足连接条件的行
• 外连接就是返回满足连接条件的行外，还返回不匹配的行，没有匹配的列值为NULL
  1. 左外连接：返回左表中不满足条件的行（以左表为基础）
  2. 右外连接：返回右表中不满足条件的行（以右表为基础）
  3. 满外连接：返回左表和右表中不满足条件的行

在SQL99中，join on表示内连接，left join表示左外连接，right join表示右外连接，full join表示满外连接（Mysql不支持满外连接）

join on语法：

SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件

• on子句用于指定额外的连接条件，这个连接条件与其他条件是分开的
• 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义一样均表示内连接（就用join，别搞那么多花的）

left join和right join语法：

#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

Mysql不支持满外连接FULL JOIN，但是可以用LEFT JOIN UNION RIGHT JOIN替代

只记住join，left join，right join其他的不管

6.2 UNION使用

用于合并结果集。有union和union all两种

• union操作符：返回两个结果集的并集，去除重复记录
• union all操作符：返回两个结果集的并集，不去除重复记录

语法：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

6.3 7种SQL JOINS的实现

分别以内连接，左外连接，右外连接起点衍生

mysql中不支持满外连接，但是我们可以通过UNION来实现满外连接！！（图最下两种情况）

6.4 SQL99语法新特性

• 自然连接：NATURAL JOIN用来表示自然连接，它会自动查找两张表中的所有相同的字段（名称相同的字段），然后等值连接。下面两种写法等价：department_id和manager_id为相同名称字段，等值连接

  #SQL92
  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`;
  
  #SQL
  SELECT employee_id,last_name,department_name
  FROM employees e NATURAL JOIN departments d;
  

• USING连接：SQL99还支持使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用。

  SELECT employee_id,last_name,department_name
  FROM employees e JOIN departments d
  USING (department_id);
  

6.5 小结

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

• WHERE：适用于所有关联查询
• ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
• USING：只能和JOIN一起使用，要求两个关联字段在关联表中名称一致，且只能表示关联字段值相等

多表连接类似嵌套循环，非常耗费资源。

在阿里巴巴java开发手册中：【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。

说明：即使双表 join 也要注意表索引、SQL 性能（高级篇细说，性能优化）

7 单行函数

7.1 函数的理解

函数就是将常用代码逻辑封装起来，提高代码效率和可维护性。

从函数定义出发，Mysql中函数分为内置函数和自定义函数

• 内置函数：DBMS系统自带函数，如Mysql中的concat()
• 自定义函数：字面意思

从输入数据个数出发，Mysql中函数分为单行函数和多行函数

• 单行函数：作用于单个数据值返回单个结果的函数
• 聚合函数：作用于一组数据值返回单个结果的函数

使用SQL语言可以和不同的DBMS交互，但是不同的DBMS之间差异很大

只有很少的函数是被 DBMS 同时支持的。比如，大多数 DBMS 使用（||）或者（+）来做拼接符，而在 MySQL 中的字符串拼接函数为concat()。大部分 DBMS 会有自己特定的函数，这就意味着采用SQL函数的代码可移植性是很差的，因此在使用函数的时候需要特别注意。

通常都是在后端完成对数据的加工，少用函数

7.2 常见单行函数

• 基本函数
• 角度与弧度互换函数
• 字符串函数
• 日期和时间函数
• 流程控制函数
• 加密与解密函数
• Mysql信息函数
• 其他函数

具体参见：第07章_单行函数.pdf

8. 聚合函数

对比单行函数，聚合函数是作用于一组数据，返回单一结果（这个组指的是分组，没有GROUP BY默认所有记录一组）

8.1 常见的聚合函数

8.1.1 COUNT

• COUNT(*)，count(1)返回表的总记录数
• COUNT(expr)返回字段expr不为空的总记录数

8.1.2 AVG和SUM

对于数值类型的数据，有计算平均值和总值的函数

• AVG(expr)：计算表字段expr的平均值（NULL记录不参与计算）
• SUM(expr)：计算表字段expr的总值

8.1.3 MIN和MAX

• MIN(expr)：计算expr字段的最小值
• MAX(expr)：计算expr字段的最大值

8.2 GROUP BY

GROUP BY用于对查询结果分组

GROUP BY使用注意：SELECT列表中所有未包含在组函数（聚合函数）中的列都应该包含在GROUP BY子句中，反过来，包含在GROUP BY中的列不必包含在SELECT列表中

非常合理的规定，如果存在字段A不满足上述规定，那结果集该如何展示？同一个分组，A字段数据不同如何展示？

扩展：

和GROUP BY配套使用的关键字WITH ROLLUP。作用是在查询出的分组记录后增加一条记录，该记录以所有记录为一组，计算所有列

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

8.3 HAVING

HAVING用于分组过滤（通常会使用聚合函数来定义过滤条件）

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

8.3.1 HAVING和WHERE的对比

区别1：

• WHERE 只能使用表字段作为筛选条件，但不能使用分组中的聚合函数作为筛选条件；
• HAVING 建议要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

为什么having建议要和group by配合使用，首先第一点having诞生目的就是分组过滤，第二点如果只是简单字段过滤，从性能优化的角度应该把这样的过滤放在where子句中，达到过滤后再表连接的性能优化目的

区别2：如果需要通过连接从关联表中获取需要的数据，

• WHERE 是先筛选后连接
• HAVING 是先连接后筛选

8.4 SELECT执行过程

• select关键字书写顺序：不能颠倒！虽然某些可视化工具允许颠倒某些顺序

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

• select语句执行顺序

  FROM -> 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 的执行过程中，对于我们来说是不可见的。

9. 子查询

9.1 子查询概念

子查询就是一个查询语句嵌套再另一个查询语句的内部。

子查询的引入：谁的工资比Abel高？

解决方式一：分两次查询

解决方式二：自连接，将Abel数据连接在每一行

解决方式三：子查询

SELECT last_name,salary
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
);

• 按照内查询返回的一条记录还是多条记录，将子查询分为单行子查询和多行子查询
• 按照子查询是否依赖外部查询，将子查询分为关联子查询和非关联子查询

9.2 单行子查询

单行子查询常用操作符：

操作符	含义
=	equal to
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to
<> 、!=	not equal to

题目：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id

实现方式1：不成对比较

SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
        (SELECT manager_id
        FROM employees
        WHERE employee_id IN (174,141))
AND department_id IN
        (SELECT department_id
        FROM employees
        WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);

实现方式2：成对比较

SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
        (SELECT manager_id, department_id
        FROM employees
        WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);

9.2.1 HAVING中的子查询

题目：查询最低工资大于50号部门最低工资的部门id和其最低工资

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

分析：50号部门最低工资作为子查询

9.2.2 CASE中的子查询

case条件表达式

CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END
CASE SCORE WHEN 'B' THEN '良' ELSE '不及格' END
CASE SCORE WHEN 'C' THEN '中' ELSE '不及格' END

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

9.3 多行子查询

多行子查询常用操作符

操作符	含义
IN	存在，存在子查询返回的结果集中
ANY	任意一个，和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	所有，需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

9.3.1 比较ANY和ALL

ANY指任意一个，ALL指所有。参考下面案例：

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

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

9.3.2 子查询虚表命名

题目：查询平均工资最低的部门id

#方式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
)

方式一中，from型的子查询：子查询是作为from的虚表，子查询要用()引起来，必须给虚表取别名。否则：错误代码：1248 Every derived table must have its own alias

9.4 关联子查询

子查询的执行依赖外部查询称为相关子查询，主查询每一行都会执行一次子查询。实际问题中根据需求会自然而然的写出关联子查询

案例：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

9.4.1 EXISTS和NOT EXISTS

EXISTS 是用于测试子查询结果是否为空。非空返回true，空返回false；NOT EXISTS则相反

通常和子查询结合使用，返回true和false

题目：查询公司管理者的employee_id，last_name，job_id，department_id信息

SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( 
    SELECT *
    FROM employees e2
    WHERE e2.manager_id =
    e1.employee_id
);

9.4.2 关联更新

题目：在employees中增加一个department_name字段，数据为员工对应的部门名称

# 1）
ALTER TABLE employees
ADD(department_name VARCHAR2(14));

# 2）
UPDATE employees e
SET department_name = 
(
    SELECT department_name
    FROM departments d
    WHERE e.department_id = d.department_id
);

9.4.3 关联删除

题目：删除表employees中，其与emp_history表皆有的数据

DELETE FROM employees e
WHERE employee_id in
(
    SELECT employee_id
    FROM emp_history
    WHERE employee_id = e.employee_id
)

10 管理数据库和表

10.1 创建和管理数据库

10.1.1 创建数据库

• 方式1：创建数据库

CREATE DATABASE 数据库名;

• 方式2：创建数据库并指定字符集

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

• 方式3：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ）

CREATE DATABASE IF NOT EXISTS 数据库名;

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

10.1.2 使用数据库

• 查看当前所有的数据库

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

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

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

• 查看指定库下所有的表

SHOW TABLES FROM 数据库名;

• 查看数据库的创建信息

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

• 使用/切换数据库

USE 数据库名;

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上数据库名.。

10.1.3 修改数据库

• 更改数据库字符集

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

10.1.4 删除数据库

• 方式1：删除指定的数据库

DROP DATABASE 数据库名;

• 方式2：删除指定的数据库（ 推荐 ）

DROP DATABASE IF EXISTS 数据库名;

10.2 创建和管理表

10.2.1 创建表

• 方式一

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

创建数据库，创建表，删除数据库，删除表都是推荐添加IF NOT EXIST或IF EXIST，添加位置为名称前

• 方式二

使用AS subquery选项，用子查询结果集创建表

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

将子查询结果集作为创建表的一部分，具体来说

• 如果create语句中指定的字段和子查询结果集字段重名，采用子查询的字段和数据
• 如果create语句中指定的字段和子查询结果集字段没有重名，保留create中指定的字段

CREATE TABLE emp1(myColumn varchar(20)) AS SELECT * FROM employees;
# 等价于复制employees表，改名为emp1，并增加列myColumn，默认值为NULL

10.2.2 使用表

• 查看表数据

select ... from ... where ...group by ... having ...order by ...limit

• 查看表结构

desc 表名

• 查看表创建

SHOW CREATE TABLE 表名
# 使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。

10.2.3 修改表

修改表中已经存在的数据表结构：如增加列，修改列，删除列，重命名列

• 增加列

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 旧表名 TO 新表名;
# 方式二：
ALTER table 旧表名 RENAME [TO] 新表名; -- [TO]可以省略

• 删除表

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

drop table 语句不能回滚

DCL 中 COMMIT 和 ROLLBACK

• COMMIT：提交数据。一旦执行COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。
• ROLLBACK：回滚数据。一旦执行ROLLBACK,则可以实现数据的回滚。回滚到最近的一次COMMIT之后。

• 清空表

TRUNCATE TABLE 表名

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

回滚是数据回滚到最近一次提交，如果执行delete删除数据后没有提交（默认自动提交，需要设置SET autocommit = FALSE），则可以通过rollback回滚到数据删除前，但这也仅限于DML语句

TRUNCATE作为DCL，数据库控制语言，执行完自动提交，所以无法回滚

10.2.4 阿里巴巴开发手册之MySQL字段命名

• 【 参考 】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

  说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

• 【 强制 】表名、字段名必须使用小写字母、数字和下划线，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。

• 【 强制 】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。

• 【 强制 】表必备三字段：id, gmt_create, gmt_modified，主键id，数据创建时间，数据最后修改时间

  说明：其中 id 必为主键，类型为BIGINT UNSIGNED、单表时自增、步长为 1。gmt_create,gmt_modified 的类型均为 DATETIME 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新

• 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

10.2.5 MySQL8中的DDL原子化

在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即 DDL操作要么成功要么回滚。

DDL操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log（该表是隐藏的表，通过show tables无法看到）中，用于回滚操作。通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中。

现有book1一张表

在MySQL5.7中，没有DDL原子性

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

mysql> SHOW TABLES;
Empty set (0.00 sec)

在MySQL8中，DDL原子性

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| book1 |
+------------------+
1 row in set (0.00 sec)

11 表数据增删改查

11.1 增加记录

方式一：按照表所有字段默认顺序插入数据

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同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：

• Records：表明插入的记录条数
• Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值
• Warnings：表明有问题的数据值，例如发生数据类型转换

方式四：将查询结果插入表中

# 不必写values子句，添加字段名和结果集字段名一一对应
INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

增语句中 values也可以写value，into可以不写

11.2 更新记录

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

11.3 删除记录

DELETE FROM table_name [WHERE <condition>];

11.4 计算列

MySQL8新特性：计算列，简单来说就是某一列是通过别的列计算得来

例如，表包含字段abc，定义c字段为a+b，那么c就是计算列，是通过别的列计算得来

案例：

创建表，定义计算列

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

查询表

mysql> SELECT * FROM tb1;
+------+------+------+------+
| id   |   a  |   b  |   c  |
+------+------+------+------+
| NULL |  100 |  200 |  300 |
+------+------+------+------+
1 row in set (0.00 sec)

12 MySQL数据类型

类型	类型举例
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、 DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值类型:GEOMETRY、POINT、LINESTRING、POLYGON; 集合类型:MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

12.1 整数类型

12.1.1 类型介绍

整数类型	字节	有符号数取值范围	无符号数取值范围
TINYINT	1	-128~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608-8388607	0~16777215
INT、INTEGER	4	-2147483648~2147483647	0~4294967295
BIGINT	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

12.1.2 可选属性

1. M : 表示显示宽度，当数据宽度小于M时用指定字符填充，M的取值范围是0~255。例如，int(5)：当数据宽度小于5位的时候在数字前面需要用字符填满宽度。该项功能需要配合“ ZEROFILL ”使用，表示用“0”填满宽度，否则指定显示宽度无效。

   alter table test1 modify age int(5) zerofill
   # 再次select时会有显示0填充，可视化工具可能没有效果，那就再cmd下测试
   

   从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性。

   该可选字段逐渐被抛弃了，不必理会

2. UNSIGNED：指定该整数类型采用无符号整数类型，该字段的范围也和有符号整数类型不一样了

   CREATE TABLE test(
   	col1 INT unsigned,
   	col2 INT
   );
   # 分别对应INT无符号整数类型和有符号整数类型的最大值
   insert test value(4294967295,2147483647);
   

3. ZEROFILL：0填充（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性）

12.1.3 适用场景

• TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。
• SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。
• MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。
• INT、INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。
• BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

选择整数类型，考虑存储空间和可靠性的平衡性问题：一方面，用占用字节数少的整数类型可以节省存储空间；另一方面，要是为了节省存储空间，使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，就可能引起系统错误，影响可靠性

实际中，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。因此，你需要确保数据不会超过取值范围，再去考虑如何节省存储空间

12.2 浮点类型

计算机中用定点数或者浮点数来存储小数

12.2.1 类型介绍

• FLOAT 表示单精度浮点数；

• DOUBLE 表示双精度浮点数；

• REAL默认就是 DOUBLE。如果你把 SQL 模式设定为启用REAL_AS_FLOAT ，那么，MySQL 就认为REAL 是 FLOAT。如果要启用REAL_AS_FLOAT，可以通过以下 SQL 语句实现：

  SET sql_mode = “REAL_AS_FLOAT”;
  

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

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

由于浮点数有精度丢失的问题，所以Mysql中使用DECIMAL来存储精度要求高的数值

12.3 定点数

MySQL中定点数只有DECIMAL一种类型

• DECIMAL(M,D)标识定点数，其中M表示精度（数字的总位数），D表示标度（小数点右侧的位数）。在不指定精度和标度时，默认DECIMAL(10,0)
• 定点数在MySQL内部用字符串存储，所以它一定是准确的

浮点数和定点数在相同长度下，浮点数表示范围大，但是精度丢失。但是定点数绝对准确，且表示范围由M精度指定

“由于 DECIMAL 数据类型的精准性，在我们的项目中，除了极少数（比如商品编号）用到整数类型外，其他的数值都用的是 DECIMAL，原因就是这个项目所处的零售行业，要求精准，一分钱也不能差。 ” ——来自某项目经理

12.4 位类型

BIT(M)用于存储二进制值，没有指定M，默认BIT(1)存储一位二进制值。

向BIT类型的字段中插入数据，数据会以二进制的形式存储在表中，一定要确保插入的数据在BIT类型支持的范围内。例如BIT(2)表示的范围是0~3

• 使用SELECT命令查询位字段时，可以用 BIN(列名) 或 HEX(列名) 函数进行读取
• 使用列名+0查询数据时，可以直接查询出存储的十进制数据的值

12.5 日期和时间类型

• YEAR 类型通常用来表示年

• DATE 类型通常用来表示年、月、日

  使用 CURRENT_DATE() 或者 NOW() 函数，会插入当前系统的日期。

• TIME 类型通常用来表示时、分、秒

  使用 CURRENT_TIME() 或者 NOW() ，会插入当前系统的时间

• DATETIME 类型通常用来表示年、月、日、时、分、秒

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

• TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒

  存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。

  TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的

类型	名称	字节	日期格式	最小值	最大值
YEAR	年	1	YYYY或YY	1901	2155
TIME	时间	3	HH:MM:SS	-838:59:59	838:59:59
DATE	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
DATETIME	日期时间	8	YYYY-MM-DDHH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	日期时间	4	YYYY-MM-DDHH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTC

• 为什么时间类型 TIME 的取值范围不是 -23:59:59～23:59:59 呢？原因是 MySQL 设计的 TIME 类型，不光表示一天之内的时间，而且可以用来表示一个时间间隔，这个时间间隔可以超过 24 小时。
• 开发中用的最多的日期时间类型是DATETIME，实际项目中，尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息，取值范围也最大，使用起来比较方便。

12.6 文本字符串类型

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

12.6.1 CHAR和VARCHAR

字符串(文本)类型	特点	长度	长度范围	占用存储空间
CHAR(M)	固定长度	M	0 <= M <= 255	M个字符对应的字节
VARCHAR(M)	可变长度	M	0 <= M <= 65535	(实际长度 + 1/2) 个字节

CHAR(M)和VARCHAR(M)用M指定字符串长度

• CHAR类型
  1. CHAR(M)不指定长度，默认M=1
  2. 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在 右侧填充 空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
  3. 定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。
• VARCHAR类型
  • VARCHAR(M) 定义时， 必须指定长度M，否则报错。可存储M个字符
  • VARCHAR实际存储会用长度前缀记录字符串长度，当最大长度小于等于255，前缀占1字节。否则前缀占2字节

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

12.6.2 TEXT类型

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

在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。由于实际存储的长度不确定，MySQL不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用CHAR(M)，或者 VARCHAR(M)。

TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR，VARCHAR来代替。还有TEXT类型不用加默认值，加了也没用。而且text和blob类型的数据删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用一个表。

12.7 ENUM类型

ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。

如：

CREATE TABLE test_enum(
	season ENUM('春','夏','秋','冬','unknow')
);

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

12.8 SET类型

在MySQL中，SET 是一种用于存储零个或多个由字符串表示的枚举值的数据类型。它允许你从一个固定的字符串集合中选择一个或多个值进行存储。（ENUM只能选一个）

CREATE TABLE temp_mul(
    gender ENUM('男','女'),
    hobby SET('吃饭','睡觉','打豆豆','写代码')
);

INSERT INTO temp_mul VALUES('男','睡觉,打豆豆'); #成功
# Data truncated for column 'gender' at row 1
INSERT INTO temp_mul VALUES('男,女','睡觉,写代码'); #失败

12.9 二进制字符串类型

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

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

详细见：第12章_MySQL数据类型精讲.pdf

12.10 JSON类型

详细见：第12章_MySQL数据类型精讲.pdf

12.11 空间类型

详细见：第12章_MySQL数据类型精讲.pdf

12.12 阿里巴巴开发手册之类型

• 任何字段如果为非负数，必须是 UNSIGNED
• 【 强制 】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。
  • 说明：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并分开存储。
• 【 强制 】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。
• 【 强制 】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

13 约束

XMIND总结：第13章_约束整理.xmind

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名'; # 查看都有哪些约束

show index from 表名称; # 查看表的索引

为防止数据库中存在非法数据，SQL以约束的方式对表数据进行条件限制

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

• NOT NULL 非空约束，规定某个字段不能为空
• UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
• PRIMARY KEY 主键(非空且唯一)约束
• FOREIGN KEY 外键约束
• CHECK 检查约束（MySQL不支持，可以用但没有效果）
• DEFAULT 默认值约束

13.1 非空约束

NOT NULL非空约束，默认所有的类型的值都可以是NULL

13.2 唯一性约束

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

• 创建唯一约束时没有唯一约束名，就默认和列名相同
• MySQL会默认给唯一约束的列创建唯一索引

建表后添加唯一索引，第一种方式可以添加复合唯一约束，第二种则只能添加单列的唯一约束

13.3 主键约束

PRIMARY KEY主键约束，主键约束列要求非空且唯一，相当于非空约束+唯一性约束

• 一个表只能有一个主键约束
• MySQL的主键约束名总是PRIMARY，就算自己命名也没有用
• 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。

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

13.4 自增列

AUTO_INCREMENT指定字段自增

• 一个表最多一个自增列
• 自增列必须是键列（主键列，唯一键列）
• 自增列数据类型必须是整数类型

#错误演示
create table employee(
    eid int auto_increment,
    ename varchar(20)
);
# ERROR 1075 (42000): Incorrect table definition; there can be only one auto column
and it must be defined as a key

create table employee(
    eid int primary key,
    ename varchar(20) unique key auto_increment
);
# ERROR 1063 (42000): Incorrect column specifier for column 'ename' 因为ename不是整数类型

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

在MySQL 5.7系统中，自增主键的分配规则由InnoDB数据字典内部一个 计数器来决定的，而该计数器只在内存中维护 ，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。

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

13.5 外键约束

FOREIGN KEY限定表字段的引用完整性

• 被引用的表为主表，引用别人的表为从表（主表-父表，从表-子表）

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

• 主表被引用的列必须是主键列或者唯一约束列（就是必须唯一的意思）

• 创建外键约束不指定外键约束名，默认名不是列名，而是由MySQL生成一个外键名

• 创建表时，先创建主表再创建从表，删除表时先删除从表再删除主表

• 外键约束约束主表从表双方，主表的修改和删除数据受约束，从表的添加和修改数据受约束

  如：当主表的记录被从表引用时，主表的该条记录不允许删除。需要先删除从表中引用该记录的数据，然后才能删除主表的数据

• 从表外键列和主表被引用的列，名称可以不同，但类型必须一致

• 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引

• 删除外键约束后，必须手动删除对应的索引

外键约束约束的主从表双方，因此约束等级提供解决方式

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

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

也就是主表更新则从表更新，从表有引用记录则不允许主表删除

在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会 因为外键约束的系统开销而变得非常慢 。所以， MySQL 允许你不使用系统自带的外键约束，在 应用层面 完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

所以在阿里巴巴开发规范中

【 强制 】不得使用外键与级联，一切外键概念必须在应用层解决。

说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于 单机低并发 ，不适合 分布式 、 高并发集群 ；级联更新是强阻塞，存在数据库 更新风暴 的风险；外键影响数据库的 插入速度 。

15.6 CHECK

CHECK检查字段值是否满足要求：CHECK（逻辑表达式）。CHECK约束在MySQL5.7不起作用，MySQL8才有用

CREATE TABLE temp(
    id INT AUTO_INCREMENT,
    NAME VARCHAR(20),
    age INT CHECK(age > 20),
    PRIMARY KEY(id)
);

15.7 默认约束

DEFAULT指定字段默认值

15.8 面试

• 面试1 为什么建表时，加 not null default ‘’ 或 default 0

  答：不想让表中出现null值。

• 面试2 为什么不想要 null 的值

  答:

  1. 不好比较。null是一种特殊值（表示不确定状态），比较时只能用专门的is null 和 is not null来比较。碰到运算符，通常返回null。
  2. 效率不高。影响提高索引效果。因此，我们往往在建表时 not null default ‘’ 或 default 0

• 面试3 带AUTO_INCREMENT约束的字段值是从1开始的吗？

  在MySQL中，默认AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性（AUTO_INCREMENT）的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。

  还可以聊聊MySQL自增列的持久化问题

• 面试4 并不是每个表都可以任意选择存储引擎？

  外键约束（FOREIGN KEY）不能跨引擎使用。

  MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。

14 视图

14.1 常见的数据库对象

对象	描述
表(TABLE)	表是存储数据的逻辑单元，以行和列的形式存在，列就是字段，行就是记录
数据字典	就是系统表，存放数据库相关信息的表。系统表的数据通常由数据库系统维护，程序员通常不应该修改，只可查看
约束(CONSTRAINT)	执行数据校验的规则，用于保证数据完整性的规则
视图(VIEW)	一个或者多个数据表里的数据的逻辑显示，视图并不存储数据
索引(INDEX)	用于提高查询性能，相当于书的目录
存储过程(PROCEDURE)	用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境
存储函数(FUNCTION)	用于完成一次特定的计算，具有一个返回值
触发器(TRIGGER)	相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

14.2 视图概述

通过select语句将基表部分数据封装成虚拟表，称为视图

也就是说，视图是基于现有表（基表）的查询结果构建的虚拟表，可以理解为存储起来的SELECT语句

为什么要有视图：

1. 操作简单

   将经常使用的查询操作定义为视图，无需关心表结构而直接获取数据，极大简化了开发人员对数据库的操作。

2. 减少数据冗余

   视图集中展示我们当前业务关注的数据，减少数据冗余，而视图本身不存储数据，不占用数据存储的资源

3. 数据安全

   通过视图决定将什么数据暴露给用户，什么数据隐藏，实现用户对数据的访问限制

视图带来优点，相应的增加维护视图的成本。如果实际数据表的结构变更了，相关的视图也需要维护，否则容易成为系统潜在隐患

14.3 创建视图

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

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

# 创建视图可选项：WITH CHECK OPTION 用于确保对视图进行增删改时，符合视图定义的操作才被允许
# 例如，假设有一个视图只包含员工表中工资大于等于 50000 的员工信息。如果尝试插入一个工资小于 50000 的员工记录，由于不符合视图定义的条件，操作将失败并抛出错误。

在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字段列表一致。如果SELECT语句中给字段取了别名，那么视图中的字段名和别名相同。

14.4 查看视图

• 查看数据库的表对象、视图对象

SHOW TABLES;

• 查看视图的结构

DESC / DESCRIBE 视图名称;

• 查看视图的属性信息

# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称';

• 查看史特的详细定义信息

SHOW CREATE VIEW 视图名称;

上述的指令对表也适用，视图作为特殊的表（通过select语句将基表部分数据封装成虚拟表）当然也能用

14.5 更新视图数据

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

视图中行和列必须和基表行和列一对一，视图才可更新。以下情况视图不支持更新操作：

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

虽然可以更新视图数据，但总的来说，视图作为 虚拟表 ，主要用于 方便查询 ，不建议更新视图的数据。对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。

14.6 修改删除视图

• 修改视图：

  # 方式一：使用CREATE OR REPLACE VIEW 子句修改视图，用于创建或替换（如果已存在同名视图）视图
  CREATE OR REPLACE VIEW empvu80
  (id_number, name, sal, department_id)
  AS
  SELECT employee_id, first_name || ' ' || last_name, salary, department_id
  FROM employees
  WHERE department_id = 80;
  
  # 方式二：使用ALTER VIEW
  ALTER VIEW 视图名称
  AS
  查询语句
  

• 删除视图：删除的只是视图，不影响基表数据

  DROP VIEW IF EXISTS 视图名称;
  

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

15 存储过程与函数

将复杂的SQL逻辑封装起来，就是存储过程和函数。

存储过程没有返回值，函数有返回值

15.1 存储过程

将预先编译的SQL语句存储在MySQL服务器上，使用时直接调用即可

存储过程提高sql语句重用性，简化操作。同时减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器），减少sql暴露在网上的风险，提高数据安全性

15.1.1 创建存储过程

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

1. 参数类型

   • IN ：当前参数为输入参数，不指定则默认输入参数

   • OUT ：当前参数为输出参数

   • INOUT ：当前参数既可以为输入参数，也可以为输出参数。

2. characteristics 表示创建存储过程时指定的对存储过程的约束条件

   • LANGUAGE SQL ：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
   • [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 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
     • 如果没有设置相关之，默认为DEFINER
   • COMMENT 'string' ：注释信息，可以用来描述存储过程。

3. 存储过程可能会用到复杂的SQL语句

   • BEGIN…END若只包含一个SQL语句，可以省略begin...end，若包含多个SQL语句，SQL语句以;分隔
   • DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，
   • SET：赋值语句，用于对变量进行赋值。
   • SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

4. 可以指定显得结束标记

   DELIMITER 新的结束标记
   

   因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。

   比如：DELIMITER //语句的作用是将MySQL的结束符设置为//，并以END //结束存储过程。存储过程定义完毕之后再使用DELIMITER ;恢复默认结束符。

案例：创建存储过程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 ;

15.1.2 调用存储过程

# 使用CALL调用存储过程
CALL 存储过程名(实参列表)

# 调用in输入参数的存储过程
CALL sp1('值')

# 调用out输出参数的存储过程
set @name;
CALL sp2(@name);
select @name;

# 调用inout输入输出参数的存储过程
set @name='值';
CALL sp2(@name);
select @name;

案例：

DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT,OUT r INT)
	BEGIN
		DECLARE i INT;
		DECLARE SUM INT;
		SET i = 1;
		SET SUM = 0;
		WHILE i <= n DO
			SET SUM = SUM + i;
			SET i = i +1;
		END WHILE;
	
		SELECT SUM INTO r;
	END //
DELIMITER ;

调用

mysql> CALL add_num(10,@sum);
Query OK, 1 row affected (0.00 sec)

mysql> select @sum;
+------+
| @sum |
+------+
|   55 |
+------+
1 row in set (0.00 sec)

15.2 存储函数

MySQL支持自定义函数，自定义函数调用方式和MySQL自带函数一样

15.2.1 创建存储函数

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

1. 不同于存储过程参数分输入输出，存储函数参数均输入
2. 和存储过程一样，如果函数体只有一条语句，可以省略BEGIN和AND
3. characteristic用法取值与存储过程一致
4. DELIMITER指定结束标识

注意：

若在创建存储函数中报错you might want to use the less safe log_bin_trust_function_creators variable，有两种处理方法：

• 方式1：加上必要的函数特性[NOT] DETERMINISTIC 和 {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}

• 方式2：SET GLOBAL log_bin_trust_function_creators = 1;

  这个报错通常是在MySQL数据库中创建函数或存储过程时出现的，它是MySQL的一种安全特性，旨在防止恶意用户在服务器上执行未经授权的代码。

  当你尝试创建一个函数或存储过程时，如果这个函数或过程使用了安全性较低的操作，例如使用了自定义函数或使用了动态SQL语句，MySQL会提示这个警告。这时，你可以选择使用“less safe”的方式来创建函数或过程，这意味着你需要设置MySQL的“log_bin_trust_function_creators”变量为1。

  这个变量默认为0，表示MySQL不信任函数或存储过程的创建者。当你将这个变量设置为1时，MySQL会相信函数或过程的创建者是可信的，从而允许他们创建使用“less safe”操作的函数或过程。

  需要注意的是，使用“less safe”选项可能会导致安全问题，因为这允许用户在服务器上执行未经授权的代码。因此，你应该仅在有充分理由且知道自己在做什么的情况下使用这个选项

15.2.2 调用存储函数

存储函数的使用方法与MySQL内部函数的使用方法是一样

SELECT 函数名(实参列表)

15.3 深入存储过程和存储函数

15.3.1 比较存储过程和存储函数

	关键字	调用语法	返回值
存储过程	PROCEDURE	CALL 存储过程()	理解为0或多个
存储函数	FUNCTION	SELECT函数()	只有一个

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

15.3.2 查看存储过程和存储函数

1. 使用SHOW CREATE语句查看存储过程和函数的创建信息

   SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
   

2. 使用SHOW STATUS语句查看存储过程和函数的状态信息

   SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
   # [LIKE 'pattern']：匹配存储过程或函数的名称，不写匹配全部
   

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

   MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：

   SELECT * FROM information_schema.Routines
   WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
   # 说明：如果在MySQL数据库中存在存储过程和函数名称相同的情况，最好指定ROUTINE_TYPE查询条件来指明查询的是存储过程还是函数。
   

15.3.3 修改存储过程和存储函数

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

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

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

• CONTAINS SQL ，表示子程序包含SQL语句，但不包含读或写数据的语句。
• NO SQL ，表示子程序中不包含SQL语句。
• READS SQL DATA ，表示子程序中包含读数据的语句。
• MODIFIES SQL DATA ，表示子程序中包含写数据的语句。
• SQL SECURITY { DEFINER | INVOKER } ，指明谁有权限来执行。
  • DEFINER ，表示只有定义者自己才能够执行。
  • INVOKER ，表示调用者可以执行。
• COMMENT 'string' ，表示注释信息。

案例：修改存储过程CountProc的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行，代码如下：

ALTER PROCEDURE CountProc
MODIFIES SQL DATA
SQL SECURITY INVOKER ;

查询修改后的信息

SELECT specific_name,sql_data_access,security_type
FROM information_schema.`ROUTINES`
WHERE routine_name = 'CountProc' AND routine_type = 'PROCEDURE';

结果显示，存储过程修改成功。从查询的结果可以看出，访问数据的权限（SQL_DATA_ ACCESS）已经变成MODIFIES SQL DATA，安全类型（SECURITY_TYPE）已经变成INVOKER。

15.3.4 删除存储过程和函数

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

15.4 存储过程的争议

首先存储过程封装复杂SQL逻辑，存出在MySQL服务器，可减少网络传输量，一次编译多次使用大大减少开发工作量。

基于上面这些优点，不少大公司都要求大型项目使用存储过程，比如微软、IBM 等公司。但是国内的阿里禁止开发人员使用存储过程

阿里开发规范

【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

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

2. 调式维护成本困难。只要极少数DBMS支持存储过程调式。其次数据表的变更可能导致存储过程失效，维护困难

3. 不适用高并发

   • 高并发场景，需要减轻数据库压力。而存储过程的执行会增加数据库的负担。因为它们需要在数据库服务器上进行解释和执行，消耗额外的资源。
   • 有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护

16 变量、流程控制与游标

16.1 变量

MySQL中变量分为系统变量和用户自定义变量

16.1.1 系统变量概念

由系统定义的变量即为系统变量，在MySQL启动时由服务器赋值，系统变量分为

• **全局系统变量（需要添加 global 关键字）**也简称为全局变量
• **会话系统变量（需要添加 session 关键字）**没指定全局还是会话默认会话系统变量

静态变量作为特殊的全局系统变量。（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）

每一个MySQL客户机成功连接MySQL服务器后，都会产生与之对应的会话。会话期间，MySQL服务实例会在MySQL服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变量值的复制。

• 全局系统变量针对于所有会话（连接）有效，但不能跨重启
• 会话系统变量仅针对于当前会话（连接）有效。会话期间，当前会话对某个会话系统变量值的修改，不会影响其他会话同一个会话系统变量的值。
• 会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改。

在MySQL中有些系统变量只能是全局的，例如 max_connections 用于限制服务器的最大连接数；有些系统变量作用域既可以是全局又可以是会话，例如 character_set_client 用于设置客户端的字符集；有些系统变量的作用域只能是当前会话，例如 pseudo_thread_id 用于标记当前会话的 MySQL 连接 ID。

16.1.2 系统变量的使用

• 查看所有系统变量

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

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

• 查看部分系统变量

#查看满足条件的部分全局变量。
SHOW GLOBAL VARIABLES LIKE '%标识符%';

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

• 查看指定系统变量

#查看指定的全局变量的值
SELECT @@global.变量名;

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

#或者
SELECT @@变量名;

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

• 修改系统变量

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

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

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

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

16.1.3 用户变量概念

用户自定义的变量即为用户变量，根据作用范围分为：

• 会话用户变量：只对当前会话有效的用户变量
• 局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在存储过程和函数中使用

MySQL中会话用户变量以一个@开头，局部变量就是普通名字

16.1.4 用户变量的使用

• 会话用户变量的定义

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

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

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

SELECT @用户变量

• 局部变量的定义

# 局部变量的定义：使用DECLARE 在BEGIN...END的首行定义
DECLARE 变量名 类型 [default 值]; # 如果没有DEFAULT子句，初始值为NULL

• 局部变量的赋值

#局部变量只在BEGIN...END有效
#方式1：一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;

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

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

#局部变量只在BEGIN...END有效
SELECT 局部变量名;

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

16.2 定义条件和处理程序

• 定义条件 是事先定义程序执行过程中可能遇到的问题
• 处理程序 定义了在遇到问题时应当采取的处理方式

保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。

16.2.1 定义条件

定义条件就是给MySQL中的错误命名，将一个错误名和指定的错误关联起来。随后通过错误名定义处理程序

定义条件使用DECLARE语句，语法格式如下：

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

错误码（或错误条件值）的说明：

• MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误。（分别对应错误码（或错误条件值））
  • MySQL_error_code是数值类型错误代码。
  • sqlstate_value是长度为5的字符串类型错误代码。
• 例如，在ERROR 1418 (HY000)中，1418是MySQL_error_code，'HY000’是sqlstate_value。

举例：定义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';

16.2.2 处理程序

处理程序就是在指定类型错误时的处理方式

定义处理程序时，使用DECLARE语句的语法如下：

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

• 处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。
  • CONTINUE ：表示遇到错误不处理，继续执行。
  • EXIT ：表示遇到错误马上退出。
  • UNDO ：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。
• 错误类型（即条件）可以有如下取值：
  • SQLSTATE ‘字符串错误码’ ：表示长度为5的sqlstate_value类型的错误代码；
  • MySQL_error_code ：匹配数值类型错误代码；
  • 错误名称 ：定义条件预先定义的错误名，该错误名和指定的错误关联
  • SQLWARNING ：匹配所有以01开头的SQLSTATE错误代码；
  • NOT FOUND ：匹配所有以02开头的SQLSTATE错误代码；
  • SQLEXCEPTION ：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；
• 处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像“ SET 变量 = 值 ”这样的简单语句，也可以是使用 BEGIN … END 编写的复合语句

案例：

CREATE TABLE employees(email VARCHAR(20) NOT NULL,last_name VARCHAR(20));

INSERT employees VALUE('127@qq.com','linzhuowei')

SET @exception_handler_value=0;

SET @x=0;

DELIMITER //

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

DELIMITER ;

SELECT @x,@exception_handler_value

CALL UpdateDataNoCondition()

结果：

mysql> SELECT @x,@exception_handler_value;
+------------+---------------------------+
| @x         | @exception_handler_value  |
+------------+---------------------------+
| NULL       | NULL                      |
+------------+---------------------------+
1 row in set (0.00 sec)

16.3 流程控制

16.3.1 分支结构之IF

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

16.3.2 分支结构之CASE

语法结构1

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

语法结构2

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

16.3.3 循环结构之LOOP

# loop_label表示LOOP语句的标注名称，该参数可以省略。
[loop_label:] LOOP
    循环执行的语句
    #使用LEAVE跳出循环 LEAVE [loop_label]
END LOOP [loop_label]

16.3.4 循环结构之WHILE

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

16.3.5 循环结构之REPEAT

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

16.3.6 跳转语句之LEAVE

LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 break。

LEAVE 标记名

16.3.7 跳转语句之ITERATE

ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue，意思为“再次循环”。

语句基本格式如下：

ITERATE label
#label参数表示循环的标志。

16.4 游标

通过游标，精确定位结果集中的某一条记录（MySQL中游标可以在存储过程和函数中使用）

游标的使用

1. 声明游标

   # 向指定结果集创建游标
   DECLARE 游标名 CURSOR FOR select_statement;
   #select_statement代表select语句
   

2. 打开游标

   OPEN 游标名
   

3. 使用游标获取数据

   FETCH 游标名 INTO 变量1 [, 变量2...] 
   # 游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致
   

   使用游标将当前行字段数据逐个放入后续的变量中

4. 关闭游标

   CLOSE 游标名
   

   游标占用系统资源，如果不及时关闭，会一直保持到存储过程结束

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

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

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

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

以通过设置系统变量max_execution_time来实现：

SET GLOBAL MAX_EXECUTION_TIME=2000;

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

SET PERSIST global max_connections = 1000;

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

17 触发器

当某些事件（INSERT、UPDATE、DELETE）发生时执行相应的操作，这就时触发器的作用

17.1 创建触发器

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

• 表名 ：表示触发器监控的对象。
• BEFORE|AFTER ：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。
• INSERT|UPDATE|DELETE ：表示触发的事件。
  • INSERT 表示插入记录时触发；
  • UPDATE 表示更新记录时触发；
  • DELETE 表示删除记录时触发。
• CREATE TRIGGER 触发器名称
• 触发器执行的语句块：触发器执行的语句块 ：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块。

案例：定义触发器“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 ;

注意事项：

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

例如：基于子表员工表t_employee的DELETE语句定义了触发器t1，而子表的部门编号did字段定义了外键约束引用了父表部门表t_department的主键列部门编号did，并且该外键加了ONDELETE SET NULL子句，那么如果此时删除父表部门表t_department在子表员工表t_employee有匹配记录的部门记录时，会引起子表员工表t_employee匹配记录的部门编号did修改为NULL，但是但是此时不会激活触发器t1。只有直接对子表员工表t_employee执行DELETE语句时才会激活触发器t1。

17.2 查看或删除触发器

• 查看所有触发器

SHOW TRIGGERS\G

• 查看指定触发器

SHOW CREATE TRIGGER 触发器名

• 从系统库information_schema的TRIGGERS表中查询触发器的信息

SELECT * FROM information_schema.TRIGGERS;

• 删除触发器

DROP TRIGGER IF EXISTS 触发器名称;

17.3 ON UPDATE

行记录更新时自动更新该字段，常用于更新时间

CREATE TABLE your_table (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(255),  
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  
);

17.4 触发器的优缺点

优点：

1. 保证数据完整性，当A表发生变更，可以通过触发器修改和A关联的数据表，保证数据完整性
2. 触发器还可以用在操作数据前，对数据进行合法性检查

缺点：触发器可读性差，隐秘性高，系统维护非常有挑战。触发器由事件驱动，执行A语句出现的报错可能并非来自A语句，可能来自触发器，大大影响错误排查

18 MySQL8新特性

第18章_MySQL8其它新特性.pdf