MySql学习-基础篇

MySql基础篇

概述

关系型数据库

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库

特点：

1. 使用表存储数据，格式统一，便于维护
2. 使用SQL语言操作，标准统一，使用方便

MySql数据模型

数据类型

MySql中的数据类型有很多，主要分为三类：数值类型、字符串、日期时间类型

数值类型

类型	大小	有符号(SIGNED)范围	无符号(UNSIGNED)范围	描述
TINYINT [(M)] [UNSIGNED] [ZEROFILL]	1 byye	(-128, 127)	(0, 255)	小整数值
SMALLINT [(M)] [UNSIGNED] [ZEROFILL]	2 byte	(-31768, 32767)	(0, 65535)	大整数值
MEDIUMINT [(M)] [UNSIGNED] [ZEROFILL]	3 byte	(-8388608, 8388607)	(0, 16777215)	大整数值
INT [(M)] [UNSIGNED] [ZEROFILL]或INTEGER	4 byte	(-2147483648, 2147483647)	(0, 4294967295)	大整数值
BIGINT [(M)] [UNSIGNED] [ZEROFILL]	8 byte	(-2^63, 2^63 -1)	(0, 2^64 -1)	极大整数值
FLOAT [(M, D)] [UNSIGNED] [ZEROFILL]	4 byte	(-3.402823466 E+38, 3.402823466351 E+38)	0 和 (1.175494351 E-38, 3.402823466 E+38)	单精度浮点数
DOUBLE [(M, D)] [UNSIGNED] [ZEROFILL]	8 byte	(-1.7976931348623157 E+308, 1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308, 1.7976931348623157 E+308)	双精度浮点数
DECIMAL[(M [, D])] [UNSIGNED] [ZEROFILL]	常用于存储精确的小数，M是总位数，D是小数点后的位数	依赖于M(精度)和D(标度)的值	依赖于M(精度)和D(标度)的值	小数值(精确定点数)

字符串类型

类型	大小	描述
CHAR [ (M) ]	0 ~ 255 bytes	定长字符串，在存储时会默认用空格填充，如果 M 省略，则长度为 1，存储时占用 M 个字节，相较于 VARCHAR 来说 CHAR 的性能要好些
VARCHAR (M)	0 ~ 65535 bytes	变长字符串，M 表示最大长度，存储时所占字节为 L + 1，相较于 CHAR 来说性能较差
TINYBLOB	0 ~ 255 bytes	不超过 255 个字符的二进制数据
TINYTEXT [ (M) ]	0 ~ 255bytes	短文本字符串，没有默认值，占用 L + 1个字节
BLOB	0 ~ 65 535 bytes	二进制形式的长文本数据
TEXT [ (M) ]	0 ~ 65 535 bytes	长文本数据，没有默认值，占用 L + 2 字节
MEDIUMBLOB	0 ~ 16 777 214 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT [ (M) ]	0 ~ 16 777 215 bytes	中等长度文本数据，没有默认值，占用 L + 3 个字节
LONGBLOB	0 ~ 4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT [ (M) ]	0 ~ 4 294 967 295 bytes	极大文本数据，不能有默认值，占用 L + 4 个字节
ENUM (‘value1’, ‘value2’, …)		ENUM (枚举类型) 是一个字符串对象，其值从允许值列表中选择，它只能有一个值，从值列表中选择,最多可包含65,535个不同的元素
SET (‘value1’, ‘value2’, …)		字符串对象，该对象可以有零个或多个值，最多可包含64个不同的成员

日期和时间类型

类型	大小	范围	格式	描述
DATE	3	1000- 01- 01 至 9999- 12- 31	YYYY- MM- DD	日期值
TIME	3	- 838: 59: 59 至 838: 59: 59	HH: MM: SS	时间值或一段时间间隔
YEAR	1	1901 至 2155	YYYY	年份值
DATETIME	8	1000- 01- 01 00: 00: 00 至 9999- 12- 31 23: 59: 59	YYYY- MM- DD HH: MM: SS	混合日期和时间值
TIMESTAMP	4	1970- 01- 01 00: 00: 01 至 2038- 01- 19 03: 14: 07	YYYY- MM- DD HH: MM: SS	混合日期和时间值，时间戳

字符集

MySQL的字符集支持(Character Set Support)有两个方面：

​ 字符集(Character set)和排序方式(Collation)。

MySQL对于字符集的支持细化到四个层次:

​ 服务器(server)，数据库(database)，数据表(table)和连接(connection)。

MySQL对于字符集的指定可以细化到一个数据库，一张表，一列，应该用什么字符集。

# 1.1、查看字符集的设置 
show variables like 'character_set_%';
# 下面是查看结果
+--------------------------+----------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+----------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | utf8mb4                                                 |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8mb3                                                 |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+------------------------------------+

# 1.2、查看字符集排序设置
show variables like 'collation_%';
# 以下是字符集排序的查看结果
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | gbk_chinese_ci     |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+

# 修改字符集
# 2.1、修改服务器级别字符集
    # 临时修改：
    SET GLOBAL character_set_server=urf8;

    # 永久修改：
    # 打开mysql配置文件：在[mysqld] 后添加character-set-server=utf8
    
# 2.2、修改数据库级
    # 临时修改
    SET GLOBAL character_set_database=utf8;

    # 永久修改
    # 改了服务器级就可以了

