MySql数据库单表、多表查询练习

– 按部门求出该部门平均工资分组查询

SELECT TRUNCATE(AVG(SAL),0) AS ‘部门平均工资’ FROM emp GROUP BY emp.

DEPTNO

;

– 查询部门平均工资大于2000元的部门

SELECT dept.

DNAME

AS ‘平均工资大于2000的部门’ FROM dept WHERE dept.

DEPTNO

IN (SELECT emp.

DEPTNO

FROM emp GROUP BY emp.

DEPTNO

HAVING TRUNCATE(AVG(SAL),0) > 2000);

– 按部门平均工资降序排列

SELECT TRUNCATE(AVG(SAL),0) AS ‘部门平均工资’ , emp.

DEPTNO

FROM emp GROUP BY emp.

DEPTNO

ORDER BY TRUNCATE(AVG(SAL),0) DESC;

– 除10号部门外，查询部门平均工资大于2000元的部门

SELECT AVG(SAL) FROM emp WHERE emp.

DEPTNO

!= 10 GROUP BY emp.

DEPTNO

HAVING AVG(SAL) > 2000;

– 显示部门平均工资的最大值

SELECT TRUNCATE(AVG(SAL),0) AS ‘部门平均工资最大值’, emp.

DEPTNO

FROM emp GROUP BY emp.

DEPTNO

ORDER BY TRUNCATE(AVG(SAL),0) DESC LIMIT 0,1;

– 查询员工的编号，姓名，部门名，使用表别名简化

SELECT emp.

EMPNO

, emp.

ENAME

, dept.

DNAME

FROM emp, dept WHERE emp.

DEPTNO

= dept.

DEPTNO

;

– 显示员工的编号，姓名，月薪，工资级别

SELECT emp.

EMPNO

, emp.

ENAME

, emp.

SAL

, salgrade.

GRADE

FROM emp, salgrade WHERE emp.SAL BETWEEN salgrade.

LOSAL

AND salgrade.

HISAL

ORDER BY salgrade.

GRADE

;

– 按部门10,20,30,40号，统计各部门员工人数，要求显示部门号，部门名，人数

SELECT dept.

DEPTNO

, dept.

DNAME

, COUNT(emp.

EMPNO

) FROM dept, emp WHERE dept.

DEPTNO

= emp.

DEPTNO

GROUP BY emp.

DEPTNO

; # 因为一个部门没有数据，所以没有显示全

SELECT dept.

DEPTNO

, dept.

DNAME

, COUNT(emp.

EMPNO

) FROM dept LEFT JOIN emp ON dept.

DEPTNO

= emp.

DEPTNO

GROUP BY emp.

DEPTNO

; # 至于分清左右，主要看全显示的是哪个表，是哪个就朝那边

– 显示每个员工的上级

SELECT a.

ENAME

, b.

ENAME

FROM emp a LEFT JOIN emp b ON b.

EMPNO

= a.

MGR

;

SELECT CONCAT(a.

ENAME

,‘的BOSS是–>’,b.

ENAME

) FROM emp a LEFT JOIN emp b ON b.

EMPNO

= a.

MGR

;

– 查询员工薪资最高的员工信息

SELECT * FROM emp WHERE emp.

SAL

= (SELECT MAX(SAL) FROM emp);

– 查询工资比WARD高的员工信息

SELECT * FROM emp WHERE emp.

SAL

> (SELECT SAL FROM emp WHERE emp.

ENAME

=‘WARD’);

– 查询部门名为’SALES’的员工信息

– 方式一：子查询

SELECT * FROM emp WHERE emp.

DEPTNO

= (SELECT dept.

DEPTNO

FROM dept WHERE dept.

DNAME

=‘SALES’);

– 方式二：多表查询

SELECT emp.* FROM emp, dept WHERE emp.

DEPTNO

= dept.

DEPTNO

AND dept.

DNAME

= ‘SALES’;

– 查询部门名为’ACCOUNTING’或’SALES’的员工信息（多行子查询，使用in关键字）

SELECT * FROM emp WHERE emp.

DEPTNO

IN (SELECT dept.

DEPTNO

FROM dept WHERE dept.

DNAME

= ‘ACCOUNTING’ OR dept.

DNAME

= ‘SALES’);

– 查询部门名不是’ACCOUNTING’或’SALES’的员工信息（多行子查询，使用in关键字）

SELECT * FROM emp WHERE emp.

DEPTNO

NOT IN (SELECT dept.

DEPTNO

FROM dept WHERE dept.

DNAME

= ‘ACCOUNTING’ OR dept.

DNAME

= ‘SALES’);

– 查询工资比20号部门【任意any】一个员工工资【低<】的员工信息（多行子查询，使用any关键字）

SELECT * FROM emp WHERE emp.

SAL

< ANY(SELECT SAL FROM emp WHERE emp.

DEPTNO

= 20);

SELECT * FROM emp WHERE emp.

SAL

< (SELECT MAX(SAL) FROM emp WHERE emp.

DEPTNO

= 20);

– 查询工资比30号部门【所有all】员工【低<】的员工信息（多行子查询，使用all关键字）

SELECT * FROM emp WHERE emp.

SAL

< ALL(SELECT SAL FROM emp WHERE emp.

DEPTNO

= 30);

SELECT * FROM emp WHERE emp.

SAL

< (SELECT MIN(SAL) FROM emp WHERE emp.

DEPTNO

= 30);

– 使用并集运算，查询20号部门或30号部门的员工信息

SELECT * FROM emp WHERE emp.

DEPTNO

= 20

UNION

SELECT * FROM emp WHERE emp.

DEPTNO

= 30;

– union：二个集合中，如果都有相同的，取其一

– union all：二个集合中，如果都有相同的，都取

– 使用交集运算[intersect]，查询工资在1000-2000和1500-2500之间的员工信息（方式一）

– select * from emp where sal between 1000 and 2000

– intersect

– select * from emp where sal between 1500 and 2500;

– （方式二：）

SELECT * FROM emp WHERE (emp.

SAL

BETWEEN 1000 AND 2000) AND (emp.

SAL

BETWEEN 1500 AND 2500);

– 使用差集运算[minus]，查询工资在1000-2000，但不在1500-2500之间的员工信息（方式一）

– select * from emp where sal between 1000 and 2000

– minus

– select * from emp where sal between 1500 and 2500;

– （方式二：）

SELECT * FROM emp WHERE (emp.

SAL

BETWEEN 1000 AND 2000) AND (emp.

SAL

NOT BETWEEN 1500 AND 2500);

集合查询的细节：

1）集合操作时，必须确保集合列数是相等

select empno,ename,sal,comm from emp where deptno = 20

union

select empno,ename,sal from emp where deptno = 30;错

2）集合操作时，必须确保集合列类型对应相同

select empno,ename,sal,comm from emp where deptno = 20

union

select empno,ename,sal,hiredate from emp where deptno = 30;错

3）A union B union C = C union B union A（交换律）

select * from emp where deptno = 10

union

select * from emp where deptno = 20

union

select * from emp where deptno = 30;

4）当多个集合操作时，结果的列名由第一个集合列名决定

select empno “编号”,ename “姓名”,sal “薪水” from emp where deptno = 20

union

select empno,ename,sal from emp where deptno = 10;