第第第第4 4 4 4章章章章 数据库及表结构设计数据库及表结构设计数据库及表结构设计数据库及表结构设计————数据库及基本操作数据库及基本操作MySQL+实用教程（第4版） 目 录目 录目 录目 录01系统数据库 系统数据库系统数据库在安装MySQL8.0后，就产生了4个系统数据库：包括information_schema、mysql、performance_schema和 sys，在登录MySQL服务器后，可查看MySQL系统已有的数据库：SHOW DATABASES;下面简单介绍4个系统数据库的作用,另外还有2个实例数据库1）information_schema数据库它保存了mysql服务器所有数据库的信息比如数据库的名称、数据库的表、访问权限、数据库表的数据类型、数据库索引的信息等等有时用于表述该信息的其他术语包括“数据字典”和“系统目录”2）mysql数据库它是MySQL的核心数据库，类似于SQL Server中的master表，主要负责存储数据库的用户、权限设置、关键字等MySQL自己需要使用的控制和管理信息3）performance_schema数据库主要用于收集数据库服务器性能参数，可用于监控服务器在一个较低级别的运行过程中的资源消耗、资源等待等情况。
4）sys数据库库中所有的数据来自performance_schema目标是把performance_schema的复杂度降低，让DBA能更好的阅读这个库里的内容，了解数据库的运行情况5）sakila和world实例数据库如果用户在安装MySQL时，在“Choosing a Setup Type”（安装类型）页选择“Examples and tutorials：”（实例和教程）项，则系统中还会看到另外2个实例数据库sakila和world 目 录目 录目 录目 录02数据库创建和修改1. 数据库的创建和修改2. 数据库删除 数据库创建和修改数据库创建和修改1. 数据库的创建和修改创建MySQL8数据库：CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] 数据库名 [DEFAULT] CHARACTER SET [=] 字符集名 | [DEFAULT] COLLATE [=] 排序规则名 | DEFAULT ENCRYPTION [=] {'Y' | 'N'}数据库名后面描述当前定义的数据库的属性，包括：字符集编码、字符排序规则和是否加密，它被存储在数据字典中。
数据库名前加文件夹路径例4.1】创建网上商城数据库（emarket），采用gbk字符集和gbk_bin排序规则CREATE DATABASE IF NOT EXISTS emarketDEFAULT CHARACTER SET gbkDEFAULT COLLATE gbk_bin;语句执行后，在MySQL 8.0安装的文件夹（默认为：C:\ProgramData\MySQL\MySQL Server 8.0\Data）下就会生成一个“数据库名”的子目录“\emarket”，此后在该数据库中创建的所有对象（包括表及其数据）都会以文件存储在该子目录下 数据库创建和修改数据库创建和修改2. 数据库删除数据库创建后，如果需要修改数据库的参数，可以使用“ALTER DATABASE...”语句，选项与CREATE DATABASE相同，这里就不再重复可以通过下列语句显示数据库属性SHOW CREATE DATABASE 数据库名已经创建的数据库需要删除，使用下列语句DROP {DATABASE | SCHEMA} [IF EXISTS] 数据库名 第第第第4 4 4 4章章章章 数据库及表结构设计数据库及表结构设计数据库及表结构设计数据库及表结构设计————创建表结构创建表结构 创建表结构创建表结构1. 创建表结构（1）使用CREATE TABLE语句创建表结构CREATE TABLE 表名[(列定义, ...[表约束])][表选项][表结构或表记录源]列定义:列名 列数据类型 [长度和小数] [空值] [虚拟] [键] [注释] [默认值] [其他属性] 创建表结构创建表结构（2）Navicat工具创建表结构【例4.2】采用Navicat创建emarket数据库中商品分类表（category）表结构。
在创建的连接下双击emarket数据库，再选择“表”，按右键，在快捷菜单中单击“新建表”，出现创建表窗口，输入内容如图4.1所示单击“保存”按钮，在出现的“表名”对话框中输入“category”，单击“确定”，category表创建完成在emarket数据库的“表”下就会出现“category” 创建表结构创建表结构（3）显示表属性在表创建后，可以通过下面语句显示指定表的创建属性：SHOW CREATE TABLE 表名通过下面语句显示表属性或者表指定列的属性：DESC 表名 [列名]【例4.2续】显示category表属性USE emarket;SHOW CREATE TABLE category;#（a）DESC category;#（b）DESC category 类别编号;#（c）显示category表“类别编号”列属性 创建表结构创建表结构显示结果分别如图4.2（a）、（b）、（c）所示说明：（a）以创建表语句方式显示category表结构，虽然category表结构并不是语句方式创建的通过该方式，可以了解采用Navicat创建表结构与创建表语句方式创建表结构的对应关系b）以列方式显示category表结构。
c）显示category表指定列结构 创建表结构创建表结构2. 删除表表结构创建后，可以修改表结构有了表结构，就可以增加、修改和删除表记录表记录的操作将在下一章系统介绍下列语句可以删除表，删除表后表结构和表记录均不存在，一般用于重新定义表DROP TABLE IF EXISTS 表名; 目 录目 录目 录目 录01列及其常用属性1. 列名2. 列数据类型3. 长度和小数4. 空值限制：NOT NULL/ NULL5. 虚拟6. 键7. 注释：COMMENT 列及其常用属性列及其常用属性1. 列名列又称字段，列名又称字段名列名必须符合标识符规则，中英文均可，长度不能超过64个字符，而且在表中要唯一如果采用MySQL保留字必须用单引号括起来列名一般可以采用英文、汉语拼音、中文等，采用中文作为列名，阅读方便；但从编程角度，英文、汉语拼音输入容易些本书前面介绍MySQL基础知识采用中文作为列名，后面实习系统开发时则采用英文列名 列及其常用属性列及其常用属性2. 列数据类型下面说明数据类型选择原则：（1）不需要表达小数用整数，无正负用无符号（unsigned），以可能存储的最大值选择整数类型2）包含小数但精度要求高不能选择浮点数而要选择定点数。
3）保存字符个数差别较大选择变长，需要进行字符运算即使保存全面为数字也要选择字符型，例如学号可能包含专业入学时间等信息需要保存的多媒体信息（例如图形、声音、视频等）用二进制字符型4）需要进行日期运算的选择日期型，需要包含时间的选择日期时间型5）内容规范的字符串选择枚举型，同时包含不确定规范信息选择集合型 列及其常用属性列及其常用属性3. 长度和小数（1）字符类型（char和varchar）长度为本列最大存放的字符个数，一个英文和一个中文均算作1个字符，例如：存放“abc中文系统”为7个字符默认长度为255列占用的空间大小与列采用的字符集有关如果采用gbk字符集，“abc中文系统”占用11个字节，因为gbk字符集英文占用1个字节，汉字占用2个字节；如果采用utf8mb4字符集，每一个字符占用4个字节，这样“abc中文系统”就占用28个字节2）浮点数类型（float、double）如果不指定长度和小数位，默认为整数，在最大数据范围内，数据超过有效数字位数的部分低位为0；如果指定长度和小数位，只要在数据类型允许的范围内，整数部分显示的位数=长度-小数位数-13）定点数类型（decimal）用于保存准确数字数据，长度和小数位可以根据用户需要指定。
例如：支付金额可能达到xxxxx.xx，数据不大，但有效位数超过6位，为了准确表达，可以采用定点数类型decimal支付金额decimal(8.2)其他数据类型指定长度和小数位没有意义 列及其常用属性列及其常用属性4. 空值限制：NOT NULL/ NULLNOT NULL表示列内容不允许为空，NULL或者不写此项表示允许为空例如：商品名称varchar(32)NOT NULL商品图片blobNULLNavicat中勾选“不是null”项，表示非空（NOT NULL），否则表示允许空（NULL）说明：（1）对于字符型，NULL并不是空格；对于数值型和位（bit）型，NULL并不是0NULL值是一个值，是没有赋值的值，可以与NULL进行等于（=）比较2）如果列指定为“NULL”或者没有指定，当增加一行时，即使该列不指定值也没有默认值，该行也能够成功保存，并且该列保存为NULL值3）如果列指定为“NOT NULL”，当增加一行时，在非严格模式下，列名表NOT NULL列可以省略，若该列没有指定值也无默认值则被设置为该列数据类型的隐式默认值（数值类型为0，字符串类型为空白字符串' '，日期和时间类型为相应格式的“零”值）；在严格模式下，列名表没有默认值的NOT NULL列不可以省略，且值不能为NULL。
列及其常用属性列及其常用属性5. 虚拟Navicat中勾选该项，表示本列在表中并不直接存放内容，而是由其他列生成关于虚拟列后面还会详细介绍6. 键列的键包括主键（PRIMARY KEY）和唯一键（UNIQUE [KEY]）内容在列约束中详细介绍7. 注释：COMMENT注释仅仅是为了此后自己或者别人显示表结构时方便了解该列作用的说明内容，最多1024个字符也可以用#后面跟注释内容例如：商品图片blobCOMMENT '图片不能大于64KB' 目 录目 录目 录目 录02列 约 束1. 主键约束：PRIMARY KEY2. 唯一键约束：UNIQUE [KEY]3. 完整性约束：CHECK 列列 约约 束束1. 主键约束：PRIMARY KEY选择PRIMARY KEY即该列作为主键主键列不允许为空（NOT NULL），并且在本表中必须唯一一个表中主键约束只能一个，而且主键列约束就是表的主键约束主键约束（PRIMARY KEY）以一列和一列以上（复合主键约束）当CREATE TABLE语句方式创建表的列约束方式创建主键约束只能为一列例4.3】创建emarket数据库供货商（supplier）表结构。
USE emarket;CREATE TABLE supplier(供货商编号char(2) NOT NULL PRIMARY KEY,供货商名称varchar(16) NOT NULL);复合主键约束就是需要一个以上的列才能唯一确定表记录 列列 约约 束束2. 唯一键约束：UNIQUE [KEY]选择UNIQUE [KEY]指明该列在本表中也必须唯一，但可以包含一个NULL值在一个表中可以包含多个唯一键约束例如：号char(11)NOT NULL UNIQUE KEY,varchar(20)NULL UNIQUE有了唯一键约束，在表约束中还可以定义其他列作为唯一性约束3. 完整性约束：CHECK对于不同类型的列均可以通过CHECK约束来实现：列名 数据类型... [CONSTRAINT 约束名] CHECK(约束条件)其中：约束条件是包含当前列名在内的条件表达式不同的列可以定义不同的约束条件，可以通过约束名来区分，也可以通过约束名删除约束例如，评估一个星对应1分，最大分值为5分，没有评估为0：评估分tinyintUNSIGNED DEFAULT 0 CHECK(评估分 <= 5) 目 录目 录目 录目 录03列默认值1. 显式常量默认值2. 显式表达式默认值3. 隐式默认值 列默认值列默认值1. 显式常量默认值显式常量默认值是由用户指定的常量，如果出现与定义声明的列数据类型不完全匹配的情形，MySQL能自动根据通常的类型转换规则隐式强制转换为用户设计表时所声明的类型。
例4.4】创建临时表，包含3个不同数据类型的列，均以DEFAULT指定常量默认值USE mydb;CREATE TABLE IF NOT EXISTS tab_default1(商品名称 varchar(32)DEFAULT '水果',价格float(6,2)NOT NULL DEFAULT 0,库存量smallint(3)DEFAULT 0);DESC tab_default1 '价格';#（a）INSERT INTO tab_default1 VALUES();SELECT * FROM tab_default1;#（b）运行结果如图4.3所示： 列默认值列默认值2. 显式表达式默认值除了常量，DEFAULT指定的默认值也可以是表达式通常将表达式默认值括在括号内，以便与常量默认值区分开来3. 隐式默认值如果不包含显式默认值，对于NULL、NOT NULL、UTO_INCREMENT、时间戳以外的日期和时间、枚举以外等MySQL将按系统规则来确定默认值 目 录目 录目 录目 录04数值类型属性1. 无符号：UNSIGNED2. 默认值：DEFAULT 值3. 填充零：ZEROFILL4. 自动递增：AUTO_INCREMENT 数值类型属性数值类型属性1. 无符号：UNSIGNED指定该列整数为“无符号”即不允许负值，否则系统错误。
例如：库存量smallintUNSIGNED NOT NULL2. 默认值：DEFAULT 值列指定DEFAULT，当增加一行时，只要该列不指定值就采用指定的默认值作为列值应该采用该列的常见初始值作为默认值默认值必须为一个常数例如，库存量默认值为0：库存量smallintUNSIGNED NOT NULL DEFAULT 03. 填充零：ZEROFILL当插入的值长度小于类型设定的长度时，剩余部分用0填补 数值类型属性数值类型属性4. 自动递增：AUTO_INCREMENT例如，订单编号采用系统自增属性：订单编号intNOT NULL AUTO_INCREMENT（1）标注AUTO_INCREMENT为自增列，当向表插入行记录时，默认情况下列值从1开始，按照自然数自动增12）每个表只能有一个AUTO_INCREMENT列，并且必须被索引，不能有默认值3）插入记录时，AUTO_INCREMENT列可以指定为NULL或者0，或者不指定该列，内容实际填自增值如果启用了下列模式：SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';则可以在AUTO_INCREMENT列中将指定的0存储为0，而不生成新的序列值。
4）要在插入行之后检索AUTO_INCREMENT值，可以使用LAST_INSERT_ID()5）插入记录时，可以指定AUTO_INCREMENT列的值，后面插入记录时如果指定NULL值或者不指定该列，内容实际填在此基础上自增6）通过下列语句可以修改此后行记录AUTO_INCREMENT列起始值：ALTER TABLE 表名 AUTO_INCREMENT = 值 数值类型属性数值类型属性【例4.5】创建emarket数据库订单表（orders），其中“订单编号”列为AUTO_INCREMENT列USE emarket;CREATE TABLE orders(订单编号intNOT NULL PRIMARY KEY AUTO_INCREMENT, #（a）帐户名varchar(16)NOT NULL,支付金额decimal(8,2)NOT NULL,下单时间datetimeNOT NULL #（b）);说明：（a）订单编号：系统生成，不重复，值自增，正好符合从小到大编码规则b）下单时间：要求提供具体的日期时间且时间精确到秒，设为datetime型 目 录目 录目 录目 录05字符类型属性1. 默认值：DEFAULT 值2. 字符集：CHARACTER SET 字符集名3. 排序规则：COLLATE 排序规则名4. 键长度：LEFT(列名, 长度) 字符类型属性字符类型属性1. 默认值：DEFAULT 值列指定默认值，默认值必须为一个字符串常数。
例如：’ABC’2. 字符集：CHARACTER SET 字符集名指定列的字符集3. 排序规则：COLLATE 排序规则名指定列的字符集对应的排序规则4. 键长度：LEFT(列名, 长度)如果选择指定为主键，默认情况下为该列的所有内容，但如果指定键长度，则将该列的前面键长度指定的内容作为主键 目 录目 录目 录目 录06虚拟列（生成列） 虚拟列（生成列）虚拟列（生成列）CREATE TABLE语句支持生成列，这种列的值是从列定义中包含的表达式计算而来，有时称为虚拟列列名 数据类型 [GENERATED ALWAYS] AS (表达式) [VIRTUAL | STORED] [属性] [列约束]说明：（1）AS(表达式)：指示生成列并定义用于计算列值的表达式2）GENERATED ALWAYS可不写表达式中不含AUTO_INCREMEN列注意，表达式需要用加括号3）VIRTUAL或STORED关键字指示如何存储列值VIRTUAL：虚拟生成列它不存储列值，在任何BEFORE触发器之后立即计算列值虚拟列不需要存储空间如果没有指定关键字，则默认为VIRTUALSTORED：存储生成列在插入或更新行时计算和存储列值，它需要存储空间，并且可以被索引。
虚拟列（生成列）虚拟列（生成列）关于生成列的使用还要注意以下几点：（1）生成列的定义也可以引用前面已经定义的生成列2）如果表达式计算的数据类型与声明的列类型不同，则根据通常的MySQL类型转换规则隐式强制转换为声明的类型3）外键约束不能引用生成列4）如果显式地对生成列执行插入、替换或更新，则唯一允许的值是默认值（DEFAULT）生成列可用于简化和统一查询，可以将复杂条件定义为生成列，并从表上的多个查询引用它，以确保所有查询都使用完全相同的条件5）有些系统函数不能包含在生成表达式中例如：表中包含身份证列，定义一个生成列年龄，通过CURDATE()函数得到当前日期与身份证中的出生日期相减+1得到年龄列值是不可以的因为CURDATE()不是确定的值 虚拟列（生成列）虚拟列（生成列）【例4.6】创建emarket数据库商品表（commodity）结构，总价和商品列作为生成列1）创建表USE emarket; CREATE TABLE commodity(商品编号char(6)NOT NULL PRIMARY KEY,商品名称varchar(32)NOT NULL,价格decimal(6,2)NOT NULL,库存量smallint(3)UNSIGNED DEFAULT 0,商品图片blobNULL, 总价decimal(10,2)AS (价格 * 库存量), #（a）商品varchar(100)AS (CONCAT(商品编号, ' ', 商品名称)) #（b）);其中：（a）总价列是由价格和库存量相乘计算产生的，整体需要括起来；（b）商品列用内置的CONCAT()函数将“商品编号”和“商品名称”拼接起来，整体需要括起来。
虚拟列（生成列）虚拟列（生成列）2）向commodity表插入3条测试记录USE emarket;INSERT INTO commodity(商品编号, 商品名称,价格, 库存量)VALUES('1A0101', '洛川红富士苹果冰糖心10斤箱装', 44.80, 3601);INSERT INTO commodity(商品编号, 商品名称,价格, 库存量)VALUES('1A0201', '烟台红富士苹果10斤箱装', 29.80, 5698);INSERT INTO commodity(商品编号, 商品名称,价格, 库存量)VALUES('1A0302', '阿克苏苹果冰糖心5斤箱装', 29.80, 12680);3）SELECT查询商品和总价信息SELECT 商品, 总价 FROM commodity;显示结果如图4.4所示 目 录目 录目 录目 录07表 约 束1. 主键约束：PRIMARY KEY2. 唯一键约束：UNIQUE [KEY]3. CHECK约束 表表 约约 束束1. 主键约束：PRIMARY KEY在列定义时通过PRIMARY KEY属性指定列为主键：列名 数据类型...NOT NULL PRIMARY KEY当多列作为主键时，则需要通过表属性PRIMARY KEY项指定。
组成主键的列均为NOT NULLPRIMARY KEY(列名[DESC], ...)如果PRIMARY KEY约束需要多列时，也可以另外定义一个自增列作为内部管理列，然后用该列作为主键当然，可以表属性PRIMARY KEY项也可以定义一列为主键 表表 约约 束束【例4.7】创建emarket数据库订单项表（orderitems），表上“订单编号”和“商品编号”构成联合主键USE emarket; CREATE TABLE orderitems(订单编号intNOT NULL,商品编号char(6)NOT NULL,订货数量intUNSIGNED NOT NULL,发货否bit(1)NOT NULL DEFAULT 0,PRIMARY KEY(订单编号 DESC, 商品编号));说明：“订单编号”列后加DESC项，表示订单编号按照从大到小排列（加ASC，或者什么不写为从小到大排列），同一订单编号记录，按照商品编号从小到大排列因为订单编号小的是以前的订单，而我们往往更关心当前新订单 表表 约约 束束2. 唯一键约束：UNIQUE [KEY]在列定义时通过UNIQUE 属性指定列为唯一键：列名 数据类型... UNIQUE当唯一键由多列组成时，需要表属性UNIQUE项指定。
[CONSTRAINT 约束名] UNIQUE [KEY] ( 列名[DESC], ... )组成唯一键的列可以包含NULL，但最多只在一行出现唯一键约束在一个表中可以没有，也可以建立一个或多个当然，表属性UNIQUE [KEY]也可以定义单列作为唯一键 表表 约约 束束3. CHECK约束CHECK列约束限定的是列的内容，但对于多个列之间（部分）内容关系可以通过CHECK表约束来限定当然CHECK列约束也可以通过CHECK表约束来实现从MySQL 8.0.16开始，创建表时对所有存储引擎都支持表和列CHECK约束的核心特性定义CHECK约束的基本语法：[CONSTRAINT 约束名] CHECK(约束条件) [[NOT] ENFORCED]说明：（1）CONSTRAINT 约束名约束名如果省略，MySQL将从表名、文字_chk_和序号（1、2、3、…）生成一个名称约束名称的最大长度为64个字符它们区分大小写，但不区分重音2）CHECK(约束条件)约束条件包含多个列逻辑表达式对于表的每一行，约束条件值必须为TRUE或UNKNOWN（对于空值）如果条件的计算结果为FALSE，则该插入和更新行操作不会成功。
3）[NOT] ENFORCED可选强制执行子句指示是否强制约束，如果省略或指定为ENFORCED，则创建并强制约束；如果指定为NOT ENFORCED，则创建约束但不强制 表表 约约 束束【例4.8】创建emarket数据库用户表（user），号、身份证号和有效期列需要进行CHECK约束USE emarket;CREATE TABLE user(帐户名varchar(16) NOT NULL PRIMARY KEY,姓名 char(4) NOT NULL,性别enum('男','女') NOT NULL DEFAULT '男',密码 varchar(12) NOT NULL DEFAULT 'abc123',号 char(11)NOT NULL UNIQUE CHECK(LENGTH(TRIM(号))=11 AND LEFT(号,1)='1'), #(a)身份证号char(18)NOT NULL,有效期date NOT NULL,常用地址jsonNULL,职业 enum('学生','职工','教师','医生','军人','公务员','其他') NULL,关注set('水果','肉禽','海鲜水产','粮油蛋'),投递位置point,UNIQUE(身份证号),CHECK(YEAR(有效期)-CONVERT(SUBSTR(身份证号,7,4),UNSIGNED)>=20) #(b)); 表表 约约 束束说明：（a）“号”列约束，约束条件为：号去除前后空格符仍然为11个字符，并且第1个字符为“1”。
对于号还有其他条件，例如：全部为数字字符、打头的3位符合要求等等，这里仅仅表示一点意思目前，实际应用号验证的方法是给提供的号发验证码，界面上提供文本框供用户输入，看文本框用户输入内容是否与发出的一致，这样才能从根本上验证号的正确性和真实性b）这里为表约束，因为包含2个列之间的约束，所以只能作为表约束，采用默认约束名约束条件为：（身份证）有效期列与身份证号列中的出生年份（第7位开始的4位）相差20年以上 目 录目 录目 录目 录08表外键约束1. RESTRICT：限制2. CASCADE：级联3. SET NULL：置空4. NO ACTION：无动作5. SET DEFAULT：置默认 表外键约束表外键约束在CREATE TABLE或ALTER TABLE语句中，通过外键约束定义多表之间的关联，基本语法如下：[CONSTRAINT 约束名] FOREIGN KEY(列名, ...])REFERENCES 主表名(主表列名, ...)[ON DELETE 参考选项][ON UPDATE 参考选项]说明：（1）CONSTRAINT 约束名如果没有给出这个子句，或者在CONSTRAINT关键字后面没有包含约束名，MySQL会自动生成一个外键约束名。
约束名对于数据库和相同约束类型必须唯一，否则会导致错误2）FOREIGN KEY：表示这是建立在哪个（些）列上的外键约束3）REFERENCES：表示该外键所引用（关联）的是哪个表上的哪个（些）列4）参考选项为设定对表操作时与其相关联的表所要进行怎样的关联操作MySQL提供以下参考选项：CASCADE | SET NULL | RESTRICT | NO ACTION | SET DEFAULT 表外键约束表外键约束1. RESTRICT：限制该方式只要子表中有关联的记录，就拒绝对父表执行删除记录和更新关联列内容操作例4.9】创建一个商品目录表（commodity_list），以“类别编号”作为外键引用商品分类表（category）的“类别编号”1）向商品分类表中插入3条记录USE emarket;INSERT INTO category(类别编号, 类别名称)VALUES('1A','苹果'),('1B','梨'),('1C','橙'),('1D','柠檬'),('1E','香蕉'),('1F', '芒果'),('1G','车厘子'),('1H','草莓'),('2A','猪肉'),('2B','鸡鸭鹅'),('2C','牛肉'),('2D','羊肉'),('3A','鱼'),('3B','海鲜'),('3C','海参'),('4A','鸡蛋'),('4B','调味料'),('4C','啤酒'),('4D','滋补保健'); 表外键约束表外键约束（2）创建商品目录表（commodity_list）并在其上建立外键约束。
USE emarket;CREATE TABLE commodity_list(商品编号char(6)NOT NULL PRIMARY KEY,类别编号char(2)NOT NULL,商品名称varchar(32)NOT NULL,CONSTRAINT FK_CATEGORY_ID FOREIGN KEY(类别编号) REFERENCES category(类别编号));执行后采用Navicat在商品目录表（commodity_list）的 “外键”选项页可看到该外键约束的各项信息，其中删除时和更新时为“RESTRICT”如图4.5所示 表外键约束表外键约束向商品目录表中插入3条测试数据：INSERT INTO commodity_list(商品编号, 类别编号, 商品名称)VALUES('1A0101', '1A', '洛川红富士苹果冰糖心10斤箱装'),('1A0201', '1A', '烟台红富士苹果10斤箱装'),('1B0501', '1B', '库尔勒香梨10斤箱装');执行后commodity_list表中的记录如图4.6所示 表外键约束表外键约束（3）将category表中“类别编号”为“1B”的记录改为“B”：UPDATE category SET 类别编号 = 'B' WHERE 类别编号 = '1B';显示UPDATE操作失败信息如图4.7所示。
表外键约束表外键约束2. CASCADE：级联这种方式删除或更新父表中的行，会自动删除或更新子表中匹配的行例4.9续】测试商品分类表（category）与商品目录表（commodity_list）的级联操作1）用Navicat将商品目录表（commodity_list）的外键参考选项都设为CASCADE，如图4.8所示2）现将category表中“类别编号”为“1B”的记录改为“B”UPDATE category SET 类别编号 = 'B' WHERE 类别编号 = '1B';commodity_list表中对应记录的类别编号也随之改变，如图4.9所示 表外键约束表外键约束（3）删除category表“类别编号”为“B”的记录DELETE FROM category WHERE 类别编号 = 'B';commodity_list表中对应记录也随之删除，如图4.10所示4）执行如下语句，将两表数据复原：INSERT INTO category(类别编号, 类别名称) VALUES('1B', '梨');INSERT INTO commodity_list(商品编号, 类别编号, 商品名称)VALUES ('1B0501', '1B', '库尔勒香梨10斤箱装'); 表外键约束表外键约束3. SET NULL：置空删除或更新父表中的行，会将子表中的外键列设置为NULL。
如果要指定SET NULL操作，请确保没有将子表中的外键列声明为NOT NULL4. NO ACTION：无动作与RESTRICT作用相同，拒绝对父表执行任何操作，不再重复举例5. SET DEFAULT：置默认删除或更新父表中的行时，MySQL将子表中匹配行的对应外键列置为默认值，这个操作可以被MySQL解析器识别，但是InnoDB和NDB引擎并不支持，故该选项目前实际上不可用 目 录目 录目 录目 录09从老表创建新表结构 从老表创建新表结构从老表创建新表结构用户也可直接复制数据库中已有表的结构，用这种方式构建一个表，十分方便、快捷CREATE TABLE 表名 LIKE 源表名说明：（1）使用LIKE关键字创建一个与“源表名”相同结构的新表，源表的列名、数据类型、是否空值、主键、默认值、索引、约束、分区等都将复制，但是源表的记录不会复制，因此创建的新表是一个空表2）使用AS关键字可以复制SELECT语句查询结果表，但源表的一些属性（如主键、生成列等）却不会复制 从老表创建新表结构从老表创建新表结构【例4.10】在emarket 数据库中，用复制的方式创建一个名为commodity_copy1的表，表结构同商品表（commodity）。
USE emarket;CREATE TABLE commodity_copy1 LIKE commodity;选择需要复制的表（例如：commodity），按右键，在快捷菜单中选择“复制表”→“结构”，系统生成一个新表，表结构（包括主键、索引、约束、外键、分区等）和记录与原表相同，但新表名在老表名后面加了“_copy1”（例如：commodity_copy1） 第第第第4 4 4 4章章章章 数据库及表结构设计数据库及表结构设计数据库及表结构设计数据库及表结构设计————修改表结构修改表结构 修改表结构修改表结构ALTER TABLE语句用于改变表的结构ALTER TABLE 表名增删改列属性,...修改表约束修改表选项增删改列属性：[ADD 列名 列属性]/* 增加列属性 */[DROP 列名]/* 删除列 */[MODIFY 列名 列属性]/* 修改列属性 */[RENAME COLUMN 老列名 TO 新列名| CHANGE 老列名 新列名...]/* 修改列名 */增删表约束：[ADD 约束定义]/* 增加表约束 */[DROP 约束名]/* 删除表约束 */ 目 录目 录目 录目 录01添加和删除列1. 添加列2. 删除列 添加和删除列添加和删除列1. 添加列向已经存在的表中添加新列：ALTER TABLE 表名ADD 列名 数据类型 [列属性] [FIRST | AFTER 已存在的列名];说明：[FIRST | AFTER 已存在的列名]：表示在某列的前或后添加，不指定则添加到最后。
例4.11】在表commodity_copy2的“商品编号”列后增加新的一列“商品类别”，默认类别名称为“苹果”USE emarket;ALTER TABLE commodity_copy2ADD 商品类别 char(4) DEFAULT '苹果' AFTER 商品编号;SELECT * FROM commodity_copy2; 添加和删除列添加和删除列2. 删除列删除列就是将数据表中的某个列从表中移除：ALTER TABLE 表名 DROP 列名;如果一个表只包含一列，则不能删除该列例4.12】删除表commodity_copy2的总价和商品列ALTER TABLE commodity_copy2 DROP 总价, DROP 商品;SELECT * FROM commodity_copy2;运行结果如图4.11所示 目 录目 录目 录目 录02修改列及其属性1. 列更名：RENAME COLUMN子句2. 表更名：RENAME子句3. 修改列属性：MODIFY子句4. 单独修改列默认值：ALTER [COLUMN]子句5. 既重命名又重定义：CHANGE子句6. 改变表中列的顺序：FIRST | AFTER7. 各种子句的比较与适用场合 修改列及其属性修改列及其属性1. 列更名：RENAME COLUMN子句如果只是想给某个列改名而不改变其数据类型，使用RENAME COLUMN子句即可：ALTER TABLE 表名RENAME COLUMN 老列名 TO 新列名【例4.13】将表commodity_copy2的“价格”列改名为“进货单价”。
ALTER TABLE commodity_copy2RENAME COLUMN 价格 TO 进货单价;SELECT * FROM commodity_copy2;运行结果如图4.12所示 修改列及其属性修改列及其属性【例4.14】有一个表test的列名为a、b和cUSE mydb;CREATE TABLE test(aint,bchar(1),cbit(1));通过RENAME COLUMN子句交换其列名如下：ALTER TABLE testRENAME COLUMN a TO b,RENAME COLUMN b TO c,RENAME COLUMN c TO a;DESC test;运行结果如图4.13所示 修改列及其属性修改列及其属性2. 表更名：RENAME子句（1）RENAME子句重命名表ALTER TABLE 原表名 RENAME [TO|AS] 新表名例如，将test重命名为test1ALTER TABLE test RENAME TO test1;（2）RENAME TABLE语句重命名表RENAME TABLE 原表名 TO 新表名（3）Navicat环境下重命名表在实际数据库应用开发时，重命名表的操作可以直接在Navicat环境下非常方便地进行。
具体方法是：选择表，右击，点快捷菜单中的“重命名”，原来表名就变成可编辑状态，直接改名即可 修改列及其属性修改列及其属性3. 修改列属性：MODIFY子句若要更改列定义但不更改其名称，使用MODIFY子句：ALTER TABLE 表名 MODIFY列名 [数据类型] [属性] [默认值]说明：对于使用MODIFY进行的列定义更改，必须包括数据类型和应用于新列的所有属性，而不包括PRIMARY KEY、UNIQUE等列约束1）修改列属性，不能保留列原有的其他属性，必须重新全部指定例4.15】将表commodity_copy2的“库存量”列由smallint类型修改为int类型，同时加入该列上原来的其他属性ALTER TABLE commodity_copy2 MODIFY 库存量 int UNSIGNED NOT NULL DEFAULT 0;DESC commodity_copy2 库存量;运行结果如图4.14所示 修改列及其属性修改列及其属性2）修改列类型，表中已有数据的情况在使用MODIFY子句修改列类型时，若该列所存数据的类型与修改后的列数据类型不完全一致，MySQL会根据通常的类型转换规则隐式强制转换为新的类型。
如果缩短字符串列，值可能会被截断；如果改变数值类型，精度可能丢失例4.16】将表commodity_copy2的“进货单价”列改成整型int表commodity_copy2中已经有数据了，“进货单价”列包含两位小数，如图4.15所示 修改列及其属性修改列及其属性执行修改语句：ALTER TABLE commodity_copy2 MODIFY 进货单价 int NOT NULL;执行后结果如图4.16所示 修改列及其属性修改列及其属性3）如果MODIFY定义的新类型与列的原类型完全不匹配，还是会出错例如，将“商品类别”列改为整型，执行语句：ALTER TABLE commodity_copy2 MODIFY 商品类别 int(2);由于表中商品类别为char类型，与整型完全不匹配，系统会产生错误，如图4.17所示 修改列及其属性修改列及其属性4. 单独修改列默认值：ALTER [COLUMN]子句如果仅需要更改列的默认值，使用ALTER [COLUMN]子句可单独设定或删除默认值：ALTER TABLE 表名ALTER [COLUMN] 列名 {SET DEFAULT {literal | (表达式)} | DROP DEFAULT}说明：SET DEFAULT... | DROP DEFAULT：分别为列指定新的默认值或删除旧的默认值。
如果删除了旧的默认值，并且列可以为空，则新默认值为空；如果该列不能为空，MySQL将分配一个默认值例4.17】将表commodity_copy2的“商品类别”列默认值改为“香蕉”，列的其他属性保留不变ALTER TABLE commodity_copy2 ALTER 商品类别 SET DEFAULT '香蕉'; 修改列及其属性修改列及其属性5. 既重命名又重定义：CHANGE子句若要更改列同时更改其名称和定义，使用CHANGE子句，它需要同时指定新老列名和新的定义，语法格式如下：ALTER TABLE 表名CHANGE 老列名 新列名 [数据类型] [属性] [默认值]【例4.18】将表commodity_copy2的“进货单价”列更名为“单价”，数据类型改为decimal，长度为7取两位小数，非空；默认值为0ALTER TABLE commodity_copy2CHANGE 进货单价 单价 decimal(7,2) NOT NULL DEFAULT 0;执行后表中数据如图4.18所示 修改列及其属性修改列及其属性6. 改变表中列的顺序：FIRST | AFTER若要对表中的列重新排序，可在MODIFY或者CHANGE语句中使用FIRST和AFTER，语法如下：ALTER TABLE 表名MODIFY 列名1 列1定义 FIRST | AFTER 列名2;或者ALTER TABLE 表名CHANGE 列名1 列名1 列1定义 FIRST | AFTER 列名2 修改列及其属性修改列及其属性【例4.19】用MODIFY将commodity_copy2表中“商品类别”列置于所有列之后，再改到第1列。
1）“商品类别”列置于所有列之后ALTER TABLE commodity_copy2MODIFY 商品类别 char(4) DEFAULT '苹果' AFTER 商品图片;执行后的结果如图4.19所示2）“商品类别”列置于第1列ALTER TABLE commodity_copy2MODIFY 商品类别 char(4) DEFAULT '苹果' FIRST;执行后的结果如图4.20所示 修改列及其属性修改列及其属性7. 各种子句的比较与适用场合RENAME、MODIFY、ALTER和CHANGE都可以更改现有列的名称和定义，但它们功能又有不同RENAME和MODIFY是为了兼容Oracle的MySQL扩展，而CHANG则是对标准SQL的MySQL扩展1）RENAME：在不更改列定义的情况下重命名列，比CHANG更方便2）MODIFY：在不重命名列的情况下更改列定义比CHANG更方便3）ALTER：只能用于更改列的默认值在仅需要改变默认值的情况下，无须显式写出列已有的其他属性4）CHANGE：既可以重命名列又可以更改其定义，或者两者皆可，使用灵活，具有比RENAME、MODIFY和ALTER更多的功能。
CHANGE的语法需要两个列名，如果只是想改变定义而不改变名称，也必须两次指定相同的名称才能保持列名不变 修改列及其属性修改列及其属性例如，要更改列b的定义，需要写为：ALTER TABLE testCHANGE b b int NOT NULL;如果列名称较长而复杂，这种写法很容易出错或遗漏，建议使用MODIFY子句若要更改列名但不更改其定义，采用CHANGE，语法需要列定义，要保持原来的定义不变，必须重写一遍列当前的定义例如，要将一个int NOT NULL列从b重命名为a，必须写成：ALTER TABLE testCHANGE b a int NOT NULL; 目 录目 录目 录目 录03添加和删除表约束1. 添加删除主键约束：PRIMARY KEY2. 添加删除唯一键约束：UNIQUE [KEY]3. 添加删除CHECK约束4. 添加删除外键约束：FOREIGN KEY 添加和删除表约束添加和删除表约束1. 添加删除主键约束：PRIMARY KEY（1）添加主键约束ALTER TABLE 表名ADD [CONSTRAINT [约束名]] PRIMARY KEY(键部分,...)主键既是区分表记录唯一性的约束，通过约束名标识该约束。
同时它也是索引，索引名为“PRIMARY KEY”例如：ALTER TABLE testADD PRIMARY KEY(列名)（2）删除主键约束ALTER TABLE 表名DROP PRIMARY KEY 添加和删除表约束添加和删除表约束【例4.20】在commodity_copy2表上增加一个自增列，并将其作为主键因为表上增加自增列需要作为主键，必须先删除原来的主键约束，然后添加自增列和主键约束USE emarket;ALTER TABLE commodity_copy2 DROP PRIMARY KEY;ALTER TABLE commodity_copy2 ADD 商品ID int AUTO_INCREMENT PRIMARY KEY FIRST,AUTO_INCREMENT = 1000; DESC commodity_copy2;其中，AUTO_INCREMENT = 1000为表属性运行结果如图4.21所示 添加和删除表约束添加和删除表约束2. 添加删除唯一键约束：UNIQUE [KEY]（1）添加唯一键约束ALTER TABLE 表名ADD [CONSTRAINT [约束名]] UNIQUE [INDEX | KEY] [索引名] [索引类型] (键部分,...) [索引选项]唯一键也是区分表记录唯一性的约束，通过约束名标识该约束。
同时它也是索引，通过索引名来区分关于索引类型和索引选项在后面索引章节系统介绍2）删除唯一键约束ALTER TABLE 表名DROP {INDEX | KEY} 索引名【例4.21】创建user表结构的副本user_copy，将“姓名”列和“职业”列共同置为UNIQUE表约束USE emarket;CREATE TABLE user_copy LIKE user;ALTER TABLE user_copyADD UNIQUE KEY idx_name1 (姓名,职业); 添加和删除表约束添加和删除表约束3. 添加删除CHECK约束（1）添加CHECK约束ALTER TABLE 表名ADD [CONSTRAINT约束名] CHECK(约束逻辑表达式) [[NOT] ENFORCED]选择“NOT ENFORCED”不进行强制约束2）修改CHECK约束ALTER TABLE 表名ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED（3）删除CHECK约束ALTER TABLE 表名DROP {CHECK | CONSTRAINT} 约束名 添加和删除表约束添加和删除表约束【例4.22】将user_copy表“号”列CHECK约束变成表CHECK约束。
USE emarket;SHOW CREATE TABLE user_copy;#（a）ALTER TABLE user_copy DROP CHECK user_copy_chk_1;ALTER TABLE user_copy ADD CONSTRAINT user_copy_chk_phone CHECK(LENGTH(TRIM(号))=11 AND LEFT(号,1)='1');SHOW CREATE TABLE user_copy;#（b）显示结果如图4.22所示说明：（1）原来“号”列CHECK约束user_copy_chk_1：CHECK(LENGTH(TRIM(号))=11 AND LEFT(号,1)='1')被删除2）原来表CHECK约束user_copy_chk_2：CHECK(YEAR(有效期)-CONVERT(SUBSTR(身份证号,7,4),UNSIGNED)>=20)3）新加表CHECK约束user_copy_chk_phone：CHECK(LENGTH(TRIM(号))=11 AND LEFT(号,1)='1') 添加和删除表约束添加和删除表约束4. 添加删除外键约束：FOREIGN KEY外键约束是子表和父表某（些）列建立关联。
在创建表结构时建立外键约束，也可以在创建表结构后建立或者删除外键约束建立外键约束时父表必须已经存在一个表可以建立多个外键约束，甚至同一种约束可以建立多个外键，但外键约束的名称不能相同1）添加外键约束【例4.23】建立订单项表（orderitems）与订单表（orders）和商品表（commodity）之间的外键约束1）显示订单表（orders）、商品表（commodity）和订单项表（orderitems）表结构USE emarket;DESC orders;DESC commodity;DESC orderitems; 添加和删除表约束添加和删除表约束显示结果如图4.23（a）（b）（c）所示 添加和删除表约束添加和删除表约束（2）订单项表（orderitems）与订单表（orders）通过“订单编号”列建立外键约束；与商品表（commodity）通过“商品编号”列建立外键约束ALTER TABLE orderitems ADD CONSTRAINT fk_user_orders FOREIGN KEY(订单编号) REFERENCES orders(订单编号);ALTER TABLE orderitems ADD CONSTRAINT fk_user_commodity FOREIGN KEY(商品编号) REFERENCES commodity(商品编号);（3）查看订单项表（orderitems）的外键约束。
在Navicat中选中orderitems表，右击，点“设计表”，选择“外键”选项页，查看结果如图4.24所示 添加和删除表约束添加和删除表约束2）删除外键约束在数据表之间存在外键关联的情况下，可以删除子表，当然子表对应的关联也不复存在但如果直接删除父表，结果会显示失败，因为这样的删除操作将破坏表之间的参照完整性只有先将关联的表的外键约束取消，才能删除父表删除外键约束语句如下：ALTER TABLE 表名DROP FOREIGN KEY 外键约束名外键约束也可以通过Navicat设计表结构界面的“外键”选项页添加和删除注意：当导入多个表的数据时，如果要忽略表之前导入顺序，或者当执行LOAD DATA和ALTER TABLE操作，为了提高处理速度，可以暂时关闭外键约束功能而不需要删除外键约束SET FOREIGN_KEY_CHECKS = 0; 。