# 2.3、修改表级
	# 更改之后永久生效
	ALTER TABLE table_name DEFAULT CHARSET ust8;
	
# 2.4、修改列级
	# 更改之后永久生效
	ALTER TABLE `products` CHANGE `products_model` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
	
# 2.5、更改连接字符集
	# 临时更改
	SET GLOBAL character_set_client;
	
	# 永久更改
	打开mysql配置文件，在[client]添加 default-character-set=utf8

# 注意
# 修改 服务器级 和 连接 的字符集，需要重启mysql服务：service mysql restart

关于GBK、GB2312、UTF8

​ UTF-8：Unicode Transformation Format-8bit，允许含BOM，但通常不含BOM。是用以解决国际上字符的一种多字节编码，它对英文使用8位(即一个字节)，中文使用24为(三个字节)来编码。UTF-8包含全世界所有国家需要用到的字符，是国际编码，通用性强。UTF-8编码的文字可以在各国支持UTF8字符集的浏览器上显示。如，如果是UTF8编码，则在外国人的英文IE上也能显示中文，他们无需下载IE的中文语言支持包。

​ GBK是国家标准GB2312基础上扩容后兼容GB2312的标准。GBK的文字编码是用双字节来表示的，即不论中、英文字符均使用双字节来表示，为了区分中文，将其最高位都设定成1。GBK包含全部中文字符，是国家编码，通用性比UTF8差，不过UTF8占用的数据库比GBD大。

GBK、GB2312等与UTF8之间都必须通过Unicode编码才能相互转换：

GBK、GB2312－－Unicode－－UTF8

UTF8－－Unicode－－GBK、GB2312

值得注意的是：

utf8 与 utf8mb4 的区别

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。
可以简单的理解 utf8mb4 是目前最大的一个字符编码,支持任意文字。

排序规则：
utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。

utf8_general_cs 区分大小写，如果用户名和邮箱用这个 就会照成不良后果。

utf8_bin 字符串每个字符串用二进制数据编译存储。 区分大小写，而且可以存二进制的内容。

utf8_general_ci 校对速度快，但准确度稍差。

utf8_unicode_ci 准确度高，但校对速度稍慢。

mysql> create database study;
Query OK, 1 row affected (0.03 sec)

基本命令：

MySql启动与关机：

net start mysql80(mysql的服务名称)
net stop mysql80

MySql客户端连接：

MySql自带的客户端连接
mysql [-h 127.0.0.1] [-p 3306] -u root -p(密码)

SQL

SQL通用语法

1. SQL语句可以单行或多行书写，以分号结尾。
2. SQL语句可以使用空格或者缩进来增强语句的可读性
3. MySql数据库的SQL语句不区分大小写，关键字建议使用大写
4. 注释
   • 单行注释：– 注释内容 或 # 注释内容(MySQL特有)
   • 多行注释：/* 注释内容 */

SQL分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象（数据库，表，字段，索引）
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改查
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

DDL-数据库操作

• 查询

  查询所有数据库

  SHOW DATABASES;
  

  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | boot               |
  | information_schema |
  | mybatis            |
  | mysql              |
  | performance_schema |
  | sakila             |
  | shop               |
  | ssmbuild           |
  | stockmanager       |
  | store              |
  | student_system     |
  | sys                |
  | test               |
  | user               |
  | world              |
  | ytxz               |
  +--------------------+
  16 rows in set (0.13 sec)
  

  查询当前数据库

  SELECT DATABASE();
  

• 创建数据库

  CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
  

  **IF NOT EXISTS **(代表这个数据库不存在就创建，如存在就不执行)
  创建数据库的时候，如果不加这个选项的话，当数据存在时，会提示一个错误，加这个之后不会提示错误了

• 删除数据库

  DROP DATABASE [IF EXISTS] 数据库名;
  

  加上 IF EXISTS 数据库存在则删除，不存在则不操作，如果不加的话删除一个不存在的数据库会报错

• 使用数据库

  USE 数据库名;
  # 可以用来切换数据库
  
  # 查看当前选择的数据库
  SELECT DATABASE();
  

DDL-表操作

• 查询当前数据库所有的表

  SHOW TABLES;
  
  # 查询结果
  mysql> show tables;
  +----------------+
  | Tables_in_boot |
  +----------------+
  | base_dict      |
  | customer       |
  | sys_user       |
  +----------------+
  

• 创建表

  CREATE TABLE 表名(
  	字段1 字段1类型 [COMMENT 字段1注释],
      字段2 字段2类型 [COMMENT 字段2注释],
      字段3 字段3类型 [COMMENT 字段3注释],
      ......
      字段n 字段n类型 [COMMENT 字段n注释]
  )[COMMENT 表注释];
  
  # 创建一个用户表
  mysql> create table tb_user(
      -> id int COMMENT '编号',
      -> name varchar(10) COMMENT '姓名',
      -> age int COMMENT '年龄',
      -> gender varchar(1) COMMENT '性别'
      -> )COMMENT '用户表';
  Query OK, 0 rows affected (0.05 sec)
  

  注意：[…] 为可选参数，最后一个字段后面没有逗号

• 查看表结构信息

  DESC 表名;
  
  mysql> desc tb_user;
  +--------+-------------+------+-----+---------+-------+
  | Field  | Type        | Null | Key | Default | Extra |
  +--------+-------------+------+-----+---------+-------+
  | id     | int         | YES  |     | NULL    |       |
  | name   | varchar(10) | YES  |     | NULL    |       |
  | age    | int         | YES  |     | NULL    |       |
  | gender | varchar(1)  | YES  |     | NULL    |       |
  +--------+-------------+------+-----+---------+-------+
  4 rows in set (0.01 sec)
  

