多表查询

多表关系：

一对一；

一对多；

多对多；

查询分类：

　　连接查询；

　　　　内连接；

　　　　外连接；

　　　　　　左外连接；

　　　　　　右外连接；

　　　　自连接；

　　子查询；

内连接：取交集，隐式内连接和显式内连接。

• 隐式内连接　　select * from 表1，表2 where 条件；
• 显式内连接　　select * from 表1 join 表2 on 连接条件；

案例：查询每一个员工的姓名 , 及关联的部门的名称

隐式：select e.name,d.name from emp e,dept d where e.dept_id = d.id;

显式：select e.name,d.name from emp e join dept d on d.id = e.dept_id;

注意⚠️：一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

外连接：

左外连接：select * from 表1 left join 表2 on 连接条件；相当于查询左边表1所有的数据，包括和表2交集部分的数据；

右外连接：select * from 表1 right join 表2 on 连接条件；相当于查询右边表2所有的数据，包括和表1交集部分的数据；

案例：查询emp表的所有数据, 和对应的部门信息

 select e.*,d.* from emp e left join dept d on e.dept_id = d.id;

案例：查询dept表的所有数据, 和对应的员工信息

 select e.*,d.* from emp e right join dept d on e.dept_id = d.id;

select e.*,d.* from dept d right join emp e on e.dept_id = d.id;

注意事项⚠️:

 左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺 序就可以了。而我们在日常开发使用时，更偏向于左外连接。

自连接：自连接查询，顾名思义，就是自己连接自己，可以是内连接查询，也可以是外连接查询。

案例：查询员工 及其 所属领导的名字

select e1.name ,e2.name from emp e1,emp e2 where e1.managerid=e2.id;

 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来

 select e1.name ,e2.name from emp e1 left join emp e2 on e1.managerid=e2.id;

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

SELECT 字段列表 FROM 表A ...

UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

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

案例：将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.

selec * from emp where salary >5000 union select * from emp where age >50;

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

SELECT * FROM t WHERE column = (SELECT column FROM t2 );

根据子查询结果不同，分为:
A. 标量子查询(子查询结果为单个值)

B. 列子查询(子查询结果为一列)
C. 行子查询(子查询结果为一行)
D. 表子查询(子查询结果为多行多列)

根据子查询位置，分为:

A. WHERE之后
B. FROM之后
C. SELECT之后

标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询。

常用的操作符:= <> > >= < <=

案例：A. 查询 "销售部" 的所有员工信息

select id from dept where name = '销售部';

select * from emp where emp.dept_id =(select id from dept where name = '销售部');　　

查询在 "方东白" 入职之后的员工信息

select entrydate from emp where name ='方东白';

select * from emp where entrydate >(select entrydate from emp where name ='方东白');

列子查询

 子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。

常用的操作符:IN 、NOT IN 、 ANY 、SOME 、 ALL

 案例：A. 查询 "销售部" 和 "市场部" 的所有员工信息

select id from dept where name ='销售部' or name ='市场部';

select * from emp where dept_id in(select id from dept where name ='销售部' or name ='市场部');

查询比 财务部 所有人工资都高的员工信息

select id from dept where name ='财务部';

 select salary from emp where  dept_id in(select id from dept where name ='财务部');

select * from emp where salary > all(select salary from emp where  dept_id in(select id from dept where name ='财务部'));

查询比研发部其中任意一人工资高的员工信息

select id from dept where name = '研发部';

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

 案例:

A. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;

select salary ,managerdfrom emp where name='张无忌'; 

select * from emp where (salary,managerid)=(select salary ,managerdfrom emp where name='张无忌');

表子查询

 子查询返回的结果是多行多列，这种子查询称为表子查询。 常用的操作符:IN

 案例:

A. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

select  job,salary from emp where name = '鹿杖客' or name='宋远桥';

select * from emp where (job,salary) in (select  job,salary from emp where name = '鹿杖客' or name='宋远桥');

查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息

 select * from emp where entrydate> '2006-01-01';

select e.*,d.* from (select * from emp where entrydate> '2006-01-01') e left join dept d on e.dept_id= d.id;

多表查询

查询员工的姓名、年龄、职位、部门信息 (隐式内连接)

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

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

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

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

 select distinct dept_id from emp where e.dept_id is not null;

select * from dept where id in (select distinct dept_id from emp e where e.dept_id is not null);

or

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

查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出 来(外连接)

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

查询所有员工的工资等级

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

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

select id from dept where name='研发部';

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

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 = '研发部';

查询 "研发部" 员工的平均工资

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

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

查询工资比 "灭绝" 高的员工信息。

 select salary from emp where name ='灭绝';

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

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

select avg(salary) from emp;

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

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

 select avg (salary) from emp e,dept d where e.dept_id=d.id;

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

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

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

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

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

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