MySQL从入门到精通超详细版本，看这一篇就够了！

一、初识数据库

1. 数据库服务器、数据库与表之间的关系

•  数据库服务器上运行着数据库软件
• 数据库软件管理不同系统对应的数据库
• 数据库中包含很多张数据表
• 数据表中包含很多数据行

2. SQL简介

2.1 什么是SQL

SQL（Structured Query Language)是结构化查询语言的简称，是一种数据库查询和程序设计语言，用于读取数据，以及查询、更新和管理关系数据库

2.2 SQL分类

• DDL（Data Definition Language）数据定义语言，用来维护存储数据的结构（代表指令：create、drop、alter
• DML（Data Manipulation Language）数据操纵语言，用来对数据进行操作（代表指令：insert、delete、update、select
• DCL（Control）数据控制语言，主要负责权限管理和事务

3.MySQL架构

 二、库的操作

1.查看数据库

1.1 语法

show databases;

• 在MySQL中每条语句以分号结束
• SQL中的字符不区分大小写
• databases是复数形式

2.创建数据库

2.1 语法

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option] ...
create_option: [DEFAULT] {
 CHARACTER SET [=] charset_name
 | COLLATE [=] collation_name
 | ENCRYPTION [=] {'Y' | 'N'}
}

• 大写部分表示关键字
• db_name：表示自定义的数据库名
• {}中表示必须选
• |表示任选一个
• []表示可选项
• ctrl+c可以结束当前的输入，在重新起一行

3.字符集编码和校验规则

3.1 查看数据库支持的字符集编码

show charset;

• MySQL8.0默认的字符集编码是utf8mb4

3.2 数据库支持的排序规则

show collation;

• MySQL默认的排序规则是utf8mb4_0900_ai_ci

4.查看创建库的语句

4.1 语法

show create database db_name

5.修改数据库

5.1 语法

ALTER {DATABASE | SCHEMA} [db_name]
 alter_option ...
alter_option: {
 [DEFAULT] CHARACTER SET [=] charset_name
 | [DEFAULT] COLLATE [=] collation_name
 | [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
 | READ ONLY [=] {DEFAULT | 0 | 1}
}

• 命令行窗口中按上下键可以快速调出之前写的SQL

6.删除数据库

6.1 语法

 DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

6.2 注意事项

• 删除数据库是危险操作，不要随意删除数据库
• 删除数据库后，数据库所对应的目录和目录中的所有文件都会被删除

三、数据类型

1.数据值类型

1.1 类型列表

2.字符串类型

2.1 类型列表

2.2 char与varchar的区别

• char为固定长度的字符串，取值范围比较小

• varchar为可变长度字符串，取值范围较大

• 数据确定长度一样就使用char，如果长度有变化，就使用varchar

• char类型比较浪费磁盘空间，但效率高，varchar则相反

3.日期类型

3.1 类型列表

 3.2 其他

• fsp为可选设置，用来指定小数秒精度，范围从0到6，值为零表示没有小数部分，省略默认精度为零
• CURRENT_DATE 和 CURRENT_DATE() 是 CURDATE() 的同义词用于获取当前日期
• CURRENT_TIME 和 CURRENT_TIME([fsp])是 CURTIME()的同义词用于获取当前时间
• CURRENT_TIMESTAMP和CURRENT_TIMESTAMP([FSP])是NOW()的同义词用于获取当前日期和时间
• 以上均为MySQL的内置函数

四、表操作

1.查看所有表

1.1 语法

show tables;

2.创建表

2.1 语法

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
 field datatype [约束] [comment '注解内容']
 [, field datatype [约束] [comment '注解内容']] ...
) [engine 存储引擎] [character set 字符集] [collate 排序规则];

• 每个列以逗号结束
• 字符串是以单引号包裹
• 最后一列没有逗号
• 表名和关键字冲突时加双引号
• 创建表之后，在磁盘上有对应的文件，innodb存储引擎，一个表对应一个文件，使用myisam存储引擎对应着三个文件

3.查看表结构

3.1 语法

desc 表名;

4.修改表

4.1 语法

