多表查询综合归纳

目录

1. 多表关系

1.1 一对多（多对一）

1.2 多对多

 1.3 一对一

2. 多表查询概述

2.1 熟悉表

2.2 笛卡尔积

2.3 消除笛卡尔积

2.4 多表查询分类

3. 内连接

3.1 隐式内连接

3.2 显式内连接

4. 外连接

4.1 左外连接

4.2 右外连接

5. 自连接

5.1 自连接查询 

5.2 外连接查询

6. 联合查询

6.1 union all 

6.2 union

6.3 使用联合查询条件

7. 嵌套/子查询

7.1 标量子查询

7.2 列子查询

1） in 的使用

2）all 的使用

3） any 的使用

7.3 行子查询

7.4 表子查询

8. 多表查询案例

9. 总结

9.1 多表关系

 9.2 多比查询

​编辑

 10 源代码(复制自取)

---

1. 多表关系

1.1 一对多（多对一）

1.2 多对多

 1.3 一对一

2. 多表查询概述

 

2.1 熟悉表

在接下来的学习中，我们暂且需要使用这两张表，因此，十分有必要熟悉表的结构及其内容;

1. 员工表

2. 部门表

2.2 笛卡尔积

2.3 消除笛卡尔积

在上述多表查询中，可以查到这样的元组。

 显然，有许多信息是我们不需要的，接下来就需要过滤信息，消除笛卡尔积；

2.4 多表查询分类

3. 内连接

内连接又分为隐式内连接和显示内连接；

3.1 隐式内连接

先执行 from ，因此表取别名后，仅允许使用表名；

3.2 显式内连接

显示内连接中， innet 常省略不写；

4. 外连接

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

此外，左外连接可以改为右外连接，右外连接可以改为左外连接；

4.1 左外连接

左外连接中，out 通常省略；

4.2 右外连接

5. 自连接

5.1 自连接查询 

其使用如下案例所示：

5.2 外连接查询

6. 联合查询

6.1 union all 

这是薪资低于 5000 的员工信息

这是年龄大于 50 的员工信息

这是将两个条件的查询结果联合起来的表

6.2 union

union 可以去除重复;

6.3 使用联合查询条件

7. 嵌套/子查询

7.1 标量子查询

7.2 列子查询

 

1） in 的使用

下面举例演示：

2）all 的使用

 

 

3） any 的使用

7.3 行子查询

7.4 表子查询

再看第二个例子；

8. 多表查询案例

注意：以下查询并非只有一种方式；

 1. 查询员工的姓名，年龄，职位，部门信息；

 2. 查询年龄小于30岁的员工的姓名，年龄，职位，部门信息；

3. 查询拥有员工的部门ID，部门名称（6号部门没有员工）

 4. 查询所有年龄大于40岁的员工，及其所属部门名称，如果员工没有分配部门，也需要展示出来；

 5. 查询所有员工的工资等级

 6. 查询研发部所有员工的信息及工资等级; 

 7. 查询“研发部“员工的平均工资

 8. 查询工资比”灭绝“高的员工信息

 9. 查询比平均工资高的员工信息

 10. 查询低于本部门平均工资的员工信息

 11. 查询所有部门信息，并统计部门员工人数

9. 总结

9.1 多表关系

 9.2 多表查询

 此外，较惋惜的是，本篇未涉及 having,group by ,order by 的使用以及SQL 语句的执行顺序;

 10 源代码(复制自取)

