多表查詢

1. 分類：   

* 合并結果集

    * 連接配接查詢

    * 子查詢

合并結果集

  * 要求被合并的表中，列的類型和列數相同，字段可以不相同

  * UNION，去除重複行

  * UNION ALL，不去除重複行

SELECT * FROM cd

UNION ALL

SELECT * FROM ab;

示例：

連接配接查詢

1. 分類   

* 内連接配接

    * 外連接配接

      > 左外連接配接

      > 右外連接配接

      > 全外連接配接(MySQL不支援)

    * 自然連接配接（屬于一種簡化方式）

2. 内連接配接

  * 方言連接配接：SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.xx=别名2.xx

    * 标準：SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

    * 自然：SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2

    * 内連接配接查詢出的所有記錄都滿足條件。

标準連接配接：

方言連接配接：

自然連接配接：（不推薦使用，可讀性太差）

輸出：

3. 外連接配接  

    * 左外（把左邊表的所有條件都查出來，若關聯表滿足條件則顯示出來，如是Null 則關聯表全顯示 Null）：SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

      > 左表記錄無論是否滿足條件都會查詢出來，而右表隻有滿足條件才能出來。左表中不滿足條件的記錄，右表部分都為NULL

    * 左外自然：SELECT * FROM 表1 别名1 NATURAL LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

* 右外：SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

      > 右表記錄無論是否滿足條件都會查詢出來，而左表隻有滿足條件才能出來。右表不滿足條件的記錄，其左表部分都為NULL

    * 右外自然：SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

    * 全連結：可以使用UNION來完成全連結

子查詢

：查詢中有查詢（檢視select關鍵字的個數！）

1. 出現的位置： 

    * where後作為條件存在

    * from後作為表存在(多行多列)

2. 條件

    * (***)單行單列：SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 條件)

    * (**)多行單列：SELECT * FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 條件)

    * (*)單行多列：SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 條件)

    * (***)多行多列：SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 條件

練習：資料表

1. 查出至少有一個員工的部門。顯示部門編号、部門名稱、部門位置、部門人數。

select d.deptno,d.dname,d.loc,a.count from dept d,

(select deptno,count(*) count from emp group by deptno) a where d.deptno = a.deptno

2.列出薪金比關羽高的所有員工。

select * from emp where sal > (select sal from emp where ename ='關羽')

3.列出所有員工的姓名及其直接上級的姓名。

select e.ename,m.ename from emp e,emp m where e.mgr=m.empno

4.列出受雇日期早于其直接上級的所有員工的編号,姓名和部門名稱

方法一：

select *from 

(select b.ename,b.hiredate,a.ename fname,a.hiredate fhir ,b.deptno from emp a,emp b where a.empno = 

b.mgr and b.hiredate < a.hiredate)a,dept d

where a.deptno = d.deptno

方法二：

select e1.empno,e1.ename,d.dname from emp e1 

left join emp e2

on e1.mgr = e2.empno

left join dept d

on e1.deptno = d.deptno

where e1.hiredate < e2.hiredate order by e1.empno 

5.列出部門名稱和這些部門的員工資訊，同時列出那些沒有員工的部門。

 select d.dname,e.* from dept d left join emp e on d.deptno=e.deptno

6. 列出所有文員的姓名及其部門名稱，部門的人數。

select a.ename,a.dname,b.count from 

(select e.ename,d.dname,e.deptno from emp e left join  dept d on e.deptno = d.deptno where e.job ='文員') a

left join 

(select deptno,count(*) count from emp group by deptno) b

on a.deptno = b.deptno

7. 列出最低薪金大于15000的各種工作及從事此工作的員勞工數。

  select job,count(*)  from emp  group by job  having min(sal)>15000

8. 列出在銷售部工作的員工的姓名，假定不知道銷售部的部門編号。

 select e.ename from emp e  where e.deptno in ( select deptno from dept  where  dname='銷售部' )

9. 列出薪金高于公司平均薪金的所有員工資訊，所在部門名稱，上級上司，工資等級。

select a.*,e.ename,s.grade from 

(select e.*,d.dname from emp e, dept d where e.deptno = d.deptno and sal >

(select round(avg(sal),2) from emp)) a

left join emp e on a.mgr = e.empno

left join salgrade s on a.sal between s.losal and s.hisal

10.列出與龐統從事相同工作的所有員工及部門名稱。

select e.empno,e.ename,d.dname from emp e,dept d  where job in (select distinct job  from emp

  where ename='龐統') and e.deptno=d.deptno and e.ename!='龐統'

11.列出薪金高于在部門30工作的所有員工的薪金的員工姓名和薪金、部門名稱。

select e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno and sal>

(select max(sal) from emp where deptno = 30)

12.列出在每個部門工作的員工數量、平均工資。

 select d.dname, avg(sal),count(*)from emp e,dept d where e.deptno=d.deptno group by d.dname