• 查看指定表的建表语句

  SHOW CREATE TABLE 表名;
  
  mysql> SHOW CREATE TABLE tb_user;
  +---------+--------------------+
  | Table   | Create Table                                                                                             |
  +---------+--------------------+
  | tb_user | CREATE TABLE `tb_user` (
    `id` int DEFAULT NULL COMMENT '编号',
    `name` varchar(10) DEFAULT NULL COMMENT '姓名',
    `age` int DEFAULT NULL COMMENT '年龄',
    `gender` varchar(1) DEFAULT NULL COMMENT '性别'
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表' |
  +---------+--------------------+
  1 row in set (0.01 sec)
  

• DDL-表操作-修改

  • 添加字段

    ALTER TABLE 表名 ADD 字段名 类型 (长度) [COMMENT 注释] [约束];
    
    # 案例：为 emp 表增加一个新的字段“昵称” 为 nickname，类型为 varchar(20)
    
    mysql> ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';
    Query OK, 0 rows affected (0.04 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

  • 修改字段

    • 修改数据类型

      ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);
      
      # 案例：将 emp 表的 nickname 字段数据类型修改为 vachar(30);
      
      ALTER TABLE emp MODIFY nickname varchar(30);
      

    • **修改字段名 和 字段数据类型 **

      ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [COMMENT 注释] [约束];
      
      # 将 emp 表的 nickname 字段修改为 username，类型修改为 varchar(20); 
      
      ALTER TABLE emp CHANGE nickname username varchar(20) COMMENT '用户名';
      

      在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，必须要指定新字段名及类型，即使字段数据类型不变！

  • 删除字段

    ALTER TABLE 表名 DROP 字段名;
    
    # 案例：删除 emp 表的 nickname 字段
    
    ysql> ALTER TABLE emp DROP nickname;
    Query OK, 0 rows affected (0.06 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    # 查看删除后的表结构信息
    mysql> desc emp;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int         | YES  |     | NULL    |       |
    | name  | varchar(10) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.05 sec)
    

  • 修改表名

    ALTER TABLE 表名 RENAME TO 新表名;
    
    # 案例：将 emp 表的表名修改为 employee;
    
    mysql> ALTER TABLE emp RENAME TO employee;
    Query OK, 0 rows affected (0.04 sec)
    
    # 查看修改后的表名
    mysql> show tables;
    +-----------------+
    | Tables_in_study |
    +-----------------+
    | employee        |
    | tb_user         |
    +-----------------+
    2 rows in set (0.01 sec)
    

  • 删除表

    • 删除表

      DROP TABLE [IF EXISTS] 表名;	# 不加 [IF EXISTS] 的话，如果删除一个不存在的表，会报错，加了之后就不报错了
      
      # 案例：删除 tb_user 表
      mysql> DROP TABLE tb_user;
      Query OK, 0 rows affected (0.04 sec)
      
      # 查看所有的表
      mysql> show tables;
      +-----------------+
      | Tables_in_study |
      +-----------------+
      | employee        |
      +-----------------+
      1 row in set (0.01 sec)
      

    • 删除指定表，并重新创建该表

      TRUNCATE TABLE 表名;	# 相当于格式化，重新创建会把表中的 数据 清空
      
      # 重新创建 employee 表
      mysql> TRUNCATE TABLE employee;
      Query OK, 0 rows affected (0.08 sec)
      
      # 查看所有的表，发现 employee 表存在
      mysql> show tables;
      +-----------------+
      | Tables_in_study |
      +-----------------+
      | employee        |
      +-----------------+
      1 row in set (0.01 sec)
      

      不管采用那种删除方式，在删除表时都会把表中数据清空

DML

MDL英文全称是 Data Manipulation Language (数据操作语言)，用来对数据库中表的数据记录进行增删改操作。

• 添加数据

1. 给指定字段添加数据

INSERT INTO 表名(字段名1, 字段名2, ...) VALUES(值1, 值2, ...);	# 值的顺序必须和前面字段一一对应

2. 给全部字段添加数据

INSERT INTO 表名 VALUES(值1, 值2, ...);	# 值的顺序必须和 数据表 定义的顺序一致

3. 批量添加数据

# 给字段字段批量增加
INSERT INTO 表名(字段名1, 字段名2, ...) VALUES(值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...), ...;

# 批量给全部字段增加值
INSERT INTO 表名 VALUES(值1, 值2, ...), (值1, 值2, ...), ...;

注意：

​ 插入数据时，指定的字段 顺序需要与 值的顺序是一一对应的；

​ 字符串 和 日期类型 的数据应该包含在引号中；

​ 插入的 数据大小，应该在字段的 规定范围内；

• 修改数据

UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [WHERE 条件];

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

• 删除数据

DELETE FROM 表名 [WHERE 条件];

注意：

​ DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。

​ DELETE 语句不能删除某一个字段的值(可以使用UPDATE 将某个字段的值 置为 null)

DQL

DQL英文全称是Data Query Language(数据查询语言)，用来查询数据库中表的记录

查询表中数据

SELECT 
		字段列表
FROM 
		表名 [表别名]
WHERE 
		条件列表
GROUP BY
		分组字段列表
HAVING
        分组后条件列表
ORDER BY
		排序字段列表
LIMIT
		分页参数

注意：如果定义了表别名之后，就不能再使用原表名了

基本查询

1. 查询多个字段

# 查询指定字段
SELECT 字段1, 字段2, ... FROM 表名;

# 查询所有字段
SELECT * FROM 表名;

2. 给查询出来的字段设置别名

SELECT 字段1 [AS] 别名1, 字段2 [AS] 别名2, ... FROM 表名;

3. 去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

条件查询

1. 语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

2. 条件

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN …(min) AND …(max)	在某个范围之内(含最小、最大值)
IN (…)	在 in 之后的列表中的值，多选一
LIKE 占位符	模糊匹配(_匹配单个字符，%匹配任意个字符)
IS NULL	是 NULL
IS NOT NULL	不是 NULL

逻辑运算符	功能
AND 或 &&	并且（多个条件同时成立）
OR 或 ||	或者（多个条件任意一个成立）
NOT 或 !	非，不是

聚合函数

1. 介绍

   将一列数据作为一个整体，进行纵向计算

2. 常见的集合函数

函数	功能
COUNT(*)	统计整张表的行数（包括 NULL）
COUNT(字段名)	返回某列的行数（不包括 NULL 值）
MAX(字段名)	某列的最大值
MIN(字段名)	某列的最小值
AVG(字段名)	某列的平均值
SUM(字段名)	对某列进行求和

3. 语法

SELECT 聚合函数(字段名) FROM 表名;

注意：NULL 值不参与所有聚合函数运算

分组查询

1. 语法

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件]

