• 1、数据库概述 与 MYSQL5.7、MYSQL8.0 安装篇（基于Windows）
  • MYSQL是什么，为什么要使用MYSQL
    • MYSQL的四个版本
  • MYSQL环境搭建
    • MYSQL的安装与卸载
      • Windows10 下安装MYSQL 8.26版本
        • 配置环境变量
      • Windows10 下安装MYSQL 5.7版本（属于安装2个MYSQL版本）
      • MYSQL的卸载步骤
    • MYSQL的环境问题
      • 登录到MYSQL
      • 解决MYSQL5.7中的中文乱码问题 | 8.0的数据库不用修改
      • 解决使用Navicat图形化界面工具无法连接到MYSQL 8的问题
      • MYSQL忘记密码的还原办法
  • SQL的基本介绍
    • SQL是干什么的
    • SQL的分类
      • DDL 数据定义语言
      • DML 数据操纵语言
      • DCL 数据控制语言
  • 写SQL的一些提议
  • MYSQL中的注释
• 2、SQL之SELECT使用篇、包含运算符
  • SELECT的查询结构（2种）
    • 对查询结构中参数的解释
    • SELECT查询语句的执行顺序
  • MYSQL中的运算符
    • 算数运算符（7个）
    • 比较运算符（19个）
    • 逻辑运算符（7个）
    • 位运算符（6个）
    • 使用运算符时的小问题记录
  • MYSQL中的数据类型
    • 所有的数据类型
    • 常见的数据类型可用属性
    • 数据类型中的一些细节
      • UNSIGNED对于整数型、浮点型的作用
      • MYSQL5.7整数类型的宽度显示作用
      • 浮点数类型(m,n)的作用
      • DATETIME 和 TIMESTAMP的区别
      • CHAR、VARCHAR、TEXT的使用建议
      • 选择数据类型的建议
  • 单行函数
    • 数值型函数
    • 字符串函数（SQL语言中字符串的索引从1开始）
  • 常用的多行函数
    • 时间日期函数（只列举常用）
      • 获取日期、时间
      • 日期与时间戳的转换
      • 计算日期和时间的函数
      • 日期的格式化与解析
  • 多表查询JOIN必须要会的一张图
  • 子查询的分类
    • 按照查询的记录数量
      • 单行比较操作符
      • 多行比较操作符
    • 按内查询是否被执行多次
  • SELECT 查询中遇到的问题记录
    • WHERE 与 HAVING的区别
    • EXISTS 与 NOT EXISTS的作用
    • 使用了GROUP BY 分组后，SELECT 的列表问题、WITH ROLLUP问题
• 3、SQL 之DDL、DML、DCL使用篇
  • 数据库的增删改查
  • 对数据表的增删改查
    • 创建表的3种方式（包括创建临时表）
    • 查看建表语句以及表字段结构
    • 修改表
    • 对于数据的增删改
      • 增加数据的5种方式
      • 修改数据
      • 删除数据的2种方式
        • 删除表中重复数据
  • MYSQL表中对于数据及类型的约束
    • 约束需要考虑的4个方面
    • 约束的分类
      • 根据约束数据列的限制
      • 根据约束的作用范围
      • 常见的6个约束如下
      • 查看某个表已有的表级约束
      • 添加、修改、删除列级约束
      • 添加、删除表级约束(check在MYSQL5.7不支持)
• 4、其他数据库对象篇
  • 视图
    • 视图的创建与使用
    • 视图的原理
  • 存储过程与存储函数
    • 存储过程
      • 存储过程的创建与调用（IN、OUT、INOUT）
        • IN 、OUT的同时使用
        • INOUT的使用
        • 删除存储过程
        • 查看存储过程的信息（3种方式）
    • 存储函数
      • 存储函数的创建与调用
        • 不传入参数
        • 传入单个参数
        • 传入多个参数
      • 删除存储函数
      • 查看存储函数的信息（3种）
    • 存储过程与存储函数的参数作用
    • 报错：you might want to use the less safe log_bin_trust_function_creators variable
    • 错误处理
      • 处理错误通常定义在存储过程和存储函数当中，使用如下：
        • 错误的处理方式有3种
        • 处理错误的6种定义方式
        • 抛出一个自定义的错误
    • 存储过程、存储函数的区别
  • 系统变量与用户变量
    • 系统变量
      • 系统变量的分类
      • 系统变量的查看、修改（全局、会话）
      • 用户变量的创建、修改、使用（会话、局部）
        • 会话用户变量
        • 局部用户变量
  • 流程控制
    • 条件判断语句 IF、CASE
    • 循环语句LOOP、WHILE、REPEAT
      • LOOP 循环（需要配合LEAVE）
      • WHILE循环
      • REPEAT循环（UNTIL后面不能加分号）
    • 跳转语句 LEAVE、ITERATE
  • 游标（相当于JAVA中的迭代器）
  • 触发器
    • 基本语法
    • 使用示例
    • 查询触发器、删除触发器
      • 触发器对于外键约束时的处理情况
• 常见问题
  • 事物回滚的概念（MYSQL8.0保证了对DDL事物的原子性）
  • TRUNCATE 与 DELETE 不添加 WHERE条件的区别
  • 关于约束
    • 为什么开发建表时，加 not null default '' 或 default 0
    • 为什么不想要null值
  • AUTU_INCREMENT约束的字段是从1开始的吗？
  • 是不是每个表都可以选择存储引擎（用了外键约束后不可以）

