2的24次方

mysql之常量和数据类型

⼀、列的数据类型

1.数字类（int后⾯的数字并不代表存放的数字的长度，只代表显⽰的宽度，可以不关注）

数字类关注：

1、存储什么类型的数字

整数：正数、负数

Createtablet1（idint（正负都可以），id1intunsigned（正数））；

在int中插⼊⼩数，⾃动删除⼩数点后⾯的

2、存储的值的范围

mysql>helpint

Name:‘INT’

Description:

INT[(M)][UNSIGNED][ZEROFILL]

这⾥的M并不代表数字的长度或者范围，只表⽰显⽰宽度，可以不关注

nedrangeis-2147483648to2147483647.

Theunsignedrangeis0to4294967295.

数字类数据有：

1.1、整型：5种

每个整型类型可以指定⼀个最⼩显⽰宽度。这个宽度并不表⽰存储的值有多⼤

（官⽹搜索：datatypes）

类型取值范围

tinyint[(displaysize)]-128-----127

smallint[(displaysize)]-32768-----32767(⼏万)

mediumint[(displaysize)]-8388608—8388607（⼏百万）

integer[(displaysize)]-2147483648-----2147483647（⼏⼗亿）

bigint[(displaysize)]-9223372-----

9223372(天⽂数字)

例2：

CREATETABLEwidth(c4INTEGER(4));

INSERTINTOwidthVALUES(1);

SELECT*FROMwidth;

INSERTINTOwidthVALUES(10000);

SELECT*FROMwidth;

值超过显⽰宽度，显⽰宽度⾃动增加

1.2、⼩数类型（常⽤）

decimal[(precision[,scale])]

precision：精度，不包含⼩数点的数字总位数(1–30)。不指定默认为10

scale：⼩数位数。如果不指定，默认为0

该类型的同义词：dec，numeric，fixed

decimal[(P/M[,D])]：表⽰可以存储D位⼩数的P/M位数

precision(P/M)：有效数字数的精度,⽀持范围(1-65),不指定默认为10

scale：⼩数位数,⽀持范围（1-30）如果不指定，默认为0

该类型的同义词：dec，numeric，fixed

MySQL要求P/M>=D

只想要⼩数：P/M=D

只想要整数：(P/M，0),0可省略

此时插⼊整数可以；

插⼊⼩数也可以，但会对第⼀位⼩数四舍五⼊，取整数

mysql>helpdecimal

Name:‘DECIMAL’

Description:

DECIMAL[(M[,D])][UNSIGNED][ZEROFILL]

Apacked“exact”totalnumberofdigits

(theprecision)andDisthenumberofdigitsafterthedecimalpoint

(thescale).Thedecimalpointand(fornegativenumbers)the-sign

0,valueshavenodecimalpointor

imumnumberofdigits(M)forDECIMALis65.

Themaximumnumberofsupporteddecimals(D)mitted,

mitted,thedefaultis10.

UNSIGNED,ifspecified,disallowsnegativevalues.

Allbasiccalculations(+,-,*,/)withDECIMALcolumnsaredonewith

aprecisionof65digits.

1.3、Float（不常⽤）

1.⼩数位数不是固定的

2.超出存储范围，近似值进⾏存储

0-236

24-5315

mysql>helpfloat

Name:‘FLOAT’

Description:

FLOAT[(M,D)][UNSIGNED][ZEROFILL]

float[(length)|(,)]

double[(precision)|(,)]

主要⽤来存储⼩数点前后有很多位数字的值。它和⼩数类型的区别在于⼩数点的位置可以在任何地⽅，即⼩数点是“浮动的”。由于对⼀个

浮点数字来说，可⽤的存储空间有限，如果⼀个数字⾮常⼤或⾮常⼩，将存储这个数字的近似值⽽不是实际值

浮点数⼜分为单精度和双精度。区别在于值所保留的存储空间数量不同

