多表查询（续）

连接查询

内连接： 相当于查询集合A与集合B的交集部分 外连接 左外连接：查询左表所有数据，以及两张表交集部分数据 右外连接：查询右表所有数据，以及两张表交集部分数据 自连接：当前表与自身的连接查询，自连接必须使用表别名

-- 内连接
-- 内连接演示
-- 1、查询每一个员工的姓名，以及关联的部门的名称（隐式内连接实现）
-- 表结构：emp，dept
-- 连接条件：emp.dept_id = dept.id
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
select e.name, d.name from emp e, dept d where e.dept_id = d.id;
-- 2、查询每一个员工的姓名，以及关联的部门的名称（显式内连接实现）
-- 表结构：emp，dept
-- 连接条件：emp.dept_id = dept.id
-- 第一个表 inner join 第二个表 on 连接条件(inner关键字可以省略)
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
-- 外连接
-- 外连接演示
-- 1、查询emp表的所有数据，和对应部门的信息（左外连接）(outer可省略掉)
-- 表结构：emp，dept
-- 连接条件：emp.dept_id = dept.id
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
-- 2、查询dept的所有数据，和对应的员工信息（右外连接）
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

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

-- 自连接
-- 1、查询员工以及所属领导的名字
-- 表结构：emp a, emp, b
select a.name, b.name from emp a, emp b where a.managerid = b.id;
-- 2、查询所有员工emp及其领导的名字emp，如果员工没有领导，也需要查询出来
-- 表结构：emp a, emp, b
select a.name 员工, b.name 领导 from emp a left join emp b on a.managerid = b.id;

-- 联合查询-union, union all
-- 对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
-- 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
-- union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。
-- 1、将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来。
select * from emp where salary < 5000
union all
select * from emp where age > 50;

-- 上述SQL语句执行以后会有重复的条目，执行下述语句可去重
select * from emp where salary < 5000
union
select * from emp where age > 50;

子查询

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

子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个，根据子查询结果不同，分为：

标量子查询（子查询结果为单个值） 列子查询（子查询结果为一列） 行子查询（子查询结果为一行） 表子查询（子查询结果为多行多列）

-- 标量子查询
-- --------------------------------------------子查询---------------------------------------
-- 标量子查询
-- 1、查询销售部的所有员工信息
-- 先查询出销售部的部门ID
select id from dept where name = 销售部;
-- 根据销售部的部门ID查找员工信息
select * from emp where dept_id = 4;
select * from emp where dept_id = (select id from dept where name = 销售部);
-- 2、查询在东方白入职之后的员工信息
-- 查询方东白的入职日期
select entrydate from emp where name = 方东白;
-- 查询指定入职时期之后入职的员工信息
select * from emp where entrydate > 2009-02-12;
select * from emp where entrydate > (select entrydate from emp where name = 方东白);
-- 列子查询
-- 1、查询销售部和市场部的所有员工信息
-- 查询销售部和市场部的部门ID
select id from dept where name = 销售部 or name = 市场部;
-- 根据部门ID查询员工信息
select * from emp where dept_id in(2, 4);
select * from emp where dept_id in(select id from dept where name = 销售部 or name = 市场部);
-- 2、查询比财务部所有人工资都高的员工信息
-- 查询财务部所有人的工资
select id from dept where name = 财务部;
select salary from emp where dept_id = 3;
select salary from emp where dept_id = (select id from dept where name = 财务部);
-- 查询比财务部所有人工资都高的员工信息
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = 财务部));
-- 3、查询比研发部其中任意一人工资高的员工信息
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 = 研发部));
select * from emp where salary > some (select salary from emp where dept_id = (select id from dept where name = 研发部));

-- 行子查询
-- 1、查询与张无忌的薪资及直属领导相同的员工信息
-- 查询张无忌的薪资及其直属领导
select salary, managerid from emp where name = 张无忌;
-- 查询与张无忌的薪资及直属领导相同的员工信息
select * from emp where salary = 12500 and managerid = 1;
select * from emp where (salary, managerid) = (12500, 1);
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = 张无忌);

-- 表子查询
-- 子查询结果返回的是多行多列，这种查询称为表子查询
-- 1、查询与鹿杖客，宋远桥的职位和薪资相同的员工信息
-- 查询鹿杖客和宋远桥的职位和薪资
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 = 宋远桥);
-- 2、查询入职日期是2006-01-01之后的员工信息，及其部门信息
-- 查询入职日期是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;

-- --------------------------------多表查询案例---------------------------------------------
create table salgrade(
    grade int,
    losal int,
    hisal int
) comment 薪资等级表;

insert into salgrade values (1, 0, 3000);
insert into salgrade values (2, 3001, 5000);
insert into salgrade values (3, 5001, 8000);
insert into salgrade values (4, 8001, 10000);
insert into salgrade values (5, 10001, 15000);
insert into salgrade values (6, 15001, 20000);
insert into salgrade values (7, 20001, 25000);
insert into salgrade values (8, 25001, 30000);

-- 1、查询员工的姓名、年龄、职位、部门信息（隐式内连接）
-- 表：emp，dept
-- 连接条件：emp.dept_id = dept.id
select e.name, e.age, e.job, d.name from emp e, dept d where e.dept_id = d.id;

-- 2、查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
-- 表：emp，dept
-- 连接条件：emp.dept_id = dept.id
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、部门名称
-- 表：emp，dept
-- 连接条件：emp.dept_id = dept.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 d.id = e.dept_id where e.age > 40;

-- 5、查询所有员工的工资等级
-- 表：emp，salgrade
-- 连接条件：emp.salary >= salgrade.losal and emp..salary <= salgrade.hisal
select e.*, s.grade, s.losal, s.hisal from emp e, salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
select e.*, s.grade, s.losal, s.hisal from emp e, salgrade s where e.salary between s.losal and s.hisal;

-- 查询研发部所有员工的信息及工资等级
-- 表：emp，salarygrade, dept
-- 连接条件：e.salary between s.losal and s.hisal，emp.dept_id = dept.id
-- 查询条件: dept.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 hisal)
  and d.name = 研发部;

-- 7、查询研发部的平均工资
-- 表：emp，dept
-- 连接条件：emp.dept_id = dept.id
select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = 研发部;

-- 8、查询工资比灭绝高的员工信息
-- 查询灭绝的薪资
select salary from emp where name = 灭绝;
-- 查询工资比灭绝高的的员工信息
select * from emp where salary > (select salary from emp where name = 灭绝);

-- 9、查询比平均薪资高的员工信息
-- 查询员工的平均薪资
select avg(salary) from emp;
-- 查询比平均薪资高的员工信息
select * from emp where salary > (select avg(salary) from emp);

-- 10、查询低于本部门平均工资的员工信息
-- 比如查询指定部门平均薪资1
select avg(salary) from emp where emp.dept_id = 1;
-- 查询低于本部门平均工资的员工信息
select *, (select avg(salary) from emp e1 where e1.dept_id = e2.dept_id) 平均
from emp e2
where e2.salary < (select avg(salary) from emp e1 where e1.dept_id = e2.dept_id);

-- 11、查询所有的部门信息，并统计部门的员工人数
select id,name from dept;
select id,name, (select count(*) from emp e where e.dept_id = d.id) 人数 from dept d;
select count(*) from emp where dept_id = 1;
-- 12、查询所有学生的选课情况，展示出学生名称，学号，课程名称
-- 表：student，course，student_course
-- 连接条件:student.id = student_course.studentid, course.id = student_course.courseid
select s.name, s.no, c.name
from student s,
     student_course sc,
     ccourse c
where s.id = sc.studentid
  and c.id = sc.courseid;