MySQL实用教程(新体系.综合应用实例视频)第4版第4章 数据库及表结构设计

第4章

数据库及表结构设计——数据库及基本操作MySQL+实用教程（第4版）01系统数据库系统数据库在安装MySQL8.0后，就产生了4个系统数据库：包括information_schema、mysql、performance_schema和sys，在登录MySQL服务器后，可查看MySQL系统已有的数据库：SHOWDATABASES;下面简单介绍4个系统数据库的作用,另外还有2个实例数据库。（1）information_schema数据库它保存了mysql服务器所有数据库的信息。比如数据库的名称、数据库的表、访问权限、数据库表的数据类型、数据库索引的信息等等。有时用于表述该信息的其他术语包括“数据字典”和“系统目录”。（2）mysql数据库它是MySQL的核心数据库，类似于SQLServer中的master表，主要负责存储数据库的用户、权限设置、关键字等MySQL自己需要使用的控制和管理信息。（3）performance_schema数据库主要用于收集数据库服务器性能参数，可用于监控服务器在一个较低级别的运行过程中的资源消耗、资源等待等情况。（4）sys数据库库中所有的数据来自performance_schema。目标是把performance_schema的复杂度降低，让DBA能更好的阅读这个库里的内容，了解数据库的运行情况。（5）sakila和world实例数据库如果用户在安装MySQL时，在“ChoosingaSetupType”（安装类型）页选择“Examplesandtutorials：”（实例和教程）项，则系统中还会看到另外2个实例数据库sakila和world。02数据库创建和修改1.数据库的创建和修改2.数据库删除数据库创建和修改1.数据库的创建和修改创建MySQL8数据库：CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]数据库名[DEFAULT]CHARACTERSET[=]字符集名|[DEFAULT]COLLATE[=]排序规则名|DEFAULTENCRYPTION[=]{'Y'|'N'}数据库名后面描述当前定义的数据库的属性，包括：字符集编码、字符排序规则和是否加密，它被存储在数据字典中。数据库名前加文件夹路径。【例4.1】创建网上商城数据库（emarket），采用gbk字符集和gbk_bin排序规则。CREATEDATABASEIFNOTEXISTSemarket DEFAULTCHARACTERSETgbk DEFAULTCOLLATEgbk_bin;语句执行后，在MySQL8.0安装的文件夹（默认为：C:\ProgramData\MySQL\MySQLServer8.0\Data）下就会生成一个“数据库名”的子目录“\emarket”，此后在该数据库中创建的所有对象（包括表及其数据）都会以文件存储在该子目录下。数据库创建和修改2.数据库删除数据库创建后，如果需要修改数据库的参数，可以使用“ALTERDATABASE...”语句，选项与CREATEDATABASE相同，这里就不再重复。可以通过下列语句显示数据库属性。SHOWCREATEDATABASE数据库名已经创建的数据库需要删除，使用下列语句。DROP{DATABASE|SCHEMA}[IFEXISTS]数据库名第4章