在⼀个浮点数据类型中可以指定长度，来确定具体的浮点类型：长度在0—24之间的是单精度浮点数，长度在25—53之间的是双精度浮点

数

createtablec(idfloat(25));——只有M的时候，M>24才会⾃动变成double。

createtabled(idfloat(29,3));——整数位7位后数据不准

⼀个单精度浮点，数字精确到⼩数点后7位。即对第8位进⾏四舍五⼊，保留到第七位

双精度浮点，数字精确到⼩数点后15位。

例：

createtableg(idfloat(10,9));

inrtintogvalues(1.123456789);

inrtintogvalues(1.3);

inrtintogvalues(1.3);

测试float类型的精确度

例：

createtablef(idfloat(10,2));

inrtintofvalues(12345678.13);

inrtintofvalues(12345678.16);

inrtintofvalues(12345678.564);

inrtintofvalues(12345678.568);

inrtintofvalues(12345678.168);

例3：使⽤浮点数据类型

CREATETABLEmeasurements(

nrINTEGER,

measurement_valueFLOAT(1));

INSERTINTOmeasurements

VALUES(1,99.99),

(2,99999.99),

(3,99999999.99),

(4,99999999999.99),

(5,99999999999999.99),

(6,0.999999),

(7,0.9999999),

(8,99999999.9999),

(9,(1.0/3));

SELECT*FROMmeasurements;

结果中，第1，6⾏存储的是实际值，第2,3,4,5,7,8,9⾏存储的是近似值⽽不是实际值

将上例中的float(1)改为float(30),再插⼊同样的数据，观察结果：第1,2,3,4,6,7,8⾏保存的是实际值，第5,9⾏存储近似值

如果使⽤两个参数来指定浮点类型，那么它的含义是指定显⽰浮点值的宽度（1—255）和⼩数位数（0—30）。该类型⾃动成为⼀个单精

度浮点数

例4：

CREATETABLEmeasurements(

nrINTEGER,

measurement_valueFLOAT(17,3));

INSERTINTOmeasurements

VALUES(1,99.99),

(2,99999.99),

(3,99999999.99),

(4,99999999999.99),

(5,99999999999999.99),

(6,0.999999),

(7,0.9999999),

(8,99999999.9999),

(9,(1.0/3));

SELECT*FROMmeasurements;

可见，所有的值在⼩数点后⾯有3位。具有宽度和⼩数位数的float的同义词是real和float4。Double也是float的同义词。指定double等于

指定了float(30)

注意：精确存储⽤decimal，模糊存储⽤float

2、位类型（⽤的很少，可以不看）

BIT[(LENGTH)]

存储基于位的值。Length取值范围1—64.默认为1

案例：

存储⼈员信息表，包括：姓名，⼿机号码，性别（男⼥）（0,1），是否考上⼤学（是否）（0,1），是否找到⼯作（是，否）（0,1），在

本地还是外省（本地，外省）（0,1），⼯资范围（<1000，1000-2000，2000-3000，>3000）（00,01,10,11），可以将以上信息

归为三列，姓名，⼿机号码，属性，其中属性为bit类型的，例如：

姓名⼿机号码属性（bit）

张三1391

例5：

CREATETABLEan_bit(idBIT(8));

INSERTINTOan_bitVALUES(b’11’);

不能这样查询：

SELECT*FROMan_bit;

可以看到⼗进制的值：

SELECTid+0FROMan_bit;

可以看到⼆进制的值：

SELECTbin(id+0)FROMan_bit;

可以看到⼗六进制的值：

SELECThex(id+0)FROMan_bit;

例：

createtablett(idbit(8));

inrtintottvalues(b’111’);

inrtintotttid=b’11’;

不能这样查询：lectidfromtt;

可以看到⼗进制、⼆进制、⼋进制、⼗六进制的值

lectid,(id+0),bin(id+0),oct(id+0),hex(id+0)fromtt;

3、字符串类型

char[(length)]：固定长度字符串。值少于长度则在尾部⾃动填充空格。length的单位是字符，底层具体⼏个字节不确定。取值范围0—

