MySQL

MySQL——入门

MySQL卸载

1. 打开任务管理器，停止MySQL服务
2. 打开控制面板，卸载MySQL
3. 删除MySQL下载文件夹和MySQL数据存储文件夹中的MySQL数据版本信息
4. 删除环境变量

MySQL下载

1. 官网下载安装包
2. 配置环境变量
3. 打开cmd 输入mysql --version 查看是否安装成功
4. 启动MySQL服务
   方法一：win + r ：输入services.msc查看计算机服务
   方法二：以管理员身份运行cmd，输入net start 服务名称，来启动服务。或者输入net stop 服务名称，来停止服务。

对MySQL进行操作

注释：--空格

1. 登录MySQL：mysql -u 用户名 -p 密码

2. 退出MySQL：方法一：exit。方法二：quit

3. 查看所有的数据库：show databases；（show：展示，databases：数据库）
   

4. 创建自己的数据库：create database 数据库名称；（create：创造）不能创建两个名字相同的数据库

5. 进入到指定的数据库中：use 数据库名称；（use：使用）

6. 创建数据表：create table 数据表名（字段名 字段属性（字段长度），字段名 字段属性（字段长度），字段名 字段属性（字段长度）...）；

7. 查看数据表：show tables；

8. 查看数据表信息：show create table 数据表名；

9. 查看数据库信息：show create database 数据库名；
   或者使用：show create table 数据表名\G；（两种方法都可以查看数据表或数据库信息，只是返回的格式不一样）。

10. 查看当前使用的是哪个数据库：select database();

11. 修改数据库字符集：ALTER DATABASE 数据库名 character set 字符集；（alter：改变，character：字符）

12. 删除数据库：drop database 数据库名；
    安全命令：drop database if exists 数据库名；如果该数据库存在则删除该数据库（exists：存在）

13. 创建表：create table [IF NOT EXISTS] 表名（
    字段1，数据类型[约束条件][默认值]，
    ...
    [表约束条件]
    ）；
    如果加上了 IF NOT EXISTS 则表示如果数据库中不存在要创建的数据表则创建，如果存在则忽略创建。
    建表语句中不可缺少的部分：

    • 表名
    • 列名（字段名）、数据类型、长度（int类型有默认长度11位，可以不用指定）

    可选指定：

    • 约束条件
      • 自增组件：AUTO_INCREMENT（一个表只能有一个自增长列，自增长列一般和主键配合使用）
      • 主键约束：PRIMARY KEY（保证唯一性和非空性）
      • 非空约束：not null（保证字段的值不能为空）
      • 默认约束：default（保证字段保证有值，即使不插入值也会有默认值）
      • 唯一约束：unique（保证唯一性，但可以为空）
    • 默认值

14. 查看表中字段信息：DESC 表名；

15. 修改表：使用ALTER TABLE语句实现

    • 追加一个列：ALTER TABLE 表名 ADD [COLUMN] 新增字段名 新增字段属性 新增属性长度 [FIRS|AFTER 字段名]；
    • 修改一个列：ALTER TABLE 表名 MODIFY [COLUMN] 需要修改的字段名 字段类型 字段长度 [DEFAULT 默认值] [FIRS|AFTER 字段名];
    • 重命名一个列：ALTER TABLE 表名 CHANGE [COLUMN] 字段名 新字段名 新字段类型；
    • 删除一个列：ALTER TABLE 表名 DROP COLUMN 字段名；（column：列）
    • 重命名表：方法一：RENAME TABLE 旧表名 TO 新表名；
      方法二：ALTER TABLE 旧表名 RENAME [TO] 新表名；
    • 删除表：DROP TABLE [IF EXISTS] 数据表名1，数据表2...；
      IF EXISTS 为安全模式，即当表存在时将其删除。可以连续删除多个表，在删除与其它表有关联的表时要先解除关联（外键），否则无法删除。数据表删除后，所有数据都将清除。
    • 清空表：方法一：TRUNCATE TABLE 表名；（不能回滚）
      方法二：DELETE FROM 表名；（可以回滚，开发中建议使用该方法）

16. 数据导入指令：source 需要导入的数据表地址；

---

MySQL图形化界面工具——Navicat

数据库创建四个步骤
创建数据库——确认字段——创建数据库表——插入数据

MySQL标识符命名规则

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

SQL语言功能分类

• DDL（数据定义语言）主要用于创建、删除、修改数据表和数据库。主要语句关键字CREATE、DROP、ALTER等
• DML（数据操作语言）主要用于的数据的增删改查操作。主要语句关键字INSERT、DELETE、SELECT、UPDATE等。（其中SELECT极为重要）
• DCL（数据控制语言）用于定义数据库、表、字段、用户的访问权限和安全级别。主要语句关键字GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。
• DQL（数据查询语言）由于SELECT使用的相当平凡，所以有人会将select单独作为一门语言。还有将COMMIT、ROLLBACK取出来称为TCL（事务控制语言）

SQL语言的规则与规范

基本规则：

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

SQL大小写规范：

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

注释

• 单行注解：方法一：# 注释内容（MySQL独有，在其它数据库中使用可能会报错）
  方法二：-- 注释内容
• 多行注释：/*注释内容*/

SELECT语句

• 基本语句：SELECT * FROM 表名；（不加后面的FROM也是可以在MySQL中运行的，它相当于：SELECT 字段名 FROM DUAL; DUAL是MySQL的虚拟表，它的作用是为了让该语句符合规范。也可以使用SELECT进行计算）。
  * 代表 所有字段，但在开发中通常不会使用 * ，而是使用具体的字段来实现，这样可以节省资源的消耗。
• 起别名：使用 AS 关键字（可以省略，但不推荐省略）：SELECT 字段名 AS 别名 FROM...;
  省略后的写法：SELECT 字段名 别名 FROM...；
• 去重复行查询：SELECT DISTINCT 字段名 FROM...；
• 着重号：SELECT * FROM from；（当字段名、表名等与MySQL关键字一致时，使用着重号避免报错）
• 查询常数：SELECT '常数'AS 别名，字段名 FROM 表名；（会在查询结果前自动补上我们所输入的常数——可以是字符串或数字等）。
• 过滤数据：SELECT * FROM 表名 WHERE 条件语句；（在查询结果中只会显示符合WHERE条件的数据）

MySQL运算符

算术运算符

• +：加法运算
  MySQL的加法运算只对数字进行运算，不会拼接字符串，如果遇到字符串则会将其转化为数字，如果转化失败则按零处理。
• -：减法运算
• *：乘法运算
• /或DIV：除法运算求商：A/B 或 A DIV B
  在MySQL中，一个数除以0，结果会变成null，一个数除以另一个数后，不论是否除尽，结果都是一个浮点数，如果除不尽则会保留小数点后四位。
• %或MOD：模运算求余数：A%B 或 A MOD B

比较运算符

• =：等于运算符（判断等式两边是否相等，若相等返回1，不相等返回0。如果有一边为空，则结果为空）
• <=>：安全等于运算符（与等于运算符一样，但可以对null进行判断，若都为null则返回1，若只有一方为null则返回0）
• <>或！=：不等于运算符
• <：小于运算符
• <=：小于等于运算符
• >：大于运算符
• >=：大于等于运算符
• IS NULL | ISNULL：为空运算符（判断是否为空）
• IS NOTNULL：不为空运算符（判断是否不为空）
• LEAST：最小值运算符（返回最小值）
• GREATEST：最大值运算符（返回最大值）
• BETWEEN AND:两值之间运算符（判断一个值是否在另外两值之间）
• IN：属于运算符
• NOT IN：不属于运算符
• LIKE：模糊匹配运算符（判断一个值是否属于模糊匹配规则）
  通配符：% 匹配0或多个字符（用于字符后），_ 匹配一个字符（用于字符前）
• REGEXP | RLIKE：正则表达式运算符（判断一个值是否属于正则表达式规则）

逻辑运算符

• AND 或 &&：逻辑与运算
• OR 或 ||：逻辑或运算
• NOT 或 !：逻辑非运算符
• XOR：逻辑异或运算符（在多个条件时只满足其中一个而不满足其他）

---

DML（数据操作语言——增删改查）

