[大数据]MySQL多表操作

之前章节对数据的操作都是基于一张数据表完成的，即单表操作，然而实际应用中业务逻辑较为复杂，表与表之间可能存在业务联系，有时候需要基于两张或者两张以上的数据表进行操作，即多表操作。本章将针对多表操作的相关知识进行讲解。

多表查询

在关系型数据库中，一张数据表通常存储一个实体的信息。当两张或多张数据表中存在相同意义的字段时，如果需要同时显示多张数据表中的数据，便可以通过这些意义相同的字段将不同的数据表进行连接，并对连接后的数据表进行查询，这样的查询通常称为连接查询。在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}]

添加外键约束的参数说明

操作关联表

实际开发中，需要根据实体的内容设计数据表，实体间会有各种关联关系，因此数据表之间也存在着各种关联关系。下面对数据表的关联关系、关联表添加数据、关联表删除数据进行讲解。

数据表的关联关系

一对一

一对一关系的两张数据表建立外键时，要分清主从关系。 在数据表的主从关系中，从表需要主表的存在才有意义。

多对一

表之间的关系是通过外键建立的。在多对一的表关系中，应该将外键添加在“多”的一方，否则会造成数据的冗余。

多对多

通常情况下，为了实现多对多关联关系需要定义一张中间表（称为连接表），中间表会存在两个外键。?

需要注意的是，一对一关联关系在数据库中并不常见，因为以这种方式存储的信息通常会放在一个表中。在实际开发中，一对一关联关系可以应用于如下场景。

（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语句如下所示。