Mysql基礎8-多表查詢

一、多表關係

一對多或者多對一
- 案例：部門與員工的關係
- 關係：一個部門對應多個員工，一個員工對應一個部門（不考慮跨部門的特殊情況）
- 實現：在多的一方建立外來鍵，指向一的一方的主鍵，這裡員工表是多的的一方，部門表是一的一方

多對多
- 案例：學生與課程的關係
- 關係：一個學生可以選修多門課程，一門課程也可以供多個學生選擇
- 實現：建立第三張中間表，中間表至少包含兩個外來鍵，分別關聯兩方主鍵

一對一
- 案例：使用者與使用者詳情的關係
- 關係：一對一關係，多用於單表拆分，將一張表的基礎欄位放在一張表，其他詳情欄位放在另外一張表中，藝體生操作效率
- 實現：在任意一方加入外來鍵，關聯另外一方的主鍵，並且設定為唯一的(unique)
- 說明：這個是外來鍵user_id需要加一個約束保證其唯一，從而保證一個詳情只能對應一個使用者。

二、多表查詢

　　2.1、概述：

　　　　多表查詢指從多張資料表中查詢資料

　　2.2、分類：

- 連線查詢
  - 內連線：相當於查詢A、B交集部分資料
  - 外連線
    - 左外連線：查詢左表所有資料，以及兩張表交集部分資料
    - 右外連線：查詢右表所有資料，已經兩張表交集部分資料：
  - 自連線：當前表與自身的連線查詢，連線必須使用表別名
- 子查詢
- 聯合查詢

三、範例資料表結構及資料

　　3.1、emp：員工表結構及資料

mysql> select * from emp;
+----+--------+------+--------------+--------+------------+-----------+---------+
| id | name   | age  | job          | salary | entrydate  | managerid | dept_id |
+----+--------+------+--------------+--------+------------+-----------+---------+
|  1 | 張三   |   43 | 董事長       |  48000 | 2017-07-20 |      NULL |       5 |
|  2 | 李四   |   38 | 專案經理     |  23900 | 2016-08-20 |         1 |       1 |
|  3 | 問問   |   22 | 開發         |  18000 | 2022-07-20 |         2 |       1 |
|  4 | 芳芳   |   22 | 開發         |  21000 | 2019-08-18 |         2 |       1 |
|  5 | 珊珊   |   22 | 開發         |  15000 | 2021-04-10 |         3 |       1 |
|  6 | 娜娜   |   25 | 財務         |  24000 | 2023-07-16 |         1 |       3 |
|  7 | 咔咔   |   25 | 出納         |   8000 | 2021-07-10 |         6 |       3 |
|  8 | 靜靜   |   27 | 人事         |   5000 | 2021-07-11 |         1 |    NULL |
+----+--------+------+--------------+--------+------------+-----------+---------+
8 rows in set (0.00 sec)

　　3.2、dept：部門表結構及資料

mysql> select * from dept;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 研發部    |
|  2 | 市場部    |
|  3 | 財務部    |
|  4 | 銷售部    |
|  5 | 總經辦    |
|  6 | 人事部    |
+----+-----------+
6 rows in set (0.00 sec)

　　3.3、score1：成績表1結構及資料

mysql> select * from score1;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | 張三   |    94 |
|  2 | 李四   |    93 |
|  3 | 王五   |    87 |
|  4 | 趙六   |    71 |
+----+--------+-------+
4 rows in set (0.00 sec)

　　3.4、score2：成績表2結構及資料

mysql> select * from score2;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | 張三   |    94 |
|  2 | 李四   |    97 |
|  3 | 王五   |    91 |
|  4 | 趙六   |    82 |
+----+--------+-------+
4 rows in set (0.00 sec)

四、內連線

　　4.1、概述：

　　　　內連線查詢的是兩張表的交集的部分

　　4.2、內連線查詢語法

　　　　4.2.1 隱式內連線

select 欄位列表 from 表1，表2 where 條件...;

　　　　4.2.2 顯示內連線 inner可以省略不寫

select 欄位列表 from 表1 [inner] join 表2 on 連線條件...;

　　4.3、案例

　　　　案例1：查詢每個員工的姓名，及關聯的部門的名稱（隱式內連線實現）