# 案例：查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
SELECT COUNT(*) FROM employee WHERE age < 45 GROUP BY workaddress HAVING COUNT(*) >= 3;

2. WHERE 和 HAVING 的区别
   • 执行时机不同：WHERE 是分组之前进行过滤，不满足WHERE 条件，不参与分组；而 HAVING 是对分组之后的结果进行过滤。
   • 判断条件不同：WHERE不能对聚合函数进行判断，而 HAVING 可以。

注意：

• 执行顺序：WHERE > 聚合函数 > HAVING
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

排序查询

1. 语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;	# 支持多字段排序

2. 排序方式
   • ASC：升序（默认值）
   • DESC：降序

注意：如果是多字段排序，当第一个字段值 相同时，才会根据第二个字段 进行排序

分页查询

1. 语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

注意：

• 起始索引从 0 开始，起始索引 = （查询的页码 - 1）* 每页显示的记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL 中是 LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 LIMIT 10。

DQL语句的执行顺序

# sql 的编写顺序												
SELECT 															
		字段列表												
FROM 
		表名
WHERE 
		条件列表
GROUP BY
		分组字段列表
# 分组后查询条件不能使用 WHERE，需要使用 HAVING
HAVING
		分组后条件列表
ORDER BY
		排序字段列表
LIMIT
		分页参数

执行顺序：FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY --> LIMIT

DCL

DCL 英文全称是 Data Control Language(数据控制语言)，用来管理数据库 用户、控制数据库的 访问权限

管理用户

1. 查询用户

USE mysql;

# 从用户表中查询所有的用户
SELECT * FROM user;

2. 创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

# 案例：创建一个 itcast 用户，只能在本地访问，密码为 123456

mysql> create user 'itcast'@'localhost' IDENTIFIED by '123456';
Query OK, 0 rows affected (0.06 sec)

# 案例：创建一个用户 heima 可以在任意主机上访问，密码 1234565

mysql> create user 'heima'@'%' IDENTIFIED by '123456';
Query OK, 0 rows affected (0.02 sec)
# % 是通配符，代表任意主机

3. 修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password By '新密码';

# 案例：修改 itcast 用户的密码为 1234567
mysql> ALTER USER 'itcast'@'localhost' IDENTIFIED WITH mysql_native_password BY '1234567';
Query OK, 0 rows affected (0.02 sec)

4. 删除用户

DROP USER '用户名'@'主机名';

# 案例：itcats用户
mysql> DROP USER 'itcast'@'localhost';
Query OK, 0 rows affected (0.01 sec)

权限控制