255

varchar[(length)]（常⽤，短）：可变长度字符串。length的单位是字符。取值范围0—65535

longvarchar（长）：可变长度字符串。最⼤长度2的24次⽅减1个字符

Longtext（长）：可变长度字符串。最⼤长度4G个字符

对于char和varchar，如果长度为0，则只能存储null值或者空字符串’’值

TEXT，最⼤65535（2^16-1），但是建表时最⼤到4294967295，根据⼤⼩⾃由变换（tinytext/text/mediumtext/longtext），⼀

般⽤于存储⽂字

TINYTEXT：最⼤255(2^8−1)个字符

MEDIUMTEXT：可变长度字符串。最⼤长度16,777,215（2^24-1）个字符

LONGTEXT：可变长度字符串。最⼤长度4,294,967,295(2^32-1)个字符

text是varchar的同义词。mediumtext是longvarchar的同义词

当要存储的数据较长时，选择哪⼀种数据类型根据具体的长度选择longvarchar或者longtext。

4、⽇期,时间

⽇期类型

date：4字节

datetime：8字节

time：3字节

timestamp（微秒）：4字节

year[(2)|(4)]：1字节

5、⼆进制⼤对象类型

binary：定长⼆进制字符串，255个字符

varbinary：可变长⼆进制字符串，65535个字符

blob，最⼤65535（2^16-1）个字节，但是建表时最⼤到4294967295，根据⼤⼩⾃由变换

（tinyblob/blob/mediumblob/longblob），⼀般⽤于存储⼆进制数据，如图⽚，⾳频，⽂件等

TINYBLOB：最⼤255(2^8−1)个字节

MEDIUMBLOB：可变长度⼆进制字符串。最⼤长度16,777,215（2^24-1）个字节LONGBLOB：最⼤长度4,294,967,295(2^32-

1)个字节

longvarbinary：2的24次⽅减1个字符

longblob（存放照⽚，⽂件）：2的32次⽅-1个字符

注意：

在数据库中存放照⽚的⽅法：

1、存放照⽚时可以在数据库中只存放照⽚的名字，在⽬录中存放真实的照⽚。（推荐使⽤）

2、在数据库中直接⽤blob或longblob存放照⽚

注意：在数据库中删除表droptablet1，t2，t3；

6、使⽤ENUM和SET类型

定义数据库列时，可以使⽤ENUM（enumeration，枚举）和SET（集合）类型。通过它们，可以变通的实现CHECK约束

它们两者的区别是：使⽤ENUM，只能选⼀个值；

使⽤SET，可以选多个值

它们的相同点是：ENUM和SET中的值都必须是字符串类型

6.1、ENUM类型（⽤的多）

例：x列的取值只能是M或F

CREATETABLEplayers_small(

playernoINTEGERPRIMARYKEY,

NAMEchar(15)NOTNULL,

initialschar(3)NOTNULL,

birth_dateDATE,

xENUM(‘M’,‘F’));

INSERTINTOplayers_smallVALUES(24,‘John’,‘p’,‘1985-04-22’,‘M’);

INSERTINTOplayers_smallVALUES(25,‘Marry’,‘q’,‘1981-07-01’,‘F’);

–⼩写m可以⾃动转为⼤写

INSERTINTOplayers_smallVALUES(111,‘Tom’,‘t’,‘1982-11-11’,‘m’);

INSERTINTOplayers_smallVALUES(201,‘Chen’,‘p’,‘1972-02-12’,NULL);

INSERTINTOplayers_smallVALUES(199,‘king’,‘s’,‘1970-02-12’,‘X’);

错误代码：1265

DATAtruncatedFORCOLUMN‘x’ATROW1

SELECT*FROMplayers_small;

在内部存储ENUM值时，MYSQL给ENUM中的每个值⼀个顺序号码。第⼀个值的顺序号码是1，第⼆个值的顺序号码是2，以此类推。当排

