第5章MySQL表结构管理

第5章MySQL表结构管理主要内容5.1表的概念5.2MySQL的数据类型5.3创建表5.4定义约束5.5使用Workbench创建表5.6查看表5.7修改表5.8删除表5.9本章小结5.1表的概念表（Table）是关系数据库中最重要的数据库对象，用来存储数据库中的数据。一个数据库包含一个或多个表，表由行（Row）和列（Column）组成。表中的一行称为一个记录（Record），每个表包含若干行数据。列又称为字段（Field），由数据类型、长度、是否允许空值、默认值等组成，每个列表示记录的一个属性。一个完整的表包含表结构和表数据两部分内容，表的结构主要包括表的列名、数据类型、长度、是否允许空值、默认值以及约束等，表数据就是表中的记录。例如，“教务管理系统”数据库中的学生表的结构和部分数据如表5.1和表5.2所示。5.1表的概念表5.1学生（student）表结构列名数据类型长度允许空值默认值约束说明snochar11否

主键学生编号snamevarchar10否

学生姓名ssexchar1否男只能取'男'或'女'性别sbirthdaydate

否

出生日期snationvarchar10否

民族mnochar4是

外键，参照major表的mno列专业编号5.1表的概念表5.2学生（student）表数据snosnamessexsbirthdaysnationmno20190101001刘丽女2001-3-2汉族010120190101002张林男2000-9-12汉族010120190102001李宏男2001-8-29回族010220200102001孙明男2001-10-18汉族010220200102002赵均男2000-12-19汉族010220200101001张莉女2001-6-20汉族010120210201001牛伟男2003-9-18回族02015.2MySQL的数据类型定义表时需要对表中的列进行属性设置，包括列的名称、数据类型、长度、默认值等，其中最重要的属性就是数据类型。数据类型是指存储在数据库中的数据的类型，决定了数据的存储格式和取值范围。MySQL中的数据类型主要包括数值类型、字符串类型、日期和时间类型、空间类型和JSON类型。本书主要介绍数值类型、字符串类型、日期和时间类型。5.2MySQL的数据类型5.2.1数值类型整数类型整数类型包括TINYINT、INTEGER（或INT）、SMALLINT、MEDIUMINT和BIGINT。数据类型存储字节数无符号数取值范围有符号数取值范围TINYINT10~255-128~127SMALLINT20~65535-32768~32767MEDIUMINT30~16777215-8388608~8388607INTEGER（或INT）40~4294967295-2147483648~2147483647BIGINT80~264-1-263~263-15.2MySQL的数据类型5.2.1数值类型定点数类型定点数类型包括DECIMAL和NUMERIC，用于存储精确的数值数据。在MySQL中，NUMERIC被实现为DECIMAL。定点数类型的定义语法为DECIMAL(M,D)或NUMERIC(M,D)，其中M是最大位数（精度），范围是1到65，D是小数点后的位数（小数位数），它的范围是0到30，并且不能大于M。如果D省略，则默认为0，如果M省略，则默认为10。例如，定义salary列的数据类型为DECIMAL(5,2)，则该列能够存储具有五位数字和两位小数的任何值，因此salary列的取值范围从-999.99到999.99。5.2MySQL的数据类型5.2.1数值类型浮点数类型浮点数类型包括单精度FLOAT类型和双精度DOUBLE类型，代表近似的数据值。MySQL对单精度值使用四个字节存储，对双精度值使用八个字节存储。对于FLOAT，MySQL支持在关键字FLOAT之后指定可选的精度，但是FLOAT（p）中的精度值仅用于确定存储大小，0到23之间的精度将产生一个4字节的单精度浮点列。从24到53的精度将产生一个8字节的双精度浮点列。由于浮点值是近似值，而不是存储为精确值，因此在比较中尝试将其视为精确值可能会导致问题。5.2MySQL的数据类型5.2.1数值类型位值类型位数据类型用于存储位值，BIT（M）允许存储一个长度为M的位值，M的范围从1到64。要指定位值，可以使用b'值'表示法。值是使用0和1组成的二进制值。例如，b'111'和b'10000000'分别表示7和128。如果给一个BIT（M）列赋值一个长度小于M的位值，则该值在左侧用零填充。例如，将b'101'赋值给类型为BIT（6）的列实际上与b'000101'相同。5.2MySQL的数据类型5.2.2字符串类型字符串数据类型包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET类型数据类型存储字节CHAR(M)M×

w字节，0<=

M

<=255，其中w是字符集中最大长度字符所需的字节数。BINARY(M)M字节，0

<=

M

<=255VARCHAR(M),

