本文转自测试人社区，原文链接：
一、多表简介定义：结合两个或多个表来执行SQL数据库操作，这通常涉及到一个或多个表的关联，基于某些共享的列（通常是键）之间的关系。特点：简化数据；提高复用性；方便权限控制；提高系统的稳定性和负载能力。二、多表关系
一对多：定义：主表的一条记录可以对应从表的多条记录；例子：部门表，员工表；建表原则：在一对多关系中，多的表定位从表，设置外键指向主表。多对多：定义：主表的多条记录可以对应从表的多条记录；例子：商品信息表，客户表，订单表；建表原则：需要创建第三张表作为中间表，中间表需要包含两张表的主键。一对一：定义：从表的一条记录对应主表的一条记录；例子：员工信息表与身份证表、联系方式；建表原则：这种对应关系的数据，通常放在单表里。2.1 多表及应用场景介绍多表就是在数据库设计中，使用多张表格来实现数据存储的要求；在实际的项目开发中，数据量大而且复杂，需要分库分表；分表：按照一定的规则，对原有的数据库和表进行拆分；表与表之间可以通过外键建立连接。2.2 多表设计案例创建一张员工信息表，包含字段：eid 员工ID (自增主键)ename 员工姓名age 年龄gender 性别dept_name 所在部门dept_id 部门IDdept_manager 部门主管dept_location 所在地点2.2.1 以表单形式完成建表CREATE TABLE emp( emp_id INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), age INT , gender VARCHAR(10), dept_name VARCHAR(20), dept_id INT, dept_manager VARCHAR(20), dept_location VARCHAR(20) );2.2.2 插入数据INSERT INTO emp VALUES (1,'张三', 20, '男','研发部',1,'张无忌','北京');INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location)VALUES ('李四', 25, '男','研发部',1,'张无忌','北京');INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) VALUES ('宋江', 40, '男','研发部',1,'张无忌','北京');INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) VALUES ('林冲', 25, '男','研发部',1,'张无忌','北京');INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) VALUES ('林徽因', 25, '女','研发部',1,'张无忌','北京');INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) VALUES ('周芷若', 25, '女','运营部',2,'赵敏','深圳');INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) VALUES ('任盈盈', 25, '女','运营部',2,'赵敏','深圳');2.2.3 多表设计模式将数据拆分为员工信息表 employee 和部门信息表 dept；两个表之间通过部门id：dept_id字段连接。# 创建员工信息表CREATE TABLE emp_part( emp_id INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), age INT , gender VARCHAR(10),dept_id INT);# 创建部门表CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(20), dept_manager VARCHAR(20), dept_location VARCHAR(20) );2.2.4 插入数据# 向部门表插入数据INSERT INTO dept(dept_name,dept_manager,dept_location) VALUES('研发部','张无忌','北京');INSERT INTO dept(dept_name,dept_manager,dept_location) VALUES('运营部','赵敏','深圳');# 向员工信息表插入数据INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('李四', 25, '男',1);INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('宋江', 40, '男',1);INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('张三', 20, '男',1);INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('林冲', 25, '男',1);INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('林徽因', 25,'女',1);INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('周芷若', 25,'女',2);INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('任盈盈', 25, '女',2);三、SQL约束-外键约束3.1 简介外键约束（FOREIGN KEY，缩写FK）是用来实现数据库表的参照完整性的。它是指表中某个字段的值，依赖于另一张表中某个字段的值，而被依赖的字段必须且有主键约束或唯一约束。被依赖的表，通常称之为父表或主表，设置外键约束的表称之为子表或从表。3.2 相关概念主键：可以唯一标识一条记录的列；外键：从表中与主表的主键对应的字段；主表：外键所指向的表，约束其他表的表；从表：外键所在的表，被约束的表；价值：建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。3.3 建立外键约束创建表时添加外键约束：CONSTRAINT [外键约束的名称] FOREIGN KEY (外键字段) REFERENCES [主表名称] (主表字段)添加外键约束：ALTER TABLE [表名] ADD CONSTRAINT [外键约束的名称] FOREIGN KEY [外键字段] REFERENCES [主表名称(主键字段)]# 创建一个关联到主表的从表CREATE TABLE emp_part(emp_id INT PRIMARY KEY AUTO_INCREMENT,ename VARCHAR(20),age INT ,gender VARCHAR(10),dept_id INT,-- 添加外键约束CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id));# 插入一条非法数据INSERT INTO emp_part VALUES(1,'cindy',20,'female','4')3.4 删除外键约束语法：ALTER TABLE [表名] DROP FOREIGN KEY [外键约束名称]注意事项：从表外键数据类型必须与主表的主键一致；删除数据时，需先删除从表数据，再删除主表数据；添加数据时，先添加主表数据，再添加从表数据。# 删除外键约束ALTER TABLE emp_part DROP FOREIGN KEY emp_dept# 插入一条非法数据INSERT INTO emp_part VALUES(1,'cindy',20,'female','4')SELECT * FROM emp_part# 向主表中插入一条数据INSERT INTO dept VALUES(2,'运营部','张三','北京')# 向从表中插入一条数据INSERT INTO emp_part VALUES(1,'cindy',20,'female','2')# 删除主表中的数据DELETE FROM dept WHERE id=23.4.1 级联删除删除主表数据的同时，也删除掉从表中相关的数据：ON DELETE CASCADE# 创建员工信息表并添加级联删除的外键约束CREATE TABLE emp_part(emp_id INT PRIMARY KEY AUTO_INCREMENT,ename VARCHAR(20),age INT ,gender VARCHAR(10),dept_id INT,-- 添加外键约束CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)-- 设置允许级联删除ON DELETE CASCADE);# 向员工信息表中添加一条数据INSERT INTO emp_part VALUES(1,'cindy',20,'female','2')#删除主表中部门id=2的部门DELETE FROM dept WHERE id=2# 查看从表中的数据是否同时被删除SELECT * FROM emp_part四、多表查询4.1 简介多表查询，也成为关联查询，指两个或更多个表一起完成查询操作。前提条件是这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联关系，这个关联字段可能建立了外键，也可能没有外键。4.2 笛卡尔积定义：笛卡尔积是一个数学概念，又称直积，它是指两个集合元素所有可能有序对的集合。示例：A={a,b},B={c,d}A*B={(a,c),(b,c),(a,d),(b,d)}语法：select 字段名称 from 表1,表24.3 多表查询的操作定义：通过查询多张表格获取数据，至少涉及两张表。数据准备：创建部门表，插入三条数据；创建员工信息表，添加外键约束，允许级联删除，并向三个部门插入对应的员工信息。示例：
创建部门信息表
CREATE TABLE dept(id INT PRIMARY KEY AUTO_INCREMENT,dept_name VARCHAR(20),dept_manager VARCHAR(20),dept_location VARCHAR(20));INSERT INTO dept VALUES(1,'研发部','张无忌','北京');INSERT INTO dept VALUES(2,'运营部','赵敏','深圳');INSERT INTO dept VALUES(3,'销售部','周芷若','成都');# 创建员工信息表并添加级联删除的外键约束CREATE TABLE emp_part(emp_id INT PRIMARY KEY AUTO_INCREMENT,ename VARCHAR(20),age INT ,gender VARCHAR(10),dept_id INT,salary INT,-- 添加外键约束CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)-- 设置允许级联删除ON DELETE CASCADE);向员工信息表中插入数据
INSERT INTO emp_part VALUES(1,'令狐冲',25,'男','1',20000);INSERT INTO emp_part VALUES(2,'任盈盈',23,'女','1',15000);INSERT INTO emp_part VALUES(3,'岳不群',45,'男','1',40000);INSERT INTO emp_part VALUES(4,'任我行',40,'男','1',30000);INSERT INTO emp_part VALUES(5,'岳灵珊',21,'女','1',10000);INSERT INTO emp_part VALUES(6,'赵灵儿',21,'女','2',7000);INSERT INTO emp_part VALUES(7,'林月如',22,'女','2',10000);INSERT INTO emp_part VALUES(8,'阿奴',20,'女','2',7000);INSERT INTO emp_part VALUES(9,'李逍遥',25,'男','2',15000);INSERT INTO emp_part VALUES(10,'景天',28,'男','2',20000);INSERT INTO emp_part VALUES(11,'邱莹莹',21,'女','3',5000);INSERT INTO emp_part VALUES(12,'关雎尔',22,'女','3',8000);INSERT INTO emp_part VALUES(13,'曲筱绡',23,'女','3',10000);INSERT INTO emp_part VALUES(14,'樊胜美',30,'女','3',10000);INSERT INTO emp_part VALUES(15,'安迪',28,'女','3',20000);五、多表查询-内连接查询5.1 简介内连接查询，也成为内连接或等值连接，是数据库查询中常见的查询方式。内连接通过匹配两个或多个表的字段相等，来返回结果。内连接主要关注两个表中的匹配行，只显示能够匹配上的记录。这种连接方式不考虑其他运算符（如>、<、>=、<=、<>、between and 等），只关注等值条件。注意：内连接查询可以分为隐式内连接和显示内连接两种写法，但功能是一样的。在查询的列出现二义性时，要使用完全限定名。如果没有使用where子句过滤，则查询出的行数是两个数据表中的行数的乘积，这称之为“笛卡尔积”，会产生很多无用的数据。5.2 内连接内连接（INNER JOIN）：将两个或多个表中满足条件的记录组合在一起，组成一个新的结果集。内连接只返回两个表中都有的记录，即连接条件满足的记录。匹配显示，不匹配则不显示。示例：比如使用外键=主键这个条件过滤掉无效数据。按语法结构分为：隐式内连接和显示内连接。5.2.1 隐式内连接在笛卡尔积的基础上，使用where条件过滤无用的数据，这种连接方式是隐式内连接。语法：select [字段名称] from 表1, 表2 where [条件]示例：
筛选出运营部的员工的id，姓名以及所在城市
SELECT emp_id,ename,dept_locationFROM emp_part,deptWHERE dept_id=id and dept_name="运营部";5.2.2 显式内连接语法：select [字段名称] from [表1] inner join [表2] on [条件]示例：
查询运营部的员工id，姓名以及所在城市
SELECT emp_id,dept_location,enameFROM emp_partINNER JOIN dept ON dept_id=id AND dept_name="运营部"六、多表查询-外连接查询6.1 简介多表查询中的外连接查询，是一种扩展了内连接查询的查询方式。外连接查询在多表查询时，返回满足连接条件的所有行，以及左表中未找到匹配行的右表的所有行（左外连接），或右表中未找到匹配行的左表的所有行（右外连接）。外连接通常用于查找相关联的数据，即使在某些表中没有匹配的行。
6.2 外连接外连接查询：查询多个表中相关联的行，有时需要包含没有关联的行中数据，即返回查询结果集合中不仅包含符合连接条件的行，还包含左表（左连接）、右表（右连接）中的所有数据行。左外连接：使用LEFT OUTER JOIN，其中OUTER可以省略。右外连接：使用RIGHT OUTER JOIN，其中OUTER可以省略。6.2.1 左连接左连接：以左表为基准匹配右表的数据，右表中没有的项，显示为空。语法：SELECT [字段] FROM [左表] LEFT JOIN [右表] ON [条件]示例：公司新成立的人力资源部，还未招聘员工，请使用左连接方式查询出公司所有部门员工的id，姓名，性别以及他们所在的部门名称和城市。
#向部门表中插入人力资源部INSERT INTO dept VALUES(4,'人力资源部','甄嬛','北京');#查询出需要的数据SELECT emp_id,ename,gender,dept_name,dept_locationFROM dept LEFT JOIN emp_part ON dept.id=emp_part.dept_id6.2.2 右连接右连接：以右表为基准匹配左表的数据，左表中没有的项，显示为空。语法：SELECT [字段] FROM [左表] RIGHT JOIN [右表] ON [条件]示例：使用右连接方式查询所有部门员工的id，姓名，性别以及他们所在的部门名称和城市。
#向部门表中插入人力资源部INSERT INTO dept VALUES(5,'人力资源部','苏答应','北京');#查询出需要的数据SELECT emp_id,ename,gender,dept_name,dept_locationFROM dept RIGHTJOIN emp_part ON dept.id=emp_part.dept_id七、子查询7.1 简介子查询：一个查询语句嵌套在另一个查询语句内部，在SELECT子句中先计算子查询，子查询的结果作为外层另一个查询的过滤条件，查询可以基于一个表或多个表。这个特性从MySQL 4.1开始引入。子查询作为过滤条件时需要用()包裹。7.2 常见类型FROM型子查询：将子查询的结果作为付父查询的表来使用；IN/NOT IN型子查询：子查询的结果是单列多行，作为where的过滤条件；WHERE型子查询：查询结果作为过滤条件出现在比较运算符的一端。7.2.1 带FROM关键词的子查询子查询是一张多行多列的表，将子查询作为父查询的表来嵌套查询；子查询语句必须用()包裹且需要有别名；示例：计算出各部门性别为男性的员工人数。
select dept_name,count(emp_id)from( select dept_name, emp_id,ename,gender from dept inner join emp_part where id=dept_id and gender='男') b group by dept_name;7.2.2 带IN关键词的子查询将子查询作为WHERE语句后的过滤条件，常用于子查询结果是单列多行的情况。子查询语句必须用()包裹且需要有别名；示例：查询出北京地区所有的员工信息
SELECT *FROM emp_partWHERE dept_id IN (SELECT id FROM dept WHERE dept_location = '北京')7.2.3 带比较运算符的子查询将子查询的结果作为过滤条件，放在比较运算符的一端。常用于子查询结果为单个结果的情况；子查询语句必须用()包裹且需要有别名。示例：
#查询出薪资大于公司平均薪资的员工id,姓名及薪资SELECT emp_id,ename,salary FROM emp_partWHERE salary > (SELECT AVG(salary) FROM emp_part);7.2.4 with as如果一整句查询语句中，某个子查询的结果会被多个父查询引用，通常建议将共用的子查询用简写表示。语法：with [表名] as (select ...)示例：
# 查询出部门平均薪资大于公司平均薪资的部门名称，部门主管，所在地及部门平均薪资# 不使用 with ...asselect dept_id,dept_name,dept_manager,dept_location,avg_salary from dept inner join(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)b on id=dept_idand avg_salary > (select avg(avg_salary) from(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)b);# 使用 with ...aswith dept_avg as(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)selectdept_id,dept_name,dept_manager,dept_location,avg_salary from dept inner join dept_avgon id=dept_idand avg_salary > (select avg(avg_salary) from dept_avg);