ALTER TABLE tbl_name [alter_option [, alter_option] ...];
alter_option: {
 table_options
 | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
 | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
 | DROP [COLUMN] col_name
 | RENAME COLUMN old_col_name TO new_col_name
 | RENAME [TO | AS] new_tbl_name

• 新增一列、修改某一列、删除某一列、重命名某一列、修改表名

5.删除表

5.1 语法

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

五、增删改查操作（CRUD）

1.Create 新增

1.1 语法

INSERT [INTO] table_name
 [(column [, column] ...)]
VALUES
 (value_list) [, (value_list)] ...
value_list: value, [, value] ...

1.2 单行数据全列插入

• value_list中值的数量必须和定义表的列的数量及顺序一致

1.3 单行数据指定列插入

• value_list中值的数量必须和指定列数量及顺序一致

1.4 多行数据指定列插入

• 在一条INSERT语句中也可以指定多个value_list，实现一次插入多行数据
• 每个SQL都会发送一次请求，可以通过多条插入的方式，减少客户端与数据库服务器之间的连接次数

2.Retrieve 检索

2.1 语法

SELECT
 [DISTINCT]
 select_expr [, select_expr] ...
 [FROM table_references]
 [WHERE where_condition]
 [GROUP BY {col_name | expr}, ...]
 [HAVING where_condition]
 [ORDER BY {col_name | expr } [ASC | DESC], ... ]
 [LIMIT {[offset,] row_count | row_count OFFSET offset}]

2.2 全列查询

• select * from 表名；
• 表示在一个表中查所有的记录

2.3 指定列查询

• select 列名[，列名] from 表名；
• 查询的列可以是一个也可以是多个，中间用逗号隔开

2.4 查询字段为表达式

• 表达式本身就是一个常量
• 也可以是常量的运算

2.5 为查询结果指定别名

• SELECT column [AS] alias_name [, ...] FROM table_name；
• as可以省略，别名如果包含空格必须用单引号包裹

2.6 结果去重查询

• 在结果集中去除重复记录，可以使⽤DISTINCT
• 去重时，要求所有列都是相同的，才会判定为重复
• 查询时不加限制条件会返回表中所有结果

2.7 where条件查询

• SELECT
  select_expr [, select_expr] ... [FROM table_references]
  WHERE where_condition
• 比较运算符
• 逻辑运算符
•  模糊查询： %表示0或多个字符，等于没有指定条件，_占位符，代表一个字符
• NULL查询：is null/is not null NULL与任何值的运算结果都为NULL

2.8 Order by 排序

-- ASC 为升序（从⼩到⼤）
-- DESC 为降序（从⼤到⼩）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...] ORDER BY {col_name | expr } [ASC | 
DESC], ... ;

• 要对查询出来的结果进行排序
• NULL视为比任何值都小
• 查询中如果没有DRDER BY子句，返回的顺序就是未定义的

2.9 分页查询

-- 起始下标为 0
-- 从 0 开始，筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT num;
-- 从 start 开始，筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT start, num;
-- 从 start 开始，筛选 num 条结果，⽐第⼆种⽤法更明确，建议使⽤
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT num OFFSET start;

• 分页查询的目的就是为了限制返回结果集的大小，减少服务器的压力
• start的值如果超出表中记录数的范围，最终返回一个空

3.Update 修改

3.1 语法

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
 SET assignment [, assignment] ...
 [WHERE where_condition]
 [ORDER BY ...]
 [LIMIT row_count]

3.2 注意事项

• 以原值的基础上做变更时，不能使用math += 30 这样的语法
• 不加where条件时，会导致全表数据都被更新，谨慎操作

4.Delete 删除

4.1 语法

DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

4.2 注意事项

• 执行Delete时不加条件会删除整张表中的数据
• 一般不进行删除，在一个表中加一列deletestate，这个列的值 0正常 1已删除

5.截断表

5.1 语法

TRUNCATE [TABLE] tbl_name

5.2 注意事项

• 只能对整表操作
• 不对数据操作所以效率较高
• 执行之后表回到最初的状态

6.插入查询结果

6.1 语法 

 INSERT INTO table_name [(column [, column ...])] SELECT ...

7.聚合函数

7.1 常用函数

8.Group by 分组查询

