小张的个人博客

个人充电 | 生活点滴

0%

MySQL入门学习笔记

发表于 更新于 分类于
本文字数： 37k 阅读时长 ≈ 34 分钟

前言

这是个人刚开始学习MySQL数据库时所记录的一些笔记，总结成十几章内容，包括基本增删改查操作语句，创建数据库，创建修改数据表，约束，视图，存储过程和函数，变量，流程控制和触发器等内容，适合刚开始接触数据库学习的小伙伴o(￣▽￣)ブ

第 01 章 数据库概述

1. 为什么要使用数据库

  • 持久化(persistence)： 把数据保存到可掉电式存储设备中以供之后使用 。大多数情况下，特别是企

业级应用， 数据持久化意味着将内存中的数据保存到硬盘上加以保存 ，而持久化的实现过程大多

通过各种关系数据库来完成。

  • 持久化的主要作用：将内存中的数据存储在关系型数据库中。

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

2. 1 数据库的相关概念

  • DB：数据库（Database），其本质是一个文件系统。它保存了一系列有组织的数据。

  • DBMS：数据库管理系统（Database Management System），是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。

  • SQL：结构化查询语言（Structured Query Language）专门用来与数据库通信的语言。

2. 2 数据库与数据库管理系统的关系

数据库管理系统(DBMS)可以管理多个数据库(DB)，为保存应用中实体的数据，在数据库创建会多个表以保存程序中实体用户的数据。

3. RDBMS 与 非RDBMS

3.1 关系型数据库(RDBMS)

3. 1. 1 实质

  • 关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。

  • 关系型数据库以行(row)和列(column)的形式存储数据，这一系列的行和列被称为表(table)，一组表组成了一个库(database)。

  • 关系型数据库，就是建立在关系模型基础上的数据库。

  • SQL 是关系型数据库的查询语言。

3. 1. 2 优势

  • 复杂查询：可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。

  • 事务支持：使得对于安全性能很高的数据访问要求得以实现。

3. 2 非关系型数据库(非RDBMS)

非关系型数据库种类

  1. 键值型数据库

    键值型数据库通过 Key-Value 键值的方式来存储数据，Key 作为唯一的标识符，优点是查找速度快，在这方面明显优于关系型数据库，缺点是无法像关系型数据库一样使用条件过滤（比如 WHERE），如果你不知道去哪里找数据，就要遍历所有的键，就会消耗大量的计算。键值型数据库典型的使用场景是作为内存缓存。Redis是最流行的键值型数据库。

  2. 文档型数据库

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

  3. 搜索引擎数据库

    搜索引擎数据库是应用在搜索引擎领域的数据存储形式，由于搜索引擎会爬取大量的数据，并以特定的格式进行存储，这样在检索的时候才能保证性能最优。核心原理是“倒排索引”。典型产品有Solr、Elasticsearch、Splunk 等。

  4. 列式数据库

    列式数据库是相对于行式存储的数据库，Oracle、MySQL、SQL Server 等数据库都是采用的行式存储（Row-based），而列式数据库是将数据按照列存储到数据库中，这样做的好处是可以大量降低系统的I/O，适合于分布式文件系统，不足之处在于功能相对有限。典型产品有HBase等。

  5. 图形数据库

    图形数据库，利用了图这种数据结构存储了实体（对象）之间的关系。图形数据库最典型的例子就是社交网络中人与人的关系，数据模型主要是以节点和边（关系）来实现，特点在于能高效地解决复杂的关系问题。图形数据库就是一种存储图形关系的数据库。它利用了图这种数据结构存储了实体（对象）之间的关系。典型产品有Neo4J、InfoGrid等。

第 02 章 SELECT语句

1. 基本SELECT语句

1. 1 SELECT … FROM …

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

选择全部列：

1
SELECT * FROM 表名

注意：一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符‘*’。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。

1. 2 列的别名

别名使用方法：紧跟列名，也可以 在列名和别名之间加入关键字AS，别名使用双引号 ，以便在别名中包含空格或特殊的字符并区分大小写。AS 可以省略，在实际使用时建议别名简短，见名知意；

1
2
3
4
5
6
SELECT last_name AS name, commission_pct comm
FROM employees;


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

1. 3 去除重复行

默认情况下，查询会返回全部行，包括重复行，在SELECT语句中使用关键字DISTINCT去除重复行；

1
2
SELECT DISTINCT department_id
FROM employees;

要注意的是：

  1. DISTINCT 需要放到所有列名的前面，否则会报错。

  2. DISTINCT 其实是对后面所有列名的组合进行去重。

1. 4 空值参与运算

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

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

1. 5 查询常数

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

  • SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。

2. 过滤数据

语法：使用WHERE子句，将不满足条件的行过滤掉，WHERE子句紧随 FROM子句

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

第 03 章 运算符

1. 算术运算符

算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。

  1. 加法与减法运算符

由运算结果可以得出如下结论：

  • 一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；

  • 一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；

  • 加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；

  • 在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按 0 计算。（补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）

  1. 乘法与除法运算符

