🏠關于專欄：半夜學習MySQL專欄用于記錄MySQL數據相關內容。
🎯每天努力一點點，技術變化看得見

---

回顧基本查詢

下面使用幾個案例，一起回顧之前文章所介紹的基本查詢↓↓↓
案例1： 查詢工資高于500或崗位為MANAGER的雇員，同時還要滿足他們的姓名售資委大寫的J

select * from emp where (sal>500 or job='MANAGER') and substring(ename,1,1)='J';

案例2： 按照部門號升序而雇員工資降序排列

select * from emp order by deptno asc, sal desc;

案例3： 對年薪進行降序排列

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

案例4： 顯示工資最高的員工的名字和工作崗位

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

案例5： 顯示工資高于平均工資的員工信息

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

案例6： 顯示每個部門的平均工資和最高工資

select deptno, avg(sal), max(sal) from emp group by deptno;

案例7： 顯示平均工資低于2000的部門號和它的平均工資

select deptno, avg(sal) as avgsal from emp group by deptno having avgsal<2000;

案例8： 顯示每種崗位的雇員總數，平均工資

select count(*) as '雇員總數', format(avg(sal), 2) as '平均工資' from emp group by job;

回顧完的這些查詢操作，都是對一張表進行查詢，但在實際開發中是遠遠不夠的。下面我們就一起來了解學習以下復合查詢。

多表查詢

實際開發中的數據往往來自不同的表，所以需要多表查詢。這里介紹多表查詢使用的oracle9i自帶的scott庫下的emp、dept、salegrade表。先看一下這三張表吧↓↓↓

多表查詢通過案例的方式進行介紹

案例1： 顯示雇員名、雇員工資及其所在部門的名字。
☆ps：雇員名、雇員工資來自emp表，而部門名字在dept表中。我們可以嘗試讓emp表和dept表組合。

select * from emp, dept;

上述組合中：
Ⅰ 從第一張表中選出第一條巨鹿和第二個表的所有集合進行組合；
Ⅱ 然后從第一張表中取第二條數據，和第二張表中的所有記錄組合
Ⅲ 不加過來條件，得到的上圖結果稱為笛卡爾積

但上圖中那么多記錄，我們只需要emp表中的dept等于dept表中的deptno字段的記錄

select emp.ename, emp.sal, dept.dname from emp, dept where emp.deptno=dept.deptno;

案例2： 顯示部門號為10的部門名、員工名和工資

select dname, ename, sal from emp, dept where emp.deptno=dept.deptno and emp.deptno=10;

案例3： 顯示各個員工的姓名、工資及工資級別

select ename, sal, grade from emp, salgrade where emp.sal between losal and hisal;

自連接

上面介紹的是多張表的連接操作，那能否實現一張表實現自己和自己連接呢？這就是自連接。下面圖演示的就是dept表自身和自身的連接↓↓↓

案例： 顯示員工FORD的上級領導的編號和姓名（emp表中mgr表示的是領導的編號）
●方法1：使用子查詢的方式

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

●使用多表查詢（自連接查詢）

select leader.empno, leader.ename from emp leader, emp worker where leader.empno=worker.mgr and worker.ename='FORD';

子查詢

子查詢是嵌入到其他sql語句中的select查詢語句，也叫做嵌套查詢。下文對子查詢的多種情況做出介紹↓↓↓

單行子查詢

子查詢語句返回一行記錄的查詢，稱為單行子查詢
示例： 顯示SMITH同一部門的員工
☆思路：要知道與SMITH同部門的員工，就要先知道SMITH位于哪個部門↓↓↓

select deptno from emp where ename='SMITH';

由上可知SMITH位于20號部門，下面可以找出20號部門的所有員工↓↓↓

select * from emp where deptno=20;

將第一個查詢結果嵌入第二個查詢的where子句中，這就構成嵌套查詢語句↓↓↓

select * from emp where deptno=(select deptno from emp where ename='SMITH');

多行子查詢

如果子查詢返回的結果多條記錄，該子查詢稱為多行子查詢。

● in關鍵字：查詢和10號部門的工作崗位相同的雇員的名字、崗位、工資、部門號，但是不包含10號部門員工

