MySQL-8-表结构设计课件

MySQL8表结构设计数据类型选择分析：【网上商城表实例】数据类型选择分析：【网上商城表实例】【例4.2】网上商城数据库（emarket）包含商品分类表（category）、供货商表（supplier）、商品表（commodity）、用户表（user）、订单表（orders）和订单项表（orderitems）。为了方便学习SQL命令，分清语句关键字、列名、数据库名、表名等层次。网上商城数据库（emarket）和表名用英文，而表列名用中文表示。而后面各种MySQL应用开发使用的数据库中表列名用英文，从而与实际开发的情形相似。1.商品分类表（category）商品分类表（category）的各列及数据类型如表4.1所示。列

名类

型长

度允许空值说

明类别编号char2否主键类别名称varchar8否

数据类型选择分析：【网上商城表实例】2.供货商表（supplier）供货商表（supplier）的各列及数据类型如表4.2所示。列

名类

型长

度允许空值说

明供货商编号char2否主键供货商名称varchar16否

3.商品表（commodity）商品表（commodity）的各列及数据类型如表4.3所示。列

名类

型长

度允许空值说

明商品编号char6否主键商品名称varchar32否

价格decimal7（2位小数）否

库存量smallint3否默认0，非负值商品图片blob默认是

数据类型选择分析：【网上商城表实例】4.用户表（user）用户表（user）的各列及数据类型如表4.4所示。列

名类

型长

度允许空值说

明帐户名varchar16否主键，唯一,充当帐号功能姓名char4否

性别enum默认否默认“男”密码varchar12否含字母数字，长度大于8手机号char11否唯一，可作为登录名身份证号char18否唯一，符合身份证编码规则有效期date默认否2099.12.30作为长期常用地址json默认是

职业enum默认是选择常用的大类关注set默认是商品分类作为关注内容投递位置point默认是特定位置或者某区域用户公共位置数据类型选择分析：【网上商城表实例】5.订单表（orders）订单表（orders）的各列及数据类型如表4.5所示。列

名类

型长

度允许空值说

明订单编号int6否主键，自动递增帐户名varchar16否

支付金额decimal8（2位小数）否

下单时间datetime默认否

数据类型选择分析：【网上商城表实例】6.订单项表（orderitems）订单项表（orderitems）各列及数据类型如表4.6所示。列

名类

型长

度允许空值说

明订单编号int6否主键商品编号char6否主键订货数量int4否值为非负数发货否bit1否0未发货，1已发货，默认0创建表结构：【网上商城表结构实例】1.创建表结构表由表结构和表记录组成。在创建表结构后才能操作表记录。创建表结构可以采用CREATETABLE语句或者Navicat等工具软件。（1）使用CREATETABLE语句创建表结构CREATETABLE表名(

列定义, ... [表键] [表约束])[表选项]列定义::=列名列数据类型[长度和小数][空值][虚拟][键][注释][默认值][其他属性]创建表结构：【网上商城表结构实例】（2）Navicat工具创建表结构【例4.3】采用Navicat创建emarket数据库中商品分类表（category）表结构。在创建的连接下双击emarket数据库，再选择“表”，按右键，在快捷菜单中单击“新建表”，出现创建表窗口，输入内容如图4.1所示。创建表结构：【网上商城表结构实例】（3）显示表属性在表创建后，可以通过下面语句显示指定表的创建属性：SHOWCREATETABLE表名通过下面语句显示表属性或者表指定列的属性：DESC表名[列名]【例4.3续】显示category表属性。USEemarket;SHOWCREATETABLEcategory; #（a）DESCcategory; #（b）DESCcategory类别编号; #（c）显示category表“类别编号”列属性显示结果分别如图4.2（a）、（b）、（c）所示。