由运算结果可以得出如下结论：

  • 一个数乘以整数 1 和除以整数 1 后仍得原数；

  • 一个数乘以浮点数 1 和除以浮点数 1 后变成浮点数，数值与原数相等；

  • 一个数除以整数后，不管是否能除尽，结果都为一个浮点数；

  • 一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后 4 位；

  • 乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。

  • 在数学运算中， 0 不能用作除数，在MySQL中，一个数除以 0 为NULL。

  1. 求模（求余）运算符

2. 比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回 1 ，比较的结果为假则返回 0 ，其他情况则返回NULL。比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。

  1. 等号运算符

    等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回 1 ，不相等则返回0 。

    在使用等号运算符时，遵循如下规则：

  • 如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。

  • 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。

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

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

  1. 安全等于运算符

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

  2. 不等于运算符

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

  3. 空运算符

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

  4. 非空运算符

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

  5. 最小值运算符

    语法格式为：LEAST(值 1 ，值 2 ，…，值n)。其中，“值n”表示参数列表中有n个值。在有两个或多个参数的情况下，返回最小值。当参数是整数或者浮点数时，LEAST将返回其中最小的值；当参数为字符串时，返回字母表中顺序最靠前的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。

  6. 最大值运算符

    语法格式为：GREATEST(值 1 ，值 2 ，…，值n)。其中，n表示参数列表中有n个值。当有两个或多个参数时，返回值为最大值。假如任意一个自变量为NULL，则GREATEST()的返回值为NULL。当参数中是整数或者浮点数时，GREATEST将返回其中最大的值；当参数为字符串时，返回字母表中顺序最靠后的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。

  7. BETWEEN AND运算符

    BETWEEN运算符使用的格式通常为：

    1
    2
    SELECT D FROM TABLE 
    WHERE C BETWEEN A AND B

    此时，当C大于或等于A，并且C小于或等于B时，结果为 1 ，否则结果为 0 。

  8. IN运算符

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

  9. NOT IN运算符

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

  10. LIKE运算符

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

  11. REGEXP运算符

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

3. 逻辑运算符

逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为 1 、 0 或者NULL。

MySQL中支持 4 种逻辑运算符如下：

  1. 逻辑非运算符

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

  2. 逻辑与运算符

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

  3. 逻辑或运算符

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

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

  1. 逻辑异或运算符

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

4. 位运算符

位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数。

MySQL支持的位运算符如下：

  1. 按位与运算符

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

  2. 按位或运算符

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

  3. 按位异或运算符

    按位异或（^）运算符将给定的值对应的二进制数逐位进行逻辑异或运算。当给定值对应的二进制位的数值不同时，则该位返回 1 ，否则返回 0 。

  4. 按位取反运算符

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

注意：由于按位取反（~）运算符的优先级高于按位与（&）运算符的优先级，所以 10 & ~ 1 ，首先，对数字 1 进

行按位取反操作，结果除了最低位为 0 ，其他位都为 1 ，然后与 10 进行按位与操作，结果为 10 。

  1. 按位右移运算符

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

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

5. 运算符的优先级

结论：赋值运算符的优先级最低，使用“()”括起来的表达式的优先级最高。

第 04 章 排序与分页

1. 排序数据

1. 1 排序规则

使用 ORDER BY 子句进行排序，ASC（ascend）为升序，DESC（descend）为降序，ORDER BY 子句在SELECT语句的结尾。

1. 2 单列排序

1
2
3
4
# 例子如下：
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;

1. 3 多列排序

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

2. 分页

2. 1 分页规则

所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。MySQL中使用 **LIMIT **实现分页，LIMIT 子句必须放在整个SELECT语句的最后！

1
LIMIT [位置偏移量],行数

第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是 0 ，第二条记录的位置偏移量是1 ，以此类推）；第二个参数“行数”指示返回的记录条数。

举例：MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第 5 条记录开始后面的 3 条记录，和“LIMIT4,3;”返回的结果相同。

分页显式公式：LIMIT（当前页数- 1 ）*每页条数，每页条数

使用 LIMIT 的好处：约束返回结果的数量可以减少据表的网络传输量，也可以提升查询效率。如果我们知道返回结果只有1 条，就可以使用LIMIT 1，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回；

第 05 章 多表查询

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

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

1. 笛卡尔积产生的问题

1. 1 笛卡尔积（或交叉连接）的理解

  • 笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

  • SQL 92 中，笛卡尔积也称为交叉连接，英文是 CROSS JOIN。在 SQL 99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。

1. 2 分析与问题解决

笛卡尔积的错误会在下面条件下产生 ：

  1. 省略多个表的连接条件（或关联条件）

  2. 连接条件（或关联条件）无效

  3. 所有表中的所有行互相连接

为了避免笛卡尔积， 可以 在 WHERE 加入有效的连接条件。加入连接条件后，查询语法如下：

在 WHERE子句中写入连接条件，在表中有相同列时，在列名之前加上表名前缀

2. 多表查询分类讲解

分类 1 ：等值连接 vs 非等值连接

等值连接

1
2
3
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column 1 = table2.column 2 ;  #连接条件为等号即为等值连接

注意事项：

  1. 多个连接条件与 AND 操作符

  2. 区分重复的列名，当多个表中有相同列时，必须在列名之前加上表名前缀，在不同表中具有相同列名的列可以用表名加以区分。

  3. 使用表的别名可以简化查询，列名前使用表名前缀可以提高查询效率。但需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。

  4. 连接 n个表,至少需要n- 1 个连接条件。 比如，连接三个表，至少需要两个连接条件。

