MySql里的子查询练习

MySql里的子查询练习

-- 单行子查询(> < >= <= = <>)
 -- 查询出高于10号部门的平均工资的员工信息
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=10);
-- 多行子查询(in  not in any all)
 -- 查询出比10号部门任何员工薪资高的员工信息
SELECT * FROM emp WHERE sal> (SELECT MAX(sal) FROM emp WHERE deptno=10);
-- 多列子查询(实际使用较少)
 -- 和10号部门同名同工作的员工信息
SELECT * FROM emp WHERE (ename,job)IN (SELECT ename,job FROM emp WHERE deptno=10) AND deptno<>10;
-- select 后面接子查询 
 -- 获取员工的名字和部门的名字
SELECT ename,(SELECT dname FROM dept d WHERE d.deptno=e.deptno) m FROM emp e
-- from 后面接子查询
    -- 查询emp表中经理信息
SELECT * FROM emp e,(SELECT job FROM emp WHERE job='MANAGER') j,dept d WHERE e.`job`=j.job AND d.deptno=e.`deptno`
-- where 后面接子查询 
 -- 薪资高于10号部门平均工资的所有员工信息
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=10);
-- group by 后面接子查询
 -- 有哪些部门的平均工资高于30号部门的平均工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>(SELECT AVG(sal) FROM emp WHERE deptno=30) 
-- 工资>JONES工资
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='jones');
-- 查询与SCOTT同一个部门的员工
SELECT * FROM emp WHERE job =(SELECT job FROM emp WHERE ename='scott') AND ename!='scott'
-- 工资高于30号部门所有人的员工信息
SELECT * FROM emp WHERE sal> (SELECT MAX(sal) FROM emp WHERE deptno=30);
-- 查询工作和工资与MARTIN完全相同的员工信息
SELECT * FROM emp WHERE  (job,sal) IN (SELECT job,sal FROM emp WHERE ename='martin') AND ename!='martin'
-- 有两个以上直接下属的员工信息
SELECT *,deptno,COUNT(mgr) FROM emp GROUP BY deptno HAVING COUNT(mgr)>2
-- 查询员工编号为7788的员工名称,员工工资,部门名称,部门地址
SELECT e.ename,e.sal,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno AND e.empno=7788
#1. 查询出高于本部门平均工资的员工信息
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno
SELECT e.* FROM emp AS e ,(SELECT deptno,AVG(sal) AS asal FROM emp  GROUP BY deptno) AS d WHERE e.deptno=d.deptno AND e.sal>d.asal 
#2. 列出达拉斯加工作的人中,比纽约平均工资高的人
 SELECT * FROM emp WHERE deptno = ( SELECT deptno FROM dept WHERE loc ='DALLAS')
      AND 
      sal > (SELECT AVG(sal) FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc ='NEW YORK'));
#3. 查询7369员工编号,姓名,经理编号和经理姓名
SELECT empno,ename,deptno FROM emp WHERE job='MANAGER'
SELECT e.empno,e.ename,m.no,m.name FROM emp AS e,(SELECT empno AS NO,ename AS NAME,deptno AS dept FROM emp) AS m WHERE e.deptno=m.dept AND e.empno=7369
#4. 查询出各个部门薪水最高的员工所有信息
SELECT e.* FROM emp AS e ,(SELECT deptno,MAX(sal) AS asal FROM emp  GROUP BY deptno) AS d WHERE e.deptno=d.deptno AND e.sal=d.asal