添加：
方法一：为表中所有字段按默认顺序添加数据：INSERT INTO 表名 VALUES （数据1，数据2...）;
方法二：为表中指定字段添加数据：INSERT INTO 表名（字段1，字段2...） VALUES （数据1，数据2...）;
方法三：添加多条数据：INSERT INTO 表名 VALUES （数据1，数据2...），（数据1，数据2...）...；
方法四：和SELECT联用，可以将SELECT查询的结果添加到对应表中，可以不用写values
语法：INSERT INTO 表名（所需字段） SELECT （对应字段） FROM 查询表名 [WHERE]
修改：
UPDATE 表名 SET 字段1=值1，字段2=值2... [WHERE语句]；如果不适用WHERE语句则会将所有的数据修改update可以当逻辑删除使用
删除：
DELETE FROM 表名 [WHERE语句]；如果不使用WHERE语句则会删除整张表的数据
计算列：不需要添加，数据由给定的其余字段的值计算得出（在MySQL8中CREATE TABLE和ALTER TABLE中都支持增加计算列）
CREATE TABLE 表名(
id INT,
字段1 INT,
字段2 INT,
字段3 INT GENERATED ALWATS AS(字段1+字段2) VIRTUAL
);
排序和分页：
排序：关键字（ORDER BY），两个参数（ASC:升序（默认），DESC：降序），在使用时放置于语句的末尾
单列排序：SELECT * FROM 表名 ORDER BY 字段（根据此字段来进行排序） [ASC或DESC];
多列排序：SELECT * FROM 表名 ORDER BY 字段1，字段2 [ASC或DESC];此排序会先满足字段1的排序，如果字段1数值相同便会对字段2排序。可以分别指定排序规则（升序或降序）。
在对字符进行排序时，是根据第一个字符的编码大小来排序的，所以当排序的字符为字符串的数字时，我们可以在排序时对其进行加0操作，使其变成数字。

---

分页：关键字（LIMIT），LIMIT一定是放在最后的
SELECT * FROM 表名 LIMIT 位置的偏移量（从哪一行开始显示，如果不指定，默认是第一行）从0开始，行数（展示的条数）；
偏移量=(页数-1)*行数

多表查询

表与表之间的关联关系

• 一对多
• 一对一
• 自我引用

笛卡尔积
当多个集合进行组合时，会排列出所有有可能的排序。在SQL92中，笛卡尔积也称为交叉连接，它可以将任意两张毫不相干的表连接起来。但由于会列出所有情况，便会出现重复和错误数据。
连表查询
SELECT 表1.字段1，表2.字段2...FROM 表1，表二 [WHERE语句，消除笛卡尔积]；
多表查询分类

• 等值连接、非等值连接

  • 等值连接：两张表的相关联数据等值
  • 非等值连接：一张表的数据在另一张表数据范围之内（通过范围查询）

• 自连接、非自连接

  • 自连接：在同一张表内存在关系（使用取别名的方式，虚拟出两张表代表不同的意义，它也是一种等值连接）
  • 非自连接：
    使用join on来连表查询
    SELECT 表名1.字段，表名2.字段 FROM 表1 JOIN 表2 ON 条件语句；

• 内连接、外连接

  • 内连接：合并具有的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
    可以使用INNER JOIN ON来进行内连接
    SELECT 表名1别名.字段，表名2别名.字段 FROM 表1 别名 INNER JOIN 表2 别名 ON 条件语句；
  • 外连接：一张表和另一张表的数据相关联。除了可以返回符合条件的数据，也可以返回左或右表中不满足条件的值，这种称为左外连接或右外连接
    • 左外连接：连接条件左边的表为主表，右边的为从表，查询时会返回所有符合条件的左表数据，和部分右表数据
      语法：SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 关联条件；（表1为左表，表2为右表）
    • 右外连接：连接条件右边的表为主表，左边的为从表，查询时会返回所有符合条件的右表数据，和部分左表数据
      语法：SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 关联条件；表1为右表，表2为左表）
    • 满外连接：查询全部的数据

  join on除了无法进行非等值连接外，可以使用其余连接方式。

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

  • UNION：查询两张表全部的数据，对于重复的数据只会取其中一张表的数据
  • UNION ALL：查询两张表的全部数据，不会去除重复的数据（效率比UNION高）

• NATURAL JOIN：自然连接，和内连接一样，但省去了ON的条件语句

  • SELECT 表名1别名.字段，表名2别名.字段 FROM 表1 别名 INNER JOIN 表2 别名 ON 条件语句；
  • SELECT 表名1别名.字段，表名2别名.字段 FROM 表1 别名 NATURAL JOIN 表2 别名

• USING：指定同名字段进行等值连接，但只能配合JOIN字段，等同于where语句
  SELECT 表1.，表2. FROM 表1 JOIN 表2 USING（表1和表2中相同的字段）；
  多表查询十分消耗性能，它相当于嵌套for循环，因此多表查询时禁止关联无关的表。在超过三张表进行查询时，禁止使用JOIN，多表关联查询时，保证被关联的字段需要有索引。——《阿里巴巴开发手册》

基本函数