创建表结构：【网上商城表结构实例】2.删除表表结构创建后，可以修改表结构。有了表结构，就可以增加、修改和删除表记录。表记录的操作将在下一章系统介绍。下列语句可以删除表，删除表后表结构和表记录均不存在，一般用于重新定义表。DROPTABLEIFEXISTS表名;01列及其常用属性1.列名2.列数据类型3.长度和小数4.空值限制：NOTNULL/NULL5.虚拟6.键7.注释：COMMENT列及其常用属性1.列名列又称字段，列名又称字段名。列名必须符合标识符规则，中英文均可，长度不能超过64个字符，而且在表中要唯一。如果采用MySQL保留字必须用单引号括起来。列名一般可以采用英文、汉语拼音、中文等，采用中文作为列名，阅读方便；但从编程角度，英文、汉语拼音输入容易些。本书前面介绍MySQL基础知识采用中文作为列名，后面实习系统开发时则采用英文列名。列及其常用属性2.列数据类型（1）整数类型和浮点类型如果不需要小数，就使用整数类型；如果需要表示小数部分，就使用浮点类型。对于浮点类型列，存入的数值超过指定小数位会进行四舍五入。例如，假设列值的范围为1～99999，若使用整数，则使用无符号mediumint类型；若需要存储小数，则使用浮点类型。例如，“库存量”肯定为整数，不能使用浮点类型；如果其值不超过xxx，可以用smallint，就不要用int，定义为：库存量 smallint（2）单精度类型和双精度类型浮点类型又包括了单精度（float）和双精度（double）类型。double类型的精度（16位有效数字）比float类型（6位有效数字）高，因此要求存储精度较高时应选择double类型。列及其常用属性（3）浮点数和定点数浮点数float、double相对于定点数decimal的优势是：在长度一定的情况下，浮点数能表示更大的数据范围。由于浮点数容易产生误差，因此对精确度要求比较高时，建议使用decimal来存储。decimal是以字符串存储的，用于定义货币等对精确度要求较高的数据。在数据迁移中，float(m,d)是非标准SQL定义，数据库迁移可能会出现问题，最好不要这样使用。另外，两个浮点数进行减法和比较运算时也容易出问题，因此在进行计算的时候一定要小心。进行数值比较时，最好使用decimal类型。（4）日期与时间类型MySQL对于不同种类的日期和时间有很多数据类型，比如year和time。如果只需要记录年份，使用year类型即可；如果只记录时间，使用time类型；如果同时需要记录日期和时间，则可以使用timestamip或datetime类型。列及其常用属性（5）char与varchar之间的特点与选择char是固定长度字符，varchar是可变长度字符。char会自动删除插入数据的尾部空格，varchar不会删除尾部空格。char是固定长度，所以它的处理速度比varchar要快，但缺点是浪费存储空间，所以对存储不大但在速度上有要求的要使用char类型，反之使用varchar类型。存储引擎对于选择char和varchar的影响：对于MyISAM引擎，最好使用char数据列，这样可使整个表静态化，从而使数据检索更快，用空间换时间。对于InnoDB引擎，最好用varchar，因为InnoDB数据表的存储格式不分固定和可变长度，使用char不一定比varchar更好，而且varchar是按照实际长度存储的，比较节省空间，所以对磁盘I/O和数据存储总量比较好。例如，“姓名”虽然有2、3、4个字符的可能性，但以3个字符居多，且仅差1个字符，所以选char(4)，定义为：姓名 char(4)列及其常用属性（6）blob类型和text类型blob是二进制字符串，text是非二进制字符串，两者均可存放大容量的信息。blob主要存储图片、音频等，而text只能存储纯文本文件。例如：商品图片 blob（7）字符类型（char）和整数类型实际应用中，像“商品编号”、“学号”等虽然均由数字组成，但不同位置的数字代表不同含义，有时还需要通过部分数字进行查询。如果采用整数类型，首先是首位0不能自然显示，其次是部分数字进行查询的条件表达式会比较复杂，所以这类列应该采用字符类型。例如：商品编号 char(6)列及其常用属性（8）字符类型（char）和枚举类型（enum）例如，“职业”列可以选择char(3)直接存放“学生”、“职工”、“教师”、“医生”、“军人”、“公务员”和“其他”等字符串，但要需要控制在该列不能存放此外的其他字符串，否则查询时会漏掉。也可以用char(1)存放1、2、3、4、5、6、7代号对应以上字符串，但直接查询表内容时显示又不直观。如果需要直观显示，需要进行变换，同时也要控制输入时不能存放其他代号。这种情况最佳的选择是使用enum（枚举类型），其中存放的是枚举序号，显示的则是字符串，直观又方便。例如：职业 enum('学生','职工','教师','医生','军人','公务员','其他')列及其常用属性（9）字符类型（char）和集合类型（set）例如：“关注”列如果选择char类型，其他存储长度需要考虑可能关注所有内容。查找关注内容记录，需要在列的整个长度匹配，所以存储空间大，处理效率低。这种情况最佳的选择是使用集合类型（set），存储的是表结构定义列时对应的二进制二进制位，查找时匹配一个或者一个以上的位信息，输出时又可直观显示。例如：关注 set('水果','肉禽','海鲜水产','粮油蛋')列及其常用属性（10）字符类型（char）和json类型例如，“常用地址”可以存放x省x市x区x路x小区x幢x号（还有许多不限于这种格式如农村、境外地址），另外还可附带联系人、电话等信息。如果只考虑存储和原样显示，那选择varchar(32)直接输入即可。但如果需要从中按部分字段内容进行查询，使用json是更好的选择，因为其存放的内容已经通过各种键名标签进行了规范，定义为：常用地址 json列及其常用属性3.长度和小数（1）字符类型（char和varchar）长度为本列最大存放的字符个数，一个英文和一个中文均算作1个字符，例如：存放“abc中文系统”为7个字符。默认长度为255。列占用的空间大小与列采用的字符集有关。如果采用gbk字符集，“abc中文系统”占用11个字节，因为gbk字符集英文占用1个字节，汉字占用2个字节；如果采用utf8mb4字符集，每一个字符占用4个字节，这样“abc中文系统”就占用28个字节。（2）浮点数类型（float、double）如果不指定长度和小数位，默认为整数，在最大数据范围内，数据超过有效数字位数的部分为0；如果指定长度和小数位，只要在数据类型允许的范围内，整数部分显示的位数=长度-小数位数-1。例如，价格如果在xxxx.xx范围，该列可以采用单精度浮点类型float，因为它的有效位数6位。价格 float(7.2)列及其常用属性（3）定点数类型（decimal）用于保存准确数字数据，长度和小数位可以根据用户需要指定。例如：支付金额可能达到xxxxx.xx，数据不大，但有效位数超过6位，为了准确表达，可以采用定点数类型decimal。支付金额 decimal(8.2)列及其常用属性4.空值限制：NOTNULL/NULLNOTNULL表示列内容不允许为空，NULL或者不写此项表示允许为空。例如，“商品名称”列不能为空：商品名称 varchar(32) NOTNULL又例如，商品图片可以暂时没有，所以该列可以为空：商品图片 blob NULL5.虚拟Navicat中勾选该项，表示本列在表中并不直接存放内容，而是由其他列生成。列及其常用属性6.键列的键包括主键（PRIMARYKEY）和唯一键（UNIQUE[KEY]）。内容在列约束中详细介绍。7.注释：COMMENT注释仅仅是为了此后自己或者别人显示表结构时方便了解该列作用的说明内容，最多1024个字符。使用SHOWCREATETABLE或SHOWFULLCOLUMNS语句显示表属性时可看到列注释内容。例如：商品图片 blob COMMENT'图片不能大于64KB'02列