VARBINARY(M)如果列值需要0−255个字节，则L+1个字节，如果值可能需要超过255个字节，则L+2个字节TINYBLOB,

TINYTEXTL+1个字节，其中

L<2

8BLOB,

TEXTL+2个字节，其中

L<2

16MEDIUMBLOB,

MEDIUMTEXTL+3个字节，其中

L<2

24LONGBLOB,

LONGTEXTL+4个字节，其中

L<2

32ENUM('value1','value2',...)1或2个字节，取决于枚举值的数量（最多65,535个值）SET('value1','value2',...)1、2、3、4或8个字节，取决于集合成员的数量（最多64个成员）5.2MySQL的数据类型5.2.2字符串类型CHAR和VARCHAR类型使用CHAR和VARCHAR定义列时需要声明长度，该长度表示要存储的最大字符数。例如，CHAR（30）最多可以容纳30个字符。CHAR列的长度固定为声明的长度。长度可以是0到255之间的任意值。存储时，会使用空格右填充到指定的长度。VARCHAR列中的值是可变长度字符串。长度可以指定为0到65535之间的值。VARCHAR的有效最大长度取决于最大行大小（65535字节）和使用的字符集。VARCHAR值在存储时不进行填充，在存储和检索值时保留尾随空格。5.2MySQL的数据类型5.2.2字符串类型CHAR和VARCHAR类型VARCHAR值存储为1字节或2字节长度前缀加数据。长度前缀表示值中的字节数。如果值不超过255字节，则使用一个长度字节；如果值超过255字节，则使用两个长度字节。对于VARCHAR列，超出列长度的尾随空格在插入之前被截断并生成警告。对于CHAR列，都会从插入的值中截断多余的尾随空格。5.2MySQL的数据类型5.2.2字符串类型BINARY和VARBINARY类型BINARY和VARBINARY类型与CHAR和VARCHAR类似，只是它们存储字节字符串而不是字符串，这意味着它们具有二进制字符集和排序规则，比较和排序基于值中字节的数值。BINARY和VARBINARY的允许最大长度与CHAR和VARCHAR的相同，只是BINARY和VARBINARY的长度是以字节为单位而不是字符。存储BINARY值时，将使用0x00（零字节）将其值右填充到指定的长度，检索时，不会删除任何尾随字节。比较时0x00和空格是不同的，0x00排序在空格之前。对于VARBINARY，插入时不进行填充，检索时也不删除字节。5.2MySQL的数据类型5.2.2字符串类型BLOB和TEXT类型BLOB是一个二进制大对象，可以容纳可变数量的数据。有四种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，它们的区别仅在于它们可以保存的值的最大长度不同。有四种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT，它们对应于四种BLOB类型并且具有相同的最大长度和存储要求。TEXT值被视为非二进制字符串（字符串）。对于TEXT和BLOB列，插入时不进行填充，检索时也不删除字节。另外，BLOB和TEXT列不能有DEFAULT值。5.2MySQL的数据类型5.2.2字符串类型ENUM类型ENUM类型又称为枚举类型，在创建表时，使用ENUM('值1','值2',...,'值n')的形式定义一个列，该列的值只能取列表中的某一个元素。ENUM类型的取值列表中最多只能包含65535个值。例如，性别列的定义为：ssexENUM('男','女')NOTNULL，那么该列的值可以为'男'或者'女'。使用ENUM类型定义列时，会自动删除成员值中的尾随空格。如果将ENUM列声明为允许空值，则NULL值被认为是该列的有效值，该列的默认值为NULL。如果将ENUM列声明为不允许取空值（NOTNULL），则其默认值是列表中的第一个元素。5.2MySQL的数据类型5.2.2字符串类型SET类型在创建表时，使用SET('值1','值2',...,'值n')的形式定义的列的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的取值列表最多只能有64个元素。使用SET类型定义列时，会自动删除成员值中的尾随空格。插入记录时，SET列中的元素顺序无关紧要，存入MySQL数据库后，数据库系统会自动按照定义时的顺序显示。例如，使用SET('a','b')NOTNULL定义的列，其值可以为以下几种情况：'a'、'b'、'a,b'。5.2MySQL的数据类型5.2.3日期和时间类型日期和时间数据类型包括DATE、TIME、DATETIME、TIMESTAMP和YEAR类型数据类型存储字节格式取值范围YEAR1字节YYYY1901~2155DATE3字节'YYYY-MM-DD''1000-01-01'~'9999-12-31'TIME3字节+小数秒部分存储'hh:mm:ss''-838:59:59'~'838:59:59'DATETIME5字节+小数秒部分存储'YYYY-MM-DDhh:mm:ss''1000-01-0100:00:00'~'9999-12-3123:59:59'TIMESTAMP4字节+小数秒部分存储'YYYY-MM-DDhh:mm:ss''1970-01-0100:00:01'UTC~'2038-01-1903:14:07'UTC5.2MySQL的数据类型5.2.3日期和时间类型YEAR类型YEAR类型用于存储年份值，接受4位字符串、4位数字、1位或2位字符串、1位或2位数字等几种形式的输入值。如果输入值为4位字符串或4位数字，有效范围为'1901'~'2155'或1901~2155。如果输入值为1位或2位字符串，有效范围为'0'~'99'，MySQL将'0'~'69'转换为2000~2069，将'70'~'99'转换为1970~1999。如果输入值为1位或2位数字，有效范围为0~99，MySQL将0~69转换为2000~2069，将70~99转换为1970~1999。5.2MySQL的数据类型5.2.3日期和时间类型TIME类型TIME类型用于存储时间值，格式'hh:mm:ss'或'hhh:mm:ss'。其中的小时部分不仅可以表示一天中的某个时间（必须小于24小时），还可以表示经过的时间或两个事件之间的时间间隔（可能远远大于24小时，甚至为负值）。使用TIME类型定义列时如果指定小数位数，则存储小数秒部分需要额外的存储空间。TIME和TIME(0)等效，使用3个字节存储，TIME(1)和TIME(2)使用4个字节存储，TIME(3)和TIME(4)使用5个字节存储，TIME(5)和TIME(6)使用6个字节存储。5.2MySQL的数据类型5.2.3日期和时间类型DATE类型DATE类型用于包含日期部分但不包含时间部分的值。MySQL以'YYYY-MM-DD'格式检索并显示日期值，支持的范围为'1000-01-01'~'9999-12-31'。5.2MySQL的数据类型5.2.3日期和时间类型DATETIME和TIMESTAMP类型DATETIME类型和TIMESTAMP类型用于同时包含日期和时间部分的值。DATETIME类型支持的范围为'1000-01-0100:00:00'到'9999-12-3123:59:59'，TIMESTAMP类型的范围为'1970-01-0100:00:01'UTC到'2038-01-1903:14:07'UTC。MySQL将TIMESTAMP类型值从当前时区转换为UTC进行存储，并从UTC转换回当前时区进行检索。DATETIME或TIMESTAMP值可以包含精度高达微秒（6位）的尾部小数秒部分。DATETIME类型值的范围为'1000-01-0100:00:00.000000'到'9999-12-3123:59:59.999999'，TIMESTAMP类型值的范围为'1970-01-0100:00:01.000000'到'2038-01-1903:14:07.99999'。5.3创建表5.3.1使用CREATETABLE语句创建表语法格式：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]tbl_name(col_namedata_type[(n[,m])][NOTNULL|NULL][DEFAULT{literal|(expr)}][AUTO_INCREMENT][COMMENT'string'],...,[index_definition],[constraint_definition])[table_options]5.3创建表5.3.1使用CREATETABLE语句创建表说明：TEMPORARY：如果使用该关键字，则创建的表为临时表。临时表只在当前连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间。IFNOTEXISTS：该选项用于防止表存在时发生错误。tbl_name:要创建的表的名称，可以使用db_name.tbl_name的方式在指定的数据库中创建表。如果只有表的名称，则在当前数据库中创建表。col_name：列的名称。data_type：列的数据类型，参见5.2节中的内容。NOTNULL|NULL：指定该列是否允许空值，默认为NULL。5.3创建表5.3.1使用CREATETABLE语句创建表说明：DEFAULT{literal|(expr)}：指定列的默认值，默认值可以是常量，也可以是表达式。AUTO_INCREMENT：用于定义自增列，每个表只能有一个自增列，该列的数据类型必须是整型或浮点型，并且必须被索引，不能有默认值。自增序列从1开始。在自增列中插入NULL（建议）或0值时，该列的值将设置为当前列的最大值+1。COMMENT'string'：该选项指定列的注释，最长可达1024个字符。index_definition：定义索引。constraint_definition：定义约束。5.3创建表5.3.1使用CREATETABLE语句创建表说明：table_options：表的选项，常用的选项有：AUTO_INCREMENT[=]value：表的初始自动增量值；[DEFAULT]CHARACTERSET[=]charset_name：指定表的默认字符集；ENGINE[=]engine_name：指定表的存储引擎；ENCRYPTION[=]{'Y'|'N'}：启用或禁用InnoDB表的页面级数据加密。5.3创建表5.3.1使用CREATETABLE语句创建表【例5.1】在jwgl数据库中创建student表，表结构见表5-1。在MySQL命令行客户端输入命令（本例先不考虑约束）：USEjwgl;CREATETABLEstudent(snoCHAR(11)NOTNULL,snameVARCHAR(10)NOTNULL,ssexCHAR(1)NOTNULLDEFAULT'男',sbirthdayDATENOTNULL,snationVARCHAR(10)NOTNULL,mnoCHAR(4));5.3创建表5.3.1使用CREATETABLE语句创建表【例5.1】在jwgl数据库中创建student表，表结构见表5-1。5.3创建表5.3.1使用CREATETABLE语句创建表【例5.2】在jwgl数据库中创建course表，表结构：列名数据类型长度精度允许空约束说明cnochar3

