MySQL_03SQL连接查询

2022-03-13 03:43:30

MySQL_03SQL连接查询

1.什么是连接查询？

从一张表中单独查询，称为单表查询。

多张表联合起来查询，被称为连接查询。

比如从emp表中取员工名字，同时从dept表中取部门名字，就是一种跨表查询。需要使用连接查询才可以实现。

2.连接查询的分类

2.1根据语法的年代分类

SQL92：1992年的时候出现的语法
SQL99：1999年的时候出现的语法(重点学习)

2.2根据表连接的方式分类

1.内连接：

等值连接
非等值连接
自连接

2.外连接：

左外连接（左连接）
右外连接（右连接）

3.全连接(了解)

3.笛卡尔积现象

3.1什么是笛卡尔积现象？

当两张表进行连接查询，且没有任何条件限制时，最终查询结果的数据条数会是两张表各自数据条数的乘积。这种现象被称为：笛卡尔积现象。

dept表：4条数据

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.01 sec)

emp表：14条数据

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.01 sec)

现在查询dept表中的部门名以及emp表中的员工名，不加以任何条件限制：

mysql> select ename,dname from emp,dept;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | ACCOUNTING |
| SMITH  | RESEARCH   |
| SMITH  | SALES      |
| SMITH  | OPERATIONS |
| ALLEN  | ACCOUNTING |
| ALLEN  | RESEARCH   |
| ALLEN  | SALES      |
| ALLEN  | OPERATIONS |
| WARD   | ACCOUNTING |
| WARD   | RESEARCH   |
...
56 rows in set (0.00 sec)

由结果来看，查询的数据总条数为14*4=56条。

从而可见在两个表之间进行连接查询，就是拿前一个表中的每一条数据挨个与后一个表中的所有数据进行匹配，类似于双重循环嵌套。

3.2如何避免笛卡尔积现象？

连接时加条件，满足这个条件的记录就会被筛选出来。

如：

#表起别名。很重要，效率问题。
mysql> select e.ename,d.dname
    -> from emp e,dept d
    -> where e.deptno = d.deptno;#SQL92语法
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+
14 rows in set (0.01 sec)

最终查询结果只有14条，但是匹配的次数还是56次，只不过进行了四选一筛选。

注意：通过笛卡尔积现象得出，表的连接次数越多，查询的效率就越低。所以我们要尽可能减少连接次数。

4.内连接

内连接的特点是查询出的所有结果都符合连接条件。

4.1等值连接

当两个表进行连接查询时，如果连接的条件是等量关系，那么就称之为等值连接。

案例：查询每个员工所在的部门名称，显示除SALES部门外的员工名和部门名。

SQL92语法:

select e.ename,d.dname 
from emp e,dept d 
where e.deptno = d.deptno and d.dname != ‘sales‘;

SQL99语法：

select e.ename,d.dname
from emp e
join dept d
on e.deptno = d.deptno
where d.dname != ‘sales‘;

对比可见：

SQL92的缺点：
- 结构不清晰，表的连接条件和后续进一步的筛选条件都放到了where后面。
SQL99的优点：
- 表的连接条件是独立的，连接之后，如果还需要进一步筛选，可以往后再添加where语句。

SQL99的语法格式：

select 字段名1,字段名2...
from 表1
join 表2
on 表1和表2的连接条件
where 筛选条件

SQL99还可以在join前添加inner关键字，便于区分为内连接。

inner可以省略，但带着inner可读性更好，可以一眼看出来是内连接。

select e.ename,d.dname
from emp e
inner join dept d
on e.deptno = d.deptno
where d.dname != ‘sales‘;

4.2非等值连接

当两个表进行连接查询时，如果连接的条件不是等量关系，那么就称之为非等值连接。

案例：查询所有员工的薪资等级，显示员工名、薪资和薪资等级。

emp表：

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

salgrade表：

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

开始查询：

mysql> select e.ename,e.sal,s.grade
    -> from emp e
    -> inner join salgrade s
    -> on e.sal between s.losal and s.hisal;#连接条件不是等量关系
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.00 sec)

4.3自连接

emp表中每一个员工都有一个对应的领导编号(mgr)，该领导编号是其对应领导的员工编号(empno)。

如：SMITH的领导就是FORD。

mysql> select empno,ename,mgr from emp;
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+
14 rows in set (0.01 sec)

现需要查询员工的上级领导，要求显示员工名和对应的领导名。

思路：将一张表看作两张表。

emp a 员工表：

+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+

emp b 领导表

