MySQL 基础语法,联结查询相关。

0. 数据表

接下来的示例会使用 MySQL 自动生成的 compony 数据库,里面有三张表(dept部门表,emp员工表,salgrade薪资表)。

dept部门表 emp员工表 salgrade薪资等级表
deptno (部门编号) empno(员工编号) grade(等级)
dname(部门名称) ename(员工名称) losal(最低工资)
loc(位置) job(职位) hisal(最高工资)
mgr(上司员工编号)
hiredate(入职时间)
sal(工资)
comm(佣金)
deptno(部门编号)

1. 子查询

嵌套在其他查询语句的查询。

将子查询作为过滤条件

假如,现在需要查询所有大于平均工资的员工信息,应该如何查询?下面列出具体步骤:

  1. 查询所有员工的平均工资。
  2. 根据获得的平均工资查询工资大于平均工资的员工信息。
// 查询所有员工的平均工资。
SELECT AVG(sal) AS avg_sal FROM emp;
// 获得出平均工资: 2073.2143
+-----------+
| avg_sal |
+-----------+
| 2073.2143 |
+-----------+
1 row in set (0.01 sec)
// 查询工资大于平均工资(2073.2143)的员工信息
SELECT * FROM emp WHERE sal > 2073.2143;
// 所有大于平均工资的员工信息
+-------+-------+-----------+------+---------------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+---------------------+------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 14:25:53 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 14:14:14 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 12:12:12 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 12:12:12 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 12:12:12 | 5000 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 12:12:12 | 3000 | NULL | 20 |
+-------+-------+-----------+------+---------------------+------+------+--------+
6 rows in set (0.03 sec)

从上面的示例发现,其实 avg_sal(2073.2143) 等同于 SELECT AVG(sal) AS avg_sal FROM emp;。所以可以将它作为子查询嵌套到另一条查询语句中。

// 查询所有大于平均工资的员工信息
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) AS avg_sal FROM emp);
// 所有大于平均工资的员工信息
+-------+-------+-----------+------+---------------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+---------------------+------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 14:25:53 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 14:14:14 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 12:12:12 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 12:12:12 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 12:12:12 | 5000 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 12:12:12 | 3000 | NULL | 20 |
+-------+-------+-----------+------+---------------------+------+------+--------+
6 rows in set (0.04 sec)

将子查询作为计算字段

假如,现在需要查询每个部门的信息以及人数,应该如何查询?下面列出具体步骤:

  1. 从部门表中查询出部门列表。
  2. 根据每个部门,统计在员工表中的数目。
// 查询出部门列表
SELECT * FROM dept ORDER BY deptno;
// 部门列表
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.04 sec)
// 根据每个部门,统计在员工表中的数目
SELECT COUNT(*) AS emps FROM emp WHERE deptno = '10';
// 编号 10 的员工数
+------+
| emps |
+------+
| 3 |
+------+
1 row in set (0.04 sec)

同样我们将两条查询语句组合起来。

// 查询每个部门的信息以及人数
SELECT
dname, (SELECT COUNT(*) FROM emp WHERE emp.deptno = dept.deptno) AS emps
FROM
dept
ORDER BY deptno;
// 每个部门的信息以及人数
+------------+------+
| dname | emps |
+------------+------+
| ACCOUNTING | 3 |
| RESEARCH | 5 |
| SALES | 6 |
| OPERATIONS | 0 |
+------------+------+
4 rows in set (0.04 sec)

外层的查询语句就是用来查询部门列表的,如何将查询出的部门编号传给子查询作为子查询的过滤条件,然后将子查询的结果作为外层查询的字段输出。

注意嵌套查询中的过滤条件 WHERE emp.deptno = dept.deptno 。如果列名是相同的要指明是哪一张表的列。

2. JION 联结查询语句

将具有某些关系的两张表联结在一起。

外键,笛卡尔积

外键

外键是作为某一个表中的一列,它包含另一张表的主键值,定义了两张表之间的关系。比如说,员工表包含了部门表的部门编号,这说明了员工与部门之间的关系。

笛卡儿积

在联结查询中,SQL 是不会考虑两张表之间的关系的。它会把一张表的一行与另一张表的所有行进行匹配,所产生的结果数就是这两张表的行数的笛卡尔积。

