MySQL学习笔记

最新推荐文章于 2025-03-18 15:03:16 发布

myc13381

最新推荐文章于 2025-03-18 15:03:16 发布

阅读量1.3k

点赞数 23

文章标签： mysql 学习笔记

本文链接：https://blog.youkuaiyun.com/myc13381/article/details/140246713

版权

MySQL

Ubuntu下MySQL 安装

sudo apt install mysql-server
sudo apt install mysql-client

配置文件在/etc/mysql/debain.cnf中，里面包含默认用户名和密码

启动/关闭/登录

Windows

net start mysql
net stop mysql
mysqladmin -u root password xxxx # 管理员模式修改root用户密码

Linux

sudo systemctl start mysql
sudo systemctl stop mysql
sudo systemctl restart mysql # 重启
sudo systemctl status mysql # 查看运行状态

备份/恢复

# 在shell中操作
# 备份
mysqldump -u [username] -p[password] --routines [database_name] > [backup_file.sql]
# 恢复
mysql -u [username] -p[password] [database_name] < [backup_file.sql]

概述

SQL语句的分类

DDL：Data Define Language ------------------ 数据定义语言用来定义数据库对象

DML：Data Manipulaion Language --------- 数据操作语言，用来对数据库进行增删改

DQL：Data Query Language ------------------- 数据询查语言，用来查询数据库中表的记录

DCL：Data Control Language ------------------ 数据控制语言，用来创建数据库用户，控制数据库访问权限

SELECT -------------------------------------------- 从数据库中查询提取数据
UPDATE -------------------------------------------- 更新数据库中的数据
DELETE -------------------------------------------- 从数据库中删除数据
INSERT INTO ------------------------------------ 将新数据插入数据库
CREATE DATABASE ----------------------------- 创建一个新的数据库
ALTER DATABASE ------------------------------- 修改数据库
CREATE TABLE ----------------------------------- 创建一个新表
ALTER TABLE ------------------------------------- 修改表
DROP TABLE --------------------------------------- 删除一个表
CREATE INDEX ----------------------------------- 创建索引（搜索键）
DROP INDEX --------------------------------------- 删除一个索引

MySQL 常见数据类型

数值数据类型

数据类型	描述
BIT(size)	位值类型。每个值的位数在 size 中指定。 size 参数可以保存从 1 到 64 的值。size 的默认值为 1。
TINYINT(size)	一个非常小的整数。有符号范围是-128 到 127。无符号范围是 0 到 255。size 参数指定最大显示宽度（即 255）
BOOL	零被认为是false，非零值被认为是true。
BOOLEAN	等同 BOOL
SMALLINT(size)	一个小整数。有符号范围是 -32768 到 32767。无符号范围是 0 到 65535。size 参数指定最大显示宽度（即 255）
MEDIUMINT(size)	一个中等整数。有符号范围是 -8388608 到 8388607。无符号范围是 0 到 16777215。size 参数指定最大显示宽度（即 255）
INT(size)	一个中等整数。有符号范围是 -2147483648 到 2147483647。无符号范围是 0 到 4294967295。size 参数指定最大显示宽度（即 255）
INTEGER(size)	等于 INT(size)
BIGINT(size)	一个大整数。有符号范围是-9223372036854775808到9223372036854775807。无符号范围是0到18446744073709551615。size参数指定最大显示宽度（即255）
FLOAT(size, d)	一个浮点数。 size 中指定了总位数。小数点后的位数在 d 参数中指定。此语法在 MySQL 8.0.17 中已弃用，并将在未来的 MySQL 版本中删除
FLOAT(p)	一个浮点数。 MySQL 使用 p 值来确定是使用 FLOAT 还是 DOUBLE 作为结果数据类型。如果 p 是从 0 到 24，则数据类型变为 FLOAT()。如果 p 是从 25 到 53，则数据类型变为 DOUBLE()
DOUBLE(size, d)	一个正常大小的浮点数。 size 中指定了总位数。 d参数中指定小数点后的位数
双精度(size, d)
DECIMAL(size, d)	一个精确的定点数。 size 中指定了总位数。小数点后的位数在 d 参数中指定。 size 的最大数为 65。d 的最大数为 30。size 的默认值为 10。< em>d 为 0。
DEC(size, d)	等于 DECIMAL(size,d)

字符串数据类型

数据类型	描述
CHAR(size)	一个固定长度的字符串（可以包含字母、数字和特殊字符）。 size 参数指定以字符为单位的列长度 - 可以从 0 到 255。默认为 1
VARCHAR(size)	可变长度字符串（可以包含字母、数字和特殊字符）。 size 参数指定字符的最大列长度 - 可以从 0 到 65535
BINARY(size)	等于 CHAR()，但存储二进制字节字符串。 size 参数以字节为单位指定列长度。默认为 1
VARBINARY(size)	等于 VARCHAR()，但存储二进制字节字符串。 size 参数指定最大列长度（以字节为单位）。
TINYBLOB	对于 BLOB（二进制大对象）。最大长度：255 字节
TINYTEXT	保存一个最大长度为 255 个字符的字符串
TEXT(size)	保存一个最大长度为 65,535 字节的字符串
BLOB(size)	对于 BLOB（二进制大对象）。最多可容纳 65,535 字节的数据
MEDIUMTEXT	保存最大长度为 16,777,215 个字符的字符串
MEDIUMBLOB	对于 BLOB（二进制大对象）。最多可容纳 16,777,215 字节的数据
LONGTEXT	保存最大长度为 4,294,967,295 个字符的字符串
LONGBLOB	对于 BLOB（二进制大对象）。最多可容纳 4,294,967,295 字节的数据
ENUM(val1, val2, val3, …)	只能有一个值的字符串对象，从可能值列表中选择。您最多可以在一个 ENUM 列表中列出 65535 个值。如果插入的值不在列表中，则将插入一个空白值。这些值按您输入的顺序排序
SET(val1, val2, val3, …)	可以有 0 个或多个值的字符串对象，从可能的值列表中选择。一个 SET 列表中最多可以列出 64 个值

