mysql之多表查询

站长

2023年03月19日 20:24 · 阅读数 14

链接查询

等值连接

等值连接的语法较为简单，语法如下：

SELECT table1.column1, table2.column2
FROM table1, table2
WHERE table1.column3 = table2.column4;

其中 SELECT 子句后可跟连接的表中任意字段，FROM 子句后跟用来连接的表，而 WHERE 子句后的 table1.column = table2.column 即为连接条件，也可以在 WHERE 子句中追加过滤条件对记录进行筛选。为了提高性能、避免字段名的使用产生歧义，在多表连接查询的时候，建议以 表名.字段名 的方式来书写 SELECT 子句。

精简名称：表别名

表别名和字段别名类似，就是给表起另外一个名字。不过两者的不同点在于，字段别名是为了让结果易于理解；而表别名则是为了让 SQL 语句简单化。表别名直接写在表名后即可，其语法如下：

SELECT t1.column1, t2.column2
FROM table1 t1, table2 t2
WHERE t1.column3 = t2.column4;

在 FROM 子句中原表名的后面定义别名，在其他子句中就可以使用别名了。

多表等值连接

如果连接的表超过两张，需要使用 AND 来组合多个等值条件，具体语法如下：

SELECT t1.column1, t2.column2, t3.column3
FROM table1 t1, table2 t2, table3 t3
WHERE t1.column4 = t2.column5 AND t1.column6 = t3.column7

等值连接的关键是找到表与表之间的桥梁（等值条件），通过桥梁构建一个信息更全面的表，以此达到多表查询的目的。

自然连接

在等值连接中，我们并没有强调用来连接的字段名必须相同，只要字段值相等即可进行连接。在等值连接中，如果用于连接的两个字段，其字段名与数据类型完全相同，换句话说，自然连接是特殊的等值连接，特殊之处在于多个表中用于连接的字段同名且同类型。自然连接使用关键字 NATURAL JOIN，其 SQL 语法如下：

SELECT t1.column1, t2.column2
FROM table1 t1
NATURAL JOIN table2 t2

使用该语法，解析引擎会自动探测两个表中相同的字段并设定等值条件，这样的字段可以不止一个，有多少个这样的字段就会生成多少个等值条件。相比显式等值连接来说语法更为简洁。可以看到，此 SQL 语句中没有使用 WHERE 子句，也没有出现连接符，但是也达到了等值连接的效果。这是因为，自然连接会自动去查找两个表中是否有相同的字段（字段名相同、字段类型也相同），找到后自动完成等值连接。如果连接的表中，没有相同字段，则会返回一个空结果

自然连接还会自动去掉重复列

自然连接需要 MySQL 判定表中相同的字段，在有多个相同字段时，如果想指定以某个字段进行等值连接，需要使用 JOIN……USING…… 语法来指定，其语法如下：

SELECT t1.column1, t2.column2
FROM table1 t1
JOIN table2 t2
USING(字段)

注意，该语法中不再使用 NATURAL JOIN 。其中字段是指 table1 和 table2 中相同的列。

需要注意：

USING 里面的字段不能加表名作前缀，该字段此时是一个连接字段，不再属于某张单独的表。
连接的表中必须要拥有相同字段才能使用 USING。除了 JOIN……USING…… 子句，还可以使用 JOIN……ON…… 子句完成类似的功能，其语法如下：

SELECT t1.column1, t2.column2
FROM table1 t1
JOIN table2 t2
ON(t1.字段 = t2.字段)

其中 ON 子句中的字段名可以不同。

需要注意：

使用 ON 子句，不会消除重复列，因为 ON 中的等值条件无需列相同；
使用 ON 子句，连接的字段名可以不同，相当于是 WHERE ** = ** 的一种替代品。下面我们看一个例子，完成对城市的名称、行政区名以及城市面积的查询，需要连接 city 表和 country。使用 ON 子句完成该查询，仅显示前 10 条记录，其 SQL 语句如下：

SELECT ci.Name, ci.District, co.SurfaceArea
FROM city ci JOIN country co
ON(ci.CountryCode = co.Code)
LIMIT 10;

输出结果：

mysql之多表查询

通过对 USING 子句和 ON 子句的使用，可以得出以下结论：

