前言

??前面我們講解的mysql表的查詢都是對一張表進行查詢，在實際開發中這遠遠不夠。

1. 基本查詢回顧

??查詢工資高于500或崗位為MANAGER的雇員，同時還要滿足他們的姓名首字母為大寫的J

select * from EMP where (sal>500 or job='MANAGER') and ename like 'J%';

??按照部門號升序而雇員的工資降序排序

select * from EMP order by deptno, sal desc;

??使用年薪進行降序排序

select ename, sal*12+ifnull(comm,0) as '年薪' from EMP order by 年薪 desc;

??顯示工資最高的員工的名字和工作崗位

select ename, job from EMP where sal = (select max(sal) from EMP);

??顯示工資高于平均工資的員工信息

select ename, sal from EMP where sal>(select avg(sal) from EMP);

??顯示每個部門的平均工資和最高工資

select deptno, format(avg(sal), 2) , max(sal) from EMP group by deptno;

??顯示平均工資低于2000的部門號和它的平均工資

select deptno, avg(sal) as avg_sal from EMP group by deptno having avg_sal<2000;

??顯示每種崗位的雇員總數，平均工資

select job,count(*), format(avg(sal),2) from EMP group by job;

2. 多表查詢

??實際開發中往往數據來自不同的表，所以需要多表查詢。本節我們用一個簡單的公司管理系統，有三張表EMP，DEPT，SALGRADE來演示如何進行多表查詢。
??顯示雇員名、雇員工資以及所在部門的名字。 因為上面的數據來自EMP和DEPT表，因此要聯合查詢，其實我們只要emp表中的deptno = dept表中的deptno字段的記錄

select EMP.ename, EMP.sal, DEPT.dname from EMP, DEPT where EMP.deptno = DEPT.deptno;

??顯示部門號為10的部門名，員工名和工資

select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno and DEPT.deptno = 10;

??顯示各個員工的姓名，工資，及工資級別

select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;

3. 自連接

??自連接是指在同一張表連接查詢
??顯示員工FORD的上級領導的編號和姓名（mgr是員工領導的編號–empno）
??使用的子查詢：

select empno,ename from emp where emp.empno=(select mgr from emp where ename='FORD');

??使用多表查詢（自查詢）

-- 使用到表的別名
--from emp leader, emp worker，給自己的表起別名，因為要先做笛卡爾積，所以別名可以先識別select leader.empno,leader.ename from emp leader, emp worker whereleader.empno = worker.mgr and worker.ename='FORD';

4. 子查詢

??子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢

4.1 單行子查詢

??返回一行記錄的子查詢
??顯示SMITH同一部門的員工

select * from EMP WHERE deptno = (select deptno from EMP where ename='smith');

4.2 多行子查詢

??返回多行記錄的子查詢
??in關鍵字；查詢和10號部門的工作崗位相同的雇員的名字，崗位，工資，部門號，但是不包含10自己的

select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno<>10;

??all關鍵字； 顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號

select ename, sal, deptno from EMP where sal > all(select sal from EMP where deptno=30);

??any關鍵字； 顯示工資比部門30的任意員工的工資高的員工的姓名、工資和部門號（包含自己部門的員工）

select ename, sal, deptno from EMP where sal > any(select sal from EMP where deptno=30);

4.3 多列子查詢

??單行子查詢是指子查詢只返回單列，單行數據；多行子查詢是指返回單列多行數據，都是針對單列而言的，而多列子查詢則是指查詢返回多個列數據的子查詢語句
??查詢和SMITH的部門和崗位完全相同的所有雇員，不含SMITH本人

mysql> select ename from EMP where (deptno, job)=(select deptno, job from EMPwhere ename='SMITH') and ename <> 'SMITH';

4.4 在from子句中使用子查詢

??子查詢語句出現在from子句中。這里要用到數據查詢的技巧，把一個子查詢當做一個臨時表使用。
??顯示每個高于自己部門平均工資的員工的姓名、部門、工資、平均工資。

//獲取各個部門的平均工資，將其看作臨時表select ename, deptno, sal, format(asal,2) from EMP,(select avg(sal) asal, deptno dt from EMP group by deptno) tmpwhere EMP.sal > tmp.asal and EMP.deptno=tmp.dt;

??查找每個部門工資最高的人的姓名、工資、部門、最高工資

select EMP.ename, EMP.sal, EMP.deptno, ms from EMP,(select max(sal) ms, deptno from EMP group by deptno) tmpwhere EMP.deptno=tmp.deptno and EMP.sal=tmp.ms;

??顯示每個部門的信息（部門名，編號，地址）和人員數量
??方法1：使用多表

select DEPT.dname, DEPT.deptno, DEPT.loc,count(*) '部門人數' from EMP,DEPTwhere EMP.deptno=DEPT.deptno group by DEPT.deptno,DEPT.dname,DEPT.loc;

??方法2：使用子查詢

-- 1. 對EMP表進行人員統計
select count(*), deptno from EMP group by deptno;-- 2. 將上面的表看作臨時表
select DEPT.deptno, dname, mycnt, loc from DEPT,(select count(*) mycnt, deptno from EMP group by deptno) tmpwhere DEPT.deptno=tmp.deptno;

4.5 合并查詢

??在實際應用中，為了合并多個select的執行結果，可以使用集合操作符 union，union all

4.5.1 union

??該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中的重復行。
??將工資大于2500或職位是MANAGER的人找出來

select ename, sal, job from EMP where sal>2500 unionselect ename, sal, job from EMP where job='MANAGER';--去掉了重復記錄

4.5.2 union all

??該操作符用于取得兩個結果集的并集。當使用該操作符時，不會去掉結果集中的重復行。
??將工資大于25000或職位是MANAGER的人找出來

 select ename, sal, job from EMP where sal>2500 union allselect ename, sal, job from EMP where job='MANAGER';