日期和时间数据类型

数据类型	描述
DATE	日期。格式：YYYY-MM-DD。支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’
DATETIME(fsp)	日期和时间组合。格式：YYYY-MM-DD hh:mm:ss。支持的范围是从"1000-01-01 00:00:00"到"9999-12-31 23:59:59"。在列定义中添加 DEFAULT 和 ON UPDATE 以获得自动初始化并更新到当前日期和时间
TIMESTAMP(fsp)	时间戳。 TIMESTAMP 值存储为自 Unix 纪元 (‘1970-01-01 00:00:00’ UTC) 以来的秒数。格式：YYYY-MM-DD hh:mm:ss。支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC。可以使用列定义中的 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 指定自动初始化和更新到当前日期和时间
TIME(fsp)	一次。格式：hh:mm:ss。支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
YEAR	四位数格式的年份。允许采用四位数格式的值：1901 到 2155 和 0000。 MySQL 8.0 不支持两位数格式的年份。

字符串和日期时间数据需要包含在引号之内

DDL

对列的操作

创建数据库

# 查询所有数据库
show databases;
# 查询当前数据库
show database();

# 使用某个数据库
use {数据库名称};

# 创建数据库
create database [if not exists] {数据库名称};

# 删除数据库
drop database [if exists] {数据库名称};

# database(s) 关键字可以替换为 schema(s)

约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据。目的是保证数据库中数据的正确性、有效性、完整性。

约束	描述	关键字
非空约束	限制该字段不能为null	not null
唯一约束	保证字段的所有数据都是唯一、不重复的	unique
主键约束	主键是一行数据的唯一标识，要求唯一且非空	primary key （auto_increment 自增默认从1开始)
默认约束	保存数据时，如果未指定该字段，则采用默认值	default
外键约束	让两张表的数据建立连接，保证数据的一致性和完整性	foregin key

创建表

create table [if not exists] {表名}(
    {字段1} {字段类型} [约束] [comment '注释'],
    {字段2} {字段类型} [约束] [comment '注释'],
    # ....
    {字段n} {字段类型} [约束] [comment '注释']
) [comment '注释'];

# 示例
create table tb_user(
    id int unsigned primary key comment 'id',
    username varchar(20) not null unique comment '用户名称',
    name 9rchar(10) not null comment '姓名',
    age int comment '年龄',
    gender char(1) default '男' comment '性别'
) comment 'user table';

删除表

删除表后，其中的数据也会被全部删除！

drop talbe [if exists] {表名};

查询表

# 查询当前数据库所有表
show tables;
# 查询表结构
desc {表名};
# 查询建表语句
show create table {表名};

修改表

alter — 改变

# 添加字段
alter talbe {表名} add {字段名} {类型(长度)} [comment '注释'] [约束];
# 修改列数据类型
alter table {表名} modify {字段名} {新数据类型(长度)};
# 修改列名和数据类型
alter table {表名} change {旧字段名} {新字段名} {类型(长度)} [comment '注释'] [约束];
# 删除字段
alter table {表名} drop column {字段名};
# 修改表名
rename table {表名} to {新表名};

DQL

SELECT

选择列

SELECT column1, columnl2, ...
FROM table_name;
# 选择全部列使用 *
SELECT * FROM table_name

使用DISTINCT进行去重

SELECT DISTINCT column1 FROM table_name; # 输出取出重复的值

SELECT columns1, columns2, ...
FROM talbe_name
ORDER BY columnsA, columnsB, ... ASC|DESC
# ASC为升序排列，DESC为降序排列，默认为ASC，即升序排序
# 示例
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
# 按照 Country升序排列，CustomerName降序排列

DML

INSERT INTO

在表中插入新的记录(插入一行)

INSERT INTO table_name (columns1, columns2, columns3, ...)
VALUES (value1, value2, value3, ...); # 插入一行，对应列插入对应值
# 批量操作
INSERT INTO table_name (columns1, columns2, columns3, ...)
VALUES (value1, value2, value3, ...), (value1, value2, value3, ...) ...;
# 如果每一列都插入元素，可以忽略列名称的列名，直接传入value，但是需要和列的顺序对应
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

UPDATE

update用于修改表中已有记录

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
# 注意：一定要处理好condition，如果忽略WHERE将导致整张表更新
# 例
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'
WHERE CustomerID = 1;

DELETE

delete语句用于删除表中已有记录，也就是删除一行或几行

DELETE FROM table_name WHERE condition;
# 注意！如果没有WHERE condition,那么整张表将会被删除
DELETE FROM table_name; # 删除整张表

Other

WHERE

用于提取满足指定条件的记录

SELECT column1, column2, ...
FROM table_name
WHERE condition;

condition 运算符

# = 等于
# > 大于
# < 小于
# >=
# <=
# <> / != 不相等
# BETWEEN 一定范围内
# LIKE 搜索模式
# IN 为一列指定多个可能得值
# IS NULL 判断是否是NULL
# IS NOT NULL 判断是否不是NULL

