sql

[sql] 基本功能介紹-view fk pk unique

這篇會稍微簡單介紹一些sql常用的設定,包含Primary key 、Unique key、foreign key、view等的介紹

主鍵 (Primary Key)

主鍵 (Primary Key) 中的每一筆資料都是表格中的唯一值。換言之,它是用來獨一無二地確認一個表格中的每一行資料。主鍵可以是原本資料內的一個欄位,或是一個人造欄位 (與原本資料沒有關係的欄位)。主鍵可以包含一或多個欄位。當主鍵包含多個欄位時,稱為組合鍵 (Composite Key)。通常我們都只會設定一個重要欄位來當pk鍵,例如會員帳號或是身分證號碼之類的,好方便做索引(primary key會自動設定index)

唯一鍵UNIQUE

UNIQUE 用來保證欄位在資料表中的唯一性,約束資料表中的欄位不能有重複的資料。這時候你就會發現他跟primary key很像,而他們的差別在於一張table只能設定一個primary key,且不得為null,而卻可以有多個欄位設定成unique 且他可以有一個null值(unique key會自動設定index)

 index

  • 建立會占用儲存空間,資料增刪修時會異動,所以不是每個欄位都設定index就好那等同於沒設還浪費空間
  • 欄位長度是越短越好,若是固定比變動好
  • 使用「LIKE」參數時,若將萬用字元(%)置於關鍵詞後方,可以使用到index,前方不起作用 (%xxx%或%xxx 沒用, xxx%就可以)
  • 單列索引與多列索引的觀念:
  •   將「A+B+C」設為 index 時,僅搜尋A是不會用到索引的
  • 「A+B+C」和「A」設為 index不同,如搜尋A+B+C,前者直接回覆A+B+C索引值,後者為A用索引,縮小範圍後再以搜尋B+C

視圖view

view  是一個虛擬的表格,這表格的資料是從其他的表格table來的(可以是一個或是多個、也可以從另一個view來)基本表裡面的數據發生變動時,視圖裡面的數據隨之變動。那幹嘛還要view呢?

1,視圖可以讓查詢變得很清楚

如果你要找的數據存放在三張table裡面,查資料的時候,你就要寫個聯合join查詢了。假設我把join完的的資料放到view裡面,這樣查詢起來就更清楚啦,phpmyadmin那邊也會更方便看。

2,保護資料庫的重要數據,給不同的人看不同的數據

你也許需要開放資料庫給別人做查詢,不同的人只能查詢到這個table的部分資料時,就可以用view。創一個view然後給他只有查詢這個view的權限即可,這樣就可以很簡單的分配資料庫的權限了。

FOREIGN KEY
一個表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。例如我有一個「會員資料」的table,我就把他的[username]欄位設定成PRIMARY KEY 然後我會有另一個「訂單資料」的table,這時候我就會把訂單資料的[username]設定foreign key指向會員資料的[username]代表我訂單資料裡面的username跟會員資料的username是有關係的。這樣在做新增修改時就會一起修改或刪除了。

會員資料表:

Id_P LastName FirstName Address City
1 好拆 凱達格蘭大道一百號 天龍國
2 台灣大道一號 高譚市
3 愛河路 花媽市

訂單表:

se OrderNo Id_P
1 77895 3
2 44678 3
3 22456 1
4 24562 1

请注意,訂單表 中的 “Id_P” 列指向會員資料表中的 “Id_P” 列。

會員資料表中的 “Id_P” 列是 會員資料表中的 PRIMARY KEY。

訂單表中的 “Id_P” 列是訂單表中的 FOREIGN KEY。

FOREIGN KEY可以用來預防破壞表之前連接的動作。假設你要刪除會員帳號時,同時他就會警告你不能刪因為你有關聯到訂單資料那邊(你可以設定說看他要不要連動刪除之類的)

FOREIGN KEY 也能防止非法資料新增,因為他必須關聯到另一張表,例如假設我在訂單資料裡面新增了一筆不存在的會員訂單時,他就會失敗,因為根本就沒這會員

而在設定foreign key 時可以設定當我要更新update或是刪除delete時該怎辦,有以下四種選項可以選擇

  1. CASCADE – 會將有所關聯的紀錄行也會進行刪除或修改。
  2. SET NULL – 會將有所關聯的紀錄行設定成 NULL。
  3. NO ACTION – 有存在的關聯紀錄行時,會禁止父資料表的刪除或修改動作。
  4. RESTRICT – 與 NO ACTION 相同。

假設我選擇on delete cascade的話,那我把這筆會員資料砍掉時,他的訂單也就會同時也一起被砍掉了。選no action的話 他就會不准你砍,除非你先把訂單資料都先砍光才能砍這筆會員。

Be the First to comment.

Leave a Comment

你的電子郵件位址並不會被公開。

(若看不到驗證碼,請重新整理網頁。)