MySQL练习

• 多行子查询

-- 查询工资低于任何一个“CLERK”的工资的雇员信息
select sal from emp
select *
from emp
where sal < any(select sal from emp where job = "CLERK")
-- 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资
select empno,ename,sal
from emp
where sal > all (select sal from emp where job = "SALESMAN")
-- 查询部门20中职务同部门10的雇员一样的雇员信息
select *
from emp 
where deptno = 20 and job in (select job from emp where deptno = 10)
-- in 等于其中的任意一个

• 三表查询

-- 三表查询
-- 查询员工工号、姓名、薪水、部门编号、部门名称、薪水等级
select * from emp
select * from dept
select * from salgrade
-- 思路：分三步走
-- 1.查询员工工号、姓名、薪水、部门编号
select empno,ename,sal,deptno from emp
-- 2.查询员工工号、姓名、薪水、部门编号、部门名称
select e.empno,e.ename,e.sal,d.deptno,d.dname
from emp e
join dept d
on (e.deptno = d.deptno)
-- 3.查询员工工号、姓名、薪水、部门编号、部门名称、薪水等级(tips：可以先把第三张表cross join一下，判断第三张表的on条件)
select e.empno,e.ename,e.sal,d.deptno,d.dname,sg.*
from emp e
join dept d
on (e.deptno = d.deptno)
cross join salgrade sg
-- 第三张表的条件确认
select e.empno,e.ename,e.sal,e.deptno,d.dname,sg.grade
from emp e
join dept d
on (e.deptno = d.deptno)
join salgrade sg
on (e.sal between sg.losal and sg.hisal)
order by sal

• 相关子查询引入

-- 相关子查询
-- 查询最高工资的员工
select max(sal) from emp
select * from emp where sal = (select max(sal) from emp)
-- 查询本部门最高工资的员工
-- 查询10部门最高工资的员工
select max(sal) from emp where deptno = 10
select * from emp where deptno = 10 and sal = (select max(sal) from emp where deptno = 10)
-- 查询20部门最高工资的员工
select max(sal) from emp where deptno = 20
select * from emp where deptno = 20 and sal = (select max(sal) from emp where deptno = 20)
-- 查询30部门最高工资的员工
select max(sal) from emp where deptno = 30
select * from emp where deptno = 30 and sal = (select max(sal) from emp where deptno = 30)
-- 合并
select * from emp where deptno = 10 and sal = (select max(sal) from emp where deptno = 10)
union
select * from emp where deptno = 20 and sal = (select max(sal) from emp where deptno = 20)
union
select * from emp where deptno = 30 and sal = (select max(sal) from emp where deptno = 30)
order by sal
-- 上述方式可以实现但是代码稍显复杂
-- 采用相关子查询--简化代码
select * from emp e1 where sal = (select max(sal) from emp e2 where e1.deptno = e2.deptno)
order by sal
-- 相关子查询的子查询语句不能独立运行，这个与不相关子查询不同

• 相关子查询练习

-- 相关子查询练习
-- 查询[工资高于其所在部门的平均工资的]那些员工
select *
from emp e1
where sal > (select avg(sal) from emp e2 where e1.deptno = e2.deptno) 
order by sal
-- 查询[工资高于其所在部门10的平均工资的]那些员工
select avg(sal) from emp where deptno = 10
select *
from emp
where deptno = 10 and sal > (select avg(sal) from emp where deptno = 10)
-- 查询[工资高于其所在部门20的平均工资的]那些员工
select avg(sal) from emp where deptno = 20
select *
from emp
where deptno = 20 and sal > (select avg(sal) from emp where deptno = 20)
-- 查询[工资高于其所在部门30的平均工资的]那些员工
select avg(sal) from emp where deptno = 30
select *
from emp
where deptno = 30 and sal > (select avg(sal) from emp where deptno = 30)
select * 
from emp e1 where e1.deptno = 30 and sal > (select avg(sal) from emp e2 where e2.deptno = 30)

• 自连接查询

-- 自连接查询
-- 查询员工的编号、姓名、上级编号,上级的姓名
-- 思路：把一张表看成两张表
select * from emp
-- 1.查询员工的编号、姓名、上级编号
select empno,ename,mgr from emp
-- 2.查询员工的编号、姓名、上级编号,上级的姓名
select e.empno,e.ename,e.mgr,m.*
from emp e
cross join emp m
on (e.mgr = m.empno)
-- 确定on条件
select e.empno,e.ename,e.mgr,m.ename
from emp e
join emp m
on (e.mgr = m.empno)