數據庫三大范式

范式（NF）：設計關系數據庫時，遵從不同的規范要求，設計出合理的關系型數據庫，這些不同的規范要求被稱為不同的范式，各種范式呈遞次規范，越高的范式數據庫冗余越小。但是有些時候一昧的追求范式減少冗余，反而會降低數據讀寫的效率，這個時候就要反范式，利用空間來換時間。可以把它粗略地理解為一張數據表的表結構所符合的某種設計標準的級別。

1NF

即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只要數據庫是關系型數據庫(mysql/oracle/db2/informix/sysbase/sql server)，就自動的滿足1NF。數據庫表的每一列都是不可分割的原子數據項，而不能是集合，數組，記錄等非原子數據項。如果實體中的某個屬性有多個值時，必須拆分為不同的屬性 。通俗理解即一個字段只存儲一項信息。

以上，就不符合第一范式，因為進貨、銷售還可以再分為，進貨數量、進貨單位、銷售單位、銷售數量等，以下則滿足了第一范式。

2NF

第二范式（2NF）是在第一范式（1NF）的基礎上建立起來的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。滿足1NF后，要求表中的所有列，都必須依賴于主鍵，而不能有任何一列與主鍵沒有關系，也就是說一個表只描述一件事情；

例如：訂單表只描述訂單相關的信息，所以所有字段都必須與訂單id相關；產品表只描述產品相關的信息，所以所有字段都必須與產品id相 關；因此不能在一張表中同時出現訂單信息與產品信息；如下圖所示：

3NF

必須先滿足第二范式（2NF），要求：表中的每一列只與主鍵直接相關而不是間接相關，表中的每一列只能依賴于主鍵。

例如：訂單表中需要有客戶相關信息，在分離出客戶表之后，訂單表中只需要有一個用戶id即可，而不能有其他的客戶信息。因為其他的客戶信息直接關聯于用戶id，而不是直接與訂單id直接相關。

各種約束

約束是用來限定表中數據準確性、完整性、一致性、聯動性的一套規則。在Mysql中，約束保存在information_schema數據庫的table_constraints中，可以通過該表查詢約束信息。如下圖：

NOT NULL

非空約束，是否允許該列的值為NULL，這里有一點很重要，很多字段（除了時間？）默認值如果不指定的話都是NULL，所以除了NULL=NULL，其他值并不等于NULL，比如“”、0等。

修改一個字段為NOT NULL：

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | YES  |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.00 sec)

MariaDB [mydb]> ALTER TABLE user MODIFY logip varchar(20) NOT NULL;
Query OK, 5 rows affected, 5 warnings (0.04 sec)  
Records: 5  Duplicates: 0  Warnings: 5

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | NO   |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.01 sec)

這里還有一個問題，對于默認值為NULL但是又沒有指定插入這個字段：

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | YES  |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
//看username這個字段，默認值為NULL，不允許NULL
MariaDB [mydb]> INSERT INTO user(password) VALUES('test7');
Query OK, 1 row affected, 1 warning (0.00 sec)
//這里看到我們插入成功了。
MariaDB [mydb]> SELECT * FROM user WHERE password='test7';
+----+----------+----------+---------------------+---------------------+-------+
| id | username | password | regtime             | logtime             | logip |
+----+----------+----------+---------------------+---------------------+-------+
| 12 |          | test7    | 2018-02-25 15:25:14 | 0000-00-00 00:00:00 | NULL  |
+----+----------+----------+---------------------+---------------------+-------+
1 row in set (0.00 sec)

可以看到username這一列的值為空字符，而它的默認值為NULL啊， 而logip默認值為NULL，但是允許插入NULL值，所以這里顯示了NULL值。

查了一下~因為NULL為默認值，但是又不允許NULL值，所以，也就是說現在username這個字段沒有值，因為SQL_MODE的原因，只會警告一下并不會直接報錯，當我們指定SQL_MODE為’STRICT_ALL_TABLES’時，此時插入就會報如下錯誤：

MariaDB [mydb]> INSERT INTO user(password) VALUES('test88');
ERROR 1364 (HY000): Field 'username' doesn't have a default value

UNIQUE

unique代表唯一約束：唯一約束是指定table的列或列組合不能重復，保證數據的唯一性，雖然唯一約束不允許出現重復的值，但是可以為多個null，同一個表可以有多個唯一約束，多個列組合的約束。在創建唯一約束的時候，如果不給唯一約束名稱，就默認和列名相同，MySQL會給唯一約束的列上默認創建一個唯一索引。

添加唯一約束：

MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT uq_username UNIQUE(username);  //uq_username為約束名稱，UNIQUE(可多個字段）

//當插入用戶名相同的數據事則會直接報錯

MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test123');
ERROR 1062 (23000): Duplicate entry 'test4' for key 'uq_username'//刪除此約束
MariaDB [mydb]> ALTER TABLE user DROP KEY uq_username;

//添加兩個字段的約束
MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT uq_user UNIQUE(username,password);

//測試添加數據
MariaDB [mydb]> SELECT * FROM user;                                                    
+----+----------+----------+---------------------+---------------------+-------+
| id | username | password | regtime             | logtime             | logip |
+----+----------+----------+---------------------+---------------------+-------+
|  7 | test2    | test3    | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 |       |
|  8 | test3    | test3    | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 |       |
|  9 | test4    | test5    | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 |       |
+----+----------+----------+---------------------+---------------------+-------+
3 rows in set (0.00 sec)

MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test123');
Query OK, 1 row affected (0.01 sec)

//僅當兩個字段的數據都相同時才違反唯一約束
MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test5');
ERROR 1062 (23000): Duplicate entry 'test4-test5' for key 'uq_user'

PRIMARY KEY

主鍵約束相當于唯一約束+非空約束的組合，主鍵約束列不允許重復，也不允許出現空值。如果是多列組合的主鍵約束， 那么這些列都不允許為空值，并且組合的值不允許重復。每個表最多只允許一個主鍵，建立主鍵約束可以在列級別創建，也可以在表級別上創建，MySQL的主鍵名總是PRIMARY， 當創建主鍵約束時，系統默認會在所在的列和列組合上建立對應的唯一索引。

操作如下：

//因為現在的表中已經有主鍵了，先把主鍵刪掉
MariaDB [mydb]> ALTER TABLE user DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

//告訴我們一張表里只允許有一個字段為自動增長，且這個字段必須是主鍵，所以，我們要先取消它的自動增長。

MariaDB [mydb]> ALTER TABLE user MODIFY COLUMN id int(11) NOT NULL;
Query OK, 4 rows affected (0.07 sec)              
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+-------+
| Field    | Type        | Null | Key | Default             | Extra |
+----------+-------------+------+-----+---------------------+-------+
| id       | int(11)     | NO   | PRI | NULL                |       |


//再次刪除主鍵
MariaDB [mydb]> ALTER TABLE user DROP PRIMARY KEY;
Query OK, 4 rows affected (0.03 sec)              
Records: 4  Duplicates: 0  Warnings: 0

//好了，再讓我們把主鍵加上吧~~~   以下兩種方式都可以哦~
MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT PRIMARY KEY(id);
MariaDB [mydb]> ALTER TABLE user MODIFY COLUMN id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT;

FOREIGN KEY

外鍵約束是保證一個或兩個表之間的參照完整性，外鍵是構建于一個表的兩個字段或是兩個表的兩個字段之間的參照關系。 也就是說從表的外鍵值必須在主表中能找到或者為空，當主表的記錄被從表參照時，主表的記錄將不允許刪除，如果要刪除數據，需要先刪除從表中依賴該記錄的數據，然后才可以刪除主表的數據，還有一種就是級聯刪除子表數據。 注意：外鍵約束的參照列，在主表中引用的只能是主鍵或唯一鍵約束的列，假定引用的主表列不是唯一的記錄，那么從表引用的數據就不確定記錄的位置，同一個表可以有多個外鍵約束。

現在，我們創建一個GROUP表吧，用于記錄用戶的分組信息，

CREATE TABLE `usergroup` (
 `id` int(3) NOT NULL AUTO_INCREMENT,
 `name` varchar(10) NOT NULL,
 `comment` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16 |

然后呢~為user表添加一個記錄，記錄用戶屬于那個組

MariaDB [mydb]> ALTER TABLE user ADD COLUMN groupid INT(3);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

//添加一個外鍵

ALTER TABLE user ADD CONSTRAINT fk_groupid FOREIGN KEY (groupid) REFERENCES usergroup(id);

//驗證外鍵約束

MariaDB [mydb]> INSERT INTO user(username,password,groupid) VALUES('test99','test00',1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`user`, CONSTRAINT `fk_groupid` FOREIGN KEY (`groupid`) REFERENCES `usergroup` (`id`))

//可以為空，但是不可以為參照表中沒有的值

MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test99','test00');
Query OK, 1 row affected (0.01 sec)

外鍵定義：

reference_definition:
   REFERENCES tbl_name (index_col_name,...)
     [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
     [ON DELETE reference_option]
     [ON UPDATE reference_option]
reference_option:
   RESTRICT | CASCADE | SET NULL | NO ACTION

其中一下級聯操作需要注意： ON DELETE CASCADE：當刪除父（參照）表中的行時，如果子表中有依賴于被刪除父行的子行存在，那么連同子行一起刪除，不推薦使用。 ON DELETE SET NULL：當刪除父（參照）表中的行時，如果子表中有依賴于被刪除父行的子行存在，那么不刪除，而是將子行的外鍵列設置為NULL

CHECK

CHECK約束就是當向表中插入一行或更新一行數據時進行CHECK約束檢查，CHECK接受一個表達式，如果這個表達式為TRUE則允許插入，如果這個表達式為FALSE則拒絕插入，在MariaDB10.2版本才開始支持CHECK。

常見的CHECK約束有：

CONSTRAINT non_empty_name CHECK (CHAR_LENGTH(name) > 0)
CONSTRAINT consistent_dates CHECK (birth_date IS NULL OR death_date IS NULL OR birth_date

例子：檢查用戶名長度是否大于0

ALTER TABLE user ADD CONSTRAINT non_empty_name CHECK(CHAR_LENGTH(username)>0);
INSERT INTO user(id,username) VALUES(1,'');
/* SQL錯誤（4025）：CONSTRAINT `non_empty_name` failed for `test`.`user` */

這個東西看起來很雞肋的樣子，好像一般都是在業務層進行數據判斷了，而且數據庫嘛~就存數據就好了。