group by 子句的作用是通过一定的规则将一个数据集划分成若干的小的分组，然后针对若干个分组进行数据处理

8.1 语法

SELECT {col_name | expr} ,... ,aggregate_function (aggregate_expr)
 FROM table_references
 GROUP BY {col_name | expr}, ... 
 [HAVING where_condition]

8.2 having子句

使用GROUP BY对结果进行分组处理之后，对分组的结果进行过滤时，不能使用where子句，而是要使用having子句

8.3 having与where的区别

• having用于对分组结果的条件过滤
• where用于对表中真实数据的条件过滤

六、数据库约束

1.概念

数据库约束是指对数据库表中的数据所施加的规则或条件，用于确保数据的准确性和可靠性。这些约束可以是基于数据类型、值范围、唯一性、非空等规则，以确保数据的正确性和相容性

2.约束类型

3.NOT NULL 非空约束

加了非空约束的列不能为NULL值，如果预见某个列不可能为空，则强烈建议加非空约束

4.DEFAULT 默认值约束

DEFAULT约束用于向列中插入默认值，如果没有为列设置值，则将会将默认值设置到该列

5.UNIQUE 唯一约束

指定了唯一约束的列，该列的值在所有记录中不能重复，如果确定当前列是唯一列，则强烈建议加唯一约束

6.PRIMARY KEY 主键约束

• 主键约束唯一标识数据库表中的每条记录
• 主键必须包含唯一的值，而且不能包含NULL值
• 每个表只能有一个主键，可以由单个列或多个列组成
• 通常为每张表都指定一个主键，主键列强烈使用BIGINT类型
• 通常把主键列设置为自动增长，让数据库维护主键值

7.FOREIGN KEY 外键约束

• 外键用于定义主表和从表之间的关系
• 外键约束主定义在从表的列上，主表关联的列必须是主键或唯一约束
• 当定义外键后，要求从表中的外键列数据必须在主表的主键或唯一列存在或为null

8.CHECK 约束

可以应用于一个或多个列，由于限制列中可接受的数据值，从而确保数据的完整性和准确性

七、数据库设计

1.范式

数据库的范式是一组规则，不同的规范要求被称为是不同的范式

关系数据库一共有六种范式，一般只需要满足第三范式即可

1.1 第一范式

1.1.1 定义

• 数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组等
• 在关系型数据库的设计中，满足第一范式是对关系模式的基本要求，不满足第一范式的数据库就不能被称为关系数据库

1.1.2 实例

定义一个学生表，需要记录学生信息和学校信息

1.1.3 反例

学校是一个对象，可以继续进行拆分，所以不满足第一范式

在关系型数据库中，每一列都可以用基本数据类型表示，就天然满足第一范式

1.2 第二范式

1.2.1 定义

•  在满足第一范式的基础上，不存在非关键字段对任意候选键的部分函数依赖
• 存在于表中定义了复合主键的情况下
• 候选键是可以唯一标识一行数据的列或列的组合，可以从候选键中选一个或多个当表的主键

1.2.2 示例

需求：学生可以选修课程，课程有对应的学分，学生考试后会产生相应的成绩

1.2.3 反例

用一张表记录所有的信息，包括学号、姓名、年龄、性别、课程名、学分、成绩

• 表中使用学号+课程名定义复合主键来唯一标识一个学生某门课程的成绩
• 学生的信息只依赖于学号，不依赖课程名；学分只依赖课程名，不依赖学生
• 使用复合主键的表，如果一行数据中的有些列只与复合主键中的一个或其中几个列有关系，那么就是存在部分函数依赖，也就不满足第二范式
• 针对需求应该设计三张表，即：学生表，课程表和成绩表

1.2.4 不满足第二范式时可能出现的问题

• 一个表中除了记录主要的数据之外其他字段重复出现会导致数据冗余
• 可能会导致更新异常
• 插入异常
• 删除异常

1.3 第三范式

1.3.1 定义

在满足第二范式的基础上，不存在非关键字段，对任意候选键的传递依赖

1.3.2 反例

在一个表中出现了两个强相关的关系，而且这两个强相关的关系又存在传递现象，这种传递现象称为传递依赖，应该针对需求设计不同的表

