1 欄位屬性

• 主鍵、唯一鍵和自增長。

1.1 主鍵

• 主鍵：primary key，一張表中只能有一個欄位可以使用對應的鍵，用來唯一的約束該欄位裡面的數據，不能重覆。
• 一張表只能有最多一個主鍵。

1.1.1 增加主鍵

• 在SQL操作中歐有多種方式可以給表增加主鍵，大體分為三種：

• 方案1：在創建表的時候，直接在欄位之後，跟primary key關鍵字(主鍵本身不能為空)。

-- 增加主鍵
create table my_pri(
   id int primary key,
   name varchar(20) not null comment '姓名'
)charset utf8;

• • 優點：非常直接；缺點：只能使用一個欄位作為主鍵。　　

• 方案2：在創建表的時候，在所有的欄位之後，使用primary key(主鍵欄位列表)來創建主鍵，如果有多個欄位作為主鍵，可以是複合主鍵。

create table my_pri2(
   number char(10) comment '學號',
   course char(10) comment '課程代碼：3901+0000',
   score tinyint unsigned default 60 comment '成績',
   -- 增加主鍵限制：學號和課程代碼應該是唯一的
   primary key (number,course)
)charset utf8;

• 方案3：當表已經創建好之後，額外追加主鍵：可以通過修改表欄位屬性，也可以直接追加。

alter table 表名 add primary key (欄位列表);

• 前提：表中欄位對應的數據本身是獨立的（不重覆）。

1.1.2 主鍵約束

• 主鍵對應的欄位中的數據不允許重覆，一旦重覆，數據操作失敗（增和該）。

1.1.3 主鍵更新 & 刪除主鍵

• 沒有辦法更新主鍵：主鍵必須先刪除，才能增加。

drop table 表名 drop primary key;

1.1.4 主鍵分類

• 在實際創建表的過程中，很少使用真實業務數據作為主鍵欄位（業務主鍵，如學號、課程號）。
• 大部分的時候，是使用邏輯性的欄位（欄位沒有業務含義，值是什麼都沒有關係），將這種欄位主鍵稱為邏輯主鍵。

1.2 自動增長

• 自增長：當對應的欄位，不給值，或者給預設值，或者給null的時候，會自動的被系統觸發，系統會從當前欄位中的已有的最大值+1操作，得到一個新的不同的欄位。

• 通常自動增長和主鍵搭配。

1.2.1 新增自增長

• 自增長特點：auto_increment
  • 任何一個欄位要做自增長必須前提是本身是一個索引（key一欄有值）。

• • 自增長必須是數字。　　

• • 一張表最多只能有一個自增長。　　

1.2.2 自增長使用

• 當自增長被給定的值為null或者預設值的時候，會觸發自動增長。

1.2.3 修改自增長

• 自增長如果是涉及到欄位改變：必須先刪除自增長，後增加（一張薄只能有一個自增長）。

• 修改當前自增長已經存在的值：修改只能比當前已有的自增長的最大值大，不能小（小不生效）。

-- 修改表選項的值
alter table 表名 auto_increment = 值;

• 思考：為什麼自增長是從1開始？為什麼每次都是自增1呢？
  • 所有系統的變現（如字元集、校對集）都是系統內部的變數進行控制的。
  • 查看自增長對應的變數：show variables like 'auto_increment%';　　

• 可以修改變數實現不同的效果，但是修改是針對整個資料庫的修改，而不是單張表，不建議修改。

-- 不建議修改
set auto_increment_increment = 5;

1.2.4 刪除自增長

• 自增長是欄位的一個屬性：可以通過modify來進行修改（保證欄位沒有auto_increment即可）

alter table 表名 modify 欄位 類型;

1.3 唯一鍵

• 一張表往往有很多欄位需要具有唯一性，數據不能重覆；但是一張表中只能有一個主鍵，所以唯一鍵就可以解決表中有多個欄位需要唯一性的約束。

• 唯一鍵的本質和主鍵差不多，唯一鍵預設的允許自動為空，而且可以多個為空。

1.3.1 增加唯一鍵

• 基本上和主鍵差不多：三種方案。

