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;