非等值连接

一个用户查询请求涉及到多个表的时候，连接两个表的条件为=时，就是等值连接连接；其他的运算符连接的就是非等值连接。
注意：连接条件中的各连接字段类型必须是可比的，但不必是相同的，整型和浮点型是可比的，但是字符型和整型就不可比。

分类 2 ：自连接 vs 非自连接

自连接就是当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询操作。

分类 3 ：内连接 vs 外连接

内连接

合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行；

外连接

两个表在连接过程中除了返回满足连接条件的行以外 还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接 。没有匹配的行时, 结果表中相应的列为空(NULL)。

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

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

SQL 92 语法：使用(+)创建连接

在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接，而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

下面是SQL92关于外连接的语法：(层次性和可读性不强不推荐使用)

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

3. SQL 99 语法实现多表查询

3. 1 基本语法

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

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

3. 2 内连接(INNER JOIN)的实现

语法说明：

  • 可以使用 ON 子句指定额外的连接条件 ，这个连接条件是与其它条件分开的，ON 子句使语句具有更高的易读性 ；

  • 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接；

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

3. 3 外连接(OUTER JOIN)的实现

3. 3. 1 左外连接(LEFT OUTER JOIN)

语法格式：

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

3. 3. 2 右外连接(RIGHT OUTER JOIN)

语法格式：

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

需要注意的是：LEFT JOIN 和 RIGHT JOIN 只存在于 SQL 99 及以后的标准中，在 SQL 92 中不存在，只能用 (+) 表示。

3. 3. 3 满外连接(FULL OUTER JOIN)

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

SQL 99 是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。

需要注意的是：MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

4. UNION的使用

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNIONALL关键字分隔。

语法格式：
1
2
3
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

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

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

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

5. SQL 99 语法新特性

5. 1 自然连接

SQL 99 在 SQL 92 的基础上提供了一些特殊语法，比如NATURAL JOIN用来表示自然连接。我们可以把自然连接理解为 SQL 92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

在SQL 92 标准中写法：

1
2
3
4
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 99 中你可以写成：

1
2
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

5. 2 USING连接

当我们进行连接的时候，SQL 99 还支持使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用。比如：

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

与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING的括号 () 中填入要指定的同名字段。同时使用 JOIN…USING 可以简化 JOIN ON 的等值连接。它与下面的 SQL 查询结果是相同的：

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

6. 章节小结

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

  • WHERE：适用于所有关联查询

  • ON：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。

  • USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

值得注意的是：我们要控制连接表的数量。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。

1
2
3
4
#把关联条件写在where后面
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
1
2
3
4
#把关联条件写在on后面，只能和JOIN一起使用
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;
1
2
3
4
#把关联字段写在using()中，只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同，而且只能表示=
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);

第 06 章 聚合函数

区别于MySQL对单个值操作的单行函数，实际上 SQL 函数还有一类，叫做聚合（或聚集、分组）函数，它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

1. 聚合函数介绍

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

  • 聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。

1. 1 AVG和SUM函数

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

1. 2 MIN和MAX函数

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

1. 3 COUNT函数

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

问题1：用count(*)，count(1)，count(列名)谁好呢?

这取决于所用的引擎，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，好于具体的count(列名)。

问题2：能不能使用count(列名)替换count(*)?

不要使用 count(列名)来替代 count(*)，count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

2. GROUP BY

2. 1 基本使用

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

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

2. 2 GROUP BY中使用WITH ROLLUP

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

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

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

3. HAVING

3. 1 基本使用

使用HAVING子句来过滤分组

使用情况如下：

  1. 行已经被分组。

  2. 使用了聚合函数，注意不能在 WHERE 子句中使用聚合函数。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
    HAVING MAX(salary)> 10000 ;

    # 下面为错误的使用方法
    SELECT department_id, AVG(salary)
    FROM employees
    WHERE AVG(salary) > 8000
    GROUP BY department_id;

  3. 满足HAVING 子句中条件的分组将被显示。

  4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

3. 2 WHERE和HAVING的对比

区别 1 ：

  • WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；

  • HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

    这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。

区别 2 ：

如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。 这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

各自的优点与缺点：

  • WHERE 先筛选数据再关联，执行效率高，但不能使用分组中的计算函数进行筛选

  • HAVING 可以使用分组中的计算函数，在最后的结果集中进行筛选，但执行效率较低

开发中的参考选择：

WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

4. SELECT的执行过程

4. 1 查询的结构

1
2
3
4
5
6
7
8
9
#方式 1 ：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#方式 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：分页

4. 2 SELECT执行顺序

你需要记住 SELECT 查询时的两个顺序：

  1. 关键字的顺序是不能颠倒的，关键字顺序如下：

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

  2. SELECT 语句的执行顺序 （在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

比如你写了一个 SQL 语句，那么它的执行顺序如下：

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

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

第 07 章 子查询

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

1. 子查询基本情况

1. 1 子查询的基本使用

子查询的基本语法结构例子：

1
2
3
4
5
SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);