序或⽐较ENUM的时候，使⽤这些顺序号码进⾏。

SELECT*FROMplayers_small

ORDERBYx;

可见，排序并不是按照字母顺序把F放在前⾯，M放在后⾯

6.2、SET类型（⽤的少）

具有SET类型的⼀个列可以包含t列表中的多个值（最多64个）

例：假设球队可以在多个分级中⽐赛

CREATETABLEteams_new(

teamnoINTEGERNOTNULL,

playernoINTEGERNOTNULL,

divisionSET(‘first’,‘cond’,‘third’,‘fourth’));

INSERTINTOteams_newVALUES(1,27,‘first’);可以取⼀个

INSERTINTOteams_newVALUES(2,27,‘first,third’);也可以取多个

INSERTINTOteams_newVALUES(4,27,NULL);

INSERTINTOteams_newVALUES(3,27,‘first,third,sixth’);

错误代码：1265

DATAtruncatedFORCOLUMN‘division’ATROW1

SELECT*FROMteams_new;

⼆、添加数据类型选项

在某些字符串类型和所有的数字类型后⾯，可以指定⼀个数据类型选项，以改变数据类型的属性和功能。

对于字符串类型，可以添加两个类型选项：charactert和collate

对于bit之外的所有数字类型，可以添加⼀个或⼏个类型选项：unsignedzerofillauto_increment、rialdefaultvalue

ed：⽆符号的，表⽰只允许正数

例6：

CREATETABLEpenaltiesdef(

paymentnoINTEGERUNSIGNEDNOTNULLPRIMARYKEY,

playernoINTEGERUNSIGNEDNOTNULL,

payment_dateDATENOTNULL,

amountDECIMAL(7,2)UNSIGNEDNOTNULL);

ll:填充0。影响数字的显⽰⽅式：如果⼀个数字的宽度⼩于所允许的最⼤宽度，这个值前⾯会⽤0填充，如果⼤于所允许的最⼤宽

度就不会填充了。如果声明了zerofill，该列会⾃动设为unsigned

例7：

DROPTABLEwidth;

CREATETABLEwidth(c4INTEGER(4)ZEROFILL);

INSERTINTOwidthVALUES(1);

INSERTINTOwidthVALUES(200);

SELECT*FROMwidth;

_increment：⾃动增长。只适应于整数类型。⼀个表中只有⼀个列可以是⾃动增长的。

例：创建⼀个表，其主键是⾃动增长的

CREATETABLEaa(

qnointUNSIGNEDNOTNULLAUTO_INCREMENTPRIMARYKEY,

nameVARCHAR(30)NOTNULL);

当在inrt语句中为Auto_increment列指定了null值，或者没有指定值时，MySQL⾃动为其赋值：

inrtintoaavalues(NULL,‘beijing’),(NULL,‘shanghai’),(NULL,‘tianjin’);

SELECT*FROMaa;

MySQL会记住给出的最后⼀个顺序号码是多少。当要分配⼀个新的顺序号码时，最后⼀个顺序号码被找到，并且列中的最⼤值被确定。新

的顺序号码就是这两个值中的最⼤者加1。

deletefromaa;

inrtintoaavalues(NULL,‘beijing’),(NULL,‘shanghai’),(NULL,‘tianjin’);

lect*fromaa;

可见，即使删除了表中的所有⾏，插⼊新⾏时，编号还是从原来的位置继续。要想再次从1开始，必须删除整个表并重新创建。

当在INSERT语句中指定了AUTO_INCREMENT列的值时，MySQL会使⽤它：

例9：

例9：

INSERTINTOcity_namesVALUES(8,‘成都’);

INSERTINTOcity_namesVALUES(NULL,‘武汉’);

SELECT*FROMcity_names;

例10：

DELETEFROMcity_names;

INSERTINTOcity_namesVALUES(NULL,‘北京’);

INSERTINTOcity_namesVALUES(NULL,‘上海’);

SELECT*FROMcity_names;

