SQL關系模型的知識梳理總結
關系模型
關系數據庫是建立在關系模型上的。而關系模型本質上就是若幹個存儲數據的二維表,可以把它們看作很多Excel表。
表的每一行稱為記錄(Record),記錄是一個邏輯意義上的數據。
表的每一列稱為字段(Column),同一個表的每一行記錄都擁有相同的若幹字段。
字段定義瞭數據類型(整型、浮點型、字符串、日期等),以及是否允許為NULL
。註意NULL
表示字段數據不存在。一個整型字段如果為NULL
不表示它的值為0,同樣的,一個字符串型字段為NULL
也不表示它的值為空串''
。
通常情況下,字段應該避免允許為NULL。不允許為NULL可以簡化查詢條件,加快查詢速度,也利於應用程序讀取數據後無需判斷是否為NULL。
和Excel表有所不同的是,關系數據庫的表和表之間需要建立“一對多”,“多對一”和“一對一”的關系,這樣才能夠按照應用程序的邏輯來組織和存儲數據。
在關系數據庫中,關系是通過主鍵和外鍵來維護的。
主鍵
例如,假設我們把name
字段作為主鍵,那麼通過名字小明或小紅就能唯一確定一條記錄。但是,這麼設定,就沒法存儲同名的同學瞭,因為插入相同主鍵的兩條記錄是不被允許的。
對主鍵的要求,最關鍵的一點是:記錄一旦插入到表中,主鍵最好不要再修改,因為主鍵是用來唯一定位記錄的,修改瞭主鍵,會造成一系列的影響。
由於主鍵的作用十分重要,如何選取主鍵會對業務開發產生重要影響。如果我們以學生的身份證號作為主鍵,似乎能唯一定位記錄。然而,身份證號也是一種業務場景,如果身份證號升位瞭,或者需要變更,作為主鍵,不得不修改的時候,就會對業務產生嚴重影響。
所以,選取主鍵的一個基本原則是:不使用任何業務相關的字段作為主鍵。
因此,身份證號、手機號、郵箱地址這些看上去可以唯一的字段,均不可用作主鍵。
作為主鍵最好是完全業務無關的字段,我們一般把這個字段命名為id
。
外鍵
一對多
當我們用主鍵唯一標識記錄時,我們就可以在students
表中確定任意一個學生的記錄:
id | name | other columns… |
---|---|---|
1 | 小明 | … |
2 | 小紅 | … |
我們還可以在classes
表中確定任意一個班級記錄:
id | name | other columns… |
---|---|---|
1 | 一班 | … |
2 | 二班 | … |
但是我們如何確定students
表的一條記錄,例如,id=1
的小明,屬於哪個班級呢?
由於一個班級可以有多個學生,在關系模型中,這兩個表的關系可以稱為“一對多”,即一個classes
的記錄可以對應多個students
表的記錄。
為瞭表達這種一對多的關系,我們需要在students
表中加入一列class_id
,讓它的值與classes
表的某條記錄相對應:
id | class_id | name | other columns… |
---|---|---|---|
1 | 1 | 小明 | … |
2 | 1 | 小紅 | … |
5 | 2 | 小白 | … |
這樣,我們就可以根據class_id
這個列直接定位出一個students
表的記錄應該對應到classes
的哪條記錄。
例如:
小明的class_id
是1
,因此,對應的classes
表的記錄是id=1
的一班;小紅的class_id
是1
,因此,對應的classes
表的記錄是id=1
的一班;小白的class_id
是2
,因此,對應的classes
表的記錄是id=2
的二班。
在students
表中,通過class_id
的字段,可以把數據與另一張表關聯起來,這種列稱為外鍵。
外鍵並不是通過列名實現的,而是通過定義外鍵約束實現的:
ALTER TABLE students ADD CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES classes(id)
其中,外鍵約束的名稱fk_class_id
可以任意,FOREIGN KEY (class_id)
指定瞭class_id
作為外鍵,REFERENCES classes (id)
指定瞭這個外鍵將關聯到classes
表的id
列(即classes
表的主鍵)。
通過定義外鍵約束,關系數據庫可以保證無法插入無效的數據。**即如果classes
表不存在id=99
的記錄,students
表就無法插入class_id=99
的記錄。
由於外鍵約束會降低數據庫的性能,大部分互聯網應用程序為瞭追求速度,並不設置外鍵約束,而是僅靠應用程序自身來保證邏輯的正確性。這種情況下,class_id
僅僅是一個普通的列,隻是它起到瞭外鍵的作用而已。
要刪除一個外鍵約束,也是通過ALTER TABLE
實現的:
ALTER TABLE students DROP FOREIGN KEY fk_class_id
註意:刪除外鍵約束並沒有刪除外鍵這一列。刪除列是通過DROP COLUMN …實現的。
多對多
通過一個表的外鍵關聯到另一個表,我們可以定義出一對多關系。有些時候,還需要定義“多對多”關系。例如,一個老師可以對應多個班級,一個班級也可以對應多個老師,因此,班級表和老師表存在多對多關系。
多對多關系實際上是通過兩個一對多關系實現的,即通過一個中間表,關聯兩個一對多關系,就形成瞭多對多關系
一對一
一對一關系是指,一個表的記錄對應到另一個表的唯一一個記錄。
細心的話會發現,既然是一對一關系,那為啥不給students
表增加一個mobile
列,這樣就能合二為一瞭?
如果業務允許,完全可以把兩個表合為一個表。但是,有些時候,如果某個學生沒有手機號,那麼,contacts
表就不存在對應的記錄。實際上,一對一關系準確地說,是contacts
表一對一對應students
表。
還有一些應用會把一個大表拆成兩個一對一的表,目的是把經常讀取和不經常讀取的字段分開,以獲得更高的性能。例如,把一個大的用戶表分拆為用戶基本信息表user_info
和用戶詳細信息表user_profiles
,大部分時候,隻需要查詢user_info
表,並不需要查詢user_profiles
表,這樣就提高瞭查詢速度。
總結:關系數據庫通過外鍵可以實現一對多、多對多和一對一的關系。外鍵既可以通過數據庫來約束,也可以不設置約束,僅依靠應用程序的邏輯來保證。
索引
索引的概念與用法
在關系數據庫中,如果有上萬甚至上億條記錄,在查找記錄的時候,想要獲得非常快的速度,就需要使用索引。
索引是關系數據庫中對某一列或多個列的值進行預排序的數據結構。**通過使用索引,可以讓數據庫系統不必掃描整個表,而是直接定位到符合條件的記錄,這樣就大大加快瞭查詢速度。
例如,對於students
表:
id | class_id | name | gender | score |
---|---|---|---|---|
1 | 1 | 小明 | M | 90 |
2 | 1 | 小紅 | F | 95 |
3 | 1 | 小軍 | M | 88 |
如果要經常根據score
列進行查詢,就可以對score
列創建索引:
ALTER TABLE students ADD INDEX idx_score(score);
使用ADD INDEX idx_score (score)就創建瞭一個名稱為idx_score,使用列score的索引。
索引名稱是任意的,索引如果有多列,可以在括號裡依次寫上。
ALTER TABLE students ADD INDEX idx_name_score (name, score);
索引的效率取決於索引列的值是否散列,即該列的值如果越互不相同,那麼索引效率越高。**反過來,如果記錄的列存在大量相同的值,例如gender
列,大約一半的記錄值是M
,另一半是F
,因此,對該列創建索引就沒有意義。
可以對一張表創建多個索引。索引的優點是提高瞭查詢效率,缺點是在插入、更新和刪除記錄時,需要同時修改索引,因此,索引越多,插入、更新和刪除記錄的速度就越慢。
對於主鍵,關系數據庫會自動對其創建主鍵索引。使用主鍵索引的效率是最高的,因為主鍵會保證絕對唯一。
唯一索引
在設計關系數據表的時候,看上去唯一的列,例如身份證號、郵箱地址等,因為他們具有業務含義,因此不宜作為主鍵。
但是,這些列根據業務要求,又具有唯一性約束:即不能出現兩條記錄存儲瞭同一個身份證號。這個時候,就可以給該列添加一個唯一索引。例如,我們假設students
表的name
不能重復:
ALTER TABLE students ADD UNIQUE INDEX uni_name(name);
通過UNIQUE
關鍵字我們就添加瞭一個唯一索引。
也可以隻對某一列添加一個唯一約束而不創建唯一索引:
ALTER TABLE students ADD CONSTRAINT uni_name UNIQUE (name);
這種情況下,name
列沒有索引,但仍然具有唯一性保證。
無論是否創建索引,對於用戶和應用程序來說,使用關系數據庫不會有任何區別。
這裡的意思是說,當我們在數據庫中查詢時,如果有相應的索引可用,數據庫系統就會自動使用索引來提高查詢效率,如果沒有索引,查詢也能正常執行,隻是速度會變慢。
因此,索引可以在使用數據庫的過程中逐步優化。
Tips
1.通過對數據庫表創建索引,可以提高查詢速度。但索引越多,插入和更新的速度越慢。
2.索引加得不好,查詢不會變快,甚至會變慢。
3.通過創建唯一索引,可以保證某一列的值具有唯一性。
4.數據庫索引對於用戶和應用程序來說都是透明的。
以上就是SQL關系模型的知識梳理總結的詳細內容,更多關於SQL關系模型的資料請關註WalkonNet其它相關文章!
推薦閱讀:
- MySQL六種約束的示例詳解(全網最全)
- SQL Server 數據庫基礎編程詳解
- MySQL約束(創建表時的各種條件說明)
- MySQL數據庫之數據表操作DDL數據定義語言
- MySQL數據庫如何給表設置約束詳解