否主键课程编号cnamevarchar10

否

课程名称ctypevarchar5

是

课程类型cclasshourtinyint

是

课时ccreditdecimal21是

学分ctermtinyint

是1~8开设学期5.3创建表5.3.1使用CREATETABLE语句创建表【例5.2】在jwgl数据库中创建course表：在MySQL命令行客户端输入命令（本例先不考虑约束）：CREATETABLEcourse(cnoCHAR(3)NOTNULL,cnameVARCHAR(10)NOTNULL,ctypeVARCHAR(5),cclasshourTINYINT,ccreditDECIMAL(2,1),ctermTINYINT);5.3创建表5.3.2通过复制创建表使用CREATETABLE…LIKE语句语法格式为：CREATETABLEnew_tblLIKEorig_tbl;说明：该语句可以将源表的结构复制到新表中，new_tbl为要创建的新表的名称，orig_tbl为源表的名称；新表包括源表中的列、列的属性、默认值、主键约束、唯一性约束、CHECK约束和索引的定义，外键约束定义不会复制到新表中；创建的新表为空表，不包含源表中的数据。5.3创建表5.3.2通过复制创建表使用CREATETABLE…LIKE语句【例5.3】在jwgl数据库中通过复制student表结构的方式创建表student_bak。在MySQL命令行客户端输入命令：CREATETABLEstudent_bakLIKEstudent;5.3创建表5.3.2通过复制创建表使用CREATETABLE…SELECT语句语法格式为：CREATETABLEnew_tbl[(column_definition,[index_definition],[constraint_definition])[table_options]][AS]SELECT*FROMorig_tbl;5.3创建表5.3.2通过复制创建表使用CREATETABLE…SELECT语句说明：该语句可以将源表的表结构和所有记录复制到新表中，但默认值、约束、索引不会复制；如果CREATETABLE语句中定义了列的信息，则在生成的表中SELECT语句产生的列附加在这些列之后；SELECT语句可以是任意合法的查询语句，MySQL将基于查询结果创建新表。5.3创建表5.3.2通过复制创建表使用CREATETABLE…SELECT语句【例5.4】在jwgl数据库中通过复制student表中“0101”专业的学生数据创建表student_computer。在MySQL命令行客户端输入命令：CREATETABLEstudent_computerASSELECT*FROMstudentwheremno='0101';5.4定义约束5.4.1数据完整性与约束数据完整性是指数据库中数据的正确性、有效性和一致性。数据完整性是为了防止错误信息的输入使得数据库中存储不符合语义规则的数据而造成的无效操作或者产生错误的输出，因此维护数据库的完整性是非常重要的。数据完整性一般包括实体完整性、参照完整性和用户定义的完整性。约束是实现数据完整性的一种重要手段。5.4定义约束5.4.1数据完整性与约束实体完整性实体完整性要求表中每一行数据必须有唯一标识，不能重复。实体完整性可以通过定义主键约束或唯一性约束加非空约束实现。例如，在jwgl数据库的student表中，将sno列定义为主键，sno列不能取空值并且每一个sno值都是唯一的，每一个sno值可以唯一地标识表中的一行数据，从而保证student表的实体完整性。score表中，将sno列和cno列的组合定义为主键，两列不能取空值且sno值和cno值的组合不允许重复，保证score表的实体完整性。5.4定义约束5.4.1数据完整性与约束参照完整性参照完整性用于保证参照表和被参照表之间的数据一致性，可以在被参照表中定义主键，参照表中定义外键，通过主键和外键之间的对应关系来实现参照完整性。参照完整性要求参照表中外键的值或者为空值或者必须是被参照表中主键的值。例如，将major表作为被参照表，表中的mno列定义为主键，student表作为参照表，表中的mno列定义为外键，major表和student表通过主键和外键之间的对应关系实现参照完整性，student表中mno列的值只能取major表中mno列的值，从而保证两个表之间的数据一致性。5.4定义约束参照完整性示例5.4定义约束5.4.1数据完整性与约束用户定义的完整性用户定义的完整性是指针对具体的应用，用户根据需要自己定义的数据必须满足的语义要求。用户定义的完整性可以通过定义默认值、非空约束和CHECK约束来实现。例如，对于student表中的ssex列，规定其取值只能是'男'或'女'，score表中的grade列，规定其取值在0到100之间。5.4定义约束5.4.2主键约束主键约束可以用来实现实体完整性。主键可以是表中的一列，也可以是多个列的组合。由多个列组成的主键称为复合主键。MySQL中主键必须遵守以下规则：一个表只能定义一个主键；主键列不能取空值，表中的两行在主键上不能具有相同的值，即主键值必须唯一，这就是唯一性原则；复合主键不能包含多余的列，如果从一个复合主键中删除一列后，剩余的列构成的主键仍满足唯一性原则，那么这个复合主键是不正确的，称为最小化原则。复合主键的列表中每个列名只能出现一次。5.4定义约束5.4.2主键约束创建表时定义主键约束(1)定义为列级完整性约束在CREATETABLE语句中，在要定义为主键的列的属性定义中加上PRIMARYKEY，语法格式为：col_namedata_type[(n[,m])][DEFAULT{literal|(expr)}]PRIMARYKEY5.4定义约束5.4.2主键约束创建表时定义主键约束【例5.5】创建student表，将sno列定义为主键，以列级完整性约束定义。在MySQL命令行客户端输入命令：CREATETABLEstudent(snoCHAR(11)PRIMARYKEY,snameVARCHAR(10)NOTNULL,ssexCHAR(1)NOTNULLDEFAULT'男',sbirthdayDATENOTNULL,snationVARCHAR(10)NOTNULL,mnoCHAR(4));5.4定义约束5.4.2主键约束创建表时定义主键约束(2)定义为表级完整性约束在CREATETABLE语句中，在表中所有列的定义之后，加上主键约束的定义，其语法格式为：[CONSTRAINTconstraint_name]PRIMARYKEY(col_name[,col_name…])5.4定义约束5.4.2主键约束创建表时定义主键约束【例5.6】创建dept表，将dno列定义为主键，以表级完整性约束定义。列名数据类型长度允许空值约束说明dnochar2否主键学院编号dnamevarchar20否唯一学院名称dlocVarchar20是