• 方案一：在創建表的時候，欄位之後直接跟unique/unque key。

• 方案二：在所有的欄位之後增加unique key（欄位列表）；--複合唯一鍵

• 方案三：在創建表之後增加唯一鍵。

1.3.2 唯一鍵約束

• 唯一鍵與主鍵本質相同，唯一的區別就是唯一鍵預設允許為控控，而且是多個為空。
• 如果唯一鍵也不允許為空，那麼與主鍵的約束作用是一致的。

1.3.3 更新唯一鍵&刪除唯一鍵

• 更新唯一鍵：先刪除唯一鍵，再增加唯一鍵。

• 刪除唯一鍵，預設使用欄位名作為索引名字

alter  table 表名 drop index 索引名字;

2 索引

• 幾乎所有的索引都是建立在欄位之上。

• 系統根據某種演算法，將已有的數據（未來可能新增的數據），單獨建立一個文件：文件能夠實現快速的匹配數據，並且能夠快速的找到對應表中的記錄。

• 索引的意義：
  • 提升查詢數據的效率。
  • 約束數據的有效性（唯一性等）。　　

• 增加索引的前提條件：索引本身會產生索引文件（有時候可能比數據文件還打），會非常消耗磁碟空間。

• 如果某個欄位需要作為查詢條件經常使用，那麼可以使用索引。
• 如果某個欄位需要進行數據的有效性約束，也可以使用索引（主鍵、唯一鍵）。

• MySQL中提供了多種索引。
  • 主鍵索引 primary key
  • 唯一索引 unique key
  • 全文索引 fulltext index
  • 普通索引 index

• 全文索引：針對文章內部的關鍵字進行索引。
• 全文索引最大的問題在於如何確定關鍵字。

3 關係

• 將實體與實體的關係，反應到最終資料庫表的設計上來。將關係分成三種：一對一，一對多和多對多。

3.1 一對一

• 一對一：一張表的一條記錄一定只能和另一張表的一條記錄進行對應；反之亦然。

• 學生表：姓名、性別、年齡、身高、體重、婚姻、籍貫、家庭住址、緊急聯繫人。

• 表設計成以上這種形式，是符合要求的。其中姓名、性別、年齡、身高、體重是常用數據，但是婚姻、籍貫、住址和緊急聯繫人屬於不常用數據。如果每次查詢都是查詢所有數據，不常用的數據就會影響效率，實際又不用。

• 解決方案：將常用的和不常用的信息分離存儲，分成兩張表。
• 學生常用信息表

• 學生不常用信息表

• 但是如果我有時候又需要使用不常用信息怎麼辦？
  • 為了保證不常用信息和常用信息一定能夠對應上，我們唯有找到一個具有唯一性的欄位來共同連接兩張表。--主鍵欄位　　
• 所以，學習不常用信息表修改如下　　

• 綜上所述，一個常用表中的一條記錄，永遠只能在一張不常用表中匹配一條記錄；反過來，一個不常用表中的一條記錄在常用表中也只能匹配一條記錄。

3.2 一對多

• 一對多：一張表中的一條記錄可以對應另外一張表中的多條記錄，反過來，另一張表的一條記錄只能對應第一張表的一條記錄，這種關係就是一對多或多對一。

• 學生和班級的關係：
• 學生表

• 班級表

• 我們知道，一個學生只能屬於一個班級，而一個班級卻有多個學生，一對多。

• 但是以上設計：解決了實體的設計表問題，但是沒有解決關係問題：學生找不到班級，班級沒有學生。

• 解決方案：在某一張表中增加一個欄位，能夠找到另一張表中的記錄。如何做到呢？在學生表中增加一個欄位指向班級表，因為學生表的記錄只能匹配到一個班級的記錄。
• 學生表

3.3 多對多

• 多對多：一張表（A）的一條記錄能夠對應另外一張表（B）的多條記錄；同時B表中的一條記錄也能對應A表中的多條記錄。

• 老師教學：；老師和學生
• 老師表

• 學生表

• 以上設計方案：實現了實體的設計，但是沒有維護實體的關係。
• 一個老師教過多個學生，一個學生也被多個老師教多。