注意事项：

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

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

1. 2 子查询的分类

分类方式 1 ：

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

分类方式 2 ：

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

  • 子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。
  • 如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

2. 多行子查询

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

2. 1 多行比较操作符

IN 等于列表中的 任意一个

ANY 需要和单行比较操作符一起使用，和子查询返回的 某一个 值比较

ALL 需要和单行比较操作符一起使用，和子查询返回的 所有 值比较

SOME 实际上是ANY的别名，作用相同，一般常使用ANY

3. 相关子查询

3. 1 相关子查询执行流程

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

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

  • 在ORDER BY 中使用子查询：

3. 2 EXISTS 与 NOT EXISTS关键字

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

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

1
2
3
4
5
6
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);

值得注意的是：

当既可以使用子查询，也可以使用自连接时，一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

第 8 章 创建和管理表

1. 基础知识

1. 1 标识符命名规则

  • 数据库名、表名不得超过 30 个字符，变量名限制为 29 个，必须只能包含 A–Z, a–z, 0 – 9 , _共 63 个字符

  • 数据库名、表名、字段名等对象名中间不要包含空格

  • 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名，必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来

  • 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

1. 2 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

数据类型描述：

  • INT 从-2^31到2^31-1的整型数据。存储大小为 4 个字节

  • CHAR(size) 定长字符数据。若未指定，默认为 1 个字符，最大长度 255

  • VARCHAR(size) 可变长字符数据，根据字符串实际长度保存， 必须指定长度

  • FLOAT(M,D)单精度，占用 4 个字节，M=整数位+小数位，D=小数位。 D<=M<=255,0<=D<=30，默认M+D<=6

  • DOUBLE(M,D) 双精度，占用 8 个字节，D<=M<=255,0<=D<=30，默认M+D<=15

  • DECIMAL(M,D)高精度小数，占用M+2个字节，D<=M<=65，0<=D<=30，最大取值范围与DOUBLE相同。

  • DATE 日期型数据，格式’YYYY-MM-DD’

  • BLOB 二进制形式的长文本数据，最大可达4G

  • TEXT 长文本数据，最大可达4G

2. 创建和管理数据库

2. 1 创建数据库

方式 1 ：创建数据库

1
CREATE DATABASE 数据库名;

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

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

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

1
CREATE DATABASE IF NOT EXISTS 数据库名;

如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。

2. 2 使用数据库

查看当前所有的数据库

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

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

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

查看指定库下所有的表

1
SHOW TABLES FROM 数据库名;

查看数据库的创建信息

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

使用/切换数据库

1
USE 数据库名;

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

2. 3 修改数据库

更改数据库字符集

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

2. 4 删除数据库

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

1
DROP DATABASE 数据库名;

方式 2 ：判断数据库是否存在后再删除指定的数据库（推荐）

1
DROP DATABASE IF EXISTS 数据库名;

3. 创建表

3. 1 创建方式

语法格式：

加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

必须指定：表名、列名(或字段名)，数据类型， 长度

可选指定：表约束条件

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

3. 2 查看数据表结构

在MySQL中创建好数据表之后，可以查看数据表的结构。MySQL支持使用DESCRIBE/DESC语句查看数据表结构，也支持使用SHOW CREATE TABLE语句查看数据表结构。使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。

1
SHOW CREATE TABLE 表名;

4. 修改表

修改表指的是修改数据库中已经存在的数据表的结构。使用 ALTER TABLE 语句可以实现：

向已有的表中添加列

1
ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

修改现有表中的列

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

删除现有表中的列

1
ALTER TABLE 表名 DROP 【COLUMN】字段名

重命名现有表中的列

1
ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

5. 重命名表

方式一：使用RENAME

1
2
RENAME TABLE emp
TO myemp;

方式二：必须是对象的拥有者

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

6. 删除表

在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。

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

IF EXISTS的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。

注意：DROP TABLE 语句不能回滚

7. 清空表

TRUNCATE TABLE语句：删除表中所有的数据

1
TRUNCATE TABLE 数据表;

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

第 9 章 增删改语句

1. 插入数据

1. 1 将数据插入表中

使用INSERT 语句向表中插入数据。

方式 1 ：VALUES的方式添加，使用这种语法一次只能向表中插入一条数据。

情况 1 ：为表的所有字段按默认顺序插入数据，

  • 值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。
1
2
INSERT INTO 表名
VALUES (value1,value2,....);

情况 2 ：为表的指定字段插入数据

  • 为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。

  • 在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,….valuen需要与column1,…columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。

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

情况 3 ：同时插入多条记录

  • INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开

    基本语法格式如下：

1
2
3
4
5
6
INSERT INTO table_name
VALUES
(value1 [,value2, ..., valuen]),
(value1 [,value2, ..., valuen]),
......
(value1 [,value2, ..., valuen]);

或者

1
2
3
4
5
6
INSERT INTO table_name(column1 [, column2, ..., columnn])
VALUES
(value1 [,value2, ..., valuen]),
(value1 [,value2, ..., valuen]),
......
(value1 [,value2, ..., valuen]);

注意事项：

  • VALUES也可以写成VALUE，但是VALUES是标准写法。

  • 字符和日期型数据应包含在单引号中。