数据库及表结构设计——创建表结构创建表结构1.创建表结构（1）使用CREATETABLE语句创建表结构CREATETABLE表名[(

列定义, ... [表约束])][表选项][表结构或表记录源]列定义:列名列数据类型[长度和小数][空值][虚拟][键][注释][默认值][其他属性]创建表结构（2）Navicat工具创建表结构【例4.2】采用Navicat创建emarket数据库中商品分类表（category）表结构。在创建的连接下双击emarket数据库，再选择“表”，按右键，在快捷菜单中单击“新建表”，出现创建表窗口，输入内容如图4.1所示。单击“保存”按钮，在出现的“表名”对话框中输入“category”，单击“确定”，category表创建完成。在emarket数据库的“表”下就会出现“category”。创建表结构（3）显示表属性在表创建后，可以通过下面语句显示指定表的创建属性：SHOWCREATETABLE表名通过下面语句显示表属性或者表指定列的属性：DESC表名[列名]【例4.2续】显示category表属性。USEemarket;SHOWCREATETABLEcategory; #（a）DESCcategory; #（b）DESCcategory类别编号; #（c）显示category表“类别编号”列属性创建表结构显示结果分别如图4.2（a）、（b）、（c）所示。说明：（a）以创建表语句方式显示category表结构，虽然category表结构并不是语句方式创建的。通过该方式，可以了解采用Navicat创建表结构与创建表语句方式创建表结构的对应关系。（b）以列方式显示category表结构。（c）显示category表指定列结构。创建表结构2.删除表表结构创建后，可以修改表结构。有了表结构，就可以增加、修改和删除表记录。表记录的操作将在下一章系统介绍。下列语句可以删除表，删除表后表结构和表记录均不存在，一般用于重新定义表。DROPTABLEIFEXISTS表名;01列及其常用属性1.列名2.列数据类型3.长度和小数4.空值限制：NOTNULL/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；如果指定长度和小数位，只要在数据类型允许的范围内，整数部分显示的位数=长度-小数位数-1。（3）定点数类型（decimal）用于保存准确数字数据，长度和小数位可以根据用户需要指定。例如：支付金额可能达到xxxxx.xx，数据不大，但有效位数超过6位，为了准确表达，可以采用定点数类型decimal。支付金额 decimal(8.2)其他数据类型指定长度和小数位没有意义。列及其常用属性4.空值限制：NOTNULL/NULLNOTNULL表示列内容不允许为空，NULL或者不写此项表示允许为空。例如：商品名称 varchar(32) NOTNULL商品图片 blob NULLNavicat中勾选“不是null”项，表示非空（NOTNULL），否则表示允许空（NULL）。说明：（1）对于字符型，NULL并不是空格；对于数值型和位（bit）型，NULL并不是0。NULL值是一个值，是没有赋值的值，可以与NULL进行等于（=）比较。（2）如果列指定为“NULL”或者没有指定，当增加一行时，即使该列不指定值也没有默认值，该行也能够成功保存，并且该列保存为NULL值。（3）如果列指定为“NOTNULL”，当增加一行时，在非严格模式下，列名表NOTNULL列可以省略，若该列没有指定值也无默认值则被设置为该列数据类型的隐式默认值（数值类型为0，字符串类型为空白字符串''，日期和时间类型为相应格式的“零”值）；在严格模式下，列名表没有默认值的NOTNULL列不可以省略，且值不能为NULL。列及其常用属性5.虚拟Navicat中勾选该项，表示本列在表中并不直接存放内容，而是由其他列生成。关于虚拟列后面还会详细介绍。6.键列的键包括主键（PRIMARYKEY）和唯一键（UNIQUE[KEY]）。内容在列约束中详细介绍。7.注释：COMMENT注释仅仅是为了此后自己或者别人显示表结构时方便了解该列作用的说明内容，最多1024个字符。也可以用#后面跟注释内容例如：商品图片 blob COMMENT'图片不能大于64KB'02列

约

束1.主键约束：PRIMARYKEY2.唯一键约束：UNIQUE[KEY]3.完整性约束：CHECK列

约

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

供货商编号 char(2) NOTNULLPRIMARYKEY,

供货商名称 varchar(16) NOTNULL);复合主键约束就是需要一个以上的列才能唯一确定表记录。列

约

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

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

价格 float(6,2) NOTNULLDEFAULT0,

库存量 smallint(3) DEFAULT0);DESCtab_default1'价格'; #（a）INSERTINTOtab_default1VALUES();SELECT*FROMtab_default1; #（b）运行结果如图4.3所示：

列默认值2.显式表达式默认值除了常量，DEFAULT指定的默认值也可以是表达式。通常将表达式默认值括在括号内，以便与常量默认值区分开来。3.隐式默认值如果不包含显式默认值，对于NULL、NOTNULL、UTO_INCREMENT、时间戳以外的日期和时间、枚举以外等MySQL将按系统规则来确定默认值。04数值类型属性1.无符号：UNSIGNED2.默认值：DEFAULT值3.填充零：ZEROFILL4.自动递增：AUTO_INCREMENT数值类型属性1.无符号：UNSIGNED指定该列整数为“无符号”即不允许负值，否则系统错误。例如：库存量 smallint UNSIGNEDNOTNULL2.默认值：DEFAULT值列指定DEFAULT，当增加一行时，只要该列不指定值就采用指定的默认值作为列值。应该采用该列的常见初始值作为默认值。默认值必须为一个常数。例如，库存量默认值为0：库存量 smallint UNSIGNEDNOTNULLDEFAULT03.填充零：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';则可以在AUTO_INCREMENT列中将指定的0存储为0，而不生成新的序列值。（4）要在插入行之后检索AUTO_INCREMENT值，可以使用LAST_INSERT_ID()。（5）插入记录时，可以指定AUTO_INCREMENT列的值，后面插入记录时如果指定NULL值或者不指定该列，内容实际填在此基础上自增。（6）通过下列语句可以修改此后行记录AUTO_INCREMENT列起始值：ALTERTABLE表名AUTO_INCREMENT=值数值类型属性【例4.5】创建emarket数据库订单表（orders），其中“订单编号”列为AUTO_INCREMENT列。USEemarket;CREATETABLEorders(