可见，即使删除了表中的所有⾏，插⼊新⾏时，编号还是从原来的位置继续。要想再次从1开始，必须删除整个表并重新创建

默认情况下，顺序号码从1开始并且每次加1。可以分别使⽤两个系统变量来改变默认⾏为：

例11：设定顺序号码从10开始，每次加10

SET@@auto_increment_offt=10,–起始值

@@auto_increment_increment=10;–每次加⼏

CREATETABLEt10(

qnoINTEGERAUTO_INCREMENTNOTNULLPRIMARYKEY);

INSERTINTOt10VALUES(NULL),(NULL);

SELECT*FROMt10;

defaultvalue：是auto_incrementnotnullunique的缩写

createtablett(idintrialdefaultvalue);

三、列选项

定义列时，除了指定列名字、数据类型、⼤⼩之外，还可以指定列选项

1、DEFAULT

例21：创建penalties表，其中amount列的默认值为50，payment_date列的默认值为1990年1⽉1号

CREATETABLEpenalties_default(

paymentnoINTEGERNOTNULLPRIMARYKEY,

playernoINTEGERNOTNULL,

payment_dateDATEDEFAULT‘1990-01-01’,

amountDECIMAL(7,2)NOTNULLDEFAULT50.00);

列⼀旦定义了默认值，当插⼊⼀个新⾏到表中并且没有给该列明确赋值时，它将⾃动得到默认值

INSERTINTOpenalties_default(paymentno,playerno)VALUES(15,27);

SELECT*FROMpenalties_default;

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

INSERTINTOpenalties_default(paymentno,playerno,payment_date,amount)

VALUES(16,27,DEFAULT,DEFAULT);

UPDATEpenalties_default

SETamount=DEFAULT;

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

例：UPDATEpenalties_default

SETamount=year(default(payment_date))*10;

2、comment：⽤来给列添加注释。最多255个字符。注释会保存到数据字典中。例22：

CREATETABLEpenalties_comment(

paymentnoINTEGERNOTNULLPRIMARYKEY

COMMENT‘罚款编号’,

playernoINTEGERNOTNULL

COMMENT‘球员编号’,

payment_dateDATEDEFAULT‘1990-01-01’

COMMENT‘罚款⽇期’,

amountDECIMAL(7,2)NOTNULLDEFAULT50.00COMMENT’⾦额’);

例23：从数据字典查询注释信息

SELECTcolumn_name,column_comment

FROMinformation_s

WHEREtable_name=‘PENALTIES_comment’;

四、表选项

在CREATETABLE语句中，可以在表结构的后⾯指定⼏个表选项

1、engine：指定表使⽤的存储引擎。存储引擎决定了数据如何存储以及如何访问，还有事务如何处理。MySQL允许对每个表使⽤不同的

存储引擎。如果在createtable语句中没有指定存储引擎，则使⽤默认的存储引擎

例24：查询所有⽀持的存储引擎

SHOWENGINES;

例25：

CREATETABLExes(

xchar(1)NOTNULL)ENGINE=INNODB;

2、auto_increment：该选项决定了当向表中插⼊第⼀⾏时，⾃增列得到的第⼀个值是多少

例26：创建表city_names,其主键列从10开始编号

DROPTABLEcity_names;

CREATETABLEcity_names(

qnoINTEGERNOTNULLAUTO_INCREMENTPRIMARYKEY,

NAMEVARCHAR(30)NOTNULL)AUTO_INCREMENT=10;

INSERTINTOcity_namesVALUES(NULL,‘北京’);

INSERTINTOcity_namesVALUES(NULL,‘上海’);

INSERTINTOcity_namesVALUES(NULL,‘天津’);

SELECT*FROMcity_names;

3、comment:给表添加注释

例27：

CREATETABLEmycomm(

column_aINTEGER)COMMENT‘测试表’;

SELECTtable_name,table_comment

FROMinformation_

WHEREtable_name=‘MYCOMM’;

五、约束