mysql> select emp.name,dept.name as dept_name from emp,dept where emp.dept_id = dept.id;
+--------+-----------+
| name   | dept_name |
+--------+-----------+
| 張三   | 總經辦    |
| 李四   | 研發部    |
| 問問   | 研發部    |
| 芳芳   | 研發部    |
| 珊珊   | 研發部    |
| 娜娜   | 財務部    |
| 咔咔   | 財務部    |
+--------+-----------+
7 rows in set (0.00 sec)

　　　　案例2：查詢每個員工的姓名，及關聯的部門的名稱（顯示內連線實現）

mysql> select emp.name,dept.name as dept_name from emp inner join dept on emp.dept_id = dept.id;
+--------+-----------+
| name   | dept_name |
+--------+-----------+
| 張三   | 總經辦    |
| 李四   | 研發部    |
| 問問   | 研發部    |
| 芳芳   | 研發部    |
| 珊珊   | 研發部    |
| 娜娜   | 財務部    |
| 咔咔   | 財務部    |
+--------+-----------+
7 rows in set (0.00 sec)

五、外連線

　　5.1、左外連線

　　　　簡介：相當於查詢表1(左表)的所有資料，包含表1和表2交集部分的資料

select 欄位列表 from 表1 left [outer] join 表2 on 條件...;

　　5.2、右外連線

　　　　簡介：相當於查表2(右表)的所有資料，包含表1和表2交集部分的資料

select 欄位列表 from 表1 right [outer] join 表2 on 條件...;

　　5.3、案例

　　　　案例1：查詢emp表的所有資料，和對應的部門資訊(左外連線)

mysql> select emp.*, dept.name as dept_name from emp left join dept on emp.dept_id = dept.id;
+----+--------+------+--------------+--------+------------+-----------+---------+-----------+
| id | name   | age  | job          | salary | entrydate  | managerid | dept_id | dept_name |
+----+--------+------+--------------+--------+------------+-----------+---------+-----------+
|  1 | 張三   |   43 | 董事長       |  48000 | 2017-07-20 |      NULL |       5 | 總經辦    |
|  2 | 李四   |   38 | 專案經理     |  23900 | 2016-08-20 |         1 |       1 | 研發部    |
|  3 | 問問   |   22 | 開發         |  18000 | 2022-07-20 |         2 |       1 | 研發部    |
|  4 | 芳芳   |   32 | 開發         |  21000 | 2019-08-18 |         2 |       1 | 研發部    |
|  5 | 珊珊   |   27 | 開發         |  15000 | 2021-04-10 |         3 |       1 | 研發部    |
|  6 | 娜娜   |   25 | 財務         |  24000 | 2023-07-16 |         1 |       3 | 財務部    |
|  7 | 咔咔   |   29 | 出納         |   8000 | 2021-07-10 |         6 |       3 | 財務部    |
|  8 | 靜靜   |   27 | 人事         |   5000 | 2021-07-11 |         1 |    NULL | NULL      |
+----+--------+------+--------------+--------+------------+-----------+---------+-----------+
8 rows in set (0.00 sec)

　　　　說明1：查詢emp表的所有資料，即emp.*

　　　　說明2：as dept_name 是給dept.name 起的別名，防止查詢結果中出現兩個name欄位，會有歧義

　　　　說明3：from 後面的是左表，所以該語句中emp是左表

　　　　說明4：join 後面的是右表，所以該語句中的dept是右表

　　　　說明5：連線關係是emp.dept_id = dept.id

　　　　說明6：outer關鍵字是可以省略的，不影響結果

　　　　案例2：查詢dept表的所有資料，和對應的員工資訊(右外連線)