• 内置函数

  • 单行函数
    特点：操作的数据对象，接受参数返回一个结果，只对一行进行变换，每行返回一个结果，可以嵌套，参数可以是一个列或一个值。
    数值函数
    1.基本函数

    • ABS（x）：返回 x 的绝对值
    • SIGN（x）：返回 x 的符号，正数返回1，负数返回-1，0返回0
    • PI（）：返回圆周率的值
    • CEIL（x）、CEILING（x）：返回大于或等于某个值的最小整数（向下取整）
    • FLOOR（x）：返回小于或等于某个值的最大整数（向上取整）
    • LEAST（e1.e2,...）：返回列表中的最小值
    • GREATEST（e1,e2,...）：返回列表中的最大值
    • MOD（x，y）：返回x除以y后的余数
    • RAND（）：返回0-1之间的随机数
    • RAND（x）：返回0-1之间的随机数，其中 x 用作种子值，相同的 x 会返回相同的随机数
    • ROUND（x）：返回一个对 x 四舍五入后最趋近于x的整数
    • ROUND（x，y）：返回一个对 x 四舍五入后最趋近于 x 的值，保留小数点后 y 位
    • TRUNCATE（x，y）：返回数字 x 截断小数点后 y 位的小数的结果
    • SQRT（x）：返回 x 的平方根，当 x 位负数时返回NULL

    2.角度与弧度互换函数

    • RADIANS（x）：将角度转化为弧度，x为角度
    • DEGREES（x）：将弧度转化为角度，x为弧度

    3.三角函数

    • SIN（x）：返回 x 的正弦值，其中 x 位弧度值
    • ASIN（x）：返回 x 的反正弦值，即获取正弦为 x 的值。如果 x 不在-1到1之间则返回NULL
    • COS（x）：返回 x 的余弦值，其中 x 位弧度值
    • ACOS（x）：返回 x 的反余弦值，即获取余弦为 x 的值。如果 x 不在-1到1之间则返回NULL
    • TAN（x）：返回 x 的正切值，其中 x 位弧度值
    • ATAN（x）：返回 x 的反正切值，即获取正切为 x 的值
    • ATAN2（x，y）：返回两个参数的反正切值
    • COT（x）：返回 x 的余切值，其中 x 位弧度

    4.指数与对数

    • POW（x，y）、POWER（x，y）：返回 x 的 y 次方
    • EXP（x）：返回 e 的 x 次方，其中 e 是个常数：2.718281828459045
    • LN（x）、LOG（x）：返回以 e 为底的 x 的对数，当 x<=0时，返回NULL
    • LOG10（x）：返回以10为底的 x 的对数，当 x<=0时，返回NULL
    • LOG2（x）：返回以2为底的 x 的对数，当 x<=0时，返回NULL

    5.进制转换

    • BIN（x）：返回 x 的二进制编码
    • HEX（x）：返回 x 的十六进制编码
    • OCT（x）：返回 x 的八进制编码
    • CONV（x，f1，f2）：返回f1进制数变成f2进制数（x：输入值，f1，f2：进制，二进制即填入2，八进制即填入8）

    字符串函数

    • ASCLL（s）：返回字符串s中第一个字符的ascll码值
    • CHAR_LENGHT（s）：返回字符串 s 的字符数，于CHARACTER_LENGHT（s）作用相同
    • LENGHT（s）：返回字符串s的字节数，和字符集有关
    • CONCAT（s1,s2...）：连接字符串s1,s2...为一个字符串
    • CONCAT_WS（x，s1,s2,...）：和CONCAT（s1,s2...）一样，但会在每个被连接的字符串前添加字符串 x
    • INSERT（str,idx,len，replacestr）：将字符串str从第idx字符开始len个字符长的子串替换为replacestr
    • REPLACE（str，a，b）：用字符串 b 替换掉字符串str中出现的字符串 a
    • UPPER（s）、UCASE（s）：将字符串 s 中所有的字母转换成大写
    • LOWER（s）、LCASE（s）：将字符串 s 中所有的字母转换成小写
    • LEFT（str，n）：返回字符串str最左边的 n 个字符
    • RIGHT（str，n）：返回字符串str最右边的 n 个字符
    • LPAD（str，len，pad）：用字符串pad对字符串str最左边进行填充，直到str的长度为len个字符
    • RPAD（str，len，pad）：用字符串pad对字符串str最右边进行填充，直到str的长度为len个字符
    • LTRIM（s）：去掉字符串s左边的空格
    • RTRIM（s）：去掉字符串s右边的空格
    • TRIM（s）：去掉字符串s开头和结尾的空格
    • TRIM（s1 FROM s）：去掉字符串s 开始于结尾的s1
    • TRIM（LEADING s1 FROM s）：去掉字符串s 开始处的s1
    • TRIM（TRAILING s1 FROM s）：去掉字符串s 结尾处的s1
    • REPEAT（str，n）：返回字符串str重复n次的结果
    • SPACE（n）：返回n个空格
    • STRCMP（s1，s2）：比较字符串s1，s2的ASCLL码值大小。
    • SUBSTR（str，index，len）、SUBSTRING（str，index，len）、MID（str，index，len）：截取字符串str，从第index开始（若index为整数就是从左往右数，若为负数则是从右往左数，下标从1开始），len为截取的字符长度
    • LOCATE(str1.str2, start)、POSITION(str1 IN str2)、INSTR（str1.str2）：返回所要搜索的字符串首次出现的位置，若不存在则返回0，str1为要搜索的字符串，str2为目标字符串。（start为可选输入，为搜索的起始位置）
    • ELT（index，str1，str2，str3...）：index为索引值，若索引值为1则返回字符串str1，若为2则返回字符串str2...（如果index值大于str的长度或等于0，则返回null）
    • FIELD（value，value1，value2...）：返回value在value1，value2...中第一次出现的位置，作用与ELT（）相反。若值列表中没有值或值为空，则返回0
    • FIND_IN_SET("q", "s,q,l")：返回字符串q在字符串列表中出现的位置。若字符串列表中没有要搜索的字符串，或字符串列表是一个null字符串则返回0，若字符串或字符串列表为空，则返回null
    • REVERSE（s）：返回字符串s反转后的字符串
    • NULLIF（value1，value2）：如果value1等于value2，则返回null，否则返回value1。
    • IF（value1，value2，value3）：如果value1的值为true，则返回value2，否则返回value3
    • IFNULL（value1，value2）：如果value1的值为null则返回value2，否则返回value1
    • ISNULL（value）：如果value值为空则返回1，否则返回0

    获取时间和日期

    • CURDATE（）、CURRENT_DATE（）：返回当前日期（年月日）
    • CURTIME（）、CURRENT_TIME（）：返回当前时间（时分秒）
    • UTC_DATE（）：返回当前世界标准时间日期
    • UTC_DATE（）：返回当前世界标准日期
    • NOW（）、SYSDATE（）、CURRENT_TIMESTAMP（）、LOCALTIME（）、LOCALTIMESTAMP（）：返回系统当前时间和日期

    日期与时间戳的转换

    • UNIX_TIMESTAMP（）：以UNIX时间戳的形式返回当前时间
    • UNIX_TIMESTAMP（date）：将时间date以UNIX时间戳的形式返回
    • FROM_UNIXTIME（timestamp）：将UNIX时间戳的时间转换为普通格式的时间

    流程控制

    • CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 [ELSE resultn] END：相当于java的if...else if...else...
    • CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值2 THEN 值2 [ELSE 值n] END：相当于java的switch...case...

    加密与解密函数

    • PASSWORD（str）：返回字符串str的加密版本，41位长的字符串，加密结果不可逆，常用语用户密码加密
    • MD5（str）：返回字符串str经过MD5加密后的值
    • SHA（str）：从原明文密码str计算并返回加密后的密码字符串，当参数为null时返回null。SHA加密比MD5更安全
      - ENCOOE（value，password_seed）：返回使用password_seed加密后的value值
      - DECOOE（value，password_seed）：返回使用password_seed解密后的value值

  • 多行函数（聚合函数）：作用于一组数据，并对一组数据返回一个值
    语法：SELECT 列，聚合函数 FROM...
    基本聚合函数
    不能在where中使用聚合函数

    • AVG（）：平均数
    • SUM（）：求和
    • MAX（）：最大
    • MIN（）：最小
    • COUNT（）：计算总数，count（1）等于count（），count|（列名）不会统计null值，count（）会统计null值，所以不能用count（列名）替换count（*）

    GROUP BY

    • 可以使用group by将表中的数据分成若干组
    • 语法：SELECT 字段名 FROM 表名 [WHERE条件] [GROUP BY 字段名1，字段名2] [ORDER BY 字段名]；GROUP BY 后面的字段必须要在查询时声明出（声明在聚合函数中也可以），且不可以在声明其他的字段名，但可以使用聚合函数。
    • WITH ROLLUP：可以在GROUP BY语句后加WITH ROLLUP。它可以计算出查询数据的总和（但它无法对字符串进行求和，如果字段为字符串类型，它会返回null值，如果不想要null值，可以使用COALSCE函数来为字段设置默认值，需作用在SELECT后），但使用了WITH ROLLUP后不可以在使用ORDER BY来为数据进行排序。

    HAVING

    • 必须和GROUP BY一起使用，作用等同于where,对GROUP BY分组后的数据进行筛选
    • 语法：SELECT 列名 FROM 表名 [WHERE] [GROUP BY] [HAVING] [ORDER BY ]
      WHERE和HAVING对比
      WHERE
    • 优点：可以先对数据进行筛选，执行效率高
    • 缺点：不可以使用聚合函数
      HAVING
    • 优点：可以使用聚合函数
    • 缺点：在最后对结果集进行集中筛选，执行效率低
• 自定义函数

SELECT的执行过程

方式一：
SELECT...FROM...WHERE...AND...GROUP BY...HABING...ORDER BY...LIMIT
方式二：
SELECT...FROM...JOIN ON...WHERE...AND/OR...GROUP BY...ORDER BY...LIMIT

子查询

定义：一个查询语句嵌套在另一个查询语句中的查询
语句：SELECT...FROM...WHERE 条件判断（SELECT...FROM...WHERE...）
子查询分类：
分类方式一：根据返回的语句可分为单行子查询、多行子查询

• 单行子查询：返回一行数据的子查询
• 多行子查询：返回多行数据的子查询，通常使用关键字IN（还可以使用ANY：任意一个,ALL：所有）

方分类式二：根据子查询的执行次数分为不相关子查询和相关子查询

• 不相关子查询：只执行一次的子查询
• 相关子查询：需要执行多次的子查询
  例：查询年龄大于班级平均年龄的人的名字
  SELECT age，name FROM student s1 WHERE age > (SELECT ROUND(AVG(AGE),2) avg_age FROM student WHERE class_id=s1.class_id)

约束

什么是约束：约束是表级的强制规定，可以在创建表时规定约束，或者在表创建之后通过ALTER TABLE 语句规定约束
为什么要使用约束：保证了数据的完整性（数据的精确性和可靠性），它可以防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息。
为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制，从一下四个方面考虑：

• 实体完整性（entity integrity）：如：同一个表中，不能存在两条完全相同无法区分的记录
• 域完整性（domain integrity）：如：年龄范围0-120，性别范围男、女
• 引用完整性（referential integrity）：如：员工所在部门，在部门表中能找到这个部门
• 用户自定义完整性（user-defined integrity）：如：密码不能为空等

约束的分类：

1. 根据约束数据列的限制

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

2. 根据约束的作用范围

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