AND OR NOT

NOT表示取反，AND和OR和编程语言中使用方法一致

ORDER BY

对指定列中的元素进行排序

SELECT columns1, columns2, ...
FROM talbe_name
ORDER BY columnsA, columnsB, ... ASC|DESC
# ASC为升序排列，DESC为降序排列，默认为ASC，即升序排序
# 示例
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
# 按照 Country升序排列，CustomerName降序排列

LIMIT

分页查询

LIMIT子句用于指定要返回的记录数，这在大型表中很有用，返回大量记录

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT {起始索引，查询记录数}; # 索引从0开始; 起始索引 = (页码-1) / 每页展示的记录数

需要注意的是，起始索引默认为0，可以忽略不写。

聚合函数

将一列数据作为一个整体，进行纵向计算，但是聚合函数不对null值进行计算，也就是忽略null

MAX() 和 MIN()

MAX()函数返回所选列的最大值，MIN()返回所选列的最小值。

# 选取表table_name中column_name列中满足condition条件的最小值
SELECT MIN(column_name)
FROM table_name
WHERE condition;

COUNT() AVG() 和 SUM()

COUNT()返回符合指定条件的行数；AVG()返回数值列的平均值；SUM()返回数值列的总和

三者使用均需要配合SELECT 和 GROUP BY，使用公式如下

SELECT COUNT(column_name), column_name
FROM table_name
WHERE condition
GROUP BY column_name;

SELECT AVG(column_name), column_name
FROM table_name
WHERE condition # NULL值不在计算范围内
GROUP BY column_name;

SELECT SUM(column_name), column_name
FROM table_name
WHERE condition # NULL值被忽略
GROUP BY column_name;

在统计数量时推荐使用count(*)，因为MySQL对count(*)专门做了优化

LIKE

LIKE运算符在WHERE子句中用于搜索列中的指定模式

有两个通配符经常和LIKE一起使用：

百分号(%)表示零个、一个或者多个字符
下划线(_)代表一个字符

二者也可配合使用，使用规则类似正则表达式

语法

SELECT column1, column2, ...
FROM table_name
WHERE colnumN LIKE pattern;
# 提示：还可以使用AND来组合条件

LIKE 运算符	描述
WHERE CustomerName LIKE ‘a%’	查找以"a"开头的任何值
WHERE CustomerName LIKE ‘%a’	查找以"a"结尾的任何值
WHERE CustomerName LIKE ‘%or%’	查找在任何位置有"或"的任何值
WHERE CustomerName LIKE ‘_r%’	查找第二个位置有"r"的任何值
WHERE CustomerName LIKE ‘a_%’	查找以"a"开头且长度至少为 2 个字符的任何值
WHERE CustomerName LIKE ‘a__%’	查找以"a"开头且长度至少为 3 个字符的任何值
WHERE ContactName LIKE ‘a%o’	查找以"a"开头并以"o"结尾的任何值

# 选择 Customers表中列CustomerName中以"a"开头的所有客户：
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

IN/NOT IN

IN运算符允许在WHERE子句中指定多个值。IN运算符本质是多个OR条件的简写。

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT column_name(s)
FROM table_name
WHERE column_name NOT IN (value1, value2, ...);

最后括号中的value列表可以使用SELECT语句代替

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

SELECT column_name(s)
FROM table_name
WHERE column_name NOT IN (SELECT STATEMENT);

例子

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);
# 如果 SELECT Country FROM Suppliers; 结果是('Germany', 'France', 'UK')，则这两个SQL语句等价

BETWEEN AND

选择给定范围的值，这些值可以是数字、文本或者日期。范围是左闭右闭的也就是[left, right]。

SELECT column_name(s)
FROM table_name
WHERE colmun_name BETWEEN left AND right;

AS

别名用于为表或者表中的列提供临时名称，别名通常用于使列名更具可读性，仅在查询期间有效。

注意 AS关键字可以省略

SELECT column_name AS alias_name
FROM table_name; # 为列取别名

SELECT column_name(s)
FROM table_name AS alias_name; # 为表取别名

**注意！**如果别名包含空格，则别名需要使用单引号或者双引号包裹起来

使用案例

# 表Customers中为两列取别名
SELECT CustomerName AS Customer, ContactName AS "Contact Person"
FROM Customers;

# 为多个列统一起来，然后取别名
SELECT CostomerName, CONCAT_AS(',', Address, PostalCode, City, Country) AS Address
FROM Customers; 
# 这里将 Address, PostalCode, City, Country统一取别名为Address
# CONCAT_AS函数将多个结果合并成一列

# 为表取别名，简化询查语句书写
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o # 取别名 Customers-->c,Orders-->o
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;

JOIN ON

JOIN…ON…子句用于根据它们之间的相关列组合来自两个或者多个表的行。也就是表的联接。

MySQL 支持的联接类型

INNER JOIN：返回两个表中值匹配的记录
LEFT JOIN：返回左表所有记录，右表匹配记录
RIGHT JOIN：返回右表的所有记录，以及左表的匹配记录
CROSS JOIN：返回两个表中的所有记录

INNER JOIN

INNER JOIN选择在两个表中具有匹配值的记录

语法

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

举例

#现在有两个表Orders和Customers
#Orders : CustomerID,OrderDate
#**Customers : CustomerID,CustomerName,ContactName,Country
#现在将Orders中CustomerID,OrderData和Customers中的CustomerName联接起来，要求二者CustomerID相同。
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