mysql> select dept.*, emp.* from emp right join dept on emp.dept_id = dept.id;
+----+-----------+------+--------+------+--------------+--------+------------+-----------+---------+
| id | name      | id   | name   | age  | job          | salary | entrydate  | managerid | dept_id |
+----+-----------+------+--------+------+--------------+--------+------------+-----------+---------+
|  1 | 研發部    |    2 | 李四   |   38 | 專案經理     |  23900 | 2016-08-20 |         1 |       1 |
|  1 | 研發部    |    3 | 問問   |   22 | 開發         |  18000 | 2022-07-20 |         2 |       1 |
|  1 | 研發部    |    4 | 芳芳   |   22 | 開發         |  21000 | 2019-08-18 |         2 |       1 |
|  1 | 研發部    |    5 | 珊珊   |   22 | 開發         |  15000 | 2021-04-10 |         3 |       1 |
|  2 | 市場部    | NULL | NULL   | NULL | NULL         |   NULL | NULL       |      NULL |    NULL |
|  3 | 財務部    |    6 | 娜娜   |   25 | 財務         |  24000 | 2023-07-16 |         1 |       3 |
|  3 | 財務部    |    7 | 咔咔   |   25 | 出納         |   8000 | 2021-07-10 |         6 |       3 |
|  4 | 銷售部    | NULL | NULL   | NULL | NULL         |   NULL | NULL       |      NULL |    NULL |
|  5 | 總經辦    |    1 | 張三   |   43 | 董事長       |  48000 | 2017-07-20 |      NULL |       5 |
|  6 | 人事部    | NULL | NULL   | NULL | NULL         |   NULL | NULL       |      NULL |    NULL |
+----+-----------+------+--------+------+--------------+--------+------------+-----------+---------+
10 rows in set (0.00 sec)

　　　　說明1：查詢dept表的所有資料，又因為要求使用右連線，所以需要將右表作為主表，即dept要放在join關鍵字的後面

　　　　說明2：連線關係依然是emp.dept_id = dept.id

mysql> select dept.*, emp.* from dept left join emp on emp.dept_id = dept.id;
+----+-----------+------+--------+------+--------------+--------+------------+-----------+---------+
| id | name      | id   | name   | age  | job          | salary | entrydate  | managerid | dept_id |
+----+-----------+------+--------+------+--------------+--------+------------+-----------+---------+
|  1 | 研發部    |    2 | 李四   |   38 | 專案經理     |  23900 | 2016-08-20 |         1 |       1 |
|  1 | 研發部    |    3 | 問問   |   22 | 開發         |  18000 | 2022-07-20 |         2 |       1 |
|  1 | 研發部    |    4 | 芳芳   |   22 | 開發         |  21000 | 2019-08-18 |         2 |       1 |
|  1 | 研發部    |    5 | 珊珊   |   22 | 開發         |  15000 | 2021-04-10 |         3 |       1 |
|  2 | 市場部    | NULL | NULL   | NULL | NULL         |   NULL | NULL       |      NULL |    NULL |
|  3 | 財務部    |    6 | 娜娜   |   25 | 財務         |  24000 | 2023-07-16 |         1 |       3 |
|  3 | 財務部    |    7 | 咔咔   |   25 | 出納         |   8000 | 2021-07-10 |         6 |       3 |
|  4 | 銷售部    | NULL | NULL   | NULL | NULL         |   NULL | NULL       |      NULL |    NULL |
|  5 | 總經辦    |    1 | 張三   |   43 | 董事長       |  48000 | 2017-07-20 |      NULL |       5 |
|  6 | 人事部    | NULL | NULL   | NULL | NULL         |   NULL | NULL       |      NULL |    NULL |
+----+-----------+------+--------+------+--------------+--------+------------+-----------+---------+
10 rows in set (0.00 sec)

　　　　說明3：左連線和右連線其實是可以互換的，主要看錶的位置

六、自連線

　　6.1、自連線查詢語法

select 欄位列表 from 表A，別名A join 表A 別名B on 條件...;

　　6.2、自連線查詢

　　　　可以是內連線查詢，也可以是外連線查詢

　　6.3、案例

　　　　案例1：查詢員工及其所屬的領導的名字

mysql> select a.name, b.name from emp as a,emp as b where a.managerid = b.id;
+--------+--------+
| name   | name   |
+--------+--------+
| 李四   | 張三   |
| 問問   | 李四   |
| 芳芳   | 李四   |
| 珊珊   | 問問   |
| 娜娜   | 張三   |
| 咔咔   | 娜娜   |
| 靜靜   | 張三   |
+--------+--------+
7 rows in set (0.00 sec)

　　　　說明1：通過where 將兩個表連線起來的方式，內連線方式

　　　　說明2：因為是內連線方式，所以id=1的張三，所屬領導為null,就不會被查詢出來