1. 2 将查询结果插入到表中

INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。

基本语法格式如下：

1
2
3
4
5
6
INSERT INTO 目标表名
(tar_column1 [, tar_column2, ..., tar_columnn])
SELECT
(src_column1 [, src_column2, ..., src_columnn])
FROM 源表名
[WHERE condition]

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

2. 更新数据

使用 UPDATE 语句更新数据。语法如下：

1
2
3
UPDATE table_name
SET 更新字段=更新值
[WHERE condition]

  • 可以一次更新多条数据。

  • 如果需要回滚数据，需要保证在DML前，进行设置： SET AUTOCOMMIT = FALSE;

  • 使用 WHERE 子句指定需要更新的数据，如果省略 WHERE 子句，则表中的所有数据都将被更新。

3. 删除数据

使用 DELETE 语句从表中删除数据

1
DELETE FROM table_name [WHERE <condition>];

table_name指定要执行删除操作的表；“[WHERE ]”为可选参数，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录。

truncate table与delete table对比：

相同点：都可以删除表中所有数据，同时保留表结构
不同点：truncate table清除全部表数据后不可回滚；delete table不带where时也清除全部表数据，同时数据可以回滚；

第 10 章 约束

1. 约束概述

1. 1 什么是约束

约束是表级的强制规定。可以在 创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建后通过 ALTER TABLE 语句规定约束 。

1. 2 约束的分类

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

  • 单列约束 ：每个约束只约束一列

  • 多列约束 ：每个约束可约束多列数据

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

  • 列级约束 ：只能作用在一个列上，跟在列的定义后面

  • 表级约束 ：可以作用在多个列上，不与列一起，而是单独定义

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

  • NOT NULL 非空约束，规定某个字段不能为空

  • UNIQUE 唯一约束 ， 规定某个字段在整个表中是唯一的

  • PRIMARY KEY 主键(非空且唯一)约束

  • FOREIGN KEY 外键约束

  • CHECK 检查约束

  • DEFAULT 默认值约束

**注意： **MySQL不支持check约束，但可以使用check约束，不过没有任何效果

2. 非空约束

2. 1 作用

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

2. 2 关键字

NOT NULL

2. 3 特点

  • 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型

  • 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空，一个表可以有很多列都分别限定了非空

  • 空字符串’不等于NULL， 0 也不等于NULL

2. 4 添加非空约束

（1）建表时

1
2
3
4
5
CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型 NOT NULL,
字段名 数据类型 NOT NULL
);

（2）建表后

1
alter table 表名称 modify 字段名 数据类型 not null;

2. 5 删除非空约束

1
2
3
alter table 表名称 modify 字段名 数据类型 NULL;#去掉not null，相当于修改某个非注解字段，该字段允许为空
# 或者
alter table 表名称 modify 字段名 数据类型;#去掉not null，相当于修改某个非注解字段，该字段允许为空

3. 唯一性约束

3. 1 作用

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

3. 2 关键字

UNIQUE

3. 3 特点

  • 同一个表可以有多个唯一约束。

  • 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。

  • 唯一性约束允许列值为空。

  • 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。

  • MySQL会给唯一约束的列上默认创建一个唯一索引。

3. 4 添加唯一约束

（ 1 ）建表时

1
2
3
4
5
6
7
8
9
10
11
12
13
create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key,
字段名 数据类型
);
# 或者
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名)
);

（ 2 ）建表后指定唯一键约束

1
2
3
#字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的
#方式 1 ：
alter table 表名称 add unique key(字段列表);
1
2
#方式 2 ：
alter table 表名称 modify 字段名 字段类型 unique;

3. 5 关于复合唯一约束

1
2
3
4
5
6
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
unique key(字段列表) #字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的
);

3. 6 删除唯一约束

  • 添加唯一性约束的列上也会自动创建唯一索引，删除唯一约束只能通过删除唯一索引的方式删除。

  • 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

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

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

4. PRIMARY KEY 主键约束

4. 1 作用

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

4. 2 关键字

primary key

4. 3 特点

  • 主键约束相当于 唯一约束+非空约束的组合 ，主键约束列不允许重复，也不允许出现空值。

  • 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。

  • 主键约束对应着表中的一列或者多列（复合主键），如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。

  • 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。

值得注意的是：不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

4. 4 添加主键约束

（ 1 ）建表时指定主键约束

1
2
3
4
5
6
7
8
9
10
11
12
create table 表名称(
字段名 数据类型 primary key, #列级模式
字段名 数据类型,
字段名 数据类型
);
# 或者
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段名) #表级模式
);

（ 2 ）建表后增加主键约束

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

4. 5 关于复合主键

1
2
3
4
5
6
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
primary key(字段名1,字段名2)  #表示字段 1 和字段 2 的组合是唯一的，也可以有更多个字段
);

4. 6 删除主键约束

1
alter table 表名称 drop primary key;

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

5. 自增列：AUTO_INCREMENT

5. 1 作用

某个字段的值自增

5. 2 关键字

auto_increment

5. 3 特点和要求

  • 一个表最多只能有一个自增长列

  • 当需要产生唯一标识符或顺序值时，可设置自增长

  • 自增长列约束的列必须是键列（主键列，唯一键列）

  • 自增约束的列的数据类型必须是整数类型

  • 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