+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+

连接条件：员工对应的领导编号(MGR) = 领导的员工编号(empno) ，也就是：a.mgr = b.empno

开始查询：

mysql> select a.ename ‘worker‘,b.ename ‘leader‘
    -> from emp a
    -> join emp b
    -> on a.mgr = b.empno;
+--------+--------+
| worker | leader |
+--------+--------+
| 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.01 sec)

名字为KING 的是老板，没有领导，故查询结果只有13条。

5.外连接

emp表：

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

dept表：

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

5.1内连接特点

内连接的特点是查询出的结果都符合连接条件。如下面例子：

查询所有员工所在的部门名称，显示员工名、部门名和部门编号。

mysql> select a.ename,b.dname,b.deptno from emp a join dept b on a.deptno = b.deptno;
+--------+------------+--------+
| ename  | dname      | deptno |
+--------+------------+--------+
| CLARK  | ACCOUNTING |     10 |
| KING   | ACCOUNTING |     10 |
| MILLER | ACCOUNTING |     10 |
| SMITH  | RESEARCH   |     20 |
| JONES  | RESEARCH   |     20 |
| SCOTT  | RESEARCH   |     20 |
| ADAMS  | RESEARCH   |     20 |
| FORD   | RESEARCH   |     20 |
| ALLEN  | SALES      |     30 |
| WARD   | SALES      |     30 |
| MARTIN | SALES      |     30 |
| BLAKE  | SALES      |     30 |
| TURNER | SALES      |     30 |
| JAMES  | SALES      |     30 |
+--------+------------+--------+

查询出的所有结果都符合a.deptno = b.deptno这一条件。

5.2外连接特点

dept表中的部门编号还有一个是40，而emp表中没有员工是该部门的。

当部门编号为40时，a.deptno = b.deptno条件不成立，所以内连接的查询结果无法将该部门显示出来。

假如我们现在需要将所有部门中的员工都列出来，该怎么做？

现在的需求要求将所有部门也列出来，无论该部门中是否有员工。

这是内连接无法做到的。这时我们可以用外连接来实现：

select e.ename,d.dname,d.deptno 
from emp e right join dept d 
on e.deptno = d.deptno;

结果：

+--------+------------+--------+
| ename  | dname      | deptno |
+--------+------------+--------+
| CLARK  | ACCOUNTING |     10 |
| KING   | ACCOUNTING |     10 |
| MILLER | ACCOUNTING |     10 |
| SMITH  | RESEARCH   |     20 |
| JONES  | RESEARCH   |     20 |
| SCOTT  | RESEARCH   |     20 |
| ADAMS  | RESEARCH   |     20 |
| FORD   | RESEARCH   |     20 |
| ALLEN  | SALES      |     30 |
| WARD   | SALES      |     30 |
| MARTIN | SALES      |     30 |
| BLAKE  | SALES      |     30 |
| TURNER | SALES      |     30 |
| JAMES  | SALES      |     30 |
| NULL   | OPERATIONS |     40 |#部门编号为40的数据也列出来了。
+--------+------------+--------+
15 rows in set (0.00 sec)

外连接分为左外连接和右外连接。

上面用的是右外连接：

select e.ename,d.dname,d.deptno 
from emp e right join dept d 
on e.deptno = d.deptno;

右外连接表示本次查询以join关键字右边的这张表为主，主要是为了将这张表中的数据全部查询出来，无论是否符合连接条件。同时捎带着关联查询左边的表。

左外连接与右外连接一样，只是将左边的表当作主表。

mysql> select e.ename,d.dname,d.deptno
    -> from dept d
    -> left join emp e
    -> on d.deptno = e.deptno;
+--------+------------+--------+
| ename  | dname      | deptno |
+--------+------------+--------+
| CLARK  | ACCOUNTING |     10 |
| KING   | ACCOUNTING |     10 |
| MILLER | ACCOUNTING |     10 |
| SMITH  | RESEARCH   |     20 |
| JONES  | RESEARCH   |     20 |
| SCOTT  | RESEARCH   |     20 |
| ADAMS  | RESEARCH   |     20 |
| FORD   | RESEARCH   |     20 |
| ALLEN  | SALES      |     30 |
| WARD   | SALES      |     30 |
| MARTIN | SALES      |     30 |
| BLAKE  | SALES      |     30 |
| TURNER | SALES      |     30 |
| JAMES  | SALES      |     30 |
| NULL   | OPERATIONS |     40 |
+--------+------------+--------+
15 rows in set (0.00 sec)