select ename, job, sal, deptno from emp where job in (select 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);

★ps：上述all子句，等同于select max(sal) from emp where depth=30

●any關鍵字：顯示工資比30號部門的任意員工高的員工的姓名、工資和部門號（不包含30號部門的員工）

select ename,sal,deptno from emp where sal > any(select sal from emp where deptno=30) and deptno<>30;

★ps：上述any子句，等同于select min(sal) from emp where depth=30

多列子查詢

單行子查詢是指子查詢結果只返回單列、單行數據；多行子查詢是指返回單列多行數據，都是針對單列而言的。而多列子查詢則是指查詢返回多個列數據的子查詢語句。

案例： 查詢和SMITH的部門和崗位完全相同的所有雇員，不含SMTH本人

select * from emp where (deptno, job) = (select deptno, job from emp where ename='SMITH') and ename<>'SMITH';

★ps：使用多列子查詢，需要保證判斷條件左右兩側列數相同，且列名順序相同。

在from子句中使用子查詢

子查詢語句出現在from子句中，這里可以使用一個數據查詢的技巧，即把子查詢當作一個臨時表使用。

案例1： 顯示每個高于自己部門平均工資的員工的姓名、部門、工資、平均工資
☆思路：要求高于部門平均工資的信息，首先就需要先查詢各個部門的平均工資是多少。

select deptno, avg(sal) from emp group by deptno;

☆思路：讓emp表的每條記錄和上述子查詢結果做笛卡兒積，使用where限定每條記錄后面跟的平均工資是該員工所處部門的平均工資

select * from emp, (select deptno, avg(sal) from emp group by deptno) avgtable where emp.deptno = avgtable.deptno;

☆思路：最后使用where條件限定當前行中的sal要高于平均工資

select * from emp, (select deptno, avg(sal) as agvsal from emp group by deptno) avgtable where emp.deptno = avgtable.deptno and sal > agvsal;

案例2： 查找各個部門工資最高的人的姓名、工資、部門和最高工資
☆思路：首先需要找出各個部門的最高工資

select deptno, max(sal) from emp group by deptno;

☆ps：讓emp表和上述子查詢做笛卡兒積，并使用where條件限定只顯示與emp表當前行記錄的部門的平均工資。

select * from emp, (select deptno, max(sal) from emp group by deptno) maxsal where emp.deptno=maxsal.deptno;

☆思路：最后只要挑選出等于emp表中sal等于最高工資的行即可。

select * from emp, (select deptno, max(sal) ms from emp group by deptno) maxsal where emp.deptno=maxsal.deptno and sal=ms;

案例3： 顯示各個部門的信息（部門名、編號、地址）和人員數量
●方法1：使用多表查詢

select dept.dname, dept.deptno, dept.loc, count(*) as 'personNum' from emp, dept where emp.deptno=dept.deptno group by dept.deptno, dept.dname, dept.loc;

★ps：由于使用group by的查詢語句，只能顯示出現group by中的列字段、聚合函數。故這里將不需要進行排序的deptno.dname,、dept.loc一并放入了group by語句中

●方法2：使用子查詢

select dept.dname, dept.deptno, dept.loc, cp.personNum from dept, (select deptno, count(*) personNum from emp group by deptno) as cp where dept.deptno=cp.deptno;

合并查詢

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

●union
該操作符用于取得兩個結果集的并集，它會自動去掉結果集中的重復記錄。

案例： 將工資大于2500或職位為MANAGER的人顯示出來

select * from emp where sal > 2500;
select * from emp where job='MANAGER';
select * from emp where sal > 2500 union select * from emp where job='MANAGER';

上述結果與select * from emp where sal>2500 or job='MANAGER';效果相同↓↓↓

●union all
該操作符用于取兩個結果集的并集，但它并不會去除重復行↓↓↓

select * from emp where sal > 2500 union all select * from emp where job='MANAGER';

★ps：由于union all不會去除重復行，故上面結果中BLAKE、JONES出現了兩次。

🎈歡迎進入半夜學習MySQL專欄，查看更多文章。
如果上述內容有任何問題，歡迎在下方留言區指正b(￣▽￣)d