3. 根据约束的作用可分为：

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

     • 默认，所有的类型值都可以为空
     • 非空约束只能出现在表对象的列上，只能某个单独列限定非空，不能组合非空
     • 一个表可以有多个列都限定非空
     • 空字符串不等于null，0也不等于null

     建表时添加非空约束语法：CREATE TABLE 表名（
     字段名 字段类型 NOT NULL;
     字段名 字段类型 NOT NULL;
     ...
     ）
     建表后添加非空约束语法：ALTER TABLE 表名 modify 字段名 字段类型 NOT NULL;
     删除非空约束：ALTER TABLE 表名 modify 字段名 字段类型 NULL;
     或者：ALTER TABLE 表名 modify 字段名 字段类型;

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

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

     建表时添加唯一约束语法：
     方式一（单列）：
     CREATE TABLE 表名（
     字段名 字段类型 UNIQUE;
     字段名 字段类型 UNIQUE KEY;
     ...
     ）
     方式二（多列组合）：
     CREATE TABLE 表名（
     字段名 字段类型;
     字段名 字段类型;
     [constraint 起一个约束名] UNIQUE KEY(字段名1，字段名2...)
     ）
     建表后添加唯一约束语法：
     方式一（可以添加多个列）：ALTER TABLE 表名 add UNIQUE KEY（字段名1，字段名2...）;
     方式二（只能对一个列添加）：ALTER TABLE 表名 modify 字段名 字段类型 UNIQUE;
     复合唯一约束：
     语法：CREATE TABLE 表名（
     字段名 字段类型;
     字段名 字段类型;
     UNIQUE KEY（字段列表）
     ）

   • PRIMARY KEY：主键约束，默认不能为空，而且一张表中只能有一个主键约束
     特点：

     • 主键约束相当于唯一约束+非空约束，主键约束列的值不能重复，也不能出现null值
     • 一张表只能有一个主键约束，可以在列级别创建，也可以在表级别创建
     • MySQL的主键约束名是PRIMARY，就算自己命名了也没用
     • 当创建主键约束时，系统默认会在所在列或列组合上添加主键索引（根据主键索引进行查询效率会更高），如果删除了主键约束，主键索引就会自动删除
     • 不要修改主键字段的值，因为主键的数据记录是唯一标识，如果修改了主键的值，就有可能破会数据的完整性

     建表时添加主键约束语法：
     方式一（单列）：
     CREATE TABLE 表名（
     字段名 字段类型 PRIMARY KEY;
     字段名 字段类型；
     ...
     ）
     方式二（多列组合）：
     CREATE TABLE 表名（
     字段名 字段类型;
     字段名 字段类型;
     [constraint 起一个约束名] PRIMARY KEY(字段名)
     ）
     建表后添加主键约束语法：
     方式一：ALTER TABLE 表名 ADD PRIMARY KEY（字段名1，字段名2...）;
     方式二：ALTER TABLE 表名 ADD PRIMARY KEY（字段名）;
     复合主键约束：
     语法：CREATE TABLE 表名（
     字段名 字段类型;
     字段名 字段类型;
     PRIMARY KEY（字段列表）
     ）
     删除主键约束：
     语法：ALTER TABLE 表名 DROP PRIMARY KEY;

   • AUTO_INCREMENT：自增约束
     特点：

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

     建表时添加自增约束语法：
     CREATE TABLE 表名（
     字段名 字段类型 PRIMARY KEY AUTO_INCREMENT;
     字段名 字段类型；
     ...
     ）
     建表后添加主键约束语法：
     ALTER TABLE 表名 MODIFY 字段名 字段类型 AUTO_INCREMENT;
     删除自增约束：
     语法：ALTER TABLE 表名 MODIFY 字段名 字段类型;

   • FOREING KEY：外键约束
     特点：

     • 从表的外键列必须引用主表的主键或唯一约束列。因为被依赖或被参考的值必须是唯一的
     • 在创建外检约束时如果未给外键约束命名，则会随机产生一个外键名
     • 创建表时就指定外键约束的或必须要有主表
     • 删除表时要先删除从表在删除主表
     • 当主表的数据被从表参照时，主表的数据不可以被删除，如皋要删除主表的数据要先删除从表对主表数据的参照数据
     • 在从表中指定外键约束，一个表可以使用多个外键约束
     • 从表的外键约束和主表被参照的列的数据类型必须相同
     • 当创建外键约束时，系统默认会在所在列上建立对应的普通索引，但索引名是外键的约束名（根据外键查询可以提高效率）
     • 删除外键约束后，必须手动删除对应的索引

     建表时添加外键约束语法：
     CREATE TABLE 表名（
     字段名 字段类型 PRIMARY KEY AUTO_INCREMENT;
     字段名 字段类型；
     [CONSTRAINT (自定义约束名)] FOREIGN KEY (从表的字段) REFERENCES 主表名（被参考字段）
     ）
     建表后添加外键约束语法：ALTER TABLE 从表名 ADD [CONSTRAINT (自定义约束名)] FOREIGN KEY (从表的字段) REFERENCES 主表名（被参考字段）[ON UPDATE ...][ON DELETE ...];
     约束等级：

     • Cascade：在父表上update/delete记录时，同步子表上的匹配数据
     • Set null：在父表上update/delete记录时，将字段子表的匹配列设为null。使用这种方式时，子表的外键列不能设置为not null
     • No action：如果子表中1有匹配的记录则不允许对父表中的对应候选键进行update/delete操作
     • Restrict：同No action
     • Set default：父表有变时，子表将外键列设置成一个默认值，但innodb不能识别

     如果没有指定等级，就相当于Restrict方式
     对于外键约束，最好采用ON UPFATE CASCADE ON DELETE RESTRICT
     删除外键语法：
     步骤一（根据约束名删除外键约束）：ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名
     步骤二（根据索引名删除索引）：查看索引名：SHOW INDEX FROM 表名称；
     ALTER TABLE 从表名 DROP INDEC 索引名；

   • CHECK：检查约束（MySQL5.7不支持但可以使用，不过没有任何效果）
     创建表时添加CHECK约束：
     CREATE TABLE 表名（
     字段名 字段类型 PRIMARY KEY AUTO_INCREMENT;
     字段名 字段类型；
     CHECK (字段名 比较 值)
     ）

   • DEFAULT：默认值约束
     创建表时添加默认约束：
     CREATE TABLE 表名（
     字段名 字段类型;
     字段名 字段类型 DEFAULT 值；
     CHECK (字段名 比较 值)
     ）
     建表后添加默认值：
     ALTER TABLE 表名 MODIFY 字段名 字段类型 DEFAULT 默认值；
     删除默认约束：
     ALTER TABLE 表名 MODIFY 字段名 字段类型；

4. 查看某张表中的约束
   语法：SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME="表名"；

视图

1. 常见的数据库对象
   

• 约束（CONSTRAINT）:执行数据效验的规则，用于保证数据完整性的规则
• 视图（VIEW）：一个或多个数据表里的数据的逻辑显示，视图并不存储数据
• 索引（INDEX）：用于提高查询性能，相当于书的目录
• 存储过程（FUNCTION）：用于完成一次特定的计算，具有一个返回值
• 触发器（TRIGGER）：相当于事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

什么是视图

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

创建视图
CREATE VIEW 视图名 [（字段列表）] AS 查询语句;
使用SELECT语句来查询视图。
可以使用CONCAT关键字来拼接字符，达到对视图进行格式化的效果
可以基于视图创建视图
视图除了可以像其他数据表一样使用那些基本的方法，还可以使用SHOW CREATE VIEW 视图名，来查看视图的详细定义信息

不可更新视图

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

修改视图
方式一：CREATE OR REPLACE VIEW 视图表名 AS SELECT语句...
方式二：ALTER VIEW 视图名 AS SELECT语句...
删除视图
DROP VIEW [IF EXISTS] 视图名；

视图优点

1. 操作简单
2. 减少数据冗余
3. 数据安全
4. 适应灵活多变的需求
5. 能够分解复杂的查询逻辑

存储过程

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

• 简化操作，提高SQL语句的重用性，减少开发程序员的压力
• 减少操作过程中的失误，提高效率
• 减少网络传输（客户端不需要把所有的SQL语句通过网络发给服务器）
• 减少SQL语句暴露在网上的风险，也提高了数据查询的安全性

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

分类

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

• 没有参数（无参数无返回）
• 仅仅带IN类型（有参数无返回）
• 仅仅带OUT类型（无参数又返回）
• 既带IN又带OUT（有参数又返回）
• 带INOUT（有参数又返回）

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

创建存储过程

语法：CREATE PROCEDURE 存储过程名（[IN|OUT|INOUT] 参数名 参数类型，参数名...）
[characteristics ...] BEGIN 存储过程体SQL语句 END
存储过程体中可以有多条SQL语句，每条语句间用分号隔开（由于分号在SQL中是结束的意思，为了避免SQL执行的分号时结束整个语句，我们可以使用——DELIMITER 新的结束标记。来从新定义结束标记）。如果只有一条SQL语句，则可以省略BEGIN和END。

• 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语句
  • SQL SECURITY{DEFINER|INVOKER}：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程
    • DEFINER：表示只有当前存储过程的创建者或定义者才能执行当前存储过程。默认为此权限
    • INVOKER：表示拥有当前存储过程的访问权限的用户才能执行当前存储过程
  • COMMENT'string'：注释信息，可以用来描述存储过程