2.设计过程

1. 从现实业务中抽象得到概念类，概念类是从现实世界中抽象出来的，在需求分析阶段就需要确定
2. 确定实体与实体之间的关系，并画出E-R图，方便项目参与人员沟通理解
3. 根据E-R图完成SQL语句的编号并创建数据库

3.实体-关系图

3.1 E-R图的基本组成

• 实体：即数据对象，用矩形框表示，比如用户、学生、班级等
• 属性：实体的特性，用椭圆形或圆角矩形表示，如学生的姓名、年龄等
• 关系：实体之间的联系，用菱形框表示，并标明关系的类型，并用直线将相关实体与关系连接起来

3.2 关系的类型

3.2.1 一对一关系

• 一个用户实体包含的属性有：用户昵称、姓名、手机号等
• 一个账户实体包含的属性有：登录用户名，密码
• 用户实体与账户实体是一对一的关系

3.2.2 一对多关系

• 一个学生实体包含的属性有：姓名，学号，年龄，性别，入学时间
• 一个班级实体包含的属性有：班级名，学生人数
• 一个班级中有多个学生，所有班级实体和学生实体是一对多的关系，反过来也可以认为学生实体与班级实体是多对一的关系

 3.2.3 多对多关系

• 一个学生实体包含的属性有：姓名，学号，年龄，性别，入学时间
• 一个课程实体包含的属性有：课程名
• 一个学生可以选修多门课程，一门课程也可以被多名学生选修，所以学生与课程之间是多对多的关系

八、联合查询

 1.为什么使用联合查询

在数据设计时由于范式的要求，数据被拆分到多个表中，那么要查询一个数据的完整信息，就要从多个表中获取数据，这是就需要使用联合查询，这里的联合指的是多个表的组合

2.多表联合查询时MYSQL内部是如何进行计算的

• 参与查询的所有表取笛卡尔积，结果集在临时表中
• 观察哪些记录是有效数据，根据两个表的关联关系过滤掉无效数据
• 联合查询表的个数越多，表中的数据量越大，临时表就会越大，所以根据实际情况确定联合查询表的个数 

3.内连接

3.1 语法

1 select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件;
2 select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 where 其他条件;

4.外连接

• 外连接分为左外连接、右外连接和全外连接三种，MySQL不支持全外连接
• 左外连接：返回左表的所有记录和右表中匹配的记录，如果右表中没有匹配的记录，则结果集中对应字段会显示为NULL
• 右外连接：与左外连接相反
• 全外连接：返回左右表中的所有记录

4.1 语法

-- 左外连接，表1完全显⽰
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显⽰
select 字段 from 表名1 right join 表名2 on 连接条件;

5.自连接

5.1 应用场景

自连接是自己与自己取笛卡尔积，可以把行转换为列，在查询的时候可以使用where条件对结果过滤，或者说实现行与行之间的比较，在做表连接时为表起不同的别名

# 不为表指定别名
mysql> select * from score, score;
ERROR 1066 (42000): Not unique table/alias: 'score'
# 指定别名
mysql> select * from score s1, score s2;

6.子查询

子查询是把一个SELECT语句的结果当作另一个SELECT语句的条件，也较嵌套查询

6.1 语法

select * from table1 where col_name1 {= | IN} (
 select col_name1 from table2 where col_name2 {= | IN} [(
 select ...)
 ] ...
)

6.2 单行子查询

嵌套的查询中只返回一行数据

6.3 多行子查询

嵌套的查询中返回多行数据，使用[NOT] IN 关键字

6.4 多列子查询

单行子查询和多行子查询都只返回一列数据，多列子查询可以返回多个列的数据，外层查询与嵌套的内层查询的列要匹配

6.5 在form子句中使用子查询

当一个查询结果产生时，MySQL自动创建一个临时表，然后把结果集放在这个临时表中，最终返回给用户，在from子句中也可以使用临时表进行子查询或表连接操作

7. 合并查询

在实际应用中，为了合并多个select操作返回的结果，可以使用集合操作符union，union all

7.1 Union

该操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中的重复行

7.2 Union all

该操作符用于取得两个结果集的并集，使用该操作符时，不会去掉结果集中的重复行

