第36次文章：資料庫查詢語句

  • 2019 年 10 月 8 日
  • 筆記

本周繼續進階資料庫的查詢語句！內容還是有點多的呀！

進階6：連接查詢

一、基本內容

1、含義

又稱為多表查詢，當查詢的欄位來自於多個表時，就會使用到連接查詢。

2、笛卡爾乘積現象

表1 有m行，表2 有n行， 結果= m*n行。

發生原因：沒有有效的連接條件。

如何避免：添加有效的連接條件。

3、分類

（1）按年代分類，分別是1992年和1999年產生的標準。

sql92標準：僅僅支援內連接。

sql99標準【推薦】：支援內連接+外連接（左外和右外）+交叉連接。

（2）按功能分類：

  • 內連接：等值連接、非等值連接、自連接
  • 外連接：左外連接、右外連接、全外連接
  • 交叉連接
二、sql92語法

由於在sql語法中，僅僅支援內連接，所以我們對sql92語法標準的介紹僅限於內連接的三種方式。

1、等值連接

（1）基本語法

select 查詢列表 from 表1,表2

where 連接條件 【and 篩選條件】

【group by 分組條件】

【having 分組後篩選】

【order by 排序條件】

【注】：【】中的內容均為可選項目，根據用戶自己的需求進行添加。

（2）基本特點

  • 多表等值連接的結果為多表的交集部分
  • n表連接，至少需要n-1個連接條件
  • 多表的順序沒有要求
  • 一般需要為表起別名
  • 可以搭配前面介紹的所有子句使用，比如排序，分組，篩選

（3）示例

我們根據上面的基本特點，列舉一個包含所有特點的案例，具體如下：

#案例：查詢員工名，所在部門編號，部門名，以及部門所在城市  SELECT empname,e.deptId,d.dname,c.cityName  FROM emp e,dept d,city c  WHERE e.deptId = d.deptId AND d.location_id = c.location_id  ORDER BY e.deptId;

最後的結果如下

tips：在上面的案例中，我們實現了3表連接。為了將每張表進行一個連接，我們使用了2個連接條件。分別是emp表和dept表的部門編號進行關聯，dept表的與city表的位置標號location_id 相關聯。與此同時，我們為3張表格分別起了相應的別名，主要是為了在後續獲取每張表的屬性值時更加方便。在內連接的中，最後顯示出來的屬於三張表的交集部分，如果有兩張表的關聯條件不匹配的時候，那麼這條記錄就不會顯示出來。

2、非等值連接

（1）基本特點

非等值連接與等值的語法和特點均類似，唯一的區別在於連接條件。此時的連接條件成為了一個不等式而不是一個等式。

（2）示例

為了對非等值連接的進行演示，我們創建一個job_grade表格，具體的內容如下：

我們的示例內容如下所示 

#案例1：查詢員工的工資和工資級別  SELECT empname,salary,grade_level  FROM emp e,job_grade g  WHERE e.salary BETWEEN g.lowest_sal AND g.highest_sal;

最後的結果如下

tips：通過上述的簡單介紹，可以發現，整體的語法結構與等值連接完全相同，唯一差異的地方就在於連接條件由等值變為了區間範圍連接。

3、自連接

（1）基本概念

自連接的語法與等值連接的語法完全相同。獨特之處在於自連接的意義和內涵。自連接是將同一張表格當做兩張表格來使用，也就是自己與自己進行相互連接。為兩張表格起不同的別名。具體的妙處我們通過一個案例來感受一下！

（2）示例

#案例1：查詢 員工名和上級的名稱  select e.id,e.empname,e.manager_id,m.empname manager_name  from emp e,emp m  WHERE e.manager_id = m.id;

最後的結果：

tips：在這個查詢案例中，我們需要根據每個員工對應的manager_id來判斷其領導的編號，然後重新根據manager_id對應到emp表中的id中，再次尋找此id對應的姓名。內在邏輯在於：領導，也同樣是員工，也處於員工表中。所以我們使用自連接，對第一次使用到的emp表用別名e來代替員工表，對第二次使用到的emp表用別名m來代替領導表。

三、sql99語法

1、基本內容

（1）語法

select 查詢列表

from 表1 別名 【連接類型】 join 表2 on 連接條件

【where 篩選條件】

【group by 分組列表】

【having 分組後的篩選】

【order by 排序列表】

【limit 子句】

（2）分類

  • 內連接：inner
  • 外連接：

左外：left 【outer】

右外：right 【outer】

全外：full 【outer】

  • 交叉連接：cross

2、內連接

（1）特點

  • 表的順序可以調換
  • 內連接的結果=多表的交集
  • n表連接至少需要n-1個連接條件

（2）分類

等值連接、非等值連接、自連接

（3）示例

