MySql子查询练习（带答案）

drop database if EXISTS mydb;create database mydb;use mydb;-- 子查询CREATE TABLE emp(empno		INT,ename		VARCHAR(50),job		VARCHAR(50),mgr		INT,hiredate	DATE,sal		DECIMAL(7,2),comm		DECIMAL(7,2),deptno		INT) ;INSERT INTO emp VALUES(7369,\'SMITH\',\'CLERK\',7902,\'1980-12-17\',800,NULL,20);INSERT INTO emp VALUES(7499,\'ALLEN\',\'SALESMAN\',7698,\'1981-02-20\',1600,300,30);INSERT INTO emp VALUES(7521,\'WARD\',\'SALESMAN\',7698,\'1981-02-22\',1250,500,30);INSERT INTO emp VALUES(7566,\'JONES\',\'MANAGER\',7839,\'1981-04-02\',2975,NULL,20);INSERT INTO emp VALUES(7654,\'MARTIN\',\'SALESMAN\',7698,\'1981-09-28\',1250,1400,30);INSERT INTO emp VALUES(7698,\'BLAKE\',\'MANAGER\',7839,\'1981-05-01\',2850,NULL,30);INSERT INTO emp VALUES(7782,\'CLARK\',\'MANAGER\',7839,\'1981-06-09\',2450,NULL,10);INSERT INTO emp VALUES(7788,\'SCOTT\',\'ANALYST\',7566,\'1987-04-19\',3000,NULL,20);INSERT INTO emp VALUES(7839,\'KING\',\'PRESIDENT\',NULL,\'1981-11-17\',5000,NULL,10);INSERT INTO emp VALUES(7844,\'TURNER\',\'SALESMAN\',7698,\'1981-09-08\',1500,0,30);INSERT INTO emp VALUES(7876,\'ADAMS\',\'CLERK\',7788,\'1987-05-23\',1100,NULL,20);INSERT INTO emp VALUES(7900,\'JAMES\',\'CLERK\',7698,\'1981-12-03\',950,NULL,30);INSERT INTO emp VALUES(7902,\'FORD\',\'ANALYST\',7566,\'1981-12-03\',3000,NULL,20);INSERT INTO emp VALUES(7934,\'MILLER\',\'CLERK\',7782,\'1982-01-23\',1300,NULL,10);select * from emp;CREATE TABLE dept(deptno		INT,dname		VARCHAR(14),loc		VARCHAR(13));INSERT INTO dept VALUES(10, \'ACCOUNTING\', \'NEW YORK\');INSERT INTO dept VALUES(20, \'RESEARCH\', \'DALLAS\');INSERT INTO dept VALUES(30, \'SALES\', \'CHICAGO\');INSERT INTO dept VALUES(40, \'OPERATIONS\', \'BOSTON\');select * from dept;select * from emp;-- 单行子查询(> < >= <= = <>)-- 查询出高于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 > any(select sal from emp where deptno=10)and deptno!=10;#查到所有在10部门工作的人select ename,job from emp where deptno=10;#结果select * from emp where(ename,job) in(select ename,job from emp where deptno=10);-- select 后面接子查询-- 获取员工的名字和部门的名字select d.dname,e.ename from dept d,emp e where d.deptno=e.deptno;#结果select ename,(select dname from dept d where d.deptno=e.deptno)部门名称 from emp e ;-- from 后面接子查询-- 查询emp表中经理信息select * from emp where job=\"MANAGER\";-- from后面接子查询-- 查询emp表中所有管理层的信息SELECT DISTINCT mgr FROM emp;select * from emp e,(SELECT DISTINCT mgr FROM emp) mgrtable where e.empno=mgrtable.mgr;-- where 后面接子查询-- 薪资高于10号部门平均工资的所有员工信息select * from emp where sal >(select avg(sal) from emp where deptno=10);-- group by 后面接子查询-- 有哪些部门的平均工资高于30号部门的平均工资#查找30号部门的平均工资select avg(sal) from  emp where deptno=30;#结果select deptno,avg(sal) from emp GROUP BY deptno HAVING avg(sal)>(select avg(sal) from  emp where deptno=30);SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE deptno=30);-- 工资>JONES工资-- 查询与SCOTT同一个部门的员工select * from emp where ename=\"SCOTT\";select ename from emp where emp in (select * from emp where ename=\"SCOTT\");#select * from 员工表 where 部门 in (select 部门 from 员工表 where 姓名=\'张三\') and 姓名<>\'张三\'select * from emp where deptno in(select deptno from emp where ename=\"SCOTT\")andename<>\"SCOTT\";select * from emp;select * from dept;-- 工资高于30号部门所有人的员工信息select sal from emp where deptno=30;select * from emp where sal>all(select sal from emp where deptno=30) and deptno!=30;-- 查询工作和工资与MARTIN完全相同的员工信息#先查出MARTIN的工作和工资与select job,mgr from emp where ename=\"MARTIN\";#完全相同的员工信息 (job,sal)表示一个集合； in (集合) 包含select * from emp where(job,sal) in(select job,sal from emp where ename=\"MARTIN\");-- 有两个以上直接下属的员工信息select * from emp where mgr in(select mgr from emp GROUP BY mgr 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;x#SQL查询的综合案例#1. 查询出高于本部门平均工资的员工信息select * from emp e1 where e1.sal>(select avg(e2.sal) from emp e2 where e1.deptno=e2.deptno GROUP BY e2.deptno);#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 e.empno,e.ename,d.deptno,d.dname from emp e,dept d where d.deptno=e.deptno and e.empno=7369;#4. 查询出各个部门薪水最高的员工所有信息select * from emp e where e.sal=(select max(sal) from emp ep where e.deptno=ep.deptno GROUP BY deptno);select * from  dept;select * from  emp