BEGIN和END之间的关键字

• DECALRE:用来声明变量，且需要在其他语句使用之前进行变量的声明
• SET：赋值语句，用于对变量进行赋值
• SELECT ... INTO：把从数据表中查询的结果放到变量中，即变量的赋值

调用存储过程
语句：CALL 存储过程名（参数列表）；
调用IN模式的参数：CALL 存储过程名（参数列表）；
调用OUT模式的参数：
SET @name；
CALL 存储过程名（@name）;
SELECT @name;
调用INOUT模式的参数：
SET @name=值；
CALL 存储过程名（@name）;
SELECT @name;

存储函数（自定义函数）

语法：CREATE FUNCTION 函数名（参数名 参数类型...） RETURNS 返回值类型 [characteristics ...] BEGIN 函数体#在函数体中肯定有RETURN语句 END
存储函数只能有一个返回值，即SQL语句应只能返回一条数据
语法说明

1. 参数列表：指定参数为IN、OUT、INOUT。默认为IN
2. RETURNS type：表示函数的返回值类型
3. 指定函数约束，和存储过程相同

存储函数的调用
SELECT 函数名（参数列表）；

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

查看
存储过程创建信息查看：SHOW CREATE PROCEDURE 存储过程名
存储过程状态信息查看：SHOW PROCEDURE STATUS [LIKE 'PATTERN']
存储函数创建信息查看：SHOW CREATE FUNCTION 存储函数名
存储函数状态信息查看：SHOW FUNCTION STATUS [LIKE 'PATTERN']
[LIKE 'PATTERN']：为匹配存储过程或函数名，如果不加则会匹配所有
修改
ALTER [PROCEDURE|FUNCTION] 存储过程或函数名 [characteristics ...]
删除
DROP PROCEDURE|FUNCTION 存储过程或函数名；

变量、流程控制与游标（作用于存储函数和存储过程）

变量

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

系统变量：

• 全局系统变量（关键字global），简称全局变量
  • 静态变量：它是特殊的全局系统变量，在MySQL实例服务运行期间它们的值不能通过SET动态修改
• 会话体统变量（关键字session），简称local变量。默认为会话

查看系统变量
查看所有全局变量：SHOW GLOBAL VARIABLES;
查看所有会话变量：SHOW SESSION VARIABLES;或 SHOW VARIABLES
查看满足条件部分的全局变量：SHOW GLOBAL VARIABLES LIKE "%标识符%";
查看满足条件部分的会话变量：SHOW SESSION VARIABLES LIKE "%标识符%";
查看指定全局变量：SHOW @@global.变量名；
查看指定会话变量：SHOW @@session.变量名；或SHOW @@变量名；
修改系统变量的值
方式一：修改MySQL的配置文件，修改后需要重启MySQL服务
方式二：在MySQL服务运行期间，使用SET关键字修改
修改全局变量：

• SET @@global.变量名=变量值；
• SET GLOBAL 变量名=变量值；

修改会话变量：

• SET @@session.变量名=变量值；
• SET SESSION 变量名=变量值；

自定义变量（用户变量）

自定义变量以一个@开头，根据作用范围不同，又分为会话用户变量和局部变量

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

会话用户变量

用户变量的定义：
方式一：使用“=”或“:=”

• SET @变量名=值；
• SET @变量名:=值；

方式二：使用“:=”或INTO关键字

• SELECT @变量名:=表达式 [FROM 等字句]；
• SELECT 表达式 INTO @变量名[FROM 等字句]；

用户变量的查看、运算、比较等
语法：SELECT @变量名

局部变量

使用DECLARE语句来定义局部变量
语法：DECLARE 变量名 类型 [default 值]；如果没有default语句则初始值为null
变量赋值
方式一（一般用于赋值简单的值）：SET 变量名=值；|| 变量名:=值；
方式二（一般用于赋值表中的字段值）：SELECT 字段名或表达式 INTO 变量名 FROM 表名；
使用变量（查看、运算、比较等）
SELECT 局部变量名；

会话用户变量和局部变量的比较

作用域	定义位置	语法
会话用户变量	当前会话	会话的任何地方
局部变量	定义它的BEGIN END中	BEGIN END的第一句话

定义条件与处理程序

类似于Java中的try/catch。在BEGIN和AND中使用

定义条件

• 定义错误码：语法：DECLARE 为错误命名 CONDITION 错误码例如1054；
  当发生错误，错误码为1054时
• 定义错误码后的小括号（具体的错误）：语法：DECLARE 为错误命名 CONDITION FROM SQLSTATE '42000';

定义处理程序
语法：DECLARE 处理方式 HANDLER FOR 错误类型 处理语句；
处理错误的三种方式：

• CONTINUE:遇到错误不处理，继续执行
• EXIT：遇到错误马上退出
• UNDO：遇到错误后撤回之前的操作，MySQL占不支持该操作

错误类型（即条件）

• SQLSTATE:字符串错误码
• MySQL_error_code：匹配数值类型错误代码
• 错误名称：使用DECLARE 为错误命名 CONDITION...定义的错误名称
• SQLWARNING：匹配所有以01开头的SQLSTATE错误代码
• NOT FOUND：匹配所有以02开头的SQLSTATE错误代码
• SQLEXCEPTION：匹配所有没有被SQLWARNING和NOT FOUND捕获的错误代码

处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像SET 变量=值，这样简单的语句，也可以是使用BEGIN...END编写的复合语句

流程控制

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

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

针对MySQL的流程控制语句主要有3类，只能适用于存储过程和存储函数

• 条件判断语句：IF语句和CASE语句
• 循环语句：LOOP、WHILE和REPEAT语句
  • LOOP:一般用于实现简单的死循环
  • WHILE:先判断后执行
  • REPEAT：先执行后判断，至少会执行一次
• 跳转语句：ITERATE和LEAVE语句

分支结构——IF（只能在BEGIN...END中使用。即存储过程，存储函数）

语法：
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]...
[ELSE 操作]
END IF

分支结构——CASE

语句一（类似于Java中的Switch语句）：
CASE 表达式
WHEN 值1 THEN 结果1或语句1（如果是语句需要一分号结尾）
WHEN 值2 THEN 结果2或语句2（如果是语句需要一分号结尾）
...
ELSE 结果n或语句n（如果是语句需要一分号结尾）
END [case]（如果是放在BEGIN...END中需要加上[case]，如果放在select则不需要）

语句二（类似于多重IF结构）
CASE 表达式
WHEN 条件1 THEN 结果1或语句1（如果是语句需要一分号结尾）
WHEN 条件2 THEN 结果2或语句2（如果是语句需要一分号结尾）
...
ELSE 结果n或语句n（如果是语句需要一分号结尾）
END [case]（如果是放在BEGIN...END中需要加上[case]，如果放在select则不需要）

循环结构——LOOP

语法：
[loop...lable:] LOOP
循环执行的语句
END LOOP [loop...lable]

• loop...lable表示LOOP语句的标注名称，该参数可以省略。但循环中如果加入了循环控制语句（LEVAE或ITERATE），则必须要添加标注名称。

循环结构——WHILE

语法：
[while...lable:]WHILE 循环条件 DO
循环体
END WHILE [while...lable];

• while...lable表示WHILE语句的标注名称，可以省略。但循环中如果加入了循环控制语句（LEVAE或ITERATE），则必须要添加标注名称。

循环结构——REPEAT（类似于Java的do...while循环）

语法：
[repeat...lable:]REPEAT
循环体语句
UNTIL 结束1循环的条件表达式
END REPEAT[repeat...lable]

• repeat...lable表示REPEAT语句的标注名称，可以省略。但循环中如果加入了循环控制语句（LEVAE或ITERATE），则必须要添加标注名称。

跳转语句——LEAVE（类似于break）

语法：LEAVE 标记名；

跳转语句——ITERATE（类似于continue）

语法：ITERATE 标志名；

游标（光标）

在使用游标时会为数据加锁，会影响性能

1. 声明游标
   使用DECLARE关键字来声明游标
   语法：DECLARE 游标名 CURSOR FOR SELECT语句

2. 打开游标
   语法：OPEN 游标名；

3. 使用游标（从游标中获取数据）
   语法：FETCH 游标名 INTO 变量名；

4. 关闭游标
   语法：CLOSE 游标名；

MySQL8.0新特性——全局变量的持久化

MySQL数据库中，全局变量可以通过SET GLOBAL语句来设置（只会在当前会话生效）
语法：SET GLOBAL 变量名=值；
使用SET PERSOST命令会使MySQL的配置文件发生修改，谨慎使用

触发器