办公地址dphonechar8是

办公电话5.4定义约束5.4.2主键约束创建表时定义主键约束【例5.6】创建dept表，将dno列定义为主键，以表级完整性约束定义。在MySQL命令行客户端输入命令：CREATETABLEdept(dnoCHAR(2)NOTNULL,dnameVARCHAR(20)NOTNULL,dlocVARCHAR(20),dphoneCHAR(8),CONSTRAINTpk_dnoPRIMARYKEY(dno));5.4定义约束5.4.2主键约束创建表时定义主键约束【例5.7】创建score表，score表的结构如表5.7所示，sno列和cno列的组合为主键，以表级完整性约束定义。列名数据类型长度允许空约束说明snochar11否外键，参照student表的sno列(sno,cno)组合作为主键学生编号cnochar3否外键，参照course表的cno列(sno,cno)组合作为主键课程编号gradetinyint

是

成绩5.4定义约束5.4.2主键约束创建表时定义主键约束【例5.7】创建score表，score表的结构如表5.7所示，sno列和cno列的组合为主键，以表级完整性约束定义。在MySQL命令行客户端输入命令：CREATETABLEscore(snoCHAR(11),cnoCHAR(3),gradeTINYINT,CONSTRAINTpk_sno_cnoPRIMARYKEY(sno,cno));5.4定义约束5.4.2主键约束修改表时定义主键约束如果表已经存在并且需要创建主键约束，可以用ALTERTABLE语句对表结构进行修改增加主键约束，语法格式如下：ALTERTABLEtbl_nameADD[CONSTRAINTconstraint_name]PRIMARYKEY(col_name[,col_name…]);5.4定义约束5.4.2主键约束修改表时定义主键约束【例5.8】修改course表，将cno设置为主键。在MySQL命令行客户端输入命令：ALTERTABLEcourseADDCONSTRAINTpk_cnoPRIMARYKEY(cno);5.4定义约束5.4.3唯一性约束如果表中某一列或者一些列的组合不允许有重复值，可以定义唯一性约束。唯一性约束和主键约束的区别是：一个表只能定义一个主键约束，但是可以定义多个唯一性约束设置为主键的列不允许空值，即使列的定义中没有NOTNULL，而定义唯一性约束的列如果没有NOTNULL，则可以有空值5.4定义约束5.4.3唯一性约束创建表时定义唯一性约束(1)定义为列级完整性约束在CREATETABLE语句中，在要定义为主键的列的属性定义中加上UNIQUE，语法格式为：col_namedata_type[(n[,m])][NULL|NOTNULL][DEFAULT{literal|(expr)}]UNIQUE5.4定义约束5.4.3唯一性约束创建表时定义唯一性约束【例5.9】创建dept表，为dname列设置唯一性约束，以列级完整性约束定义。在MySQL命令行客户端输入命令：CREATETABLEdept(dnoCHAR(2)NOTNULL,dnameVARCHAR(20)NOTNULLUNIQUE,dlocVARCHAR(20),dphoneCHAR(8),CONSTRAINTpk_dnoPRIMARYKEY(dno));5.4定义约束5.4.3唯一性约束创建表时定义唯一性约束(2)定义为表级完整性约束在CREATETABLE语句中，在表中所有列的定义之后，加上唯一性约束的定义，其语法格式为：[CONSTRAINTconstraint_name]UNIQUE(col_name[,col_name…])5.4定义约束5.4.3唯一性约束创建表时定义唯一性约束【例5.10】创建dept表，为dname设置唯一性约束，以表级完整性约束定义。在MySQL命令行客户端输入命令：CREATETABLEdept(dnoCHAR(2)NOTNULL,dnameVARCHAR(20)NOTNULL,dlocVARCHAR(20),dphoneCHAR(8),CONSTRAINTpk_dnoPRIMARYKEY(dno),CONSTRAINTun_dnameUNIQUE(dname));5.4定义约束5.4.3唯一性约束修改表时定义唯一性约束如果表已经存在并且需要创建唯一性约束，可以用ALTERTABLE语句对表结构进行修改增加唯一性约束，语法格式如下：ALTERTABLEtbl_nameADD[CONSTRAINTconstraint_name]UNIQUE(col_name[,col_name…])5.4定义约束5.4.3唯一性约束修改表时定义唯一性约束【例5.11】修改course表，为cname设置唯一性约束。在MySQL命令行客户端输入命令：ALTERTABLEcourseADDCONSTRAINTun_cnameUNIQUE(cname);5.4定义约束5.4.4CHECK约束CHECK约束用于对某一列或者多个列的值设置检查条件，限制输入的数据必须满足检查条件。CHECK约束一般用于实现用户定义的完整性。5.4定义约束5.4.4CHECK约束创建表时定义CHECK约束(1)定义为列级完整性约束在CREATETABLE语句中，在要定义CHECK约束的列的属性定义中加上CHECK约束的定义，语法格式为：col_namedata_type[(n[,m])][NULL|NOTNULL][DEFAULT{literal|(expr)}]CHECK(expr)5.4定义约束5.4.4CHECK约束创建表时定义CHECK约束【例5.12】创建student表，定义检查约束限制ssex列的值只能取'男'或'女'，以列级完整性约束定义。在MySQL命令行客户端输入命令：CREATETABLEstudent(snoCHAR(11)PRIMARYKEY,snameVARCHAR(10)UNIQUE,ssexCHAR(1)DEFAULT'男'CHECK(ssex='男'orssex='女'),sbirthdayDATE,snationVARCHAR(10),mnoCHAR(4));5.4定义约束5.4.4CHECK约束创建表时定义CHECK约束(2)定义为表级完整性约束在CREATETABLE语句中，在表中所有列的定义之后，加上检查约束的定义，其语法格式为：[CONSTRAINTconstraint_name]CHECK(expr)5.4定义约束5.4.4CHECK约束创建表时定义CHECK约束【例5.13】创建student表，定义检查约束限制ssex列的值，以表级完整性约束定义。CREATETABLEstudent(snoCHAR(11)PRIMARYKEY,snameVARCHAR(10),ssexCHAR(1)DEFAULT'男',sbirthdayDATE,snationVARCHAR(10),mnoCHAR(4),CONSTRAINTch_ssexCHECK(ssex='男'orssex='女'));5.4定义约束5.4.4CHECK约束修改表时定义CHECK约束如果表已经存在并且需要创建CHECK约束，可以用ALTERTABLE语句对表结构进行修改增加CHECK约束，语法格式如下：ALTERTABLEtbl_nameADD[CONSTRAINTconstraint_name]CHECK(expr)【例5.14】修改course表，为cterm列设置检查约束限制输入的值必须在1到8之间。在MySQL命令行客户端输入命令：ALTERTABLEcourseADDCONSTRAINTch_ctermCHECK(cterm>=1andcterm<=8);5.4定义约束5.4.5外键约束外键约束在两个表的列之间建立参照关系，用来实现参照完整性。一个表可以有一个或多个外键，外键可以是一列也可以是多列，外键的值可以为空值，如果不为空则必须是它所参照的另一个表的主键的一个值。外键约束通常涉及到两个表，根据表之间的参照关系分为：主表（父表）：被参照表，在两个表的参照关系中主键所在的表从表（子表）：参照表，在两个表的参照关系中外键所在的表5.4定义约束5.4.5外键约束创建表时定义外键约束在CREATETABLE语句中定义外键约束，其语法格式为：[CONSTRAINT[constraint_name]]FOREIGNKEY(col_name,...)REFERENCEStbl_name(col_name,...)[ONDELETERESTRICT|CASCADE|SETNULL|NOACTION][ONUPDATERESTRICT|CASCADE|SETNULL|NOACTION]5.4定义约束5.4.5外键约束【例5.15】创建score表，将sno列设置为外键，参照student表的sno列。CREATETABLEscore(snochar(11)notnull,cnochar(3)notnull,gradetinyint,CONSTRAINTpk_sno_cnoPRIMARYKEY(sno,cno),CONSTRAINTfk_snoFOREIGNKEY(sno)REFERENCESstudent(sno));5.4定义约束5.4.5外键约束修改表时定义外键约束如果表已经存在并且需要创建外键约束，可以用ALTERTABLE语句对表结构进行修改增加外键约束，语法格式如下：ALTERTABLEtbl_nameADD[CONSTRAINT[constraint_name]]FOREIGNKEY(col_name,...)REFERENCEStbl_name(col_name,...)[ONDELETERESTRICT|CASCADE|SETNULL|NOACTION][ONUPDATERESTRICT|CASCADE|SETNULL|NOACTION]5.4定义约束5.4.5外键约束【例5.16】修改score表，将cno列设置为外键参照course表的cno列。在MySQL命令行客户端输入命令：ALTERTABLEscoreADDCONSTRAINTfk_cnoFOREIGNKEY(cno)REFERENCEScourse(cno);5.

