SQL多表查询

一、多表关系

表结构之间的联系分为三种：

• 一对多：在多的一方建立外键，指向一的一方的主键

• 多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

• 一对一：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）

---

二、多表查询概述

• 概述：从多张表中进行数据查询

例如：

有以下两张表dept和emp：

现在想查询两张表的信息，并且让信息合并，则应该：

select * from emp , dept where emp.dept_id = dept.id;

• 多表查询分类

1、连接查询

内连接：相当于查询A、B交集部分数据

外连接：

              左外连接：查询左表所有数据，以及两张表交集部分数据（用的更多）

              右外连接：查询右表所有数据，以及两张表交集部分数据

自连接：当前表与自身的连接查询，自连接必须使用表别名

2、子查询

---

三、内连接

• 隐式内连接

#查询每一个员工的姓名，以及关联的部门的名称
表结构：emp,dept
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;

• 显示内连接

#查询每一个员工的姓名，以及关联的部门的名称
表结构：emp,dept
select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;

---

四、外连接

左外连接：

# 查询emp表的所有数据，和对应的部门信息
表结构：emp,dept
select emp.*,d.name from emp left outer jion dept on emp.dept_id = dept.id;

右外连接：

# 查询dept表的所有数据，和对应的员工信息
表结构：emp,dept
select dept.*,emp.* from emp right outer jion dept on emp.dept_id = dept.id;

---

五、自连接

语法：

自连接查询可以是内连接查询，也可以是外连接查询。

#查询员工以及所属领导的名字
表结构：emp
select a.name,b.name from emp a,emp b where a.managerid = b.id;

#查询所有员工emp以及领导的名字emp，如果员工没有领导，也需要查询出来
表结构：emp
select a.name,b.name from emp a left join emp b on a.managerid = b.id;

---

六、联合查询：

概念：把多次查询的结果合并起来，形成一个新的查询结果集。

#将薪资低于五千的员工，和年龄大于50的员工全部查询出来
select * from emp where salary < 5000
union all
select * from emp where age > 50;

将查询结果去重：

#将薪资低于五千的员工，和年龄大于50的员工全部查询出来
select * from emp where salary < 5000
union 
select * from emp where age > 50;

• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
• union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

---

七、子查询

概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又叫做子查询。

子查询的外部语句可以是insert/update/delete/select的任何一个。

根据子查询结果不同，分为：

• 标量子查询
• 列子查询
• 行子查询
• 表子查询

根据子查询位置，分为：where之后、from之后、select之后。

标量子查询（子查询结果为单个值）

常用的操作符：>、<、=、<=、>=、<>

# 查询销售部的所有员工信息
select * from emp where dept_id=
(select id from dept where name = '销售部');

#查询在“方东白”入职之后的员工信息
select * from emp where entrydate > 
(select entrydate from emp where name = '方东白');

列子查询（子查询结果为一列）

常用的操作符：in、not in、any、some、all

#查询“销售部”和“市场部”的所有员工信息
select * from emp where dept_id in 
(select id from dept where name='销售部' or name='市场部');

#查询比财务部所有人工资都高的员工信息
select * from emp where salary > all
(select salary from emp where dept_id = (select id from dept where name = '财务部'));

#查询比研发部其中任意一人工资高的员工信息
select * from emp where salary > any
(select salary from emp where dept_id = (select id from dept where name = '研发部');

行子查询（子查询结果为一行）

常用操作符：=、<>、in、not in

#查询与“张无忌”的薪资以及直属领导相同的员工信息
select  from emp where 
(salary,managerid) = (select salary,managerid from emp where name = '张无忌');

表子查询（子查询结果为多行多列）

常用操作符：in

#查询与“鹿杖客”、“宋远桥”的职位和薪资相同的员工信息
select * from emp where (job,salary) in
(select job,salary from emp where name=' 鹿杖客' or name='宋远桥');

#查询入职日期是“2006-01-01”之后的员工信息以及部门信息
select emp.*,dept.* from (select * from emp where entrydate > '2006-01-01')
left join dept on emp.dept_id = dept.id;

---

八、多表查询案例

1、查询员工姓名、年龄、职位、部门信息

select e.name,e.age,e.job,d.name from emp e,dept d 
where e.dept_id = d.id;

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

select e.name,e.age,e.job,d.name from emp e inner join dept d
on e.dept_id=d.id where e.age < 30;

3、查询拥有员工的部门id、部门名称

select distinct d.id,d.name from emp e,dept d where e.dept_id = d.id;

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

select e.* ,d.name from emp e left join dept d on e.dept_id=d.id
where e.age > 40;

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

select e.*,s.grade from emp e, salgrade s 
where e.salary between s.losal and s.hisal;

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

select e.*,s.grade from emp e,dept d,salgrade s
where e.dept_id=d.id and 
(e.salary between s,losal and s.hisal) and
d.name='研发部';

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

select avg(e.salary) from emp e,dept d where e.dept_id = d.id and d.name = '研发部';

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

select * from emp where salary > (select salary from emp where name='灭绝');

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

select * from emp where salary > (select avg(salary) from emp);

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

select * from emp e2 where 
e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);

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

select d.id,d.name,(select count(*) from emp e where e.dept_id = d.id) '人数' from dept d;

12、查询所有学生的选课情况，展示出学生名称，学号，课程名称

select s.name,s.no,c.name from student s,student_course sc,course c 
where s.id = sc.studentid and sc.courseid = c.id;