联接三个表

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

具体的规则是FROM后面跟着的那张表A确定后，后面的INNER JOIN跟着的条件中就必须出现表A

LEFT JOIN

LEFT JOIN返回左表table1中的所有记录以及右表table2中的匹配记录

语法

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

举例

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName; # 最后排序

RIGHT JOIN

RIGHT JOIN返回右表table2中的所有记录以及左表table1中的匹配记录

语法

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

CROSS JOIN

CROSS JOIN返回两个表的所有记录，一种排列组合

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

注意，CROSS JOIN + WHERE可以达到INNER JOIN的效果。

自联接

就是将自己表中的某些列组合起来，可以理解为对表自身的切割。

语法

SELECT column_name(s)
FROM tableA T1, tableA T2
WHERE condition;
# 注意 T1和T2是同一张表的不同别名，可以直接使用

示例

SELECT A.CustomerName, B.CustomerName, A.City
FROM Customers A, Customers B # 同一张表的两个别名
WHERE A.CustomerID <> B.CustomerID # 不等于
AND A.City = B.City
ORDER BY A.City;

UNION

UNION运算符用于组合两个或者多个SELECT语句的结果集(将结果归到一张表中，增加表的行数，但不增加列数)

UNION中的每个SELECT语句必须具有相同的列数
这些列还必须具有相似的数据类型
每个SELECT语句中的列也必须是相同的顺序

语法

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

例如，有两张表，他们有相同的列，我们将这一个列取出来然后合并

# 将City列合并
SELECT City FROM Customers
UNION # UNION会去重
SELECT City FROM Suppliers
ORDER BY City;

SELECT City FROM Customers
UNION ALL# UNION ALL不会去重
SELECT City FROM Suppliers
ORDER BY City;

GROUP BY

GROUP BY语句将具有相同值的行进行汇总，例如”查找每个地区的客户数量“；

GROUP BY语句通常与聚合函数{count(),max(),sum(),avg()}配合使用按一列或者多列对结果集进行分组

使用GROUP BY column_name语句时，SELECT选择的列只有 column_name和聚合函数(column_name)才有意义。

语法

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
[ORDER BY column_name(s)];

使用实例

# 求每个地区的客户数量
# 这里select Country是为了和前面的count一一对应，否则数据将没有意义(?)
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC; # 降序排列

HAVING

HAVING的语义和WHERE很像，都是通过条件对行进行过滤。二者的差别是：

WHERE子句在聚合函数之前应用，并且它不能直接引用聚合函数的结果。它只能引用表中的列或常量值来过滤行。
HAVING子句在聚合函数之后应用，并且允许引用聚合函数的结果进行过滤。

GROUP BY子句之后也不允许使用WHERE，因此，HAVING一般会配合GROUP BY使用(应为GROUP BY常配合聚合函数使用)。

where 和 having 的区别

执行时机不同：where是分组之前进行过滤，不满足where条件不参与后面的分组；而having是分组之后对结果进行过滤；
判断条件不同：where不能对聚合函数进行判断，having可以。

语法

SELECT column_name(s)
FROM table_name
WHERE conditionA
GROUP BY column_name(s)
HAVING conditonB
ORDER BY colnum_name(s);

示例

# 列出每个地区的客户数量，要求客户数量需要大于5，从高到低排序
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

EXISTS

EXISTS运算符用于测试查询中是否存在任何记录。EXISTS更准确来说是一个谓词，其后面的SELECT子询查句中只要返回了至少一条记录，就会返回TRUE，否则返回FALSE。

语法

SELECT column_name(s)
FROM table_nameA
WHERE EXISTS
(SELECT column_name FROM table_nameB WHERE condition);

可以这么理解，SELECT FROM 语句是一行一行选取的，而WHERE通过后面的结果(true/false)来决定是否选择这一行，因此EXISTS作为谓词返回(true/false)就可以实现选取行的一种策略。

示例

# 返回 TRUE 并列出产品价格低于 20 的供应商
SELECT SupplierName FROm Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

ANY 和 ALL

operator 取值为 (=, <>, !=, >, >=, <, or <=)

与SELECT、WHERE和HAVING语句一起使用，作为一种选择判断条件

ANY运算符：

返回一个布尔值作为结果
如果任何子询查满足条件，则返回TRUE

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name
 FROM table_name
 WHERE condition);

示例

# 如果发现 OrderDetails 表中的任何记录的 Quantity 等于 10，则以下 SQL 语句列出 ProductName
SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
 FROM OrderDetails
 WHERE Quantity = 10);

ALL运算符：

返回一个布尔值作为结果
如果所有子询查都满足条件，则返回TRUE

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
 FROM table_name
 WHERE condition);

示例

# 如果 OrderDetails 表中的所有记录的 Quantity 等于 10，则以下 SQL 语句列出 ProductName。
SELECT ProductName
FROM Products
WHERE ProductID = ALL
(SELECT ProductID
 FROM OrderDetails
 WHERE Quantity = 10);

INSERT INTO SELECT

该语句用来从一个表中复制数据并将其插入到另一个表中，语句要求源表和目标表中的数据类型匹配。未填充的数据，使用NULL。

# 将table1中的所有列复制到table2
INSERT INTO table2
SELECT * FROM table1
WHERE condition;

# 仅将table1中的一些列复制到table2中
INSERT INTO table2 (column1, column2, ...)
SELECT column1, column2, ...
FROM table1
WHERE condition;

CONCAT()

用于拼接字符串，在MyBatis中模糊搜索有用