使用 USING 子句进行连接时，结果中用于连接的列不会重复出现；而 ON 子句的结果中，在不做干预的情况下，用于连接的列会出现两次。
使用 USING 子句时，连接的表必须有相同的字段；而 ON 子句可以不相同，使用比较灵活。

自连接

现在准备有一张员工信息表（employee），其信息如下表所示：

emp_id	emp_name	mgr_id	emp_salary
1001	章	NULL	8000
1002	力	1001	6000
1003	潘	1002	4000

employ 表建立语句如下：

CREATE TABLE employee
(
    emp_id INT NOT NULL,
    emp_name VARCHAR(10),
    mgr_id INT,
    emp_salary INT,
    PRIMARY KEY (emp_id)
);

数据插入语句如下：

INSERT INTO employee
VALUES
(1001, '章', NULL, 8000),
(1002, '力', 1001, 6000),
(1003, '潘', 1002, 4000);

emp_id 为 1001 的员工就是该企业最大的领导，所以其 mgr_id 为 NULL。查询所有员工的姓名以及上级领导的姓名，实现 SQL 语句如下：

SELECT e1.emp_name, e2.emp_name mgr
FROM employee e1, employee e2
WHERE e1.mgr_id = e2.emp_id;

输出结果：

mysql之多表查询

非等值内连接

非等值内连接，就是指连接的条件不是使用 = 计算，而是其它关系运算的结果。

比如现在小学成绩实行等级制，只给出 A、B、C、D、E 几个等级。但是无论哪个等级都需要对应一个成绩范围。那么在成绩表中，如何查询出等级达到等级 A 的学生？

笔者在这里建立 2 张表来说明这个问题。

第 1 张，学生成绩表（score），表结构：score(name, score)，信息如下表所示：

姓名 (name)	成绩 (score)
小明	95
小张	85
小李	75

score 表建立语句如下：

CREATE TABLE score (
    name VARCHAR(10) NOT NULL,
    score INT NOT NULL
);

数据插入语句如下：

INSERT INTO score VALUES
('小明', 95),
('小张', 85),
('小李', 75);

第 2 张，成绩等级表（level），表结构：level(level, low, high)，信息如下表所示：

等级 (level)	最低分 (low)	最高分 (high)
A	90	100
B	80	89
C	70	79
D	60	69
E	0	59

level 表建立语句如下：

CREATE TABLE level (
    level VARCHAR(1) NOT NULL,
    low INT NOT NULL,
    high INT NOT NULL
);

数据插入语句如下：

INSERT INTO level VALUES
('A', 90, 100),
('B', 80, 89),
('C', 70, 79),
('D', 60, 69),
('E', 0, 59);

现通过非等值内连接，实现对学生姓名及对应等级的查询，其 SQL 语句如下：

SELECT s.name, le.level
FROM score s, level le
WHERE score BETWEEN le.low AND le.high;

输出结果：

mysql之多表查询

左外连接

左外连接简称左连接，是指在两个表进行连接时，返回左表的全部记录及右表中符合条件的记录，右表没有匹配的记录用 NULL 补全。使用左连接的 SQL 语法如下：

SELECT * FROM table1 t1
LEFT JOIN table2 t2
ON(t1.column1 = t2.column2);

其中 LEFT JOIN 是 LEFT OUTER JOIN 的缩写。左连接的结果如下图阴影部分所示：

mysql之多表查询

右外连接

右外连接简称右连接，右连接刚好和左连接相反，返回右表的全部记录及左表中符合条件的记录，左表没有匹配的记录用 NULL 补全。使用右连接的 SQL 语法如下：

SELECT * FROM table1 t1
RIGHT JOIN table2 t2
ON(t1.column1 = t2.column2);

其中 RIGHT JOIN 是 RIGHT OUTER JOIN 的缩写。右连接的结果如下图阴影部分所示：

mysql之多表查询

特殊的交叉连接：笛卡尔积

笛卡尔积也叫交叉连接，原理就是一张表中的每一条记录都要和另一张表中的所有记录进行连接。如果两张表分别有 n 和 m 条记录，进行笛卡尔积的结果有 n*m 条记录。

笛卡尔积使用 JOIN 来连接，基本语法如下：

SELECT * FROM table1 JOIN table2;

继续以成绩表和等级表为例，进行笛卡尔积，其 SQL 语句如下：

SELECT * FROM score JOIN level;

输出结果：

