MySQL学习笔记-多表查询

多表查询

直接多表会产生笛卡尔集

--显示员工名字，工资，所在部门名字
select * from emp;
select * from dept;

select * from emp,dept; -- 产生笛卡儿集
select ename,sal,dname from emp,dept where emp.deptno = dept.deptno;

自连接

把一张表当成两张表来使用

-- 显示员工的名字和他的上级
select worker.ename as '员工名字',boss.ename as '上级名字' from emp worker,emp boss where worker.empno = boss.mgr;

子查询

-- 子查询，子查询是指嵌入在 其他sql语句中的select语句
------------------------------------------------------------------
-- 单行子查询
-- 查询和SIMITH工作相同的员工
------------------------------------------------------------------
 -- 先查询出SMITH的工作
select job from emp where ename = 'SMITH';
-- 然后就可以查询和SIMITH工作相同的员工
select ename,job from emp where job=(select job from emp where ename = 'SMITH');
--------------------------------------------------------------------
-- 多行子查询，使用in关键字
-- 查询和部门10内员工工作相同的员工
--------------------------------------------------------------------
-- 先查询部门10的员工工作
select distinct job from emp where deptno=20;
-- 然后查询和部门10内员工工作相同的员工
select ename,job from emp where job in (select distinct job from emp where deptno=10);
--------------------------------------------------------------------
-- 子查询做临时表
-- 1、查找每个部门工资高于本部门平均工资的人的资料
select ename,job,sal,emp.deptno from emp,(select deptno,avg(sal) as avg_sal from emp group by deptno) temp where emp.deptno = temp.deptno and emp.sal > temp.avg_sal;
--------------------------------------------------------------------
-- 多列子查询
-- 查询与ALLEN的部门相同且岗位也相同的所有员工(不包含ALLEN本人)
select job,deptno from emp where ename='ALLEN';
select ename,job,deptno from emp where (job,deptno)=(select job,deptno from emp where ename='ALLEN') and ename != 'ALLEN';
---------------------------------------------------------------------
-- 子查询练习
---------------------------------------------------------------------
-- 1、查找每个部门工资高于本部门平均工资的人的资料
--- 先查找每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;

select ename,job,sal,emp.deptno from emp,(select deptno,avg(sal) as avg_sal from emp group by deptno) temp where emp.deptno = temp.deptno and emp.sal > temp.avg_sal;
---------------------------------------------------------------------
-- 2、查找每个部门工资最高的人的详细资料
-- 错误的写法
select deptno,max(sal),ename from emp group by deptno;

-- tem.*这个表的全部字段
select ename,temp.*,sal from emp,(select deptno,max(sal) as max_sal from emp group by deptno) temp where emp.deptno = temp.deptno and emp.sal = temp.max_sal;

any和all

--all和any
-- 显示部门20的所有员工的工资
select sal from emp where deptno = 20;

-- 显示所有员工比部门30所有员工工资高的人员
select ename,sal from emp where sal > all (select sal from emp where deptno = 30);
-- 显示所有员工比部门30任何一个员工工资高的人员
select ename,sal from emp where sal > any (select sal from emp where deptno = 20);