--创建数据库
create database mul_table_demo;--使用数据库
use mul_table_demo;--创建表 department
create table department(de_id smallint primary key,  --部门idde_name varchar(20)			 --部门名称
);--插入表 department
insert into department values(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办'),(6,'人事部');--创建表 employee
create table employee (em_id smallint primary key,                  --员工IDem_name varchar(20),						 --员工名字age smallint check (age between 18 and 65),  --年龄job varchar(20),							 --工作salary int not null,						 --薪水entrydate date,								 --入职日期manager_id smallint,						 --员工的领导IDdept_id smallint foreign key references department(de_id)--部门ID 
);--插入表 employee
insert into employee values								--在将 varchar 值 '*' 转换成数据类型 int 时失败。表示数据类型弄错了
(1, '金庸',  65,'总裁'    ,20000,'2000-01-01',null, 5), --外键插入的值必须是另一个表的主键的值，即取值范围有限制。因此没有 唯一性限制  
(2, '张无忌',20,'项目经理',12500,'2005-12-05',1, 1),
(3, '杨逍',  33,'开发'    ,8400,' 2000-11-03',2, 1),
(4, '韦一笑',48,'开发'    ,11000,'2002-02-05',2, 1),
(5, '常遇春',43,'开发'    ,10500,'2004-09-07',3, 1),
(6, '小昭',  19,'程序员'  ,6600, '2004-10-12',2, 1),
(7, '灭绝',  60,'财务总监',8500, '2002-09-12',1, 3),
(8, '周芷若',19,'会计'    ,4800, '2006-06-02',7, 3),
(9, '丁敏君',23,'出纳'    ,5250, '2009-05-13',7, 3),
(10,'赵敏',  20,'市场部总监',12500,'2004-10-12',1,2),
(11,'鹿仗客',56,'职员'    ,3750, '2006-10-03',10,2),
(12,'鹤笔翁',19,'职员',    3750, '2007-05-09',10,2),
(13,'方东白',19,'职员'    ,5500, '2000-01-01',10,2),
(14,'张三丰',65,'销售总监',14000,'2009-02-12',1, 4),
(15,'俞莲舟',38,'销售',    4600, '2004-10-12',14,4),
(16,'宋远桥',40,'销售',    4600, '2004-10-12',14,'4'),	-- 4 or '4':加引号不加引号都是可以的；
(17,'陈友谅',42,null,    2000, '2010-01-01',1, null);	--null不属于任何数据类型，因此外键可以插入 null ，char/int 数据类型都可以插入 null--多表查询：笛卡尔积
select * from employee,department;--消除笛卡尔积
select * from employee,department where employee.dept_id = department.de_id;--内连接
--隐式内连接
--查询每个员工的姓名及关联的部门名称
select em.em_name as '员工姓名',de.de_name as '部门名称' 
from employee as em ,department as de 
where em.dept_id = de.de_id;--显式内连接
--查询每个员工的姓名及关联的部门名称
select em.em_name,de.de_name 
from employee as em 
join department as de
on em.dept_id = de.de_id--外连接
--左外连接
--查询 emp 表的全部数据及对应的部门信息
select em.*,de.de_name
from employee as em 
left join department as de 
on em.dept_id = de.de_id--右外连接
--查询 dept 表的所有信息，和对应的员工信息
select de.de_id,de.de_name,em.* 
from employee as em 
right join department as de
on em.dept_id = de.de_id--自连接
select * from employee;
--查询员工及其领导的名字
select b.em_name as '员工姓名', a.em_name as '领导姓名' 
from employee as a,employee as b
where a.em_id = b.manager_id;--查询所有员工及其领导的名字，如果员工没有领导，也要查询出来
select * from employee;
selectb.em_name  as '员工姓名' ,a.em_name  as '领导姓名'
from employee as a 
right join employee as b
on a.em_id = b.manager_id; --联合查询
--查询薪资低于 5000 或年龄大于 50 岁的员工的信息（union all）select * from employee as e where e.salary < 5000
union all
select * from employee as e where e.age > 50--查询薪资低于 5000 或年龄大于 50 岁的员工的信息(union)
select * from employee as e where e.salary < 5000
union 
select * from employee as e where e.age > 50select * from employee as e where e.salary < 5000 or e.age > 50;--子查询
--标量子查询
--查询“销售部”所在员工的全部信息--1. 查询“销售部”部门ID
select de_id from department as de where de.de_name ='销售部';
--2. 根据部门ID，查询“员工表”员工信息
select * from employee as em where em.dept_id = 4;
--使用子/嵌套查询，将上述结果整合起来
select * from employee as em 
where em.dept_id = (select de.de_id from department as de where de.de_name ='销售部'
);--查询“方东白”入职之后的员工信息
--方东白的入职日期
select e.entrydate from employee as e where e.em_name='方东白';
--根据方东白的入职日期，查询员工信息
select * from employee as em where em.entrydate > '2000-01-01';
----使用子/嵌套查询，将上述结果整合起来
select * from employee as em where em.entrydate > (select e.entrydate from employee as e where e.em_name='方东白');--列子查询
--查询“销售部”和“市场部”的员工信息
--1. 查询“销售部”和“市场部”的部门ID
select de.de_id from department as de where de.de_name in ('销售部','市场部'); 
--2. 查询根据“销售部”和“市场部”的部门ID，查询其员工信息
select * from employee as em where em.dept_id in (2,4);
--使用子/嵌套查询，将上述结果整合起来
select * from employee as em where em.dept_id in (select de.de_id from department as de where de.de_name in ('销售部','市场部')
);--查询比财务部所有人员工资都高的员工信息
--1. 查询财务部的人员工资
select e.salary 
from employee as e 
where e.dept_id in (select d.de_id from department as d where d.de_name = '财务部');
--2. 根据财务部的人员工资，查询比其工资都高的员工信息
select * from employee as e where e.salary > 8500;
--使用子/嵌套查询，将上述结果整合起来（all）
select * from employee as e where e.salary > all(select e.salary from employee as e where e.dept_id in (select d.de_id from department as d where d.de_name = '财务部')
);--查询比研发部其中任意一人工资高的员工信息
--1. 查询研发部人员的工资
select e.salary 
from employee as e 
where e.dept_id in (select d.de_id from department as d where d.de_name = '研发部'); 
--2.查询比其任意一人工资高的员工信息
select * from employee as e where e.salary > 6600;
----使用子/嵌套查询，将上述结果整合起来（ any）
select * from employee as e where e.salary > any (select e.salary from employee as e where e.dept_id in (select d.de_id from department as d where d.de_name = '研发部')
);--行子查询
--查询 张无忌的薪资及直属领导相同 的员工信息
--1.查询张无忌的薪资
select e.salary,e.manager_id from employee as e where e.em_name = '张无忌';
--2.根据查询结果，查询员工信息
select * from employee as e where e.salary = 12500 and e.manager_id = 1;
--使用子/嵌套查询，将上述结果整合起来
select * from employee as e where (e.salary,e.manager_id) =(select e.salary,e.manager_id from employee as e where e.em_name = '张无忌');--表子查询
--查询“鹿仗客”，“宋远桥”的职位和薪资相同的员工信息
select * from employee as e where (e.salary,e.job) in (select e.salary,e.job from employee as e where e.em_name in ('鹿仗客','宋远桥'));--查询入职日期是“2006-01-01”之后的员工信息，及其部门信息
select e.*,d.* 
from ( select * from employee as e where e.entrydate > '2006-01-01') as e ,department as d
wheree.dept_id = d.de_id;--案例巩固
select * from employee;
select * from department;
select * from salgrade;
--在创建一张表
create table salgrade(grade int,			--薪资等级losal int,			--最低薪资hisal int			--最高薪资
)
insert into salgrade values(1,0,3000),(2,3001,5000),(3,5001,8000),(4,8001,10000),(5,10001,15000),(6,15001,20000),(7,20001,25000),(8,25001,3000);--1. 查询员工的姓名，年龄，职位，部门信息；
select e.em_name,e.age,e.job,d.de_name 
from employee as e 
left join department as d 
on e.dept_id = d.de_id ; 
--2. 查询年龄小于30岁的员工的姓名，年龄，职位，部门信息
select e.em_name,e.age,e.job,d.de_name 
from employee as e 
left join department as d 
on e.dept_id = d.de_id 
where e.age < 30;--3. 查询拥有员工的部门ID，部门名称（6号部门没有员工）
--方法1：子查询
select distinctd.de_id,d.de_name 
from (select e.dept_id from employee as e) as em ,department as d
where em.dept_id = d.de_id;
--方法2：内连接
select distinctd.de_id,d.de_name 
from employee as e,department as d 
where e.dept_id = d.de_id; --4. 查询所有年龄大于40岁的员工，及其所属部门名称，如果员工没有分配部门，也需要展示出来；
select e.*,d.de_name 
from	employee as e 
left join department as d 
on e.dept_id = d.de_id 
where e.age > 40; --5. 查询所有员工的工资等级（薪水等级表没有外键，如何连接表是关键）
select * 
from employee as e ,salgrade as s 
where e.salary between s.losal and s.hisal; --6. 查询研发部所有员工的信息及工资等级
select e.*,s.grade 
from (select * from employee as e join department as d on e.dept_id = d.de_id where d.de_name = '研发部') as e ,salgrade as s 
where e.salary between s.losal and s.hisal;--7. 查询“研发部“员工的平均工资
select avg(e.salary)
from employee as e 
join department as d 
on e.dept_id = d.de_id 
whered.de_name = '研发部'--8. 查询工资比”灭绝“高的员工信息
select * from 
employee as e 
where e.salary > ( select e.salary from employee as e where e.em_name = '灭绝');--9. 查询比平均工资高的员工信息
select * from 
employee as e 
where e.salary > ( select avg(salary) from employee);--10. 查询低于本部门平均工资的员工信息
select e2.*,(select avg(e1.salary) from employee as e1 where e1.dept_id = e2.dept_id ) as '平均' 
from employee as e2 
where e2.salary < (select avg(e1.salary) from employee as e1 where e1.dept_id = e2.dept_id ) ;--11. 查询所有部门信息，并统计部门员工人数
select count(*) from employee as e where e.dept_id = 6;
select d.de_id,d.de_name,(select count(*) from employee as e where e.dept_id = d.de_id) as '人数' 
from department as d;