　　　　說明3：自連線必須要起別名，而且要給自己起兩個別名。相互連線才行，所以自連線，就是一個表起了兩個別名互相連線

　　　　案例2：查詢所有員工 emp 及其領導的名字，如果員工沒有領導也需要查詢出來

mysql> select a.name as "員工", b.name as "領導" from emp as a left join emp as b on a.managerid = b.id;
+--------+--------+
| 員工   | 領導   |
+--------+--------+
| 張三   | NULL   |
| 李四   | 張三   |
| 問問   | 李四   |
| 芳芳   | 李四   |
| 珊珊   | 問問   |
| 娜娜   | 張三   |
| 咔咔   | 娜娜   |
| 靜靜   | 張三   |
+--------+--------+
8 rows in set (0.00 sec)

　　　　說明1：因為沒有領導的也要查詢出來，所以就不能使用內連線，需要使用外連線，left和right都可以

　　　　說明2：同樣需要給emp起兩個別名

七、聯合查詢

　　7.1、聯合查詢：

　　　　union / union all 就是把多次查詢的結果合併起來，形成一個新的查詢結果集

select 欄位列表 from 表A ...

union [all]

select 欄位列表 from 表B ...

　　　　說明1：union all 會將查詢出來的兩個結果集全部合併，不會去重

　　　　說明2：union 如果不帶all 則會將兩個結果集合並，並且去重　　

　　　　說明3：對於聯合查詢的多張表的列數和欄位型別必須要保持一致

　　7.2、案例

　　　　案例1：現在有score1和score2兩張成成績表，將兩張表中都大於90分的查詢出來合併顯示

　　　　分析1：先查詢出來score1表中大於90的學生

mysql> select * from score1 where score>90;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | 張三   |    94 |
|  2 | 李四   |    93 |
+----+--------+-------+
2 rows in set (0.00 sec)

　　　　分析2：在查詢出來score2表中大於90的學生

mysql> select * from score2 where score>90;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | 張三   |    94 |
|  2 | 李四   |    97 |
|  3 | 王五   |    91 |
+----+--------+-------+
3 rows in set (0.00 sec)

　　　　分析3：聯合查詢

mysql> select * from score1 where score>90 union all select * from score2 where score>90;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | 張三   |    94 |
|  2 | 李四   |    93 |
|  1 | 張三   |    94 |
|  2 | 李四   |    97 |
|  3 | 王五   |    91 |
+----+--------+-------+
5 rows in set (0.00 sec)

　　　　說明1：因為使用的是union all 所以保留了所有資料

mysql> select * from score1 where score>90 union select * from score2 where score>90;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | 張三   |    94 |
|  2 | 李四   |    93 |
|  2 | 李四   |    97 |
|  3 | 王五   |    91 |
+----+--------+-------+
4 rows in set (0.00 sec)

　　　　說明2：去掉了all 直接只使用了union，這個時候張三，94分重複的就只顯示一次了

　　　　說明3：李四=93和李四=97,是兩個不同的資料，所以不會被合併。

八、子查詢

　　8.1、概念：

　　　　sql語句中巢狀select語句，成為巢狀查詢，又稱子查詢

select * from t1 where column1=(select column1 from t2);

　　8.2、根據結果分類

- 標量子查詢：子查詢結果為單個值
- 列子查詢：子查詢結果為一列
- 行子查詢：子查詢結果為一行
- 表子查詢：子查詢結果為多行多列

　　8.3、根據位置分類

- where之後的子查詢
- from 之後的子查詢
- select之後的子查詢

　　8.4、標量子查詢

　　　　8.4.1 概念：子查詢返回的結果是單個值(數位，字串，日期等)，最簡單的形式，這種子查詢稱為標量子查詢

　　　　8.4.2 常用的操作符：=, <, >, >=, <=

　　　　8.4.3 案例

　　　　　　案例1：查詢"研發部"的所有員工資訊

　　　　　　　　分析1：先查詢研發部的部門id

mysql> select id from dept where name="研發部";
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql>

　　　　　　　　分析2：根據研發部的部門id,查詢員工資訊