约

束1.主键约束：PRIMARYKEY2.唯一键约束：UNIQUE[KEY]3.完整性约束：CHECK带子网划分的编址Network172.16.0.0172.16.1.0172.16.2.0172.16.3.0172.16.4.0子网划分(没有划分子网时）172.16.2.200172.16.2.2172.16.2.160172.16.3.5172.16.3.100172.16.3.150172.16NetworkNetworkInterface172.16.0.0E1NewRoutingTable2160Host..E1172.16.3.1子网掩码子网掩码与IP地址一一对应，是32bit的二进制数子网掩码为连续的1和0子网掩码为1表示网络位，0表示主机位缩写“/24”表示掩码中有连续的24个1。子网掩码与IP地址一一对应，是32bit的二进制数子网掩码为1表示网络位，0表示主机位1721600255255002552552550IP

AddressDefault

Subnet

Mask8-bit

Subnet

MaskNetworkHostNetworkHostNetworkSubnetHost缩写“/16”表示掩码中有连续的16个1。11111111111111110000000000000000子网掩码1 0 0 0 0 0 0 0 = 1281 1 0 0 0 0 0 0 = 1921 1 1 0 0 0 0 0 = 2241 1 1 1 0 0 0 0 = 2401 1 1 1 1 0 0 0 = 2481 1 1 1 1 1 0 0 = 2521 1 1 1 1 1 1 0 = 2541 1 1 1 1 1 1 1 = 255128 64 32 16 8 4 2 1子网掩码目录

一、子网掩码

二、IP地址计算没有子网划分的子网掩码16NetworkHost172001010110011111111101011000001000011111111000100000000000000000000101000000000000000000000默认情况下没有划分子网00000010172.16.2.160255.255.0.0NetworkNumber带子网划分的子网掩码网络位增加8位16NetworkHost172.16.2.160255.255.255.017220101011001111111110101100000100001111111100010000111111110000001010100000000000000000000000000010Subnet128192224240248252254255NetworkNumber16172216010101100000100001010000000000010HostMaskSubnetBroadcastLastFirst172.16.2.160255.255.255.1921地址计算示例1010110011111111000100001111111111111111101000001100000000000010HostMaskSubnetBroadcastLastFirst172.16.2.160255.255.255.19212161722160地址计算示例1010110011111111000100001111111111111111101000001100000000000010HostMaskSubnetBroadcastLastFirst172.16.2.160255.255.255.192123161722160地址计算示例101011001111111100010000111111111111111110100000110000001000000000000010HostMaskSubnetBroadcastLastFirst172.16.2.160255.255.255.1921234161722160地址计算示例10101100111111110001000011111111111111111010000011000000100000000000001010111111HostMaskSubnetBroadcastLastFirst172.16.2.160255.255.255.19212345161722160地址计算示例1010110011111111000100001111111111111111101000001100000010000000000000101011111110000001HostMaskSubnetBroadcastLastFirst172.16.2.160255.255.255.192123456161722160地址计算示例101011001111111100010000111111111111111110100000110000001000000000000010101111111000000110111110HostMaskSubnetBroadcastLastFirst172.16.2.160255.255.255.1921234567161722160地址计算示例101011001111111110101100000100001111111100010000111111110000001010100000110000001000000000000010101011000001000000000010101111111010110000010000000000101000000110101100000100000000001010111110HostMaskSubnetBroadcastLastFirst172.16.2.160255.255.255.19212345678161722160地址计算示例101011001111111110101100000100001111111100010000111111110000001010100000110000001000000000000010101011000001000000000010101111111010110000010000000000101000000110101100000100000000001010111110HostMaskSubnetBroadcastLastFirst172.16.2.160255.255.255.192172.16.2.128172.16.2.191172.16.2.129172.16.2.190123456789161722160地址计算示例子网掩码练习AddressSubnetMaskClassSubnet172.16.2.1010.6.24.2010.30.36.12255.255.255.0255.255.240.0255.255.255.0AddressSubnetMaskClassSubnet172.16.2.1010.6.24.2010.30.36.12255.255.255.0255.255.240.0255.255.255.0BAA172.16.2.010.6.16.010.30.36.0子网掩码练习计算广播地址练习AddressClassSubnetBroadcast201.222.10.60255.255.255.248SubnetMask15.16.193.6255.255.248.0128.16.32.13255.255.255.252153.50.6.27255.255.255.128153.50.6.127AddressClassSubnetBroadcast201.222.10.60

255.255.255.248C201.222.10.63201.222.10.56SubnetMask15.16.193.6255.255.248.0A15.16.199.25515.16.192.0128.16.32.13

255.255.255.252B128.16.32.15128.16.32.12153.50.6.27

255.255.255.128B153.50.6.0计算广播地址练习列

约

束1.主键约束：PRIMARYKEY选择PRIMARYKEY即该列作为主键。主键列不允许为空（NOTNULL），并且在本表中必须唯一。一个表中主键约束只能一个，而且主键列约束就是表的主键约束。主键约束（PRIMARYKEY）以一列和一列以上（复合主键约束）。当CREATETABLE命令方式创建表的列约束方式创建主键约束只能为一列。【例4.4】创建emarket数据库供货商（supplier）表结构。USEemarket;CREATETABLEsupplier(

供货商编号 char(2) NOTNULLPRIMARYKEY,

供货商名称 varchar(16) NOTNULL);列

约

束2.唯一键约束：UNIQUE[KEY]选择UNIQUE[KEY]指明该列在本表中也必须唯一，但可以包含一个NULL值。在一个表中可以包含多个唯一键约束。例如：手机号 char(11) NOTNULL UNIQUEKEY,微信 varchar(20) NULL UNIQUE3.完整性约束：CHECK对于不同类型的列均可以通过CHECK约束来实现：列名数据类型...[CONSTRAINT约束名]CHECK(约束条件)其中：约束条件是包含当前列名在内的条件表达式。不同的列可以定义不同的约束条件，可以通过约束名来区分，也可以通过约束名删除约束。例如，评估一个星对应1分，最大分值为5分，没有评估为0：评估分 tinyint UNSIGNEDDEFAULT0CHECK(评估分<=5)03列默认值1.显式常量默认值2.显式表达式默认值3.隐式默认值列默认值1.显式常量默认值显式常量默认值是由用户指定的常量，如果出现与定义声明的列数据类型不完全匹配的情形，MySQL能自动根据通常的类型转换规则隐式强制转换为用户设计表时所声明的类型。【例4.5】创建临时表，包含3个不同数据类型的列，均以DEFAULT指定常量默认值。USEmydb;CREATETABLEIFNOTEXISTStab_default1(

商品名称 varchar(32) DEFAULT'水果',

价格 float(6,2) NOTNULLDEFAULT0,

库存量 smallint(3) DEFAULT0);DESCtab_default1'价格'; #（a）INSERTINTOtab_default1VALUES();SELECT*FROMtab_default1; #（b）列默认值2.显式表达式默认值除了常量，DEFAULT指定的默认值也可以是表达式。通常将表达式默认值括在括号内，以便与常量默认值区分开来。【例4.6】创建临时表，表达式作为列默认值。USEmydb;DROPTABLEIFEXISTStab_default2;CREATETABLEtab_default2( myID binary(16) DEFAULT(UUID_TO_BIN(UUID())), myDate date DEFAULT(CURRENT_DATE+INTERVAL1YEAR), myNote varchar(12));INSERTINTOtab_default2VALUES();INSERTINTOtab_default2(myID,myDate,myNote)VALUES(DEFAULT,DEFAULT,'ABC');SELECTBIN_TO_UUID(myID),myDate,myNoteFROMtab_default2;列默认值3.隐式默认值如果不包含显式默认值，MySQL将按如下方式来确定默认值。（1）如果该列可以接受NULL作为值，则使用显式的DEFAULTNULL子句定义该列。（2）如果该列不能接受NULL作为值，MySQL将定义没有显式默认子句的列。例如：如果列被定义为主键的一部分，但没有显式地定义为NOTNULL，将它创建为一个NOTNULL列，因为主键列必须是NOTNULL。（3）对于没有显式默认子句的NOTNULL列的数据输入，如果INSERT或REPLACE语句不包含该列的值，或者UPDATE语句将该列设置为NULL，MySQL将根据当时有效的SQL模式处理该列：如果启用了严格模式，则事务表将出现错误，语句将回滚；对于非事务性表，也会出现错误，但是如果这种情况发生在多行语句的第二行或后续行，则会插入前面的行。如果未启用严格模式，MySQL将列设置为列数据类型的默认值。（4）对于数值类型，默认值是0，但是对于使用AUTO_INCREMENT属性声明的整数或浮点类型，默认值是序列中的下一个值。（5）对于时间戳以外的日期和时间类型，默认值是该类型的适当“零”值，例如datetime类型的“零”值为“0000-00-0000:00:00”。（6）对于枚举以外的字符串类型，默认值是空字符串。对于枚举类型，默认值是第一个枚举值。04数值类型其他列属性1.无符号：UNSIGNED2.默认值：DEFAULT值3.填充零：ZEROFILL4.自动递增：AUTO_INCREMENT数值类型其他列属性1.无符号：UNSIGNED指定该列整数为“无符号”即不允许负值，否则系统错误。例如：库存量 smallint UNSIGNEDNOTNULL2.默认值：DEFAULT值列指定DEFAULT，当增加一行时，只要该列不指定值就采用指定的默认值作为列值。应该采用该列的常见初始值作为默认值。默认值必须为一个常数。例如，库存量默认值为0：库存量 smallint UNSIGNEDNOTNULLDEFAULT0数值类型其他列属性3.填充零：ZEROFILL当插入的值长度小于类型设定的长度时，剩余部分用0填补。4.自动递增：AUTO_INCREMENT例如，订单编号采用系统自增属性：订单编号 int NOTNULLAUTO_INCREMENT（1）标注AUTO_INCREMENT为自增列，当向表插入行记录时，默认情况下列值从1开始，按照自然数自动增1。（2）每个表只能有一个AUTO_INCREMENT列，并且必须被索引，不能有默认值。（3）插入记录时，AUTO_INCREMENT列可以指定为NULL或者0，或者不指定该列，内容实际填自增值。如果启用了下列模式：SETSQL_MODE='NO_AUTO_VALUE_ON_ZERO';数值类型其他列属性（4）要在插入行之后检索AUTO_INCREMENT值，可以使用LAST_INSERT_ID()。（5）插入记录时，可以指定AUTO_INCREMENT列的值，后面插入记录时如果指定NULL值或者不指定该列，内容实际填在此基础上自增。（6）通过下列命令可以修改此后行记录AUTO_INCREMENT列起始值：ALTERTABLE表名AUTO_INCREMENT=值【例4.7】创建emarket数据库订单表（orders），其中“订单编号”列为AUTO_INCREMENT列。USEemarket;CREATETABLEorders(

订单编号 int NOTNULLPRIMARYKEYAUTO_INCREMENT,#（a）

帐户名 varchar(16) NOTNULL,

支付金额 decimal(8,2) NOTNULL,

下单时间 datetime NOTNULL #（b）);05字符类型其他列属性1.默认值：DEFAULT值2.字符集：CHARACTERSET字符集名3.排序规则：COLLATE排序规则名4.键长度：LEFT(列名,长度)字符类型其他列属性1.默认值：DEFAULT值列指定默认值，默认值必须为一个字符串常数。例如：’ABC’。2.字符集：CHARACTERSET字符集名指定列的字符集。3.排序规则：COLLATE排序规则名指定列的字符集对应的排序规则。4.键长度：LEFT(列名,长度)如果选择指定为主键，默认情况下为该列的所有内容，但如果指定键长度，则将该列的前面键长度指定的内容作为主键。06虚拟列和虚拟列1.生成列（虚拟列）2.列数据类型自动变化虚拟列和虚拟列1.生成列（虚拟列）CREATETABLE语句支持生成列，这种列的值是从列定义中包含的表达式计算而来，有时称为虚拟列。列名数据类型[GENERATEDALWAYS]AS(表达式) [VIRTUAL|STORED][属性][列约束]关于生成列的使用还要注意以下几点：（1）生成列的定义也可以引用前面已经定义的生成列。（2）如果表达式计算的数据类型与声明的列类型不同，则根据通常的MySQL类型转换规则隐式强制转换为声明的类型。（3）外键约束不能引用生成列。（4）如果显式地对生成列执行插入、替换或更新，则唯一允许的值是默认值（DEFAULT）。（5）有些系统函数不能包含在生成表达式中。例如：表中包含身份证列，定义一个生成列年龄，通过CURDATE()函数得到当前日期与身份证中的出生日期相减+1得到年龄列值是不可以的。因为CURDATE()不是确定的值。虚拟列和虚拟列【例4.8】创建emarket数据库商品表（commodity）结构，总价和商品列作为生成列。1）创建表。USEemarket;CREATETABLEcommodity(

商品编号 char(6) NOTNULLPRIMARYKEY,

商品名称 varchar(32) NOTNULL,

价格 decimal(6,2) NOTNULL,

库存量 smallint(3) UNSIGNEDDEFAULT0,

商品图片 blob NULL,

总价 decimal(10,2) AS(价格*库存量), #（a）

商品 varchar(100) AS(CONCAT(商品编号,'',商品名称))#（b）);虚拟列和虚拟列2）向commodity表插入3条测试记录。USEemarket;INSERTINTOcommodity(商品编号,商品名称,价格,库存量) VALUES('1A0101','洛川红富士苹果冰糖心10斤箱装',44.80,3601);INSERTINTOcommodity(商品编号,商品名称,价格,库存量) VALUES('1A0201','烟台红富士苹果10斤箱装',29.80,5698);INSERTINTOcommodity(商品编号,商品名称,价格,库存量) VALUES('1A0302','阿克苏苹果冰糖心5斤箱装',29.80,12680);3）SELECT查询商品和总价信息。SELECT商品,总价FROMcommodity;虚拟列和虚拟列2.列数据类型自动变化在某些情况下，MySQL会悄悄地改变创建表的CREATETABLE语句中给出的列规范。这主要包括以下几种情形：（1）如果没有启用严格模式，则将长度规范大于65535的varchar列转换为text，将长度规范大于65535的varbinary列转换为blob。否则，在这两种情况中都会出现错误。（2）为字符数据类型指定CHARACTERSETbinary选项导致列被创建为相应的二进制数据类型：char变成binary，varchar变成varbinary，text变成blob。对于enum和set数据类型则不会发生这种情况，因为它们是根据声明创建的。例如：USEmydb;CREATETABLEtab_CSET( c1 varchar(10) CHARACTERSETbinary, c2 text CHARACTERSETbinary, c3 enum('a','b','c') CHARACTERSETbinary);虚拟列和虚拟列（3）如果使用myisampack压缩表，可能会发生某些其他数据类型的更改。（4）将其他SQL数据库供应商使用的某些数据类型映射到MySQL类型。如果您包含一个USING子句来给定存储引擎不允许的索引类型，但可以使用另一种索引类型而不影响查询结果，那么存储引擎将使用可用的类型。07表

约

束1.主键约束：PRIMARYKEY2.唯一键约束：UNIQUE[KEY]3.CHECK约束4.外键约束：FOREIGNKEY表

约

束1.主键约束：PRIMARYKEY在列定义时通过PRIMARYKEY属性指定列为主键：列名数据类型...NOTNULLPRIMARYKEY当多列作为主键时，则需要通过表属性PRIMARYKEY项指定。组成主键的列均为NOTNULL。PRIMARYKEY(列名[DESC],...)【例4.9】创建emarket数据库订单项表（orderitems），表上“订单编号”和“商品编号”构成联合主键。USEemarket;CREATETABLEorderitems(

订单编号 int NOTNULL,

商品编号 char(6) NOTNULL,

订货数量 int UNSIGNEDNOTNULL,

发货否 bit(1) NOTNULLDEFAULT0, PRIMARYKEY(订单编号DESC,商品编号) );表

约

束2.唯一键约束：UNIQUE[KEY]在列定义时通过UNIQUE属性指定列为唯一键：列名数据类型...UNIQUE当唯一键由多列组成时，需要表属性UNIQUE项指定。组成唯一键的列可以包含NULL，但最多只在一行出现。3.CHECK约束CHECK列约束限定的是列的内容，但对于多个列之间（部分）内容关系可以通过CHECK表约束来限定。当然CHECK列约束也可以通过CHECK表约束来实现。从MySQL8.0.16开始，创建表时对所有存储引擎都支持表和列CHECK约束的核心特性。定义CHECK约束的基本语法：[CONSTRAINT约束名]CHECK(约束条件)[[NOT]ENFORCED]表

约

束【例4.10】创建emarket数据库用户表（user），手机号、身份证号和有效期列需要进行CHECK约束。USEemarket;CREATETABLEuser(

帐户名 varchar(16) NOTNULLPRIMARYKEY,

姓名 char(4) NOTNULL,

性别 enum('男','女') NOTNULLDEFAULT'男',

密码 varchar(12) NOTNULLDEFAULT'abc123',

手机号 char(11) NOTNULLUNIQUE CHECK(LENGTH(TRIM(手机号))=11ANDLEFT(手机号,1)='1'), #(a)

身份证号 char(18) NOTNULL,

有效期 date NOTNULL,

常用地址 json NULL,

职业 enum('学生','职工','教师','医生','军人','公务员','其他')NULL,

关注 set('水果','肉禽','海鲜水产','粮油蛋'),

投递位置 point, UNIQUE(身份证号), CHECK(YEAR(有效期)-CONVERT(SUBSTR(身份证号,7,4),UNSIGNED)>=20)#(b));4.外键约束：FOREIGNKEY外键约束将在接下来的一节系统介绍。08外键约束1.RESTRICT：限制2.CASCADE：级联3.SETNULL：置空4.NOACTION：无动作5.SETDEFAULT：置默认外键约束一个表的外键可以为空值，若不为空值，则每一个外键值必须等于父表中主键的某个值。在CREATETABLE或ALTERTABLE语句中，通过外键约束定义多表之间的关联，基本语法如下：[CONSTRAINT约束名]FOREIGNKEY(列名,...]) REFERENCES主表名(主表列名,...) [ONDELETE参考选项] [ONUPDATE参考选项]外键约束1.RESTRICT：限制该方式只要子表中有关联的记录，就拒绝对父表执行删除记录和更新关联列内容操作。【例4.11】创建一个商品目录表（commodity_list），以“类别编号”作为外键引用商品分类表（category）的“类别编号”。（1）向商品分类表中插入3条记录。USEemarket;INSERTINTOcategory(类别编号,类别名称) VALUES ('1A','苹果'),('1B','梨'),('1C','橙'),('1D','柠檬'),('1E','香蕉'),('1F','芒果'),('1G','车厘子'),('1H','草莓'), ('2A','猪肉'),('2B','鸡鸭鹅'),('2C','牛肉'),('2D','羊肉'), ('3A','鱼'),('3B','海鲜'),('3C','海参'), ('4A','鸡蛋'),('4B','调味料'),('4C','啤酒'),('4D','滋补保健');外键约束（2）创建商品目录表（commodity_list）并在其上建立外键约束。USEemarket;CREATETABLEcommodity_list(

商品编号 char(6) NOTNULLPRIMARYKEY,

类别编号 char(2) NOTNULL,

商品名称 varchar(32) NOTNULL, CONSTRAINTFK_CATEGORY_IDFOREIGNKEY(类别编号) REFERENCEScategory(类别编号));向商品目录表中插入3条测试数据：INSERTINTOcommodity_list(商品编号,类别编号,商品名称) VALUES ('1A0101','1A','洛川红富士苹果冰糖心10斤箱装'), ('1A0201','1A','烟台红富士苹果10斤箱装'), ('1B0501','1B','库尔勒香梨10斤箱装');外键约束（3）将category表中“类别编号”为“1B”的记录改为“B”：UPDATEcategorySET类别编号='B'WHERE类别编号='1B';显示UPDATE操作失败信息如图所示。外键约束2.CASCADE：级联这种方式删除或更新父表中的行，会自动删除或更新子表中匹配的行。【例4.11续】测试商品分类表（category）与商品目录表（commodity_list）的级联操作。（1）用Navicat将商品目录表（commodity_list）的外键参考选项都设为CASCADE，如图所示。（2）现将category表中“类别编号”为“1B”的记录改为“B”。UPDATEcategorySET类别编号='B'WHERE类别编号='1B';外键约束（3）删除category表“类别编号”为“B”的记录。DELETEFROMcategoryWHERE类别编号='B';（4）执行如下语句，将两表数据复原：INSERTINTOcategory(类别编号,类别名称)VALUES('1B','梨');INSERTINTOcommodity_list(商品编号,类别编号,商品名称) VALUES('1B0501','1B','库尔勒香梨10斤箱装');外键约束3.SETNULL：置空删除或更新父表中的行，会将子表中的外键列设置为NULL。如果要指定SETNULL操作，请确保没有将子表中的外键列声明为NOTNULL。【例4.11续】测试商品分类表（category）与商品目录表（commodity_list）的置空关联操作。（1）打开Navicat设计商品目录表（commodity_list）的外键，参考选项都设为SETNULL。系统显示错误信息如图所示。外键约束（2）将商品目录表（commodity_list）“类别编号”列修改为可空（NULL）。ALTERTABLEcommodity_listMODIFY

类别编号 char(2) NULL;DESCcommodity_list;（3）打开设计商品目录表（commodity_list），在外键处将参考选项设为SETNULL，如图所示。（4）将商品分类表（category）中“类别编号”为“1B”的记录改为“B”。UPDATEcategorySET类别编号='B'WHERE类别编号='1B';外键约束4.NOACTION：无动作与RESTRICT作用相同，拒绝对父表执行任何操作，不再重复举例。5.SETDEFAULT：置默认删除或更新父表中的行时，MySQL将子表中匹配行的对应外键列置为默认值，这个操作可以被MySQL解析器识别，但是InnoDB和NDB引擎并不支持，故该选项目前实际上不可用。MySQL关联不仅可建立在两表之间，还可以定义在多表之间，形成错综复杂的关联关系。【例4.12】外键多表关联。一个product_order表有另外两个表的外键，一个外键引用product表中的两列；另一个引用customer表中的一列。外键约束（1）创建表product和customer。USEmydb;CREATETABLEproduct( category intNOTNULL, id intNOTNULL, price DECIMAL, PRIMARYKEY(category,id))ENGINE=InnoDB;

CREATETABLEcustomer( id intNOTNULL, PRIMARYKEY(id))ENGINE=InnoDB;外键约束（2）创建表product_order，参照引用product表的category、id两列，同时引用customer表的id列。USEmydb;CREATETABLEproduct_order( no intNOTNULLAUTO_INCREMENT, product_categoryintNOTNULL, product_id intNOTNULL, customer_id intNOTNULL, PRIMARYKEY(no), FOREIGNKEY(product_category,product_id) REFERENCESproduct(category,id) ONUPDATECASCADEONDELETERESTRICT, FOREIGNKEY(customer_id) REFERENCEScustomer(id))ENGINE=InnoDB;09从老表创建新表1.用语句复制表的结构和数据2.用Navicat复制表结构和数据3.用语句移植已有表的部分列从老表创建新表1.用语句复制表的结构和数据这是最为常见的应用，可以方便地从已有表创建备份表和数据，然后对其修改。CREATETABLE表名[LIKE源表名]|[AS(SELECT语句)];【例4.13】在emarket数据库中，用复制的方式创建一个名为commodity_copy1的表，表结构同商品表（commodity）；另再复制一个commodity_copy2表，其结构和内容（数据记录）都取自commodity表。前面已经向commodity表中插入3行记录，表中数据如图所示。从老表创建新表（1）复制commodity表结构到commodity_copy1表。USEemarket;CREATETABLEcommodity_copy1 LIKEcommodity;DESCcommodity; #（a）DESCcommodity_copy1; #（b）SELECT*FROMcommodity_copy1; #（c）（2）复制commodity表基本结构和数据到commodity_copy2表。CREATETABLEcommodity_copy2 AS(SELECT*FROMcommodity);DESCcommodity; #（a）DESCcommodity_copy2; #（b）SELECT*FROMcommodity_copy2; #（c）从老表创建新表2.用Navicat复制表结构和数据在实际数据库应用开发时，复制表结构和数据记录可以直接在Navicat环境下可视化操作十分方便。具体方法如下：选择需要复制的表（例如：commodity），按右键，在快捷菜单中选择“复制表”→“结构和数据”，系统生成一个新表，表结构（包括主键、索引、约束、外键、分区等）和记录与原表相同，但新表名在老表名后面加了“_copy1”（例如：commodity_copy1）。3.用语句移植已有表的部分列如果不需要老表的全部结构和数据，而只需使用其中的部分列内容，可以执行下列语句。CREATETABLE表名(

列名,...)SELECT语句从老表创建新表【例4.14】创建commodity_copy3表，仅需要commodity表商品编号、商品名称和价格列。（1）复制表创建表。USEemarket;CREATETABLEcommodity_copy3(

商品ID int NOTNULLAUTO_INCREMENTPRIMARYKEY)SELECT商品编号,商品名称,价格FROMcommodity;SELECT*FROMcommodity_copy3;从老表创建新表（2）移植的列同样也可以事先声明作为表的主键。例如：USEmydb;CREATETABLEtab_c1( a int NOTNULL, b int, c char(6))ENGINE=MyISAM;CREATETABLEtab_c2( a int NOTNULLAUTO_INCREMENT, PRIMARYKEY(a,b))ENGINE=MyISAMSELECTb,cFROMtab_c1;DESCtab_c2;10表

