之前章节对数据的操作都是基于一张数据表完成的，即单表操作，然而实际应用中业务逻辑较为复杂，表与表之间可能存在业务联系，有时候需要基于两张或者两张以上的数据表进行操作，即多表操作。本章将针对多表操作的相关知识进行讲解。
多表查询
在关系型数据库中，一张数据表通常存储一个实体的信息。当两张或多张数据表中存在相同意义的字段时，如果需要同时显示多张数据表中的数据，便可以通过这些意义相同的字段将不同的数据表进行连接，并对连接后的数据表进行查询，这样的查询通常称为连接查询。在MySQL中，连接查询包括交叉连接查询、内连接查询、外连接查询、复合条件连接查询，本节将对这些连接查询进行讲解。
交叉连接查询
交叉连接（CROSS JOIN）查询返回的结果是被连接的两张数据表中所有数据行的笛卡尔积。交叉连接的语法格式如下所示。
SELECT <字段名> FROM <数据表名1> CROSS JOIN <数据表名2> ;
或
SELECT <字段名> FROM <数据表名1>, <数据表名2> ;
上述语法格式中，两种语法格式的返回结果相同，其中<字段名>指的是需要查询的字段名称；<数据表名1>和<数据表名2>指的是需要交叉连接的数据表的名称；CROSS JOIN用于连接两个要查询的数据表，通过CROSS JOIN语句可以查询两个表中所有的数据组合。
例如，对数据库ems中员工表emp和部门表dept进行交叉连接查询，具体如下。 部门表dept用于存储部门信息，由于在第2章的讲解中将部门表删除了，所以查询之前需要先创建一个部门表，并完善部门表中的数据，具体SQL语句如下。
# 创建部门表
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(20) UNIQUE
);
# 插入部门数据
INSERT INTO dept
VALUES
(10, '总裁办'),
(20, '研究院'),
(30, '销售部'),
(40, '运营部');对员工表和部门表进行交叉连接查询，具体SQL语句如下所示。
mysql> SELECT * FROM emp,dept;
+-------+--------+--------+------+---------+---------+--------+--------+--------+
| empno | ename | job | mgr | sal | comm | deptno | deptno | dname |
+-------+--------+--------+------+---------+---------+--------+--------+--------+
| 9369 | 张三 | 保洁 | 9902 | 900.00 | NULL | 20 | 30 | 销售部 |
| 9369 | 张三 | 保洁 | 9902 | 900.00 | NULL | 20 | 40 | 运营部 |
| 9369 | 张三 | 保洁 | 9902 | 900.00 | NULL | 20 | 20 | 研究院 |
| 9369 | 张三 | 保洁 | 9902 | 900.00 | NULL | 20 | 10 | 总裁办 |
…因篇幅有限，此处省略了其他的记录
+-------+--------+--------+------+---------+---------+--------+--------+--------+
48 rows in set (0.00 sec)内连接查询
内连接（INNER JOIN）查询又称简单连接查询或自然连接查询，是常见的连接查询。内连接根据连接条件，可以对交叉连接查询的部分结果进行筛选，仅筛选出两张表中相互匹配的记录。内连接查询的语法格式如下所示。
SELECT 查询字段 FROM 数据表1 [INNER] JOIN 数据表2 ON 匹配条件;
上述语法格式中，INNER JOIN用于连接2张数据表，其中INNER可以省略；ON用于指定查询的匹配条件，即同时匹配2张数据表的条件。由于内连接查询是对2张数据表进行操作，所以需要在匹配条件中指定所操作的字段来源于哪一张数据表，如果给数据表设置了别名，也可以通过别名指定数据表。
例如，查询已经分配了部门（部门号不为NULL）的员工的信息，员工信息只需要显示员工姓名和对应部门的名称，具体SQL语句如下所示。
SELECT ename,dname FROM emp e JOIN dept d ON e.deptno=d.deptno;自连接查询
如果在一个连接查询中，涉及到的两张数据表是同一张数据表，这种查询称为自连接查询。自连接是一种特殊的内连接，它是指相互连接的数据表在物理上为同一张数据表，但逻辑上分为两张数据表。
例如，查询员工王五所在部门的所有员工信息。查询时可以使用自连接查询实现，具体SQL语句如下所示。
SELECT e1.* FROM emp e1 JOIN emp e2 ON e1.deptno=e2.deptno
WHERE e2.ename='王五';外连接查询
内连接的查询结果是符合连接条件的记录，然而有时在查询时，除了要查询出符合条件的数据外，还需要查询出其中一张数据表中符合条件之外的其他数据，此时就需要使用外连接查询。外连接查询的语法格式如下。
SELECT 所查字段 FROM 数据表1 LEFT|RIGHT [OUTER] JOIN 数据表2 ON 匹配条件
外连接查询分为左连接（LEFT JOIN）查询和右连接（RIGHT JOIN）查询，一般称呼上述语法格式中的数据表1被称为左表，数据表2被称为右表。使用左连接查询和右连接查询的区别如下。 (1)LEFT JOIN：返回左表中的所有记录和右表中符合连接条件的记录。 (2)RIGHT JOIN：返回右表中的所有记录和左表中符合连接条件的记录。
左连接查询
左连接查询的结果包括LEFT JOIN子句中左表的所有记录，以及右表中满足连接条件的记录。如果左表的某条记录在右表中不存在，则右表中对应字段的值显示为NULL。
例如，查询所有部门名称及部门对应员工的姓名。因为需要查询出所有部门的名称，查询时可以使用左连接查询，将部门表作为查询中的左表，具体SQL语句如下所示。
SELECT d.dname,e.ename FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno;右连接查询
右连接查询的结果包括RIGHT JOIN子句中右表的所有记录，以及左表中满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表中对应字段的值显示为NULL。
例如，查询所有员工姓名及对应部门的名称，没有分配部门的员工也需要查询出来。因为需要查询出所有员工的名称，查询时可以使用右连接查询，将员工表作为查询中的右表，具体SQL语句如下所示。
SELECT d.dname,e.ename FROM dept d RIGHT JOIN emp e ON e.deptno=d.deptno;复合条件连接查询
复合条件连接查询是指在连接查询的过程中，通过添加过滤条件限制执行结果，使执行结果更加精确。
例如，查询所有员工信息，员工信息包含员工所在部门的名称，并且按员工的工资降序排序。 在查询时，可以根据deptno字段，使用左连接将部门表和员工表进行关联查询，并使用ORDER BY根据sal字段的值对查询结果进行排序，具体SQL语句如下所示。
SELECT e.*,d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno
ORDER BY e.sal DESC;子查询
子查询是指一个查询语句嵌套在另一个语句内部的查询，当某个语句执行所需要的过滤条件是另外一个SELECT语句的结果时，可以使用子查询。子查询通常在WHERE子句中结合操作符一起使用，操作符可以是IN、EXISTS、ANY、ALL、比较运算符，本节将结合这几种操作符的子查询进行讲解。
IN关键字结合子查询
IN关键字结合子查询使用时，需要内层子查询语句返回的结果是一个数据列，这个数据列中的值供外层语句进行比较操作。
例如，查询工资大于2900的员工所属部门。查询时可以先通过子查询返回工资大于2900的员工所在部门的编号，接着使用IN关键字根据部门编号查询部门信息，具体SQL语句如下所示。
SELECT * FROM dept WHERE deptno IN(SELECT deptno FROM emp WHERE sal>2900);例如，查询工资小于2900的员工所在的部门信息，具体SQL语句如下所示。
SELECT * FROM dept WHERE deptno NOT IN(SELECT deptno FROM emp WHERE sal>=2900);EXISTS关键字结合子查询
EXISTS关键字用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回TRUE，否则返回FALSE。使用EXISTS关键字结合子查询进行查询时，会先执行外层查询语句，再根据EXISTS关键字后面子查询的查询结果，判断是否保留外层语句查询出的记录，EXISTS的判断结果为TRUE时，保留对应的记录，否则去除记录。
例如，查询工资大于2900的员工所在的部门信息。首先查询出部门的所有信息，然后通过子查询筛选出工资大于2900的员工信息，接着使用EXISTS关键字符合子查询结果的记录返回；具体SQL语句如下所示。
SELECT * FROM dept WHERE EXISTS
(SELECT * FROM emp WHERE emp.deptno=dept.deptno AND emp.sal>2900);使用EXISTS关键字结合子查询，和使用IN关键字结合子查询的结果一致，但在表数据不同时，这两种方式的性能也不同。当外表数据量比较大，内表数据量比较小的时候，适合使用IN关键字结合子查询进行查询。当外表数据量比较小，内表数据量比较大的情况，适合使用EXISTS关键字结合子查询进行查询。
ANY关键字结合子查询
ANY关键字表示“任意一个”的意思，必须和比较操作符一起使用，例如ANY和>结合起来使用，表示大于任意一个。ANY关键字结合子查询时，表示子查询的查询结果集中的任一查询结果，例如“值1>ANY(子查询)”，比较值1是否大于子查询返回的结果集中任意一个结果。
例如，查询部门编号为10的员工信息，要求查询到的员工信息中，工资都高于部门编号为20的部门中的最低工资。查询时可以先使用子查询语句查询出部门编号为20的部门中所有员工工资，接着查询部门编号为10的部门中所有员工信息，最后使用ANY连接两者的工资进行比较。具体SQL语句如下所示。
SELECT * FROM emp WHERE deptno=10 AND
sal>ANY(SELECT sal FROM emp WHERE deptno=20);ALL关键字结合子查询
ALL关键字表示“所有”的意思，ALL关键字结合子查询时，表示子查询的所有查询集中是所有结果，例如“值1>ALL(子查询)”，比较值1是否大于子查询返回的结果集中所有结果。
例如，查询部门编号为10的员工信息，要求查询到的员工信息中，工资都高于部门编号为20的部门中的最高工资。查询时可以使用子查询将部门编号为20的所有员工工资查询出来，然后将部门编号为10的所有员工工资与子查询的结果进行比较，只要大于子查询中的最大值，就是符合查询条件的记录，具体SQL语句如下所示。
SELECT * FROM emp WHERE deptno=10 AND
sal>ALL(SELECT sal FROM emp WHERE deptno=20);比较运算符结合子查询
前面讲解的ANY关键字和ALL关键字的子查询中使用了比较运算符>，除了>运算符，子查询中还可以使用其他的比较运算符，如<、=、!=等。
例如，查询与王五职位相同的员工信息。查询时可以先使用子查询获取王五的职位，接着根据子查询的结果筛选出职位和王五相同的员工信息，具体SQL语句如下所示。
SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='王五')
AND ename !='王五';一般情况下，表连接查询都可以用子查询替换，但反过来却不一定适用。子查询相对比较灵活、方便、形式多样，适合作为查询的筛选条件，而表连接查询更适合查看连接表的数据。
外键约束
添加外键约束
实际开发的项目中，一个健壮数据库中的数据一定有很好的参照完整性。例如，员工管理系统中有员工表和部门表，如果员工表的部门编号字段使用了20的部门编号，部门表中的20编号却被删除了，这样就会产生垃圾数据或者错误数据。为了保证数据的完整性，可以在员工表中添加外键约束。本节将对外键约束进行讲解。
外键是数据表中的一个特殊字段，它引用另一张数据表中的一列或多列，被引用的列应该具有主键约束或唯一性约束。对于两个具有关联关系的数据表来说，相关联字段中主键所在的数据表就是主表，外键所在的数据表就是从表。
在MySQL中为从表添加外键约束的语法格式如下。
ALTER TABLE 从表名 ADD CONSTRAINT [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名);
上述语法格式中，ADD CONSTRAINT表示添加约束；外键名称是可选参数，用来指定添加的外键约束的名称；FOREIGN KEY表示外键约束；使用REFERENCES指定创建的外键引用哪个表的主键。
根据添加外键约束的语法格式，给员工表emp添加外键约束，具体SQL语句如下所示。
ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno);添加外键成功之后，可以使用SHOW CREATE TABLE语句查看员工表emp的创建语句，查询语句如下所示。
SHOW CREATE TABLE emp;在为表添加外键约束时，需要注意以下情况：
（1）建立外键的表必须使用InnoDB引擎（默认的存储引擎），不能是临时表，因为在MySQL中只有InnoDB引擎才允许使用外键。
（2）定义的外键名称不能加引号，如CONSTRAINT 'FK_ID'或CONSTRAINT " FK_ID "都是错误的。
（3）外键所在列的数据类型必须和主表中主键对应列的数据类型相同。
建立外键是为了保证数据的完整性和统一性，但如果主表中的数据被删除或被修改，从表中对应的数据也应该被删除或被修改，否则数据库中会存在很多无意义的垃圾数据。MySQL可以在建立外键时添加ON DELETE或ON UPDATE子句来告诉数据库，怎样避免垃圾数据的产生。
建立外键时避免垃圾数据的语法格式如下。
ALTER TABLE 从表名 ADD CONSTRAINT [外键名称] FOREIGN KEY(外键字段名) REFERENCES 外表表名(主键字段名); [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}] [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
添加外键约束的参数说明
| 参数名称 | 功能描述 |
| CASCADE | 主表中删除或更新记录时，同时自动删除或更新从表中对应的记录 |
| SET NULL | 主表中删除或更新记录时，使用NULL值替换从表中对应的记录 （不适用于已标记为NOT NULL的字段） |
| NO ACTION | 拒绝主表删除或修改外键关联列 |
| RESTRICT | 拒绝主表删除或修改外键关联列。 （在不定义ON DELETE和ON UPDATE子句时，这是默认设置，也是最安全的设置） |
操作关联表
实际开发中，需要根据实体的内容设计数据表，实体间会有各种关联关系，因此数据表之间也存在着各种关联关系。下面对数据表的关联关系、关联表添加数据、关联表删除数据进行讲解。
数据表的关联关系
一对一
一对一关系的两张数据表建立外键时，要分清主从关系。 在数据表的主从关系中，从表需要主表的存在才有意义。
多对一
表之间的关系是通过外键建立的。在多对一的表关系中，应该将外键添加在“多”的一方，否则会造成数据的冗余。
多对多
通常情况下，为了实现多对多关联关系需要定义一张中间表（称为连接表），中间表会存在两个外键。
需要注意的是，一对一关联关系在数据库中并不常见，因为以这种方式存储的信息通常会放在一个表中。在实际开发中，一对一关联关系可以应用于如下场景。
（1）分割具有很多列的表。
（2）由于安全原因而隔离表的一部分。
（3）保存临时数据，并且可以毫不费力地通过删除保存临时数据的表，而删除这些数据。
例如，在员工表emp的deptno字段上添加外键约束，引用部门表dept的主键字段deptno，如此就通过外键加强了员工表和部门表数据之间的关联。
员工表和部门表数据之间的关联，如下图：
已经为员工表emp添加外键约束。此时员工表emp和部门表dept之间是多对一的关联关系。下面演示在这两个关联表中添加数据，具体如下。
（1）往主表dept中插入数据。因为从表emp的外键列只能插入所引用的列（部门表的deptno字段）中存在的值，所以如果要为两个数据表添加数据，就需要先为主表dept添加数据，插入数据的SQL语句如下。
INSERT INTO dept VALUES(50, '人力资源部');（2）往从表emp中插入数据。主表中添加的数据中，主键deptno的值包含10、20、30、40和50，由于员工表emp的外键引用部门表的主键deptno，因此在往员工表emp中添加数据时，其deptno字段的值只能是10、20、30、40和50，不能使用其他的值，具体语句如下。
INSERT INTO emp VALUES(9966,'八戒','运营专员',9839,3000,2000,40);
INSERT INTO emp VALUES(9999,'悟空','人事专员',9982,3000,NULL,50);数据插入成功后，如果要查询人力资源部有哪些员工，可以使用连接查询完成，也可以使用子查询完成。例如使用交叉连接查询完成查询需求，具体SQL语句及执行结果如下所示。
SELECT e.*,d.dname FROM emp e,dept d
WHERE e.deptno=d.deptno AND d.dname='人力资源部';
从上述执行结果可以得出，人力资源部只有1名员工。需要注意的是，外键约束是为了保证数据的完整性和统一性，主表和从表中进行数据的新增、编辑、删除时需要遵循外键约束的要求，但是对数据的查询没有约束性。
除了给关联表添加数据，某些情况下也存在删除关联表中数据的需求。例如，因为公司组织架构调整，需要取消人力资源部，此时就需要在数据库中将人力资源部删除。下面演示删除关联表中部门表的数据，具体如下。
由于员工表emp和部门表dept之间使用外键进行了关联，主表dept中已经被引用的值不能直接删除。如果要删除人力资源部，需要先将人力资源部中的员工删除，或者转移到其他部门，或者不分配部门（部门编号设置为NULL）。在此选择先删除人力资源部中的员工，再删除部门表中的人力资源部。
（1）删除从表emp中属于人力资源部的员工信息，具体SQL语句如下所示。
DELETE FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='人力资源部');（2）删除主表dept中的数据。此时从表emp中已经没有数据引用主表dept主键值为人力资源部的记录，可以删除主表dept中部门名称为人力资源部的记录，具体SQL语句及执行结果如下所示。
DELETE FROM dept WHERE dname='人力资源部';如果删除关联表的数据时，没有先删除从表中有关联的数据，而直接删除主表的数据，会删除失败。例如，直接删除部门表dept中名称为运营部的记录，具体SQL语句及执行结果如下所示。
DELETE FROM dept WHERE dname='运营部';ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ems`.`emp`, CONSTRAINT `fk_deptno` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`))
由此运行结果可以看出，执行删除语句报错了。说明在两个具有关联关系的表中删除数据时，需要先解除从表中外键对主表中主键值的引用，然后再删除主表中的数据，否则会报错。
删除外键约束
在实际开发中，根据业务逻辑的需求，需要解除两个表之间的关联关系时，就需要删除外键约束。删除外键约束的语法格式如下。
ALTER TABLE表名DROP FOREIGN KEY 外键名;
演示外键约束的删除，例如将员工表emp中的外键约束删除，具体SQL语句如下。
ALTER TABLE emp DROP FOREIGN KEY fk_deptno;上机实践：图书管理系统的多表操作
实践需求1：查询张三当前借阅的图书信息，图书信息只需显示借阅人编号、借阅人名称、图书名称和借阅时间。
动手实践1：根据图书表book的borrower_id字段和用户表user的name字段将表book和表user进行关联，并查询出user表中name值为张三的记录，查询出的记录只返回user表的id、name字段的值，以及book表的 borrower、name、bookname、b.borrow_time字段的值，具体的SQL语句如下所示。
实践需求2：查询价格比《西游记》的价格高的图书信息，图书信息只需显示图书名称和图书价格。
动手实践2：首先查询图书表book中name的值为西游记的图书价格，将该查询作为子查询；然后查询价格大于子查询结果的图书信息，查询出的图书信息只返回name、price字段的值，具体的SQL语句如下所示。
实践需求3：查询高于平均价的图书信息。查询价格比所有图书的平均价格还低的图书信息，图书信息只需显示图书名称和图书价格。
动手实践3：首先查询图书表book中price字段的平均值，将该查询作为子查询；然后查询价格小于于子查询结果的图书信息，查询出的图书信息只返回name、price字段的值，具体的SQL语句如下所示。
实践需求4：根据图书状态查询同类状态的图书。查询图书状态和《三国演义》相同的图书信息，图书信息只需显示图书名称、图书价格和状态。
动手实践4：首先查询图书表book中三国演义state的值，将该查询作为子查询；然后查询book表中state值和子查询结果相同的图书信息，查询出的图书信息只返回name、price、state字段的值，具体的SQL语句如下所示。
实践需求5：查询已借阅的低于某价格的图书信息。查询已借阅图书中，价格比任意未借阅的图书价格还低的图书信息，图书信息只需显示图书名称、图书价格和状态。
动手实践5：首先查询图书表book中state等于0的图书价格，将该查询作为子查询；然后查询book表中state值等于1，并且price字段的值小于任意一个子查询结果的图书信息，查询出的图书信息只返回name、price、state字段的值，具体的SQL语句如下所示。
实践需求6：查询价格比任意已借阅的图书价格还高的图书信息，图书信息只需显示图书名称、图书价格和状态。
动手实践6：首先查询图书表book中state等于1的图书价格，将该查询作为子查询；然后查询book表中price字段的值大于所有子查询结果的图书信息，查询出的图书信息只返回name、price、state字段的值，具体的SQL语句如下所示。
实践需求7：添加外键约束，对图书表中的借阅者编号添加外键约束，以保证数据的完整性。
动手实践7：在图书表book中borrower_id字段添加外键约束，创建的外键引用user表的id字段，具体的SQL语句如下所示。
1341
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