mysql> select * from emp where dept_id=1;
+----+--------+------+--------------+--------+------------+-----------+---------+
| id | name   | age  | job          | salary | entrydate  | managerid | dept_id |
+----+--------+------+--------------+--------+------------+-----------+---------+
|  2 | 李四   |   38 | 專案經理     |  23900 | 2016-08-20 |         1 |       1 |
|  3 | 問問   |   22 | 開發         |  18000 | 2022-07-20 |         2 |       1 |
|  4 | 芳芳   |   22 | 開發         |  21000 | 2019-08-18 |         2 |       1 |
|  5 | 珊珊   |   22 | 開發         |  15000 | 2021-04-10 |         3 |       1 |
+----+--------+------+--------------+--------+------------+-----------+---------+
4 rows in set (0.01 sec)

　　　　　　　　分析3：使用標量子查詢

mysql> select * from emp where dept_id = (select id from dept where name = "研發部");
+----+--------+------+--------------+--------+------------+-----------+---------+
| id | name   | age  | job          | salary | entrydate  | managerid | dept_id |
+----+--------+------+--------------+--------+------------+-----------+---------+
|  2 | 李四   |   38 | 專案經理     |  23900 | 2016-08-20 |         1 |       1 |
|  3 | 問問   |   22 | 開發         |  18000 | 2022-07-20 |         2 |       1 |
|  4 | 芳芳   |   22 | 開發         |  21000 | 2019-08-18 |         2 |       1 |
|  5 | 珊珊   |   22 | 開發         |  15000 | 2021-04-10 |         3 |       1 |
+----+--------+------+--------------+--------+------------+-----------+---------+
4 rows in set (0.00 sec)

　　　　　　案例2：查詢珊珊入職之後的員工資訊

　　　　　　　　分析1：查詢「珊珊」的入職日期

mysql> select entrydate from emp where name = "珊珊";
+------------+
| entrydate  |
+------------+
| 2021-04-10 |
+------------+
1 row in set (0.00 sec)

　　　　　　　　分析2：查詢指定日期之後的入職員工的資訊

mysql> select * from emp where entrydate > "2021-04-10";
+----+--------+------+--------+--------+------------+-----------+---------+
| id | name   | age  | job    | salary | entrydate  | managerid | dept_id |
+----+--------+------+--------+--------+------------+-----------+---------+
|  3 | 問問   |   22 | 開發   |  18000 | 2022-07-20 |         2 |       1 |
|  6 | 娜娜   |   25 | 財務   |  24000 | 2023-07-16 |         1 |       3 |
|  7 | 咔咔   |   25 | 出納   |   8000 | 2021-07-10 |         6 |       3 |
|  8 | 靜靜   |   27 | 人事   |   5000 | 2021-07-11 |         1 |    NULL |
+----+--------+------+--------+--------+------------+-----------+---------+
4 rows in set (0.00 sec)

　　　　　　　　分析3：使用標量查詢

mysql> select * from emp where entrydate > (select entrydate from emp where name = "珊珊");
+----+--------+------+--------+--------+------------+-----------+---------+
| id | name   | age  | job    | salary | entrydate  | managerid | dept_id |
+----+--------+------+--------+--------+------------+-----------+---------+
|  3 | 問問   |   22 | 開發   |  18000 | 2022-07-20 |         2 |       1 |
|  6 | 娜娜   |   25 | 財務   |  24000 | 2023-07-16 |         1 |       3 |
|  7 | 咔咔   |   25 | 出納   |   8000 | 2021-07-10 |         6 |       3 |
|  8 | 靜靜   |   27 | 人事   |   5000 | 2021-07-11 |         1 |    NULL |
+----+--------+------+--------+--------+------------+-----------+---------+
4 rows in set (0.00 sec)

　　8.5、列子查詢

　　　　8.5.1 概念：子查詢返回的結果是一列(可以是多行)，這種子查詢稱為列子查詢

　　　　8.5.2 常用操作符：in, not in, any, some, all

- in：在指定的集合範圍內，多選一
- not in：不在指定的範圍內
- any：子查詢返回列表，有任意一個滿足即可
- some：與any等同，使用some的地方都可以使用any
- all：子查詢返回列表的所有值都必須滿足

　　　　8.5.3 案例

　　　　　　案例1：查詢研發部和財務部的所有員工