订单编号 int NOTNULLPRIMARYKEYAUTO_INCREMENT,#（a）

帐户名 varchar(16) NOTNULL,

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

下单时间 datetime NOTNULL #（b）);说明：（a）订单编号：系统生成，不重复，值自增，正好符合从小到大编码规则。（b）下单时间：要求提供具体的日期时间且时间精确到秒，设为datetime型。05字符类型属性1.默认值：DEFAULT值2.字符集：CHARACTERSET字符集名3.排序规则：COLLATE排序规则名4.键长度：LEFT(列名,长度)字符类型属性1.默认值：DEFAULT值列指定默认值，默认值必须为一个字符串常数。例如：’ABC’。2.字符集：CHARACTERSET字符集名指定列的字符集。3.排序规则：COLLATE排序规则名指定列的字符集对应的排序规则。4.键长度：LEFT(列名,长度)如果选择指定为主键，默认情况下为该列的所有内容，但如果指定键长度，则将该列的前面键长度指定的内容作为主键。06虚拟列（生成列）虚拟列（生成列）CREATETABLE语句支持生成列，这种列的值是从列定义中包含的表达式计算而来，有时称为虚拟列。列名数据类型[GENERATEDALWAYS]AS(表达式) [VIRTUAL|STORED][属性][列约束]说明：（1）AS(表达式)：指示生成列并定义用于计算列值的表达式。（2）GENERATEDALWAYS可不写。表达式中不含AUTO_INCREMEN列。注意，表达式需要用加括号。（3）VIRTUAL或STORED关键字指示如何存储列值。VIRTUAL：虚拟生成列。它不存储列值，在任何BEFORE触发器之后立即计算列值。虚拟列不需要存储空间。如果没有指定关键字，则默认为VIRTUAL。STORED：存储生成列。在插入或更新行时计算和存储列值，它需要存储空间，并且可以被索引。虚拟列（生成列）关于生成列的使用还要注意以下几点：（1）生成列的定义也可以引用前面已经定义的生成列。（2）如果表达式计算的数据类型与声明的列类型不同，则根据通常的MySQL类型转换规则隐式强制转换为声明的类型。（3）外键约束不能引用生成列。（4）如果显式地对生成列执行插入、替换或更新，则唯一允许的值是默认值（DEFAULT）。生成列可用于简化和统一查询，可以将复杂条件定义为生成列，并从表上的多个查询引用它，以确保所有查询都使用完全相同的条件。（5）有些系统函数不能包含在生成表达式中。例如：表中包含身份证列，定义一个生成列年龄，通过CURDATE()函数得到当前日期与身份证中的出生日期相减+1得到年龄列值是不可以的。因为CURDATE()不是确定的值。虚拟列（生成列）【例4.6】创建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）);其中：（a）总价列是由价格和库存量相乘计算产生的，整体需要括起来；（b）商品列用内置的CONCAT()函数将“商品编号”和“商品名称”拼接起来，整体需要括起来。虚拟列（生成列）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;显示结果如图4.4所示。07表

约

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

约

束1.主键约束：PRIMARYKEY在列定义时通过PRIMARYKEY属性指定列为主键：列名数据类型...NOTNULLPRIMARYKEY当多列作为主键时，则需要通过表属性PRIMARYKEY项指定。组成主键的列均为NOTNULL。PRIMARYKEY(列名[DESC],...)如果PRIMARYKEY约束需要多列时，也可以另外定义一个自增列作为内部管理列，然后用该列作为主键。当然，可以表属性PRIMARYKEY项也可以定义一列为主键。表

约

