MySQL常見建表選項(xiàng)及約束

一、CREATE TABLE 選項(xiàng)

1、在定義列的時候，指定列選項(xiàng)

1）DEFAULT <literal>：定義列的默認(rèn)值

　　當(dāng)插入一個新行到表中并且沒有給該列明確賦值時，如果定義了列的默認(rèn)值，將自動得到默認(rèn)值 ；如果沒有，則為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    |
+----+--------+------+

當(dāng)然，也可以在INSERT和UPDATE語句中使用DEFAULT關(guān)鍵字顯式地給列賦默認(rèn)值：

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    |
+----+--------+------+

 函數(shù)default(column)可以得到一個列的默認(rèn)值：

mysql> select default(sex) from people;+--------------+
| default(sex) |
+--------------+
| m            |
| m            |
| m            |
+--------------+

2）comment：用來給列添加注釋，最多255個字符，注釋會保存到數(shù)據(jù)字典中。

　　創(chuàng)建帶有列注釋的表stu_comment

mysql> create table stu_comment    -> (    -> id int not null primary key    ->   comment '學(xué)號',    -> name varchar(20) not null
    ->   comment '姓名'
    -> );

 　從數(shù)據(jù)字典查詢注釋信息

mysql> select column_name,column_comment    -> from information_schema.columns    -> where table_name='stu_comment';+-------------+----------------+
| column_name | column_comment |
+-------------+----------------+
| id          | 學(xué)號            |
| name        | 姓名            |
+-------------+----------------+

2、在CREATE TABLE語句中的表選項(xiàng)

1）engine：指定表使用的存儲引擎

存儲引擎：決定了數(shù)據(jù)如何存儲以及如何訪問，還有事務(wù)如何處理

MySQL允許對每個表使用不同的存儲引擎，如果在create table語句中沒有指定存儲引擎，則使用默認(rèn)的存儲引擎。

　　mysql> show engines;    #查詢所有支持的存儲引擎

　　mysql> CREATE TABLE sexes(sex char(1) NOT NULL) ENGINE = INNODB; 

注意：存儲引擎是個重點(diǎn)，后面我們詳細(xì)講解。

2）auto_increment：決定當(dāng)向表中插入第一行時，自增列得到的第一個值是多少

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）

　　約束主要是防止非法數(shù)據(jù)進(jìn)入到表中，確保數(shù)據(jù)的正確性和一致性（統(tǒng)稱數(shù)據(jù)完整性）；

　　約束也可以防止一個表被刪除 。

注意：

　　1）MySQL中約束保存在information_schema.table_constraints中，可以通過該表查詢約束信息；

　　2）進(jìn)行約束定義的時間：使用create table語句、使用alter table語句。

常用的約束的類型：5種

　?、賜ot null：非空約束，指定某列不為空

　?、趗nique：唯一約束，指定某列和幾列組合的數(shù)據(jù)不能重復(fù)

　?、踦rimary key：主鍵約束，指定某列的數(shù)據(jù)不能重復(fù)、唯一

　　④foreign key：外鍵，指定該列記錄屬于主表中的一條記錄，參照另一條數(shù)據(jù)

　?、輈heck：檢查，指定一個表達(dá)式，用于檢驗(yàn)指定數(shù)據(jù)

約束定義的語法：

列級別：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約束只能在列級別定義，作用在多個列上的約束只能定義在表級別，例如復(fù)合主鍵約束；

　　2）列級別上不能定義外鍵約束，并且不能給約束起名字，由MySQL自動命名（NOT NULL除外）；

　　3）表級別上定義的約束可以給約束起名字（CHECK約束除外）

1、、not null非空約束

作用：用于確保當(dāng)前列的值不為空。

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值

注意：非空約束只能出現(xiàn)在表對象的列上。

2、unique唯一約束

　　1.唯一約束是指定table的列或列組合不能重復(fù)，保證數(shù)據(jù)的唯一性，約束的列不允許有重復(fù)值；

　　2.唯一約束不允許出現(xiàn)重復(fù)的值，但是可以為多個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值，因?yàn)閚ull <> null

　　4.在創(chuàng)建唯一約束時，如果不給唯一約束名稱，就默認(rèn)和列名相同；

　　5.唯一約束不僅可以在一個表內(nèi)創(chuàng)建，而且可以同時多表創(chuàng)建組合唯一約束。

