MySQL學習3--聯表查詢

1、常規聯表查詢

創建兩張資料庫表如下：

mysql> select * from dept;
+----+-----------+
| id | deptName  |
+----+-----------+
|  3 | 教學部    |
+----+-----------+
1 row in set (0.00 sec)

mysql> select * from employee;
+----+---------+--------+---------------------+
| id | empName | deptId | regTime             |
+----+---------+--------+---------------------+
|  1 | 老邢    |      3 | 2023-06-29 16:28:09 |
|  4 | 老萬    |      3 | 2023-06-29 16:28:47 |
|  5 | 小磊    |      3 | 2023-06-29 16:28:48 |
+----+---------+--------+---------------------+

聯表查詢  老邢屬於哪個部門？

mysql> select employee.id, employee.empName, dept.deptName  #查什麼
    ->from  employee,dept #從哪查   多張表使用逗號隔開
    ->where  employee.deptId = dept.id and employee.empName = "老邢";#約束的條件一定要帶

+----+---------+-----------+
| id | empName | deptName  |
+----+---------+-----------+
|  1 | 老邢    | 教學部    |
+----+---------+-----------+

聯表查詢的時候對欄位可以起別名

mysql> select e.id, e.empName, d.deptName
    -> from employee e, dept d
    -> where e.deptId = d.id;
+----+---------+-----------+
| id | empName | deptName  |
+----+---------+-----------+
|  1 | 老邢    | 教學部    |
|  4 | 老萬    | 教學部    |
|  5 | 小磊    | 教學部    |
+----+---------+-----------+

mysql> select e.id as "編號", e.empName as "名字", d.deptName as "部門"
    -> from employee e, dept d
    -> where e.deptId = d.id;
+--------+--------+-----------+
| 編號   | 名字   | 部門      |
+--------+--------+-----------+
|      1 | 老邢   | 教學部    |
|      4 | 老萬   | 教學部    |
|      5 | 小磊   | 教學部    |
+--------+--------+-----------+

2、內連接和外連接

join 用於根據兩個或多個表中的列之間的關係，從這些表中查詢數據。

2.1、內連接　　inner join

語法格式：

select 欄位
from 表1
inner join 表2
on 約束關係

例1：#查教學部有多少人

mysql> select d.deptName, e.empName
    -> from dept d
    -> inner join employee e
    -> on d.id = e.deptId;
+-----------+---------+
| deptName  | empName |
+-----------+---------+
| 教學部    | 老邢    |
| 教學部    | 老萬    |
| 教學部    | 小磊    |
+-----------+---------+

例2：查看老邢屬於哪個部門

mysql> select e.empName, d.deptName
    -> from employee e
    -> inner join dept d
    -> on e.deptId = d.id
    -> where e.empName = "老邢";
+---------+-----------+
| empName | deptName  |
+---------+-----------+
| 老邢    | 教學部    |
+---------+-----------+

2.2、外連接

a、左外連接

left join關鍵字會從左表 (table_name1) 那裡返回所有的行，即使在右表 (table_name2) 中沒有匹配的行。也要展示。

語法格式：

select 欄位 from 左表1 left outer join 右表2 on 約束條件

左邊表全部顯示，右邊表去匹左邊表，如果沒有匹配上就顯示null　　例：

mysql> select d.deptName, e.empName
    -> from dept d
    -> left outer join employee e
    -> on d.id = e.deptId;
+-----------+---------+
| deptName  | empName |
+-----------+---------+
| 教學部    | 老邢    |
| 教學部    | 老萬    |
| 教學部    | 小磊    |
| 技術部    | NULL    |
+-----------+---------+

b、右外連接

語法格式：

select 欄位  from 左表1  right outer join 右表2 on  約束條件

以右邊表為主右邊表要全部顯示，左邊如果沒有匹配到就顯示為null　　例：

mysql> select *
    -> from employee e
    -> right outer join dept d
    -> on e.deptId = d.id;
+------+---------+--------+---------------------+----+-----------+
| id   | empName | deptId | regTime             | id | deptName  |
+------+---------+--------+---------------------+----+-----------+
|    1 | 老邢    |      1 | 2022-04-25 10:08:04 |  1 | 教學部    |
|    2 | 雪珂    |      2 | 2022-04-25 10:08:17 |  2 | 行政部    |
|    3 | 凱旗    |      3 | 2022-04-25 10:08:30 |  3 | 技術部    |
|    4 | 小磊    |      1 | 2022-04-25 10:08:43 |  1 | 教學部    |
|    5 | 帥棟    |      1 | 2022-04-25 10:08:56 |  1 | 教學部    |
| NULL | NULL    |   NULL | NULL                |  4 | 後勤部    |
+------+---------+--------+---------------------+----+-----------+

2.3、一對一、一對多查詢

例：現有學生表和老師表

一個老師對應著多個學生（一對多）     一個學生對應著咱們的一個老師（一對一）

a、一對一查詢（使用內連接）

查詢邱博的老師？   一對一

select ts.s_name, tt.t_name
from t_student ts
inner joint_teacher tt
on ts.teacher_id = tt.t_id
where ts.s_name = "邱博"

b、一對多查詢（使用內連接）

查詢老邢所帶的學生

select tt.t_name, ts.s_name
from t_teacher tt
inner join t_student ts
on tt.t_id = ts.teacher_id
where tt.t_name = "老邢"

c、一對多查詢（不使用內連接）

select tt.t_name, ts.s_name
from t_student ts, t_teacher tt
where ts.teacher_id = tt.t_id and tt.t_name = '老邢'

2.4、多對多查詢

例：

學生和課程的關係
學生選課，
一個學生對應著多個課程
一個課程要被多個學生選擇
一個課程對應著多個學生

創建學生表、課程表、學生_課程關聯表，三表聯查 兩個inner join

#看所有學生選擇了哪些的課程
select ts.s_name, tc.c_name
from t_stu ts
inner join stu_course sc
on ts.s_id = sc.stu_id
inner join t_course tc
on sc.course_id = tc.c_id
#查看狗蛋選擇了哪些課程
select ts.s_name, tc.c_name
from t_stu ts
inner join stu_course sc
on ts.s_id = sc.stu_id
inner join t_course tc
on sc.course_id = tc.c_id
where ts.s_name = "狗蛋"

#Java被哪些學生選擇了
先寫課程表
select *
from t_course tc
inner join stu_course sc
on tc.c_id = sc.course_id
inner join t_stu ts
on sc.stu_id = ts.s_id
where tc.c_name = "Java"