　　　　　　　　分析1：查詢研發部和財務部的部門id

mysql> select id from dept where name="研發部" or name="財務部";
+----+
| id |
+----+
|  1 |
|  3 |
+----+
2 rows in set (0.00 sec)

　　　　　　　　分析2：根據部門id，查詢員工資訊

mysql> select * from emp where dept_id in (1,3);
+----+--------+------+--------------+--------+------------+-----------+---------+
| id | name   | age  | job          | salary | entrydate  | managerid | dept_id |
+----+--------+------+--------------+--------+------------+-----------+---------+
|  2 | 李四   |   38 | 專案經理     |  23900 | 2016-08-20 |         1 |       1 |
|  3 | 問問   |   22 | 開發         |  18000 | 2022-07-20 |         2 |       1 |
|  4 | 芳芳   |   22 | 開發         |  21000 | 2019-08-18 |         2 |       1 |
|  5 | 珊珊   |   22 | 開發         |  15000 | 2021-04-10 |         3 |       1 |
|  6 | 娜娜   |   25 | 財務         |  24000 | 2023-07-16 |         1 |       3 |
|  7 | 咔咔   |   25 | 出納         |   8000 | 2021-07-10 |         6 |       3 |
+----+--------+------+--------------+--------+------------+-----------+---------+
6 rows in set (0.00 sec)

　　　　　　　　分析3：列子查詢結果

mysql> select * from emp where dept_id in (select id from dept where name="研發部" or name="財務部");
+----+--------+------+--------------+--------+------------+-----------+---------+
| id | name   | age  | job          | salary | entrydate  | managerid | dept_id |
+----+--------+------+--------------+--------+------------+-----------+---------+
|  2 | 李四   |   38 | 專案經理     |  23900 | 2016-08-20 |         1 |       1 |
|  3 | 問問   |   22 | 開發         |  18000 | 2022-07-20 |         2 |       1 |
|  4 | 芳芳   |   22 | 開發         |  21000 | 2019-08-18 |         2 |       1 |
|  5 | 珊珊   |   22 | 開發         |  15000 | 2021-04-10 |         3 |       1 |
|  6 | 娜娜   |   25 | 財務         |  24000 | 2023-07-16 |         1 |       3 |
|  7 | 咔咔   |   25 | 出納         |   8000 | 2021-07-10 |         6 |       3 |
+----+--------+------+--------------+--------+------------+-----------+---------+
6 rows in set (0.01 sec)

　　　　　　案例2：查詢比研發部工資都高的員工

　　　　　　　　分析1：查詢研發部的部門id

mysql> select id from dept where name="研發部";
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

　　　　　　　　分析2：根據研發部的id，查詢出研發部所有人的工資

mysql> select salary from emp where dept_id = (select id from dept where name="研發部");
+--------+
| salary |
+--------+
|  23900 |
|  18000 |
|  21000 |
|  15000 |
+--------+
4 rows in set (0.00 sec)

　　　　　　　　分析3：比財務部所有人工資高的員工資訊

mysql> select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name="研發部"));
+----+--------+------+-----------+--------+------------+-----------+---------+
| id | name   | age  | job       | salary | entrydate  | managerid | dept_id |
+----+--------+------+-----------+--------+------------+-----------+---------+
|  1 | 張三   |   43 | 董事長    |  48000 | 2017-07-20 |      NULL |       5 |
|  6 | 娜娜   |   25 | 財務      |  24000 | 2023-07-16 |         1 |       3 |
+----+--------+------+-----------+--------+------------+-----------+---------+
2 rows in set (0.00 sec)

　　　　　　　　說明1：比研發部所有人的工資都高，其實就是比研發部最高人的工資還高

　　　　　　案例3：比研發部其中任意一人工資高的員工資訊，其實就是比研發部工資最低的人工資高

　　　　　　　　分析1：查詢研發部所有人的工資

mysql> select salary from emp where dept_id = (select id from dept where name="研發部");
+--------+
| salary |
+--------+
|  23900 |
|  18000 |
|  21000 |
|  15000 |
+--------+
4 rows in set (0.00 sec)

　　　　　　　　分析2：比研發部任意一人工資高的員工資訊

