数据库多表操作实例

- 1．列出至少有一个员工的所有部门。SELECT *,COUNT(*) a FROM emp GROUP BY deptno HAVING a>=1;-- 2．列出薪金比'刘一'多的所有员工。//刘一的薪水SELECT sal FROM emp WHERE ename='刘一';//比刘一薪水高的人SELECT ename,sal FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='刘一');-- 3．***** 列出所有员工的姓名及其直接上级的姓名。SELECT ename,mgr FROM emp;-- 4．列出受雇日期早于其直接上级的所有员工。SELECT ename FROM emp WHERE (DATEDIFF(DAY,(SELECT hiredate FROM emp),(SELECT hiredate FROM emp WHERE mgr IN(SELECT mgr FROM emp)))<0;//正确SELECT * FROM emp e WHERE hiredate < (SELECT hiredate FROM emp WHERE empno=e.mgr);-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。SELECT dname,emp.* FROM emp,dept WHERE emp.deptno=dept.deptno ORDER BY emp.deptno;SELECT dname,emp.* FROM emp,dept WHERE emp.deptno=dept.deptno ORDER BY emp.deptno;SELECT dname,emp.* FROM emp,dept WHERE emp.deptno=dept.deptno ORDER BY emp.deptno;-- 6．列出所有job为“职员”的姓名及其部门名称。SELECT dname,emp.* FROM emp,dept WHERE emp.deptno=dept.deptno HAVING job IN ('职员');-- 7．列出最低薪金大于1500的各种工作。SELECT job FROM emp GROUP BY job HAVING MIN(sal)>1500;SELECT job FROM emp GROUP BY job HAVING MIN(sal)>1500;--8．列出在部门 '销售部' 工作的员工的姓名，假定不知道销售部的部门编号。//销售部idSELECT deptno FROM dept WHERE dname='销售部';//销售部员工姓名SELECT *FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE dname='销售部');-- 9．平均薪金的所有员工。SELECT * FROM emp WHERE sal>(SELECT AVG(sal)FROM emp);-- 10．列出与'周八'从事相同工作的所有员工。SELECT *FROM emp WHERE job IN(SELECT job FROM emp WHERE ename='周八');-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。SELECT ename,sal FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno=30 GROUP BY deptno);-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。SELECT ename,sal FROM emp WHERE sal >(SELECT MAX(sal) FROM emp WHERE deptno=30 GROUP BY deptno);-- 13．列出在每个部门工作的员工数量、平均工资。SELECT COUNT(*),AVG(sal) FROM emp GROUP BY deptno;-- 14．列出所有员工的姓名、部门名称和工资。SELECT ename,dept.dname,sal+IFNULL(comm,0) FROM emp,dept WHERE emp.deptno=dept.deptno;

-- 15．列出所有部门的详细信息和部门人数。SELECT deptno FROM emp GROUP BY deptno;SELECT a.*,(SELECT COUNT(*) FROM emp WHERE deptno=a.deptno) AS 人数 FROM dept a;SELECT dept.*,(SELECT COUNT(*)FROM emp WHERE deptno=dept.deptno) a FROM dept;-- 16．列出各种工作的最低工资。SELECT job,MIN(sal+IFNULL(comm,0))FROM emp GROUP BY job;-- 17．列出各个部门的 经理 的最低薪金。SELECT deptno,MIN(sal) FROM emp WHERE job='经理' GROUP BY deptno;-- 18．列出所有员工的年工资,按年薪从低到高排序。 SELECT (sal+IFNULL(comm,0))*12 monery,ename FROM emp  ORDER BY monery;

-- 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。SELECT empno,ename,sal FROM emp WHERE sal>3000;-- 20.查询出所有薪水在'陈二'之上的所有人员信息。SELECT *FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='陈二');-- 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水SELECT empno 编号,ename,sal FROM emp WHERE sal>2000 AND deptno=20;-- 22.查询出emp表中所有的工作种类（无重复）SELECT DISTINCT job FROM emp;

-- 23.查询出所有奖金（comm）字段不为空的人员的所有信息。SELECT *FROM emp WHERE comm IS NOT NULL;-- 24.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）SELECT *FROM emp WHERE sal BETWEEN 800 AND 2500;SELECT* FROM emp WHERE sal >=800 AND sal<=2500;-- 25.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）SELECT * FROM emp WHERE empno IN(7521,7900,7782);SELECT* FROM emp WHERE empno=7521 OR empno=7900 OR empno=7782;-- 26.查询出名字中有“张”字符，并且薪水在1000以上（不包括1000）的所有员工信息。SELECT *FROM emp WHERE ename LIKE '%张%' AND sal>1000;

-- 27.查询出名字第三个汉字是“忌”的所有员工信息。SELECT * FROM emp WHERE ename LIKE '__忌';-- 28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。SELECT *FROM emp ORDER BY sal ASC,hiredate ASC;SELECT*FROM emp;-- 29.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。 order by convert(name using gbk) asc; SELECT *FROM -- 30.查询出最早工作的那个人的名字、入职时间和薪水。SELECT ename,hiredate,sal,comm FROM emp WHERE  hiredate=(SELECT MIN(hiredate)FROM emp);-- 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.SELECT  ename,sal,IFNULL(comm,100)FROM emp;-- 32.显示出薪水最高人的职位。SELECT MAX(sal)FROM emp;SELECT job FROM emp WHERE sal=(SELECT MAX(sal)FROM emp);

-- 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。SELECT dept.dname,MAX(sal),MIN(sal) FROM dept,emp WHERE dept.deptno=emp.deptno GROUP BY emp.deptno AND emp.deptno !=10;-- 34.删除10号部门薪水最高的员工。SELECT*FROM emp WHERE deptno=10;SELECT MAX(sal) FROM emp WHERE deptno=10;DELETE FROM emp WHERE sal=(SELECT MAX(sal) FROM emp WHERE deptno=10);-- 35.将薪水最高的员工的薪水降30%。SELECT empno FROM emp WHERE sal IN(SELECT MAX(sal) FROM emp);UPDATE emp SET sal=sal*0.7 WHERE empno=(SELECT empno FROM emp WHERE sal IN(SELECT MAX(sal) FROM emp));-- 36.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)-