束【例4.7】创建emarket数据库订单项表（orderitems），表上“订单编号”和“商品编号”构成联合主键。USEemarket;CREATETABLEorderitems(

订单编号 int NOTNULL,

商品编号 char(6) NOTNULL,

订货数量 int UNSIGNEDNOTNULL,

发货否 bit(1) NOTNULLDEFAULT0, PRIMARYKEY(订单编号DESC,商品编号) );说明：“订单编号”列后加DESC项，表示订单编号按照从大到小排列（加ASC，或者什么不写为从小到大排列），同一订单编号记录，按照商品编号从小到大排列。因为订单编号小的是以前的订单，而我们往往更关心当前新订单。表

约

束2.唯一键约束：UNIQUE[KEY]在列定义时通过UNIQUE属性指定列为唯一键：列名数据类型...UNIQUE当唯一键由多列组成时，需要表属性UNIQUE项指定。[CONSTRAINT约束名]UNIQUE[KEY](列名[DESC],...)组成唯一键的列可以包含NULL，但最多只在一行出现。唯一键约束在一个表中可以没有，也可以建立一个或多个。当然，表属性UNIQUE[KEY]也可以定义单列作为唯一键。表

约

束3.CHECK约束CHECK列约束限定的是列的内容，但对于多个列之间（部分）内容关系可以通过CHECK表约束来限定。当然CHECK列约束也可以通过CHECK表约束来实现。从MySQL8.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，则创建并强制约束；如果指定为NOTENFORCED，则创建约束但不强制。表

约

束【例4.8】创建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));表

约

束说明：（a）“手机号”列约束，约束条件为：手机号去除前后空格符仍然为11个字符，并且第1个字符为“1”。对于手机号还有其他条件，例如：全部为数字字符、打头的3位符合要求等等，这里仅仅表示一点意思。目前，实际应用手机号验证的方法是给提供的手机号发验证码，界面上提供文本框供用户输入，看文本框用户输入内容是否与发出的一致，这样才能从根本上验证手机号的正确性和真实性。（b）这里为表约束，因为包含2个列之间的约束，所以只能作为表约束，采用默认约束名。约束条件为：（身份证）有效期列与身份证号列中的出生年份（第7位开始的4位）相差20年以上。08表外键约束1.RESTRICT：限制2.CASCADE：级联3.SETNULL：置空4.NOACTION：无动作5.SETDEFAULT：置默认表外键约束在CREATETABLE或ALTERTABLE语句中，通过外键约束定义多表之间的关联，基本语法如下：[CONSTRAINT约束名]FOREIGNKEY(列名,...]) REFERENCES主表名(主表列名,...) [ONDELETE参考选项] [ONUPDATE参考选项]说明：（1）CONSTRAINT

约束名。如果没有给出这个子句，或者在CONSTRAINT关键字后面没有包含约束名，MySQL会自动生成一个外键约束名。约束名对于数据库和相同约束类型必须唯一，否则会导致错误。（2）FOREIGNKEY：表示这是建立在哪个（些）列上的外键约束。（3）REFERENCES：表示该外键所引用（关联）的是哪个表上的哪个（些）列。（4）参考选项为设定对表操作时与其相关联的表所要进行怎样的关联操作。MySQL提供以下参考选项：CASCADE|SETNULL|RESTRICT|NOACTION|SETDEFAULT表外键约束1.RESTRICT：限制该方式只要子表中有关联的记录，就拒绝对父表执行删除记录和更新关联列内容操作。【例4.9】创建一个商品目录表（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(类别编号));执行后采用Navicat在商品目录表（commodity_list）的“外键”选项页可看到该外键约束的各项信息，其中删除时和更新时为“RESTRICT”。如图4.5所示。表外键约束向商品目录表中插入3条测试数据：INSERTINTOcommodity_list(商品编号,类别编号,商品名称) VALUES ('1A0101','1A','洛川红富士苹果冰糖心10斤箱装'), ('1A0201','1A','烟台红富士苹果10斤箱装'), ('1B0501','1B','库尔勒香梨10斤箱装');执行后commodity_list表中的记录如图4.6所示。表外键约束（3）将category表中“类别编号”为“1B”的记录改为“B”：UPDATEcategorySET类别编号='B'WHERE类别编号='1B';显示UPDATE操作失败信息如图4.7所示。表外键约束2.CASCADE：级联这种方式删除或更新父表中的行，会自动删除或更新子表中匹配的行。【例4.9续】测试商品分类表（category）与商品目录表（commodity_list）的级联操作。（1）用Navicat将商品目录表（commodity_list）的外键参考选项都设为CASCADE，如图4.8所示。（2）现将category表中“类别编号”为“1B”的记录改为“B”。UPDATEcategorySET类别编号='B'WHERE类别编号='1B';commodity_list表中对应记录的类别编号也随之改变，如图4.9所示。

