MySQL（基础）

Q QWT65RY*（查看数据库最新排名: https://db-engines.com/en/ranking）*

第一章数据库概述

1. 为什么要使用数据库

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

.
.

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

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

3. MySQL介绍

• MySQL是一个 开放源代码的关系型数据库管理系统 ，由瑞典MySQL AB（创始人Michael Widenius）公司1995年开发，迅速成为开源数据库的 No.1。
• MySQL6.x 版本之后分为 社区版 和 商业版 。
• MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
• MySQL是可以定制的，采用了 GPL（GNU General Public License） 协议，你可以修改源码来开发自己的MySQL系统。
• MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持 4GB ，64位系统支持最大的表文件为 8TB 。
• MySQL使用 标准的SQL数据语言 形式。
• MySQL可以允许运行于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP和Ruby等。

.

• 关于MySQL 8.0:
  MySQL从5.7版本直接跳跃发布了8.0版本 ，可见这是一个令人兴奋的里程碑版本。MySQL 8版本在功能上做了显著的改进与增强，开发者对MySQL的源代码进行了重构，最突出的一点是多MySQL Optimizer优化器进行了改进。不仅在速度上得到了改善，还为用户带来了更好的性能和更棒的体验。

.
.

4. RDBMS 与 非RDBMS

一、关系型数据库(RDBMS)

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

• 关系型数据库以 行(row) 和 列(column) 的形式存储数据，以便于用户理解。\

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

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

.

二、非关系型数据库(非RDBMS)

• 非关系型数据库，可看成传统关系型数据库的功能 阉割版本 ，基于键值对存储数据，不需要经过SQL层的解析， 性能非常高 。同时，通过减少不常用的功能，进一步提高性能。
• 相比于 SQL，NoSQL 泛指非关系型数据库，包括了键值型数据库、文档型数据库、搜索引擎和列存储等，除此以外还包括图形数据库。也只有用 NoSQL 一词才能将这些技术囊括进来。
  - NoSQL 对 SQL 做出了很好的补充，比如实际开发中，有很多业务需求，其实并不需要完整的关系型数据库功能，非关系型数据库的功能就足够使用了。这种情况下，使用 性能更高 、 成本更低 的非关系型数据库当然是更明智的选择。
  比如：日志收集、排行榜、定时器等。

.
.

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

• 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
• 表具有一些特性，这些特性定义了数据在表中如何存储，类似Java和Python中 “类”的设计。

一、表、记录、字段

• E-R（entity-relationship，实体-联系）模型中有三个主要概念是： 实体集 、 属性 、 联系集 。

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

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

二、表的关联关系

• 表与表之间的数据记录有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。
• 四种：一对一关联、一对多关联、多对多关联、自我引用

一对一关联（one-to-one）
一对多关系（one-to-many）
多对多（many-to-many）
自我引用(Self reference)

.
.
.

第二章MySQL的登录

一、服务的启动与停止

• 方式1：使用图形界面工具

步骤①：打开windows服务
步骤②：找到MySQL80（点击鼠标右键）→ 启动或停止（点击）

• 方式2：使用cmd命令

# 启动 MySQL 服务命令：
	net start MySQL服务名
# 停止 MySQL 服务命令：
	net stop MySQL服务名

说明：
1. start和stop后面的服务名应与之前配置时指定的服务名一致。
2. 如果当你输入命令后，提示“拒绝服务”，请以 系统管理员身份 打开命令提示符界面重新尝试。

.
.

二、自带客户端的登录与退出
当MySQL服务启动完成后，便可以通过客户端来登录MySQL数据库。注意：确认服务是开启的。

• 登录方式1：MySQL自带客户端

开始菜单 → 所有程序 → MySQL → MySQL 8.0 Command Line Client
进去输入密码就可以~

• 登录方式2：使用cmd命令登入

格式：
	mysql -h 主机名 -P 端口号 -u 用户名 -p密码

举例：
	mysql -h localhost -P 3306 -u root -pabc123 # 这里我设置的root用户的密码是abc123

注意：
	① -p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格。
	② 客户端和服务器在同一台机器上，所以输入localhost或者IP地址127.0.0.1。同时，因为是连接本机： -hlocalhost就可以省略，如果端口号没有修改：-P3306也可以省略。
		简写成：
			mysql -u root -p
			Enter password:****
	
	③ 可以在命令行通过以下方式获取MySQL Server服务版本的信息：
		c:\> mysql -V
		c:\> mysql --version
		或登录后，通过以下方式查看当前版本信息：
			mysql> select version();

• 登录方式3：MySQL图形化管理工具

MySQL图形化管理工具极大地方便了数据库的操作与管理，常用的图形化管理工具有：MySQL
Workbench、phpMyAdmin、Navicat Preminum、MySQLDumper、SQLyog、dbeaver、MySQL ODBC
Connector。