权限名称	含义及授权级别
DELETE	允许删除表中的行。Level: Global, Database, Table
DROP	允许删除数据库、表或视图。Levels: Global, Database, Table
EVENT	允许使用事件。LevelsL: Global, Database
EXECUTE	允许用户执行存储例程。Levels: Global, Database, Table
FILE	允许用户使用LOAD DATA INFILE读写文件。Level: Global
GRANT OPTION	允许用户把自己拥有的权限授予给其他用户 或 从其他用户那里废除。Levels: Global, Database, Table, Procedure, Proxy
INDEX	允许创建或删除索引。Levels: Global, Database, Table
INSERT	允许向表中插入行。Levels: Global, Database, Table, Column
LOCK TABLES	允许使用 LOCK TABLES 语句。Levels: Global, Database
PROCESS	允许使用 SHOW PROCESSLIST 语句查看正在运行的进程。Level: Global
ALL [PRIVILEGES]	授予某个特定级别的所有权限，除了 GRANT OPTION 权限
ALTER	允许使用 ALTER TABLE 语句。LevelsL Global, Database, Table
ALTER ROUTINE	允许修改或删除存储例程。Levels: Global, Database, Procedure
CREATE	允许创建数据库和表。Levels: Global, Database, Table
CREATE ROUTINE	允许创建存储例程。Levels: Global, Database
CREATE TABLESPACE	允许创建、修改、删除表空间和日志文件组。Level: Global
CREATE TEMPORARY TABLES	允许创建临时表。Levels: Global
CREATE USER	允许使用 CREATE USER, DROP SUER, RENAME USER 和 REVOKE ALL PRIVILEGES 语句。Level: Global
CREATE VIEW	允许创建 和 修改 视图。Levels: Global, Database, Table
SHUTDOWN	允许使用 mysqladmin shutdown。Level: Global
PROXY	允许用户代理。Level: From user to user
REFERENCES	允许创建外键。Levels: Global, Database, Table, Column
RELOAD	允许使用 FLUSH 语句。Level: Global
SELECT	允许查询表。Levels: Global, Database, Table, Column
SHOW DATABASES	允许使用 SHOW DATABASES 语句。Level: Global
SHOW VIEW	允许使用 SHOW CREATE VIEW 语句。LevelsL: Global, Database, Table
SUPER	允许使用其他管理语句。例如：CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL 和 mysqladmin debug 等。Level: Global
TRIGGER	允许触发器操作。Levels: Global, Database, Table
UPDATE	允许更新表中的行。Levels: Global, Database, Table, Column
USAGE	“NO PRIVILEGES” 的同义词

1. 查询权限

SHOW GRANTS FOR '用户名'@'主机名';

2. 授予权限

GRANT 权限列表 ON 数据库名. 表名 TO '用户名'@'主机名';

3. 撤销权限

REVOKE 权限列表 ON 数据库名. 表名 FROM '用户名'@'主机名';

注意：

• 多个权限之间，使用逗号分隔
• 授权时，数据库名和表名可以使用 * 进行通配，代表所有

函数

函数 是指一段可以直接被另一段程度调用的程序或代码

字符串函数

字符串函数	功能
CONCAT ( s1, s2, … , sN )	字符串拼接，将s1, s2, … , sN 拼接成一个字符串
LCASE 或 LOWER ( str )	将字符串 str 全部转为小写
UCASE 或 UPPER ( str )	将字符串 str 全部转为大写
LPAD ( str, n, pad )	左填充，用字符串 pad 对 str 的左边进行填充，达到 n 个字符串长度
RPAD ( str, n, pad )	右填充， 用字符串 pad 对 str 的右边进行填充，达到 n 个字符串长度
TRIM ( str )	去掉字符串 头部 和 尾部 的空格
LTRIM ( s )	去掉字符串 s 开始处的空格
RTRIM ( s )	去掉字符串 s 结尾处的空格
SUBSTR 或 SUBSTRING ( str, start, length)	返回从字符串 str 从 start 位置起的 length 个长度的字符串（下标从 1 开始）
LENGTH 或 CHAR_LENGTH 或 CHARACTER_LENGTH ( str )	返回字符串 str 的长度
FIND_IN_SET (s1, strlist)	返回在字符串 s2 中与 str 字符串列表 匹配的字符串的位置
INSERT (s1, x, len, s2)	字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串
LOCATE ( s1, s )	从字符串 s 中获取 s1 的开始位置
POSITION ( s1 IN s )	从字符串 s 中获取 s1 的开始位置
REPEAT ( s, n )	将字符串 s 重复 n 次
REVERSE ( s )	将字符串 s 的顺序反过来
STRCMP ( s1, s2 )	比较字符串 s1 和 s2，如果 s1 与 s2 相等 返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1
REPLACE ( s1, s2，s3 )	将字符串 s1 中所有的 s2，全部替换为 s3

数值函数

数值函数	功能
CEIL ( x ) 或 CEILING ( x )	返回大于 或 等于 x 的最小整数 (向上取整)
ABS ( x )	返回 x 的绝对值
AVG ( 字段名 )	返回一个 字段所有行 的平均值
FLOOR ( X )	返回小于 或 等于 x 的最大整数(向下取整)
EXP ( x )	返回 e 的 x 次方
GREATEST (expr1, expr2, expr3, …)	返回列表中的最大值
LEAST (expr1, expr2, expr3, …)	返回列表中的最小值
LN ( x )	返回数字的自然对数
LOG ( x )	返回自然对数(以 e 为底的对数)
MAX ( 字段名 )	返回 当前字段所有行中 的最大值
MIN ( 字段名 )	返回 当前字段所有行中 的最小值
POW ( x, y ) 或 POWER ( x, y )	返回 x 的 y 次方
RAND ( [ x ] )	返回 0 到 1 的随机数，RAND( x ), 填写参数时，每次的返回值相同
ROUND ( x, y )	求参数 x 的四舍五入的值，结果保留 y 位小数
SIGN ( x )	返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1
SQRT ( x )	返回x的平方根
SUM ( 字段名 )	返回指定字段的总和
TRUNCATE (x, y )	返回数值 x 保留到小数点后 y 位的值 (与 ROUND 最大的区别是不会进行四舍五入)
MOD ( x, y )	返回 x 对 y 取模的结果
CONV (x, f1, f2 )	返回 f1 进制数变成 f2 进制数

日期函数