8.插入查询结果

8.1 语法

INSERT INTO table_name [(column [, column ...])] SELECT ..

九、视图

1.什么是视图

视图是一个虚拟的表，它是基于一个或多个基本表或其他视图的查询结果集。视图本身不存储数据，而是通过执行查询来动态生成数据。用户可以像操作普通表一样使用视图进行查询，更新和管理。视图本身并不占用物理储存空间，它仅仅是一个查询的逻辑表示，物理上它依赖于基础表中的数据

2.创建视图

# 语法
CREATE VIEW view_name [(column_list)] AS select_statement

3.使用视图

视图是一张虚拟表，可能通过调用视图方便的获取想要的数据，视图可以理解为把一个复杂的SQL进行了包装，视图可以与真实表进行表关联查询

4.修改数据 

• 通过真实表修改数据，会影响视图，视图中的数据是依赖于基本表的
• 修改视图同样会影响真实表

4.1 注意事项

以下视图不可更新

• 创建视图时使用聚合函数的视图
• 创建视图时使用DISTINCT
• 创建视图时使用GROUP BY 和 HAVING子句
• 创建视图时使用UNION 或 UNION ALL
• 查询列表中使用子查询
• 在FROM子句中引用不可更新视图

5.删除视图

# 语法
drop view view_name;

6.视图的优点

1. 简单性：可以将复杂的查询封装成一个简单的查询
2. 安全性：通过视图可以隐藏表中的敏感数据
3. 逻辑数据独立性：即使底层表结构发生变化，只需修改视图定义，而无需修改依赖视图的应用程序，使用到应用程序与数据库的解耦
4. 重命名列：视图允许用户重命名列名，以增强数据可读性

十、用户和权限管理

1.应用场景

2.用户

 2.1 查看用户

MySQL的用户信息保存在MySQL系统数据库的user表中，可以通过Select语句查看

•  host：允许登陆的主机，相当于白名单，如果是localhost，表示只能从本机登录
• user：用户名
• *_priv：用户拥有的权限
• authentication_string：加密后的用户密码

2.2 创建用户

2.2.1 语法

CREATE USER [IF NOT EXISTS] 'user_name'@'host_name' IDENTIFIED BY
'auth_string';

• user_name:用户名，用单引号包裹，区分大小写
• host_name:主机或ip，用单引号包裹
• auth_string:真实密码

2.2.2 注意事项

• 不指定host_name相当于'user_name'@'%',%表示所有主机都可以连接到数据库，强烈不推荐
• user_name和host_name分别用单引号包裹

2.3 修改密码

2.3.1 语法

# 为指定⽤⼾设置密码 【推荐】
ALTER USER 'user_name'@'host_name' IDENTIFIED BY 'auth_string';
# 为指定⽤⼾设置密码
SET PASSWORD FOR 'user_name'@'host_name' = 'auth_string';
# 为当前登录⽤⼾设置密码
SET PASSWORD = 'auth_string';

2.4 删除用户

2.4.1 语法

DROP USER [IF EXISTS] 'user_name'@'host_name'[, ...];

一次可以删除多个用户

3.权限与授权

所有权限都会被赋予，是管理员

3.1 给用户授权

 3.1.1 语法

grant priv_type[, priv_type ...] on priv_level
to 'user_name'@'host_name' [WITH GRANT OPTION]

• priv_type:权限类型，参考表中的privailege列
• priv_level:权限层级，决定了用户权限的作用范围，分为四个层级：全局层级（*.*）、数据库层级（db_name.*）、表层级（db_name.table_name）、列层级
• 'user_name'@'host_name'：指定用户
• [WITH GRANT OPTION]:可选，允许用户将自己的权限授权给其他用户

3.2 回收权限

3.2.1 语法

REVOKE [IF EXISTS] priv_type[, priv_type] ... ON priv_level
 FROM 'user_name'@'host_name' [, 'user_name'@'host_name'] ...

十一、索引

1.简介

MySQL的索引是一种数据结构，是为了提升数据库的查询效率、更新表中的数据

2.索引的数据结构

2.1 HASH

时间复杂度为O(1)，查询速度很快，但是不支持范围查找

