一、CREATE TABLE 选项

1、在定义列的时候，指定列选项

1）DEFAULT <literal>：定义列的默认值

　　当插入一个新行到表中并且没有给该列明确赋值时，如果定义了列的默认值，将自动得到默认值 ；如果没有，则为null。

mysql>createtablepeople->(->　　idintnotnullprimarykey,->　　namevarchar(20)notnull,->　　sexchar(1)default'm'->);mysql>insertintopeople(id,name)values(1,'张三');mysql>insertintopeoplevalues(2,'李四','f');mysql>select*frompeople;+----+--------+------+|id|name|sex|+----+--------+------+|1|张三|m||2|李四|f|+----+--------+------+

当然，也可以在INSERT和UPDATE语句中使用DEFAULT关键字显式地给列赋默认值：

mysql>insertintopeoplevalues(3,'王五',default);mysql>updatepeoplesetsex=defaultwhereid=2;mysql>select*frompeople;+----+--------+------+|id|name|sex|+----+--------+------+|1|张三|m||2|李四|m||3|王五|m|+----+--------+------+

函数default(column)可以得到一个列的默认值：

mysql>selectdefault(sex)frompeople;+--------------+|default(sex)|+--------------+|m||m||m|+--------------+

2）comment：用来给列添加注释，最多255个字符，注释会保存到数据字典中。

　　创建带有列注释的表stu_comment

mysql>createtablestu_comment->(->idintnotnullprimarykey->comment'学号',->namevarchar(20)notnull->comment'姓名'->);

　从数据字典查询注释信息

mysql>selectcolumn_name,column_comment->frominformation_schema.columns->wheretable_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>createtablemycomm(numint)comment'测试表';mysql>selecttable_name,table_comment->frominformation_schema.tables->wheretable_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：检查，指定一个表达式，用于检验指定数据

约束定义的语法：