5使用Workbench创建表列名数据类型长度允许空约束说明mnochar4否主键专业编号mnamevarchar20否唯一专业名称dnochar2是外键，参照dept表的dno列所属学院编号专业表major5.

5使用Workbench创建表打开WorkBench工具，连接到MySQL服务器。在左侧的“SCHEMAS”导航栏中找到jwgl数据库，单击左边的箭头，在下面的“Tables”节点上单击鼠标右键，在出现的菜单中选择“CreateTable…”，右侧将打开“new_table-Table”对话框的“Columns”选项卡。5.

5使用Workbench创建表5.

5使用Workbench创建表在TableName文本框中输入要创建的表名major，在Charset/Collation、Engine下拉列表中选择表的默认字符集、字符集的排序规则、存储引擎，这里采用默认值。依次输入每个列的名称、数据类型、长度；mno列为主键，不允许空值，勾选该列后的PK和NN复选框；mname列不允许空值，要求唯一，勾选该列后的NN和UQ复选框。5.

5使用Workbench创建表5.

5使用Workbench创建表选择“ForeignKeys”选项卡，在左侧列表框的“ForeignKeyName”处输入外键约束的名称“fk_dno”，“ReferencedTable”下拉列表中选择dept表。右侧列表框中的“Column”处出现major表中的列，勾选dno列，在对应的“ReferencedColumn”处选择dept表中的dno列。单击“Apply”按钮，完成表的创建。5.