选

项1.存储引擎：ENGINE=引擎名2.表空间：WORKSPACE表空间名3.分区：PARTITIONBY分区描述4.行格式：ROW_FORMAT=行格式类型5.表连接：CONNECTION=远程服务器名表

选

项表选项用于优化表的行为，这些选项适用于所有存储引擎，MySQL8.0指定表选项的语法很简单，只要在表定义语句块之后紧接着写：CREATETABLE表名( ......)表选项名=值...;1.存储引擎：ENGINE=引擎名MySQL可以使用的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、ARCHIVE、EXAMPLE、FEDERATED、HEAP、MERGE和NDB。不指定存储引擎，系统默认采用InnoDB存储引擎，这也是最常用的存储引擎。2.表空间：WORKSPACE表空间名表空间指定表存放在什么地方。表空间包括系统表空间、单表表空间、通用表空间、临时表空间和日志表空间。不指定表空间，系统默认保存在系统表空间中。表

选

项3.分区：PARTITIONBY分区描述MySQL可按一定逻辑设置分区，在不同的文件、文件系统分配单个表的多个部分。MySQL8.0支持的分区类型有哈希分区（BYHASH）、键分区（BYKEY）、范围分区（BYRANGE）和列表分区（BYLIST）。若不指定分区，则表不分区，详细内容参考第6章。4.行格式：ROW_FORMAT=行格式类型行格式就是表中行的物理存储方式。在数据库内部存储系统中，每个表中的行被分成几页，组成每个表的页面被安排在一个称为B-tree索引的树数据结构中。表数据和二级索引都使用这种类型的结构，表示整个表的B-tree索引称为聚集索引，它根据主键列进行组织。聚集索引数据结构的节点包含行中所有列的值，二级索引结构的节点包含索引列和主键列的值。5.表连接：CONNECTION=远程服务器名采用FEDERATED存储引擎表指定的连接字符串，多用于跨服务器实例访问数据库。第4章MySQL8表结构设计修改表结构修改表结构ALTERTABLE语句用于改变表的结构。ALTERTABLE表名