// 查询每个员工的部门编号与部门名称
SELECT
emp.ename, emp.deptno, dept.dname
FROM
emp
JOIN
dept
ORDER BY ename;
// 产生笛卡儿积现象
+--------+--------+------------+
| ename | deptno | dname |
+--------+--------+------------+
| ADAMS | 20 | ACCOUNTING |
| ADAMS | 20 | RESEARCH |
| ADAMS | 20 | SALES |
| ADAMS | 20 | OPERATIONS |
| ALLEN | 30 | RESEARCH |
| ALLEN | 30 | SALES |
| ALLEN | 30 | OPERATIONS |
| ALLEN | 30 | ACCOUNTING |
| BLAKE | 30 | RESEARCH |
| BLAKE | 30 | SALES |
| BLAKE | 30 | OPERATIONS |
| BLAKE | 30 | ACCOUNTING |
| CLARK | 10 | ACCOUNTING |
| CLARK | 10 | RESEARCH |
| CLARK | 10 | SALES |
| CLARK | 10 | OPERATIONS |
| FORD | 20 | ACCOUNTING |
| FORD | 20 | RESEARCH |
| FORD | 20 | SALES |
| FORD | 20 | OPERATIONS |
| JAMES | 30 | RESEARCH |
| JAMES | 30 | SALES |
| JAMES | 30 | OPERATIONS |
| JAMES | 30 | ACCOUNTING |
| JONES | 20 | RESEARCH |
| JONES | 20 | SALES |
| JONES | 20 | OPERATIONS |
| JONES | 20 | ACCOUNTING |
| KING | 10 | ACCOUNTING |
| KING | 10 | RESEARCH |
| KING | 10 | SALES |
| KING | 10 | OPERATIONS |
| MARTIN | 30 | ACCOUNTING |
| MARTIN | 30 | RESEARCH |
| MARTIN | 30 | SALES |
| MARTIN | 30 | OPERATIONS |
| MILLER | 10 | RESEARCH |
| MILLER | 10 | SALES |
| MILLER | 10 | OPERATIONS |
| MILLER | 10 | ACCOUNTING |
| SCOTT | 20 | RESEARCH |
| SCOTT | 20 | SALES |
| SCOTT | 20 | OPERATIONS |
| SCOTT | 20 | ACCOUNTING |
| SMITH | 20 | ACCOUNTING |
| SMITH | 20 | RESEARCH |
| SMITH | 20 | SALES |
| SMITH | 20 | OPERATIONS |
| TURNER | 30 | RESEARCH |
| TURNER | 30 | SALES |
| TURNER | 30 | OPERATIONS |
| TURNER | 30 | ACCOUNTING |
| WARD | 30 | ACCOUNTING |
| WARD | 30 | RESEARCH |
| WARD | 30 | SALES |
| WARD | 30 | OPERATIONS |
+--------+--------+------------+
56 rows in set (0.10 sec)

在上面的示例中,使用 JOIN 子句将员工表(emp)与部门表(dept)联结到一起。每位员工都匹配了四个不同的部门名称,但是只有一个是正确的。为了消除错误的结果,需要我们指明两张表之间的关系,而员工表中的部门编号就定义了哪个员工是哪个部门的。

// 查询每个员工的部门编号与部门名称
SELECT
emp.ename, emp.deptno, dept.dname
FROM
emp
JOIN
dept
ON
emp.deptno = dept.deptno
ORDER BY ename;
// 每个员工的部门编号与部门名称
+--------+--------+------------+
| ename | deptno | dname |
+--------+--------+------------+
| ADAMS | 20 | RESEARCH |
| ALLEN | 30 | SALES |
| BLAKE | 30 | SALES |
| CLARK | 10 | ACCOUNTING |
| FORD | 20 | RESEARCH |
| JAMES | 30 | SALES |
| JONES | 20 | RESEARCH |
| KING | 10 | ACCOUNTING |
| MARTIN | 30 | SALES |
| MILLER | 10 | ACCOUNTING |
| SCOTT | 20 | RESEARCH |
| SMITH | 20 | RESEARCH |
| TURNER | 30 | SALES |
| WARD | 30 | SALES |
+--------+--------+------------+
14 rows in set (0.07 sec)

使用 ON 指明两张表的联结条件关系。利用外键,我们就可以从四条不同的数据中找出了正确的数据了。

INNER JOIN 内联结

内联结分为等值联结,非等值联结,自联结三种。

等值联结

两张表的联结条件关系是等值的,称为等值连接。

上面的例子就是一个典型的等值联结示例。

// 查询每个员工的部门编号与部门名称
SELECT
emp.ename, emp.deptno, dept.dname
FROM
emp
JOIN
dept
ON
emp.deptno = dept.deptno
ORDER BY ename;

两张表之间的关系是等值的,员工表的部门编号等于部门表中的部门编号。

非等值联结

