MySQL 基础
MySQL 基础
SQL 语法
市面上有很多关系型数据库，不管使用哪一个，都是使用 SQL 语言来进行统一操作，SQL 语言是操作关系型数据库的统一标准。SQL 全称 Structured Query Language，结构化查询语言。
SQL 语句有以下几个通用的语法规则：
- SQL 语句可以单行或多行书写，必须以分号结尾
- SQL 语句可以使用空格/缩进来增强语句的可读性
- MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写
- 注释：
- 单行注释：
-- 注释内容或# 注释内容 - 多行注释：
/* 注释内容 */
- 单行注释：
SQL 语句根据其功能主要分为四类：DDL、DML、DQL、DCL
- DDL（Data Definition Language）：数据定义语言，用来定义数据库对象(数据库，表，字段)
- DML（Data Manipulate Lanuage）：数据操作语言，用来对数据库表中的数据进行增删改
- DQL（Data Query Language）：数据查询语言，用来查询数据库表中的记录
- DCL（Data Control Language）：数据控制语言，用来创建数据库用户、控制数据库的访问权限
DDL
DDL（Data Definition Language）：数据定义语言，用来定义数据库对象(数据库，表，字段)。
数据库操作
- 连接到 MySQL 数据库以后，一般会先查看当前 MySQL 中的数据库有哪些，是否有需要操作的数据库，操作指令如下
SHOW DATABASES;
- 我们可以创建数据库，指令如下：
CREATE DATABASE 数据库名;
如果新创建的数据库名已经存在，创建会失败，因此可以使用 IF NOT EXISTS 进行创建来避免失败的出现
CREATE DATABASE IF NOT EXISTS 数据库名;
- 数据库在创建时可以设置字符集，如果使用 utf8，则字符只能使用三个字节表示，而 MySQL 中有些字符需要四个字节，因此可以使用 utf8mb4（默认字符集），指令如下：
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
- 当我删除不存在的数据库会失败，因此在删除数据库一般使用如下的指令
DROP DATABASE [IF EXISTS] 数据库名;
- 切换到创建好的数据库，指令如下：
USE 数据库名;
- 如果我们忘记当前所使用的数据库是哪一个，可以使用如下指令来获取当前所使用的数据库名：
SELECT DATABASE();
表操作
- 切换到指定的数据库以后，可以查看此数据库中的所有数据表，命令如下：
SHOW TABLES;
- 创建一个表的命令如下：
CREATE TABLE 表名 (字段1 字段1类型 [COMMENT 字段1注释],字段2 字段2类型 [COMMENT 字段2注释],字段3 字段3类型 [COMMENT 字段3注释],......字段n 字段n类型 [COMMENT 字段n注释]
) [COMMENT 表注释];
比如创建下面结构的一张表，SQL 语句为
- 一个数据库是会存在很多的表，查询指定表结构使用如下命令：
DESC 表名;
- 不仅如此，还可以查找指定表的建表语句，通过这个语句查看的建表语句，有部分参数我们在创建表的时候，并未指定也会查询到，因为这部分是数据库的默认值，如存储引擎，字符集等
SHOW CREATE TABLE 表名;
- 修改已创建表的表名，命令如下：
ALTER TABLE 表名 RENAME TO 新表名;
- 删除已创建的表与删除已创建的数据库类似，命令如下：
DROP TABLE [IF EXISTS] 表名;
上面删除表的方式会将表和数据一并删除，如果只想删除表中的数据而不删除表，则使用下面的命令：
TRUNCATE TABLE 表名;
数据类型
上面在创建一个表的时候，需要标明每个字段的类型，MySQL 数据库中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型：
- 数值类型
TINYINT：小整数值，大小 1 字节，有符号范围是（-128，127），无符号范围是（0，255）SMALLINT：大整数值，大小 2 字节，有符号范围是（-32768，32767），无符号范围是（0，65535）MEDIUMINT：大整数值，大小 3 字节，有符号范围是（-8388608，8388607），无符号范围是（0，16777215）INT/INTEGER：大整数值，大小 4 字节，有符号范围是（-2147483648，2147483647），无符号范围是（0，4294967295）BIGINT：极大整数值，大小 8 字节，有符号范围是（-2^ 63，2^ 63 - 1），无符号范围是（0，2^64-1）FLOAT：单精度浮点数值，大小 4 字节，有符号范围是（-3.402823466 E+38，3.402823466351 E+38），无符号范围是 0 和 (1.175494351 E-38，3.402823466 E+38)DOUBLE：双精度浮点数值，大小 8 字节，有符号范围是（-1.7976931348623157E+308，1.7976931348623157E+308），无符号范围是 0 和 (2.2250738585072014E-308，1.7976931348623157E+308)DECIMAL：小数值（精确定点数）有符号范围依赖于M(精度)和D(标度)的值，无符号范围依赖于M(精度)和D(标度)的值
- 字符串类型
CHAR：定长字符串（需要指定长度），大小是 0~255 字节VACHAR：变长字符串（需要指定长度），大小是 0~65535 字节TINYBLOB：不超过 255 个字符的二进制数据，大小是 0~255 字节TINYTEXT：短文本字符串，大小是 0~255 字节BLOB：二进制形式的长文本数据，大小是 0~65535 字节TEXT：长文本数据，大小是 0~65535 字节MEDIUMBLOB：二进制形式的中等长度文本数据，大小是 0~16777215 字节MEDIUMTEXT：中等长度文本数据，大小是 0~16777215 字节LONGBLOB：二进制形式的极大文本数据，大小是 0~4294967295 字节LONGTEXT：极大文本数据，大小是 0~4294967295 字节
- 日期时间类型
DATE：日期值，格式为 YYYY-MM-DD，大小是 3 字节，范围为 1000-01-01 至 9999-12-31TIME：时间值或持续时间，格式为 HH:MM:SS，大小是 3 字节，范围为 -838:59:59 至 838:59:59YEAR：年份值，格式为 YYYY，大小是 1 字节，范围为 1901 至 2155DATETIME：混合日期和时间值，格式为 YYYY-MM-DD HH:MM:SS，大小是 8 字节，范围为 1000-01-01 00:00:00 至 9999-12-31 23:59:59TIMESTAMP：混合日期和时间值，时间戳，格式为 YYYY-MM-DD HH:MM:SS，大小是 4 字节，范围为 1970-01-01 00:00:01 至 2038-01-19 03:14:07
CHAR与VACHAR都可以描述字符串，CHAR是定长字符串，指定长度多长，就占用多少个字符，和
字段值的长度无关 。而VACHAR是变长字符串，指定的长度为最大占用长度 。相对来说，CHAR的性
能会更高些。
了解了 MySQL 的基本类型，设计一张员工信息表，要求如下：
- 编号（纯数字）
- 员工工号 (字符串类型，长10位)
- 员工姓名（字符串类型，长度不超过10位）
- 性别（男/女，存储一个汉字）
- 年龄（正常人年龄，不可能存储负数）
- 身份证号（二代身份证号均为18位，身份证中有X这样的字符）
- 入职时间（取值年月日即可）
表字段操作
如果已创建的表某个字段的类型有误，或者字段名有误，我们都可以进行修改，在表创建完以后还可以添加和删除字段（字段是一条记录）。
- 添加字段的命令如下：
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
如在上面一创建的 emp 表中添加字段 nickname 表示昵称，类型为 varchar(20)
- 删除字段的命令如下：
ALTER TABLE 表名 DROP 字段;
- 修改数据类型的命令如下：
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
- 修改字段名和字段类型命令如下：
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型(长度) [COMMENT 注释] [约束];
DML
DML 全称 Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增删改操作。主要有三个操作：添加数据、修改数据以及删除数据。
添加数据
- 给指定字段添加数据的语法如下：
INSERT INTO 表名 (字段1, 字段2, 字段3, ...) VALUES (值1, 值2, 值3, ...)
如果是给所有字段添加数据，可以使用如下的语法（每个值要与字段一一对应）：
INSERT INTO 表名 VALUES (值1, 值2, 值3, ...)
- 一个一个添加数据太过于繁琐，可以一次批量添加数据，语法如下：
INSERT INTO 表名 (字段1, 字段2, 字段3, ...) VALUES (值1, 值2, 值3, ...), (值1, 值2, 值3, ...), ... -- 批量添加指定字段的数据
INSERT INTO 表名 VALUES (值1, 值2, 值3, ...), (值1, 值2, 值3, ...), ... -- 批量添加全部字段的数据
注意事项：
- 插入数据时，指定的字段顺序需要与值的顺序时一一对应的
- 字符串和日期型数据应该包含在引号中
- 插入的数据大小，应该在字段的规定范围内
修改数据
修改数据的具体语法如下：
UPDATE 表名 SET 字段1=值1, 字段2=值2, ... [WHERE 条件];
注意事项：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
删除数据
删除数据的语法如下：
DELETE FROM 表名 [WHERE 条件];
注意事项：
DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据DELETE语句不能删除某一个字段的值（可以使用UPDATE，将该字段的值置为NULL即可）
DQL
DQL 全称 Data Query Language（数据查询语言），用来查询数据表中的记录。查询的关键字是 SELECT。
在一个正常的业务系统中，查询操作的频次是要远高于增删改的，当我们去访问企业官网、电商网站，在这些网站中我们所看到的数据，实际都是需要从数据库中查询并展示的。而且在查询的过程中，可能还会涉及到条件、排序、分页等操作。
查询的语法结构如下所示，根据语法结构逐步理解查询语句
在学习数据查询操作之前下做如下的准备工作：
drop table if exists emp;create table emp(id int comment '编号',workno varchar(10) comment '工号',name varchar(10) comment '姓名',gender char(1) comment '性别',age tinyint unsigned comment '年龄',idcard char(18) comment '身份证号',workaddress varchar(50) comment '工作地址',entrydate date comment '入职时间'
) comment '员工表';INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');
基础查询
基础查询是指查询整个表指定字段的所有数据或所有字段的所有数据，语法如下：
SELECT 字段1, 字段2, ... FROM 表名; -- 查询指定字段的所有数据
SELECT * FROM 表名; -- 查询所有字段的所有数据
如果查询出来的字段名不好理解，可以为其设置别名，语法如下：
SELECT 字段1 AS 别名1, 字段2 AS 别名2, ... FROM 表名; -- 查询指定字段的所有数据
SELECT 字段1 别名1, 字段2 别名2, ... FROM 表名; -- 查询指定字段的所有数据
查询出来的数据可能会存在很多重复的内容，可以使用 DISTINCT‘ 关键字来修饰字段去除重复，语法如下：
SELECT DISTINCT 字段列表 FROM 表名;
条件查询
条件查询是指在基础查询的基础上进行一些条件过滤，把符合的数据记录查询出来，语法如下：
SELECT 字段列表 FROM 表名 WHERE 条件列表;
MySQL 中常用的比较运算符如下图所示：
常用的逻辑运算符如下图所示：
聚合函数
聚合函数是将字段作为计算对象，对该字段的所有数据进行计算，常见的聚合函数如下：
基本使用语法如下：
SELECT 聚合函数(字段列表) FROM 表名;
注意：
NULL是不参与所有聚合函数运算符的。
分组查询
前面查询获取的数据是指定条件下的数据记录，为了方便统计，可以对这些记录进行分组，使用 GROUP BY 关键字。
基本语法如下：
SELECT 字段列表 FROM 表名 [WHERE 条件列表] GROUP BY 分组字段名1, 分组字段名2, ... [HAVING 分组后过滤条件];
WHERE和HAVING的区别
- 执行时机不同：
WHERE是在分组之前进行过滤，不满足WHERE条件是不会参与分组；而HAVING是分组之后对结果进行过滤- 判断条件不同：
WHERE不能对聚合函数进行判断，而HAVING可以- 执行顺序：
WHERE> 聚合函数 >HAVING
分组之后，查询的字段一般为分组字段和聚合函数，查询其他字段无任何意义，执行查询其他字段的语法可能还会报错。
排序查询
排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序。基本语法如下：
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2, ...;
排序方式只有两种：升序（ASC 默认）和降序（DESC）
分页查询
分页查询是指在前面查询的结果中，选取指定范围的数据，起始索引从 0 开始，使用 LIMIT 关键字，语法如下：
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
执行顺序
上述标题的顺序是查询语句的编写顺序，而查询语句的执行顺序略有不同，如下图所示
验证如下：
查询年龄大于 15 的员工姓名、年龄，并根据年龄进行升序排序
select name, age from emp where age > 15 order by age;
在查询时，我们给 emp 表起一个别名 e，然后在 select 和 where 中使用该别名
select e.name, e.age from emp e where e.age > 15 order by age;
执行上述 SQL 语句后，我们看到依然可以正常的查询到结果，此时就说明： from 先执行，然后 where 和 select 执行。那 where 和 select 到底哪个先执行呢?
我们可以给 select 后面的字段起别名，然后在 where 中使用这个别名，然后看看是否可以执行成功。
select e.name ename, e.age eage from emp e where eage> 15 order by age;
执行上述 SQL 出错了，由此我们可以得出结论：from 先执行，然后执行 where，再执行 select 。
接下来看下面的语句
select e.name ename, e.age eage from emp e where eage> 15 order by eage;
执行成功，说明 order by 是在 select 语句之后执行的。
综上所述，我们可以看到 DQL 语句的执行顺序为： from … where … group by … having … select … order by … limit …
DCL
DCL 全称 Data Control Language（数据控制语言），用来管理数据库用户、控制数据库的访问权限。一般数据库管理员用的多，开发人员只需了解即可。
管理用户
- 查询用户
select * from mysql.user;
其中 Host 代表当前用户访问的主机，如果为 localhost，仅代表只能够在当前本机访问，是不可以远程访问的，使用 % 表示所有网段可以访问。User代表的是访问该数据库的用户名。在 MySQ L中需要通过 Host 和 User 来唯一标识一个用户。
- 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
- 修改密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
- 删除用户
DROP USER '用户名'@'主机名';
权限控制
MySQL 定义了很多种权限，常用的权限如下图所示：
- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
- 授予权限
GRANT 权限列表 ON 数据库.表名 TO '用户名'@'主机名';
- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
函数
与编程语言中的函数是一样的作用，MySQL 中主要包含四类函数：字符串函数、数值函数、日期函数、流程函数
字符串函数
MySQL 内置了很多字符串函数，如下图所示：
字符串函数使用实例：将 workno 统一为 5 位数，不足五位数的在前面补 0，如 1 号就是 00001
数值函数
常见的数值函数如下所示
通过数据库的函数，生成一个六位数的随机验证码：首先获取随机数 rand() ——> 变为六位数的整数乘 1000000 ——> 没有小数 round(x, 0) ——> 可能生成的小于六位数，用 0 补齐 lpad(x, 6 , ‘0’)
日期函数
常见的日期函数如下所示：
使用日期函数查询所有员工的入职天数，并根据入职天数倒序排序
流程函数
流程函数也是很常用的一类函数，可以在 SQL 语句中实现条件筛选，从而提高语句的效率。常见的流程函数如下所示：
案例：
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);
根据分数判定成绩等级
约束
约束是作用与表中字段上的规则，用于限制存储在表中的数据。其目的是保证数据库中数据的正确、有效性和完整性。
注意：约束是作用在表中字段上的，可以创建/修改表的时候添加约束。
外键约束
外键约束是用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。我们来看一个例子：
左侧的 emp 表是员工表，里面存储员工的基本信息，包含员工的 ID、姓名、年龄、职位、薪资、入职日期、上级主管ID、部门 ID，在员工的信息中存储的是部门的 ID dept_id，而这个部门的 ID 是关联的部门表 dept 的主键 id，那 emp 表的 dept_id 就是外键，关联的是另一张表的主键。
注意：目前上述两张表，只是在逻辑上存在这样一层关系；在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。
如果此时删除 dept 表中的 id 字段，删除成功之后，部门表不存在 id 为 1 的部门，而在 emp 表中还有很多的员工，关联的为 id 为 1 的部门，此时就出现了数据的不完整性。 而要想解决这个问题就得通过数据库的外键约束。
创建外键约束的语法：
CREATE TABLE 表名(字段名 数据类型,...[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段)
); -- 在建表的时候创建外键ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段)
如上面的两个表建立外键如下所示：
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
添加了外键约束之后，我们再到 dept 表(父表)删除 id 为 1 的记录， 此时将会报错，不能删除或更新父表记录，因为存在外键约束。
删除外键的语法如下：
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
删除/更新行为
添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行为有以下几种：
具体语法如下：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE [行为] ON DELETE [行为];
多表查询
DQL 语句，也就是数据查询语句，在此之前的查询都是单表查询，而下面我们要学习的则是多表查询操作。
多表关系
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
- 一对多（多对一）
- 多对多
- 一对一
一对多
- 案例：部门与员工的关系
- 关系：一个部门对应多个员工，一个员工对应一个部门
- 实现：在多的一方建立外键，指向一的一方的主键
多对多
- 案例：学生与课程的关系
- 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
- 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
一对一
- 案例：用户 与 用户详情的关系
- 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
- 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)
多表查询概述
多表查询就是指从多张表中查询数据。数据准备如下：
-- 创建dept表，并插入数据
create table dept(id int auto_increment comment 'ID' primary key,name varchar(50) not null comment '部门名称'
)comment '部门表';INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部');-- 创建emp表，并插入数据
create table emp(id int auto_increment comment 'ID' primary key,name varchar(50) not null comment '姓名',age int comment '年龄',job varchar(20) comment '职位',salary int comment '薪资',entrydate date comment '入职时间',managerid int comment '直属领导ID',dept_id int comment '部门ID'
)comment '员工表';-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),(8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);
原来查询单表数据，执行的 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 字段值，所以在多表查询时，根据连接查询的条件并没有查询到。
多表查询分为以下几类：
- 连接查询
- 内连接：相当于查询A、B交集部分数据
- 外连接：
- 左外连接：查询左表所有数据，以及两张表交集部分数据
- 右外连接：查询右表所有数据，以及两张表交集部分数据
- 自连接：当前表与自身的连接查询，自连接必须使用表别名
- 子查询
- 标量子查询
- 列子查询
- 行子查询
- 表子查询
内连接
内连接查询的是两张表交集部分的数据（也就是下面的绿色部分的数据），内连接的语法分为两种：隐式内连接、显式内连接。
隐式内连接：
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;
显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件 ...;
案例 1：查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
-- 隐式内连接
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;-- 显式内连接
select e.name, d.name from emp e join dept d on e.dept_id = d.id;
注意事项：一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。
外连接
外连接分为两种，分别是：左外连接和右外连接。具体的语法结构为：
- 左外连接：相当于查询表 1(左表)的所有数据，当然也包含表 1 和表 2 交集部分的数据。
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
- 右外连接：相当于查询表 2 (右表)的所有数据，当然也包含表 1 和表 2 交集部分的数据。
SELECT 字段列表 FROM 表1 RIGHT[OUTER] JOIN 表2 ON 条件...;
案例 1：查询 emp 表的所有数据，和对应的部门信息。由于需求中提到，要查询 emp 的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;
案例 2：查询 dept 表的所有数据，和对应的员工信息(右外连接)。由于需求中提到，要查询 dept 表的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;
注意事项：左外连接和右外连接是可以相互替换的，只需要调整在连接查询时 SQL 中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。
自连接
自连接查询
自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。语法如下：
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
而对于自连接查询，可以是内连接查询，也可以是外连接查询。
案例 1： 查询员工及其所属领导的名字
select a.name , b.name from emp a , emp b where a.managerid = b.id;
案例 2： 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;
注意事项：在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是那一张表的字段。
联合查询
对于 union 查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。
案例：将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来。对于这个需求，我们可以直接使用多条件查询，使用逻辑运算符 or 连接即可。我们也可以通过 union/union all 来联合查询。
select * from emp where salary < 5000
union all
select * from emp where age > 50;
union all 查询出来的结果，仅仅进行简单的合并，并未去重。
select * from emp where salary < 5000
union
select * from emp where age > 50;
union 联合查询，会对查询出来的结果进行去重处理。
注意：如果多条查询语句查询出来的结果，字段数量不一致，在进行
union/union all联合查询时，将会报错。
子查询
SQL 语句中嵌套 SELECT 语句，称为嵌套查询，又称子查询，语法如下：
SELECT * FROM 表1 WHERE column1 = (SELECT column1 FROM 表2);
子查询外部的语句可以是 INSERT/DELETE/UPDATE/SELECT 的任何一个，子查询出现的位置可以是 WHERE 之后，FROM 之后以及 SELECT 之后。
子查询根据查询结果不同，可以分为以下几类：
- 标量子查询（子查询结果为单个值）
- 列子查询（子查询结果为一列）
- 行子查询（子查询结果为一行）
- 表子查询（子查询结果为多行多列）
标量子查询
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。常用的操作符：= <> > >= < <=。
案例 1：查询 “销售部” 的所有员工信息，完成这个需求时，可以将需求分解为两步：
- 查询 “销售部” 部门 ID ——
select id from dept where name = '销售部'; - 根据 “销售部” 部门ID，查询员工信息 ——
select * from emp where dept_id = (select id from dept where name = '销售部');
案例 2：查询在 “方东白” 入职之后的员工信息，完成这个需求时，我们可以将需求分解为两步：
- 查询 “方东白” 的入职日期 ——
select entrydate from emp where name = '东方白'; - 查询指定入职日期之后入职的员工信息 ——
select * from emp where entrydate > (select entrydate from emp where name = '东方白');
列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。常用的操作符：IN 、NOT IN 、ANY 、SOME 、ALL。
案例 1：查询 “销售部” 和 “市场部” 的所有员工信息，需要分解为以下两步:
- 查询 “销售部” 和 “市场部” 的部门 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 = '市场部');
案例 2：查询比财务部所有人工资都高的员工信息，需要分解为以下两步:
- 查询所有财务部人员工资
select id from dept where name = '财务部'; -- 查询财务部的部门 id
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 = '财务部') );
案例 3：查询比研发部其中任意一人工资高的员工信息，分解为以下两步:
- 查询研发部所有人工资 ——
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 = '研发部') );
行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。常用的操作符：=、<>、IN、NOT IN。
案例：查询与 “张无忌” 的薪资及直属领导相同的员工信息，需要拆解为以下两部：
- 查询 “张无忌” 的薪资及直属领导 ——
select salary, managerid from emp where name = '张无忌'; - 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ——
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');
表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询。常用的操作符：IN。
案例 1：查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息，分解为两步执行:
- 查询 “鹿杖客” , “宋远桥” 的职位和薪资 ——
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 = '宋远桥' );
案例 2：查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息，分解为两步执行：
- 入职日期是 “2006-01-01” 之后的员工信息 ——
select * from emp where entrydate > '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 ;
事务
事务简介
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
就比如：张三给李四转账 1000 块钱，张三银行账户的钱减少 1000，而李四银行账户的钱要增加 1000。 这一组操作就必须在一个事务的范围内，要么都成功，要么都失败。
正常情况：转账这个操作，需要分为以下这么三步来完成 ，三步完成之后，张三减少 1000，而李四增加 1000，转账成功：
异常情况：转账这个操作，也是分为以下这么三步来完成 ，在执行第三步是报错了，这样就导致张三减少 1000 块钱，而李四的金额没变，这样就造成了数据的不一致，就出现问题了。
为了解决上述的问题，就需要通过数据的事务来完成，我们只需要在业务逻辑执行之前开启事务，执行
完毕后提交事务。如果执行过程中报错，则回滚事务，把数据恢复到事务开始之前的状态。
MySQL 的事务默认是自动提交的，也就是说，当执行完一条 DML 语句时，MySQL 会立即隐式的提交事务。
事务操作
数据准备：
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);
未控制事务
- 测试正常情况
-- 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语法，执行就会报错)，检查最终的数据情况，发现数据在操作前后不一致了。
控制事务 1
查看/设置事务提交方式
SELECT @@autocommit;
SET @@autocommit = 0;
提交事务
COMMIT;
回滚事务
ROLLBACK;
注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的 DML 语句都不会提交, 需要手动的执行 commit 进行提交。
控制事务 2
开启事务
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;
事务四大特性
事务有四大特性，简称 ACID：
- 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
- 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变是永久的。
并发事务问题
当数据库进行并发操作时，会出现三种事务问题：
- 脏读：一个事务读到另一个事务还没有提交的数据，如事务 B 读取到事务 A 未提交的数据
- 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称为不可重复读，如事务 A 两次读取同一条记录，但是读取的数据却是不一样的
- 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据
已经存在，好像出现了 “幻影”。
事务隔离级别
为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：
查看事务隔离级别语法：
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别语法：
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
注意：事务隔离级别越高，数据越安全，但是性能越低。