mysql> select * from emp where salary > any(select salary from emp where dept_id = (select id from dept where name="研發部"));
+----+--------+------+--------------+--------+------------+-----------+---------+
| id | name   | age  | job          | salary | entrydate  | managerid | dept_id |
+----+--------+------+--------------+--------+------------+-----------+---------+
|  1 | 張三   |   43 | 董事長       |  48000 | 2017-07-20 |      NULL |       5 |
|  2 | 李四   |   38 | 專案經理     |  23900 | 2016-08-20 |         1 |       1 |
|  3 | 問問   |   22 | 開發         |  18000 | 2022-07-20 |         2 |       1 |
|  4 | 芳芳   |   22 | 開發         |  21000 | 2019-08-18 |         2 |       1 |
|  6 | 娜娜   |   25 | 財務         |  24000 | 2023-07-16 |         1 |       3 |
+----+--------+------+--------------+--------+------------+-----------+---------+
5 rows in set (0.00 sec)

　　　　　　　　說明1：這裡的any可以替換成some

　　8.6、行子查詢

　　　　8.6.1 概念：子查詢返回的結果是一行(可以是多列)，這種查詢稱之為行子查詢

　　　　8.6.2 常用的操作符：=，!=, in, not in

　　　　8.6.3 案例

　　　　　　案例1：查詢和芳芳在同一個崗位，並且還是同一個直屬領帶的員工資訊

　　　　　　　　分析1：先找到芳芳的崗位和直屬領導

mysql> select job, managerid from emp where name = "芳芳";
+--------+-----------+
| job    | managerid |
+--------+-----------+
| 開發   |         2 |
+--------+-----------+
1 row in set (0.00 sec)

　　　　　　　　分析2：在利用上面的查詢資訊當做條件，查詢到需要的結果

mysql> select * from emp where (job,managerid) = (select job,managerid from emp where name="芳芳");
+----+--------+------+--------+--------+------------+-----------+---------+
| id | name   | age  | job    | salary | entrydate  | managerid | dept_id |
+----+--------+------+--------+--------+------------+-----------+---------+
|  3 | 問問   |   22 | 開發   |  18000 | 2022-07-20 |         2 |       1 |
|  4 | 芳芳   |   22 | 開發   |  21000 | 2019-08-18 |         2 |       1 |
+----+--------+------+--------+--------+------------+-----------+---------+
2 rows in set (0.01 sec)

　　　　　　　　說明1：這種子查詢返回結果是一行資料的，就叫做行子查詢

　　8.7、表子查詢

　　　　8.7.1 概念：子查詢返回的結果是多行多列，這種子查詢稱為表子查詢

　　　　8.7.2 常用的操作符：in

　　　　8.7.3 使用場景：經常將查詢到的結果當做一個臨時表使用

　　　　8.7.4 案例

　　　　　　案例1：查詢與芳芳的年齡和部門一樣或者和娜娜年齡和部門一樣的員工

　　　　　　　　分析1：先查詢出來芳芳的和年齡和部門以及娜娜的年齡和部門

mysql> select age,dept_id from emp where name = "芳芳" or name = "娜娜";
+------+---------+
| age  | dept_id |
+------+---------+
|   22 |       1 |
|   25 |       3 |
+------+---------+
2 rows in set (0.00 sec)

　　　　　　　　說明1：這次的查詢結果仍然是一個多行多列的臨時表

　　　　　　　　分析2：將上述查詢結果當做條件使用

mysql> select * from emp where(age,dept_id) in (select age,dept_id from emp where name="芳芳" or name="娜娜");
+----+--------+------+--------+--------+------------+-----------+---------+
| id | name   | age  | job    | salary | entrydate  | managerid | dept_id |
+----+--------+------+--------+--------+------------+-----------+---------+
|  3 | 問問   |   22 | 開發   |  18000 | 2022-07-20 |         2 |       1 |
|  4 | 芳芳   |   22 | 開發   |  21000 | 2019-08-18 |         2 |       1 |
|  5 | 珊珊   |   22 | 開發   |  15000 | 2021-04-10 |         3 |       1 |
|  6 | 娜娜   |   25 | 財務   |  24000 | 2023-07-16 |         1 |       3 |
|  7 | 咔咔   |   25 | 出納   |   8000 | 2021-07-10 |         6 |       3 |
+----+--------+------+--------+--------+------------+-----------+---------+
5 rows in set (0.00 sec)