在外连接中，两张表连接，产生了主次关系。

总结：

带有right的是右外连接，又叫右连接
带有left的是左外连接，又叫左连接
往什么方向连接，就把什么方向的表当作主表
任何一个右连接都有左连接的写法
任何一个左连接都有右连接的写法
在外连接中，可以在join前加outer，增加可读性。
外连接的查询结果条数一定 >= 内连接的查询结果条数

6.连接三张以上的表

6.1如何连接三张以上的表？

连接大于等于三张表的语法：

select ...
from a
join b
on a和b的连接条件
join c
on a和c的连接条件
join d
on a和d的连接条件
...

连接的过程：有两种理解，都可以。

a表和b表先连接，然后拿a表和b表的连接联合连接c表，然后拿以上所有的连接联合连接d表。以此类推。
先拿a表和b表连接，然后拿a表和c表连接，然后拿a表和d表连接。

一条SQL中内连接和外连接可以同时存在。

6.2案例一

案例：找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级。

select e.ename,d.dname,e.sal,s.grade
from emp e
join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between losal and hisal;

结果：

+--------+------------+---------+-------+
| ename  | dname      | sal     | grade |
+--------+------------+---------+-------+
| SMITH  | RESEARCH   |  800.00 |     1 |
| ALLEN  | SALES      | 1600.00 |     3 |
| WARD   | SALES      | 1250.00 |     2 |
| JONES  | RESEARCH   | 2975.00 |     4 |
| MARTIN | SALES      | 1250.00 |     2 |
| BLAKE  | SALES      | 2850.00 |     4 |
| CLARK  | ACCOUNTING | 2450.00 |     4 |
| SCOTT  | RESEARCH   | 3000.00 |     4 |
| KING   | ACCOUNTING | 5000.00 |     5 |
| TURNER | SALES      | 1500.00 |     3 |
| ADAMS  | RESEARCH   | 1100.00 |     1 |
| JAMES  | SALES      |  950.00 |     1 |
| FORD   | RESEARCH   | 3000.00 |     4 |
| MILLER | ACCOUNTING | 1300.00 |     2 |
+--------+------------+---------+-------+
14 rows in set (0.00 sec)

6.3案例二

现在，案例升级：

找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资和薪资等级。

select ea.ename ‘worker‘,eb.ename ‘leader‘,d.dname,ea.sal,s.grade
from emp ea
join dept d
on ea.deptno = d.deptno
join salgrade s
on ea.sal between losal and hisal;
left join emp eb#左外连接，以ea表为主表，防止king不显示
on ea.mgr = eb.empno

结果：

+--------+--------+------------+---------+-------+
| worker | leader | dname      | sal     | grade |
+--------+--------+------------+---------+-------+
| SMITH  | FORD   | RESEARCH   |  800.00 |     1 |
| ALLEN  | BLAKE  | SALES      | 1600.00 |     3 |
| WARD   | BLAKE  | SALES      | 1250.00 |     2 |
| JONES  | KING   | RESEARCH   | 2975.00 |     4 |
| MARTIN | BLAKE  | SALES      | 1250.00 |     2 |
| BLAKE  | KING   | SALES      | 2850.00 |     4 |
| CLARK  | KING   | ACCOUNTING | 2450.00 |     4 |
| SCOTT  | JONES  | RESEARCH   | 3000.00 |     4 |
| KING   | NULL   | ACCOUNTING | 5000.00 |     5 |
| TURNER | BLAKE  | SALES      | 1500.00 |     3 |
| ADAMS  | SCOTT  | RESEARCH   | 1100.00 |     1 |
| JAMES  | BLAKE  | SALES      |  950.00 |     1 |
| FORD   | JONES  | RESEARCH   | 3000.00 |     4 |
| MILLER | CLARK  | ACCOUNTING | 1300.00 |     2 |
+--------+--------+------------+---------+-------+
14 rows in set (0.00 sec)

MySQL_03SQL连接查询

码农公寓

MySQL_03SQL连接查询

1.什么是连接查询？

2.连接查询的分类

2.1根据语法的年代分类

2.2根据表连接的方式分类

3.笛卡尔积现象

3.1什么是笛卡尔积现象？

3.2如何避免笛卡尔积现象？

4.内连接

4.1等值连接

4.2非等值连接

4.3自连接

5.外连接

5.1内连接特点

5.2外连接特点

6.连接三张以上的表

6.1如何连接三张以上的表？

6.2案例一

6.3案例二

相关文章