5使用Workbench创建表5.6查看表5.6.1查看当前数据库中的表可以使用SHOWTABLES命令查看当前数据库中已有的表。【例5.17】查看jwgl数据库中已有的表。在MySQL命令行客户端输入命令：USEjwglSHOWTABLES;5.6查看表5.6.2查看表结构在MySQL中可以使用DESCRIBE语句或SHOWCOLUMNS|FIELDS语句查看某个表的基本结构，包括列名、数据类型、键、是否允许空值、默认值等信息。5.6查看表5.6.2查看表结构DESCRIBE语句语法格式：DESC[RIBE][db_name.]tbl_name【例5.18】使用DESCRIBE语句查看jwgl数据库中student表的结构。在MySQL命令行客户端输入命令：DESCstudent;5.6查看表5.6.2查看表结构SHOWCOLUMNS|FIELDS语句语法格式：SHOW{COLUMNS|FIELDS}{FROM|IN}tbl_name[{FROM|IN}db_name]【例5.19】使用SHOWCOLUMNS|FIELDS语句查看jwgl数据库中course表的结构。在MySQL命令行客户端输入命令：SHOWCOLUMNSFROMcourse;5.6查看表5.6.3查看表的定义语句SHOWCREATETABLE语句可以用来查看创建表的CREATETABLE语句，其语法格式为：SHOWCREATETABLEtbl_name【例5.20】使用SHOWCREATETABLE语句查看student表的定义。在MySQL命令行客户端输入命令：SHOWCREATETABLEstudent\G5.6查看表5.6.3查看表的定义语句5.7修改表MySQL中可以使用ALTERTABLE语句修改表，包括：列的修改约束的修改修改表名和表的选项。5.7修改表5.7.1列的添加、修改与删除1．添加列MySQL中添加列的语句格式如下：ALTERTABLEtbl_nameADD[COLUMN]col_namecolumn_definition[FIRST|AFTERcol_name]5.7修改表5.7.1列的添加、修改与删除【例5.21】在student表中添加学生电话stelephone字段，数据类型为VARCHAR，长度8，允许空值。在MySQL命令行客户端输入命令：ALTERTABLEstudentADDstelephoneVARCHAR(8);5.7修改表5.7.1列的添加、修改与删除2．修改列的定义MySQL中修改列名的语句格式如下：ALTERTABLEtbl_nameMODIFY[COLUMN]col_namecolumn_definition[FIRST|AFTERcol_name]5.7修改表5.7.1列的添加、修改与删除【例5.22】将student表中的stelephone字段的数据类型改为CHAR，长度8。在MySQL命令行客户端输入命令：ALTERTABLEstudentMODIFYstelephoneCHAR(8);5.7修改表5.7.1列的添加、修改与删除3．修改列名MySQL中修改列名的语句格式如下：ALTERTABLEtbl_nameRENAMECOLUMNold_col_nameTOnew_col_name5.7修改表5.7.1列的添加、修改与删除【例5.23】将student表中的stelephone字段的名称改为sphone在MySQL命令行客户端输入命令：ALTERTABLEstudentRENAMECOLUMNstelephoneTOsphone;5.7修改表5.7.1列的添加、修改与删除4．修改列名和列定义MySQL中可以使用CHANGECOLUMN修改列名和列的定义，语句格式如下：ALTERTABLEtbl_nameCHANGE[COLUMN]old_col_namenew_col_namecolumn_definition[FIRST|AFTERcol_name]5.7修改表5.7.1列的添加、修改与删除【例5.24】将student表中的sphone字段的名称改为smobilephone，数据类型为VARCHAR，长度11，允许空值。在MySQL命令行客户端输入命令：ALTERTABLEstudentCHANGEsphonesmobilephoneVARCHAR(11);5.7修改表5.7.1列的添加、修改与删除5．修改列的默认值MySQL中可以使用ALTERCOLUMN修改列的默认值、是否可见，语句格式如下：ALTERTABLEtbl_nameALTER[COLUMN]col_name{SETDEFAULT{literal|(expr)}|SET{VISIBLE|INVISIBLE}|DROPDEFAULT5.7修改表5.7.1列的添加、修改与删除【【例5.25】修改student表，为snation列设置默认值'汉族'。在MySQL命令行客户端输入命令：ALTERTABLEstudentALTERCOLUMNsnationSETDEFAULT'汉族';5.7修改表5.7.1列的添加、修改与删除CHANGE、MODIFY、RENAMECOLUMN和ALTER子句允许对表中现有列进行更改，它们的区别为：MODIFY[COLUMN]子句只能改列的定义，不能改列的名称；RENAMECOLUMN子句只能改列的名称，不能改列的定义；MODIFY[COLUMN]子句可以重命名列，也可以更改列定义，一般用于同时更改列名和列定义。如果只改列名，使用RENAMECOLUMN子句更简单。如果只改列的定义，使用MODIFY[COLUMN]子句更方便。ALTER[COLUMN]子句仅用于更改列的默认值和可见性。5.7修改表5.7.1列的添加、修改与删除6．删除列MySQL中删除列的语句格式如下：ALTERTABLEtbl_nameDROP[COLUMN]col_name【例5.26】修改student表，删除smobilephone列。在MySQL命令行客户端输入命令：ALTERTABLEstudentDROPCOLUMNsmobilephone;5.7修改表5.7.2约束的添加与删除1．删除主键约束MySQL中可以通过下面的语句删除主键：ALTERTABLEtbl_nameDROPPRIMARYKEY需要说明的是，如果要删除的主键被其他表中的外键引用，会删除失败，如下例所示。【例5.27】使用DROPPRIMARYKEY删除student表的主键。在MySQL命令行客户端输入命令：ALTERTABLEstudentDROPPRIMARYKEY;5.7修改表5.7.2约束的添加与删除2．删除唯一性约束MySQL中唯一性