JAVA培訓(xùn)之多表查詢(xún)練習(xí)1

多表查詢(xún)練習(xí)一
通過(guò)對(duì)多表查詢(xún)的合并結(jié)果集、連接查詢(xún)以及子查詢(xún)的學(xué)習(xí)，下面我們來(lái)練習(xí)多表查詢(xún)，如下所示：
(1)查詢(xún)至少有一個(gè)員工的部門(mén)，并顯示部門(mén)編號(hào)、部門(mén)名稱(chēng)、部門(mén)位置、部門(mén)人數(shù)：

• 分析：

• 查詢(xún)結(jié)果集包含的列：deptno，dname，loc，部門(mén)人數(shù)；
• 查詢(xún)的表：dept，emp；
• 查詢(xún)條件：dept.deptno=emp.deptno，部門(mén)中至少有一個(gè)員工，即部門(mén)人數(shù)大于或等于1；

• 第一步：獲得每個(gè)部門(mén)的信息，對(duì)應(yīng)sql語(yǔ)句是：select * from dept；
• 第二步：獲取每個(gè)部門(mén)的人數(shù)，利用分組查詢(xún)，對(duì)應(yīng)的sql語(yǔ)句是：select deptno,count(*) from emp group by deptno；
• 第三步：使用內(nèi)連接查詢(xún)以上兩步的結(jié)果集，獲取每個(gè)部門(mén)的編號(hào)，名稱(chēng)，位置及部門(mén)人數(shù)。

• 實(shí)現(xiàn)：

通過(guò)分析可知，對(duì)應(yīng)的sql語(yǔ)句是：

• SELECT

FROM
dept d,(SELECT deptno,COUNT(*) FROM emp GROUP BY deptno) e

以上語(yǔ)句中，查詢(xún)的列不能寫(xiě)成e.count(*)，最好在子查詢(xún)中給count(*)起一個(gè)別名，如下所示：

• SELECT

d.*,e.cnt
FROM
dept d,(SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) e

將以上sql語(yǔ)句執(zhí)行，如圖1-1所示：

圖1-1 多表查詢(xún)練習(xí)1
圖1-1中，查詢(xún)語(yǔ)句中雖然沒(méi)有指定部門(mén)人數(shù)至少為一人，但是仍然是將部門(mén)人數(shù)大于0人的部門(mén)查詢(xún)出來(lái)，原因是，內(nèi)連接只會(huì)查詢(xún)滿(mǎn)足條件的記錄，dept表中的40部門(mén)在emp表中不存在員工，因此不滿(mǎn)足d.deptno=e.deptno這個(gè)條件，所以該部門(mén)就被過(guò)濾掉。
(2)查詢(xún)所有員工的姓名及其直接上級(jí)的姓名：

• 分析：

• 查詢(xún)結(jié)果集包含的列：?jiǎn)T工姓名，上級(jí)姓名；
• 查詢(xún)的表：上級(jí)也屬于員工，因此要查詢(xún)的表是emp表，并且是兩個(gè)emp表，emp e1，emp e2；
• 查詢(xún)條件：e1.mgr=e2.empno；

emp表中，有一個(gè)員工是沒(méi)有上級(jí)的，如果這里使用內(nèi)連接，那么該員工的記錄就會(huì)被過(guò)濾掉，因此這里使用左外連接。

• 實(shí)現(xiàn)：

通過(guò)分析可知，對(duì)應(yīng)的sql語(yǔ)句是：

• SELECT

e1.ename,e2.ename
FROM
emp e1 LEFT OUTER JOIN emp e2
ON

將以上sql語(yǔ)句執(zhí)行，如圖1-2所示：

圖1-2 多表查詢(xún)練習(xí)2
使用左外連接查詢(xún)，左表是e1，因此e1表中的所有員工姓名都被查詢(xún)出來(lái)，e2表中不滿(mǎn)足條件的位置用null代替。