5. 4 如何指定自增约束

（ 1 ）建表时

1
2
3
4
5
6
7
8
9
10
11
12
13
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(字段名)
);

（ 2 ）建表后

1
alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束

5. 5 删除自增约束

1
alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除

6. FOREIGN KEY 约束

6. 1 作用

限定某个表的某个字段的引用完整性。

6. 2 关键字

FOREIGN KEY

6. 3 主表和从表/父表和子表

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

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

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

例如2：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。

6. 4 特点

（ 1 ）从表的外键列，必须引用/参考主表的主键或唯一约束的列，因为被依赖/被参考的值必须是唯一的

（ 2 ）在创建外键约束时，如果不给外键约束命名， 默认名不是列名，而是自动产生一个外键名，也可以指定外键约束名。

（ 3 ）创建(CREATE)表时就指定外键约束的话，必须先创建主表，再创建从表

（ 4 ）删表时，先删从表（或先删除外键约束），再删除主表

（ 5 ）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据

（ 6 ）在“从表”中指定外键约束，并且一个表可以建立多个外键约束

（ 7 ）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。

（ 8 ） 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引 。但是索引名是外键的约束名。（根据外键查询效率很高）

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

6. 5 添加外键约束

（ 1 ）建表时

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

（ 2 ）建表后

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

6. 6 删除外键约束

流程如下：

1
2
3
4
5
6
7
8
9
( 1 )第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名

ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;

（ 2 ）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; #查看某个表的索引名

ALTER TABLE 从表名 DROP INDEX 索引名;

7. DEFAULT约束

7. 1 作用

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

7. 2 关键字

DEFAULT

7. 3 如何给字段加默认值

（ 1 ）建表时
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);


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

# 说明：默认值约束一般不在唯一键和主键列上加
（ 2 ）建表后
1
alter table 表名称 modify 字段名 数据类型 default 默认值

7. 4 删除默认值约束

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

第 11 章 视图

1. 常见的数据库对象

  • 表(TABLE)

    表是存储数据的逻辑单元，以行和列的形式存在，列就是字段，行就是记录

  • 数据字典

    就是系统表，存放数据库相关信息的表。系统表的数据通常由数据库系统维护，程序员通常不应该修改，只可查看

  • 约束(CONSTRAINT)

    执行数据校验的规则，用于保证数据完整性的规则

  • 视图(VIEW)

    一个或者多个数据表里的数据的逻辑显示，视图并不存储数据

  • 索引(INDEX)

    用于提高查询性能，相当于书的目录

  • 存储过程(PROCEDURE)

    用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境

  • 存储函数(FUNCTION)

    用于完成一次特定的计算，具有一个返回值

  • 触发器(TRIGGER)

    相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

2. 视图概述

2. 1 为什么使用视图？

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

2. 2 视图的理解

  • 视图是一种虚拟表，本身是不具有数据的，占用很少的内存空间，它是 SQL 中的一个重要概念。

  • 视图建立在已有表的基础上 , 视图赖以建立的这些表称为基表 。

  • 视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。

  • 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句

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

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

3. 创建视图

在 CREATE VIEW 语句中嵌入子查询

1
2
CREATE VIEW 视图名称
AS 查询语句

说明 1 ：实际上就是我们在 SQL 查询语句的基础上封装了视图 VIEW，这样就会基于 SQL 语句的结果集形

成一张虚拟表。

说明 2 ：在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字

段列表一致。如果SELECT语句中给字段取了别名，那么视图中的字段名和别名相同。

4. 查看视图

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

1
SHOW TABLES;

语法 2 ：查看视图的结构

1
DESC / DESCRIBE 视图名称;

语法 3 ：查看视图的属性信息

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

语法 4 ：查看视图的详细定义信息

1
SHOW CREATE VIEW 视图名称;

5. 不可更新的视图

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

  • 在定义视图的时候指定了“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；

  • 视图定义基于一个不可更新视图；

  • 常量视图。

  • 在定义视图的SELECT语句中使用了JOIN联合查询，视图将不支持更新操作。

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

6. 修改、删除视图

6. 1 修改视图

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

注意：CREATE VIEW 子句中各列的别名应和子查询中各列相对应。

方式 2 ：ALTER VIEW

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

6. 2 删除视图

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

1
DROP VIEW IF EXISTS 视图名称;

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

7. 总结

7. 1 视图优点

  1. 操作简单

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

  2. 减少数据冗余

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

  3. 数据安全

    MySQL将用户对数据的访问限制在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表。同时，MySQL可以根据权限将用户对数据的访问限制在某些视图上， 用户不需要查询数据表，可以直接通过视图获取数据表中的信息 。这在一定程度上保障了数据表中数据的安全性。

  4. 适应灵活多变的需求

    当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较大，可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。

  5. 能够分解复杂的查询逻辑

    数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。

7. 2 视图不足

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

实际项目中，如果视图过多，会导致数据库维护成本的问题。所以，在创建视图的时候，你要结合实际项目需求，综合考虑视图的优点和不足，这样才能正确使用视图，使系统整体达到最优。

第 12 章 存储过程与函数

1. 存储过程概述