mysql之多表查询简单来说，笛卡尔积是没有条件的连接，这会导致乘法效应，产生的数据量远远超过需要的数据，且多数配对不符合业务逻辑。在大多数情况下应尽量避免连接查询中笛卡尔积的出现。

子查询

单行子查询

只返回一行结果的子查询，称为单行子查询。对于单行子查询的结果我们可以使用单行操作符来构造外查询条件，如 >、<、= 等等。比如：采用子查询形式的 SQL 语句如下：

SELECT * FROM city
WHERE Population >=
(SELECT Population FROM city WHERE Name = 'Tokyo')
ORDER BY Population;

其含义是先查询城市 Tokyo 的人口数量，以此结果为算子，参与 >= 运算作为主查询的过滤条件。

多行子查询

返回多行结果的子查询，称为多行子查询。对于多行子查询的结果我们可以使用多行操作符来进一步构造查询条件，如 IN、ANY、ALL。

简单说一下这三个多行操作符的含义：

IN：等于多行子查询返回的结果中的任意一个即可；
ANY：和多行子查询返回的某一个值进行比较即可；
ALL：和多行子查询返回的所有值进行比较。

下面就这三个多行子查询操作运算符进行举例：

查询所有和代号为 ABW 的国家有共同语言的国家，实现的 SQL 语句如下：

SELECT DISTINCT CountryCode
FROM countrylanguage
WHERE Language IN (SELECT Language FROM countrylanguage WHERE CountryCode='ABW');

结果解析：子查询首先会查出 ABW 这个国家有 4 个语言，然后利用 IN 运算符把使用 4 种语言之一的国家编码显示出来，最后利用 DISTINCT 去掉重复行。 2. 查询人口数比 TTO 这个国家下任意城市人口数小的城市信息，换句话说，对某一城市 X，只要 TTO 下任一城市人口数比 X 人口数大，那么 X 就进入查询结果。实现的 SQL 语句如下：

SELECT * FROM city
WHERE population < ANY (SELECT population FROM city WHERE countrycode = 'TTO')
AND countrycode <> 'TTO';

结果解析：在 city 表中 TTO 这个国家共有两个城市，人口数量分别是 56601 和 43396，通过子查询先获取到这两个数据。然后利用 ANY 操作符，只要比这两个数据中任何一个小的城市信息都被查询出来。这里面包含了 TTO 这个国家本身的城市，与题意不合。所以在主查询后面加了个条件，排除 TTO 这个国家的城市。 3. 查询人口数量比 TTO 这个国家所有城市人口数量都小的城市信息，换句话说，人口数小于 TTO 下城市最小人口数的城市会进入查询结果。实现的 SQL 语句如下：

SELECT * FROM city
WHERE population < ALL (SELECT population FROM city WHERE countrycode = 'TTO');

结果解析：使用 ALL 操作运算符意味着要和子查询所有结果比较（而不是任一），所以结果数据量比使用 ANY 要少（但在结果集为空等特殊情况下，ALL 和 ANY 的结果集可能相同）

EXISTS 相关子查询

不相关子查询：子查询的查询条件不依赖于父查询的称为不相关子查询。
相关子查询：子查询的查询条件依赖于外层父查询的某个属性值的称为相关子查询，带 EXISTS 的子查询就是相关子查询。

EXISTS 表示存在量词：带有 EXISTS 的子查询不返回任何记录的数据，只返回逻辑值 True 或 False。

相关子查询执行过程：先在外层查询中取第一行记录，用该记录的相关的属性值（在内层 WHERE 子句中给定的）处理内层查询，若外层的 WHERE 子句返回 True 值，则这条记录放入结果表中。然后再取下一行记录，重复上述过程直到外层表的记录全部遍历一次为止。已知国家名 Netherlands ，查询该国所有城市信息：

SELECT * FROM city c1
WHERE EXISTS
(SELECT * FROM country c2 WHERE c1.CountryCode = c2.Code AND c2.Name = 'Netherlands');

显然，该语句可改写为：

SELECT * FROM city c1
WHERE c1.countrycode =
(SELECT code FROM country WHERE name = 'Netherlands');

就这两条语句而言，第二条的性能优于第一条。EXISTS 语句和其它子查询的性能比较，是一个复杂的问题，此处不打算深究，但可以给大家一个简单的规则做参考：子查询结果大（行数多）可用 EXISTS，子查询结果小可用 = 或 IN 等子查询。