修改选项,...

分区选项其中，“修改选项”可以添加列、删除列和更改列属性；也可以添加、删除表属性，常用的如下：[ADD列名列属性] /*增加列属性*/[DROP列名] /*删除列*/[MODIFY列名列属性] /*修改列属性*/[RENAMECOLUMN老列名TO新列名|CHANGE老列名新列名...] /*修改列名*/[ADD约束定义] /*增加表约束*/[DROP约束名] /*删除表约束*/[表属性] /*增加修改表属性*/01添加和删除列1.添加列2.删除列添加和删除列1.添加列向已经存在的表中添加新列：ALTERTABLE表名 ADD列名数据类型[列属性][FIRST|AFTER已存在的列名];【例4.15】在表commodity_copy2的“商品编号”列后增加新的一列“商品类别”，默认类别名称为“苹果”。USEemarket;ALTERTABLEcommodity_copy2 ADD 商品类别 char(4)DEFAULT'苹果'AFTER商品编号;SELECT*FROMcommodity_copy2;添加和删除列2.删除列删除列就是将数据表中的某个列从表中移除：ALTERTABLE表名DROP列名;【例4.16】删除表commodity_copy2的总价和商品列。ALTERTABLEcommodity_copy2 DROP总价,DROP商品;SELECT*FROMcommodity_copy2;02修改列及其属性1.列更名：RENAMECOLUMN子句2.表更名：RENAME子句3.修改列属性：MODIFY子句4.单独修改列默认值：ALTER[COLUMN]子句5.既重命名又重定义：CHANGE子句6.改变表中列的顺序：FIRST|AFTER7.各种子句的比较与适用场合修改列及其属性1.列更名：RENAMECOLUMN子句如果只是想给某个列改名而不改变其数据类型，使用RENAMECOLUMN子句即可：ALTERTABLE表名 RENAMECOLUMN老列名TO新列名【例4.17】将表commodity_copy2的“价格”列改名为“进货单价”。ALTERTABLEcommodity_copy2 RENAMECOLUMN价格TO进货单价;SELECT*FROMcommodity_copy2;修改列及其属性例如：有一个表test的列名为a、b和c。USEmydb;CREATETABLEtest( a int, b char(1), c bit(1));通过RENAMECOLUMN子句交换其列名如下：ALTERTABLEtest RENAMECOLUMNaTOb, RENAMECOLUMNbTOc, RENAMECOLUMNcTOa;DESCtest;修改列及其属性2.表更名：RENAME子句（1）RENAME子句重命名表ALTERTABLE原表名RENAME[TO|AS]新表名例如，将test重命名为test1。ALTERTABLEtestRENAMETOtest1;（2）RENAMETABLE语句重命名表RENAMETABLE原表名TO新表名（3）Navicat环境下重命名表在实际数据库应用开发时，重命名表的操作可以直接在Navicat环境下非常方便地进行。具体方法是：选择表，右击，点快捷菜单中的“重命名”，原来表名就变成可编辑状态，直接改名即可。修改列及其属性3.修改列属性：MODIFY子句若要更改列定义但不更改其名称，使用MODIFY子句：ALTERTABLE表名MODIFY