.
.

三、MySQL的编码设置

• 命令行操作sql乱码问题

mysql> INSERT INTO t_stu VALUES(1,'张三','男');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' atrow 1

• （1)先停止服务，（2）修改my.ini文件（3）重新启动服务

步骤1：查看编码命令
	show variables like 'character_%';
	show variables like 'collation_%';

步骤2：修改mysql的数据目录下的my.ini配置文件
	[mysql] # 大概63行左右，在其下添加
	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_%';

.
.
.

第三章基本的SELECT语句

1. SQL概述

• SQL（Structured Query Language，结构化查询语言）是使用关系模型的数据库应用语言， 与数据直接打交道 ，由 IBM 上世纪70年代开发出来。后由美国国家标准局（ANSI）开始着手制定SQL标准，先后有 SQL-86 ， SQL-89 ， SQL-92 ， SQL-99 等标准。
• SQL 有两个重要的标准，分别是 SQL92 和 SQL99，它们分别代表了 92 年和 99 年颁布的 SQL 标准，我们今天使用的 SQL 语言依然遵循这些标准。
• 不同的数据库生产厂商都支持SQL语句，但都有特有内容。
  
  .
  .

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

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

主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。

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

主要的语句关键字包括 INSERT INTO 、 DELETE 、 UPDATE 、 SELECT 等。
SELECT是SQL语言的基础，最为重要。(重中之重)

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

主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。

.
.

3. SQL语言的规则与规范

sql语言的规则：

• SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
• 每条命令以 ; 或 \g 或 \G 结束
• 关于标点符号
  字符串型和日期时间类型的数据可以使用单引号（’ '）表示
  列的别名，尽量使用双引号（" "），而且不建议省略as

.

SQL大小写规范 （建议遵守）：

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

.

命名规则：

• 数据库、表名不得超过30个字符，变量名限制为29个
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
• 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

.
.

4. 注 释：

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

.
.

5. 数据导入指令

• 方式1：在cmd命令行中使用：source 文件全路径名;
  举例：source D:\MySQL_atguigu\atguigudb.sql

• 方式2：基于具体的图形化解码的工具
  比如：SQLyog中 选择 “工具” – “执行sql脚本” – “选中xxx.sql”即可。

.
.

6. 基本的SELECT语句

• SELECT…

SELECT 1; #没有任何子句
SELECT 9/2; #没有任何子句

• SELECT 字段1,字段2,… FROM 表名;

SELECT 标识选择哪些列
FROM 标识从哪个表中选择
举例：
	SELECT department_id, location_id
	FROM departments;

	SELECT *
	FROM departments;
	
注意 *：表示表中所有的字段（列）

.
.

7. 列的别名

• 重命名一个列
• 以便在别名中包含空格或特殊的字符并区分大小写，列的别名可以使用一对""引起来，但不要使用''。
• as:全称：alias(别名),可以省略

举例：
	SELECT employee_id 编号,last_name AS "姓 氏" 
	FROM employees;

.
.

8. 去除重复行

默认情况下，查询会返回全部行，包括重复行。

• 在SELECT语句中使用关键字DISTINCT去除重复行

举例：
	SELECT DISTINCT department_id
	FROM employees;

针对于：
	SELECT DISTINCT department_id,salary
	FROM employees;
DISTINCT 其实是对后面所有列名的组合进行去重，必须两个字段的值都一样才能去除重复的行~~

• DISTINCT 是对后面所有列名的组合进行去重

.
.

9. 空值参与运算

• 所有运算符或列值遇到null值，运算的结果都为null
• 注意：在 MySQL 里面， 空值不等于空字符串或数值0。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。

.
.

10. 着重号

• 例如：SELECT * FROM ORDER; 遇到表名与关键字一样的情况