2.2 二叉搜索树

最坏情况下时间复杂度为O(N)

节点个数过多无法保证树高

2.3 B+树特点

1. 所有的真实数据都保存在叶子节点
2. 非叶子节点保存的是索引信息，包含了索引列的值和下一个节点的引用
3. 所有的叶子节点之间都有相互引用的关系，组成一个双向循环链表
4. 在相同树高的情况下，查询都需要遍历到叶子节点，时间复杂度比较稳定
5. 非叶子节点中的值都包含在叶子节点中

3.MySQL中的页

3.1 为什么要使用页

在.idb文件中最重要的结构体就是页，页是内存与磁盘交互的最小单元，默认大小为16KB，每次内存与磁盘的交互至少读取一页，所以在磁盘中每个页内部的地址都是连续的，之所以这样，是因为在使用数据的过程中，根据局部性原理，将来要使用的数据大概率与当前访问的数据在空间上是连续的，所以一次从磁盘中读取一页的数据放入内存中，下次查询时还在这个页中就可以直接查询，从而减少磁盘I/O提高性能

4. 索引分类

4.1 主键索引

• 当在一个表上定义一个主键PRIMARY KEY时，innoDB使用它作为聚集索引
• 推荐为每一个表定义一个主键，如果没有逻辑上唯一且非空的列或列集可以使用主键，则添加一个自增列

4.2 普通索引

• 最基本的索引类型，没有唯一性的限制
• 可能为多列创建组合索引，称为复合索引或组全索引

4.3 唯一索引

• 当在一个表上定义一个唯一键UNIQUE时，自动创建唯一索引
• 与普通索引类似，但区别在于唯一索引不允许有重复值

4.4 全文索引

• 基于文本列创建

4.5 聚集索引

• 与主键索引是同义词
• 如果没有为表定义PRIMARY KEY ，innoDB使用第一个UNIQUE和NOT NULL的列作为聚集索引
• 如果表中没有PRIMARY KEY或者合适的UNIQUE索引，innoDB会为新插入的行生成一个行号并用6字节的ROW_ID字段记录，单调递增，并使用ROW_ID作为索引

4.6 非聚集索引

• 聚集索引以外的索引称为非聚集索引
• 非聚集索引的每条记录都包含该行的主键列，以及指定的列
• innoDB使用这个主键值来搜索聚集索引中的行，这个过程称为回表查询

4.7 回表查询

• 当创建了一普通查询时，索引中记录的数据是当前列的数据+主键值，查询时如果查询列表中包含了除索引列和主键之外的列，这时索引记录不能返回足够的信息，就需要使用主键到主表中进行查询相应的列信息，这个过程叫做回表查询
• 回表查询扫描了两颗索引树，一个是当前使用的索引树，另一个是主键对应的索引树

4.8 索引覆盖

• 当一个select语句使用了普通索引且查询列表中的列刚好是创建普通索引时的所有或部分列，这时可以直接返回数据，而不用回表查询，这样的现象称为索引覆盖
• 只扫描一个索引树

 5.使用索引

5.1 自动创建

• 为一张表加主键约束、外键约束、唯一约束时，MySQL会为对应的列自动创建一个索引
• 如果表不指定任何约束时，MySQL会自动为每一列生成一个索引并用ROW_ID进行标识

5.2 手动创建

5.2.1 主键索引

# 方式一，创建表时创建主键
create table t_test_pk (
 id bigint primary key auto_increment,
 name varchar(20)
);
# ⽅式⼆，创建表时单独指定主键列
create table t_test_pk1 (
 id bigint auto_increment,
 name varchar(20),
 primary key (id)
);
# ⽅式三，修改表中的列为主键索引
create table t_test_pk2 (
 id bigint,
 name varchar(20)
);
alter table t_test_pk2 add primary key (id) ;
alter table t_test_pk2 modify id bigint auto_increment;

5.2.2 唯一索引

# ⽅式⼀，创建表时创建唯⼀键
create table t_test_uk (
 id bigint primary key auto_increment,
 name varchar(20) unique
);
# ⽅式⼆，创建表时单独指定唯⼀列
create table t_test_uk1 (
 id bigint primary key auto_increment,
 name varchar(20),
 unique (name)
);
# ⽅式三，修改表中的列为唯⼀索引
create table t_test_uk2 (
 id bigint primary key auto_increment,
 name varchar(20)
);
alter table t_test_uk2 add unique (name) ;