concat("hello", ", My", "SQL"); # hello, MySQL
concat("%", "name", "%"); # %name% 用于模糊搜索名字

流程控制函数

IF

if({expression}, {true时的取值}, {false的取值})

case

case {expression} when {valA} then {retA} when {valB} then {retB} .... else {defultVal} end

多表操作

外键约束

物理外键

使用foregin key定义外键关联另外一张表

缺点：

影响增删改的效率(需要检查外键关系)
仅用于单节点数据库，不适用于分布式，集群场景
容易引发数据库死锁问题

开发很少用或者几乎不用

逻辑外键

通过应用程序代码维护逻辑对应关系

开发中使用较多

内连接

隐式内连接

使用where作为判断方式

select {字段列表} from {table1, table2} where condition ...;

显示内连接

select {字段列表} from table1 [inner] join table2 on condition ...;

外连接

详细信息参考left join 和 right join

子询查

概述

SQL语句中嵌套使用select语句，称为嵌套询查，又称子询查
形式：select * from t1 where column = (select column from t2, ...);
子询查语句可以是insert/update/delete/select中的任何一个

子查询优化思路

使用in代替多个并列的or语句

下面连个sql语句等价，但是使用in会看的更加清晰

select tb_emp.name
from tb_emp
where tb_emp.dept_id = (select tb_dept.id from tb_dept where tb_dept.name = '教研部')
   or tb_emp.dept_id = (select tb_dept.id from tb_dept where tb_dept.name = '咨询部');

select tb_emp.name
from tb_emp
where tb_emp.dept_id in (select tb_dept.id from tb_dept where tb_dept.name = '教研部' or tb_dept.name = '咨询部');

使用(c1,c2,…)代替多个and

下面连个sql语句等价，但是使用(c1,c2,…)会看的更加清晰

# 多次子查询，效果差
select *
from tb_emp
where tb_emp.entry_date = (select tb_emp.entry_date from tb_emp where tb_emp.name = '韦一笑')
  and tb_emp.job = (select tb_emp.job from tb_emp where tb_emp.name = '韦一笑');

select *
from tb_emp
where (tb_emp.entry_date, tb_emp.job) = (select tb_emp.entry_date, tb_emp.job
                                         from tb_emp where tb_emp.name = '韦一笑');

同时查询两张表并且将其中一部分数据取出来并组合在一起时，可以考虑将查第一张表生成一个临时表然后使用临时表和第二张表连接后询查

第一条语句是直接连接，第二条是生成子表后连接

select tb_emp.*, tb_dept.name from tb_emp, tb_dept
where tb_emp.entry_date > '2006-01-01' tb_emp.dept_id = tb_dept.id;

select t.*, tb_dept.name
from (select * from tb_emp where entry_date > '2006-01-01') t, tb_dept
where t.dept_id = tb_dept.id;

子查询会多次查询，尽量使用连接查询

事务

概念

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

事务控制

# 开启事务
start transaction;
# 或者是
begin;

# 提交事务
commit;

# 回滚事务
rollback;

四大特性(ACID)

原子性，一致性，隔离性，持久性

原子性（Atomicity）：原子性是指事务中包含的所有操作要么全部成功，要么全部失败，不会出现部分成功的情况。如果事务中的某一操作失败，则整个事务会回滚到最初的状态，即撤销所有已经执行的操作。
一致性（Consistency）：一致性确保事务将数据库从一个一致的状态转换到另一个一致的状态。在事务开始之前和完成之后，数据库的完整性约束都得到满足。例如，在转账操作中，无论过程如何，两个账户的余额之和应该保持不变。
隔离性（Isolation）：隔离性是指一个事务在提交之前，对其他事务是不可见的。这可以防止多个事务同时执行时产生的数据混乱。隔离性的关键在于避免脏读、不可重复读和幻读等问题，确保每个事务看到的数据都是一致且未被其他事务干扰的。
持久性（Durability）：一旦事务被提交，其结果就是永久性的，即使系统发生故障也不会丢失数据。持久性通常通过将提交的事务记录到磁盘上的日志文件中来实现，即使数据库系统崩溃，也可以通过日志来恢复已提交的事务。

并发事务引发的问题

问题	描述
脏读	一个事务读到另一个事务还没有提交的数据
不可重复读	一个事务先后读取同一条记录，但是两次读取的数据不同
幻读	一个事务按照条件查询数据，没有对应的数据行，但是在插入时，发现数据又存在了，好像“幻影”

事务的隔离级别

不同隔离级别会发生的问题：

隔离级别	脏读	不可重复读	幻读
Read uncommitted	T	T	T
Read committed	F	T	T
Repeatable Read（MySQL默认）	F	F	T
Serializable（串行化）	F	F	F

从上到下，事务隔离级别不断增长。事务隔离级别越高，事务越安全，性能越低；反之亦然。

存储引擎

MySQL体系结构

连接层

客户端和链接服务。链接处理，授权认证，安全方案
服务层

完成大多数核心服务功能，例如SQL接口，SQL的分析和优化，部分内置函数的执行。跨存储引擎的功能也在这一层实现
引擎层

存储引擎真正负责MySQL数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，我们可以根据自己的需要，来选取合适的存储引擎
存储层

将数据存储在文件系统之上，并完成与存储引擎的交互

存储引擎

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

在创建表的时候，指定存储引擎，默认使用InnoDB

create table tableName(
	....
) engine = innodb [comment 注释];

查看支持的存储引擎

show engines;

InnoDB