日期函数	功能
CURDATE () 或 CURRENT_DATE ()	返回当前日期
CURRENT_TIME () 或 CURTIME ()	返回当前时间
CURRENT_TIME TAMP ()	返回当前日期 和 时间
ADDDATE ( d, n )	计算起始日期 d 加上 n 天的日期
ADDTIME ( t, n )	时间 t 加上 n 秒的时间
DATE ( )	从日期 或 日期时间表达式中 提取日期值
DAY ( )	返回日期值 d 的日期部分
DATEDIFF ( d1, d2 )	计算日期 d1-> d2 之间相隔的天数
DATE_FORMAT ( d, ‘%Y-%m-%d %r’ )	按表达式 f 的要求显示日期 d (格式化日期)
DAYNAME ( d )	返回日期 d 是星期几，如 Monday,Tuesday
DAYOFMONTH ( d )	计算日期 d 是本月的第几天
DAYOFWEEK ( d )	日期 d 今天是星期几，1 星期日，2 星期一，以此类推
DAYOFYEAR ( d )	计算日期 d 是本年的第几天
UNIX_TIMESTAMP ( )	得到时间戳
FROM_UNIXTIME ( )	时间戳转日期

EXTRACT (unit FROM d) 从日期 d 中获取指定的值，unit 指定返回的值

*unit*价值	预期*expr*格式
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

流程函数

流程函数	功能
IF ( value, t, f )	如果 value 为 true, 则返回 t, 否则返回 f
IFNULL (value1, value2 )	如果 value1 不为空，返回 value1，否则返回 value2
CASE WHEN [val1] THEN [res1] … ELSE [default] END	如果 val1 为 true, 返回 res1, … 否则返回 default 默认值
CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END	如果 expr 的值等于 val1, 返回 res1, … 否则返回 default 默认值

函数	功能
CURRENT_USER ( ) 或 SESSOION_USER ( ) 或 SYSTEM_USER ( ) 或 USER ( )	返回当前用户
DATABASE ( )	返回当前的数据库名
VERSION ( )	返回当前数据库的版本号

约束

1. 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

2. 目的：保证数据库中数据的正确、有效性 和 完整性。

3. 分类：

约束	描述	关键字
非空约束	限制该字段的数据不能为 null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	NUIQUE
主键约束	主键是一行数据的唯一表示，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束(8.0.16版本之后)	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接	FOREIGN KEY

注意：约束是作用于表中字段上的，可以在创建表 / 修改表的时候添加约束

案例1：

字段名	字段含义	字段类型	约束条件	约束关键字
id	ID 唯一标识	int	主键，并且自动增长	PRIMARY KEY, AUTO_INCREMENT
name	姓名	varchar (10)	不为空，并且唯一	NOT NULL, UNIQUE
age	年龄	int	大于0，并且小于等于120	CHECK
status	状态	char(1)	如果没有指定该值，默认为 1	DEFAULT
gender	性别	char(1)	无

案例代码1：

# 创建 用户表
CREATE TABLE user(
	id INT PRIMARY KEY AUTO_INCREMENT COMMENT'主键',
	name VARCHAR(10) NOT NULL UNIQUE COMMENT'姓名',
	age INT CHECK(age > 0 AND age <= 120) COMMENT'年龄',
	status CHAR(1) DEFAULT('1') COMMENT'状态',
	gender CHAR(1) COMMENT'性别'
)COMMENT'用户表';

# 验证约束
INSERT INTO user(name, age, status, gender) VALUES('Tom1', 19, '1', '男'), ('Tom2', 12, '0', '男');

# 1.验证主键自增
INSERT INTO user(name, age, status, gender) VALUES('Tom3', 12, '0', '男');

# 2.验证 name 不能为空，会报错
INSERT INTO user(name, age, status, gender) VALUES(NULL, 12, '0', '男');

# 3.验证 name 唯一，不可重复，会报错
INSERT INTO user(name, age, status, gender) VALUES('Tom2', 12, '0', '男');

# 4.验证 age 必须大于 0 小于等于 120，会报错
INSERT INTO user(name, age, status, gender) VALUES('Tom4', -1, '0', '男');

# 5.验证 age 必须大于 0 小于等于 120，会报错
INSERT INTO user(name, age, status, gender) VALUES('Tom2', 122, '0', '男');

# 6.验证 status 默认值，会报错
INSERT INTO user(name, age, gender) VALUES('Tom4', 12, '男');

注意：NOT NULL 异常不会申请 id 自增空间，UNIQUE 唯一性约束会申请内存空间，所以第 2 步 执行后没有发生 id自增，但是在第 3 触发了 name 字段 唯一性约束主键 id 自增了。

外键约束

• 语法：

  • 添加外键

  # 在创建表的时候添加外键
  CREATE TABLE 表名(
  	字段名 数据类型,
      ...
      [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
  )
  
  # 已经创建好的表，增加外键
  ALTER TABLE 表名 ADD CONSTAINT 外键名称 FOREIGN KEY(外键字段名称) REFERENCES 主表(主表列名);
  

  • 删除外键

  ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
  

• 删除 或 更新行为

行为	说明
NO ACTION	当在父表删除 / 更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除 / 更新。(与 RESTRUCT 一致 )
RESTRICT	当在父表删除 / 更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除 / 更新。(与 NO ACTION 一致 )
CASCADE (级联)	当在父表删除 / 更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除 / 更新外键在子表中的记录
SET NULL	当在父表删除 / 更新对应记录时，首先检查该记录是否有对应外键，如果有，则设置子表中该外键值为 NULL (这就要求该外键允许取NULL )
SET DEFAULT	父表有变更时，子表将外键列设置成一个默认的值 ( Innodb 不支持 )

# 设置外键约束，在更新 和 删除时 子表采用级联操作
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称) REFERENCES 主表(主表列名) ON UPDATE CASCADE ON DELETE CASCADE; 