5.2.3 普通索引

• 创建表的时候如果可以预期到某个列是一个频繁查询的列，直接在列上创建索引

# 方式一，创建表时指定索引列
create table t_test_index (
 id bigint primary key auto_increment,
 name varchar(20) unique
 sno varchar(10),
 index(sno)
);
# ⽅式⼆，修改表中的列为普通索引
create table t_test_index1 (
 id bigint primary key auto_increment,
 name varchar(20),
 sno varchar(10)
);
alter table t_test_index1 add index (sno) ;
# ⽅式三，单独创建索引并指定索引名
create table t_test_index2 (
 id bigint primary key auto_increment,
 name varchar(20),
 sno varchar(10)
);
create index index_name on t_test_index2(sno);

5.3 创建复合索引

• 创建语法与创建普通索引相同，只不过指定多个列，列与列之间用逗号隔开

5.4 查看索引

# ⽅式⼀
show keys from 表名
# ⽅式⼆
show index from t_test_index6;
# ⽅式三，简要信息：desc 表名;
desc t_test_index6;

5.5 删除索引

5.5.1 主键索引

# 语法
alter table 表名 drop primary key;

• 不需要写索引名，因为表中只有一个主键
• 如果主键是自增列，那么必须把自增属性给取消再删除主键

5.5.2 其他索引

# 语法
alter table 表名 drop index 索引名；

5.6 创建索引的注意事项

• 索引应该创建在高频查询的列上，如果有慢查询出现，不论是否是高频查询的列，都要创建索引
• 索引需要占用额外的存储空间
• 对表进行插入、更新和删除操作时，同时也会修改索引，可能会影响性能，因为索引有单独的索引树
• 谨慎选择和规划索引

• 通过执行计划来查看索引的使用情况：EXPLAIN SQL
• type列：表示索引的效率，避免出现ALL（全表扫描）
• key列：真是使用索引的情况，如果有值表示使用了索引
• ROWS列：查询过程中可以扫描的行数，是一个估计值，值越小效率越高
• filtered列：是否进行二次过滤，值是一个百分比，如果是100效率最高

十二、事务

1. 什么是事务

事务是把一组SQL语句打包成为一个整体，在这组SQL执行过程中，要么全部成功，要么全部失败，这组SQL语句可以是一条也可以是多条。

2. 事物的ACID特性（面试高频）

事物的ACID特性指的是Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）和Durability（持久性）。

• Atomicity（原子性）：一个事务中的所有操作，要么全部成功，要么全部失败，不会出现只执行一半的情况，如果事务在执行过程中发生错误，会回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
• Consistency（一致性）：在事务开始前和事务结束之后，数据库的完整性不会被破坏。这表明写入的数据必须完全符合所有的预设规则，包括数据的精度、关联度以及关于事务执行过程中服务器崩溃后如何恢复
• Isolation（隔离性）：数据库允许多个并发事务同时对事务进行读写和修改，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务可以指定不同的隔离级别，以权衡在不同的应用场景下数据库性能和安全
• Durability（持久性）：事务处理结束后，对数据的修改将永久的写入存储介质，即使系统故障也不会丢失

3. 为什么要使用事务

事务具备的ACID特性，是使用事务的原因，支持事务的数据库能够简化我们的编程模型，不需要考虑其他的错误和并发问题，我们接触的事务本质上是数据库对ACID模型的一个实现，是为应用层服务的

4. 如何使用事务

4.1 查看支持事务的存储引擎

• 要使用事务那么数据库就要支持事务，在MySQL中支持事务的存储引擎是InnoDB，可以通过show engines；语句查看

4.2 语法

• 通过以下语句可以完成对事物的控制

# 开始⼀个新的事务
START TRANSACTION;
# 或
BEGIN；
# 提交当前事务，并对更改持久化保存
COMMIT；
# 回滚当前事务，取消其更改
ROLLBACK；