innoDB是一种兼顾高可靠性和高性能的通用存储引擎。

特点：

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

文件：

xxx.ibd，xxx代表名，innoDB引擎每一个表都会对应这样一个表空间文件，存储该表的的表结构(frm, sdi)、数据和索引。

参数：innodb_file_per_table 用于控制每个表对应一个文件还是多个表共用一个文件，默认开启

MyISAM

特点：

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

Memory

Memory引擎的表数据时存放在内存中，由于受到硬件问题、或者断点问题的影响，只能将这些表作为临时表或缓存使用。

特点：

内存存放
hash索引(默认)

存储引擎选择/小结

InnoDB

如果应用对事务的完整性有比较高的要求，在并发条件下要求数据一致性，数据操作除了插入和查询之外，还包含很多更新、删除操作。适合存储核心数据。
MyISAM

以读操作和插入操作为主，只有很少的更新和删除操作，并且对事物的完整性、并发性要求不是很高。被MongoDB代替
Memory

内存，不如Redis?

索引

优化查询的一种方法，是帮助数据库高效获取数据的数据结构。

优点&缺点

优点

提高数据查询效率，降低数据库的IO成本
通过索引对数据进行排序，降低数据的排序成本，降低CPU消耗

缺点

索引会占用存储空间
索引大大提高了查询效率，同时也降低了insert,update,delete的效率

底层数据结构

默认B+树(多路平衡搜索树)

创建索引

create [unique|fulltext] index index_name on table_name(column_name[, ...]);

展示索引

show index from table_name;

删除索引

drop index index_name on talbe_name;

索引结构

B+Tree：最常见，大部分引擎支持
Hash：只有精确匹配才有效，不支持范围询查
R-Tree(空间索引)：MyISAM引擎的特殊索引类型，主要用于空间地理数据类型
Full-text(全文索引)：一种通过建立倒排索引，快速匹配文档的方式

B树和B+树的区别

https://blog.youkuaiyun.com/a519640026/article/details/106940115

B+数的所有数据都只存放在叶子节点之中，叶子存放所有索引
B+数的叶子节点会形成一个单向链表

MySQL对B+树进行了改造，叶子节点形成了双向循环链表

为什么InnoDB存储引擎选取B+树作为索引结构

相对于二叉树(红黑树)，层级更少，搜索效率高
对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页(固定大小16K)存储的键值减少，所以存储的指针减少，同样保存大量的数据就会增加树的高度，导致性能降低。
Hash索引只支持等值匹配，不支持范围匹配

索引分类

主键索引，唯一索引，常规索引，全文索引

在InnoDB存储引擎中，根据索引的存储形式，索引又可以分为：

聚集索引

将数据存储于索引放在一起，索引结构的叶子节点保存了这一行的数据，必须有，且只有一个
二级索引

数据域索引分开存储，索引结构的叶子节点关联的是对应的主键，可以有多个

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引
如果不存在主键，那么第一个唯一索引就是聚集索引
如果两个都不存在，InnoDB会自动生成一个rowid作为隐藏的聚集索引

如果我们通过二级索引进行查询，则先通过二级索引查询出主键索引，然后通过主键索引查询出对应行的数据

SQL性能分析

SQL执行频率

目的是确定哪一类SQL语句执行评率高

show [global|session] status # 该命令用于询查命令状态 当前会话|全局
# 例如
show global status like 'Com_______'; # 七个下划线
# 输出
# Com_binlog,0
# Com_commit,0
# Com_delete,0
# Com_import,0
# Com_insert,0
# Com_repair,0
# Com_revoke,0
# Com_select,49
# Com_signal,0
# Com_update,0
# Com_xa_end,0

慢询查日志

定位到具体哪些SQL语句执行效率比较低。慢询查日志记录了所有执行时间超过指定参数（long_quary time, 默认10秒）的所有SQL语句的日志。MySQL慢查询日志默认没有开启，登录mysql后手动开启：
```
set global slow_query_log=ON;
set long_query_time=xx
```
日志位于/var/lib/mysql/主机名-slow.log中。

profile详情

select @@have_profiling;
set global profiling = 1; # 打开开关

打开之后可以通过以下指令来查看SQL执行耗时

# 查看每一条SQL的耗时，这里会显示query_id
show profiles;

# 查看指定query_id的SQL语句各阶段耗时情况
show profile for query query_id;

# 查看指定query_id的SQL语句的CPU使用情况
show profile cpu for query query_id;

explain执行计划

explain或者desc命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
```
explain select 字段列表 from 表名 where condition;
```
各字段含义：
- id
  
  select询查的序列号，表示查询中执行select子句或者是表操作的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。
- select_type
  
  表示SELECT的类型，常见的取值有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY (SELECT、WHERE之后包含了子查询等)
- type
  
  表示连接的类型，性能由好到差依次为NULL、system、const、eq_ref、ref、range、index、all。
- possible_key
  
  显示可能应用在这张表上的索引，一个或者多个。

索引使用规则

最左前缀法则

如过索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则是指查询从最左列的索引开始，并且不跳过索引中的列。如果跳过某一列，索引将部分失效(后面的字段索引失效)。

范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。但是>=或者<=不会使索引失效。

索引失效情况

索引列运算

不要在索引列上进行运算操作，否则索引将失效。
字符串不加引号

字符串类型字段使用时不加引号，索引将失效
模糊查询

如果仅仅只是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引将失效
or连接的条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那涉及到的索引都不会被使用。也就是说or前后必须全部都是索引，查询时索引才会生效。
数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