可以为列定义约束。约束主要是防⽌⾮法数据进⼊到表中，确保数据的正确性和⼀致性（统称数据完整性）。约束也可以防⽌⼀个表被删除

约束的类型：5种

NOTNULL：⾮空约束（尽量都加⼊，强烈建议使⽤）

UNIQUE：唯⼀性约束（这个列的值唯⼀，可以为空值，⼀个表可以有多个unique）

PRIMARYKEY：主键约束（⼀个表只能有⼀个primarykey，这个列数值唯⼀，不能为空）

FOREIGNKEY：外键约束

CHECK：检查约束

约束定义的时间：

1、使⽤CREATETABLE语句

2、表已经创建完了，使⽤ALTERTABLE语句

约束定义的语法：

列级别：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)

注意：NOTNULL约束只能在列级别定义，作⽤在多个列上的约束只能定义在表级别，例如复合主键约束。列级别上不能定义外键约束，并

且不能给约束起名字，由MySQL⾃动命名（NOTNULL除外）。表级别上定义的约束可以给约束起名字（CHECK约束除外）

1、NOTNULL约束

具有⾮空约束的列不允许有NULL值

CREATETABLEtest_nn(

idINTEGERNOTNULL);

约束直接对DML操作带来影响

INSERTINTOtest_nn(id)VALUES(1);

INSERTINTOtest_nn(id)VALUES(NULL);

错误代码：1048

COLUMN‘id’cannotbeNULL

UPDATEtest_nnSETid=NULLWHEREid=1

错误代码：1048

COLUMN‘id’cannotbeNULL

2、UNIQUE约束

具有唯⼀性约束的列不允许有重复值。

在创建唯⼀性约束的时候，如果不给唯⼀性约束名称，就默认和列名相同

CREATETABLEtest_uk(idINTEGERUNIQUE);

INSERTINTOtest_uk(id)VALUES(1);

INSERTINTOtest_uk(id)VALUES(2);

INSERTINTOtest_uk(id)VALUES(1);

错误代码：1062

DUPLICATEentry‘1’FORKEY‘id’

UPDATEtest_uk

SETid=1

WHEREid=2;

错误代码：1062

DUPLICATEentry‘1’FORKEY‘id’

INSERTINTOtest_uk(id)VALUES(NULL);

INSERTINTOtest_uk(id)VALUES(NULL);

SELECT*FROMtest_uk;

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

查询数据字典，查看唯⼀键约束的信息

SELECT*FROMinformation__CONSTRAINTS

WHEREtable_name=‘test_uk’;

3、PRIMARYKEY

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

Primarykey=notnull+unique

主键：⽤来唯⼀的标⽰表中的每⼀⾏。其类型⼀般为整型或者字符串

每个表最多只允许⼀个主键。主键名总是PRIMARY。

CREATETABLEtest_pk(idINTEGERPRIMARYKEY);

INSERTINTOtest_pk(id)VALUES(1);

INSERTINTOtest_pk(id)VALUES(2);

INSERTINTOtest_pk(id)VALUES(2);

错误代码：1062

Duplicateentry‘2’forkey‘PRIMARY’

UPDATEtest_pk

SETid=1

WHEREid=2;

错误代码：1062

Duplicateentry‘1’forkey‘PRIMARY’

INSERTINTOtest_pk(id)VALUES(NULL);

错误代码：1048

Column‘id’cannotbenull

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

SELECT*FROMinformation__CONSTRAINTS

WHEREtable_name=‘test_uk’;

4、FOREIGNKEY：

外键：

1、外键是⼀个约束

2、外键约束什么呢？

外键约束⼀个表的⼀个列的数值来⾃于另外⼀个表的⼀个列的数值

案例：现有学⽣表，科⽬表

要求成绩表⾥⾯的学号这个列⾥⾯的所有数值（学号），必须来⾃于学⽣表⾥⾯的学号这个列⾥⾯的数值