1、数据库概述 与 MYSQL5.7、MYSQL8.0 安装篇（基于Windows）

MYSQL是什么，为什么要使用MYSQL

• MYSQL是一个关系数据库管理系统（RDBMS），我们可以把MYSQL理解为一个管理程序，它管理着我们的一个个数据库。打比方：MYSQL就好比WORD程序，数据库就好比使用WORD程序打开的一个个.docx文档。

• MYSQL主要是性能卓越、开放源代码、用户群体广泛、主要还有免费版

• MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持 4GB ，64位系统支持最大的表文件为 8TB 。

MYSQL的四个版本

• 社区版(GPL)，免费

  • General Public License,GNU通用公共许可协议，软件可以自由使用，修改源代码都是允许的。

• 集群社区版（免费）

• 商业版（收费）

• 集群商业版（收费）

MYSQL环境搭建

MYSQL的安装与卸载

Windows10 下安装MYSQL 8.26版本

• 下载地址

• 双击下载好的安装包：mysql-installer-community-8.0.26.0.msi

• 选择自定义安装，Custom
  

• 将产品移动到需要安装的右侧
  

• 点一下移动到右侧的产品，点Advanced Options高级选项
  

• 设置MYSQL安装位置以及数据库存放的位置
  

• 一路next，最后点击Execute执行

• 继续点击next，进行MYSQL的配置
  

• 继续点击next，使用新的认证授权方式，再点确定
  

• 设置root的密码（我这里是abc123）后点击next

• 设置服务名称、开机是否自启动等，设置好后点击next
  

• 最后点击Execute执行，然后一路确认即可。

配置环境变量

• 找到并复制MYSQL8安装的路径中的bin路径，我这里是这个：D:\a_dev_software\MYSQL\MySQL Server 8.0\bin

• 将其添加到系统环境变量中的path当中
  

Windows10 下安装MYSQL 5.7版本（属于安装2个MYSQL版本）

• 下载地址

• 双击下载好的安装包：mysql-installer-community-5.7.34.0.msi

• 页面弹出后，直接点击右下角的cancel取消，确认

• 在页面中选择add
  

• 将产品移动到需要安装的右侧
  

• 点一下移动到右侧的产品，点Advanced Options高级选项
  

• 设置MYSQL安装位置以及数据库存放的位置
  

• 一路next，最后点击Execute执行

• 继续点击next，进行MYSQL的配置（端口记得换，不能2个MYSQL都用一个端口）
  

• 点击next，设置root的密码（我这里是abc123）后点击next

• 设置服务名称、开机是否自启动等，设置好后点击next
  

• 最后点击Execute执行，然后一路确认即可。安装完成最后可以看到如下页面
  

MYSQL的卸载步骤

1. 在服务面板停止MYSQL服务

2. 在控制面板找到MYSQL，将其卸载

3. 找到数据库文件，可以自由选择是否删除

4. 删除对应的环境变量

5. 如果是MYSQL5.7，则需要删除如下注册表，win + r后输入regedit回车即可进入注册表管理界面

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MySQL服务 目录删除
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL服务 目录删除
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\MySQL服务 目录删除
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL服务目录
删除
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySQL服务删除

MYSQL的环境问题

登录到MYSQL

mysql -uroot -pabc123 -P3306 -hlocalhost

-u用户名
-p密码
-P端口
-h目标主机，localhost代表本机

解决MYSQL5.7中的中文乱码问题 | 8.0的数据库不用修改

• 原因：MYSQL5.7中的默认编码字符集为latin1，并不支持我们的中文，因此我们需要将其修改为UTF-8

• 使用命令查看系统编码（要登录到mysql才行）

# 查看系统的默认字符集
show variables like 'character_%';

# 查看系统的比较规则字符集（理解为数据的比较字符集）
show variables like 'collation_%';

• 找到MYSQL 5.7数据目录中的my.ini文件

# 63行左右，在[mysql]下添加默认字符集
[mysql]
default-character-set=utf8 #默认字符集

# 在76行左右，在[mysqld]下添加如下内容
character-set-server=utf8 # 设置服务器的字符集
collation-server=utf8_general_ci # 设置比较规则

• 重启MYSQL5.7的服务即可

• 小提示：已经创建好的数据库、数据表的字符集不会被更改，需要自行修改

alter table 表名 charset utf8; #修改表字符编码为UTF8

alter table 表名 modify 字段名 字段类型 charset utf8; #修改字段字符编码为UTF8

alter database 数据库名 charset utf8; #修改数据库的字符编码为utf8

解决使用Navicat图形化界面工具无法连接到MYSQL 8的问题

登录MYSQL8，执行如下语句

#使用mysql数据库
USE mysql;

#修改'root'@'localhost'用户的密码规则和密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abc123';

#刷新权限
FLUSH PRIVILEGES;

MYSQL忘记密码的还原办法

1: 通过任务管理器或者服务管理，关掉mysqld(服务进程) 

2: 通过命令行+特殊参数开启（也可以直接在配置文件[mysqld]后加上skip-grant-tables）

mysqld --defaults-file="指定mysql的配置文件my.ini" --skip-grant-tables

3: 此时，mysqld服务进程已经打开。并且不需要权限检查 

4: mysql -uroot 无密码登陆服务器。另启动一
个客户端进行