触发器可以在一张表有数据更新时，同步更新另一张表的数据

创建触发器

语法：
CREATE TRIGGER 触发器名
{BEFORE|AFTER} {INSERT|UPDATE|DELECT} ON 表名
FOR EACH ROW
触发器执行的语句块；

• 表名：触发器监控的对象
• BEFORE|AFTER：表示触发的时间，BEFORE表示事件之前触发，AFTER表示事件之后触发
• INSERT|UPDATE|DELECT：表示触发条件
• 触发器执行的语句块：可以是单条的SQL语句，也可以是由BEGIN...END结构组成的复合语句

查看和删除触发器

查看
方式一：查看当前数据库的所有触发器的定义
SHOW TRIGGERS\G 或 SHOW TRIGGERS；
方式二：查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名；
方式三：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息
SELECT * FROM information_schema.TRIGGERS;
删除
DROP TRIGGER IF EXEISTS 触发器名；

触发器的优缺点

优点

1. 保证数据的完整性
2. 触发器可以帮助我们记录操作数据
3. 触发器可以在操作数据前对数据进行合法性检查

缺点

1. 可读性差
2. 相关数据的变更可能会导致触发器出错
3. 在使用外键时可能导致触发器失效

存储引擎

MySQL体系结构

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，所以存储引擎也可被称为表类型。

在建表时指定存储引擎（如果不指定则会使用默认的存储引擎）：
CREATE TABLE 表名（
字段名 字段类型 ...
...
）ENGIN = INNODB [COMMENT 表注释]

查询当前表支持的存储引擎：
SHOW ENGINES;

存储引擎的特点

1. INNODB
INNODB是一种兼顾高可靠性和高性能的通用存储引擎
特点：

• DML操作遵循ACID模型、支持事物
• 行级锁，提高并发访问性能
• 支持外键FOREIGN KEY约束，保证数据的完整性和正确性

文件：
xxx.lbd:xxx代表表名，INNODB的每张表都会对应这样一张表空间文件，存储该表的表结构（frm—早期，sdl—新版的）、数据和索引
参数：innodb_file_per_table
SHOW VARIABLES LIKE "innodb_file_per_table";
如果该参数开启，代表对于innodb引擎的表，每一张都对应一个ldb文件
打开MySQL的数据存放目录就可以找到这些文件。在cmd中使用ibd2sdi 表名.ibd可以查看

逻辑存储结构

• 表空间：INNODB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件在表空间文件中可以包含多个Segment段
• 段：表空间由各个段组成，常见的段有数据段，索引段，回滚段等。INNODB中对于段的管理都是引擎自身完成的，不需要认为控制，一个段中包含多个区
• 区：区是表空间的单元结构，每个区的大小为1M，默认情况下，INNODB存储引擎页大小为16K，即一个区中共有64个连续的页
• 页，页是组成区的最小单元，页也是INNODB存储引擎磁盘管理的最小单元，每个页的默认大小为16KB，为了保证页的连续性,INNODB存储引擎每次从磁盘申请4-5个区
• 行：INNODB存储引擎是面向行的，也就是说数据按行进行存放，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段

2. MyISAM
MyISAM是MySQL早期的默认存储引擎
特点

• 不支持事物，不支持外键
• 支持表锁，不支持行锁
• 访问速度快

文件
xxx.sdl:存储表结构信息
xxx.MYD:存储数据
xxx.MYI:存储索引

3. Memory
Memory引擎的表数据是存储在内存中，由于收到硬件问题或断电问题的影响，只能将这些表作为临时表或缓存使用
特点：

• 内存存放
• hash索引

文件：
xxx.sdl存储表结构信息

比较

特点	INNODB	MYISAM	MEMORY
存储限制	64TB	有	有
事物安全	支持	——	——
锁机制	行锁	表锁	表锁
B+tree索引	支持	支持	支持
Hash索引	——	——	支持
全文索引	支持	支持	——
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	——	——

存储引擎的选择

• INNODB：MySQL的默认引擎，支持外键、事物。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询外，还包含很多更新，删除操作。那么INNODB是个比较合适的选择
• MyISAM：如果应用是以读操作和插入操作为主，只有很少更新和删除操作，并且对事物的完整性、并发性要求不是很高，那么选择这个存储引擎非常合适
• Memory：将数据保存在内存中，访问速度快，通常用于临时表或缓存。Memory的缺点就是对表的大小有限制，太大的表无法缓存在内存中。而且无法保证数据的安全性

索引

索引的优势和劣势

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构：

索引结构	描述
B+Tree索引	最常见的索引类型，大部分索引都支持B+树索引（如没有明确说明则大部分都是该索引）
Hash索引	底层数据结构使用hash表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R+tree（空间索引）	空间索引是MYLSAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full+text（全文索引）	是一种通过建立倒排索引，快速匹配文档的方式，类似于Lucene，Solr，ES

上述是MySQL所支持的所有索引，下面是几个搜索引擎对这几种索引的支持：

索引	INNODB	MYLSAM	MEMORY
B+Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R+tree（空间索引）	不支持	支持	不支持
Full+text（全文索引）	5.6版本后支持	支持	不支持

B+Tree的演变

二叉树
假如说我们的MySQL的索引是二叉树结构。在理想情况下它会是这样：

但如果是按顺序插入的，则它就会变成一个单向链表：

所以：如果选择二叉树作为索引结构，就会存在以下缺点：

• 顺序插入时，会形成一个链表，查询性能大大降低
• 大量数据情况下，层级较深，检索速度慢

导致这个现象的主要原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。想要解决这个问题，我们就需要一棵平衡二叉树。
红黑树：红黑树是一棵自平衡二叉树，即使是顺序插入数据，最终形成的数据结构也是一棵平衡点二叉树；

但即便如此，由于红黑树也是一棵二叉树，所以也会存在一个缺点：

• 大量数据情况下，层级较深，检索速度慢

B-Tree
B-Tree：B树是一棵多叉平衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。
B树相对于平衡二叉树，每个节点存储了更多的键值（key）和数据（data），并且每个节点拥有更多的子节点，子节点的个数一般称为阶。
以一颗最大度数（max-degree）为5阶的B-Tree为例：这个B树每个节点最多存储4个key，5个指针：

• 树的度数指的是一个节点的子节点个数。
  图中的每个节点称为页，页就是我们的磁盘块。在MySQL中数据读取的基本单位就是页，所以这里叫做页更符合MySQL中索引的底层数据结构。
  
  如果这是一个5阶B数，在插入数据时，所有数据都会放在·1第一层，按小到大排列。当第一层的数据打到五个后，会以中间值为奇点，分别向左和向右分列出第二层，以此类推。

特点：

• 5阶的B树，每一个节点最多存储4个key，对应5个指针。
• 一旦节点存储的key数量达到5，就会裂变，中间元素向上分裂。
• 在B树中，非叶子节点和叶子节点都会存放数据。

B+Tree

B+Tree是B-Tree的变种，

这是一颗最大度数为4（4阶）的B+Tree。我们可以看到两部分：

• 蓝色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
• 绿色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。
  

B+Tree和B-Tree对比：

• 所有数据都出现在叶子节点
• 叶子节点形成一个单向链表
• 非叶子节点仅仅起到索引数据作用，具体的数据都是叶子节点存放的

MySQL索引数据结构对经典的B+Tree进行了优化，在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提个区间访问的性能，便于排序。

索引分类

在MySQL数据库中将索引分为以下几类：

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

聚集索引&二级索引

在innodb存储引擎中，根据索引的存储形式可以分为聚集索引和二级索引：

分类	含义	特点
聚集索引（Clusteredindex）	将数据存储和索引放到一块，索引结构的叶子节点保存了行数据	必须有而且只有一个
二级索引（Secondaryindex）	将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个
聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
• 如果表没有主键，或没有适合的唯一索引，则INNODB会自动生成一个rowid作为隐藏的聚集索引

聚集索引和二级索引的具体结构：

• 聚集索引的叶子节点下挂的是这一行的数据
• 二级索引的叶子结点下挂的是该字段对应的主键值

当我们对一张表输入该查询语句时：SELECT * FROM USER WHERE NAME='张三';

1. 由于是根据name字段进行查询，所以先根据name='张三'找到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到“张三”对应的主键值。
2. 由于查询返回的值是*，所以此时还要根据主键值到聚集索引中查找对应的记录。
3. 最终拿到这一行的数据直接返回即可。

这种先到二级索引中查找数据，找到主键值，然后在到聚集索引中根据主键值，获取数据的方式就被称为回表查询。

索引语法

创建索引：
CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名（
字段名...
）