与等值联结相对,两张表的联结条件关系是非等值的,称为非等值联结。

假如,现在需要查询所有员工的工资等级,应该如何查询?每个员工都有工资,而薪资等级表保存了每个等级的工资范围。所以需要查询每个员工的工资在哪个薪资等级的范围内。

// 查询所有员工的工资等级
SELECT
emp.ename, salgrade.grade
FROM
emp
JOIN
salgrade
ON
emp.sal BETWEEN salgrade.losal AND salgrade.hisal
ORDER BY emp.ename;
// 所有员工的工资等级
+--------+-------+
| ename | grade |
+--------+-------+
| ADAMS | 1 |
| ALLEN | 3 |
| BLAKE | 4 |
| CLARK | 4 |
| FORD | 4 |
| JAMES | 1 |
| JONES | 4 |
| KING | 5 |
| MARTIN | 2 |
| MILLER | 2 |
| SCOTT | 4 |
| SMITH | 1 |
| TURNER | 3 |
| WARD | 2 |
+--------+-------+
14 rows in set (0.07 sec)

等值联结与非等值联结非常相似,其中最大的区别在于联结条件关系。

自联结

同一张表进行联结查询,称为自联结。

什么情况下会使用到自联结?假如,现在需要查询每个员工的上级名称,应该如何查询?员工表中保存着每个员工的上级员工编号,而上级员工编号对应的就是员工表中的员工。

// 查询每个员工的上级名称
SELECT
emp.ename, mgr_emp.ename AS mgr_name
FROM
emp
JOIN
emp mgr_emp
ON
emp.mgr = mgr_emp.empno;
// 每个员工的上级名称
+--------+----------+
| ename | mgr_name |
+--------+----------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+----------+
13 rows in set (0.06 sec)

由于使用同一张表进行联结,所以需要给两张表取别名,用于区分这两张表(通常给表取别名可以省略 AS 关键字)。

将子查询作为临时表

除了前一章节介绍子查询的两种用法外,还可以将子查询出的结果作为一张临时表,和其他表进行联结查询。

假如,现在需要查询每个部门各薪资等级的人数,应该如何查询?下面列出具体步骤:

  1. 查询所有员工的薪资等级,并根据部门编号和薪资等级进行分组。
  2. 将查询出的结果和部门表进行联结,获取部门名称。
// 查询所有员工的薪资等级,并根据部门编号和薪资等级进行分组
SELECT
emp.deptno, salgrade.grade, COUNT(*) AS emp_num
FROM
emp
JOIN
salgrade
ON
emp.sal BETWEEN salgrade.losal AND salgrade.hisal
GROUP BY emp.deptno, salgrade.grade;
// 所有员工的薪资等级,并根据部门编号和薪资等级进行分组
+--------+-------+---------+
| deptno | grade | emp_num |
+--------+-------+---------+
| 20 | 1 | 2 |
| 30 | 3 | 2 |
| 30 | 2 | 2 |
| 20 | 4 | 3 |
| 30 | 4 | 1 |
| 10 | 4 | 1 |
| 10 | 5 | 1 |
| 30 | 1 | 1 |
| 10 | 2 | 1 |
+--------+-------+---------+
9 rows in set (0.07 sec)
// 查询每个部门各薪资等级的人数,将查询出的结果和部门表进行联结
SELECT
dept.dname, dept.deptno, t.grade, t.emp_num
FROM
(SELECT
emp.deptno, salgrade.grade, COUNT(*) AS emp_num
FROM
emp
JOIN
salgrade
ON
emp.sal BETWEEN salgrade.losal AND salgrade.hisal
GROUP BY emp.deptno, salgrade.grade) t
JOIN
dept
ON
dept.deptno = t.deptno
ORDER BY dept.deptno, t.grade;
// 每个部门各薪资等级的人数
+------------+--------+-------+---------+
| dname | deptno | grade | emp_num |
+------------+--------+-------+---------+
| ACCOUNTING | 10 | 2 | 1 |
| ACCOUNTING | 10 | 4 | 1 |
| ACCOUNTING | 10 | 5 | 1 |
| RESEARCH | 20 | 1 | 2 |
| RESEARCH | 20 | 4 | 3 |
| SALES | 30 | 1 | 1 |
| SALES | 30 | 2 | 2 |
| SALES | 30 | 3 | 2 |
| SALES | 30 | 4 | 1 |
+------------+--------+-------+---------+
9 rows in set (0.07 sec)

内联结的完整语法是:INNER JOIN。通常会将 INNER 省略。

OUTER JOIN 外联结