列级别：CREATETABLEtable_name(column_namedata_type　　[[NOTNULL]|[UNIQUE[KEY]|PRIMARYKEY]　　|CHECK(expr)],…)表级别：CREATETABLEtable_name(　　column_namedata_type[NOTNULL],　　column_namedata_type[notnull],…,　　[CONSTRAINTconstraint_name]PRIMARYKEY(col_name,...)　　|[CONSTRAINTconstraint_name]unique(col_name,...)　　|[CONSTRAINTconstraint_name]foreignKEY(col_name)REFERENCEStbl_name(index_col_name)　　|check(expr)

注意：

　　1）NOT NULL约束只能在列级别定义，作用在多个列上的约束只能定义在表级别，例如复合主键约束；

　　2）列级别上不能定义外键约束，并且不能给约束起名字，由MySQL自动命名（NOT NULL除外）；

　　3）表级别上定义的约束可以给约束起名字（CHECK约束除外）

1、、not null非空约束

作用：用于确保当前列的值不为空。

mysql>createtabletemp_nn(idintnotnull);约束直接对DML操作带来影响mysql>insertintotemp_nnvalues(1);QueryOK,1rowaffected(0.00sec)mysql>insertintotemp_nnvalues(null);ERROR1048(23000):Column'id'cannotbenull具有非空约束的列不允许有null值

注意：非空约束只能出现在表对象的列上。

2、unique唯一约束

　　1.唯一约束是指定table的列或列组合不能重复，保证数据的唯一性，约束的列不允许有重复值；

　　2.唯一约束不允许出现重复的值，但是可以为多个null；

　　3.同一个表可以有多个唯一约束，多个列组合的约束

mysql>createtabletemp_uk(->idintnotnullunique,->namevarchar(20)unique);mysql>insertintotemp_ukvalues(1,'a');mysql>insertintotemp_ukvalues(2,'a');ERROR1062(23000):Duplicateentry'a'forkey'name'mysql>insertintotemp_ukvalues(2,null);mysql>insertintotemp_ukvalues(3,null);mysql>select*fromtemp_uk;+----+------+|id|name|+----+------+|2|NULL||3|NULL||1|a|+----+------+

可见，唯一性约束的列可以有多个null值，因为null <> null

　　4.在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同；

　　5.唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。

mysql>createtabletest(->idintnotnull,->namevarchar(20),->passwordvarchar(16),---使用表级约束语法->constraintuk_name_pwdunique(name,password)->);#表示用户名和密码组合不能重复QueryOK,0rowsaffected(0.08sec)查询数据字典，查看唯一键约束的信息mysql>select*frominformation_schema.table_constraints->wheretable_name='test';

3、primary key主键约束

　　primary key = not null + unique

主键：用来唯一的标示表中的每一行(类型一般为整型或者字符串)

　　具有主键约束的列不允许有null值，并且不允许有重复值;

　　每个表最多只允许一个主键(可定义联合主键)，主键名总是PRIMARY。

mysql>createtabletemp_pk(->idintprimarykey);mysql>insertintotemp_pkvalues(1),(2);mysql>insertintotemp_pkvalues(1);ERROR1062(23000):Duplicateentry'1'forkey'PRIMARY'mysql>updatetemp_pksetid=1whereid=2;ERROR1062(23000):Duplicateentry'1'forkey'PRIMARY'mysql>insertintotemp_pkvalues(null);ERROR1048(23000):Column'id'cannotbenull

!!给主键一个新的名字，但在数据字典中，主键名还是显示primary

联合主键（用几个列进行唯一标识一行）

mysql>createtabletemp_pk(->idint,->namevarchar(20),->constraintpk_id_nameprimarykey(id,name)->);QueryOK,0rowsaffected(0.06sec)mysql>desctemp_pk;+-------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+-------------+------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(20)|NO|PRI|NULL||+-------+-------------+------+-----+---------+-------+2rowsinset(0.00sec)mysql>insertintotemp_pkvalues(1,'张三');QueryOK,1rowaffected(0.00sec)mysql>insertintotemp_pkvalues(2,'李四');QueryOK,1rowaffected(0.01sec)mysql>insertintotemp_pkvalues(1,'王五');QueryOK,1rowaffected(0.00sec)mysql>insertintotemp_pkvalues(1,'张三');ERROR1062(23000):Duplicateentry'1-张三'forkey'PRIMARY'mysql>select*fromtemp_pk;+----+--------+|id|name|+----+--------+|1|张三||1|王五||2|李四|+----+--------+3rowsinset(0.00sec)

4、foreign key外键约束

外键约束：

　　参照完整性约束，保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

注意：

　　1）具有外键约束的列的值不能随便给，必须满足外键所引用的主键的取值；

　　2）一张表中可以定义多个外键；

　　3）外键列默认可以给null值。

按照定义，外键必须引用一个主键或者唯一键，引用的主键一般在另外一张表中，也可以是本表的主键(后者称为“自引用”)。

父子表：

　　外键所在的表叫做子表、从表

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

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

示例：创建外键约束

//创建父表mysql>createtabledept(->deptidint,->dnamevarchar(20),->constraintdept_deptid_pkprimarykey(deptid)->);mysql>insertintodept(deptid,dname)values(10,'市场部');mysql>insertintodept(deptid,dname)values(20,'销售部');//创建子表（表级别创建外键约束）mysql>createtableemp(->idint,->namevarchar(20),->deptidint,->constraintemp_id_pkprimarykey(id),->constraintemp_deptid_fkforeignkey(deptid)->referencesdept(deptid)->);

查询数据字典，查看外键约束的信息：

　　上面创建子表的时候给外键约束命名emp_deptid_fk；

　　如果不给外键约束命名，那么默认的名字是表名_ibfk_n, n是整数,从1开始；

此时，emp表中deptid列(外键约束)受dept主表限制

mysql>insertintoemp(id,name,deptid)values(1,'张三',10);QueryOK,1rowaffected(0.00sec)mysql>insertintoemp(id,name,deptid)values(2,'李四',10);QueryOK,1rowaffected(0.00sec)mysql>insertintoemp(id,name,deptid)values(3,'王五',50);　　#insert主表deptid列没有的数据ERROR1452(23000):Cannotaddorupdateachildrow:aforeignkeyconstraintfails(`test`.`emp`,CONSTRAINT`emp_deptid_fk`FOREIGNKEY(`deptid`)REFERENCES`dept`(`deptid`))mysql>updateempsetdeptid=30whereid=1;　　#update主表deptid列没有的数据ERROR1452(23000):Cannotaddorupdateachildrow:aforeignkeyconstraintfails(`test`.`emp`,CONSTRAINT`emp_deptid_fk`FOREIGNKEY(`deptid`)REFERENCES`dept`(`deptid`))外键的update更新操作规则如下删除规则……mysql>deletefromdeptwheredeptid=10;　　#delete父表中的行(子表中有引用的数据行)ERROR1451(23000):Cannotdeleteorupdateaparentrow:aforeignkeyconstraintfails(`test`.`emp`,CONSTRAINT`emp_deptid_fk`FOREIGNKEY(`deptid`)REFERENCES`dept`(`deptid`))外键的默认删除规则：　　当删除父表中的行时，如果子表中有依赖于被删除父行的子行存在，那么就不允许删除，并抛出异常（默认对外键使用ondeleterestrict或ondeletenoaction选项）

外键引用定义：

reference_definition:REFERENCEStbl_name(index_col_name,...)[MATCHFULL|MATCHPARTIAL|MATCHSIMPLE][ONDELETEreference_option][ONUPDATEreference_option]reference_option:RESTRICT|CASCADE|SETNULL|NOACTION

在定义外键约束时，通过使用on delete cascade或者on delete set null选项，可以改变外键的默认删除规则：

　　①ON DELETE CASCADE：级联删除。当删除父表中的行时，如果子表中有依赖于被删除父行的子行存在，那么连同子行一起删除（很危险！！！）

　　②ON DELETE SET NULL：当删除父表中的行时，如果子表中有依赖于被删除父行的子行存在，那么不删除，而是将子行的外键列设置为null

　　……

mysql>createtableemp(->idint,->namevarchar(20),->deptidint,->constraintemp_id_pkprimarykey(id),->constraintemp_deptid_fkforeignkey(deptid)　　->referencesdept(deptid)->ondeletecascade->);……

5、check约束

　　MySQL可以使用check约束，但check约束对数据验证没有任何作用。

　　Oracle中可以使用check约束，是有相应的作用的。

mysql>createtabletest_ck(->idintcheck(id>0)->);mysql>insertintotest_ckvalues(-100);mysql>select*fromtest_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*fromstudentorderbysex;+----+--------+------+|id|name|sex|+----+--------+------+|5|王五|NULL||1|张三|M||2|李四|F|+----+--------+------+

2、set类型：因为可以列举多个值，所以在建表列约束中常被使用到。

mysql>createtableteam(->teamnointnotnull,->divisionset('north','south','east','west')->);mysql>insertintoteamvalues(1,'west');mysql>insertintoteamvalues(2,'west,south');mysql>insertintoteamvalues(4,null);mysql>insertintoteamvalues(3,'east,asia');ERROR1265(01000):Datatruncatedforcolumn'division'atrow1mysql>select*fromteam;+--------+------------+|teamno|division|+--------+------------+|1|west||2|south,west||4|NULL|+--------+------------+