在sql92語法中，我們已經對內連接的3種分類進行了詳細講解。為了避免冗餘，我們在此處僅給出其中一種內連接的示例進行詳解。

#案例.查詢哪個部門的員工個數>3的部門名和員工個數，並按個數降序（排序）  SELECT dname,COUNT(*)  FROM emp e INNER JOIN dept d ON e.deptId = d.deptId  GROUP BY e.deptId  HAVING COUNT(*)>2  ORDER BY count(*) DESC;

查看一下結果：

tips：我們可以將上面給出的案例與sql92語法中的案例進行對比，可以發現，sql99語法將連接條件使用關鍵字on進行連接，與篩選條件分開，具有更強的可讀性。在其他的條件語法上，與sql92沒有區別。

3、外連接

應用場景：用於查詢一個表中有，另一表沒有的記錄。

（1）特點

  • 查詢的結果=主表中所有的行，如果從表和它匹配的將顯示匹配行，如果沒有匹配的則顯示null。
  • left join 左邊的為主表，right join 右邊的為主表，左外和右外交換兩個表的順序，可以實現同樣的效果。
  • 一般用於查詢除了交集部分的剩餘的不匹配的行。
  • full join 兩邊都是主表，左外和右外交換兩個表的順序，可以實現同樣的效果。
  • 全外連接=內連接的結果+表1中有但表2中沒有的+表2中有但表1中沒有的。

（2）分類

左外連接、右外連接、全外連接（其中，mysql不支援全外連接）

（3）示例

#案例：查詢哪個部門沒有員工    #左外  SELECT d.deptId,d.dname  FROM dept d LEFT JOIN emp e ON e.deptId = d.deptId  WHERE e.id IS NULL;    #右外  SELECT d.deptId,d.dname  FROM emp e RIGHT OUTER JOIN dept d ON e.deptId = d.deptId  WHERE e.id IS NULL;    #全外(mysql不支援全外連接,此處僅作為語法展示，沒有運行後的結果)  SELECT e.*,d.*  FROM emp e FULL OUTER JOIN dept d ON e.deptId = d.deptId;

左外結果與右外結果相同，如下所示

tips：sql99語法中，可以提供三種外連接，但是我們使用的是mysql資料庫，不支援全外連接，所以我們最後僅僅運行左外和右外的結果。兩者的結果相同，與我們在外連接特點中表述的相同。

4、交叉連接

（1）特點

類似於笛卡爾乘積

（2）示例

#交叉連接 也就是笛卡爾乘積  SELECT e.*,d.*  FROM emp e  CROSS JOIN dept d;

查看一下結果：

tips：上面展示出來的僅僅是結果集的一部分。全部結果是兩張表的笛卡爾集。

四、小總結

1、sql92語法 PK sql99語法

功能：sql99支援的較多。

可讀性：sql99實現連接條件和篩選條件的分離，可讀性較高。

所以我們在使用連接查詢的時候，更加推薦使用sql99語法。

2、幾種連接區別

內連接，左外連接，右外連接，以及其他集合形式的實現方法

進階7：子查詢

一、基本概念

1、含義

嵌套在其他語句內部的select語句稱為子查詢或內查詢。

外面的語句可以是insert、update、delete、select等，一般select作為外面的語句較多。

外面如果為select語句，則此語句稱為外查詢或主查詢。

2、分類

（1）按出現位置

select後面：標量子查詢

from後面：表子查詢

where 或 having後面：標量子查詢、列子查詢、行子查詢

exists後面：標量子查詢、列子查詢、行子查詢、表子查詢

（2）按結果集的行列

標量子查詢（單行子查詢）：結果集為一行一列

列子查詢（多行子查詢）：結果集為多行一列

行子查詢：結果集為多行多列

表子查詢：結果集為多行多列

【注】：子查詢的分類較多，在使用的時候，主要出現的情況是上面標註顏色的幾種情況。下面我們將根據不同的分類來給出具體的案例進行講解。

二、where或having後面

1、主要分類

（1）標量子查詢（單行子查詢）

（2）列子查詢（多行子查詢）

（3）行子查詢（多行多列）

2、特點

（1）子查詢放在小括弧內

（2）子查詢一般放在條件的右側

（3）標量子查詢，一般搭配著單行操作符使用：< > = >= <= <>

（4）列子查詢，一般搭配著多行操作符使用：IN、ANY/SOME、ALL

（5）子查詢的執行優先於主查詢執行，主查詢的條件用到了子查詢的結果

3、示例

我們根據上面的分類，對每一類給出一個典型案列，結合其語法和結果一起分析