5: 修改权限表 （1） use mysql; （2）update user set authentication_string=password('新密
码') where user='root' and Host='localhost'; （3）flush privileges;

6: 通过任务管理器，关掉mysqld服务进
程。

7: 再次通过服务管理，打开mysql服务。 8: 即可用修改后的新密码登陆。

SQL的基本介绍

SQL是干什么的

• SQL（Structured Query Language，结构化查询语言），用于跟数据打交道。在不同的数据库管理系统中，SQL可能会出现一些细微的差距。

SQL的分类

DDL 数据定义语言

CARETE、DROP、ALTER等

DML 数据操纵语言

INSERT、DELETE、UPDATE、SELECT

DCL 数据控制语言

GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等

写SQL的一些提议

1. SQL可以写单行或多行，注意可读性

2. 字符串类型及日期类型，使用单引号''表示

3. 在书写别名时，使用as并且别名使用双引号包裹""

4. 数据库名、表名、表别名、字段名、字段别名都使用小写

5. SQL关键字、函数名、绑定变量都大写

MYSQL中的注释

# 单行注释

-- 单行注释

/*
多行注释
*/

2、SQL之SELECT使用篇、包含运算符

SELECT的查询结构（2种）

• 第一种

SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

• 第二种

SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

对查询结构中参数的解释

• （1）from：从哪些表中筛选

• （2）on：关联多表查询时，去除笛卡尔积

• （3）where：从表中筛选的条件

• （4）group by：分组依据

• （5）having：在统计结果中再次筛选

• （6）order by：排序

• （7）limit：分页

SELECT查询语句的执行顺序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
首先假设我们进行的查询是一个多表查询：

1. 首先先通过** CROSS JOIN 求笛卡尔积**，相当于得到虚拟表 vt（virtual table）1-1；

2. 通过** ON 进行筛选**，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；

3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

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

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

5. 当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到** SELECT 和 DISTINCT**阶段 。首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1 和 vt5-2 。

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

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

MYSQL中的运算符

算数运算符（7个）

• +：加法
• -：减法
• *：乘法
• / 或者 DIV：除法
• % 或者 MOD：取模，取余时仅看被模数的正负号

比较运算符（19个）

• =：等于

• <=>：安全等于，为null而生，可以比较null

• <> 或者 !=： 不等于

• <：小于

• <=：小于等于

• >：大于

• >=：大于等于

• IS NULL：判断是否为null

• IS NOT NULL：判断是否不为null

• ISNULL：判断是否为null的函数

• LEAST：最小值运算符

• GREATEST：最大值运算符

• BETWEEN AND：判断一个值是否在两个值之间

• IN：属于运算符，判断一个值是否为列表中任意一个值

• NOT IN：不属于运算符，判断一个值是否不为列表中任意一个值

• LIKE：模糊匹配，%代表多个任意字符，_一个任意字符

• REGEXP：正则表达式运算符

• RLIKE：正则表达式运算符

逻辑运算符（7个）

• AND 或者 &&：逻辑与

• OR 或者 ||： 逻辑或

• NOT 或者 !：逻辑非

• XOR：异或

位运算符（6个）

• &：与

• |： 或

• ^：异或

• <<：左移

• >>：右移

• >>>：无符号右移

使用运算符时的小问题记录

• NULL值与任何值进行计算时，结果都为NULL，除非使用<=>、IS NULL、IS NOT NULL、IFNULL()等判断NULL的运算符或函数

• 字符串类型的数值在计算时，会隐式转换成与其进行计算的数据类型，若是转换失败则将会被转换为0，字符串与字符串进行比较时例外。

• 当一个数除0，那么结果为NULL

MYSQL中的数据类型

所有的数据类型

常见的数据类型可用属性

数据类型中的一些细节

UNSIGNED对于整数型、浮点型的作用

• 对于整数型： 将无法存储负数，且将数据存储范围扩大（扩大的大小就是负数的范围）。

• 对于浮点型： 仅仅是无法存储负数。存储空间一样占用，存储范围没有变化。

MYSQL5.7整数类型的宽度显示作用

• 设置了宽度仍然可以存储超越宽度的数值，所以需要配合上ZEROFILL使用才有意义。

• ZEROFILL ： 整数位数不足宽度时，会往左侧补0。（添加此参数时，会同时默认添加UNSIGNED）

浮点数类型(m,n)的作用

• 用于限制浮点数的宽度m和小数位数n

• 宽度m包括小数位，例如：99.51的宽度m为4

DATETIME 和 TIMESTAMP的区别

• TIMESTAMP存储空间比较小，表示的日期时间范围也比较小。

• TIMESTAMP 底层存储的是毫秒值，计算速度更快。

• TIMESTAMP 的日期时间会根据时区自动变换。

CHAR、VARCHAR、TEXT的使用建议

• 当字符串长度固定或基本不变时，使用CHAR

• 字符数不超过5000时，使用VARCHAR

• 字符数大于5000时，使用TEXT并新建一个表进行存储，避免影响索引查询的效率

• InnoDB存储引擎内部行的存储格式并不区分固定长度与可变长度列，影响性能的因素是数据行使用的存储总量。

选择数据类型的建议

• 整数类型：一般采用INT

• 小数类型：使用DECIMAL(M,D)

• 日期时间：DATETIME

• 字符串类型：略（上面有写）

原因：这样做的好处是，确保你的系统不会因为数据类型定义出错。数据出错的代价远远高于占用的资源代价。

单行函数

数值型函数

字符串函数（SQL语言中字符串的索引从1开始）

常用的多行函数

时间日期函数（只列举常用）

获取日期、时间

# 1.获取当前的日期、时间、日期时间
SELECT CURDATE(),CURTIME(),NOW()
FROM DUAL

日期与时间戳的转换

# 2. 日期与时间戳的转换
# 2.1 获取当前时间戳
SELECT UNIX_TIMESTAMP() FROM DUAL;
# 2.2 获取指定时间的时间戳
SELECT UNIX_TIMESTAMP('2022-08-13 16:33:06') FROM DUAL;
# 2.3 将时间戳转换为日期
SELECT FROM_UNIXTIME(1660379586) FROM DUAL;

计算日期和时间的函数

# 3. 计算日期和时间的函数  DATEDIFF、TO_DAYS、DATE_ADD、DATE_SUB
# 3.1 计算2个日期相差的天数，左边减右边，结果：3
SELECT DATEDIFF('2022-08-13', '2022-08-10') FROM DUAL;
# 3.2 计算2个时间相差的时间，左减右，结果：06:05:28
SELECT TIMEDIFF('16:35:48', '10:30:20') FROM DUAL;
# 3.3 返回一个日期距离0000年1月1日的天数(注意是天数)
SELECT TO_DAYS('2000,01,01') FROM DUAL;
# 3.4 为一个日期时间 增加日期时间，结果: 2022-08-15 16:33:06
SELECT DATE_ADD('2022-08-13 16:33:06',INTERVAL 2 DAY) FROM DUAL;
# 3.5 为一个日期时间 减少日期时间，结果: 2022-08-11 16:33:06
SELECT DATE_SUB('2022-08-13 16:33:06',INTERVAL 2 DAY) FROM DUAL;

日期的格式化与解析

# 4. 日期的格式化与解析
# 4.1 将一个日期时间进行格式化, 2022/08/13 16:33:06
SELECT DATE_FORMAT('2022-08-13 16:33:06','%Y/%m/%d %H-%i-%S');
# 4.2 获得一个日期时间的格式，注意，日期和时间的也可以获取
SELECT GET_FORMAT(TIME, 'ISO') FROM DUAL;
# 4.3 将一个字符串转换为日期格式
SELECT STR_TO_DATE('16:33:06',GET_FORMAT(TIME,'ISO')) FROM DUAL;

多表查询JOIN必须要会的一张图

• （1）左上图：相当于左外连接

• （2）右上图：相当于右外连接

• （3）中图：相当于内连接

• （4）左中图：相当于左外连接不要中间的部分。

• （5）右中图：相当于右外连接不要中间部分

• （6）左下图：可以用左上图 UNION ALL 右中图 。 也可以用右上图 UNION ALL 左中图

• （7）右下图：左中图 UNION ALL 右中图即可。

子查询的分类

按照查询的记录数量

1. 单行子查询（子查询返回的只有一条记录）

2. 多行子查询（子查询返回的有多条记录）

单行比较操作符

多行比较操作符

按内查询是否被执行多次

1. 不相关子查询（每次都是固定的返回结果）

2. 相关子查询（每一次的查询都需要依靠外查询来提供查询条件）

SELECT 查询中遇到的问题记录

WHERE 与 HAVING的区别

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

EXISTS 与 NOT EXISTS的作用

  EXISTS 通常用于相关子查询，判断子查询中的数据是否符合条件，符合时返回1（true），不符合时返回0（false）。

  NOT EXISTS与其恰恰相反。

  使用IN 和 NOT IN的相关子查询通常可以改用 EXISTS 和 NOT EXISTS实现。

使用了GROUP BY 分组后，SELECT 的列表问题、WITH ROLLUP问题

• 使用了GROUP BY 分组后，SELECT 列表只能放聚合函数或进行了分组的字段。

• 当使用了ORDER BY 后，无法使用WITH ROLLUP，

• WITH ROLLUP：在GROUP BY分组字段的基础上再进行统计数据，对数据进行求和。

3、SQL 之DDL、DML、DCL使用篇

数据库的增删改查

1. 创建数据库
CREATE DATABASE IF NOT EXISTS my_db CHARACTER SET = 'UTF8';

2. 查询所有的数据库
SHOW DATABASES;

3. 查看正在使用的数据库
SELECT DATABASE();

4. 查看指定数据库下所有的表
SHOW TABLES FROM 数据表名;

5. 查看数据库的创建信息
SHOW CREATE DATABASE 数据库名

6. 使用/切换数据库
USE 数据库名;

7. 修改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET = 'GBK';

8. 删库跑路
DROP DATABASE IF EXISTS 数据库名。

对数据表的增删改查

创建表的3种方式（包括创建临时表）

1. 白手起家创建
CREATE TABLE IF NOT EXISTS(
	字段名 字段类型 [约束条件] [默认值],
	字段名 字段类型 [约束条件] [默认值],
	字段名 字段类型 [约束条件] [默认值]
);

2. 通过查询的表数据创建（注意查询语句如果有别名会使用别名作为新表的字段名）
CREATE TABLE 需要创建的表名
AS
SELECT * FROM 表名;

3. 创建临时表
# 创建一个临时表
CREATE TEMPORARY TABLE 表名
SELECT * FROM 表名;

# 删除一个临时表
DROP TABLE 临时表名;

查看建表语句以及表字段结构

# 查看建表语句
SHOW CREATE TABLE 表名;

# 查看表字段结构
DESC 表名字;

修改表

1. 追加列
ALTER TABLE 表名
ADD COLUMN 字段名 字段类型 [约束] [默认值] [AFTER|LAST|FIRST];

2. 修改列
ALTER TABLE 表名
MODIFY 修改的字段名 字段类型 [约束] [默认值] [AFTER|LAST|FIRST];

3. 重命名列
ALTER TABLE 表名
CHANGE 修改的字段名 新字段名 字段类型 [约束] [默认值] [AFTER|LAST|FIRST];

4. 删除列
ALTER TABLE 表名
DROP COLUMN 字段名;

5. 修改表名
RENAME TABLE 原表名 TO 修改后的表名;

6. 删除表
DROP TABLE IF EXISTS 表名;

7. 清空表（会把表中数据清空并释放存储空间）
TRUNCATE TABLE 表名;

对于数据的增删改

增加数据的5种方式

# 方式1
INSERT INTO 表名
values(各字段的值);

# 方式2（推荐）
INSERT INTO 表名(指定字段)
VALUES (根据指定的字段填写值);

# 方式3、添加多行数据
INSERT INTO 表名(指定字段)
VALUES
(根据指定的字段填写值),
(根据指定的字段填写值),
(根据指定的字段填写值);

# 方式4、利用SELECT实现多行数据的添加，（注意SELECT查询出来的数据类型与表名可以匹配）
INSERT INTO 表名(指定字段)
SELECT 填写字段
FROM 表名;

例如：
	INSERT INTO t_test1
	SELECT last_name, salary
	FROM employees;

# 方式5、依然是利用SELECT实现数据的添加
INSERT INTO 表名
SELECT 字段列表，逗号间隔 UNIOIN ALL
SELECT 字段列表，逗号间隔 UNIOIN ALL
SELECT 字段列表，逗号间隔;

例如：
	INSERT INTO t_teset1
	SELECT '张三',2000 union all
	SELECT '李四',2500 union all
	SELECT '王五',3000 ;

修改数据

UPDATE 表名  SET 需要修改的字段=需要修改后的值  WHERE ..

删除数据的2种方式

# 方式1
DELETE FROM 表名 WHERE ...

# 方式2 配合SELECT，例如多个表同时删除某条记录
语法示例如下：
DELETE e, d
FROM employee e
JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id = 10;

删除表中重复数据

DELETE FROM class_info 
WHERE id IN (
	SELECT id FROM (
		SELECT id 
		FROM class_info
		WHERE id NOT IN (
			SELECT MIN(id)
			FROM class_info
			GROUP BY class_name, class_leader
		)
	) c
);

MYSQL表中对于数据及类型的约束

约束需要考虑的4个方面

约束就是为了保证数据的完整性，完整性指的是精确性和可靠类。

• 实体完整性。同一个表中，不能出现2条无法区分的记录

• 域完整性。指的是表中的字段能够存储的数据规范。提现在check 检查约束。

• 引用完整性。例如：员工所在部门，那么必须存在该部门。提现在foreign key 外键。

• 用户自定义完整性。例如：想要让用户名唯一，或者年龄不能为空等.

约束的分类

根据约束数据列的限制

• 单列约束，每个约束只能约束一列。

• 多列约束，每个约束可以约束多列。

根据约束的作用范围

• 列级约束：约束作用于列上

• 表级约束：约束作用于表上

常见的6个约束如下

• NOT NULL 该列不能为空，列级约束

• UNIQUE 唯一性约束，表级约束

• PRIMARY KEY 主键约束（非空、唯一），无法更改约束名，表级约束

• FOREIGN KEY 外键约束，表级约束。必须引用/参考主表的主键或唯一约束的列

  • 设置外键时可以指定 ON UPDATE/DELETE CASCADE/SET NULL ，分别代表更新或删除时，另一张关联表CASCADE共同删除或修改，SET NULL 设置为空

• CHECK 检查约束，表级约束

• DEFAULT 默认值，列级约束

查看某个表已有的表级约束

SELECT *
FROM information_schema.table_constraints
WHERE TABLE_NAME = 'employees';

添加、修改、删除列级约束

• 添加列级约束

1. 通过创建表的方式
CREATE TABLE t_test1 (
	name VARCHAR(12) NOT NULL,
	age INT
)

2. 通过ALTER MODIFY的方式
ALTER TABLE t_test1
MODIFY age INT NOT NULL;

• 修改列级约束

ALTER TABLE t_test1
MODIFY age INT DEFAULT 10;

• 删除列级约束（其实就是ALTER MODIFY 该字段不写约束即可）

ALTER TABLE t_test1
MODIFY age INT;

添加、删除表级约束(check在MYSQL5.7不支持)

• 添加表级约束

# 1. 通过创建表时指定
# 提示：constriant就是为了给表约束取名。对主键无效。不取名则默认为字段名(check的默认格式为 表名_chk_1，表名_chk_2，外键的为：表名_ibfk_1)。

CREATE TABLE test_2(
	id INT,
	name VARCHAR(12),
	t_test1_id INT,
	age INT,
	birthday DATE,
	CONSTRAINT fk_id FOREIGN KEY (t_test1_id) REFERENCES t_test1(id),
	PRIMARY KEY(id),
	CHECK (age > 10),
	UNIQUE KEY(name)
)

2. 通过ALTER ADD 添加

ALTER TABLE test_2
ADD CONSTRAINT chk_age CHECK(age < 20);

• 删除表级约束（根据约束名）

# 1、 删除主键约束 和 唯一约束
ALTER TABLE test_2
DROP INDEX `PRIMARY`;

ALTER TABLE test_2
DROP INDEX name;

# 2、删除检查约束
ALTER TABLE test_2
DROP CHECK test_2_chk_1;

# 3、删除外键约束
ALTER TABLE test_2
DROP FOREIGN KEY fk_id;

4、其他数据库对象篇

视图

视图的创建与使用

# 创建视图
CREATE VIEW vu_sal
AS 
SELECT * FROM employees;

# 使用视图
SELECT * FROM vu_sal;

视图的原理

• 视图其实就是一张虚拟表，视图中不存储数据

• 可以把视图理解为一个存储起来的SELECT查询

• 对视图的修改就相当于对于基表的修改

• 对视图进行一条数据数据修改时，该条数据必须与基表中的数据一对一对应

存储过程与存储函数

存储过程

存储过程的创建与调用（IN、OUT、INOUT）

IN 、OUT的同时使用

# 创建存储过程format_date(),实现传入一个日期，格式化成xx年xx月xx日并返回
delimiter $

CREATE PROCEDURE format_date(IN my_date DATE, OUT res VARCHAR(32))
BEGIN
		SELECT	DATE_FORMAT(my_date, GET_FORMAT(DATE,'ISO')) INTO res
		FROM DUAL;
END $

delimiter ;

set @my_date = '2020/10/20';
CALL format_date(@my_date, @res);
SELECT(@res) FROM DUAL;

INOUT的使用

# 传入a和b两个值，最终a和b都翻倍并返回、创建带inout模式参数的存储过程
DELIMITER $
CREATE PROCEDURE calculate_num(INOUT a INT, INOUT b INT)
BEGIN
		SELECT a * 2 INTO a;
		SELECT b * 2 INTO b;

END $
DELIMITER ;

# 调用
set @a = 2;
set @b = 6;
CALL calculate_num(@a, @b);
SELECT @a FROM DUAL;
SELECT @b FROM DUAL;

删除存储过程

DROP PROCEDURE 存储过程名;

查看存储过程的信息（3种方式）

SHOW CREATE PROCEDURE 存储过程名;

SHOW PROCEDURE STATUS LIKE '存储过程名';

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

存储函数

存储函数的创建与调用

不传入参数

#无参有返回
#1. 创建函数get_count(),返回公司的员工个数
DELIMITER //
CREATE FUNCTION get_count() RETURNS INT
DETERMINISTIC
CONTAINS SQL

BEGIN
	RETURN (SELECT COUNT(1) FROM employees);
END //
DELIMITER ;

# 调用存储函数
SELECT get_count() FROM DUAL;

传入单个参数

#2. 创建函数ename_salary(),根据员工姓名，返回它的工资
DELIMITER $
CREATE FUNCTION ename_salary(input_name VARCHAR(25))  RETURNS DOUBLE(8,2)
	DETERMINISTIC
	CONTAINS SQL
BEGIN
	RETURN (SELECT salary FROM employees WHERE last_name = input_name);
END $
DELIMITER ;
drop FUNCTION ename_salary;

# 调用
set @input_name = 'Austin';
SELECT ename_salary(@input_name);



#3. 创建函数dept_sal() ,根据部门名，返回该部门的平均工资
DELIMITER $
CREATE FUNCTION dept_sal(dept_id INT) RETURNS INT 
	DETERMINISTIC
	CONTAINS SQL
BEGIN
		RETURN (SELECT AVG(salary) FROM employees WHERE department_id =  dept_id);
END $
DELIMITER ;

# 调用
SELECT dept_sal(60);

传入多个参数

#4. 创建函数add_float()，实现传入两个float，返回二者之和
DELIMITER $
CREATE FUNCTION add_float(f1 FLOAT, f2 FLOAT) RETURNS FLOAT 
	DETERMINISTIC
	CONTAINS SQL
BEGIN
		RETURN (SELECT f1 + f2 FROM DUAL);
END $
DELIMITER ;

# 调用
SELECT add_float(60, 60);

删除存储函数

DROP FUNCTION 存储函数名;

查看存储函数的信息（3种）

SHOW CREATE FUNCTION 函数过程名;

SHOW FUNCTION STATUS LIKE '函数过程名';

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

存储过程与存储函数的参数作用

• CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；

• NO SQL表示当前存储过程的子程序中不包含任何SQL语句；

• READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；

• MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。

• 默认情况下，系统会指定为CONTAINS SQL。

报错：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执行的时候，如果出现报错，往往不仅有错误码CODE，还有个错误代号SQLSTATE，例如：其中的1136就是错误码，21S01就是错误代号SQLSTATE
  ERROR 1136 (21S01): Column count doesn't match value count at row 1

处理错误通常定义在存储过程和存储函数当中，使用如下：

错误的处理方式有3种

• EXIT 遇到错误时就停止

• CONTINUE 继续执行

• UNDO 遇到错误时撤回之前的操作（MYSQL不支持）

处理错误的6种定义方式

	# 定义错误名称
	DECLARE column_not_match CONDITION FOR 1130; # 通过CODE 定义
	DECLARE column_not_match_2 CONDITION FOR SQLSTATE '21S01'; # 通过SQLSTATE定义

	# 1. 使用错误码
	DECLARE EXIT HANDLER FOR 1130 SET @info = '输入的字段与列字段不匹配,CODE 1130';

	# 2. 使用字符提示SQLSTATE
	DECLARE CONTINUE HANDLER FOR SQLSTATE '21S01' SET @info = '输入的字段与列字段不匹配, SQLSTATE 21S01';

	# 3. 使用定义好的错误名称
	DECLARE EXIT HANDLER FOR column_not_match SET @info = '输入的字段与列字段不匹配,column_not_match';

	# 4. 匹配所有以01开头的SQLSTATE错误代码；
	DECLARE EXIT HANDLER FOR SQLWARNING SET @info = '输入的字段与列字段不匹配,SQLWARNING';

	# 5. 匹配所有以02开头的SQLSTATE错误代码；
	DECLARE EXIT HANDLER FOR NOT FOUND SET @info = '输入的字段与列字段不匹配,NOT FOUND';

	# 6. 匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；
	DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = '输入的字段与列字段不匹配,SQLEXCEPTION';

抛出一个自定义的错误

示例：
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '这是一个自定义的错误';

存储过程、存储函数的区别

注意：存储函数可以放在查询语句中使用，存储过程不行

系统变量与用户变量

系统变量

系统变量的分类

• 全局系统变量（被所有会话所共享）

• 会话系统变量（一个MYSQL的连接就相当于一个会话）

• 注意：有一些系统变量，既是全局系统变量、也是会话系统变量

系统变量的查看、修改（全局、会话）

# 全局系统变量的查看
SHOW GLOBAL VARIABLES;
SELECT @@global.character_set_database; # 查看指定全局系统变量

# 会话系统变量的查看
SHOW SESSION VARIABLES;
SHOW VARIABLES; # 默认查看会话系统变量
SELECT @@session.character_set_database; # 查看指定会话系统变量

# 修改全局系统变量
SET @@global.MAX_CONNECTIONS = 150;
SET global max_connections = 1000;

# 修改会话系统变量
SET @@session.character_set_database = 'gbk';
SET session character_set_database = 'utf8mb3';

• 注意：要想让系统变量永久生效，只能通过更改配置文件的方式。否则重启MYSQL服务都会充值系统变量。MYSQL8修改全局系统变量时添加关键字PERSIST可以让全局系统变量持久化。例如：

SET PERSIST max_connections = 1000;

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

用户变量的创建、修改、使用（会话、局部）

会话用户变量

# 会话用户变量的定义
SET @A = 1;
SET @B = 2;
SET @SUM = @A + @B;

# 会话系统变量的使用
SELECT @SUM FROM DUAL;

局部用户变量

• 提示： 只能定义在存储过程或存储函数当中，准确来说是必须定义在 BEGIN END语句块当中，并且变量的声明必须放在BEGIN的首行。

DELIMITER $
CREATE PROCEDURE test_var()
BEGIN
		# 1. 声明变量
		DECLARE var1 INT DEFAULT 10;
		DECLARE var2 VARCHAR(12);
		DECLARE var3 DATE;
		
		# 2. 为变量赋值
		# 2.1 第一种赋值方式，SET
		SET var2 = '张三';
		# 2.2 第二种赋值方式，SELECT .. INTO ..
		SELECT STR_TO_DATE(NOW(),GET_FORMAT(DATETIME,'ISO')) INTO var3 FROM DUAL;

		# 3. 使用变量
		SELECT var1,var2,var3;

END $
DELIMITER ;

流程控制

条件判断语句 IF、CASE

# 注意ELSE后面不需要接THEN
DELIMITER $
CREATE PROCEDURE if_test(IN num INT)
BEGIN

	IF num = 0
		 THEN SELECT 'the num is 0';
	ELSEIF num = 1
		 THEN SELECT 'the num is 1';
	ELSE 
		 SELECT 'num不是0也不是1呗';
	END IF;

END $
DELIMITER ;

# CASE的第一种用法，相当于JAVA中的IF ELSE
DELIMITER $
CREATE PROCEDURE case_test(IN num INT)
BEGIN

	CASE   WHEN num = 0
		THEN SELECT 'the num is 0';
		WHEN num = 1
		THEN SELECT 'the num is 1';
	ELSE 
		SELECT 'num不是0也不是1呗';
	END CASE;

END $
DELIMITER ;

# CASE的第二种用法，相当于JAVA中的SWITCH
DELIMITER $
CREATE PROCEDURE case_test2(IN num INT)
BEGIN

 CASE 	num WHEN 0
		THEN SELECT 'the num is 0';
		WHEN 1
		THEN SELECT 'the num is 1';
	ELSE 
		SELECT 'num不是0也不是1呗';
	END CASE;

END $
DELIMITER ;

循环语句LOOP、WHILE、REPEAT

LOOP 循环（需要配合LEAVE）

DELIMITER $
CREATE PROCEDURE loop_test(OUT result INT)
BEGIN
	# 初始化
	DECLARE num INT DEFAULT 0;

	# 循环条件
	loop_lable:LOOP
		# 迭代条件
		IF num = 10
			THEN LEAVE loop_lable;
		END IF;

	# 循环体、迭代条件
	SET num = num + 1;

	END LOOP loop_lable;

	SET result = num;
END $
DELIMITER ;

# 调用
CALL loop_test(@result);
SELECT @result;

WHILE循环

DELIMITER $
CREATE PROCEDURE while_test(OUT result INT)
BEGIN
		# 初始化
		DECLARE num INT DEFAULT 0;
			# 循环条件
			WHILE num < 10 DO
				# 循环体、迭代条件
				SET num = num + 1;
			END WHILE;

		SET result = num;
END $
DELIMITER ;

REPEAT循环（UNTIL后面不能加分号）

DELIMITER $
CREATE PROCEDURE repeat_test(OUT result INT)
BEGIN
		# 初始化
		DECLARE num INT DEFAULT 0;

			# 循环条件
			REPEAT

				# 循环体、迭代条件
				SET num = num + 1;

				UNTIL num > 10
			END REPEAT;

		SET result = num;
END $
DELIMITER ;

# 调用
CALL repeat_test(@result);
SELECT @result;

跳转语句 LEAVE、ITERATE

• 设置标签不仅仅可以作用在LOOP上，还可以作用到其他的循环结构、甚至是BEGIN上。一般都是配合LEAVE使用

• ITERATE理解一下，与JAVA中的continue作用一样。

游标（相当于JAVA中的迭代器）

注意： 游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致

DELIMITER $
CREATE PROCEDURE cursor_test()
BEGIN
		# 定义局部变量
		DECLARE avg_sal DOUBLE DEFAULT 0.0;
		DECLARE avg_sum_sal DOUBLE DEFAULT 0.0;
		DECLARE count INT DEFAULT 1;

		# 1. 定义游标
		DECLARE avg_sal_cursor CURSOR FOR
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
				ORDER BY avg_sal;
		
		# 2. 打开游标
		OPEN avg_sal_cursor;

		# 3. 使用游标
		WHILE count <= 3 DO
			# 每次都取出游标中的一条数据赋值给 avg_sal
			FETCH avg_sal_cursor INTO avg_sal;
			# 累计求和
			SET avg_sum_sal = avg_sum_sal + avg_sal;
			# 迭代条件
			SET count = count + 1;
		END WHILE;

		# 4. 关闭游标
		CLOSE avg_sal_cursor;

		SELECT avg_sum_sal;
END $
DELIMITER ;

# 调用
CALL cursor_test();

触发器

基本语法

CREATE TRIGGER 触发器名
(BEFORE|AFTER) (INSERT|DELETE|UPDATE) 表名
FOR EACH ROW  # 表示表中的每一行进行操作时都会触发该触发器
BEGIN
	// 这里写方法体
	// 注意： 使用NEW可以获取到当前操作的数据
END

使用示例

DELIMITER //
CREATE TRIGGER after_insert_emp
AFTER INSERT ON test_tri
FOR EACH ROW
BEGIN
	INSERT INTO test_tri_log
	VALUES(NEW.last_name, NEW.salary);
END //

DELIMITER ;

查询触发器、删除触发器

# 方式1
SHOW TRIGGERS;

# 方式2
SHOW CREATE TRIGGER 触发器名;

# 方式3
SELECT * FROM information_schema.TRIGGERS;

# 删除触发器
DROP TRIGGER IF EXISTS 触发器名称;

触发器对于外键约束时的处理情况

• 前提：触发器是对于从表进行定义的

• 如果修改从表，那么触发器会正常执行

• 如果修改主表导致的从表内容修改，触发器不会执行。

常见问题

事物回滚的概念（MYSQL8.0保证了对DDL事物的原子性）

• 原子性： 理解一次访问数据库的请求所执行的SQL，要么全都执行成功，要么全都别执行。

• MYSQL中，DDL数据定义语言的操作都是不可回滚的，执行时需要务必注意

DDL操作回滚日志
写入到data dictionary数据字典表mysql.innodb_ddl_log
（该表是隐藏的表，通过show tables无法看到）

• DML操作如果想要回滚数据，则需要先将自动回滚关闭（建议关闭前先提交一次），而后手动提交事物

# 提交事物
commit;

# 将自动回滚设置为false
set autocommit = false;

# 执行DML操作
---

# 提交事物
commit;

TRUNCATE 与 DELETE 不添加 WHERE条件的区别

在效率上来说，TRUNCATE可以更快，但是TRUNCATE不支持事物的回滚，并且无法触发TRIGGER，建议是尽量不实用TRUNCATE

关于约束

为什么开发建表时，加 not null default '' 或 default 0

因为不想要null值

为什么不想要null值

• null值除了is not null 、is null 等特殊的比较运算符，一般计算结果都为null，会影响我们查询的结果

• null值会影响索引的效果。

AUTU_INCREMENT约束的字段是从1开始的吗？

默认是从1开始的，如果添加第一条字段时手动指定AUTO_INCREMENT字段的值，那么会按照指定字段的值往后排。

是不是每个表都可以选择存储引擎（用了外键约束后不可以）

MYSQL支持多种存储引擎，默认都使用INNODB，当然也可以在创建表的时候指定存储引擎。不过外键关联的表无法指定不同的存储引擎。（想想明明2张表需要关联，还指定不同的存储引擎，这不就影响参照完整性了么）。