查看索引：
SHOW INDEX FROM 表名；
删除索引：
DROP INDEX 索引名 ON 表名；

SQL性能分析

SQL执行频率：MySQL客户端连接成功后，通过SHOW [SESSION|GLOBAL] STATUS;命令可以提供服务器状态信息，通过如下命令可以查看当前数据库增删改查语句的访问频次：

• session：查看当前会话
• global：查询全局数据
• SHOW [SESSION|GLOBAL] STATUS LIKE'Com_';
  • Com_delete：删除次数；
  • Com_insert：插入次数；
  • Com_select：查询次数；
  • Com_update：更新次数；

通过这些命令我们就可以知道该数据库是以查询为主还是以增删改为主。如果是以增删改为主，那就没有必要进行索引的优化；如果是以查询为主，那就要考虑对数据库进行索引优化了。我们可以通过慢查询日志来针对查询语句进行优化

慢查询日志

慢日志查询记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，我们可以通过查询系统变量slow_query_log来确定:
SHOW VARIABLES LIKE 'SLOW_QUERY_LOG';
开启慢日志指令：
SET GLOBAL SLOW_QUERY_LOG=ON;
在开启慢日志后，指定路径下就会多出一个记录慢日志查询的文件：
SHOW VARIABLES LIKE '%SLOW%';
查看慢日志查询设置：
SHOW VARIABLES LIKE 'LONG%';
设置合理的，业务可以接受的慢查询时间上限long_query_time
SET LONG_QUERY_TIME=2;
MySQL提供的数据导入方式（用来导入大量的数据）：
LOAD DATA LOCAL INFILE '数据存放地址' INTO TABLE '表名' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\N';

profile详情

show profile 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了，通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
SELECT @@have_profiling;
可以通过SET语句在session/global级别开启profile：
SET [SESSION|GLOBAL] PROFILING=1;
打开开关后，我们所执行的SQL语句都会被MySQL记录，并记录执行时间都消耗到哪去了。
通过如下命令查看耗时情况：
方式一：查看每一条SQL的基本耗时情况
SHOW PROFILES;
方式二：查看指定query_id的SQL语句各个阶段的耗时情况
SHOW PROFILE FOR QUERY QUERY_ID;

explain

EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
语法：在SELECT语句前加上EXPLAIN或DESC关键字
explain执行计划中各个字段的含义：

• id：SELECT的查询序号（id相同，执行顺序从上到下，id不同，值越大，越先执行）
• select_type：表示SELECT的类型是简单查询还是复杂查询，常见取值（SIMPLE:简单表，即不使用表连接或者子查询；PRIMARY:主查询，即外层查询；UNION：表示第二个或后面的查询语句；SUBQUERY:SELECT/WHERE之后包含了子查询）等。
• type：表示连接类型，性能由好到差的连接类型为：NULL、system、const、eq_ref、ref、range、index、all
  • system,const：MySQL查询时能够将查询条件优化或常量，并且走primary key或者unique key，整张表中只可能查询出一条数据
  • eq_ref：在进行连接查询或者子查询时primary key或者unique key索引的所有字段
  • ref：进行查询时，使用NORMAL或primary key或者unique key中的部分索引
  • range：通常出现在能够走索引的范围查询
  • index：扫描全索引就能拿到结果值，一般是利用二级索引，使用索引覆盖
  • all：全表扫描，扫描聚簇索引的所有叶子节点
• possible_key：显示可能应用在这张表的索引，一个或多个
• key：实际使用的索引，如果为NULL，则表示没有使用索引
• key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际长度，在不损失精度的情况下，长度越短越好
• rows：MySQL认为必须要执行查询的行数，在INNODB引擎的表中，是一个估值，可能并不总是准确的
• filtered：表示返回结果的行数占需读取行数的百分比，值越大越好
• ref：这一列显示在key列记录的索引中，表查找值所用到的列或常量，常见的有const（常量）、字段名

索引使用

最左前缀法则（最要针对联合索引）

最左前缀法则：指查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效（后面的字段索引失效）。即在查询时如果用到了联合索引，就必须用到索引创建时的第一个字段，否则索引不生效，如果没有按照创建顺序使用索引，或中间少使用了索引，则在查询时会丢失掉后面的索引。

没有使用索引：在去掉profession查询时，索引失效

中间丢失索引：在去掉age查询时，只会使用部分索引。

where后面的字段顺序对索引没有影响。只要联合索引最左侧的字段存在，联合索引就能使用。

范围查询

在使用范围查询时，如果使用大于、小于，则会使部分索引失效。所以最好使用大于等于、小于等于。这样不仅满足最左前缀法则，还可以使索引全部成功。

索引失效

1. 索引列运算：在创建索引的列上进行运算
2. 字符串不加引号：字段类型不匹配
3. 模糊查询：尾部的模糊查询，索引不会失效。尾部的模糊查询，索引失效。
   
4. or连接条件：用or分割开条件，如果or的前面的列中有索引，后面的列中没有索引，那么涉及到的索引都不会被用到。只有两侧都有索引，索引才不会失效。
5. 数据分布影响：如果MySQL评估使用索引比全表查询更慢则不使用索引。

SQL提示

在SQL语句中使用人为的手段，达到优化操作的目的

• use index：建议MySQL使用那条索引（仅仅是建议，MySQL内部还是会进行评估）
  语法：select * from 表名 use index（索引名）；
• ignore index：忽略指定索引
  语法：select * from 表名 ignore index（索引名）；
• force index：强制使用索引
  语法：select * from 表名 force index（索引名）；

覆盖索引

当所查询的字段，已经创建了联合索引，则不需要在进行回表查询。尽量使用索引覆盖，减少使用select * 使用select * 必定会使用回表查询

前缀索引

当字段类型为字符串时，有时候需要索引很长的字符串，这会让索引变得很大。查询时浪费大量的磁盘IO，影响查询效率。此时可以将字符串的一部分前缀建立索引，这样可以大大节约索引空间，提升索引效率。
语法：CERATE INDEX 索引名 ON 表名（column（查询长度））;

单列索引与联合索引

• 单列索引：索引中只包含一个列

• 联合索引：索引中包含多个列

索引设计原则

• 针对于数据量较大，查询比较频繁的表建立索引
• 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
• 尽量选择区分度高的列建立索引，尽量建立唯一索引，区分度越高，使用索引的效率就越高
• 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
• 尽量使用联合索引，减少单列索引。查询时，联合索引很多时候可以覆盖索引、节省存储空间、避免回表、提高查询效率。
• 要控制索引的数量，索引并不是多多益善。索引越多，维护索引结构的代价也就越大，会影响增删改查的效率。
• 如果索引列不能存储空值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效的用于查询。

事务

事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体，一起向系统提交或撤回操作请求，即这些操作要么同时成功，要么同时失败。

事务控制一：
查看事务提交方式：（1为自动提交）
SELECT @@autocommit;
设置事务提交方式：
SET @@autocommit = 0;
提交事务：
COMMIT;
回滚事务：
ROLLBACK；

我们只是将事务由自动提交改为手动提交，此时我们执行DML语句都不会提交，需要手动COMMIT

事务控制二：
开启事务：
START TRANSACTION 或BEGIN;
提交事务：
COMMIT;
回滚事务：
ROLLBACK；

事务四大特性（ACID）

• 原子性：事务时不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性：事务完成使必须使所有的数据保持一致状态
• 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

脏读：一个事务读取到另一个事务还没有提交的数据。
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同。
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时又发现数据行已存在。

事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：

隔离级别	脏读	不可重复读	幻读
Read uncmmitted（读未提交）	√	√	√
Read committed（读已提交）	×	√	√
Repeatable Read（默认，可重复读）	×	×	√
Serializable（串行化）	×	×	×

查看事务隔离级别：
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别：
SET {SESSION|GLOBAL} TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE}

事务隔离级别越高，数据越安全，性能越低

事务原理

事务的四个特性（ACID），实际上是由两个部分组成：原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。而隔离性是由数据库的锁，加上MVCC来保证的。

redo log

redo log又称重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
该日志主要由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）。前者是在内存中，后者是在磁盘中。当事务提交之后会把所有的修改信息都存到该日志文件中，用于在刷脏页到磁盘发生错误的时候，进行数据恢复使用。
如果没有redo log：事务如果在从缓冲区向磁盘提交的过程中出现错误，而用户却受到已提交的响应，这就无法保证事务的持久性。

redo log：会将对数据的操作记录在文件中，如果出现错误就会回到数据的初始状态，如果没有错误保存的信息就会删除。

undo log