SQL提示

SQL提示是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。例如告诉数据库该用哪个索引

select * from tableName use index(xxx) where condition; # 建议使用什么索引

select * from tableName ignore index(xxx) where condition; # 忽略什么索引

select * from tableName force index(xxx) where condition; # 必须使用什么索引

覆盖索引

尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能找到)，减少使用SELECT *。

using index condition : 查找使用了索引，但是需要回表查询数据

using where; using index : 查找使用了索引，但是需要的数据都能在索引列中找到，不需要回表。

前缀索引

当字段类型为字符串(varchar,text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。

create index idx_name on tableName (columnName(n)); # n代表取前n个字符来构建索引

前缀长度的选择：

可以根据索引的选择性来决定，选择性是指不重复的索引值(基数)和数据表的记录总数比值，索引选择性越高则查询效率越高，唯一索引的选择性为1(最高)。

单列索引和联合索引

主要的变数在于是否会回表查询，如果使用单列索引查询多列数据，必然会回表查询，但是如果是联合索引查询索引中的字段，那么就不会回表查询。

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引而非单列索引。

收到最左前缀法则的影响，在创建联合索引的时候要考虑各个字段的顺序。

索引设计原则

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

SQL优化

插入优化

insert
- 推荐使用批量插入，一次建议插入500~1000条数据，如果数据太大，建议拆分为多条批量插入。
- 手动提交事务，减少事务提交次数。
- 主键顺序插入

load

插入大批量数据，直接使用load指令从文件中插入，性能提升明显

使用方式

# 客户端连接服务端时加上参数 --local-infile
mysql --local-infile -u root -p
# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local-infile = 1;
# 执行load指令将准备好的数据，加载到表结构中
load data local infile '/path/of/file/sql.log' into table 'tableName' fields terminated by '分隔符' lines terminated by '每行结束标志';

load指令插入时，如果是主键顺序插入，那么也会有性能优化。

主键优化

这里建议查看视频，解释了为什么主键顺序插入效率较高

数据组织方式

在InnoDB引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。

页分裂

页可以为空，也可以填充一半，也可以填充100%(搜索MySQL的逻辑存储结构，表空间->段->区->页->行)。每个页包含了2~N行数据(如果一行数据过大，会行溢出)，根据主键顺序排列。

页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除并且它占有的空间被允许其他记录声明使用。

当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%)，InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。

主键设计原则

在满足业务需求的情况下，尽量降低主键的长度。因为二级索引会存储主键(聚集索引)。
插入数据时，尽量顺着顺序插入，选择使用auto_increment自增主键，减少页分裂操作的发生。
尽量不要使用UUID作为主键或者其他自然主键(如身份证号码)。因为这些长度长而且无序。
业务操作时，尽量避免对主键的修改。修改主键会导致其他索引建构重新建立，会有开销。

order by 优化

两种排序方式

Using filesort：通过表的索引或者全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都称之为filesort排序。
Using index：通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高。

创建索引时指定排序方式

默认采用升序建立索引，可以指定为降序

create index indexName on tableName (columnName, asc/desc); # 指定排序顺序

查询时如果使用降序排列，可以考虑建立降序索引来优化查询。

注意如果不可避免的出现filesort，大数据量排序时考虑增大排序缓冲区的大小 sort_buffer_size，默认为256KB。如果超出缓冲区会在磁盘中排序，性能较低。

group by 优化

通过索引优化，因为如果建立了索引，相同的分组内容是连续的，加快查询效率。也可以避免建立临时表。

limit 优化

limit可以选择从哪一个位置开始，建立覆盖索引可以快速定位到该位置，从而实现查询优化。

一般分页查询时，通过覆盖索引+子询查的形式进行优化。

count 优化

InnoDB在进行count时只会一条一条读取，优化策略是自己计数。

count的几种用法

count(主键)

InnoDB引擎会遍历整张表，把每一行的主键值取出来，返回给服务层。服务层拿到主键后，直接进行累加(因为主键不可能为NULL)。
count(字段)

和主键一样，服务层获取，但是如果该行该字段为NULL，则不参与计数。
count(常数)

InnoDB引擎遍历整张表，但不取值。服务处对于返回的每一行，放一个数字1，再进行累加。
count(*)

InnoDB引擎不会遍历表，而是做了专门的优化，不取值，服务层直接进行累加。

效率字段 < 主键id < 常数 = *，建议使用count(*)

update 优化

避免行锁升级为表锁，建议使用索引作为判断条件。

使用索引时，才会使用行锁。如果判断条件不是索引，会锁住整张表。导致并发操作效率下降。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

视图 View

概念及语法

视图(view)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列的数据来自定义视图的询查中使用的表，并且是在使用视图中动态生成的。

通俗的说，视图只保存了询查的SQL逻辑，不保存询查的结果。

# 创建视图
create [or replace] view 视图名称[(列表名称)] as select语句 [with [cascaded | LOCAL] check option];

这里select查询出来的表就是视图的基表。

# 查询视图创建的语句
show create view 视图名称;
# 查看视图数据
select * from 视图名称 where condition;

# 修改视图
# 方式一 重点在 or replace
create [or replace] view 视图名称[(列表名称)] as select语句 [with [cascaded | LOCAL] check option];
# 方式二
alter view 视图名称[(列表名称)] as select语句 [with [cascaded | local] check option];

# 删除
drop view [if exists] 视图名称, [视图名称]...;

视图检查选项