例如：createtablechengji（s_idint，k_idint，chengjidecimal（5,2），constrainti_fiforeignkey（s_id）references

xuesheng（s_id），constrainti_f2foreignkey（k_id）referenceskemu（k_id））；

3、外键引⽤的⼀定是另外⼀个表的主键或者唯⼀键

创建的外键，约束在哪⾥找到呢？

Showdatabas；

Uinformation_schema;

Showtables;

Select*fromTABLE_CONSTRAINTS;

外键约束规则：

1、从表inrt数据，会检查主表，看⼀下外键列的数据是否来⾃于主键列

2、对主表进⾏delete的时候，检查外表，看⼀下外表中是否引⽤了主表中要删除的数据

3、如果我要修改外键引⽤的主键列的话，会报错，⼦会失去⽗

注意：只要有引⽤，主表就受到了限制

ondelete

主表删除数据，⼦表引⽤了主表的数据，外键该如何动作

RESTRICT/NOACTION:不允许

CASCADE：级联删除

SETNULL:⼦表外键改为null

注意：

删除外键：

altertablechengjidropforeignkeyi_f1；

添加外键：

altertablechengjiaddconstrainti_f1foreignkey（s_id）referencesxuesheng（s_id）ondeletecascde；

外键约束⼜叫做参照完整性约束。具有外键约束的列，因为着它的值不能随便给，必须满⾜外键所引⽤的主键的取值。⼀张表中可以定义多

个外键。外键列默认可以给null值。

按照定义，外键必须引⽤⼀个主键或者唯⼀键。引⽤的主键⼀般在另外⼀张表中，也可以是本表的主键。后⼀种情况通常称为“⾃引⽤”。

⽗⼦表的概念：外键所在的表叫做⼦表，外键所引⽤的主键所在的表叫做⽗表。注意，⽗⼦表是相对⽽⾔的，表a可以是表b的⼦表，但同时

也可以是表c的⽗表

例：

Createtablefather(namevarchar(20)primarykey,idint);

createtablechild(idint,father_namevarchar(20),child_namevarchar(20),CONSTRAINTidx1primarykey(id),CONSTRAINT

idx2foreignkey(father_name)referencesfather(name));

inrtintofathervalues(‘zhangsan’,1),(‘lisi’,1),(‘wangwu’,1);

inrtintochildvalues(1,‘lisi’,‘lisan’);

inrtintochildvalues(2,‘lisishsh’,‘lisan’);

deletefromfatherwherename=‘lisi’;

创建⽗表

CREATETABLEdept(

deptidINTEGER,

dnameVARCHAR(20),

CONSTRAINTdept_deptid_pkPRIMARYKEY(deptid));

INSERTINTOdept(deptid,dname)VALUES(10,‘市场部’);

INSERTINTOdept(deptid,dname)VALUES(20,‘销售部’);

查询数据字典，查看主键约束的信息。可见，虽然给主键⼀个新的名字，但在数据字典中，主键名还是显⽰primary

SELECT*FROMinformation__CONSTRAINTS

WHEREtable_name=‘dept’;

创建⼦表

CREATETABLEemp(

idINTEGER,

NAMEVARCHAR(20),

deptidINTEGER,

CONSTRAINTemp_id_pkPRIMARYKEY(id),

CONSTRAINTemp_deptid_fkFOREIGNKEY(deptid)REFERENCESdept(deptid));

查询数据字典，查看外约束的信息。如果不给外键约束命名，那么默认的名字为：表名_ibfk_n,n是整数,从1开始

SELECT*FROMinformation__CONSTRAINTS

WHEREtable_name=‘emp’;

INSERTINTOemp(id,NAME,deptid)VALUES(1,‘张三’,10);

INSERTINTOemp(id,NAME,deptid)VALUES(2,‘李四’,10);

INSERTINTOemp(id,NAME,deptid)VALUES(3,‘王五’,50);

错误代码：1452