1. 1 理解

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

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

好处 ：

  1. 简化操作，提高了sql语句的重用性，减少了开发程序员的压力

  2. 减少操作过程中的失误，提高效率

  3. 减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）

  4. 减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性

和视图、函数的对比 ：

它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是没有返回值的。

1. 2 分类

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

  1. 没有参数（无参数无返回）
  2. 仅仅带 IN 类型（有参数无返回）
  3. 仅仅带 OUT 类型（无参数有返回）
  4. 既带 IN 又带 OUT（有参数有返回）
  5. 带 INOUT（有参数有返回）

注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。

2. 创建存储过程

2. 1 语法分析

语法：

1
2
3
4
5
6
7
8
9
DELIMITER 新的结束标记
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
存储过程体
END 新的结束标记
修饰符 返回类型 方法名(参数类型 参数名,...){
方法体;
}

说明：

  1. 参数前面的符号的意思

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

  • OUT：当前参数为输出参数，也就是表示出参；执行完成后，调用这个存储过程客户端或者应用程序就可以读取这个参数返回值了。

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

  1. 形参类型可以是 MySQL数据库中的任意类型。

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

  • 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表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。如果没有设置相关的值，则MySQL默认指定值为DEFINER。

  • COMMENT ‘string’：注释信息，可以用来描述存储过程。

  1. 存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句。

  2. 需要设置新的结束标记

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

比如：“DELIMITER //”语句的作用是将MySQL的结束符设置为//，并以“END //”结束存储过程。存储过程定义完毕后再使用“DELIMITER ;”恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。当使用DELIMITER命令时，应该避免使用反斜杠（‘\’）字符，因为反斜线是MySQL的转义字符。

3. 调用存储过程

调用格式如下：

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

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

4. 存储函数的使用

4. 1 语法分析

语法格式：

1
2
3
4
5
6
7
CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
函数体 #函数体中肯定有 RETURN 语句
END
SELECT 函数名(实参列表)

说明：

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

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

  3. characteristic 创建函数时指定的对函数的约束。

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

4. 2 调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。

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

5. 1 查看

创建完之后，怎么知道我们创建的存储过程、存储函数是否成功了呢？MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看，也可直接从系统的information_schema数据库中查询。这里介绍 3 种方法。

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

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

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

    1
    2
    3
    SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

    #[LIKE 'pattern']：匹配存储过程或函数的名称，可以省略。当省略不写时，会列出MySQL数据库中存在的所有存储过程或函数的信息。

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

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

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

说明：如果在MySQL数据库中存在存储过程和函数名称相同情况，最好指定ROUTINE_TYPE查询条件来指明查询的是存储过程还是函数。

5. 2 修改

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

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

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

注意：修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句。但是，这两个语句的结构是一样的，语句中的所有参数也是一样的。

5. 3 删除

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

1
2
DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名
# IF EXISTS：如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告。

6. 关于存储过程使用的争议

6. 1 优点

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

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

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

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

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

6. 2 缺点

存储过程虽然有诸如上面的好处，但缺点也是很明显的。

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

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

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

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

第 13 章 变量、流程控制与游标

1. 变量

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

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

1. 1 系统变量

1. 1. 1 系统变量分类

  • 变量由系统定义，不是用户定义，属于服务器层面。启动MySQL服务，生成MySQL服务实例期间，MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。这些系统变量的值要么是编译MySQL时参数的默认值，要么是配置文件（例如my.ini等）中的参数；

  • 系统变量分为全局系统变量（需要添加global 关键字）以及会话系统变量（需要添加 session 关键字），有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。 如果不写，默认为会话级别。 静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系统变量。

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

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

1. 1. 2 查看系统变量

查看所有或部分系统变量

1
2
3
4
5
6
7
8
9
10
11
12
13
#查看所有全局变量
SHOW GLOBAL VARIABLES;

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

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

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

查看指定系统变量

1
2
3
4
5
6
7
#查看指定的系统变量的值
SELECT @@global.变量名;

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

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

1.1.3修改系统变量

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

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

1
2
3
4
5
6
7
8
9
10
11
#为某个系统变量赋值
#方式 1 ：
SET @@global.变量名=变量值;
#方式 2 ：
SET GLOBAL 变量名=变量值;

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

1. 2 用户变量

1. 2. 1 用户变量分类

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

  • 会话用户变量：作用域和会话变量一样，只对当前连接会话有效。

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

1. 2. 2 会话用户变量

作用域：针对于当前会话有效，可定义在会话的任何位置，等同于会话变量的作用域

  1. 查看用户变量的值
1
SELECT @用户变量
  1. 声明变量并初始化
1
2
3
#方式 1 ：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;
  1. 赋值（更新用户变量的值）
1
2
3
#方式 2 ：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];

1. 2. 3 局部变量

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

作用域：仅仅在定义它的BEGIN … END 中有效，只能放在 BEGIN … END 中，而且只能放在第一句

  1. 定义变量
1
DECLARE 变量名 类型 [default 值];  # 如果没有DEFAULT子句，初始值为NULLBEGIN
  1. 变量赋值

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

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

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

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

3 .使用变量 （查看、比较、运算等)

1
SELECT 局部变量名;