我们可以使用着重号 ` 来修饰表名：
	SELECT * FROM `order`;

• 结论:
  我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在SQL语句中使用一对``（着重号）引起来。

.
.

11. 显示表结构

• 使用DESCRIBE 或 DESC 命令，表示表结构。
  DESCRIBE employees;
  或
  DESC employees;
  

.
.

12. 过滤数据：where

• 语法：

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

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

.
.
.

第四章_运算符

1. 算术运算符
算术运算符主要用于数学运算，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。

.

• 加法与减法运算符：

① 一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
② 一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
③ 加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；

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

• 乘法与除法运算符：

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

• 求模（求余）运算符：

SELECT 12 % 3, 12 MOD 5 FROM dual; 值为：0 和 2

.
.

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

.

• 等号运算符：

① 等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0。
② 如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
③ 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
④ 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
⑤ 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。

• 安全等于运算符：

① 安全等于运算符（<=>）与等于运算符（=）的作用是相似的，唯一的区别是‘<=>’可以用来对NULL进行判断。
② 使用安全等于运算符时，两边的操作数的值都为NULL时，返回的结果为1而不是NULL，当其中一个操作数为NULL时，其返回值为0，而不为NULL。其他返回结果与等于运算符相同。

• 不等于运算符：

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

.
.

非符号类型的运算符：

.

• 空运算符：

空运算符（IS NULL或者ISNULL）判断一个值是否为NULL，如果为NULL则返回1，否则返回0。
格式：SELECT NULL IS NULL, ISNULL(NULL), ISNULL(‘a’), 1 IS NULL;

• 非空运算符：

非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。
格式：SELECT NULL IS NOT NULL, ‘a’ IS NOT NULL, 1 IS NOT NULL;

• 最小值运算符：

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

• 最大值运算符：

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

• LIKE运算符：

LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。
① “%”：匹配0个或多个字符。
② “_”：只能匹配一个字符。
③ “\”：转义字符

• REGEXP运算符：

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

REGEXP运算符在进行匹配时，常用的有下面几种通配符：

格式：SELECT ‘shkstart’ REGEXP ‘^s’, ‘shkstart’ REGEXP ‘t$’, ‘shkstart’ REGEXP ‘hk’;
返回：1，1，1

.
.

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

.

• 非运算符：

逻辑非（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。

.
.

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

.

• 按位与运算符：

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

• 按位或运算符：

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

• 按位异或运算符：

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

• 按位取反运算符：

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

由于按位取反（~）运算符的优先级高于按位与（&）运算符的优先级，所以10 & ~1，首先，对数字1进行按位取反操作，结果除了最低位为0，其他位都为1，然后与10进行按位与操作，结果为10。

• 按位右移运算符：

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

说明：1的二进制数为0000 0001，右移2位为0000 0000，对应的十进制数为0。4的二进制数为0000 0100，右移2位为0000 0001，对应的十进制数为1。

• 按位左移运算符：

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

说明：1的二进制数为0000 0001，左移两位为0000 0100，对应的十进制数为4。4的二进制数为0000 0100，左移两位为0001 0000，对应的十进制数为16。

.
.

运算符的优先级

.
.
.

第五章_排序与分页

• 如果没有使用排序存在，默认情况下查询返回的数据是按照添加数据的顺序显示的。
• 列的别名只能在 order by 中使用不能再where中使用。
  因为SELECT语句执行的时候先执行FROM（找到要查询的表）和 WHERE（过滤条件是什么），然后执行SELECT 字段1，字段2（找到要查询的字段），最后执行ORDER BY（显示的顺序规则）。

.
.

排序数据

• ORDER BY 子句在SELECT语句的结尾。
• 排序规则
  ASC（ascend）: 升序
DESC（descend）:降序

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

• 多列排序：

例如：
	SELECT last_name, department_id, salary
	FROM employees
	ORDER BY department_id, salary DESC;

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

.
.

分页

• 分页原理
  所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。
• MySQL中使用 LIMIT 实现分页
• 注意：LIMIT 子句必须放在整个SELECT语句的最后！
• 格式：
  LIMIT [位置偏移量,] 行数; （如果偏移量为0的话可以省略）

例如：
	--第11至20条记录：
	SELECT * FROM 表名 LIMIT 10,10;

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

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

.
.
.

第六章_多表查询

案例：查询员工的姓名及其部门名称

SELECT last_name, department_name
FROM employees, departments;
	一共输出2889行！！！
	
分析错误情况：
	SELECT COUNT(employee_id) FROM employees;
	#输出107行
	
	SELECT COUNT(department_id)FROM departments;
	#输出27行
	
	SELECT 107*27 FROM dual; // 2889

• 我们把上述多表查询中出现的问题称为：笛卡尔积的错误。
  笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能
  组合
• 为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。
• 加入连接条件后，查询语法：

SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;  #连接条件

• 结论：
  ① 区分重复的列名：多个表中有相同列时，必须在列名之前加上表名前缀。
  ② 表的别名：使用别名可以简化查询,只要涉及多个表，都需要在列名前加表的别名（或 表名）进行限定。
  ③ 列名前使用表名前缀可以提高查询效率。
  ④ 连接 n个表,至少需要n-1个连接条件。
• 注意：如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。

.
.

多表查询分类讲解

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

等值连接：
	SELECT e.employee_id, e.last_name, d.department_name
	FROM employees e , departments d
	WHERE e.department_id = d.department_id;

非等值连接：
	SELECT e.employee_id,e.last_name,j.grade_level
	FROM employees AS e,job_grades AS j
	WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

.

• 分类2：自连接 vs 非自连接
  当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。从而进行内连接查询。
  外连接就是连接其他表。

自连接（自己连接自己）
	SELECT e1.`employee_id`,e1.`first_name`,e2.`employee_id`,e2.`first_name`
	FROM employees e1,employees e2
	WHERE e1.`manager_id` = e2.`employee_id`;

.

• 分类3：内连接 vs 外连接
  除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。
• 内连接: 结果集中不包含一个表与另一个表不匹配的行
• 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。
  没有匹配的行时, 结果表中相应的列为空(NULL)。!!!!!!!!!!!!!!
• 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。
  如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

.

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

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

而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

.
.

SQL99语法实现多表查询

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

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

• 语法说明：

可以使用 ON 子句指定额外的连接条件。
这个连接条件是与其它条件分开的。
ON 子句子句使语句具有更高的易读性。
关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接

.
.

内连接(INNER JOIN)的实现

• 语法：可以省略INNER

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

.
.

外连接(OUTER JOIN)的实现

除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行~~~

• 左外连接(LEFT OUTER JOIN)

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

• 右外连接(RIGHT OUTER JOIN)

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

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

.
.

满外连接(FULL OUTER JOIN)

• 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
• SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
• 需要注意的是，MySQL不支持FULL JOIN UNION [ALL]的使用来代替 👇👇👇👇👇。

.
.

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

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

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

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

.
.

7种SQL JOINS的实现

-- 中图
	SELECT e.employee_id,e.`last_name`,d.`department_name`
	FROM employees AS e
	JOIN departments AS d
	ON e.`department_id` = d.`department_id`;

-- 左上图
	SELECT e.`employee_id`,e.`last_name`,d.`department_name`
	FROM employees AS e
	LEFT OUTER JOIN departments AS d
	ON e.`department_id` = d.`department_id`;

-- 右上图
	SELECT e.employee_id,e.`last_name`,d.`department_name`
	FROM employees AS e
	RIGHT OUTER JOIN departments AS d
	ON e.`department_id` = d.`department_id`;

-- 左中图
	SELECT e.employee_id,e.`last_name`,d.`department_name`
	FROM employees AS e
	LEFT OUTER JOIN departments AS d
	ON e.`department_id` = d.`department_id`
	WHERE d.`department_id` IS NULL;

-- 右中图
	SELECT e.employee_id,e.`last_name`,d.`department_name`
	FROM employees AS e
	RIGHT OUTER JOIN departments AS d
	ON e.`department_id` = d.`department_id`
	WHERE e.`department_id` <=> NULL;

-- 左下图：左上图 + 右中图
	SELECT e.`employee_id`,e.`last_name`,d.`department_name`
	FROM employees AS e
	LEFT OUTER JOIN departments AS d
	ON e.`department_id` = d.`department_id`
	UNION ALL
	SELECT e.employee_id,e.`last_name`,d.`department_name`
	FROM employees AS e
	RIGHT OUTER JOIN departments AS d
	ON e.`department_id` = d.`department_id`
	WHERE e.`department_id` <=> NULL;

-- 右下图：左中图 + 右中图
	SELECT e.employee_id,e.`last_name`,d.`department_name`
	FROM employees AS e
	LEFT OUTER JOIN departments AS d
	ON e.`department_id` = d.`department_id`
	WHERE d.`department_id` IS NULL
	UNION ALL
	SELECT e.employee_id,e.`last_name`,d.`department_name`
	FROM employees AS e
	RIGHT OUTER JOIN departments AS d
	ON e.`department_id` = d.`department_id`
	WHERE e.`department_id` <=> NULL;

.
.

拓展：SQL99语法新特性

• NATURAL JOIN 用来表示自然连接。
  我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值
  连接 。

代码：
	SELECT employee_id,last_name,department_name
	FROM employees e NATURAL JOIN departments d;

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

代码：
	SELECT employee_id,last_name,department_name
	FROM employees e JOIN departments d
	USING (department_id)

• 使用 JOIN...USING 可以简化 JOIN ON 的等值连接。

.
.

总结：

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

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

.
.
.

第七章_单行函数

单行函数:

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

一、 数值函数

1. 基本函数

.

2. 角度与弧度互换函数

.

3. 三角函数

.

4. 指数与对数

.

5. 进制间的转换

.
.

字符串函数

注意：MySQL中，字符串的位置是从1开始的。

.
.

日期和时间函数

1. 获取日期、时间

.

2. 日期与时间戳的转换

.

3. 获取月份、星期、星期数、天数等函数

.

4. 日期的操作函数

.

5. 时间和秒钟转换的函数

.

6. 计算日期和时间的函数

• 举例：
  

.

7. 日期的格式化与解析

• 非GET_FORMAT 函数中fmt参数常用的格式符：
  

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

.
.
.

流程控制函数

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

.

.
.
.

加密与解密函数

• 其中，PASSWORD(str)\ENCODE()\DECODE\,MySQL 8.0 弃用了。
• 可以看到，ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数。可逆性
• MD5()和SHA() 是不可逆的。

.
.
.

MySQL信息函数

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

.
.
.

其他函数

.
.
.

第八章_聚合函数

上一章讲到了 SQL 单行函数。实际上 SQL 函数还有一类，叫做聚合（或聚集、分组）函数，它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

.
.

一、聚合函数介绍

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

• 聚合函数类型:
  AVG()
SUM()
MAX()
MIN()
COUNT()

• 聚合函数语法
  

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

.
.

二、常用的聚合函数

1. AVG和SUM函数

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

2. MIN和MAX函数

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

3. COUNT函数

• COUNT(*)返回表中记录总数，适用于任意数据类型。
  
• COUNT(expr) 返回expr不为空的记录总数。
  
• 问题：用count(*)，count(1)，count(列名)谁好呢?
  其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
  Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好
  于具体的count(列名)。
  count(*) = count(1) > count(列名)
• 问题：能不能使用count(列名)替换count(*)?
  不要使用 count(列名)来替代 count(*) ， count(*) 是 SQL92 定义的标准统计行数的语法，跟数
  据库无关，跟 NULL 和非 NULL 无关。
  说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

.
.

三、GROUP BY的使用

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

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

• 结论一： GROUP BY 声明在 FROM后面、WHERE后面、ORDER BY 前面、LIMIT前面。
• 结论二： SELECT列表中出现的非聚合函数的字段必须声明在 GROUP BY中；反之，GROUP BY中声明的字段可以不出现在SELECT列表中。

结论二代码：
	SELECT department_id, AVG(salary)
	FROM employees
	GROUP BY department_id ;

	SELECT AVG(salary)
	FROM employees
	GROUP BY department_id ;

.
2. 使用多个列分组

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

.

3. 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的使用

• 要求1: 如果过滤条件中使用了聚合函数，则必须使用HAIVING来替换WHERE。否则，报错。
• 要求2: HAIVING 必须声明在 GROUP BY 的后面。
• 要求3: 开发中，我们使用 HAVING 的前提是 SQL 中使用了 GROUP BY。

.

WHERE和HAVING的对比

.

结论：

当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。
当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE和HAVING中都可以。但，建议使用WHERE（效率更高）。

.
.

五、SELECT的执行过程

1、查询语句的结构

.

2、SELECT执行顺序
需要记住 SELECT 查询时的两个顺序：

• 写的时候 关键字的顺序是不能颠倒的：

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

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

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

比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的：

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

.

3、SQL 的执行原理

.
.
.

第九章_子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。
因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果进行比较。

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

.
.

一、需求分析与问题解决

1、实际问题?

• 解决方式：
使用子查询 ->
  .
  .

2、子查询的基本使用

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

1. 子查询（内查询）在主查询之前一次执行完成。
2. 子查询的结果被主查询（外查询）使用 。
3. 子查询要包含在括号内
4. 将子查询放在比较条件的右侧
5. 单行操作符对应单行子查询，多行操作符对应多行子查询!!!

.
.

3、子查询的分类

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

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

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

.
.

二、单行子查询

1、单行比较操作符

• 题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
  

.

2、HAVING 中的子查询
首先执行子查询。
向主查询中的HAVING 子句返回结果。

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

.

3、CASE中的子查询

• 题目：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800
  的department_id相同，则location为’Canada’，其余则为’USA’。
  
  .

注意：

• ① 子查询为空值时子查询不返回任何行。
• ② 多行子查询使用单行比较符，报错！

.
.

三、多行子查询

内查询返回多行！！
使用多行比较操作符

.

1、多行比较操作符

.

2、例题~

• 题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
  
• 题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
  
• 题目：查询平均工资最低的部门id
  
  .

3、空值问题

• 子查询中返回有null值
  

.
.

四、相关子查询

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

.

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

子查询中使用主查询中的列

.

2、代码示例

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

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

.

• 在ORDER BY 中使用子查询：
  题目：查询员工的id,salary,按照department_name 排序
  

.3、EXISTS 与 NOT EXISTS关键字

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

• 如果在子查询中不存在满足条件的行：
  条件返回 FALSE
  继续在子查询中查找
• 如果在子查询中存在满足条件的行：
  不在子查询中继续查找
  条件返回 TRUE
• NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

.

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

.
.

子查询的编写技巧：从里往外写 或 从外往里写

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

.
.
.

第10章_创建和管理表 ~ DDL

一、 基础知识

1、一条数据存储的过程

• 从系统架构的层次上看，MySQL 数据库系统从大到小依次是 数据库服务器 、 数据库 、 数据表 、数 据表的 行与列 。

.

2、标识符命名规则

• 数据库名、表名不得超过30个字符，变量名限制为29个
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
• 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

.

3、MySQL中的数据类型

• 常用的几类类型介绍如下：
  

.
.

2、创建和管理数据库

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

1、创建数据库

.

2、使用数据库

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

.

3、 修改数据库

.

4、删除数据库

.
.

创建和管理表

1、创建表

• 创建方式一：
  

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

• 创建方式二：使用 AS subquery 选项，将创建表和插入数据结合起来，通过列名和默认值定义列。
  

.

2、查看数据表结构
在MySQL中创建好数据表之后，可以查看数据表的结构。MySQL支持使用 DESCRIBE/DESC 语句查看数据
表结构，也支持使用 SHOW CREATE TABLE 语句查看数据表结构。

.

3、修改表
修改表指的是修改数据库中已经存在的数据表的结构。

• 使用 ALTER TABLE 语句可以实现：
增：向已有的表中添加列
  改：修改现有表中的列
  删：删除现有表中的列
  重命名现有表中的列

• 添加一个列：ADD
  

• 修改一个列：MODIFY 可以修改列的数据类型，长度、默认值和位置
  

• 重命名一个列：CHANGE
  

• 删除一个列：DROP
  \

.

4、重命名表

.

5、删除表

• 在MySQL中，当一张数据表 没有与其他任何数据表形成关联关系 时，可以将当前数据表直接删除。
• 数据和结构都被删除
• 所有正在运行的相关事务被提交
• 所有相关索引被删除
  
  .

6、清空表

• TRUNCATE TABLE语句：
  删除表中所有的数据
  释放表的存储空间
  
• TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚。

.
.

DCL(数据库控制语言) 中 COMMIT 和 ROLLBACK

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

.

DDL 和 DML 的回滚说明

① DDL的操作一旦执行，就不可回滚。且使用指令 SET autocommit = FALSE 也没用！
因为执行完DDL操作之后，会在执行一次COMMIT。（而此COMMIT操作不受 SET autocommit = FALSE 影响）
② DML的操作默认情况下，也是不可以回滚的。但，如果在执行DML之前，
执行了SET autocommit = FALSE，则执行的DML操作就可以实现回滚。

.
.

第十一章_数据处理之增删改

1、增：插入数据（INSERT INTO）

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

• 情况1：为表的所有字段按默认顺序插入数据
  说明：值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。
  

• 情况2：为表的指定字段插入数据
  说明：在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,....valuen需要与column1,...columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。
  

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

注：
① 使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下： ●　Records：表明插入的记录条数。 ●　Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。 ●　Warnings：表明有问题的数据值，例如发生数据类型转换。
② 一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句 在处理过程中 效率更高 。

.

方式2：将查询结果插入到表中
INSERT还可以将SELECT语句查询的结果插入到表中。

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

2、改：更新数据（UPDATE）

• 语法如下：
  
• 可以一次更新多条数据。
• 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;
• 使用 WHERE 子句指定需要更新的数据。
  
• 如果省略 WHERE 子句，则表中的所有数据都将被更新。
  

.
.

3、删除数据（DELETE FROM）

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

• 使用 WHERE 子句删除指定的记录。
  

• 如果省略 WHERE 子句，则表中的全部数据将被删除
  

.
.

4、MySQL8新特性：计算列

• **什么叫计算列呢？**简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列 不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。
• 在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。
  
  插入演示数据，语句如下：
  
  

.
.
.

第十二章_MySQL数据类型精讲

小结及选择建议

• 在定义数据类型时，如果确定是 整数 ，就用 INT ； 如果是 小数 ，一定用定点数类型
  DECIMAL(M,D) ； 如果是日期与时间，就用 DATETIME 。
• 这样做的好处是，首先确保你的系统不会因为数据类型定义出错。不过，凡事都是有两面的，可靠性
  好，并不意味着高效。比如，TEXT 虽然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。
  

.
.
.

第十三章_约束（Constraint）

一、约束(constraint)概述

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

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

.

2、什么是约束

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

.

3、约束的分类

• 根据约束数据列的限制，约束可分为：
  单列约束：每个约束只约束一列
  多列约束：每个约束可约束多列数据

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

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

.

查看某个表已有的约束

.
.

二、非空约束（NOT NULL）

1、作用

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

.

2、特点

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

.

3、添加非空约束

• 创建表时:
  

• 创建表之后:
  

.

4、删除非空约束

.
.

三、唯一性约束（UNIPUE）

1、作用

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

.

2、特点

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

.

3、添加唯一约束

• 创建表时：
  

• 创建表后：
  
  .

4、关于复合唯一约束

.

5、删除唯一约束

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

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

.
.

四、主键约束（PRIMARY KEY）

1、作用

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

.

2、特点

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

.

3、添加主键约束

• 创建表时：
  

• 创建表之后：
  

.

4、关于复合主键

.

5、 删除主键约束

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

.
.

五、自增列（AUTO_INCREMENT）

1、作用

• 某个字段的值自增

.

2、特点和要求

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

.

3、如何指定自增约束

• 创建表时：
  
• 创建表后：
  

.

4、如何删除自增约束

.

5、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）

1、 作用

• 限定某个表的某个字段的引用完整性。
• 比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。

.

2、主表和从表/父表和子表

• 主表（父表）：被引用的表，被参考的表
• 从表（子表）：引用别人的表，参考别人的表

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

.

3、特点

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

.

4、添加外键约束

• 创建表时：
  

• 创建表之后：
  

.

5、删除外键约束

.

6、约束等级

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

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

.

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

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

.

7、开发场景

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

• 阿里开发规范
  【 强制 】不得使用外键与级联，一切外键概念必须在应用层解决。

.
.

七、检查约束（CHECK）

1、作用

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

.

2、说明：MySQL 5.7 不支持，MySQL 8.0中可以使用check约束了

.
.

八、默认值约束（DEFAULT）

1、作用

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

.

2、如何给字段加默认值

• 创建表时：
  

• 创建表之后：
  

.

3、如何删除默认值约束

.
.

九、面试

.
.
.

第十四章_视图

一、常见的数据库对象

.
.

2、视图概述

.

1、为什么使用视图

• 视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他。

.

2、视图的理解

• 视图是一种 虚拟表 ，本身是 不具有数据 的，占用很少的内存空间，它是 SQL 中的一个重要概念。
• 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。

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

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

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

.
.

三、创建视图（CREATE VIEW … AS …）

.

1、创建单表视图

• 实际上就是我们在 SQL 查询语句的基础上封装了视图 VIEW，这样就会基于 SQL 语句的结果集形成一张虚拟表。
  
• 在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字段列表一致。如果SELECT语句中给字段取了别名，那么视图中的字段名和别名相同。

.

2、创建多表联合视图

• 利用视图对数据进行格式化
  我们经常需要输出某个格式的内容，比如我们想输出员工姓名和对应的部门名，对应格式为emp_name(department_name)，就可以使用视图来完成数据格式化的操作：
  

.

3、基于视图创建视图
当我们创建好一张视图之后，还可以在它的基础上继续创建视图。

• 举例：联合“emp_dept”视图和“emp_year_salary”视图查询员工姓名、部门名称、年薪信息创建“emp_dept_ysalary”视图。
  

.
.

四、查看视图信息

.
.

五、更新视图的数据

1、一般情况

• MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的
  数据发生变化时，数据表中的数据也会发生变化
• 视图的CRUP操作和表一模一样
  例如：UPDATE操作：
  UPDATE 视图名 SET 修改的数据 WHERE 条件;
  …

.
.

2、不可更新的视图

• 要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。
  

.

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

.
.

六、修改、删除视图

1、修改视图

• 方式1：使用CREATE OR REPLACE VIEW 子句修改视图
  
  说明：OR REPLACE（如果创建的视图不存在就创建，如果存在则覆盖）

• 方式2：ALTER VIEW
  

.

2、删除视图

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

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

.
.

七、总结

1、视图的优点

.

2、视图不足

.
.
.

第十五章_存储过程与函数

MySQL从5.0版本开始支持存储过程和函数。存储过程和函数能够将复杂的SQL逻辑封装在一起，应用程
序无须关注存储过程和函数内部复杂的SQL逻辑，而只需要简单地调用存储过程和函数即可。

.
.

一、存储过程概述

1、理解

.

2、分类

• 存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：
• 1、没有参数（无参数无返回）
• 2、仅仅带 IN 类型（有参数无返回）
• 3、仅仅带 OUT 类型（无参数有返回）
• 4、既带 IN 又带 OUT（有参数有返回）
• 5、带 INOUT（有参数有返回）
  注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。

.
.

二、创建存储过程

1、语法分析

• 说明：
  
  
  
• 示例：
  

.
.

三、调用存储过程

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

1、调用格式

• 格式：
  

.

2、代码举例

.
.

四、存储函数的使用

前面学习了很多函数，使用这些函数可以对数据进行的各种处理操作，极大地提高用户对数据库的管理效率。MySQL支持自定义函数，定义好之后，调用方式与调用MySQL预定义的系统函数一样。

1、语法分析

• 语法格式：
  
• 说明：
  

.

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

• 调用格式：
  

.

3、代码举例

.

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

.

4、对比存储函数和存储过程

.
.

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

1、查看

.

2、修改

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

.

3、删除

.
.

六、关于存储过程使用的争议

尽管存储过程有诸多优点，但是对于存储过程的使用，一直都存在着很多争议，比如有些公司对于大型
项目要求使用存储过程，而有些公司在手册中明确禁止使用存储过程，为什么这些公司对存储过程的使
用需求差别这么大呢？

.
.
.

第十六章_变量、流程控制与游标

变量

在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。
在 MySQL 数据库中，变量分为 系统变量 以及 用户自定义变量 。

一、系统变量

① 系统变量分类

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

.

② 全局变量 vs 会话变量

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

• 有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。

• 如果不写关键字，默认会话级别。

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

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

③ 变量作用域

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

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

.

④ 查看系统变量

• 查看所有或部分系统变量
  
• 查看指定系统变量
  

.

⑤ 修改系统变量的值
有些时候，数据库管理员需要修改系统变量的默认值，以便修改当前会话或者MySQL服务实例的属性、特征。

• 方式一：修改MySQL 配置文件 ，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）。
• 方式二：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值。
  

注意：
一、使用“set”命令修改全局系统变量只针对于当前数据库实例是有效的，一旦重启mysql服务，就失效了。
二、使用“set”命令修改会话系统变量只针对于当前会话是有效的，一旦结束会话，重新建立起新的会话，就失效了。

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

• 使用SET GLOBAL语句设置的变量值只会 临时生效 。 数据库重启 后，服务器又会从MySQL配置文件中读取变量的默认值。 MySQL 8.0版本新增了 SET PERSIST 命令。
  
• MySQL会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。

.
.

二、用户变量

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

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

.

② 会话用户变量

.

③ 局部变量

• 定义：可以使用 DECLARE 语句定义一个局部变量
• 作用域：仅仅在定义它的 BEGIN ... END 中有效
• 位置：只能放在 BEGIN … END 中，而且只能放在第一句
  

.

④ 举例

.

⑤ 对比会话用户变量与局部变量

.
.

三、定义条件与处理程序

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

.

1、定义条件
定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。它将一个 错误名字 和 指定的错误条件 关联起来。这个名字可以随后被用在定义处理程序的 DECLARE HANDLER 语句中。

.

2、错误码的说明

.

3、定义处理程序
可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。

• 处理程序例如：
  

.

4、案例

.
.

四、流程控制

解决复杂问题不可能通过一个 SQL 语句完成，我们需要执行多个 SQL 操作。流程控制语句的作用就是控制存储过程中 SQL 语句的执行顺序，是我们完成复杂操作必不可少的一部分。只要是执行的程序，流程就分为三大类：

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

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

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

.
.

1、分支语句

① 分支结构之 IF

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

.

② 分支结构之 CASE

• 举例：
  

.
.

2、循环语句

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

• LOOP语句的基本格式如下：
  
• 其中，loop_label表示LOOP语句的标注名称，该参数可以省略。
• 举例：
  

.

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

• WHILE语句的基本格式如下：
  
• while_label为WHILE语句的标注名称；如果循环条件结果为真，WHILE语句内的语句或语句群被执行，直至循环条件为假，退出循环。
• 举例：
  

.

③ 循环结构之REPEAT
REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断。

• REPEAT语句的基本格式如下：
  
• repeat_label为REPEAT语句的标注名称，该参数可以省略；
• 举例：
  

.

④ 对比三种循环结构
这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称。

• LOOP：一般用于实现简单的"死"循环
• WHILE：先判断后执行
• REPEAT：先执行后判断，无条件至少执行一次

.

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

• 基本格式：
  
  其中，label参数表示循环的标志。LEAVE和BEGIN ... END或循环(二者是添加标记名)一起被使用。
• 举例：
  

.

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

• 语句基本格式如下：
  
• 举例：
  
  .
  .

五、游标

1、1 什么是游标（或光标）

• 虽然我们也可以通过筛选条件 WHERE 和 HAVING，或者是限定返回记录的关键字 LIMIT 返回一条记录，但是，却无法在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是 随意定位到某一条记录 ，并对记录的数据进行处理。

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

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

• MySQL中游标可以在存储过程和函数中使用。

• 举例：
  

.

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

• 举例：
  

• 小结
  ① 游标是 MySQL 的一个重要的功能，为 逐条读取 结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。
  ② 但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行 加锁 ，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会 消耗系统资源 ，造成内存不足，这是因为游标是在内存中进行的处理。

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

.
.
.

第十七章_触发器

一、触发器概述

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

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

.
.

二、触发器的创建

创建触发器语法

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

• 举例：
  

.

NEW和OLD

• 在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
• 在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
• 在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；

.
.

三、 查看和删除触发器

1、查看触发器

.

2、删除触发器

.

3、触发器 的 优点

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

.

4、触发器 的 缺点

• ① 触发器最大的一个问题就是可读性差，太隐蔽 不受应用层的控制 。这对系统维护是非常有挑战的。
• ② 相关数据的变更，可能会导致触发器出错。

.

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