閱讀目錄---MySQL常見的建表選項及限制：
1、create table選項
1、指定列選項：default、comment
2、指定表選項：engine、auto_increment、comment
2、create table限制
1、not null：非空限制
2、unique：唯一限制
3、primary key：主鍵限制
4、foreign key：外鍵
5、check：檢查---enum、set
一、CREATE TABLE 選項
1、在定義列的時候，指定列選項
1）DEFAULT <literal>：定義列的預設值
當插入一個新行到表中并且沒有給該列明确指派時，如果定義了列的預設值，将自動得到預設值 ；如果沒有，則為null。
mysql> create table people
-> (
-> id int not null primary key,
-> name varchar(20) not null,
-> sex char(1) default 'm'
-> );
mysql> insert into people(id,name) values(1,'張三');
mysql> insert into people values(2,'李四','f');
mysql> select * from people;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | 張三 | m |
| 2 | 李四 | f |
+----+--------+------+ 當然，也可以在INSERT和UPDATE語句中使用DEFAULT關鍵字顯式地給列賦預設值：
mysql> insert into people values(3,'王五',default);
mysql> update people set sex=default where id=2;
mysql> select * from people;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | 張三 | m |
| 2 | 李四 | m |
| 3 | 王五 | m |
+----+--------+------+ 函數default(column)可以得到一個列的預設值：
mysql> select default(sex) from people;
+--------------+
| default(sex) |
+--------------+
| m |
| m |
| m |
+--------------+ 2）comment：用來給列添加注釋，最多255個字元，注釋會儲存到資料字典中。
建立帶有列注釋的表stu_comment
mysql> create table stu_comment
-> (
-> id int not null primary key
-> comment '學号',
-> name varchar(20) not null
-> comment '姓名'
-> ); 從資料字典查詢注釋資訊
mysql> select column_name,column_comment
-> from information_schema.columns
-> where table_name='stu_comment';
+-------------+----------------+
| column_name | column_comment |
+-------------+----------------+
| id | 學号 |
| name | 姓名 |
+-------------+----------------+ 2、在CREATE TABLE語句中的表選項
1）engine：指定表使用的存儲引擎
存儲引擎：決定了資料如何存儲以及如何通路，還有事務如何處理
MySQL允許對每個表使用不同的存儲引擎，如果在create table語句中沒有指定存儲引擎，則使用預設的存儲引擎。
mysql> show engines; #查詢所有支援的存儲引擎
mysql> CREATE TABLE sexes(sex char(1) NOT NULL) ENGINE = INNODB;
注意：存儲引擎是個重點，後面我們詳細講解。
2）auto_increment：決定當向表中插入第一行時，自增列得到的第一個值是多少
3）comment：給表添加注釋
mysql> create table mycomm(num int) comment '測試表';
mysql> select table_name,table_comment
-> from information_schema.tables
-> where table_name='mycomm';
+------------+---------------+
| table_name | table_comment |
+------------+---------------+
| mycomm | 測試表 |
+------------+---------------+ 二、CREATE TABLE 限制
作用：可以為列定義限制（constraint）
限制主要是防止非法資料進入到表中，確定資料的正确性和一緻性（統稱資料完整性）；
限制也可以防止一個表被删除 。
注意：
1）MySQL中限制儲存在information_schema.table_constraints中，可以通過該表查詢限制資訊；
2）進行限制定義的時間：使用create table語句、使用alter table語句。
常用的限制的類型：5種
①not null：非空限制，指定某列不為空
②unique：唯一限制，指定某列和幾列組合的資料不能重複
③primary key：主鍵限制，指定某列的資料不能重複、唯一
④foreign key：外鍵，指定該列記錄屬于主表中的一條記錄，參照另一條資料
⑤check：檢查，指定一個表達式，用于檢驗指定資料
限制定義的文法：
列級别：
CREATE TABLE table_name(column_name data_type
[ [NOT NULL] | [UNIQUE [KEY] | PRIMARY KEY]
|CHECK(expr)],…)
表級别：
CREATE TABLE table_name(
column_name data_type [NOT NULL],
column_name data_type [not null],…,
[CONSTRAINT constraint_name] PRIMARY KEY (col_name,...)
|[CONSTRAINT constraint_name] unique (col_name,...)
|[CONSTRAINT constraint_name] foreign KEY (col_name) REFERENCES tbl_name (index_col_name)
|check(expr) 1）NOT NULL限制隻能在列級别定義，作用在多個列上的限制隻能定義在表級别，例如複合主鍵限制；
2）列級别上不能定義外鍵限制，并且不能給限制起名字，由MySQL自動命名（NOT NULL除外）；
3）表級别上定義的限制可以給限制起名字（CHECK限制除外）
1、、not null非空限制
作用：用于確定目前列的值不為空。
mysql> create table temp_nn(id int not null);
限制直接對DML操作帶來影響
mysql> insert into temp_nn values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into temp_nn values(null);
ERROR 1048 (23000): Column 'id' cannot be null
具有非空限制的列不允許有null值 注意：非空限制隻能出現在表對象的列上。
2、unique唯一限制
1.唯一限制是指定table的列或列組合不能重複，保證資料的唯一性，限制的列不允許有重複值；
2.唯一限制不允許出現重複的值，但是可以為多個null；
3.同一個表可以有多個唯一限制，多個列組合的限制
mysql> create table temp_uk(
-> id int not null unique,
-> name varchar(20) unique);
mysql> insert into temp_uk values(1,'a');
mysql> insert into temp_uk values(2,'a');
ERROR 1062 (23000): Duplicate entry 'a' for key 'name'
mysql> insert into temp_uk values(2,null);
mysql> insert into temp_uk values(3,null);
mysql> select * from temp_uk;
+----+------+
| id | name |
+----+------+
| 2 | NULL |
| 3 | NULL |
| 1 | a |
+----+------+ 可見，唯一性限制的列可以有多個null值，因為null <> null
4.在建立唯一限制時，如果不給唯一限制名稱，就預設和列名相同；
5.唯一限制不僅可以在一個表内建立，而且可以同時多表建立組合唯一限制。
mysql> create table test(
-> id int not null,
-> name varchar(20),
-> password varchar(16),
---使用表級限制文法
-> constraint uk_name_pwd unique(name,password)
-> ); #表示使用者名和密碼組合不能重複
Query OK, 0 rows affected (0.08 sec)
查詢資料字典，檢視唯一鍵限制的資訊
mysql> select * from information_schema.table_constraints
-> where table_name='test'; 3、primary key主鍵限制
primary key = not null + unique
主鍵：用來唯一的标示表中的每一行(類型一般為整型或者字元串)
具有主鍵限制的列不允許有null值，并且不允許有重複值;
每個表最多隻允許一個主鍵(可定義聯合主鍵)，主鍵名總是PRIMARY。
mysql> create table temp_pk(
-> id int primary key);
mysql> insert into temp_pk values(1),(2);
mysql> insert into temp_pk values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> update temp_pk set id=1 where id=2;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into temp_pk values(null);
ERROR 1048 (23000): Column 'id' cannot be null !!給主鍵一個新的名字，但在資料字典中，主鍵名還是顯示primary
聯合主鍵（用幾個列進行唯一辨別一行）
mysql> create table temp_pk(
-> id int,
-> name varchar(20),
-> constraint pk_id_name primary key(id,name)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> desc temp_pk;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into temp_pk values(1,'張三');
Query OK, 1 row affected (0.00 sec)
mysql> insert into temp_pk values(2,'李四');
Query OK, 1 row affected (0.01 sec)
mysql> insert into temp_pk values(1,'王五');
Query OK, 1 row affected (0.00 sec)
mysql> insert into temp_pk values(1,'張三');
ERROR 1062 (23000): Duplicate entry '1-張三' for key 'PRIMARY'
mysql> select * from temp_pk;
+----+--------+
| id | name |
+----+--------+
| 1 | 張三 |
| 1 | 王五 |
| 2 | 李四 |
+----+--------+
3 rows in set (0.00 sec) 4、foreign key外鍵限制
外鍵限制：
參照完整性限制，保證一個或兩個表之間的參照完整性，外鍵是建構于一個表的兩個字段或是兩個表的兩個字段之間的參照關系。
1）具有外鍵限制的列的值不能随便給，必須滿足外鍵所引用的主鍵的取值；
2）一張表中可以定義多個外鍵；
3）外鍵列預設可以給null值。
按照定義，外鍵必須引用一個主鍵或者唯一鍵，引用的主鍵一般在另外一張表中，也可以是本表的主鍵(後者稱為“自引用”)。
父子表：
外鍵所在的表叫做子表、從表
外鍵所引用的主鍵所在的表叫做父表、主表
注意：父子表是相對而言的，表a可以是表b的子表，但同時也可以是表c的父表
示例：建立外鍵限制
//建立父表
mysql> create table dept(
-> deptid int,
-> dname varchar(20),
-> constraint dept_deptid_pk primary key(deptid)
-> );
mysql> insert into dept(deptid,dname) values(10,'市場部');
mysql> insert into dept(deptid,dname) values(20,'銷售部');
//建立子表（表級别建立外鍵限制）
mysql> create table emp(
-> id int,
-> name varchar(20),
-> deptid int,
-> constraint emp_id_pk primary key(id),
-> constraint emp_deptid_fk foreign key(deptid)
-> references dept(deptid)
-> ); 查詢資料字典，檢視外鍵限制的資訊：
上面建立子表的時候給外鍵限制命名emp_deptid_fk；
如果不給外鍵限制命名，那麼預設的名字是表名_ibfk_n, n是整數,從1開始；
此時，emp表中deptid列(外鍵限制)受dept主表限制
mysql> insert into emp(id,name,deptid) values(1,'張三',10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into emp(id,name,deptid) values(2,'李四',10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into emp(id,name,deptid) values(3,'王五',50); #insert主表deptid列沒有的資料
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_deptid_fk` FOREIGN KEY (`deptid`) REFERENCES `dept` (`deptid`))
mysql> update emp set deptid=30 where id=1; #update主表deptid列沒有的資料
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_deptid_fk` FOREIGN KEY (`deptid`) REFERENCES `dept` (`deptid`))
外鍵的update更新操作規則如下删除規則……
mysql> delete from dept where deptid=10; #delete父表中的行(子表中有引用的資料行)
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_deptid_fk` FOREIGN KEY (`deptid`) REFERENCES `dept` (`deptid`))
外鍵的預設删除規則：
當删除父表中的行時，如果子表中有依賴于被删除父行的子行存在，那麼就不允許删除，并抛出異常（預設對外鍵使用on delete restrict 或on delete no action選項） 外鍵引用定義：
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選項，可以改變外鍵的預設删除規則：
①ON DELETE CASCADE：級聯删除。當删除父表中的行時，如果子表中有依賴于被删除父行的子行存在，那麼連同子行一起删除（很危險！！！）
②ON DELETE SET NULL：當删除父表中的行時，如果子表中有依賴于被删除父行的子行存在，那麼不删除，而是将子行的外鍵列設定為null
……
mysql> create table emp(
-> id int,
-> name varchar(20),
-> deptid int,
-> constraint emp_id_pk primary key(id),
-> constraint emp_deptid_fk foreign key(deptid)
-> references dept(deptid)
-> on delete cascade
-> );
…… 5、check限制
MySQL可以使用check限制，但check限制對資料驗證沒有任何作用。
Oracle中可以使用check限制，是有相應的作用的。
mysql> create table test_ck(
-> id int check(id>0)
-> );
mysql> insert into test_ck values(-100);
mysql> select * from test_ck;
+------+
| id |
+------+
| -100 |
+------+ The CHECK clause is parsed but ignored by all storage engines。
定義資料庫列時，可以使用ENUM（enumeration，枚舉）和SET（集合）類型：變通的實作CHECK限制
兩者的差別是：
使用ENUM，隻能選一個值；
使用SET，可以選多個值；
ENUM和SET中的值都必須是字元串類型。
1、enum枚舉類型
在内部存儲ENUM值時，MYSQL給ENUM中的每個值一個順序号碼：第一個值的順序号碼是1，第二個值的順序号碼是2，以此類推。當排序或比較ENUM的時候，使用這些順序号碼進行。
mysql> select * from student order by sex;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 5 | 王五 | NULL |
| 1 | 張三 | M |
| 2 | 李四 | F |
+----+--------+------+ 2、set類型：因為可以列舉多個值，是以在建表列限制中常被使用到。
mysql> create table team(
-> teamno int not null,
-> division set('north','south','east','west')
-> );
mysql> insert into team values(1,'west');
mysql> insert into team values(2,'west,south');
mysql> insert into team values(4,null);
mysql> insert into team values(3,'east,asia');
ERROR 1265 (01000): Data truncated for column 'division' at row 1
mysql> select * from team;
+--------+------------+
| teamno | division |
+--------+------------+
| 1 | west |
| 2 | south,west |
| 4 | NULL |
+--------+------------+
@author：http://www.cnblogs.com/geaozhang/