2. 流程控制

只要是执行的程序，流程就分为三大类：

  • 顺序结构：程序从上往下依次执行

  • 分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行

  • 循环结构：程序满足一定条件下，重复执行一组语句

针对于MySQL 的流程控制语句主要有 3 类。注意：只能用于存储程序。

  • 条件判断语句：IF 语句和 CASE 语句

  • 循环语句：LOOP、WHILE 和 REPEAT 语句

  • 跳转语句：ITERATE 和 LEAVE 语句

2. 1 分支结构之 IF

IF 语句的语法结构是：

1
2
3
4
5
6
IF 表达式 1 THEN 操作 1
[ELSEIF 表达式 2 THEN 操作2]......
[ELSE 操作N]
END IF

#根据表达式的结果为TRUE或FALSE执行相应的语句。这里“[]”中的内容是可选的。

特点：① 不同的表达式对应不同的操作 ② 使用在begin end中

2. 2 分支结构之 CASE

CASE 语句的语法结构 1 ：

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

CASE 语句的语法结构 2 ：

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

2. 3 循环结构之LOOP

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

LOOP语句的基本格式如下：

1
2
3
4
5
[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label]

#其中，loop_label表示LOOP语句的标注名称，该参数可以省略

2. 4 循环结构之WHILE

WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。WHILE语句的基本格式如下：

1
2
3
4
5
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];

# while_label为WHILE语句的标注名称；如果循环条件结果为真，WHILE语句内的语句或语句群被执行，直至循环条件为假，退出循环

2. 5 循环结构之REPEAT

REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。

REPEAT语句的基本格式如下：

1
2
3
4
5
6
[repeat_label:] REPEAT
　　　　循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]

# repeat_label为REPEAT语句的标注名称，该参数可以省略；REPEAT语句内的语句或语句群被重复，直至expr_condition为真。

对比三种循环结构：

  • loop：一般用于实现简单的死循环
  • while：先判断后执行
  • repeat：先执行后判断，无条件至少执行一次

2. 6 跳转语句之LEAVE语句

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

基本格式如下：

1
2
LEAVE label
# 其中，label参数表示循环的标志。LEAVE和BEGIN ... END或循环一起被使用。

2. 7 跳转语句之ITERATE语句

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

语句基本格式如下：

1
2
ITERATE label
# label参数表示循环的标志。ITERATE语句必须跟在循环标志前面。

3. 游标

3. 1 什么是游标

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

  • 在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，我们可以通过操作游标来对数据行进行操作。MySQL中游标可以在存储过程和函数中使用。

3. 2 使用游标步骤

游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。如果我们想要使用游标，一般需要经历四个步骤。不同的 DBMS 中，使用游标的语法可能略有不同。

第一步，声明游标

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

1
DECLARE cursor_name CURSOR FOR select_statement;

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

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

1
DECLARE cursor_name CURSOR IS select_statement;

要使用 SELECT 语句来获取数据结果集，而此时还没有开始遍历数据，这里 select_statement 代表的是SELECT 语句，返回一个用于创建游标的结果集。

第二步，打开游标

打开游标的语法如下：

1
OPEN cursor_name

当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区，为后面游标的逐条读取结果集中的记录做准备。

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

语法如下：

1
FETCH cursor_name INTO var_name [, var_name] ...

这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。

注意事项：

  1. var_name必须在声明游标之前就定义好。

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

第四步，关闭游标

1
CLOSE cursor_name

当我们使用完游标后需要关闭掉该游标。因为游标会占用系统资源，如果不及时关闭， 游标会一直保持到存储过程结束 ，影响系统运行的效率。而关闭游标的操作，会释放游标占用的系统资源。关闭游标之后，我们就不能再检索查询结果中的数据行，如果需要检索只能再次打开游标。

第 14 章_触发器

1. 触发器概述

触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE、DELETE事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。

当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

2. 触发器的创建

创建触发器的语法结构是：

1
2
3
4
5
6
7
8
9
10
11
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;

# 表名：表示触发器监控的对象。
# BEFORE|AFTER：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。
# INSERT|UPDATE|DELETE：表示触发的事件。
# INSERT 表示插入记录时触发；
# UPDATE 表示更新记录时触发；
# DELETE 表示删除记录时触发。

触发器执行的语句块可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块。

3. 查看、删除触发器

3. 1 查看触发器

查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等。

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

1
SHOW TRIGGERS;

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

1
SHOW CREATE TRIGGER 触发器名

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

1
SELECT * FROM information_schema.TRIGGERS;

3. 2 删除触发器

触发器也是数据库对象，删除触发器也用DROP语句，语法格式如下：

1
DROP TRIGGER IF EXISTS 触发器名称;

4. 触发器的优缺点

4. 1 优点

  1. 触发器可以确保数据的完整性 。

  2. 触发器可以帮助我们记录操作日志。

  3. 触发器还可以用在操作数据前，对数据进行合法性检查。

4. 2 缺点

  1. 触发器最大的一个问题就是可读性差。

因为触发器存储在数据库中，并且由事件驱动，这就意味着触发器有可能不受应用层的控制。这对系统维护是非常有挑战的。

  1. 相关数据的变更，可能会导致触发器出错。

特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率。