多表查询

多表关系

概述

在项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多(多对一)

• 案例：部门 与 员工 的关系
• 关系：一个部门对应多个员工，一个员工对应一个部门
• 实现：在多的一方建立外键，指向一的一方的主键

多对多

• 案例：学生 与 课程的关系
• 关系：一个学生可以选修多门课程
• 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

• 案例：用户 与 用户详情的关系
• 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
• 实现：在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的(UNIQUE)

多表查询

概述：指从多张表中查询数据

笛卡尔积：笛卡尔乘积是在在数学中，两个集合 A集合 和 B集合 的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）

多表查询的分类

连接查询

内连接：相当于查询A、B交集部分数据

• 隐式内连接

SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;

# 案例，查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）
SELECT emp.name AS '姓名', dept.name AS '部门' FROM emp, dept WHERE emp.dept_id = dept.id;

• 显式内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;

# 案例：查询每一个员工的姓名，及关联的部门的名称（显式内连接实现）
SELECT e.name AS '姓名', d.name AS '部门' FROM emp e INNER JOIN dept d ON e.dept_id = d.id;

外连接：

​ 左外连接：查询左表所有数据，以及两张表交集部分数据

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;

# 案例：查询 emp 表的所有数据，和对应的部门信息（左外连接）
SELECT e.*, d.name AS 'dept' FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;

相当于查询表1（左表）的所有数据 包含 表1和表2 交集部分的数据

​ 右外连接：查询右表所有数据，以及两张表交集部分数据

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;

# 案例：查询 dept 表的所有数据，和对应的员工信息（右外连接）
SELECT e.*, d.name AS 'dept' FROM emp e RIGHT OUTER JOIN dept d ON e.dept_id = d.id;

相当于查询表2（左表）的所有数据 包含 表1和表2 交集部分的数据

​ 自连接：当前表 与 自身的连接查询，自连接必须使用表别名

自连接查询语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

# 案例：查询员工 及其 所属领导的名字
SELECT a.name AS '员工', b.name AS '领导' FROM emp a JOIN emp b ON a.managerid = b.id;

自连接查询，可以是内连接查询，也可以是外连接查询

# 案例：查询所有员工 及其 所属领导的名字，如果员工没有领导，也需要查询出来
SELECT a.name AS '员工', b.name AS '领导' FROM emp a LEFT JOIN emp b ON a.managerid = b.id;

联合查询

对于UNION查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

# 案例：查询 工资小于 12000 以及 年龄大于 50 的员工
SELECT * FROM emp WHERE salary < 12000
UNION ALL
SELECT * FROM emp WHERE age > 50;

注意：

​ 加 ALL 会将两个结果集直接合并( 会有重复数据 )，不加 会去重

​ 对于多表查询 返回字段列表必须一致

子查询

概念：SQL语句中嵌套 SELECT 语句，称为嵌套查询，又称子查询

SELECT * FROM 表1 WHERE column = (SELECT column1 FROM 表2);

子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

根据子查询结果不同，分为：

• 标量子查询（子查询结果为单个值）
• 列子查询（子查询结果为一列）
• 行子查询（子查询结果为一行）
• 表子查询（子查询结果为多行多列）

根据子查询位置，分为：WHERE 之后、FROM 之后、SELECT 之后。

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。

​ 常用的操作符：= <> > >= < <=

# 案例：查询"销售部" 的 所有员工信息
SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '销售部');

列子查询

子查询返回的结果是单个值（可以是多行），这种子查询称为列子查询。

​ 常用的操作符：IN 、NOT IN 、ANY 、SOME 、ALL

操作符	描述
IN	在指定集合范围之内，多选一
NOT IN	不在指定集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与 ANY 等同，使用 SOME 的地方都可以使用 ANY
ALL	子查询返回列表的所有值都必须满足

# IN 案例：查询 "销售部" 和 "市场部" 的所有员工信息
SELECT * FROM emp WHERE id IN (SELECT id FROM dept WHERE name = '销售部' or name = '市场部');

