数据库查询案例(一) 单表查询 45 题
use `部门雇员`; DROP TABLE dept; CREATE TABLE `dept` ( `deptno` bigint(2) NOT NULL PRIMARY KEY, `dname` varchar(20) default NULL, `loc` VARCHAR(20) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 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'); DROP TABLE EMP; CREATE TABLE `EMP` ( `EMPNO` BIGINT(4) NOT NULL PRIMARY KEY, `ENAME` varchar(20) default NULL, `JOB` VARCHAR(20) default NULL, `MGR` BIGINT default NULL, `HIREDATE` DATE default NULL, `SAL` DOUBLE(7,2) default NULL, `COMM` DOUBLE(7,2) default NULL, `DEPTNO` BIGINT default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 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); -- 练习题: -- -- -- 01.查询每个雇员的编号、姓名、职位。 SELECT EMPNO,ENAME,JOB FROM EMP; -- -- 02.查询每个雇员的职位,使用 DISTINCT 消除掉显示的重复行记录。 SELECT DISTINCT JOB FROM EMP; -- -- 03.计算每个雇员的编号、姓名、基本年薪。年薪=(工资+奖金)*12,(使用 IFNULL 函数判断 NULL) SELECT EMPNO,ENAME,((SAL+IFNULL(COMM,0))*12) FROM EMP ; -- -- -- 04.每个雇员每个月公司会补贴饭食 200 元,交通补助 300 元,计算年薪。 SELECT ((500+SAL+IFNULL(COMM,0))*12) FROM EMP ; -- -- 05.查询基本工资高于 2000 的全部雇员信息。 SELECT * FROM EMP WHERE SAL>2000; -- -- 06.查询出 smith 的信息。 SELECT * FROM EMP WHERE ENAME = "smith"; -- -- 07.查询出所有不是 CLERK 的详细信息。 SELECT * FROM emp WHERE JOB <> "CLERK"; -- 08.查询出所有销售人员(SALESMAN)的基本信息,并且要求销售人员的工资高于 1300。 SELECT * from emp WHERE JOB = "SALESMAN" AND sal>1300; -- -- 09.查询出工资范围在 1500~3000 之间的全部雇员信息(包含 1500 和 3000)。 SELECT * FROM EMP WHERE SAL BETWEEN 1500 AND 3000; -- -- 10.查询出所有经理或者是销售人员的信息,并且要求这些人的基本工资高于 1500。 SELECT * FROM emp WHERE (JOB = 'SALESMAN' OR JOB = 'MANAGER') AND SAL > 1500; -- -- 11.要求查询出所有在 1981 年雇佣的雇员信息。 SELECT * FROM EMP WHERE HIREDATE LIKE "1981%"; -- -- 12.查询所有领取奖金的雇员信息(comm 不为空)。 SELECT * FROM EMP WHERE COMM>0; -- -- 13.查询所有领取奖金高于 100 的雇员信息。 SELECT * FROM EMP WHERE COMM>100; -- -- 14.查询出雇员编号是 7369、7566、9999 的雇员信息。 SELECT * FROM EMP WHERE EMPNO IN(7369,7566,9999); -- -- 15.查询出所有雇员姓名是以 A 开头的全部雇员信息。 select * FROM emp WHERE ENAME LIKE "A%"; -- -- 16.查询出雇员姓名第二个字母是 M 的全部雇员信息。 select * FROM emp WHERE ENAME LIKE "_M%"; -- -- 17.查询出雇员姓名任意位置上包含字母 A 的全部雇员信息。 SELECT * FROM emp WHERE ENAME LIKE "%A%"; -- -- 18.查询出所有雇员的信息,要求按照工资排序。 SELECT * FROM emp ORDER BY SAL; -- -- 19.要求查询所有雇员的信息,按照雇佣日期由先后排序。 SELECT * FROM emp ORDER BY HIREDATE; -- -- 20.查询全部雇员信息,按照工资由高到低排序,如果工资相同,则按照雇佣日期由先后排序。 SELECT * FROM emp ORDER BY SAL DESC, HIREDATE ASC; -- -- 21.查询部门 30 中的所有员工。 SELECT * FROM emp WHERE DEPTNO=30; -- -- 22.查询出所有办事员(CLERK)的姓名,编号和部门编号。 SELECT ENAME,EMPNO,DEPTNO FROM EMP WHERE JOB="CLERK"; -- -- 23.查询出奖金高于薪金的员工。 SELECT * FROM EMP WHERE COMM>SAL; -- -- 24.查询出奖金高于薪金的 60%的员工。 SELECT * FROM EMP WHERE COMM>SAL*0.6; -- -- 25.查询出部门 10 中所有经理(MANAGER)和部门 20 中所有办事员(CLERK)的详细资料。 SELECT * FROM EMP,dept WHERE ((emp.DEPTNO=10 AND emp.JOB="MANAGER") OR (emp.DEPTNO=20 AND emp.JOB="CLERK")) AND emp.DEPTNO=dept.DEPTNO; -- -- 26.查询出部门 10 中所有经理,部门 20 中所有办事员,既不是经理又不是办事员但其薪金大于或等于 2000 的所有员工的信息。 SELECT * FROM emp,dept WHERE ( (emp.JOB = "MANAGER" AND emp.DEPTNO=10) OR (emp.JOB = "CLERK" AND emp.DEPTNO=20) OR (emp.SAL>2000 and emp.JOB <>"MANAGER" AND emp.JOB <> "CLERK") ) AND (emp.DEPTNO=dept.DEPTNO); -- -- 27.查询出收取奖金的员工的不同工作。 SELECT DISTINCT JOB FROM emp WHERE COMM<>0; -- -- 28.查询出不收取奖金或收取的奖金低于 100 的员工。 SELECT *FROM emp WHERE IFNULL(COMM,0)<100; -- -- 29.查询出不带有“R”的员工的姓名。 SELECT ENAME FROM emp WHERE ENAME NOT LIKE "%R%"; -- -- 30.查询出每个雇员的姓名、职位、领导姓名。 SELECT A.ENAME "雇员的姓名", A.JOB "雇员的职位", B.ENAME "雇员领导姓名" FROM emp A,emp B WHERE A.MGR = B.EMPNO; -- -- 31.查询出所有员工的编号、姓名及其直接上级的编号、姓名,显示的结果按领导年工资的降序排列。 SELECT A.EMPNO ,A.ENAME , B.EMPNO ,B.ENAME, (B.SAL+IFNULL(B.COMM,0))*12 "领导年工资" FROM emp A,emp B WHERE A.MGR = B.EMPNO ORDER BY (B.SAL+IFNULL(B.COMM,0))*12 DESC; -- -- 32.查询出在销售部(SALES)工作的员工姓名、基本工资、雇佣日期、部门名称。(不知道销售部编号)。 SELECT a.ename,a.empno,a.sal,a.hiredate,b.dname FROM emp a,dept b WHERE a.job='salesman' AND a.deptno=b.deptno; -- -- 33.查询出所有员工的姓名、部门名称和工资。 SELECT emp.ename,dept.dname,emp.sal FROM emp,dept WHERE emp.deptno = dept.deptno; -- -- 34.查询出所有员工的年工资,所在部门名称,按年薪从低到高排序。 SELECT (emp.sal+IFNULL(emp.comm,0))*12 "年薪", dept.dname FROM emp,dept WHERE emp.deptno = dept.deptno ORDER BY "年薪"; SELECT (a.sal+IFNULL(a.COMM,0))*12 AS ys,b.dname FROM emp a,dept b WHERE a.deptno=b.deptno ORDER BY ys ; -- (☆☆☆)-- 35.查询出某个员工的上级主管及所在部门名称,并要求出这些主管中的薪水超过 3000。 SELECT DISTINCT t2.`ENAME`,t3.`DNAME` FROM emp t1,emp t2,dept t3 WHERE t1.`MGR` = t2.`EMPNO` AND t2.`DEPTNO` = t3.`DEPTNO` AND t2.`SAL` > 3000; -- -- 36.查询出公司的最高和最低工资。 SELECT MIN(sal),MAX(sal) FROM emp; -- -- 37.查询出每个部门的人数、平均工资,只显示部门编号。 SELECT DEPTNO,COUNT(DEPTNO),AVG(SAL) FROM emp GROUP BY DEPTNO; -- -- 38.查询出每种职位的最高和最低工资。 SELECT job, MAX(sal),MIN(sal) FROM emp GROUP BY job; -- -- 39.查询平均工资高于 2000 的职位信息,以及从事此职位的雇员人数、平均工资。 SELECT job,count(job),AVG(sal) FROM emp GROUP BY job HAVING AVG(sal)>2000; -- -- 40.查询员工编号大于其领导编号的每个员工的姓名、职位、领导名称。 SELECT t1.ename, t1.job, t2.ename, t1.deptno ,t2.deptno FROM emp t1,emp t2 WHERE t1.empno>t1.mgr AND t1.mgr = t2.empno; -- (☆☆☆)-- 41 查询出至少有一个员工的所有部门编号、名称,并统计出这些部门的平均工资、最低工资、最高工资。 SELECT t1.DEPTNO, t2.DNAME, AVG(sal),MIN(sal),MAX(sal) FROM emp t1,dept t2 WHERE t1.DEPTNO = t2.DEPTNO GROUP BY t2.DNAME; -- -- (☆☆☆)42.查询出部门名称和这些部门的员工信息(数量、平均工资),同时列出那些没有员工的部门。 SELECT dept.DNAME,COUNT(ename),IFNULL(AVG(sal),0) FROM emp RIGHT JOIN dept ON emp.DEPTNO = dept.DEPTNO GROUP BY dept.DNAME; -- 43.查询工资比 smith 更高的全部员工信息。 SELECT * FROM emp,dept WHERE emp.DEPTNO = dept.DEPTNO AND sal>(SELECT SAL FROM emp WHERE ename = "smith"); -- -- 44.查询工资和职位和 smith 相同的所有员工信息。 SELECT * FROM emp,dept WHERE emp.DEPTNO = dept.DEPTNO AND job=(SELECT job FROM emp WHERE ename = "smith"); -- -- (☆☆☆) 45.查询各部门的部门编号,部门名称,部门地址,员工人数和平均工资。 SELECT dept.DEPTNO,dept.dname,dept.LOC,COUNT(JOB),IFNULL(AVG(sal),0) FROM emp RIGHT JOIN dept ON emp.DEPTNO = dept.DEPTNO GROUP BY dept.dname; -- 右连接 SELECT * FROM emp RIGHT JOIN dept ON emp.DEPTNO = dept.DEPTNO GROUP BY dept.dname; -- 左连接 SELECT * FROM emp left JOIN dept ON emp.DEPTNO = dept.DEPTNO GROUP BY dept.dname;