• 解決方案：不管在那張表中增加欄位，都會出現問題：該欄位要保存多個數據，而且是與其它表有關係的欄位，不符合表的設計規範，增加一張新表，專門維護兩張表之間的關係。
• 中間關係表：老師與學生的關係

• 增加了中間表之後：中間表與老師表形成了一對多的關係，而且中間表是多表，維護了能夠唯一找到一表的關係。

4 範式

• 範式：是離散數學中的知識，是為瞭解決一種數據的存儲與優化的問題（保存數據的存儲之後，凡是能夠通過關係尋找出來的數據，堅決不再重覆存儲，起終極目標是為了減少數據冗餘）。

• 範式：是一種分層結構的規範，分為6層：每一層都比上一層更加嚴格。

• 六層範式：1NF、2NF、3NF、4NF、5NF和6NF，其中1NF要求最低，6NF要求最高。

• MySQL屬於關係型資料庫：有空間浪費，而範式致力於節省存儲空間。所以，在設計資料庫的時候，會利用範式來指導設計。但是資料庫不單是解決空間問題，還要保證效率；而範式只為解決空間問題，所以資料庫的設計不可能完全按照範式的要求實現，所以一般情況下，只有前三種範式需要滿足。

• 範式在資料庫的設計當中是有指導意義，但是不是強制規範。

4.1 第一範式 1NF

• 第一範式：在設計表存儲數據的時候，如果表中設計的欄位存儲的數據，在取出來使用之前還需要額外的處理（拆分），那麼就說表的設計不滿足第一範式。
• 第一範式：屬性不可再分，欄位保證原子性。

• 講師代課表

• 上表的設計不存在問題，但是如果需求是將數據查出來之後，要求一個老師從什麼時候開始上課，到什麼時候結束課程，此時需要將代課時間進行拆分，不符合第一範式，因為數據不具有原子性，可以再拆分。

• 解決方案：：將代課時間拆成兩個欄位就可以了。

4.2 第二範式 2NF

• 第二範式：在數據表設計的過程中，如果有複合主鍵，且表中有欄位並不是由整個主鍵來確定，而是依賴主鍵的某個欄位（主鍵的部分），存在欄位依賴主鍵部分的問題，稱之為部分依賴。第二範式就是要解決表設計不允許出現部分依賴。

• 在上面的表中：因為講師沒有辦法作為獨立主鍵，需要結合班級才能作為主鍵（複合主鍵：一個老師在一個班永遠只帶一個階段的課）。代課時間、開始時間和結束時間欄位都與當前的代課主鍵（講師和班級）：但是性別並不依賴班級，教室不依賴講師，性別隻依賴講師，教室只依賴班級，出現了性別和教室依賴主鍵的一部分：即部分依賴。

• 解決方案：取消複合主鍵，使用邏輯主鍵。

4.3 第三範式 3NF

• 要滿足第三範式，必須滿足第二範式。
• 第三範式：理論上講，一張表中的所有欄位都應該直接依賴主鍵（邏輯主鍵除外），如果表設計中存在一個欄位，並不直接依賴主鍵，而是通過某個非主鍵依賴，最終實現依賴主鍵，把這種不是直接依賴主鍵，而是依賴非初見欄位的依賴關係稱之為傳遞依賴。第三範式就是解決傳遞依賴的問題。

• 講師代課表

• 以上設計方案中，性別依賴講師存在，講師依賴主鍵；教室依賴班級，班級依賴主鍵；性別和教室都存在傳遞依賴。

• 解決方案：將存在傳遞依賴的欄位，一級依賴的欄位本身單獨取出，形成一個單獨的表，然後在需要對應的信息的時候，使用對應的實體表的主鍵加起來。

5 逆規範化

• 有的時候，在設計表的時候，如果一張表中有幾個欄位是需要從另外的表中去獲取信息。理論上講，的確可以獲取到想要的數據，但是就是效率低一點。所以我們會刻意在某些表中，不去保存另外表的主鍵（邏輯主鍵），而是直接保存想要的數據信息，這樣一來，在查詢數據的時候，一張表可以直接提供數據，而不需要多表查詢（效率低），但是會導致數據冗餘增加。