#1、標量子查詢  #案例：查詢最低工資大於1號部門最低工資的部門id和其最低工資  select MIN(salary),deptId from emp GROUP BY deptId  HAVING MIN(salary) > (select MIN(salary) from emp WHERE deptId = 1 );    #2、列子查詢（多行子查詢）  #案例：返回其他部門中比deptId = 1部門任一工資低的員工的資訊  SELECT * FROM emp  WHERE salary < (SELECT MAX(salary) FROM emp WHERE deptId =1) AND deptId <> 1;  #或  SELECT * FROM emp  WHERE salary < ANY(SELECT salary FROM emp WHERE deptId =1) AND deptId <> 1;    #3、行子查詢（結果集一行多列或多行多列）  #案例：查詢員工編號最小並且工資最高的員工資訊  SELECT * from emp  WHERE id = (select MIN(id) FROM emp )  AND salary = (SELECT MAX(salary) FROM emp)  #或  SELECT * from emp  WHERE (id,salary)= (select MIN(id),MAX(salary) FROM emp)

標量子查詢結果：

列子查詢結果：

行子查詢結果：

tips：通過三者的對比我們可以做出一定的總結。出現在where和having後面的子查詢，都屬於產生篩選條件的值，標量子查詢直接產生一個值；列子查詢返回一系列的值，但是需要配合any,some等關鍵字進行使用，最後依舊是使用一個常量值來代替篩選條件；對於行子查詢，是對於多個欄位的值進行同時匹配，需要每個欄位的交集，在這種篩選條件下，一般很難有相關的資訊匹配成功，所以在日常情況中也很少使用到。

三、select後面

1、特點

僅僅支援標量子查詢。

2、示例

#案例：查詢每個部門的詳細資訊以及員工數  select d.*,(SELECT COUNT(*) FROM emp e WHERE e.deptId = d.deptId) 部門的員工個數  FROM dept d;

查詢結果

tips：這個案例很好的展示了一種情況，子查詢得到的結果，也可以直接放在select語句後面，作為查詢列表被直接顯示出來。

四、from後面

1、特點

將子查詢結果充當一張表，要求必須起別名。

2、示例

#案例：查詢每個部門的平均工資的工資等級    #（1）查詢每個部門的平均工資  SELECT AVG(salary),deptId FROM emp GROUP BY deptId;  #（2）連接（1）的結果集和job_grade表，篩選條件平均工資 between lowest_sal AND highest_sal  SELECT a.*,j.grade_level  FROM (SELECT AVG(salary) ag_d,deptId FROM emp GROUP BY deptId) a  LEFT OUTER JOIN job_grade j ON a.ag_d BETWEEN j.lowest_sal AND j.highest_sal;

結果圖：

tips：在上面的案例中，我們總共分為兩步進行查詢，第一步查詢出每個部門的平均工資之後，mysql中會產生一個虛擬表格，我們需要對這個虛擬表格起一個別名，這樣才能夠在後續添加連接條件的時候使用到此虛擬表。當我們對第一次產生的虛擬表格起一個別名之後，我們就可以將其放在from後面，作為一張新表，與job_grade表格進行內連接，然後添加相應的連接條件，得到最後的結果。

五、exists後面

1、語法

EXISTS(完整的查詢語句)

結果：1或0

2、示例

#案例1：查詢有員工名的部門名  SELECT dname FROM dept d  WHERE EXISTS(SELECT * FROM emp e WHERE e.deptId = d.deptId);  #或  SELECT DISTINCT d.dname  FROM emp e INNER JOIN dept d  ON e.deptId = d.deptId;

結果

tips：在上面示例中，我們主要解釋一下exists方式的查詢。部門表中的每個部門編號如果存在員工表中，那麼我們就列舉出此員工的詳細資訊，然後使用exist來判斷此子查詢是否存在值，如果有值，則返回1，如果沒有，則返回0，當返回1的時候，就滿足篩選條件，然後主查詢就顯示出此部門的名稱。

進階8：分頁查詢

一、應用場景

當要查詢的條目數太多，一頁顯示不全。這個時候我們就需要使用到分頁查詢，對每頁顯示的內容進行限定。

二、語法

select 查詢列表 from 表

limit 【offset】，size；

【注】：

（1）offset代表的是起始的條目索引，當起始索引不寫的時候，我們默認為從0開始；

（2）size代表的是顯示的條目數。

三、特點

（1）limit語句放在查詢語句的最後

（2）公式

假如要顯示的頁數為page，每一頁條目數為size，那麼起始索引為：

offset=(page-1)*size

四、示例
#案例：有獎金的員工資訊，並且工資較高的前5名顯示  select * FROM emp WHERE bonus is NOT NULL  ORDER BY salary DESC  LIMIT 5;

結果：

tips：在分頁查詢的案例中，我們省略了起始索引，程式中默認從0開始，給我們輸出了排序之後的前五條記錄。

VirMach 便宜 VPS

QNews

QNews