• START TRANSACTION 或 BEGIN 开始一个新事务；
• COMMIT 提交当前事务，并对更改持久化保存
• ROLLBACK回滚当前事务，取消其更改
• 无论提交还是回滚，事物都会关闭

4.3 保存点

在事务执行的过程中设置保存点，回滚时指定保存点可以把数据恢复到保存点的状态

# 开启事务
mysql> START TRANSACTION;
# 设置保存点
mysql> SAVEPOINT savepoint1;
# 设置第⼆个保存点
mysql> SAVEPOINT savepoint2;
# 回滚到第⼆个保存点
mysql> ROLLBACK TO savepoint2;
# 回滚到第⼀个保存点
mysql> ROLLBACK TO savepoint1;
# 回滚时不指定保存点，直接回滚到事务开始时的原始状态，事务关闭
mysql> ROLLBACK;

4.4 自动/手动提交事务

• 默认情况下，MySQL是自动提交事务的，也就是说执行的每个操作，比如插入、更新和删除，都会自动开启一个事务并且在语句执行完成后自动提交，发生异常时自动回滚
• 查看当前事务是否可以自动提交可以使用以下语句

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON | # ON 表⽰⾃动提交开启
+---------------+-------+

• OFF表示自动提交关闭，需要手动COMMIT或ROLLBACK
• 可以通过以下语句设置事务为自动或手动提交

# 设置事务⾃动提交
mysql> SET AUTOCOMMIT=1; # ⽅式⼀
mysql> SET AUTOCOMMIT=ON; # ⽅式⼆
# 设置事务⼿动提交
mysql> SET AUTOCOMMIT=0; # ⽅式⼀
mysql> SET AUTOCOMMIT=OFF; # ⽅式⼆

• 只要手动开启一个事务，必须以COMMIT或ROLLBACK结束，和是否自动提交没有关系
• 已提交的事务不能回滚

5. 事物的隔离性和隔离级别

5.1 什么是隔离性

MySQL服务可以同时被多个客户端访问，每个客户端执行的DML语句以事务为基本单位，那么不同的客户端在对同一张表的同一条数据进行修改的时候就可能出现相互影响的情况，为了保证不同的事务之间在执行的过程中不受影响，那么事务之间就需要相互隔离，这种特性就是隔离性

5.2 隔离级别

事务间不同程度的隔离，称为事务的隔离级别；不同的隔离级别在性能和安全性方面做了取舍，在MySQL的InnoDB引擎中事务的隔离级别有四种，分别是：

• READ YNCOMMITED，读未提交
• READ COMMITED，读已提交
• REPEATABLE READ，可重复读
• SERIALIZABLE，串行化

5.2.1 READ YNCOMMITED，读未提交

在事务中，事务A对数据进行了修改，事务B可以访问到，事务B访问到了事务A还没有提交的数据（可能发生回滚），这个现象叫做“脏读”

5.2.2 READ COMMITED，读已提交

事务A在一个事务对同一条记录查询了两次，在两次查询之间事务B对数据进行了修改并且提交了事务，当A第二次查询数据的时候，发现与第一次查到的结果不一样，这个现象叫“不可重复读”

5.2.3 REPEATABLE READ，可重复读

事务A第一次查询到的结果集，与第二次查询到的结果不一样，这个现象叫“幻读”

在InnoDB引擎中，使用了next-key锁，锁住了目标行与之前的间隙，解决了部分的幻读问题

5.2.4 SERIALIZABLE，串行化

可以解决所有的数据安全问题，所有的事物是一个接一个的执行，一个事务必须等待上一个事务执行完成后才能执行

 5.3 查看和设置隔离级别

• 事务的隔离级别分为全局作用域和会话作用域，查看不同作用域事务的隔离级别，可以使用以下连接方式：

# 全局作⽤域
SELECT @@GLOBAL.transaction_isolation;
# 会话作⽤域
SELECT @@SESSION.transaction_isolation;
# 可以看到默认的事务隔离级别是REPEATABLE-READ(可重复读)
+---------------------------------+
| @@SESSION.transaction_isolation |
+---------------------------------+
| REPEATABLE-READ | # 默认是可重复读
+---------------------------------+
1 row in set (0.00 sec)