# ALL 案例：查询 比 财务部 所有人工资都高的员工
SELECT * FROM emp WHERE salary > ALL (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部'));

# ANY 案例：查询比研发部
SELECT * FROM emp WHERE salary > ANY (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部'));

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

​ 常用的操作符：=、<>、IN、NOT IN

# 行子查询 案例：查询 与 "张无基" 的薪资及直属领导相同的员工信息

# 1.查询 "张无基" 的薪资及直属领导
SELECT salary, managerid FROM emp WHERE name = '张无基';
# 2.根据 1 查询的信息，去查询员工信息
SELECT * FROM emp WHERE (salary, managerid) = (12500, 1);
# 合并1 和 2 两条语句 
SELECT * FROM emp WHERE (salary, managerid) = (SELECT salary, managerid FROM emp WHERE name = '张无基');

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询

​ 常用的操作符：IN

# 表子查询 案例1：查询与 "鹿杖客" ，"宋远桥" 的职位和薪资相同的员工信息
SELECT * FROM emp WHERE (job, salary) IN ( SELECT job, salary FROM emp WHERE name = '鹿杖客' or name = '宋远桥');

# 表子查询 案例2：查询入职日期是 '2006-01-01' 之后的员工信息，及其部门信息
SELECT e.*, d.* FROM ( SELECT * FROM emp WHERE entrydate > '2006-01-01') e LEFT JOIN dept d ON e.dept_id = d.id);	

注意：案例2将 内部查询出来的结果作为一个新的表，去和 外表进行连接查询

案例

# 隐式内连接 案例：查询 员工信息及其所在部门信息
SELECT e.name, e.age, e.job, d.* FROM emp e, dept d WHERE e.dept_id = d.id;

# 显示内连接 案例：查询年龄小于30岁的员工姓名、年龄、职位、部门信息
SELECT e.name, e.age, e.job, d.* FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.age < 30;

注意：ON 后面接 两个表连接的条件，WHERE 后面接 单表查询的条件

# 案例：查询 拥有 员工的部门信息
SELECT DISTINCT d.id, d.name FROM emp e, dept d WHERE e.dept_id = d.id;

# 左外连接 案例：查询所有年龄大于 40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来
SELECT e.*, d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.age > 40;

# BETWEEN AND 案例：查询所有员工的工资等级
SELECT e.id, e.name, e.salary, s.* FROM emp e, salgrade  s WHERE e.salary BETWEEN s.losal AND s.hisal;

# 查询 "研发部" 所有员工的信息 及工资等级
SELECT e.id, e.name, e.salary, s.* FROM (SELECT emp.* FROM emp INNER JOIN dept ON emp.dept_id = dept.id WHERE dept.name = '研发部') e, salgrade s WHERE e.salary BETWEEN s.losal AND s.hisal;

事务

事务简介

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

事务的操作

# 模拟银行转账
# 张三 向 李四 转账 1000
CREATE TABLE account(
	id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
	name VARCHAR(10) COMMENT '姓名',
	money INT COMMENT '余额'
) COMMENT '账户表';

INSERT INTO account(id, name, money) VALUES(NULL, '张三', 2000), (NULL, '李四', 2000);

# 1.先查找 张三是否有足够的余额
SELECT * FROM account WHERE name = '张三';
# 2.将 张三的余额减 1000
UPDATE account set money = money - 1000 WHERE name = '张三';
# 3.将 李四的余额加 1000
UPDATE account set money = money + 1000 WHERE name = '李四';

MySQL的事务是默认自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

查看事务的提交方式

SELECT @@AUTOCOMMIT		# 结果为 1 表示事务自动提交	结果为 0 表示事务手动提交 

修改事务的提交方式 该操作为 临时操作，仅对 当前会话操作有效

set @@AUTOCOMMIT = 0;	# 将事务的提交方式设置为 手动提交

所以当上面的 语句 2 执行之后，数据库中的 张三的数据已经被修改了，但是如果在 李四修改数据之前，程序发生异常的话，会导致 李四的数据无法修改，破坏数据的一致性。

UPDATE account set money = money - 1000 WHERE name = '张三';	# 程序执行正常，事务已被提交，数据库中数据已被修改

程序执行出错
UPDATE account set money = money + 1000 WHERE name = '李四';	# 程序执行出错后，会导致 数据的不一致性错误

为了避免出现数据的不一致性错误，我们需要手动管理事务

方式一：修改事务管理方式

# 设置提交方式为手动提交，临时操作，仅对当前会话操作有效
set @@AUTOCOMMIT = 0;
# 1.程序正常
UPDATE account set money = money - 1000 WHERE name = '张三';	# 程序正常，但事务未提交，数据库中数据未修改

UPDATE account set money = money + 1000 WHERE name = '李四';	# 程序正常，但事务未提交，数据库中数据未修改

COMMIT;	# 以上程序均正常，必须提交事务才会，修改数据库中的数据

# 2.程序出现异常
UPDATE account set money = money - 1000 WHERE name = '张三';	# 程序正常，但事务未提交，数据库中数据未修改

程序执行出错
UPDATE account set money = money + 1000 WHERE name = '李四';	# 程序异常，此条程序不会执行
# COMMIT 上面的程序，出现异常，如果此时强行提交事务的话，会导致数据的不一致性问题

# 为了避免提交异常事务，导致数据的不一致性，我们需要将操作回滚，将数据修改恢复到事务开始之前的样子
ROLLBACK;

方式二：显式开启事务 无须修改事务提交方式

1. 开启事务

   START TRANSACTION; 或 BEGIN;
   

2. 提交事务

   COMMIT;
   

3. 回滚事务

   ROLLBACK;
   

START TRANSACTION;	# 显式开启事务
# BEGIN;

UPDATE account set money = money - 1000 WHERE name = '张三';	# 程序执行正常

程序执行出错
UPDATE account set money = money + 1000 WHERE name = '李四';	# 程序执行出错

COMMIT;	# 事务正常执行就提交

ROLLBACK;	# 事务异常就回滚

事务的四大特性

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

并发事务问题

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

事务隔离级别

隔离级别	脏读（是否会出现）	不可重复读	幻读
Read uncommitted（读未提交）	是	是	是
Read committed（读已提交）	否	是	是
Repeatable Read（可重复读）(默认)	否	否	是
Serializable（可串行化）	否	否	否

注意：事务隔离级别越高，数据越安全，但是性能越低。

查看事务的隔离级别

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};