MySQL多表查询

最新推荐文章于 2024-09-20 21:26:51 发布

郭子不想改bug

最新推荐文章于 2024-09-20 21:26:51 发布

阅读量73

点赞数

分类专栏：数据库文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_39579781/article/details/120339255

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

连接查询

1、重复记录

### 1.1 distinct:

distinct 出现在job,deptno两个字段之前，表示两个字段联合起来去重；

mysql> select distinct job,deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| CLERK     |     30 |
| CLERK     |     10 |
+-----------+--------+

统计工作岗位的数量：

mysql> select count(job) from emp;
+------------+
| count(job) |
+------------+
|         14 |
+------------+

去重后：

mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+

2、连接查询

2.1 分类：

根据表连接的方式：

内连接：

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

外连接：

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

2.2 匹配时加条件：

select
	ename,dname
from 
	emp,dept
where
	emp.deptno = dept.deptno;
	
	
mysql> select
    -> ename,dname
    -> from
    -> emp,dept
    -> where
    -> emp.deptno = dept.deptno;
+--------+------------+
| 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)

2.2.1 表起别名：

select
	e.ename,d.dname
from 
	emp e,dept d
where
	e.deptno = d.deptno; //SQL 92

+--------+------------+
| 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      |
+--------+------------+

注意：表的连接次数越多，效率越低，尽量避免表的连接次数

2.3 内连接之等值连接：

案例：查询每个员工所在部门名称，显示员工名和部门名

emp 和 dept 表进行连接，条件是 e.deptno = d.deptno

select
	e.ename,d.dname
from 
	emp e,dept d
where
	e.deptno = d.deptno; //SQL 92


select
	e.ename,d.dname
from 
	emp e
join	
	dept d
on
	e.deptno = d.deptno; //SQL 99

2.3.1 SQL99语法

select 
	```
from 
	a
(inner) join //可省略
	b
on
	a和b的连接条件
where
	筛选条件

2.4 内连接之非等值连接

案例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级；

select 
	e.ename,e.sal,s.grade
from 
	emp e
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 |
+--------+---------+-------+

2.5 内连接之自连接

案例：查询员工的上级领导，要求显示员工名和对应的领导名

技巧：一张表看成两张表

emp a：员工表

emp b：领导表

select
	a.ename as 'emplyee',b.ename as 'boss'
from 
	emp a
join
	emp b
on 
	a.mgr = b.empno;

+---------+-------+
| emplyee | boss  |
+---------+-------+
| 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 |
+---------+-------+

2.6 外连接

2.6.1 右外连接

select
	e.ename,d.dname
from 
	emp e right join dept d //右外连接
on
	e.deptno = d.deptno; //SQL 99
	
+--------+------------+
| 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      |
| NULL   | OPERATIONS |
+--------+------------+

right代表：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表

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

2.6.2 左外连接：

select
	e.ename,d.dname
from 
	dept d 
left join 
	emp e 
on
	e.deptno = d.deptno; //SQL 99
	
	
+--------+------------+
| 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      |
| NULL   | OPERATIONS |
+--------+------------+

任何一个左连接和右连接都可互换

案例：查询每个员工的上级领导，要求显示所有员工的名字和领导名

select
	a.ename as 'emplyee',b.ename as 'boss'
from 
	emp a
left join
	emp b
on 
	a.mgr = b.empno;
	
+---------+-------+
| emplyee | boss  |
+---------+-------+
| SMITH   | FORD  |
| ALLEN   | BLAKE |
| WARD    | BLAKE |
| JONES   | KING  |
| MARTIN  | BLAKE |
| BLAKE   | KING  |
| CLARK   | KING  |
| SCOTT   | JONES |
| KING    | NULL  |
| TURNER  | BLAKE |
| ADAMS   | SCOTT |
| JAMES   | BLAKE |
| FORD    | JONES |
| MILLER  | CLARK |
+---------+-------+
14 rows in set (0.00 sec)

2.7 多张表连接

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

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

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

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

案例：找出每个员工的部门名称以及工资等级，还有上级领导

要求显示员工名，领导名，部门名，薪资、薪资的等级

select
	e.ename,e.sal,d.dname,s.grade,l.ename
from 
	emp e
join
	dept d
on 
	e.deptno = d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
left join 
	emp l
on
	e.mgr = l.empno;
	
+--------+---------+------------+-------+-------+
| ename  | sal     | dname      | grade | ename |
+--------+---------+------------+-------+-------+
| SMITH  |  800.00 | RESEARCH   |     1 | FORD  |
| ALLEN  | 1600.00 | SALES      |     3 | BLAKE |
| WARD   | 1250.00 | SALES      |     2 | BLAKE |
| JONES  | 2975.00 | RESEARCH   |     4 | KING  |
| MARTIN | 1250.00 | SALES      |     2 | BLAKE |
| BLAKE  | 2850.00 | SALES      |     4 | KING  |
| CLARK  | 2450.00 | ACCOUNTING |     4 | KING  |
| SCOTT  | 3000.00 | RESEARCH   |     4 | JONES |
| KING   | 5000.00 | ACCOUNTING |     5 | NULL  |
| TURNER | 1500.00 | SALES      |     3 | BLAKE |
| ADAMS  | 1100.00 | RESEARCH   |     1 | SCOTT |
| JAMES  |  950.00 | SALES      |     1 | BLAKE |
| FORD   | 3000.00 | RESEARCH   |     4 | JONES |
| MILLER | 1300.00 | ACCOUNTING |     2 | CLARK |
+--------+---------+------------+-------+-------+
14 rows in set (0.00 sec)