CannotADDORUPDATEachildROW:aFOREIGNKEYCONSTRAINTfails(,CONSTRAINTemp_deptid_fk

FOREIGNKEY(deptid)REFERENCESdept(deptid))

UPDATEemp

SETdeptid=50

WHEREid=1;

错误代码：1452

CannotADDORUPDATEachildROW:aFOREIGNKEYCONSTRAINTfails(,CONSTRAINTemp_deptid_fk

FOREIGNKEY(deptid)REFERENCESdept(deptid))

删除⽗表中的⾏：

DELETEFROMdeptWHEREdeptid=10;

错误代码：1451

CannotDELETEORUPDATEaparentROW:aFOREIGNKEYCONSTRAINTfails(,CONSTRAINTemp_deptid_fk

FOREIGNKEY(deptid)REFERENCESdept(deptid))

这就是外键的默认删除规则：当删除⽗表中的⾏时，如果⼦表中有依赖于被删除⽗⾏的⼦⾏存在，那么就不允许删除，并抛出异常（默认对

外键使⽤ondeleterestrict或ondeletenoaction选项）

在定义外键约束时，通过使⽤ondeletecascade或者ondeletetnull选项，可以改变外键的默认删除规则

ONDELETECASCADE：级联删除。当删除⽗表中的⾏时，如果⼦表中有依赖于被删除⽗⾏的⼦⾏存在，那么连同⼦⾏⼀起删除

DROPTABLEemp;

SET@@autocommit=0;

CREATETABLEemp(

idINTEGER,

NAMEVARCHAR(20),

deptidINTEGER,

CONSTRAINTemp_id_pkPRIMARYKEY(id),

CONSTRAINTemp_deptid_fkFOREIGNKEY(deptid)REFERENCESdept(deptid)

ONDELETECASCADE);

INSERTINTOemp(id,NAME,deptid)VALUES(1,‘张三’,10);

INSERTINTOemp(id,NAME,deptid)VALUES(2,‘李四’,10);

DELETEFROMdeptWHEREdeptid=10;

观察⼦表中的⾏被同时删除

SELECT*FROMemp;

ONDELETESETNULL：当删除当删除⽗表中的⾏时，如果⼦表中有依赖于被删除⽗⾏的⼦⾏存在，那么不删除中，⽽是将⼦⾏的外键列

设置为null

DROPTABLEemp;

CREATETABLEemp(

idINTEGER,

NAMEVARCHAR(20),

deptidINTEGER,

CONSTRAINTemp_id_pkPRIMARYKEY(id),

CONSTRAINTemp_deptid_fkFOREIGNKEY(deptid)REFERENCESdept(deptid)

ONDELETESETNULL);

INSERTINTOemp(id,NAME,deptid)VALUES(1,‘张三’,10);

INSERTINTOemp(id,NAME,deptid)VALUES(2,‘李四’,10);

DELETEFROMdeptWHEREdeptid=10;

—观察⼦表中的⾏还存在，但是外键为null

SELECT*FROMemp;

对于外键的更新操作，也有类似的onupdate{restrict|noaction}、onupdatecascade、onupdatetnull选项

5、CHECK约束

MySQL中可以写出CHECK约束，但实际上没有任何作⽤（TheCHECKclauispardbutignoredbyallstorageengines）

CREATETABLEtest_ck(

idINTEGERCHECK(id>0)

);

INSERTINTOtest_ckVALUES(-100);也是可以的

六、对表名和列名使⽤反引号（不建议使⽤）

如果表名或者列名使⽤了MySQL的保留字，那么必须使⽤反引号（1左边的字符）把它们括起来，否则出错

在引⽤这些表名和列名时，也必须加上反引号

CREATETABLElect(

lectINTEGER

);

SELECTlectFROMlectWHERElect=1;

如果表名和列名都不是Mysql的保留字，也可以使⽤反引号把它们括起来。在引⽤这些表名和列名时，可以不加反引号。

CREATETABLEmyemp(

idINTEGER

);

SELECTidFROMmyemp;

SELECTidFROMmyemp;