Mysql基礎8-多表查詢_ZenDei技術網路在線

一、多表關係一對多或者多對一案例：部門與員工的關係關係：一個部門對應多個員工，一個員工對應一個部門（不考慮跨部門的特殊情況）實現：在多的一方建立外鍵，指向一的一方的主鍵，這裡員工表是多的的一方，部門表是一的一方多對多案例：學生與課程的關係關係：一個學生可以選修多門課程，一門課程也可以供 ...

一、多表關係

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

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

一對一
- 案例：用戶與用戶詳情的關係
- 關係：一對一關係，多用於單表拆分，將一張表的基礎欄位放在一張表，其他詳情欄位放在另外一張表中，藝體生操作效率
- 實現：在任意一方加入外鍵，關聯另外一方的主鍵，並且設置為唯一的(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   |

Mysql基礎8-多表查詢

一、多表關係

一對多或者多對一

多對多

一對一

二、多表查詢

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、案例

　　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、案例