MySQL
1、MySQL相关概念
1.1、三个概念
| 名称 | 描述 | 简称 |
|---|---|---|
| 数据库 | 存储和管理数据的仓库，数据是有组织的进行存储 | DataBase（DB） |
| 数据库管理系统 | 操纵和管理数据库的大型软件 | DataBase Management System (DBMS) |
| SQL | 操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准 | Structured Query Language (SQL) |
1.2、主流的关系型数据库管理系统
- Oracle：大型的收费数据库，Oracle公司产品，价格昂贵
- MySQL：开源免费的中小型数据库，后来Sun公司收购了MySQL，而Oracle又收购了Sun公司。 目前Oracle推出了收费版本的MySQL，也提供了免费的社区版本
- SQL Server：Microsoft 公司推出的收费的中型数据库，C#、.net等语言常用
- PostgreSQL：开源免费的中小型数据库
- DB2：IBM公司的大型收费数据库产品
- SQLLite：嵌入式的微型数据库。Android内置的数据库采用的就是该数据库
- MariaDB：开源免费的中小型数据库。是MySQL数据库的另外一个分支、另外一个衍生产品，与 MySQL数据库有很好的兼容性
注：不论我们使用的是上面的哪一个关系型数据库，最终在操作时，都是使用SQL语言来进行统一操作， SQL语言是操作关系型数据库的统一标准
1.3、MySQL数据库
MySQL是一个关系型数据库管理系统，体积小、速度快、开源的数据库软件，适合中小型网站、或者大型网站（集群）！
前世：瑞典MySQL AB公司
今生：属于Oracle旗下产品
1.3.1、版本
MySQL官方提供了两种不同的版本：
- 社区版本（MySQL Community Server）
- 免费， MySQL不提供任何技术支持
- 商业版本（MySQL Enterprise Edition） 收费
- 可以使用30天，官方提供技术支持
1.3.2、下载
1.3.3、安装
双击官方下来的安装包文件，根据提示安装即可
1.3.4、启动与停止
MySQL安装完成之后，在系统启动时，会自动启动MySQL服务，无需我们手动启动
当然，也可以手动的通过指令启动停止，以管理员身份运行cmd，进入命令行执行如下指令：
net start mysql80 # 启动
net stop mysql80 # 停止
注意：上述的 mysql80 是我们在安装MySQL时，默认指定的mysql的系统服务名，不是固定的，如果未改动，默认就是mysql80
1.3.5 、客户端连接
方式一：使用MySQL提供的客户端命令行工具
方式二：使用系统自带的命令行工具执行指令
mysql [-h 127.0.0.1] [-P 3306] -u root -p
# 参数：
-h : MySQL服务所在的主机IP
-P : MySQL服务端口号， 默认3306
-u : MySQL数据库用户名
-p ： MySQL数据库用户名对应的密码
[]内为可选参数，如果需要连接远程的MySQL，需要加上这两个参数来指定远程主机IP、端口，如果连接本地的MySQL，则无需指定这两个参数。
注：使用这种方式进行连接时，需要安装完毕后配置PATH环境变量
1.3.6 、数据模型
关系型数据库（RDBMS）
概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库
特点：
- 使用表存储数据，格式统一，便于维护
- 使用SQL语言操作，标准统一，使用方便
所谓二维表，指的是由行和列组成的表，如下图（就类似于Excel表格数据，有表头、有列、有行， 还可以通过一列关联另外一个表格中的某一列数据）。之前提到的MySQL、Oracle、DB2、 SQLServer这些都是属于关系型数据库，里面都是基于二维表存储数据的。简而言之，基于二维表存储数据的数据库就成为关系型数据库，不是基于二维表存储数据的数据库，就是非关系型数据库
数据模型
MySQL是关系型数据库，是基于二维表进行数据存储的，具体的结构图下:
可以通过MySQL客户端连接数据库管理系统DBMS，然后通过DBMS操作数据库
可以使用SQL语句，通过数据库管理系统操作数据库，以及操作数据库中的表结构及数据
一个数据库服务器中可以创建多个数据库，一个数据库中也可以包含多张表，而一张表中又可以包含多行记录
2、SQL
全称Structured Query Language，结构化查询语言。操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准
2.1 、SQL通用语法
- SQL语句可以单行或多行书写，以分号结尾
- SQL语句可以使用空格
/缩进来增强语句的可读性 - MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
- 注释：
- 单行注释：-- 注释内容 或 # 注释内容
- 多行注释：
/* 注释内容 */
2.2、SQL分类
SQL语句，根据其功能，主要分为四类：DDL、DML、DQL、DCL
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，用来定义数据库对象(数据库，表， 字段) |
| DML | Data Manipulation Language | 数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中表的记录 |
| DCL | Data Control Language | 数据控制语言，用来创建数据库用户、控制数据库的 访问权限 |
2.3、DDL
Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段)
2.3.1、DDL-数据库操作
查询
# 查询所有数据库
show databases;
# 查询当前数据库
select database();
创建
create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序
规则 ];
create database if not exists shopping_db default charset utf8mb4;
删除
drop database [ if exists ] 数据库名;
drop database if exists shopping_db;
使用
use 数据库名;
use school_db;
2.3.2、DDL-表操作
查询当前数据库所有表
show tables;
查看指定表结构
desc 表名;
desc tb_student;
查询指定表的建表语句
show create table 表名;
show create table tb_student;
通过这条指令，主要是用来查看建表语句的，而有部分参数我们在创建表的时候，并未指定也会查询到，因为这部分是数据库的默认值，如：存储引擎、字符集等
创建表结构
/*
CREATE TABLE if not exists 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;
*/
注： […] 内为可选参数，最后一个字段后面没有逗号
比如，我们创建一张表tb_student，对应的结构如下，那么建表语句为：
# 创建数据库
create database if not exists school_db default charset utf8mb4;
# 使用数据库
use school_db;
# 创建表结构
CREATE TABLE IF NOT EXISTS tb_student (
id INT auto_increment COMMENT '学生编号',
NAME VARCHAR (30) NOT NULL COMMENT '学生姓名',
age INT NOT NULL COMMENT '学生年龄',
gender VARCHAR(1) NOT NULL COMMENT '学生年龄',
PRIMARY KEY (id)
) COMMENT '学生表';
2.3.3、DDL-表操作-数据类型
MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型
数值类型
| 类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 bytes | (-32768，32767) | (0，65535) | 大整数值 |
| MEDIUMINT | 3 bytes | (-8388608，8388607) | (0，16777215) | 大整数值 |
| INT或INTEGER | 4 bytes | (-2147483648，2147483647) | (0，4294967295) | 大整数值 |
| BIGINT | 8 bytes | (-263，263-1) | (0，2^64-1) | 极大整数值 |
| FLOAT | 4 bytes | (-3.402823466 E+38，3.402823466351 E+38) | 0 和 (1.175494351 E-38，3.402823466 E+38) | 单精度浮点数值 |
| DOUBLE | 8 bytes | (-1.7976931348623157 E+308，1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308) | 双精度浮点数值 |
| DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值(精确定点数) |
double(7,2)：7表示整个数字长度，2表示小数位个数
1). 年龄字段 – 不会出现负数，而且人的年龄不会太大
age tinyint unsigned
2). 分数 – 总分100分, 最多出现一位小数
score double(4,1)
字符串类型
| 类型 | 大小 | 描述 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
char 与 varchar 都可以描述字符串
- char是定长字符串，指定长度多少，就占用多少个字符，多的使用空字符补位
- 而varchar是变长字符串，按照实际长度存储，不能超过指定的长度
- char：性能高，浪费空间
- varchar，性能低，节省空间
1). 用户名 username ------> 长度不定，最长不会超过50
username varchar(50)
2). 性别 gender ---------> 存储值，不是男，就是女
gender char(1)
3). 手机号 phone --------> 固定长度为11
phone char(11)
日期时间类型
| 类型 | 大小 | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| 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 | 混合日期和时间值，时间戳 |
1). 生日字段
birthday date
2). 创建时间
create_time datetime
2.3.4、DDL-表操作-修改
添加字段
ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
ALTER TABLE tb_employee ADD email VARCHAR(20) COMMENT '邮箱';
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);
ALTER TABLE tb_employee MODIFY e_name VARCHAR(20) COMMENT '姓名';
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
ALTER TABLE tb_employee CHANGE e_name username VARCHAR(25) COMMENT '用户名';
删除字段
ALTER TABLE 表名 DROP 字段名;
ALTER TABLE tb_employee DROP email;
修改表名
ALTER TABLE 表名 RENAME TO 新表名;
ALTER TABLE tb_employee RENAME TO employee;
2.3.5、DDL-表操作-删除
DROP TABLE [ IF EXISTS ] 表名;
DROP TABLE IF EXISTS employee;
可选项 IF EXISTS 代表，只有表名存在时才会删除该表，表名不存在，则不执行删除操作（如果不加该参数项，删除一张不存在的表，执行将会报错）
删除指定表，并重新创建表
TRUNCATE TABLE 表名;
TRUNCATE TABLE tb_student;
注：在删除表的时候，表中的全部数据也都会被删除（保留表结构）
2.4、图形化界面工具
上述，我们已经讲解了通过DDL语句，如何操作数据库、操作表、操作表中的字段，而通过DDL语句执行在命令进行操作，主要存在以下两点问题：
- 会影响开发效率
- 使用起来，并不直观，并不方便
所以呢，我们在日常的开发中，会借助于MySQL的图形化界面，来简化开发，提高开发效率。而目前 mysql主流的图形化界面工具，有以下几种：
2.5、DML
DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作
- 添加数据（INSERT）
- 修改数据（UPDATE）
- 删除数据（DELETE）
添加数据
具体语法：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
给指定字段添加数据
INSERT INTO tb_employee(e_no,e_name,age) VALUES (1001,'晓敏',20);
给全部字段添加数据
INSERT INTO tb_employee VALUES (NULL,1002,'小赵','女',19,'410362200206089658',NOW());
批量添加数据
INSERT INTO tb_employee VALUES
(NULL,1003,'晓明','男',18,'410365200306186532',NOW()),
(NULL,1004,'小林','男',20,'410365200306256832',NOW()),
(NULL,1005,'小李','男',19,'410362200106186321',NOW());
注：
- 插入数据时，指定的字段顺序需要与值的顺序是一一对应的
- 字符串和日期型数据应该包含在引号中
- 插入的数据大小，应该在字段的规定范围内
修改数据
具体语法：
UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;
UPDATE tb_employee SET e_name = '小昭',gender = '女' WHERE id = 1;
注：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据
删除数据
具体语法：
DELETE FROM 表名 [ WHERE 条件 ] ;
DELETE FROM tb_employee WHERE id = 5;
注：DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据
2.6、DQL
DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录
查询关键字：SELECT（重中之重）
在一个正常的业务系统中，查询操作的频次是要远高于增删改的，当我们去访问企业官网、电商网站， 在这些网站中我们所看到的数据，实际都是需要从数据库中查询并展示的。而且在查询的过程中，可能还会涉及到条件、排序、分页等操作
2.6.1、基本语法
具体语法：
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
- 基本查询（不带任何条件）
- 条件查询（WHERE）
- 聚合函数（count、max、min、avg、sum）
- 分组查询（group by）
- 排序查询（order by）
- 分页查询（limit）
2.6.2、基础查询
查询多个字段
SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;
SELECT * FROM 表名 ;
注：* 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）
字段设置别名
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
2.6.3、条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表 ;
比较运算符
| 比较运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <>或!= | 不等于 |
| BETWEEN … AND … | 在某个范围之内(含最小、最大值) |
| IN(…) | 在in之后的列表中的值，多选一 |
| LIKE 占位符 | 模糊匹配(_匹配单个字符, %匹配任意个字符) |
| IS NULL | 是NULL |
逻辑运算符
| 逻辑运算符 | 功能 |
|---|---|
| AND 或 && | 并且 (多个条件同时成立) |
| OR 或 | | | 或者 (多个条件任意一个成立) |
| NOT 或 ! | 非，不是 |
具体使用：
-- A. 查询年龄等于 88 的员工
SELECT * FROM emp WHERE age = 88;
-- B. 查询年龄小于 20 的员工信息
SELECT * FROM emp WHERE age < 20;
-- C. 查询年龄小于等于 20 的员工信息
SELECT * FROM emp WHERE age <= 20;
-- D. 查询没有身份证号的员工信息
SELECT * FROM emp WHERE idcard IS NULL;
-- E. 查询有身份证号的员工信息
SELECT * FROM emp WHERE idcard IS NOT NULL;
-- F. 查询年龄不等于 88 的员工信息
SELECT * FROM emp WHERE age != 88;
SELECT * FROM emp WHERE age <> 88;
-- G. 查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息
SELECT * FROM emp WHERE age >= 15 AND age <= 20;
SELECT * FROM emp WHERE age >= 15 && age <= 20;
SELECT * FROM emp WHERE age BETWEEN 15 AND 20;
-- H. 查询性别为 女 且年龄小于 25岁的员工信息
SELECT * FROM emp WHERE gender = '女' AND age < 25;
-- I. 查询年龄等于18 或 20 或 40 的员工信息
SELECT * FROM emp WHERE age = 18 OR age = 20 OR age = 40;
SELECT * FROM emp WHERE age IN (18,20,40);
-- J. 查询姓名为两个字的员工信息 _ %
SELECT * FROM emp WHERE `name` LIKE '__';
-- K. 查询身份证号最后一位是X的员工信息
SELECT * FROM emp WHERE idcard LIKE '%X';
SELECT * FROM emp WHERE idcard LIKE '_________________X';
注：between 最小值 and 最大值，两者不可颠倒
2.6.4、聚合函数
将一列数据作为一个整体，进行纵向计算
常见的聚合函数
| 函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
具体语法：
SELECT 聚合函数(字段列表) FROM 表名 ;
具体使用：
-- A. 统计该企业员工数量
SELECT COUNT(*) FROM emp;
SELECT COUNT(idcard) FROM emp;
-- B. 统计该企业员工的平均年龄
SELECT AVG(age) '平均年龄' FROM emp;
-- C. 统计该企业员工的最大年龄
SELECT Max(age) '最大年龄' FROM emp;
-- D. 统计该企业员工的最小年龄
SELECT MIN(age) '最小年龄' FROM emp;
-- E. 统计西安地区员工的年龄之和
SELECT SUM(age) '年龄总和' FROM emp WHERE workaddress = '西安';
注：NULL值是不参与所有聚合函数运算的
2.6.5、分组函数
具体语法：
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];
where与having区别：
- 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组，而having是分组之后对结果进行过滤
- 判断条件不同：where不能对聚合函数进行判断，而having可以
具体使用：
-- A. 根据性别分组 , 统计男性员工 和 女性员工的数量
SELECT gender,COUNT(*) FROM emp GROUP BY gender;
-- B. 根据性别分组 , 统计男性员工 和 女性员工的平均年龄
SELECT gender,AVG(age) FROM emp GROUP BY gender;
-- C. 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
SELECT workaddress,COUNT(*) 'address_count' FROM emp WHERE age < 45 GROUP BY workaddress HAVING address_count >= 3;
-- D. 统计各个工作地址上班的男性及女性员工的数量
SELECT workaddress,gender,COUNT(*) '数量' FROM emp GROUP BY workaddress,gender;
注：
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
- 执行顺序：where > 聚合函数 > having
- 支持多字段分组，具体语法为：group by columnA,columnB
2.6.6、排序函数
排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序
具体语法：
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;
排序方式：
- ASC：升序(默认值)
- DESC：降序
具体使用：
-- A. 根据年龄对公司的员工进行升序排序
SELECT age FROM emp ORDER BY age ASC;
SELECT age FROM emp ORDER BY age;
-- B. 根据入职时间, 对员工进行降序排序
SELECT entrydate FROM emp ORDER BY entrydate DESC;
-- C. 根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
SELECT age,entrydate FROM emp ORDER BY age,entrydate DESC;
注：
- 如果是升序, 可以不指定排序方式ASC
- 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序
2.6.7、分页函数
分页操作在业务系统开发时，也是非常常见的一个功能，我们在网站中看到的各种各样的分页条，后台都需要借助于数据库的分页操作
具体语法：
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
具体使用：
-- A. 查询第1页员工数据, 每页展示10条记录
-- B. 查询第2页员工数据, 每页展示10条记录 --------> (页码-1) * 每页展示记录数
SELECT * FROM emp LIMIT 0,10;
SELECT * FROM emp LIMIT 10;
SELECT * FROM emp LIMIT 10,10;
-- 1. 统计员工表中, 年龄小于60岁的 , 男性员工和女性员工的人数。
SELECT gender,COUNT(*) '人数' FROM emp WHERE age < 60 GROUP BY gender;
-- 2. 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
SELECT `name`,age,entrydate FROM emp WHERE age <= 35 ORDER BY age,entrydate DESC;
-- 3. 查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
SELECT * FROM emp WHERE gender = '男' AND age BETWEEN 20 AND 40 ORDER BY age,entrydate ASC LIMIT 5;
注：
- 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
- 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10
2.6.8、执行顺序
2.7、DCL
DCL英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限
2.7.1、管理用户
查询用户
select * from mysql.user;
查询的结果如下：
- 其中 Host代表当前用户访问的主机，如果为localhost，仅代表只能够在当前本机访问，是不可以远程访问的
- User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;
删除用户
DROP USER '用户名'@'主机名' ;
具体使用：
# 查询用户
select * from mysql.user;
# 创建用户xiaozhang, 只能够在当前主机localhost访问, 密码123456;
CREATE USER 'xiaozhang'@'localhost' IDENTIFIED BY '123456';
# 创建用户xiaozhao, 可以在任意主机访问该数据库, 密码123456;
CREATE USER 'xiaozhao'@'%' IDENTIFIED BY '123456';
# 修改用户xiaozhao的访问密码为admin;
ALTER USER 'xiaozhao'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';
# 删除 xiaozhang@localhost 用户
DROP USER 'xiaozhang'@'localhost';
注：
- 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户
- 主机名可以使用
%通配 - 这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库管理员）使用
2.7.2 权限控制
MySQL中定义了很多种权限，但是常用的就以下几种：
| 权限 | 说明 |
|---|---|
| ALL, ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 修改数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库/表/视图 |
| CREATE | 创建数据库/表 |
查询权限
SHOW GRANTS FOR '用户名'@'主机名' ;
授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
具体使用：
# 查询权限
SHOW GRANTS FOR 'xiaozhao'@'%';
# 授予权限
GRANT ALL ON school_db.* TO 'xiaozhao'@'%';
# 撤销权限
REVOKE ALL ON school_db.* FROM 'xiaozhao'@'%';
注：
- 多个权限之间，使用逗号分隔
- 授权时， 数据库名和表名可以使用
*进行通配，代表所有
3、函数
函数是指一段可以直接被另一段程序调用的程序或代码。 也就意味着，这一段程序或代码在MySQL中已经给我们提供了，我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可！
MySQL中的函数主要分为以下四类： 字符串函数、数值函数、日期函数、流程函数
3.1 、字符串函数
MySQL中内置了很多字符串函数，常用的几个如下：
| 函数 | 功能 |
|---|---|
| CONCAT(S1,S2,…Sn) | 字符串拼接，将S1，S2，… Sn拼接成一个字符串 |
| LOWER(str) | 将字符串str全部转为小写 |
| UPPER(str) | 将字符串str全部转为大写 |
| LPAD(str,n,pad) | 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度 |
| RPAD(str,n,pad) | 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度 |
| TRIM(str) | 去掉字符串头部和尾部的空格 |
| SUBSTRING(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
具体使用：
-- concat : 字符串拼接
SELECT CONCAT('Hello','欢迎学习','MySQL');
-- lower : 全部转小写
SELECT LOWER('HELLO');
-- upper : 全部转大写
SELECT UPPER('hello');
-- lpad : 左填充
SELECT LPAD('01',5,'-');
-- rpad : 右填充
SELECT RPAD('20',5,'-');
-- trim : 去除前后空格
SELECT TRIM(' Hello MySQL ');
-- substring : 截取子字符串
SELECT SUBSTRING('Hello MySQL',1,5); # 索引值从1开始
# 由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如： 1号员工的工号应该为00001
UPDATE emp SET workno = LPAD(workno,5,0);
3.2 、数值函数
常见的数值函数如下：
| 函数 | 功能 |
|---|---|
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x,y) | 返回x/y的模 |
| RAND() | 返回0~1内的随机数 |
| ROUND(x,y) | 求参数x的四舍五入的值，保留y位小数 |
具体使用：
-- ceil：向上取整
SELECT CEIL(5.1);
-- floor：向下取整
SELECT FLOOR(5.9);
-- mod：取模
SELECT MOD(5,3);
SELECT MOD(10,2);
-- rand：获取随机数
SELECT RAND();
-- round：四舍五入
SELECT ROUND(6.4);
SELECT ROUND(6.5);
SELECT ROUND(3.1415926,2); # 3.14
SELECT ROUND(3.145616,2); # 3.15
# 案例：通过数据库的函数，生成一个六位数的随机验证码
SELECT LPAD( ROUND( RAND()* 1000000, 0 ), 6, 0 ) 'code';
3.3 、日期函数
常见的日期函数如下：
| 函数 | 功能 |
|---|---|
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间 |
| YEAR(date) | 获取指定date的年份 |
| MONTH(date) | 获取指定date的月份 |
| DAY(date) | 获取指定date的日期 |
| DATE_ADD(date, INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
| DATEDIFF(date1,date2) | 返回起始时间date1 和 结束时间date2之间的天数 |
具体使用：
-- curdate：当前日期
SELECT CURDATE();
-- curtime：当前时间
SELECT CURTIME();
-- now：当前日期和时间
SELECT NOW();
-- YEAR , MONTH , DAY：当前年、月、日、时、分、秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
# ...
-- date_add：增加指定的时间间隔
SELECT DATE_ADD(NOW(),INTERVAL 10 YEAR);
-- datediff：获取两个日期相差的天数
SELECT DATEDIFF(NOW(),'2022-08-28 16:17:43');
# 案例：查询所有员工的入职天数，并根据入职天数倒序排序
SELECT DATEDIFF(CURRENT_DATE,entrydate) `hire_days_count` FROM emp ORDER BY hire_days_count DESC;
3.4 、流程函数
流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率
| 函数 | 功能 |
|---|---|
| IF(value , t , f) | 如果value为true，则返回t，否则返回 f |
| IFNULL(value1 , value2) | 如果value1为空，返回value2，否则返回value1 |
| 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默认值 |
具体使用：
# if
SELECT IF(FALSE,'ok','error');
# ifnull
SELECT IFNULL('a','b');
SELECT IFNULL('','b');
select IFNULL(null,'b');
# 需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
# case when then else end
SELECT `name`,(CASE workaddress WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '二线城市' END) '工作地址' FROM emp;
# 案例:
# 环境准备
create table score(
id int comment 'ID',
name varchar(20) comment '姓名',
math int comment '数学',
english int comment '英语',
chinese int comment '语文'
) comment '学员成绩表';
insert into score(id, name, math, english, chinese) VALUES
(1, 'Tom', 67, 88, 95),
(2, 'Rose' , 23, 66, 90),
(3, 'Jack', 56, 98, 76);
SELECT * FROM score;
SELECT id,`name`,
(CASE WHEN chinese > 85 THEN '优秀' WHEN chinese >= 60 THEN '及格' ELSE '不及格' END) '语文',
(CASE WHEN math > 85 THEN '优秀' WHEN math >= 60 THEN '及格' ELSE '不及格' END) '数学',
(CASE WHEN english > 85 THEN '优秀' WHEN english >= 60 THEN '及格' ELSE '不及格' END) '英语'
FROM score;
4、约束
4.1、概述
概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据
目的：保证数据库中数据的正确、有效性和完整性
分类：
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段的数据不能为null | NOT NULL |
| 唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | PRIMARY KEY |
| 默认约束 | 保存数据时，如果未指定该字段的值，则采用默认值 | DETAULT |
| 检查约束(8.0.16版本 之后) | 保证字段值满足某一个条件 | CHECK |
| 外键约束 | 用来让两张表的数据之间建立连接，保证数据的一致 性和完整性 | FOREIGN KEY |
注：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束
4.2、使用约束
需求： 根据需求，完成表结构的创建。需求如下
对应的建表语句为：
CREATE TABLE IF NOT EXISTS tb_user(
id INT auto_increment COMMENT 'ID唯一标识',
`name` VARCHAR(10) UNIQUE NOT NULL COMMENT '姓名',
age INT CHECK(age > 0 && age < 120) COMMENT '年龄',
`status` CHAR(1) DEFAULT 1 COMMENT '状态',
gender CHAR(1) '性别',
PRIMARY KEY(id)
) COMMENT '用户表';
4.3、外键约束
外键：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性
左侧的emp表是员工表，里面存储员工的基本信息，包含员工的ID、姓名、年龄、职位、薪资、入职日期、上级主管ID、部门ID，在员工的信息中存储的是部门的ID dept_id，而这个部门的ID是关联的部门表dept的主键id，那emp表的dept_id就是外键，关联的是另一张表的主键
注：目前上述两张表，只是在逻辑上存在这样一层关系；在数据库层面，并未建立外键关联， 所以是无法保证数据的一致性和完整性的
删除部门表id为1的部门，此时已经不存在id为1的部门，而在emp表中还有很多的员工，关联的为id为1的部门，此时就出现了数据的不完整性。 而要想解决这个问题就得通过数据库的 外键约束
4.3.1、语法
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名)
REFERENCES 主表 (主表列名) ;
# 为emp表的dept_id字段添加外键约束,关联dept表的主键id
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);
添加了外键约束之后，我们再到dept表(父表)删除id为1的记录，然后看一下会发生什么现象。 此时将会报错，不能删除或更新父表记录，因为存在外键约束
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
# 删除emp表的外键fk_emp_dept_id
ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept_id;
4.3.2、删除/更新行为
添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行 为有以下几种：
| 行为 | 说明 |
|---|---|
| NO ACTION | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不 允许删除/更新。 (与 RESTRICT 一致) 默认行为 |
| RESTRICT | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不 允许删除/更新。 (与 NO ACTION 一致) 默认行为 |
| CASCADE | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则 也删除/更新外键在子表中的记录 |
| SET NULL | 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表 中该外键值为null（这就要求该外键允许取null） |
具体使用：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES
主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
# cascade
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE;
# set null
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id) REFERENCES dept(id) ON UPDATE SET NULL ON DELETE SET NULL;
5、多表查询
5.1、多表关系
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
- 一对多(多对一)
- 多对多
- 一对一
5.1.1、 一对多
案例：部门 与 员工的关系
关系: 一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键
5.1.2、 多对多
案例：学生 与 课程的关系
关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
5.1.3、 一对一
案例：用户 与 用户详情的关系
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)
5.2、多表查询概述
多表查询就是指从多张表中查询数据
原来查询单表数据，执行的SQL形式为：select * from emp;
那么我们要执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from emp , dept ; 具体的执行结果如下：
此时，我们看到查询结果中包含了大量的结果集，总共102条记录，而这其实就是员工表emp所有的记录 (17) 与 部门表dept所有记录(6) 的所有组合情况，这种现象称之为笛卡尔积。接下来，就来简单介绍下笛卡尔积
笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况
而在多表查询中，我们是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据
在SQL语句中，如何来去除无效的笛卡尔积呢？ 我们可以给多表查询加上连接查询的条件即可
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;
而由于id为17的员工，没有dept_id字段值，所以在多表查询时，根据连接查询的条件并没有查询到
5.2.1、分类
- 连接查询
- 内连接：相当于查询A、B交集部分数据
- 外连接：
- 左外连接：查询左表所有数据，以及两张表交集部分数据
- 右外连接：查询右表所有数据，以及两张表交集部分数据
- 自连接：当前表与自身的连接查询，自连接必须使用表别名
- 子查询
5.3、内连接
内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据)
内连接的语法分为两种：隐式内连接、显式内连接
语法结构及具体使用：
# 隐式内连接
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
# 显式内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
# 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
SELECT e.`name`,d.`name` FROM emp e,dept d WHERE e.dept_id = d.id;
# 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)
SELECT e.`name`,d.`name` FROM emp e
INNER JOIN dept d
ON e.dept_id = d.id;
表的别名：
①. tablea as 别名1 , tableb as 别名2 ;
②. tablea 别名1 , tableb 别名2 ;
注：一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段
5.4、外连接
外连接分为两种，分别是：左外连接 和 右外连接
语法结构及具体使用：
# 左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
# 右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
# 查询emp表的所有数据, 和对应的部门信息
SELECT e.*,d.`name` FROM emp e
LEFT JOIN dept d
ON e.dept_id = d.id;
# 查询dept表的所有数据, 和对应的员工信息(右外连接)
SELECT d.*,e.* FROM emp e
RIGHT JOIN dept d
ON e.dept_id = d.id;
# 左外连接实现
SELECT d.*,e.* FROM dept d
LEFT JOIN emp e
ON e.dept_id = d.id;
左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据
右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据
注：左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接
5.5、自连接
自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次
语法结构及具体使用：
# 自连接
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
# 查询员工 及其 所属领导的名字
SELECT e1.`name` '员工',e2.`name` '领导'
FROM emp e1,emp e2
WHERE e1.managerid = e2.id;
# 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
SELECT e1.`name` '员工',e2.`name` '领导'
FROM emp e1
LEFT JOIN emp e2
ON e1.managerid = e2.id;
注：在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底 是哪一张表的字段
5.5.1、联合查询
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集
语法结构及具体使用：
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
# 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来
# UNION ALL 对查询的结果直接合并
SELECT * FROM emp WHERE salary < 5000
UNION ALL
SELECT * FROM emp WHERE age > 50;
# UNION 对查询的结果合并并去重
SELECT * FROM emp WHERE salary < 5000
UNION
SELECT * FROM emp WHERE age > 50;
注：
- 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
- union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重
5.6、子查询
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个
根据子查询结果不同，分为：
- 标量子查询（子查询结果为单个值）
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
5.6.1、标量子查询
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。
常用的操作符：= <> > >= < <=
具体使用：
# 查询 "销售部" 的所有员工信息
# 查询销售部的id
SELECT id FROM dept WHERE `name` = '销售部';
# 根据查询的部门id查询在销售部的员工信息
SELECT * FROM emp WHERE dept_id = (
SELECT id FROM dept WHERE `name` = '销售部'
);
# 查询在 "方东白" 入职之后的员工信息
# 查询方东白的入职日期
SELECT entrydate FROM emp WHERE `name` = '方东白';
# 根据查询的入职日期查询对应员工信息
SELECT * FROM emp WHERE entrydate > (
SELECT entrydate FROM emp WHERE `name` = '方东白'
);
5.6.2、列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询
常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL
| 操作符 | 描述 |
|---|---|
| IN | 在指定的集合范围之内，多选一 |
| NOT IN | 不在指定的集合范围之内 |
| ANY | 子查询返回列表中，有任意一个满足即可 |
| SOME | 与ANY等同，使用SOME的地方都可以使用ANY |
| ALL | 子查询返回列表的所有值都必须满足 |
具体使用：
# 查询 "销售部" 和 "市场部" 的所有员工信息
# 查询出销售部和市场部的id
SELECT id FROM dept WHERE `name` = '销售部' OR `name` = '市场部';
# 根据查询到的部门id查找对应员工信息
SELECT * FROM emp WHERE dept_id IN(
SELECT id FROM dept WHERE `name` = '销售部' OR `name` = '市场部'
);
# 查询比 财务部 所有人工资都高的员工信息
# 查询财务部的id
SELECT id FROM dept WHERE `name` = '财务部';
# 查询所有 财务部 人员工资
SELECT salary FROM emp WHERE dept_id = (
SELECT id FROM dept WHERE `name` = '财务部'
);
# 比 财务部 所有人工资都高的员工信息
SELECT * FROM emp WHERE salary > ALL (
SELECT salary FROM emp WHERE dept_id = (
SELECT id FROM dept WHERE `name` = '财务部'
)
);
# 查询比研发部其中任意一人工资高的员工信息
# 查询研发部的id
SELECT id FROM dept WHERE `name` = '研发部';
# 查询研发部所有人工资
SELECT salary FROM emp WHERE dept_id = (
SELECT id FROM dept WHERE `name` = '研发部'
);
# 比研发部其中任意一人工资高的员工信息
SELECT * FROM emp WHERE salary > any (
SELECT salary FROM emp WHERE dept_id = (
SELECT id FROM dept WHERE `name` = '研发部'
)
);
5.6.3、行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询
常用的操作符：= 、<> 、IN 、NOT IN
具体使用：
# 查询与 "张无忌" 的薪资及直属领导相同的员工信息
# 查询张无忌的薪资
SELECT salary,managerid FROM emp WHERE `name` = '张无忌';
SELECT * FROM emp WHERE (managerid,salary) = (
SELECT managerid,salary FROM emp WHERE `name` = '张无忌'
);
SELECT * FROM emp WHERE managerid = (
SELECT managerid FROM emp WHERE `name` = '张无忌'
) AND salary = (
SELECT salary FROM emp WHERE `name` = '张无忌'
);
5.6.4、表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询
常用的操作符：IN
具体使用：
# 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
# 查询 两人的职位和薪资
SELECT job,salary FROM emp WHERE `name` = '鹿杖客' OR `name` = '宋远桥';
SELECT * FROM emp WHERE (job,salary) IN (
SELECT job,salary FROM emp WHERE `name` = '鹿杖客' OR `name` = '宋远桥'
);
# 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
# 查询 "2006-01-01" 之后的员工信息
SELECT * FROM emp WHERE entrydate > '2006-01-01';
SELECT * FROM (SELECT * FROM emp WHERE entrydate > '2006-01-01') e
LEFT JOIN dept d
ON e.dept_id = d.id;
SELECT e.*,d.* FROM emp e
LEFT JOIN dept d
ON e.dept_id = d.id
WHERE entrydate > '2006-01-01';
6、事务
6.1、事务简介
事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
就比如：张三给李四转账1000块钱，张三银行账户的钱减少1000，而李四银行账户的钱要增加 1000。 这一组操作就必须在一个事务的范围内，要么都成功，要么都失败
正常情况：转账这个操作，需要分为以下这么三步来完成 ，三步完成之后，张三减少1000，而李四增加1000，转账成功 ；
异常情况：转账这个操作，也是分为以下这么三步来完成，在执行第三步是报错了，这样就导致张三减少1000块钱，而李四的金额没变,，这样就造成了数据的不一致，就出现问题了
为了解决上述的问题，就需要通过数据的事务来完成，我们只需要在业务逻辑执行之前开启事务，执行完毕后提交事务。如果执行过程中报错，则回滚事务，把数据恢复到事务开始之前的状态
注： 默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐式的提交事务
6.2、事务操作
数据准备：
drop table if exists account;
create table account(
id int primary key AUTO_INCREMENT comment 'ID',
name varchar(10) comment '姓名',
money double(10,2) comment '余额'
) comment '账户表';
insert into account(name, money) VALUES ('张三',2000), ('李四',2000);
6.2.1、未控制事务
测试正常情况
-- 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 = '李四';
测试完毕之后检查数据的状态，可以看到数据操作前后是一致的
测试异常情况
-- 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 = '李四';
我们把数据都恢复到2000， 然后再次一次性执行上述的SQL语句(出异常了… 这句话不符合SQL语法，执行就会报错)，检查最终的数据情况，发现数据在操作前后不一致了！
6.2.2、控制事务一
查看/设置事务提交方式
SELECT @@autocommit;
SET @@autocommit = 0; # 设置手动提交事务
提交事务、回滚事务
COMMIT; # 提交事务
ROLLBACK; # 回滚事务
注意：上述的这种方式，我们是修改了事务的自动提交行为，把默认的自动提交修改为了手动提交，此时我们执行的DML语句都不会提交，需要手动的执行commit进行提交
6.2.3、控制事务二
开启事务、提交事务、回滚事务
START TRANSACTION 或 BEGIN ;
COMMIT;
ROLLBACK;
转账案例：
-- 开启事务
start transaction;
-- 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 = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;
6.3、事务四大特性
- 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态
- 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的，不可逆
上述就是事务的四大特性，简称ACID
6.4、并发事务问题
赃读：一个事务读到另外一个事务还没有提交的数据
B读取到了A未提交的数据
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
事务A两次读取同一条记录，但是读取到的数据却是不一样的
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”
6.5、事务隔离级别
为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted | √ | √ | √ |
| Read committed | × | √ | √ |
| Repeatable Read(默认) | × | × | √ |
| Serializable | × | × | × |
查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
注：事务隔离级别越高，数据越安全，但是性能越低
552
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