当使用with check option子句创建视图时，MySQL会通过视图检查正在更改的每一行，列入插入、更新、删除，使其符合视图的定义。MySQL允许基于一个视图创建另一个视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，MySQL提供了两个选项：cascaded和local，默认为cascaded。

cascaded会检查依赖的视图的所有限制，级联模式，上面所有限制都会被使用

local，递归检查，递归模式，不加with check option的限制不会起作用

视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一关系。如果视图包含一下任何一项，则视图不可更新：

聚合函数或者窗口(SUM,MIN,MAX,COUNT)等
DISTINCT
GROUP BY
HAVING
UNION或者UNION ALL

作用

简单

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些经常被使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
安全

数据库可以授权，但不能授权到数据库特定行和特定列上。通过视图用户只能查询和修改他们所能看见的资源。
数据独立

视图可帮助用户屏蔽真实表结构带来的变化

存储过程

概念和语法

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据库和应用服务器之间的传输，对提高数据传输的效率是友好的。

存储过程的思想很简单，就是数据库SQL层面的代码封装与重用。

特点：1. 封装，复用；2. 可以接收参数，也可以返回数据；3. 减少网络交互，提高效率。

注意，存储过程后面是有个括号的。

# 创建存储过程
create procedure 存储过程名称([参数列表])
begin
	SQL语句
end;

# 调用存储过程
call 名称 ([参数]);

# 查看存储过程
# 查询指定数据库所有存储过程以及状态信息 xxx 表示database名称
select * from information_schema.routines where routine_schema = 'xxx';
# 查询创建存储过程的SQL语句
show create procedure 存储过程名称;

# 删除
drop procedure [if exists] 存储过程名称;

注意，在命令行中直接使用上述语法会报错，因为多个分号冲突了，此时需要通过delimiter XX重新设置SQL语句结束标志。

变量

该部分没有学习完，后面再补吧

系统变量

系统变量是MySQL服务器提供的，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)和会话变量(SESSION)。

# 查看系统变量
show [session|global] variables # 查看所有系统变量 默认session
show [session|global] variables like '...' # 通过like模糊匹配查找
select @@[session|global] 系统变量名; # 查看指定变量的值

set [session|global] 系统变量名=值;
set @@[session|global.]系统变量名=值;

注意，MySQL服务重启之后，所有设置的全局参数会失效

用户变量

触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。触发器有点像构造/析构函数的行为。

使用别名OLD和NEW来引用触发器中变化的内容记录。目前触发器只支持行级触发，不支持语句级触发，简单来说就是触发次数受到行数的影响。

触发器类型	NEW和OLD
INSERT	NEW表示将要或者已经新增的数据
UPDATE	OLD表示修改前的数据，NEW表示将要或者已经修改的数据
DELETE	OLD表示已经或者将要被删除的数据

语法

# 创建
create trigger triggerName
	before/after insert/update/delete
	on tableName for each row # 行级触发器
begin
	trigger_stml; # 触发器执行的语句，使用new/old获取数据
end;

# 查看触发器
show triggers;

# 删除触发器
drop trigger [secheamName.]triggerName;

锁

全局锁

全局锁就是对整个数据库实例进行加锁，加锁之后数据库处于只读状态，后续的DML的写语句，DDL语句，已经更新的事务提交语句都会阻塞。

典型的使用场景是做全库的逻辑备份，对所有表进行锁定，从而获取一致性视图，保证数据完整性。

# 上锁
flush tables with read lock;
# 解锁
unlock tables;

特点

如果在主库在备份，那么备份期间都不能执行更新，业务处于停摆状态
如果在从库上备份，那么从库备份期间不能同步主库发送的二进制日志，导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数--single-transaction来完成不加锁的一致性数据备份。

表级锁

每次操作锁住整张表。锁定的粒度大，发生锁冲突概率高，并发度低。MyISAM，InnoDB，DBD存储引擎中均实现。

表锁
```
# 加锁
lock tables tableName... read/write;
# 释放锁
unlock tables;
```
1. 表共享读锁(read lock)
  
  加读锁的客户端只允许读，不允许写；其他客户端对锁住的表写入时会阻塞
2. 表独占写锁(write lock)
  
  一个客户端对一张表加上写锁后，自己对该表的操作是可读可写的；但是其他客户端无法读写，执行也会阻塞。
元数据锁(meta data lock, MDL)

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

对一张表进行增删改查的时候，加MDL读/锁(共享)；当对表结构进行更改的时候(alter)，加MDL写锁(排他)。
意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，InnoDB加入了意向锁，使得使用表锁不用检查每行数据是否加锁，使用意向锁来减少锁表的检查。
- 意向共享锁（IS）：与表锁共享锁（read）兼容，与表锁排他锁（write）互斥。
- 意向排他锁（IX）：与表锁共享锁（read）寄排他锁（write）都互斥。意向锁之间不会互斥。

行级锁

每次操作锁住对应的行数据。锁的粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB引擎中。

InnoDB的数据是基于索引组织实现的，行锁是通过对索引上的索引项来加锁的，而不是对记录加锁。

行锁 Record Lock

锁定单个行记录的锁，防止其他事务对此进行update和delete，在RC，RR隔离级别下都支持
间隙锁 Gap Lock

锁的索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行INSERT，产生幻读。在RR隔离级别支持。
临键锁 Next-Key Lock

行锁盒间隙锁的组合，同时锁住数据并锁住数据前面的间隙。在RR隔离级别支持。

行锁

InnoDB实现了一下两种类型的行锁

共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排它锁(X)：允许获得排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁。

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