表外键约束（3）删除category表“类别编号”为“B”的记录。DELETEFROMcategoryWHERE类别编号='B';commodity_list表中对应记录也随之删除，如图4.10所示。（4）执行如下语句，将两表数据复原：INSERTINTOcategory(类别编号,类别名称)VALUES('1B','梨');INSERTINTOcommodity_list(商品编号,类别编号,商品名称) VALUES('1B0501','1B','库尔勒香梨10斤箱装');表外键约束3.SETNULL：置空删除或更新父表中的行，会将子表中的外键列设置为NULL。如果要指定SETNULL操作，请确保没有将子表中的外键列声明为NOTNULL。4.NOACTION：无动作与RESTRICT作用相同，拒绝对父表执行任何操作，不再重复举例。5.SETDEFAULT：置默认删除或更新父表中的行时，MySQL将子表中匹配行的对应外键列置为默认值，这个操作可以被MySQL解析器识别，但是InnoDB和NDB引擎并不支持，故该选项目前实际上不可用。09从老表创建新表结构从老表创建新表结构用户也可直接复制数据库中已有表的结构，用这种方式构建一个表，十分方便、快捷。CREATETABLE表名LIKE源表名说明：（1）使用LIKE关键字创建一个与“源表名”相同结构的新表，源表的列名、数据类型、是否空值、主键、默认值、索引、约束、分区等都将复制，但是源表的记录不会复制，因此创建的新表是一个空表。（2）使用AS关键字可以复制SELECT语句查询结果表，但源表的一些属性（如主键、生成列等）却不会复制。从老表创建新表结构【例4.10】在emarket数据库中，用复制的方式创建一个名为commodity_copy1的表，表结构同商品表（commodity）。USEemarket;CREATETABLEcommodity_copy1 LIKEcommodity;选择需要复制的表（例如：commodity），按右键，在快捷菜单中选择“复制表”→“结构”，系统生成一个新表，表结构（包括主键、索引、约束、外键、分区等）和记录与原表相同，但新表名在老表名后面加了“_copy1”（例如：commodity_copy1）。第4章

数据库及表结构设计——修改表结构修改表结构ALTERTABLE语句用于改变表的结构。ALTERTABLE表名

增删改列属性,...

修改表约束

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