undo log：回滚日志，用于记录数据被修改前的信息，作用包含两个：

• 提供回滚（保证事务的原子性）
• MVCC（多版本并发控制）

undo log和redo log记录物理日志不一样，他是逻辑日志。可以认为当delete一条数据时，undo log中会记录一条对应的insert记录，反之亦然。当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到对应的内容并进行回滚。

undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。
undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的rollback segment回滚段中，内部包含1024个undo log segment。

MVCC

基本概念

1. 当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取记录进行加锁。
   对于我们的日常操作如：select...lock in share mode（共享锁），select...from update、update、insert、delete（排他锁）都是一种当前读。
2. 快照读：简单的select（不加锁）就是快照读，快照读读取的是记录数据的可见版本，有可能是历史数据，不加锁是非阻塞读。
   • Read Commtted：每次select，都生成一个快照读。
   • Repeatable Read：开启事务后第一个select语句才是快照读的地方。
   • Serializable：快照读会退化为当前读。
3. MVCC：全称Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本使得读写操作没有冲突，快照读为Mysql实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

隐藏字段

id	age	name
1	1	张三
2	2	李四
3	3	王五

当我们创建如上一张表时，我们所能看到的是显示的三个字段。但InnoDB还会自动的给我们添加三个隐藏字段：

隐藏字段	含义
DB_TRX_ID	最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID
DB_ROLL_RTX	回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本
DB_ROW_ID	隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

undo log
回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。
版本链

read view
Read View（读视图）是快照读，SQL执行时MVCC提取数据的依据，记录并保护系统当前活跃的事务（未提交的）id。

Read View中包含了四个核心字段：

字段	含义
m_ids	当前活跃的事务id集合
min_trx_id	最小活跃事务id
max_trx_id	预分配事务id，当前最大事务id+1（因为事务id是自增的）
creator_rtx_id	ReadView创建者的事务id

在ReadView中规定了版本链的访问规则：
trx_id代表当前undo log版本链对应事务id

条件	是否可以访问	说明
rtx_id==creator_rtx_id	可以访问版本	成立，说明数据是当前这个事务更改的
rtx_id<min_trx_id	可以访问版本	成立，说明数据已经提交了
rtx_id>max_trx_id	不可以访问版本	成立，说明该事务是在ReadView生成后才开启
min_trx_id<=rtx_id<=max_trx_id	如果rtx_id不在m_ids中，是可以访问该版本的	成立，说明数据已经提交了

不同的隔离级别，生成的ReadView的时机是不同的：

• READ COMMITTED：在事务中每一次执行快照读时生成ReadView
• REPEATABLE READ：仅在事务第一次执行快照读时生成ReadView，后续复用该ReadView

原理分析
RC隔离级别

RR隔离级别

MySQL锁机制

概述
锁是计算机协调多个进程或线程并发访问某一资源的机制，在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题。锁冲突也是影响数据库并发访问性能的一个重要因果。

MySQL中的锁，按锁的粒度分：

• 全局锁：锁定数据库中的所有表
• 表级锁：每次操作锁住整张表，MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁。它实现简单资源消耗较少，被大部分MySQL引擎支持。
• 行级锁：每次操作锁住对应的行数据。MySQL中锁定粒度最细的一种锁。表示只针对当前操作的行进行加锁。行级所能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也越大。行级锁分为共享锁和排他锁，InnoDB存储引擎默认采用行锁。InnoDB与MyISAM的最大不同有两点，一是支持事（TRANSACTION），二是采用了行级锁。
• 页锁：页锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多。行级锁冲突少，但速度慢。所以取了折中的页级，一次锁定相邻的一组记录。

按数据操作类型分类：

• 读锁（共享锁，Share Lock）：针对同一份数据，多个读取操作可以同时进行而不会互相影响。若事务T对数据对象A加上读锁，则事务T只能读A。其他事务只能在对A加读锁，而不能加写锁。直到事务T释放A上的读锁。这就保证了其他事务可以读A，但在事务T释放A上的读锁之前不能对A做任何修改。
• 写锁（排它锁，Excluslve Lock）：写锁只可以加一个，当前写操作没有完成前，它会阻断其他写锁和读锁。若事务T对数据对象A加上写锁，则只允许事务T读取和修改A，其他任何事物都不能再对A加任何类型锁，直到T释放A上的锁。它防止其他任何事物获取资源上的锁。直到在事务的末尾将资源上的原始锁释放为止。在更新操作（INSERT、UPDATE 或 DELETE）过程中始终应用排它锁。

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
语法
加全局锁：flush tables with read lock;
释放锁：unlock tables；
特点
数据库中加全局锁，是一个比较重要的操作，存在以下问题：

• 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就要停摆。
• 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志，会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数“--single-transaction”参数来完成不加锁的一致性数据备份：mysqldump --single-transaction -u用户名 -p密码 homewore > homework.sql

表级锁

表级锁，每次操作锁住整张表，锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BOB等存储引擎中。
对于表级锁主要分以下三类：

• 表锁
• 元数据锁（meta date lock，MDL）
• 意向锁

表锁

对于表锁分为两类：

• 表共享读锁（read lock）
• 表独占写锁（write lock）

语法：
加锁：lock table 表名 read（write） 表名2 read（write）；
释放锁：unlock tables;
查看表上加过的锁：show open tables；

特点：

元数据锁

meta data lock（元数据锁），简写MDL。MDL加锁过程是系统自动控制，无需显示使用。在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的一致性。在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

这里的元数据可以简单理解为一张表的表结构。也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。

MySQL在5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁（共享），当对表结构进行变更操作的时候，加MDL写锁（排他）。

对应SQL	锁类型
lock tables xxx read/write	SHARED_READ_ONLY/SHARED_NO_READ_WRITE
select、select…lock in shred mode	SHARED_READ 与 SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
insert、update、delete、select…for update	SHARED_WRITE 与 SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
alter table…	EXCLUSIVE，与其他的MDL都互斥

在mysql 5.7中，performance_schema库中新增了metadata_locks表，专门记录MDL的相关信息。首先要开启metadata_locks锁记录：
update performance_schema.setup_instruments set ENABLED = 'YES',TIMED ='YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
查看数据库中元数据锁的情况：
select * from performance_schema.metadata_locks;

意向锁

为了避免DML在执行时，加的行锁与表锁冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行的数据是否加锁，使用意向锁来减少表锁的检查。
比如一个事务想要对表加排它锁，如果没有意向锁的话，那么该事物在加锁前需要判断当前表的每一行是否已经加了锁，如果表很大，遍历每行进行判断需要耗费大量时间，如果使用意向锁的话，那么加锁前，只需要判断当前表是否有意向锁即可。这样加快了对表锁的处理速度。

分类：

• 意向共享锁（IS）：由语句select... lock in share mode添加。与表锁共享锁（read）兼容，与表锁排它锁（write）互斥。
• 意向排它锁（IX）：由insert、update、delete、select...for update添加。与表锁共享锁（read），表锁排它锁（write）都互斥。意向锁之间不会互斥。

可以通过以下SQL，查看意向锁及行锁的加锁情况：
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
意向锁是由存储引擎自己维护的，是内部机制，用户无法操作意向锁

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的。而不是对记录加锁。

行级锁分类：

• 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此进行update和delete。在RC，RR隔离级别下都支持。
• 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变。防止其他事务在这个间隙进行insert。产生幻读。在RR隔离级别下都支持。
• 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下都支持。

行锁

Inndb实现了以下两种类型的行锁：

• 共享锁（S）：允许一个事务区读一行，阻止其他事务获得想同数据集的排它锁。
• 排它锁（X）：允许获取排它锁的事务更新数据，阻止其他事务获得想同数据集的共享锁和排它锁。

两种行锁的兼容情况：

常用的SQL语句在执行时，所加的行锁如下：

SQL	行锁类型	说明
INSERT…	排它锁	自动加锁
UPDATE…	排它锁	自动加锁
DELETE…	排它锁	自动加锁
SELECT（正常）	不加任何锁
SELECT…LOCK IN SHARE MODE	共享锁	需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT…FOR UPDATE	排它锁	需要手动在SELECT之后加FOR UPDATE

默认情况下，InnoDB在RR事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

• 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
• InnoDB的行锁是针对与索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

可以通过以下SQL，查看意向锁及行锁的加锁情况：
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

间隙锁和临键锁

默认情况下，InnoDB在RR事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

• 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
• 索引上的等值查询（非唯一普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。
• 索引上的范围查询（唯一索引）会访问到不满足条件的第一个值为止。

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上使用间隙锁。