多表操作

第一章 外鍵

在實際開發項目中，一個健壯的數據表一定有很好的參照完整性，為保證數據的完整性，需將兩表建立關係。這時可通過外鍵約束來實現

1.1、介紹

什麼是外鍵約束？

在另一張表中引用另一張表的主鍵約束或唯一約束。

例如：如下操作創建表

create table grade(
	id int primary key,
    name varchar(36)
);

create table student(
	sid int primary key,
    sname varchar(36),
    gid int not null,
    foreign key(gid) references grade(id)
);

從上述創建表可知，學生表中引用班級表中的主鍵，從而兩表有了聯繫。兩表有了主從關係。

主表：被引用的表，如 grade 表

從表：引用表，如 student 表

註意：引用外鍵後，外鍵列只能插入參照列存在的值，且刪除數據時，只能先刪除 student 表中的數據，再刪除 grade 表的數據

1.2、外鍵約束

還可以在表創建後來創建外鍵約束。

語法：

alter table 表名 add constraint 外鍵約束名 foreign key(外鍵欄位) references 外表(主鍵欄位)
[on delete [cascade|set null | no action | restrict]]
[on update [cascade|set null | no action | restrict]]

需要註意的是建立外鍵的表必須是 innoDB 型，不能為臨時表。

定義外鍵約束名時，不能加引號。

[]:可選項，當刪除主表時，從表中的數據也應該刪除，各參數如下：

1.3、刪除外鍵

當有業務需求需要刪除表間的關聯關係時，需要刪除外鍵約束

語法：

alter table 表名 drop foreign key 外鍵約束名;

第二章 關聯表

2.1、關聯關係

• 多對一
  • 是常見的一種關係，在多對一的表關係中，外鍵會建在多的一方，否則會造成數據的冗餘。
• 多對多
  • 會常見中間表，該表會存有 兩個外鍵。
• 一對一
  • 常見於保存臨時的數據表中

註意:在兩個具有關聯表中刪除數據時，一定要先刪除從表數據，再刪除主表數據。

第三章 連接查詢

3.1、交叉連接

也是進行笛卡爾積運算。

語法：

select * from 表1 cross join 表2;

結果是兩表列的乘積。

不常見。

3.2、內連接

又稱簡單連接或自然連接，是常見的一種連接查詢

通過表中共有欄位進行連接運算

語法：

select 查詢欄位 from 表1 [inner] join 表2
on 表1.欄位 = 表2.欄位

還有一種連接為自連接，是內連接特殊的存在，是在同一張表上進行的連接運算。

語法：

select 查詢欄位 from 表1 join 表2
on 表1.欄位 = 表2.欄位

例如：

select * from emp e1 join emp e2
on e1.欄位 = e2.欄位

3.3、外連接

查詢兩張表中另一張表的數據加上本身的數據。也就是說查詢結果中以一張表為主表，在另一張表為從表中進行匹配，

語法：

select 查詢欄位 from 表1 left|right [outer] join 表2
on 表1.欄位 = 表2.欄位 
where
.....

1、左外連接

語法：

select 查詢欄位 from 表1 left join 表2
on 表1.欄位 = 表2.欄位 
where
.....

左邊的表為主表，右邊為從表，以主表為主，從表為輔

如果主表在從表中沒有匹配，則以null代替

2、右外連接

語法：

select 查詢欄位 from 表1 right join 表2
on 表1.欄位 = 表2.欄位 
where
.....

右邊的表為主表，左邊為從表，以主表為主，從表為輔

如果主表在從表中沒有匹配，則以null代替

3.4、複合連接

在連接查詢中，添加過濾條件來限制查詢結果，使查詢結果更加精確。例如 在鏈家查詢中添加 where、group by、order by等操作。

第四章 子查詢

4.1、帶 in 查詢

內層查詢語句返回集合，集合中的元素供外層查詢進行比較

例如：查詢存在年齡為20 歲的員工部門

select * 
from dept 
where did in (
	select did
   	from emp where age=20
);

其中 in 前還可以添加 not ，表示不再其中，與 in 相反

4.2、帶 exists 查詢

true 、false

當為true 時，外層查詢才會執行

例如：查詢 emp 表中是否存在年齡大於 21 歲的員工

select *
from dept
where exists(
	select did
    from emp where age > 21
);

exists 比 in 關鍵字的運行效率高，在實際開發中，尤其是數據量大時，推薦使用

4.3、帶 any 查詢

表示滿足其一即可

例如：

select *
from dept
where did > any(
	select did from emp
);

4.4、帶 all 查詢

全都滿足

例如：

select *
from dept
where did > all(
	select did from emp
);

4.5、比較運算符查詢

'<','>','=','<=','>=','!='