列名[数据类型][属性][默认值]说明：对于使用MODIFY进行的列定义更改，必须包括数据类型和应用于新列的所有属性，而不包括PRIMARYKEY、UNIQUE等列约束。1）修改列属性，不能保留列原有的其他属性，必须重新全部指定。【例4.15】将表commodity_copy2的“库存量”列由smallint类型修改为int类型，同时加入该列上原来的其他属性。ALTERTABLEcommodity_copy2 MODIFY库存量intUNSIGNEDNOTNULLDEFAULT0;DESCcommodity_copy2库存量;运行结果如图4.14所示。修改列及其属性2）修改列类型，表中已有数据的情况。在使用MODIFY子句修改列类型时，若该列所存数据的类型与修改后的列数据类型不完全一致，MySQL会根据通常的类型转换规则隐式强制转换为新的类型。如果缩短字符串列，值可能会被截断；如果改变数值类型，精度可能丢失。【例4.16】将表commodity_copy2的“进货单价”列改成整型int。表commodity_copy2中已经有数据了，“进货单价”列包含两位小数，如图4.15所示。修改列及其属性执行修改语句：ALTERTABLEcommodity_copy2 MODIFY进货单价intNOTNULL;执行后结果如图4.16所示。修改列及其属性3）如果MODIFY定义的新类型与列的原类型完全不匹配，还是会出错。例如，将“商品类别”列改为整型，执行语句：ALTERTABLEcommodity_copy2MODIFY商品类别int(2);由于表中商品类别为char类型，与整型完全不匹配，系统会产生错误，如图4.17所示。修改列及其属性4.单独修改列默认值：ALTER[COLUMN]子句如果仅需要更改列的默认值，使用ALTER[COLUMN]子句可单独设定或删除默认值：ALTERTABLE表名 ALTER[COLUMN]列名{SETDEFAULT{literal|(表达式)}|DROPDEFAULT}说明：SETDEFAULT...|DROPDEFAULT：分别为列指定新的默认值或删除旧的默认值。如果删除了旧的默认值，并且列可以为空，则新默认值为空；如果该列不能为空，MySQL将分配一个默认值。【例4.17】将表commodity_copy2的“商品类别”列默认值改为“香蕉”，列的其他属性保留不变。ALTERTABLEcommodity_copy2 ALTER商品类别SETDEFAULT'香蕉';修改列及其属性5.既重命名又重定义：CHANGE子句若要更改列同时更改其名称和定义，使用CHANGE子句，它需要同时指定新老列名和新的定义，语法格式如下：ALTERTABLE表名 CHANGE老列名新列名[数据类型][属性][默认值]【例4.18】将表commodity_copy2的“进货单价”列更名为“单价”，数据类型改为decimal，长度为7取两位小数，非空；默认值为0。ALTERTABLEcommodity_copy2 CHANGE进货单价单价decimal(7,2)NOTNULLDEFAULT0;执行后表中数据如图4.18所示。修改列及其属性6.改变表中列的顺序：FIRST|AFTER若要对表中的列重新排序，可在MODIFY或者CHANGE语句中使用FIRST和AFTER，语法如下：ALTERTABLE表名 MODIFY列名1列1定义FIRST|AFTER列名2;或者ALTERTABLE表名 CHANGE列名1列名1列1定义FIRST|AFTER列名2修改列及其属性【例4.19】用MODIFY将commodity_copy2表中“商品类别”列置于所有列之后，再改到第1列。（1）“商品类别”列置于所有列之后。ALTERTABLEcommodity_copy2 MODIFY商品类别char(4)DEFAULT'苹果'AFTER商品图片;执行后的结果如图4.19所示。（2）“商品类别”列置于第1列。ALTERTABLEcommodity_copy2 MODIFY商品类别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的定义，需要写为：ALTERTABLEtest CHANGEbbintNOTNULL;如果列名称较长而复杂，这种写法很容易出错或遗漏，建议使用MODIFY子句。若要更改列名但不更改其定义，采用CHANGE，语法需要列定义，要保持原来的定义不变，必须重写一遍列当前的定义。例如，要将一个intNOTNULL列从b重命名为a，必须写成：ALTERTABLEtest CHANGEbaintNOTNULL;03添加和删除表约束1.添加删除主键约束：PRIMARYKEY2.添加删除唯一键约束：UNIQUE[KEY]3.添加删除CHECK约束4.添加删除外键约束：FOREIGNKEY添加和删除表约束1.添加删除主键约束：PRIMARYKEY（1）添加主键约束ALTERTABLE表名 ADD[CONSTRAINT[约束名]]PRIMARYKEY(键部分,...)主键既是区分表记录唯一性的约束，通过约束名标识该约束。同时它也是索引，索引名为“PRIMARYKEY”。例如：ALTERTABLEtest ADDPRIMARYKEY(列名)（2）删除主键约束ALTERTABLE表名 DROPPRIMARYKEY添加和删除表约束【例4.20】在commodity_copy2表上增加一个自增列，并将其作为主键。因为表上增加自增列需要作为主键，必须先删除原来的主键约束，然后添加自增列和主键约束。USEemarket;ALTERTABLEcommodity_copy2DROPPRIMARYKEY;ALTERTABLEcommodity_copy2ADD商品IDintAUTO_INCREMENTPRIMARYKEYFIRST,AUTO_INCREMENT=1000;DESCcommodity_copy2;其中，AUTO_INCREMENT=1000为表属性。运行结果如图4.21所示。添加和删除表约束2.添加删除唯一键约束：UNIQUE[KEY]（1）添加唯一键约束ALTERTABLE表名 ADD[CONSTRAINT[约束名]]UNIQUE[INDEX|KEY] [索引名][索引类型](键部分,...) [索引选项]唯一键也是区分表记录唯一性的约束，通过约束名标识该约束。同时它也是索引，通过索