　　　　　　案例2：查詢入職時間是「2020-01-01」之後的員工資訊及部門資訊

　　　　　　　　分析1：先查詢入職時間是「2020-01-01」之後的員工資訊

mysql> select * from emp where entrydate > "2020-01-01";
+----+--------+------+--------+--------+------------+-----------+---------+
| id | name   | age  | job    | salary | entrydate  | managerid | dept_id |
+----+--------+------+--------+--------+------------+-----------+---------+
|  3 | 問問   |   22 | 開發   |  18000 | 2022-07-20 |         2 |       1 |
|  5 | 珊珊   |   22 | 開發   |  15000 | 2021-04-10 |         3 |       1 |
|  6 | 娜娜   |   25 | 財務   |  24000 | 2023-07-16 |         1 |       3 |
|  7 | 咔咔   |   25 | 出納   |   8000 | 2021-07-10 |         6 |       3 |
|  8 | 靜靜   |   27 | 人事   |   5000 | 2021-07-11 |         1 |    NULL |
+----+--------+------+--------+--------+------------+-----------+---------+
5 rows in set (0.00 sec)

　　　　　　　　分析2：在查詢這部分員工對應的部門資訊

mysql> select e.*, d.* from (select * from emp where entrydate > "2020-01-01") as e left join dept as d on e.dept_id=d.id;
+----+--------+------+--------+--------+------------+-----------+---------+------+-----------+
| id | name   | age  | job    | salary | entrydate  | managerid | dept_id | id   | name      |
+----+--------+------+--------+--------+------------+-----------+---------+------+-----------+
|  3 | 問問   |   22 | 開發   |  18000 | 2022-07-20 |         2 |       1 |    1 | 研發部    |
|  5 | 珊珊   |   22 | 開發   |  15000 | 2021-04-10 |         3 |       1 |    1 | 研發部    |
|  6 | 娜娜   |   25 | 財務   |  24000 | 2023-07-16 |         1 |       3 |    3 | 財務部    |
|  7 | 咔咔   |   25 | 出納   |   8000 | 2021-07-10 |         6 |       3 |    3 | 財務部    |
|  8 | 靜靜   |   27 | 人事   |   5000 | 2021-07-11 |         1 |    NULL | NULL | NULL      |
+----+--------+------+--------+--------+------------+-----------+---------+------+-----------+
5 rows in set (0.00 sec)

　　　　　　　　說明1：將自查詢的結果當做一張臨時表，與其他的表做連線查詢

一、多表關係

一對多或者多對一

多對多

一對一

二、多表查詢

2.1、概述：

2.2、分類：

三、範例資料表結構及資料

3.1、emp：員工表結構及資料

3.2、dept：部門表結構及資料

3.3、score1：成績表1結構及資料

3.4、score2：成績表2結構及資料

四、內連線

4.1、概述：

4.2、內連線查詢語法

4.3、案例

五、外連線

5.1、左外連線

5.2、右外連線

5.3、案例

六、自連線

6.1、自連線查詢語法

6.2、自連線查詢

6.3、案例

七、聯合查詢

7.1、聯合查詢：

7.2、案例

八、子查詢

8.1、概念：

8.2、根據結果分類

8.3、根據位置分類

8.4、標量子查詢

8.5、列子查詢

8.6、行子查詢

8.7、表子查詢

　　2.1、概述：

　　2.2、分類：

　　3.1、emp：員工表結構及資料

　　3.2、dept：部門表結構及資料

　　3.3、score1：成績表1結構及資料

　　3.4、score2：成績表2結構及資料

　　4.1、概述：

　　4.2、內連線查詢語法

　　4.3、案例

　　5.1、左外連線

　　5.2、右外連線

　　5.3、案例

　　6.1、自連線查詢語法

　　6.2、自連線查詢

　　6.3、案例

　　7.1、聯合查詢：

　　7.2、案例

　　8.1、概念：

　　8.2、根據結果分類

　　8.3、根據位置分類

　　8.4、標量子查詢

　　8.5、列子查詢

　　8.6、行子查詢

　　8.7、表子查詢