列名[数据类型][属性][默认值]（1）修改列属性，不能保留列原有的其他属性，必须重新全部指定。【例4.18】将表commodity_copy2的“进货单价”列该为int类型。ALTERTABLEcommodity_copy2 MODIFY进货单价int;DESCcommodity_copy2进货单价;修改列及其属性MODIFY子句在赋予列新的类型和默认值的时候，无法保留原来列上已经定义的其他属性项，比如，本例原来“进货单价”列是NOTNULL，但在用MODIFY修改时由于未显式地指定选项NOTNULL，结果修改后此列变成了允许空值（Null属性为“YES”），要在修改的同时保留原有列的其他属性，修改时必须同时加上这些属性。ALTERTABLEcommodity_copy2 MODIFY进货单价intNOTNULL;【例4.19】将表commodity_copy2的“库存量”列由smallint类型修改为int类型，同时加入该列上原来的其他属性。ALTERTABLEcommodity_copy2 MODIFY库存量intUNSIGNEDNOTNULLDEFAULT0;DESCcommodity_copy2库存量;修改列及其属性（2）修改列类型，表中已有数据的情况。在使用MODIFY子句修改列类型时，若该列所存数据的类型与修改后的列数据类型不完全一致，MySQL会根据通常的类型转换规则隐式强制转换为新的类型。如果缩短字符串列，值可能会被截断；如果改变数值类型，精度可能丢失。【例4.20】将表commodity_copy2的“进货单价”列改成整型int。表commodity_copy2中已经有数据了，“进货单价”列包含两位小数，如图所示。执行修改语句：ALTERTABLEcommodity_copy2 MODIFY进货单价intNOTNULL;修改列及其属性（3）如果MODIFY定义的新类型与列的原类型完全不匹配，还是会出错。例如，将“商品类别”列改为整型，执行语句：ALTERTABLEcommodity_copy2MODIFY商品类别int(2);由于表中商品类别为char类型，与整型完全不匹配，系统会产生错误，如图所示。修改列及其属性4.单独修改列默认值：ALTER[COLUMN]子句如果仅需要更改列的默认值，使用ALTER[COLUMN]子句可单独设定或删除默认值：ALTERTABLE表名 ALTER[COLUMN]列名{SETDEFAULT{literal|(表达式)}|DROPDEFAULT}【例4.21】将表commodity_copy2的“商品类别”列默