外联结分为左外联结(左联结),右外联结(右联结)两种。

左联结和右联结几乎一摸一样,因为每一种左联结查询都有一种右联结查询的写法,反过来也一样。

左联结

联结两张表,以左边的表为主表,右边的表为副表,称为左联结。

主表与副表的区别就是,主表是主要查询的数据,而副表是附属的查询数据。具体区别通过示例来说明。

假如,现在需要查询每个员工的上级名称,应该如何查询?这是自联结章节的示例,在自联结中返回了 13 条数据,但是员工表有 14 个员工。这是因为 king 员工没有上级,所以自联结将 king 的数据给去除了。我们再使用左联结进行查询。

// 查询每个员工的上级名称
SELECT
emp.ename, mgr_emp.ename AS mgr_name
FROM
emp
LEFT JOIN
emp mgr_emp
ON
emp.mgr = mgr_emp.empno
ORDER BY emp.ename;
// 每个员工的上级名称
+--------+----------+
| ename | mgr_name |
+--------+----------+
| ADAMS | SCOTT |
| ALLEN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| FORD | JONES |
| JAMES | BLAKE |
| JONES | KING |
| KING | NULL |
| MARTIN | BLAKE |
| MILLER | CLARK |
| SCOTT | JONES |
| SMITH | FORD |
| TURNER | BLAKE |
| WARD | BLAKE |
+--------+----------+
14 rows in set (0.07 sec)

查询后发现返回了 14 条数据,而 king 对应的是 NULL。在上面的示例中,左表是 emp,右表是 mgr_emp。所以 emp 是主要查询的数据,就算是 king 没有对应的上级也会查询出来。

再来一个示例,现在需要查询每个部门的人数,应该如何查询?

使用等值联结(内连接):

// 查询每个部门的人数
SELECT
dept.deptno, dept.dname, COUNT(emp.deptno) AS emp_num
FROM
dept
JOIN
emp
ON
dept.deptno = emp.deptno
GROUP BY dept.deptno
ORDER BY dept.deptno;
// 每个部门的人数
+--------+------------+---------+
| deptno | dname | emp_num |
+--------+------------+---------+
| 10 | ACCOUNTING | 3 |
| 20 | RESEARCH | 5 |
| 30 | SALES | 6 |
+--------+------------+---------+
3 rows in set (0.05 sec)

有四个部门却只返回了 3 条数据。

使用左联结:

// 查询每个部门的人数
SELECT
dept.deptno, dept.dname, COUNT(emp.deptno) AS emp_num
FROM
dept
LEFT JOIN
emp
ON
dept.deptno = emp.deptno
GROUP BY dept.deptno
ORDER BY dept.deptno;
// 每个部门的人数
+--------+------------+---------+
| deptno | dname | emp_num |
+--------+------------+---------+
| 10 | ACCOUNTING | 3 |
| 20 | RESEARCH | 5 |
| 30 | SALES | 6 |
| 40 | OPERATIONS | 0 |
+--------+------------+---------+
4 rows in set (0.06 sec)

在上面的示例中,左表是 dept,右表是 emp。所以 dept是主要查询的数据,即使 40 部门没有员工仍然返回了 4 条数据。

通过两个示例,说明了主表与副表的区别,也同时说明了内联结与外联结的区别(外联结拥有一张作为主要查询的表,而内联结每一张表都是等同的)。

右联结

联结两张表,以右边的表为主表,左边的表为副表,称为右联结。

前面提到右联结与左联结大同小异,所以直接将上一个的示例变成右联结查询。

现在需要查询每个部门的人数,应该如何查询?

// 查询每个部门的人数
SELECT
dept.deptno, dept.dname, COUNT(emp.deptno) AS emp_num
FROM
emp
RIGHT JOIN
dept
ON
dept.deptno = emp.deptno
GROUP BY dept.deptno
ORDER BY dept.deptno;
// 每个部门的人数
+--------+------------+---------+
| deptno | dname | emp_num |
+--------+------------+---------+
| 10 | ACCOUNTING | 3 |
| 20 | RESEARCH | 5 |
| 30 | SALES | 6 |
| 40 | OPERATIONS | 0 |
+--------+------------+---------+
4 rows in set (0.06 sec)

在上面的示例中,左表是 emp,右表是 dept。所以 dept是主要查询的数据。仅仅将两张表的位置调转了一下,然后将 LEFT JOIN 替换成 RIGHT JOIN 而已。

外联结的完整语法是:LEFT OUTER JOIN(RIGHT OUTER JOIN)。通常会将 OUTER 省略。

评论