mysql> create table test(    ->   id int not null,    ->   name varchar(20),    ->   password varchar(16),---使用表級約束語法       ->   constraint uk_name_pwd unique(name,password)    -> );    #表示用戶名和密碼組合不能重復(fù)Query OK, 0 rows affected (0.08 sec)查詢數(shù)據(jù)字典，查看唯一鍵約束的信息mysql> select * from information_schema.table_constraints    -> where table_name='test';

3、primary key主鍵約束

　　primary key = not null + unique

主鍵：用來唯一的標(biāo)示表中的每一行(類型一般為整型或者字符串)

　　具有主鍵約束的列不允許有null值，并且不允許有重復(fù)值;

　　每個表最多只允許一個主鍵(可定義聯(lián)合主鍵)，主鍵名總是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

!!給主鍵一個新的名字，但在數(shù)據(jù)字典中，主鍵名還是顯示primary

聯(lián)合主鍵（用幾個列進(jìn)行唯一標(biāo)識一行）

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外鍵約束

外鍵約束：

　　參照完整性約束，保證一個或兩個表之間的參照完整性，外鍵是構(gòu)建于一個表的兩個字段或是兩個表的兩個字段之間的參照關(guān)系。

注意：

　　1）具有外鍵約束的列的值不能隨便給，必須滿足外鍵所引用的主鍵的取值；

　　2）一張表中可以定義多個外鍵；

　　3）外鍵列默認(rèn)可以給null值。

按照定義，外鍵必須引用一個主鍵或者唯一鍵，引用的主鍵一般在另外一張表中，也可以是本表的主鍵(后者稱為“自引用”)。

父子表：

　　外鍵所在的表叫做子表、從表

　　外鍵所引用的主鍵所在的表叫做父表、主表

注意：父子表是相對而言的，表a可以是表b的子表，但同時也可以是表c的父表

示例：創(chuàng)建外鍵約束

//創(chuàng)建父表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,'銷售部');//創(chuàng)建子表（表級別創(chuàng)建外鍵約束）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)    -> );

查詢數(shù)據(jù)字典，查看外鍵約束的信息：

　　上面創(chuàng)建子表的時候給外鍵約束命名emp_deptid_fk；

　　如果不給外鍵約束命名，那么默認(rèn)的名字是表名_ibfk_n, n是整數(shù),從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列沒有的數(shù)據(jù)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列沒有的數(shù)據(jù)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更新操作規(guī)則如下刪除規(guī)則……mysql> delete from dept where deptid=10;　　#delete父表中的行(子表中有引用的數(shù)據(jù)行)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`))外鍵的默認(rèn)刪除規(guī)則：　　當(dāng)刪除父表中的行時，如果子表中有依賴于被刪除父行的子行存在，那么就不允許刪除，并拋出異常（默認(rèn)對外鍵使用on delete restrict 或on delete no action選項(xiàng)）

外鍵引用定義： 

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選項(xiàng)，可以改變外鍵的默認(rèn)刪除規(guī)則：

　?、貽N DELETE CASCADE：級聯(lián)刪除。當(dāng)刪除父表中的行時，如果子表中有依賴于被刪除父行的子行存在，那么連同子行一起刪除（很危險！?。。?/p>

　?、贠N DELETE SET NULL：當(dāng)刪除父表中的行時，如果子表中有依賴于被刪除父行的子行存在，那么不刪除，而是將子行的外鍵列設(shè)置為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約束對數(shù)據(jù)驗(yàn)證沒有任何作用。

　　Oracle中可以使用check約束，是有相應(yīng)的作用的。

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。

定義數(shù)據(jù)庫列時，可以使用ENUM（enumeration，枚舉）和SET（集合）類型：變通的實(shí)現(xiàn)CHECK約束

兩者的區(qū)別是：

　　使用ENUM，只能選一個值；

　　使用SET，可以選多個值；

ENUM和SET中的值都必須是字符串類型。

1、enum枚舉類型

注意：

　　在內(nèi)部存儲ENUM值時，MYSQL給ENUM中的每個值一個順序號碼：第一個值的順序號碼是1，第二個值的順序號碼是2，以此類推。當(dāng)排序或比較ENUM的時候，使用這些順序號碼進(jìn)行。

mysql> select * from student order by sex;+----+--------+------+
| id | name   | sex  |
+----+--------+------+
|  5 | 王五    | NULL |
|  1 | 張三    | M    |
|  2 | 李四    | F    |
+----+--------+------+

2、set類型：因?yàn)榭梢粤信e多個值，所以在建表列約束中常被使用到。

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 1mysql> select * from team;+--------+------------+
| teamno | division   |
+--------+------------+
|      1 | west       |
|      2 | south,west |
|      4 | NULL       |
+--------+------------+