单元3数据库和表的基本操作

数据库是数据库管理系统的基础与核心，是存放数据库对象的容器，数据库文件是数据库的存在形式。数据库管理就是设计数据库、定义数据库，以及修改和维护数据库的过程，数据库的效率和性能在很大程度上取决于数据库的设计和优化。本章将详细地讲解数据库和数据表的基本操作。
本单元主要学习目标如下： 
 掌握数据库的创建、查看、修改和删除等操作。
 掌握数据表的创建、查看、修改和删除等操作。
 了解MySQL的数据类型，掌握基本数据类型的使用。
 掌握表的约束，以及给表添加约束的命令。
3.1数据库的基本操作

3.1.1创建数据库
MySQL服务器中的数据库可以有多个，分别存储不同的数据。要想将数据存储到数据库中，首先需要创建数据库，这是使用MySQL各种功能的前提。启动并连接MySQL服务器，即可对MySQL数据库进行操作。
在MySQL中创建数据库的基本SQL语法格式如下。



CREATE DATABASE ［IF NOT EXISTS］ 数据库名;





参数说明如下。
（1） ［IF NOT EXISTS］： 可选子句，该子句可防止创建数据库服务器中已存在的新数据库的错误，即不能在MySQL服务器中创建具有相同名称的数据库。
（2） CREATE DATABASE： 数据库名，必选项，即要创建的数据库名称。建议数据库名称尽可能有意义，并且具有一定的描述性。创建数据库时，数据库命名的规则如下。
① 不能与其他数据库重名，否则将发生错误。
② 名称可以由任意字母、阿拉伯数字、下画线和“$”组成，可以使用上述任意字符开头，但不能使用单独的数字开头，否则会造成它与数值相混淆。
③ 名称最长可为64个字符。
④ 不能使用MySQL关键字作为数据库名。
⑤ 默认情况下，Windows下对数据库名的大小写不敏感； 而在Linux下对数据库名的大小写是敏感的。为了使数据库在不同平台间进行移植，建议采用小写的数据库名。
在创建完数据库后，MySQL会在存储数据的data目录中创建一个与数据库同名的子目录，同时，会在该子目录下生成一个db.opt文件，用于保存数据库选项。




【例31】创建名为library的数据库。
(1) 直接使用CREATE DATABASE语句创建，SQL语句如下。



CREATE DATABASE library;





执行结果如图31所示。


图31直接使用CREATE DATABASE

语句创建数据库

(2) 使用含IF NOT EXISTS子句的CREATE DATABASE语句创建，SQL语句如下。



CREATE DATABASE IF NOT EXISTS library;






SQL语句执行后显示“OK”，说明语句执行成功，数据库已经创建了。
3.1.2查看数据库
在MySQL中，成功创建数据库后，可以使用SHOW DATABASES语句显示MySQL服务器中的所有数据库。语法格式如下。



SHOW DATABASES;





使用该命令可以查询在MySQL中已经存在的所有数据库。
【例32】在例31中，我们创建了数据库library，现在使用命令查看MySQL服务器中的所有数据库。SQL语句如下。



SHOW DATABASES;





执行结果如图32所示。


图32使用SHOW DATABASES

语句查看数据库

从图32中可以看出，目前在MySQL服务器上存在着五个数据库，其中，除了例31创建的library数据库之外，还有information_schema、mysql、performance_schema、sys这四个数据库，这四个数据库都是在MySQL安装完成后由系统自动创建的。
(1) information_schema是信息数据库，存储着MySQL数据库服务器所维护的所有其他数据库的信息。在information_schema数据库中，有几个只读表。它们实际上是视图，而不是基本表，因此，用户无法看到与之相关的任何文件。
(2) mysql是MySQL的核心数据库，类似于SQL Server中的master表，主要负责存储数据库的用户、权限设置、关键字等控制和管理信息。mysql数据库中的数据不可以删除，否则，MySQL将不能正常运行。如果对mysql数据库不是很了解，不要轻易修改这个数据库里的信息。
(3) performance_schema数据库主要用于收集数据库服务器性能参数。该数据库中所有表的存储引擎均为performance_schema，而用户是不能创建存储引擎为performance_schema的表的。
(4) library是安装时创建的一个测试数据库，是一个空数据库，其中没有任何表，可以删除。
要想查看某个已经创建的数据库信息，可以通过SHOW CREATE DATABASE语句实现，具体语法格式如下。



SHOW CREATE DATABASE数据库名称;





【例33】查看创建好的数据库library的信息，SQL语句如下。

SHOW CREATE DATABASE library;

执行之后，输出结果显示了数据库library的创建信息及其编码方式。
3.1.3选择数据库
上面虽然成功创建了数据库library，但并不表示当前就可以使用数据库library。在使用指定数据库之前，必须通过使用USE语句告诉MySQL要使用哪个数据库，使其成为当前默认数据库。其语法格式如下。



USE数据库名;





【例34】选择名称为library的数据库，设置其为当前默认的数据库。使用USE语句选择数据库library，SQL语句如下。



USE library;





运行结果如图33所示。


图33选择名称为library的数据库

3.1.4修改数据库
数据库创建之后，数据库编码方式就确定了。修改数据库的编码方式，可以使用ALTER DATABASE语句，具体语法格式如下。



ALTER DATABASE 数据库名称DEFAULT CHARACTER SET编码方式COLLATE编码方式_bin;





其中,“数据库名称”是要修改的数据库的名字，“编码方式”是修改后的数据库编码方式。
【例35】将数据库library的编码方式修改为gbk，SQL语句如下。



ALTER DATABASE library DEFAULT CHARACTER SET gbk COLLATE gbk_bin;






为了验证数据库的编码方式是否修改成功，可以使用例33中的SHOW CREATE DATABASE语句查看修改后的数据库。
3.1.5删除数据库
删除数据库可以使用DROP DATABASE命令，具体语法格式如下。



DROP DATABASE数据库名称;





其中,“数据库名称”是要删除的数据库的名字。需要注意的是，如果要删除的数据库不存在，则会出现错误。
【例36】删除名为company的数据库。SQL语句如下。



DROP DATABASE company;







图34DROP DATABASE语句执行结果

执行结果如图34所示。
为了验证数据库是否删除成功，可以使用SHOW DATABASES语句查看当前MySQL数据库服务器上的所有数据库，当前MySQL数据库服务器中已经不存在company数据库了，表明删除成功。
注意： 在使用DROP DATABASE删除数据库时，若待删除的数据库不存在，MySQL服务器会报错。值得一提的是，在执行删除数据库操作之前，一定要备份需要保留的数据，确保数据的安全，避免误操作造成严重后果。
注意： MySQL 中单行注释以“＃”开始标识，也支持标准SQL中“”单行注释。但是为了防止“”与SQL语句中负号和减法运算的混淆，在第二个短横线后必须添加至少一个控制字符(如空格、制表符、换行符等)将其标识为单行注释符号。示例如下。




＃此处填写单行注释内容，如： 若服务器中没有mydb数据库，则创建，否则忽略此SQL

CREATE DATABASE IF NOT EXISTS mydb;

--此处填写单行注释内容，如： 若服务器中存在mydb数据库，则删除，否则忽略此SQL

DROP DATABASE IF EXISTS mydb;






同样地，MySQL也支持标准SQL中的多行注释“／*此处填写注释内容*／”，它的开始符号为“／*”，结束符号为“*／”，中间的内容就是要编写的注释。示例如下。



/*

此处填写多行注释内容

如： 利用以下SQL查看当前服务器中的所有数据库

*／

SHOW DATABASES;






在开发中编写的SQL语句，建议合理地添加单行或多行注释，方便阅读与理解。
在MySQL使用的过程中，它相关的基本语法有以下三点需要注意的地方。
(1) 换行、缩进与结尾分隔符。MySQL中的SQL语句可以单行或多行书写，多行书写时可以按Enter键换行，每行中的SQL语句可以使用空格和缩进增强语句的可读性，在SQL语句完成时通常情况下使用分号(；)结尾，在命令行窗口中也可使用“＼g”结尾，效果与分号相同。另外，在命令行窗口中，还可以使用“＼G”结尾，如SHOW DATABASES＼G将显示结果以每条记录(一行数据)为一组，将所有的字段纵向排列展示。
(2) 大小写问题。MySQL的关键字在使用时不区分大小写，如SHOW DATABASES与 show databases都表示获取当前MySQL服务器中有哪些数据库。另外，MySQL中的所有数据库名称、数据表名称、字段名称默认情况下在Windows系统下都忽略大小写，在Linux系统下数据库与数据表名称则区分大小写，通常开发时推荐都使用小写。
(3) 反引号的使用。在项目开发中，为了避免用户自定义的名称与系统中的命令(如关键字)冲突，最好使用反引号(``)包裹数据库名称、字段名称和数据表名称。其中，反引号(`)在键盘中左上角Tab键的上方，读者只需将输入法切换到英文，按下此键即可输入反引号(`)。
3.2数据类型
为字段选择合适的数据类型对数据库的优化非常重要。MySQL支持多种数据类型，大致可以分为四类： 数值类型、日期和时间类型、字符串(字符)类型和二进制类型。
1.  数值类型
MySQL支持所有标准SQL数值类型，包括精确数值类型(INTEGER、SMALLINT和DECIMAL)和近似数值类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的简写，关键字DEC是DECIMAL的简写。
作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。表31列出了MySQL每种数值类型占用的字节数、范围以及用途。
从表31中可以看出，不同数值类型所占用的字节数和取值范围都是不同的。其中,定点数类型DECIMAL的有效取值范围由M和D决定，M表示整个数据的位数，不包括小数点； D表示小数点后数据的位数。例如，将数据类型为DECIMAL(5,3)的数据3.1415插入数据库，显示的结果为3.142。


表31数值类型


类型大小范围(有符号)范围(无符号)用途


TINYINT1b(-128，127) (0，255) 小整数值 
SMALLINT2b(-32 768，32 767) (0，65 535) 大整数值 
MEDIUMINT3b(-8 388 608，8 388 607) (0，16 777 215) 大整数值 
INT或
INTEGER4b(-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值 
BIGINT8b(-9 223 372 036 854 775 808，
9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值 
FLOAT4b(-3.402 823 466 E+38，
1.175 494 351 E-38) (1.175 494 351 E-38，
3.402 823 466 E+38) 单精度浮点数值 
DOUBLE8b(-1.797 693 134 862 315 7 E+308，
-2.225 073 858 507 201 4 E-308)(2.225 073 858 507 201 4 E-308，
1.797 693 134 862 315 7 E+308) 双精度浮点数值 
DECIMALM+2(-1.797 693 134 862 315 7 E+308，
-2.225 073 858 507 201 4 E-308)(2.225 073 858 507 201 4 E-308，
1.797 693 134 862 315 7 E+308) 小数值

创建表时，选择数字类型应遵循以下原则。
(1) 选择最小的可用类型，如果值永远不超过127，则使用TINYINT比使用INT强。
(2) 对于完全都是数字的，可以选择整型数据。
(3) 浮点型数据用于可能具有小数部分的数，如货物单价、网上购物支付金额等。
注意： 
(1) 在选择数据类型时，若一个数据将来可能参与数学计算，推荐使用整数、浮点数或定点数类型； 若只用来显示，则推荐使用字符串类型。例如，商品库存可能需要增加、减少、求和等，所以保存为整数类型； 用户的身份证、电话号码一般不需要计算，可以保存为字符串类型。
(2) 表的主键推荐使用整数类型，与字符串类型相比，整数类型的处理效率更高，查询速度更快。
(3) 当插入的值的数据类型与字段的数据类型不一致，或使用ALTER TABLE修改字段的数据类型时，MySQL会尝试尽可能将现有的值转换为新类型。例如，字符串'123'、'-123'、'1.23'与数字123、-123、1.23可以互相转换； 1.5转换为整数时，会被四舍五入，结果为2。
2.  日期和时间类型
表示日期和时间值的日期和时间类型有DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个“零”值，当输入不合法的值时，MySQL使用“零”值插入。TIMESTAMP类型具备专有的自动更新特性。表32列举了MySQL中日期和时间类型所对应的字节数、范围、格式以及用途。
其中，DATE类型用于表示日期值，不包含时间部分。在MySQL中，DATE类型常用的字符串格式为： "YYYYMMDD"或者"YYYYMMDD"。
例如，输入“20210124”或者“20210124”，插入数据库的日期均为20210124。
TIME类型用于表示时间值，它的显示形式一般为HH:MM:SS，其中，HH表示小时，MM表示分，SS表示秒。


表32日期和时间类型


类型大小范围格式用途



DATE3b10000101~99991231 YYYYMMDD 日期值 
TIME3b'838:59:59'~'838:59:59' HH:MM:SS 时间值或持续时间 
YEAR1b1901~2155 YYYY 年份值 
DATETIME8b10000101 00:00:00~99991231 23:59:59 YYYYMMDD HH:MM:SS 混合日期和时间值 
TIMESTAMP4b19700101 00:00:00~2038 

结束时间是第2147483647秒，北京时间2038119 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS 混合日期和时间值，时间戳 


例如，输入“115253”，插入数据库中的时间为11:52:53。
YEAR类型用于表示年。在MySQL中，常使用四位字符串或数字表示，对应的字符串的范围为'1901'~'2155'，数字范围为1901~2155。
例如，输入“2021”或2021，插入到数据库中的值均为2021。
DATETIME类型用于表示日期和时间，它的显示形式为“YYYYMMDD HH:MM:SS”，其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分，SS表示秒。
例如，输入“20210124 08:23:52”或“20210124082352”，插入数据库中的DATETIME类型的值均为20210124 08:23:52。
TIMESTAMP类型用于表示日期和时间，它的显示形式与DATETIME类型相同，但取值范围比DATETIME类型小。当TIMESTAMP类型的字段输入为NULL时，系统会以当前系统的日期和时间填入。当TIMESTAMP类型的字段无输入时，系统也会以当前系统的日期和时间填入。
3.  字符串类型和二进制类型
为了存储字符串、图片和声音等数据，MySQL提供了字符串和类型二进制类型。表33列举了这些数据类型的取值范围和用途。


表33字符串类型和二进制类型


类型大小用途


CHAR（n）0~255字符定长字符串，n为字符串的最大长度。若输入数据的长度超过了n值，超出部分将会被截断； 否则，不足部分用空格填充。例如，对于CHAR（4），若插入值为'abc'，则其占用的存储空间为4字节
VARCHAR（n）0~65536字符变长字符串,n为字符串的最大长度。占用字节数随输入数据的实际长度而变化，最大长度不得超过n+1。例如，VARCHAR（4），若插入值为'abc'，则其占用的存储空间为4字节，若插入值为'abcd'，则其占用的存储空间为5字节
续表


类型大小用途


BINARY（n）0~255字节固定长度的二进制数据，n为字节长度，若输入数据的字节长度超过了n值，超出部分将会被截断； 否则，不足部分用字符'＼0填充。例如，对BINARY（3），插入值为'a＼0'时变成'a＼0＼0'值存入
VARBINARY（n）0~65536字节可变长度的二进制数据，n为字节长度
ENUM1~65535个值枚举类型，语法格式为： ENUM（'值1','值2',…,'值n'）。该类型的字段值只能为枚举值中的某一个。例如，性别字段数据类型可以设为ENUM（'男'，'女'）
SET1~64个值集合类型，语法格式为： SET（'值1','值2'，…，'值n'）。例如，人的兴趣爱好字段的数据类型可以设为SET（'听音乐'，'看电影'，'购物'，'游泳','旅游'），该字段的值从集合中取值，且可以取多个值
BIT（n）1~64位位字段类型。如果输入的值的长度小于n位，在值的左边用0填充。例如，为BIT（6）分配值'101'的效果与分配值'000101'的效果相同
TINYBLOB0~255字节不超过255个字符的二进制字符串
BLOB0~65535字节二进制形式的文本数据，主要存储图片、音频等信息
MEDIUMBLOB0~16777215字节二进制形式的中等长度文本数据
LONGBLOB0~4294967295字节二进制形式的极大长度文本数据
TINYTEXT0~255字节短文本字符串
TEXT0~65535字节文本数据。如新闻内容、博客、日志等
MEDIUMTEXT0~16777215字节中等长度文本数据
LONGTEXT0~4294967295字节极大长度文本数据

CHAR(n)为固定长度的字符串，在定义时指定字符串的长度最大为n个字符个数。当保存时，MySQL会自动在右侧填充空格，以达到其指定的长度。例如，CHAR(8)定义了一个固定长度的字符串列，字符个数最大为8。当检索到CHAR值时，尾部的空格将被删除。
VARCHAR(n)为可变长度的字符串。VARCHAR的最大实际长度L由最长行的大小和使用的字符集确定，其实际占用的存储空间为字符串实际长度L加1。例如，VARCHAR(50)定义了一个最大长度为50的字符串列，如果写入的实际字符串只有20个字符，则其实际存储的字符串为20个字符和一个字符串结束字符。保存和检索VARCHAR的值时，其尾部的空格仍然保留。
CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和尾部空格是否被保留等也不同。在存储或检索过程中不进行大小写转换。TEXT类型用于保存非二进制字符串，如文章的内容、评论等信息。当保存或检索TEXT列的值时，不会删除尾部空格。
TEXT类型有四种： TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。不同的TEXT类型的存储空间和数据长度都不同。
MySQL中的二进制字符串数据类型有BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。
BINARY和VARBINARY类似于CHAR和VARCHAR，不同的是，它们包含二进制字符串，而不要非二进制字符串。也就是说，它们包含字节字符串，而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值。
BLOB是一个二进制大对象，可以容纳可变数量的数据。有四种BLOB类型： TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们的区别是可容纳的存储范围不同。
创建表时，使用字符串类型时应遵循以下原则。
(1) 从速度方面考虑，要选择固定的列，可以使用CHAR类型。
(2) 要节省空间，使用动态的列，可以使用VARCHAR类型。
(3) 要将列中的内容限制为一种选择，可以使用ENUM类型。
(4) 允许在一列中有多个条目，可以使用SET类型。
(5) 如果要搜索的内容不区分大小写，可以使用TEXT类型。
(6) 如果要搜索的内容区分大小写，可以使用BLOB类型。
3.3数据表的基本操作

3.3.1创建数据表
创建完数据库并熟悉了MySQL支持的数据类型后，接下来的工作是创建数据表。创建数据表其实就是在已经创建好的数据库中建立新表。
数据表属于数据库，在创建数据表之前，应该使用语句“USE<数据库名>”指定操作是在哪个数据库中进行。如果没有选择数据库，MySQL会抛出No database selected的错误提示。
创建数据表的语句为CREATE TABLE，语法规则如下。



CREATE TABLE数据表名称 

(

字段名1数据类型 ［完整性约束条件］ ［默认值］, 

字段名2数据类型 ［完整性约束条件］ ［默认值］, 　　 

… 　

字段名n数据类型 ［完整性约束条件］ ［默认值］, 

);






在上述语法格式中，“数据表名称”是创建的数据表的名字，“字段名”是数据表的列名，“完整性约束条件”是字段的特殊约束条件。关于表的约束将在3.4节进行详细讲解。
使用CREATE TABLE创建表时，必须指定以下信息。
(1) 数据表名不区分大小写，且不能使用SQL中的关键字，如DROP、INSERT等。
(2) 如果数据表中有多个字段(列)，字段(列)的名称和数据类型要用英文逗号隔开。
【例37】在library数据库中创建一个用于存储图书信息的books表，其结构如表34所示。


表34books表结构


字段名数 据 类 型备 注 说 明


Bookidchar(6)图书编号
Booknamevarchar(50)书名
Authorvarchar(50)作者
Pressvarchar(40)出版社
Pubdatedate出版日期
Typevarchar(20)类型
Numberint(2)库存数量
Infovarchar(255)简介

使用library数据库，SQL语句如下。



USE library;





接下来创建books表，SQL语句如下。



CREATE TABLE books

(

Bookid char(6),

Bookname varchar(50),

Author varchar(50),

Press varchar(40),

Pubdate date,

Type varchar(20),

Number int(2),

Info varchar(255)

);






执行语句后，便创建了一个名称为books的数据表，使用SHOW TABLES语句查看数据表是否创建成功，SQL语句如下。



SHOW TABLES;





运行结果如图35所示。


图35使用SHOW TABLES语句查看数据表


从图35中可以看到，library数据库中已经有了数据表books，表明数据表创建成功。
3.3.2查看数据表
创建好数据表之后，可以查看数据表，以确认其定义是否正确。在MySQL中，查看数据表的方式有以下两种。
1.  使用SHOW CREATE TABLE语句查看数据表
语法格式如下。



SHOW CREATE TABLE数据表名称;





其中，“数据表名称”是要查看的数据表的名字。
【例38】使用SHOW CREATE TABLE语句查看books表。SQL语句如下。



SHOW CREATE TABLE books;






执行结果如图36所示。


图36使用SHOW CREATE TABLE语句查看books表的详细结构


2.  使用DESCRIBE语句查看数据表
使用DESCRIBE语句查看数据表，可以查看到数据表的字段名、类型、是否为空、是否为主键等信息。语法格式如下。



DESCRIBE表名;





或者使用简写，语法格式如下。



DESC表名;






【例39】使用DESCRIBE语句查看books表。SQL语句如下。



DESCRIBE books;






执行结果如图37所示。


图37使用DESCRIBE语句查看books表的基本结构


其中： 
(1) Field表示该表的字段名。
(2) Type表示对应字段的数据类型。
(3) Null表示对应字段是否可以存储NULL值。
(4) Key表示对应字段是否编制索引和约束。
(5) Default表示对应字段是否有默认值。
(6) Extra表示获取到的与对应字段相关的附加信息。
3.3.3修改数据表
数据表创建之后，用户还可以对表中的某些信息进行修改，修改表是指修改数据库中已经存在的数据表结构。MySQL使用ALTER TABLE语句修改表。常用的修改表的操作有： 修改表名、修改字段名和数据类型、修改字段的数据类型、添加字段、删除字段、修改字段的排列位置等。下面对这些操作进行详细介绍。
1.  修改表名
MySQL可以通过ALTER TABLE语句实现对表名的修改，语法格式如下。



ALTERTABLE 旧表名 RENAME［TO］ 新表名;






其中，关键字TO是可选的，使用与否都不影响运行结果。
【例310】将数据库library中books表的表名改为tb_books。
修改表名之前，先用SHOW TABLES语句查看数据库中的表，结果如图38所示。
执行下述命令，将books表名改为tb_books。　　



ALTER TABLE books RENAME tb_books;





上述命令执行成功后，再用SHOW TABLES语句查看数据库中的表，结果如图39所示。



图38使用SHOW TABLES语句查看所有表结果




图39使用SHOW TABLES语句查看ALTER TABLE语句执行结果



从图39中可以看出，数据库library中的表名books已经被成功修改为tb_books。
2.  修改字段名和数据类型
语法格式如下。



ALTER TABLE表名CHANGE旧字段名 新字段名 新数据类型;





其中，“旧字段名”是修改之前的字段名称，“新字段名”是修改之后的字段名称，“新数据类型”是修改后的数据类型。注意，修改后的数据类型不能为空。如果只修改字段名，不修改数据类型，可以将新数据类型写为字段原来的数据类型。
【例311】将tb_books表中的Author字段改名为bookAuthor，数据类型保持不变。修改字段之前，用DESC tb_books语句查看表的信息，执行结果如图310所示。


图310执行修改字段名前查看表结构


执行下述命令，将tb_books表中的Author字段改名为bookAuthor。



ALTER TABLE tb_books CHANGE Author bookAuthor  varchar(50);





执行结果如图311所示。


图311执行修改字段名后查看表结构


从图311中可以看出，tb_books表中的Author字段成功改名为bookAuthor。
3.  修改字段的数据类型
语法格式如下。



ALTER TABLE 表名 MODIFY 字段名 新数据类型;





【例312】将tb_books表中的Bookname字段的数据类型由varchar(50)修改为varchar(100)。执行修改命令之前，先用DESC tb_books语句查看tb_books表的结构，如图312所示。


图312执行修改字段数据类型前查看表结构


执行修改命令，SQL语句如下。



ALTER TABLE tb_books MODIFY Bookname varchar(100);






命令执行成功后，再查看一下tb_books表的结构，结果如图313所示。


图313执行修改字段数据类型后查看表结构


从图313中可以看出，Bookname字段的数据类型已经由varchar(50)修改为varchar(100)。
4.  添加字段
语法格式如下。



ALTER TABLE表名ADD 新字段名 数据类型

［约束条件］ ［FIRST|AFTER已经存在的字段名］;





其中，“新字段名”是新添加的字段名称，“FIRST”是可选参数，用于将新添加的字段设置为表的第一个字段，“AFTER已经存在的字段名”也是可选参数，用于将新添加的字段添加到指定字段的后面。如不指定位置，则默认将新添加字段追加到表末尾。
【例313】在数据表tb_books中的Press字段后添加一个int类型的字段column1。SQL语句如下。



ALTER TABLE tb_books ADD column1 int AFTER Press;





为了验证column1字段是否添加成功，使用DESC语句查看tb_books表的结构，执行结果如图314所示。


图314执行添加字段后查看表结果


从图314中可以看出，在tb_books表中已经成功添加了cloumn1字段，数据类型为int。
5.  删除字段
语法格式如下。



ALTER TABLE 表名 DROP 字段名;





【例314】删除tb_books表中的cloumn1字段。SQL语句如下。



ALTER TABLE tb_books DROP cloumn1;





为了验证cloumn1字段是否删除成功，使用DESC语句查看tb_books表的结构，执行结果如图315所示。


图315执行删除字段后查看表结构结果


从图315中可以看出，tb_books表中的cloumn1字段已经不存在了。
6.  修改字段的排列位置
在创建一个数据表的时候，字段的排列位置就已经确定了，但这个位置并不是不能改变的，可以使用ALTER TABLE改变指定字段的位置，语法格式如下。



ALTER TABLE 表名 MODIFY 字段名1 新数据类型 FIRST|AFTER 字段名2;






其中,“FIRST”是可选参数，用于将“字段名1”设置为表的第一个字段，“AFTER字段名2”也是可选参数，用于将“字段名1”移动到“字段名2”的后面。此命令可以同时修改字段的数据类型和位置。如果只修改位置，不修改数据类型，可以将新数据类型写为字段原来的数据类型。
【例315】将tb_books表中的Press字段修改为表中的第一个字段。
SQL语句如下。



ALTER TABLE tb_books MODIFY Press varchar(40) FIRST;





使用DESC语句查看tb_books表的结构，执行结果如图316所示。


图316执行修改Press字段为第一个字段后查看表结果


从图316中可以看出，Press字段已经被修改为表的第一个字段了。
【例316】将tb_books表中的Press字段移动到bookAuthor字段之后，并将数据类型修改为tinyint。
SQL语句如下。



ALTER TABLE tb_books MODIFY Press tinyint AFTER bookAuthor;






使用DESC语句查看tb_books表的结构，执行结果如图317所示。


图317执行移动Press字段后查看表结果


从图317中可以看出，tb_books表中的Press字段已经成功移动到bookAuthor字段之后，并且数据类型已被修改为tinyint。
3.3.4删除数据表
删除数据表是指删除数据库中已经存在的表，同时，该数据表中的数据也会被删除。注意，一般数据库中的多个数据表之间可能会存在关联，要删除具有关联关系的数据表比较复杂，这种情况将在后续章节介绍。本节只涉及没有关联关系的数据表的删除。
删除数据表的语法格式如下。



DROP TABLE 表名； 





【例317】删除tb_books表。
SQL语句如下。



DROP TABLE tb_books;





为了验证tb_books表是否删除成功，使用DESC语句查看，执行结果如图318所示。


图318使用DESC语句查看DROP TABLE语句执行结果


从图318中可以看出，tb_books表已经不存在了，说明已被成功删除。
3.4数据表的约束
为了防止数据表中插入错误的数据，MySQL定义了一些维护数据库完整性的规则，即表的约束，约束用来确保数据的准确性和一致性。数据的完整性就是对数据的准确性和一致性的一种保证。常见的表约束有以下五种： 主键约束(PRIMARY KEY CONSTRAINT)、外键约束(FOREIGN KEY CONSTRAINT)、非空约束(NOT NULL CONSTRAINT)、唯一约束(UNIQUE CONSTRAINT)和默认约束(DEFAULT CONSTRAINT)。
3.4.1主键约束
主键又称主码，由表中的一个字段或多个字段组成，能够唯一地标识表中的一条记录。主键约束要求主键字段中的数据唯一，并且不允许为空。主键分为两种类型： 单字段主键和复合主键。
注意，每个数据表中最多只能有一个主键。
1.  单字段主键
(1) 创建表时指定主键，语法格式如下。



字段名数据类型PRIMARY KEY ;





【例318】创建tb_books表，并设置Bookid字段为主键。SQL语句如下。



CREATE TABLE tb_books

(

Bookid char(6)PRIMARY KEY,

Bookname varchar(50),

Author varchar(50),

Press varchar(40),

Pubdate date,

Type varchar(20),

Number int(2),

Info varchar(255)

);






执行上述命令之后，用DESC语句查看tb_books表的结构，执行结果如图319所示。


图319设置Bookid为主键后的表结构


从图319中可以看出，Bookid字段的“Key”显示为PRI，表示此字段为主键。
(2) 为已存在的表添加主键约束，语法格式如下。



ALTER TABLE表名MODIFY字段名 数据类型PRIMARY KEY;





【例319】将tb_books表的Bookid字段修改为主键。
首先将前面创建的tb_books表删除，再新建tb_books表，SQL语句如下。



DROP TABLE tb_books;

CREATE TABLE tb_books

(

Bookid char(6) ,

Bookname varchar(50),

Author varchar(50),

Press varchar(40),

Pubdate date,

Type varchar(20),

Number int(2),

Info varchar(255)

);






执行上述命令之后，用DESC语句查看tb_books表的结构，执行结果如图320所示。


图320删除并创建表tb_books执行结果


接下来，使用ALTER语句将Bookid字段修改为主键，SQL语句如下。



ALTER TABLE tb_books MODIFY Bookid char(6) PRIMARY KEY;





为了验证Bookid字段的主键约束是否添加成功，再次使用DESC语句查看tb_books表的结构，执行结果如图321所示。


图321使用ALTER语句修改表主键执行结果


从图321中可以看出，Bookid字段的“Key”显示为PRI，表示此字段为主键。
(3) 删除主键约束，语法格式如下。



ALTER TABLE表名DROP PRIMARY KEY;





【例320】删除tb_books表的Bookid字段的主键约束。SQL语句如下。



ALTER TABLE tb_books DROP PRIMARY KEY;






为了验证Bookid字段的主键约束是否删除，使用DESC语句查看tb_books表的结构，执行结果如图322所示。


图322删除主键约束执行结果


从图322中可以看出，Bookid字段的“Key”为空，表示此字段已经不是主键了。
2.  复合主键
复合主键是指主键由多个字段组成。
(1) 创建表时指定复合主键，其语法格式如下。



PRIMARY KEY (字段名1, 字段名2,…,字段名n);





其中,“字段名1,字段名2,…,字段名n”指的是构成主键的多个字段的名称。
【例321】创建sales表，设置product_id、region_code字段为复合主键。SQL语句如下。



CREATE TABLE sales

(

product_id int(11),

region_code varchar(10),

quantity int(11),

price float,

PRIMARY KEY (product_id,region_code)

);






执行上述命令之后，用DESC语句查看sales表的结构，执行结果如图323所示。


图323指定复合主键执行结果


从图323中可以看出，product_id字段和region_code字段的“Key”均显示为PRI，表示这两个字段共同作为主键。
(2) 为已存在的表添加复合主键，语法格式如下。



ALTER TABLE 表名 ADDPRIMARY KEY (字段名1, 字段名2,…,字段名n);





【例322】将sales表的product_id字段和region_code字段作为复合主键。
首先将前面创建的sales表删除，再新建sales表。SQL语句如下。



DROP TABLE sales;

CREATE TABLE sales

(

product_id int(11),

region_code varchar(10),

quantity int(11),

price float,

);






执行上述命令之后，用DESC语句查看sales表的结构，执行结果如图324所示。
接下来，使用ALTER语句将sales表的product_id字段和region_code字段设为复合主键。SQL语句如下。



ALTER TABLE sales ADD PRIMARY KEY (product_id, region_code);





为了验证product_id字段和region_code字段作为复合主键是否添加成功，再次使用DESC语句查看sales表的结构，执行结果如图325所示。



图324删除并新建sales表执行结果





图325使用ALTER语句修改sales表主键

执行结果



(3) 删除复合主键约束，语法格式如下。



ALTER TABLE表名DROP PRIMARY KEY;





3.4.2外键约束
外键在两个表的数据之间建立关联，它可以是一个字段或者多个字段。一个表可以有一个或者多个外键。一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。注意，关联指的是在关系数据库中，相关表之间的联系。它是通过相同或者相容的字段或字段组来表示的。从表的外键必须关联主表的主键，且关联字段的数据类型必须匹配。
定义外键后，不允许在主表中删除与子表具有关联关系的记录。
主表(父表)： 对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即主表。
从表(子表)： 对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即从表。
1.  创建表时添加外键约束
语法格式如下。



CONSTRAINT 外键名 FOREIGN KEY (从表的外键字段名) REFERENCES 主表名 (主表的主键字段名)





其中，“外键名”是指从表创建的外键约束的名字。
【例323】定义借书表borrow，其表结构如表35所示； 在borrow表上创建外键约束，其中图书表books为主表，borrow表为从表。


表35borrow表结构


序号列名数据类型备注


1Borrowidchar(6)借书记录号
2Borrowbookidchar(6)图书编号
3Borrowreaderidchar(6)借书证号
4Borrowdatedatetime借阅时间
5Borrownumint(2)借阅册数

定义数据表borrow，让它的键Borrowbookid作为外键关联到books表的主键Bookid，SQL语句如下。



CREATE TABLE borrow

(

Borrowidchar(6) PRIMARY KEY,

Borrowbookid char(6),

Borrowreaderid char(6),

Borrowdate datetime,

Borrownum int(2)，

CONSTRAINT fk_bks_brw FOREIGN KEY(Borrowbookid) REFERENCES books(Bookid)

)ENGINE=InnoDB;






执行上述命令之后，使用SHOW CREATE TABLE语句查看books表的结构，执行结果如图326所示。


图326使用SHOW CREATE TABLE语句查看books表执行结果


使用SHOW CREATE TABLE语句查看borrow表的结构，执行结果如图327所示。


图327使用SHOW CREATE TABLE语句查看borrow表执行结果


从图327中可以看出，已经成功地创建了books表和borrow表的主外键关联。要特别注意，主表books的主键字段Bookid和从表borrow的外键字段Borrowbookid的数据类型必须兼容或者一致，且含义一样。在创建表时创建表的主外键关联，必须先创建主表，再创建从表。
2. 为已存在的表添加外键约束
语法格式如下。



ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表的外键字段名) REFERENCES 主表名(主表的主键字段名);





其中，“外键名”是指从表创建的外键约束的名字。
【例324】已存在图书表books和借阅表borrow，为借阅表borrow创建外键。
（1） 删除borrow表和books表，SQL语句如下。



DROP TABLEborrow;

DROP TABLEbooks;





（2） 先创建主表books,SQL语句如下。



CREATE TABLE books

(

Bookid char(6)PRIMARY KEY,

Bookname varchar(50),

Author varchar(50),

Press varchar(40),

Pubdate date,

Type varchar(20),

Number int(2),

Info varchar(255)

)ENGINE=InnoDB;






（3） 再创建从表borrow，SQL语句如下。



CREATE TABLE borrow

(







Borrowidchar(6) PRIMARY KEY,

Borrowbookid char(6),

Borrowreaderid char(6),

Borrowdate datetime,

Borrownum int(2)

)ENGINE=InnoDB;






执行上述命令之后，使用SHOW CREATE TABLE语句查看books表，执行结果如图328所示。


图328使用SHOW CREATE TABLE语句查看books表执行结果


使用SHOW CREATE TABLE语句查看borrow表，执行结果如图329所示。


图329使用SHOW CREATE TABLE语句查看borrow表执行结果


接下来，使用ALTER语句为借阅表borrow创建外键。SQL语句如下。



ALTER TABLE borrow ADD CONSTRAINT fk_bks_brw FOREIGN KEY(Borrowbookid) REFERENCES books (Bookid);





为了验证借阅表borrow的外键是否创建成功，再次使用SHOW CREATE TABLE语句查看borrow表，执行结果如图330所示。


图330使用SHOW CREATE TABLE语句查看添加外键执行结果


对比图329和图330，可以看出已经成功地创建了books表和borrow表的主外键关联。
3. 删除外键约束
语法格式如下。



ALTER TABLE从表名DROP FOREIGN KEY外键名;





【例325】删除borrow表Borrowbookid字段的外键约束，外键约束名是fk_bks_brw。SQL语句如下。



ALTER TABLE borrow DROP FOREIGN KEY fk_bks_brw;





为了验证borrow表Borrowbookid字段的外键约束是否删除，使用SHOW CREATE TABLE语句查看borrow表，执行结果如图331所示。


图331使用SHOW CREATE TABLE语句查看删除外键执行结果


对比图330和图331，可以看出已经成功地删除了books表和borrow表的主外键关联。但是仍出现“Key 'fk_bks_brw'('Borrowbookid')”的信息，是因为MySQL在创建外键后，会自动创建一个同名的索引。外键删除，但索引不会被删除。本书会在后续章节中详细介绍索引。
3.4.3非空约束
非空约束是指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。
1. 创建表时添加非空约束
语法格式如下。



字段名 数据类型NOT NULL;





【例326】创建company表，并设置company_id字段为主键，company_address字段为非空约束。SQL语句如下。



DROP TABLE IF EXISTS company;

CREATE TABLE company

(

company_id int(11) PRIMARY KEY,

company_name varchar(50),

company_address varchar(200) NOT NULL

);





执行上述命令之后，使用DESC语句查看company表的结构，执行结果如图332所示。


图332创建company表时添加非空约束执行结果


从图332中可以看出，company_address字段的“Null”列的值为NO，表示这个字段不允许为空。
2. 为已经存在的表添加非空约束
语法格式如下。



ALTER TABLE 表名 MODIFY 字段名 新数据类型 NOT NULL;





此命令可以同时修改字段的数据类型和增加非空约束。如果不修改字段的数据类型，将“新数据类型”写为字段原来的数据类型即可。
【例327】将company表的company_address字段设置为非空约束。
首先创建company表，SQL语句如下。



DROP TABLE IF EXISTS company;

CREATE TABLE company

(

company_id int(11) PRIMARY KEY,

company_name varchar(50),

company_address varchar(200)

);






执行上述命令之后，使用DESC语句查看company表的结构，执行结果如图333所示。
接下来，使用ALTER语句将company_address字段设置为非空约束，SQL语句如下。



ALTER TABLE company MODIFY company_address varchar(200) NOT NULL;





为了验证company_address字段的非空约束是否添加成功，再次使用DESC语句查看company表的结构，执行结果如图334所示。



图333创建company表执行结果




图334company表添加非空约束执行结果



从图334中可以看出，company_address字段的“Null”列的值为NO，表示这个字段不允许为空。
3. 删除非空约束
语法格式如下。



ALTER TABLE表名MODIFY字段名 数据类型;





【例328】删除company表的company_address字段的非空约束。
SQL语句如下。



ALTER TABLE company MODIFY company_address varchar(200);





为了验证company_address字段的非空约束是否删除成功，使用DESC语句查看company表的结构，执行结果如图335所示。


图335company表删除非空约束执行结果


从图335中可以看出，company_address字段的“Null”列的值为YES，表示这个字段允许为空。
3.4.4唯一约束
唯一约束要求该列值唯一，不能重复。唯一约束可以确保一列或者几列不出现重复值。
1. 创建表时添加唯一约束
语法格式如下。



字段名数据类型UNIQUE;






【例329】创建company表，并将company_id字段设置为主键，company_address字段设置为非空约束，company_name字段设置为唯一约束。
SQL语句如下。



DROP TABLE IF EXISTS company;

CREATE TABLE company

(

company_id int(11) PRIMARY KEY,

company_name varchar(50) UNIQUE,

company_address varchar(200) NOT NULL

);





执行上述命令之后，使用DESC语句查看company表的结构，执行结果如图336所示。


图336创建表时添加唯一约束执行结果


从图336中可以看出，company_name字段的“Key”列的值为UNI，表示这个字段具有唯一约束。
注意： 一个表中可以有多个字段声明为唯一约束，但是只能有一个主键； 声明为主键的字段不允许有空值，但是声明为唯一约束的字段允许存在空值。
2. 为已存在的表添加唯一约束
语法格式如下。



ALTER TABLE表名MODIFY字段名 新数据类型UNIQUE;






此命令可以同时修改字段的数据类型和增加唯一约束。如果不修改字段的数据类型，将“新数据类型”写为字段原来的数据类型即可。
【例330】将company表的company_name字段修改为唯一约束。首先创建company表，SQL语句如下。



DROP TABLE IF EXISTS company;

CREATE TABLE company

(

company_id int(11) PRIMARY KEY,

company_name varchar(50),

company_address varchar(200) NOT NULL

);






执行上述命令之后，使用DESC语句查看company表的结构，执行结果如图337所示。
接下来，使用ALTER语句为company_name字段添加唯一约束，SQL语句如下。



ALTER TABLE company MODIFY company_name varchar(50) UNIQUE;





为了验证company_name字段的唯一约束是否添加成功，再次使用DESC语句查看company表的结构，执行结果如图338所示。



图337创建company表执行结果




图338为company表添加唯一约束执行结果



从图338中可以看出，company_name字段的“Key”列的值为UNI，表示这个字段具有唯一约束。
3. 删除唯一约束
语法格式如下。



ALTER TABLE 表名 DROP INDEX 字段名





【例331】删除company表的company_name字段的唯一约束。SQL语句如下。



ALTER TABLE company DROP INDEX company_name;





为了验证company_name字段的唯一约束是否删除成功，使用DESC语句查看company表的结构，执行结果如图339所示。从图339中可以看出，company_name字段的“Key”列的值为空，表示这个字段已没有唯一约束。


图339在company表中删除唯一约束执行结果


3.4.5默认约束
若将数据表中某列定义为默认约束，在用户插入新的数据行时，如果没有为该列指定数据，那么数据库系统会自动将默认值赋给该列，默认值也可以是空值(NULL)。
1. 创建表时添加默认约束
语法格式如下。



字段名数据类型DEFAULT默认值;





【例332】创建company表，并将company_id字段设置为主键，company_address字段设置为非空约束，company_name字段设置为唯一约束，company_tel字段的默认值为“0371”，SQL语句如下。



DROP TABLE IF EXISTS company;

CREATE TABLE company

(

company_id int(11) PRIMARY KEY,

company_name varchar(50) UNIQUE,

company_address varchar(200) NOT NULL,

company_tel varchar(20) DEFAULT '0371-'

);






执行上述命令之后，使用DESC语句查看company表的结构，执行结果如图340所示。


图340例332执行结果


从图340中可以看出，company_tel字段的“Default”列的值为“0371”，表示这个字段具有默认值“0371”。
2. 为已存在的表添加默认约束
语法格式如下。



ALTER TABLE 表名 MODIFY 字段名 新数据类型 DEFAULT 默认值;





此命令可以同时修改字段的数据类型和增加默认约束。如果不修改字段的数据类型，将“新数据类型”写为字段原来的数据类型即可。
【例333】为company表的company_tel字段添加默认约束，默认值为“0371”。
首先创建company表，SQL语句如下。



DROP TABLE IF EXISTS company;

CREATE TABLE company

(

company_id int(11) PRIMARY KEY,

company_name varchar(50) UNIQUE,

company_address varchar(200) NOT NULL,

company_tel varchar(20) 

);






执行上述命令之后，使用DESC语句查看company表的结构，执行结果如图341所示。
接下来，使用ALTER语句为company_tel字段添加默认约束，SQL语句如下。



ALTER TABLE company MODIFY company_tel varchar(20) DEFAULT '0371';





为了验证company_tel字段的默认约束是否添加成功，再次使用DESC语句查看company表的结构，执行结果如图342所示。



图341创建company表执行结果




图342company表添加默认约束执行结果



从图342中可以看出，company_tel字段的“Default”列的值为“0371”，表示这个字段具有默认值“0371”。
3. 删除默认约束
语法格式如下。



ALTER TABLE表名MODIFY字段名 数据类型;





【例334】删除company表的company_tel字段的默认约束。SQL语句如下。



ALTER TABLE company MODIFY company_tel varchar(20);





为了验证company_tel字段的默认约束是否删除，使用DESC语句查看company表的结构，执行结果如图343所示。


图343删除company表默认约束执行结果


从图343中可以看出，company_tel字段已经没有默认值了。
3.5字段值自动增加
在数据库中，如果表的主键值是逐一增加的，我们希望在每次插入记录时由系统自动生成，这可以通过为表的主键添加AUTO_INCREMENT关键字来实现。在MySQL中，AUTO_INCREMENT字段的初始值是1，每增加一条记录，字段值自动加1，但一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须被设置为主键。AUTO_INCREMENT约束所在的字段可以是任何整数类型(TINYINT, SMALLINT,INT,BIGINT)。
1. 创建表时指定字段值自动增加
语法格式如下。



字段名数据类型PRIMARY KEY AUTO_INCREMENT;





【例335】创建company表，并将company_id字段设置为主键，其值自动增加，company_address字段设置为非空约束，company_name字段设置为唯一约束，company_tel字段的默认值设置为“0371”。SQL语句如下。



DROP TABLE IF EXISTS company;

CREATE TABLE company

(

company_id int(11) PRIMARY KEY AUTO_INCREMENT,

company_name varchar(50) UNIQUE,

company_address varchar(200) NOT NULL,

company_tel varchar(20) DEFAULT '0371-'

);






执行上述命令之后，使用DESC语句查看company表的结构，执行结果如图344所示。
从图344中可以看出，company_id字段的“Extra”列的值为“auto_increment”，表示这个字段值是自动增加的。系统会自动填入自动增加字段的值，用户在插入记录时不需要给出。


图344创建表时指定字段值自动增加执行结果


2. 为已存在的表设置字段值自动增加
语法格式如下。



ALTER TABLE 表名 MODIFY 字段名 新数据类型 AUTO_INCREMENT;





【例336】设置company表的company_id字段值自动增加。
首先创建company表，SQL语句如下。



DROP TABLE IF EXISTS company;

CREATE TABLE company

(

company_id int(11) PRIMARY KEY,

company_name varchar(50) UNIQUE,

company_address varchar(200) NOT NULL,

company_tel varchar(20) DEFAULT '0371-'

);






执行上述命令之后，使用DESC语句查看company表的结构，执行结果如图345所示。
接下来，使用ALTER语句将company_id字段设置为自动增加，SQL语句如下。



ALTER TABLE company MODIFY company_id int(11) AUTO_INCREMENT;





为了验证company_id字段值的自动增加是否添加成功，再次使用DESC语句查看company表的结构，执行结果如图346所示。



图345创建company表执行结果




图346company表设置自动增加执行结果



从图346中可以看出，company_id字段的“Extra”列的值为“auto_increment”，表示这个字段值是自动增加的。
3. 删除字段值的自动增加
语法格式如下。



ALTER TABLE表名MODIFY字段名 数据类型;





【例337】删除company表的company_id字段值的自动增加。SQL语句如下。



ALTER TABLE company MODIFY company_id int(11);





为了验证company_id字段值的自动增加是否删除，使用DESC语句查看company表的结构，执行结果如图347所示。


图347删除字段值的自动增加执行结果


从图347中可以看出，company_id字段的“Extra”列的值为空，表示这个字段值不再自动增加。
注意： 在为字段删除自动增加并重新添加自动增长后，自动增长的初始值会自动设为该列现有的最大值加1。在修改自动增加值时，修改的值若小于该列现有的最大值，则修改不会生效。
3.6综合案例： 教务管理系统数据库
本节以教务管理系统为例来介绍数据库的创建和数据表的设计。教务管理系统用来帮助高校学生选修课程。学生通过系统可以查看所有选修课程的相关信息，包括课程名、学时、学分，也可以查看相关授课教师的信息，包括教师姓名、性别、学历、职称，还可以通过系统查看自己的考试成绩。教师通过系统可以查看选修自己课程的学生的信息，包括学号、姓名、性别、出生日期、班级，也可以通过系统录入学生的考试成绩。
3.6.1创建“教务管理系统”数据库
“教务管理系统”数据库的创建语句如下。



CREATE DATABASE Jwsystem;





3.6.2在“教务管理系统”数据库中创建表
根据教务管理系统的要求，在“Jwsystem”数据库中设计如下数据表。
(1) 学生表的结构设计如表36所示。


表36学生表(studentInfo)结构


序号列名数据类型允许NULL值约束备注


1snochar(8)不能为空主键学号
2snamevarchar(10)不能为空姓名
3sgenderchar(2)性别
4sbirthdate出生日期
5sclassvarchar(20)班级

创建studentInfo表的SQL语句如下。



CREATE TABLE studentInfo

(

sno char(8) PRIMARY KEY NOT NULL,

sname varchar(10) NOT NULL,

sgender char(2),

sbirth date,

sclass varchar(20) 

);






(2) 教师表的结构设计如表37所示。



表37教师表(teacher)结构


序号列名数据类型允许NULL值约束备注


1tnochar(4)不能为空主键工号
2tnamevarchar(10)不能为空姓名
3tgenderchar(2)性别
4teduvarchar(10)学历
5tprovarchar(8)默认为“副教授”职称

创建teacher表的SQL语句如下。



CREATE TABLE teacher

(

tno char(4) PRIMARY KEY NOT NULL,

tname varchar(10) NOT NULL,

tgender char(2),

tedu varchar(10),

tpro varchar(8) DEFAULT '副教授'

);






(3) 课程表的结构设计如表38所示。



表38课程表(course)结构


序号列名数据类型允许NULL值约束备注


1cnochar(4)不能为空主键课程号
2cnamevarchar(40)唯一约束课程名
3cperiodint学时
4creditdecimal(3,1)学分
5ctnochar(4)是教师表的外键授课教师

创建course表的SQL语句如下。



CREATE TABLE course

(

cno char(4) PRIMARY KEY NOT NULL,

cname varchar(40) UNIQUE,

cperiod int,

credit decimal(3,1),

ctno char(4) ,

CONSTRAINT fk_teacher_course FOREIGN KEY (ctno) REFERENCES teacher(tno)

);






(4) 选课表的结构设计如表39所示。



表39选课表(elective)结构


序号列名数据类型允许NULL值约束备注





1snochar(8)
2cnochar(4)
主键(学号,课程号),其中学号是学生表的外键，课程号是课程表的外键

学号
课程号

3scoreint成绩

创建elective表的SQL语句如下。



CREATE TABLE elective

(

sno char(8),

cno char(4),

score int,

PRIMARY KEY (sno,cno),

CONSTRAINT fk_course_elective FOREIGN KEY (cno) REFERENCES course (cno),

CONSTRAINT fk_stu_elective FOREIGN KEY (sno) REFERENCES studentInfo (sno)

);






“教务管理系统”数据库创建完毕。
单元小结
 数据库的基本操作： 创建数据库、查看数据库、修改数据库、删除数据库。
 数据表的基本操作： 创建数据表、查看数据表、修改数据表、删除数据表。
 四类数据类型： 数值类型、日期/时间类型、字符串(字符)类型、二进制类型。
 MySQL中的五种约束： 主键约束、外键约束、非空约束、唯一约束、默认约束。
单元实训项目

项目一： 创建“网上书店”数据库
在安装好的MySQL中创建“网上书店”数据库(如： BookShop)。
项目二： 在“网上书店”数据库中创建表
目的： 
(1) 熟练掌握创建表结构的方法。
(2) 掌握查看表信息的方法。
内容： 
(1) 使用MySQL创建会员表(如表310所示)、图书表(如表311所示)的表结构。


表310会员表(user)结构


列名数据类型允许NULL值约束备注


uidchar（4）不允许主键会员编号
unamevarchar（20）会员昵称
emailvarchar（20）电子邮箱
tnumvarchar（15）联系电话
scoreint积分


表311图书表(book)结构


列名数据类型允许NULL值约束备注


bidint不允许主键图书编号
bnamevarchar（50）不允许图书名称
authorchar（8）作者
priceflcat价格
publishervarchar（50）出版社
discountfloat折扣
cidint图书类别表的外键图书类型

(2) 使用MySQL创建图书类别表(如表312所示)、订购表(如表313所示)的表结构。


表312图书类别表(category)结构


列名数据类型允许NULL值约束备注


cidint不允许主键类别编号
cnamevarchar（16）类别名称


表313订购表(b_order)结构


列名数据类型允许NULL值约束备注


bidint不允许图书编号
uidchar（4）不允许会员编号
ordernumint默认值为1订购量
orderdatedatetime认购日期
deliverydatedatetime发货日期

(3) 使用DROP TABLE语句删除上述创建的表，然后使用CREATE TABLE语句再次创建上述表。
(4) 查看会员表的信息。
(5) 修改会员表结构。添加字段“联系地址”，数据类型设置为varchar(50)； 更改“联系地址”为“联系方式”； 删除添加的字段“联系地址”。
(6) 使用创建表时添加约束和为已存在的表添加约束这两种方式给表添加约束。
单元练习题
一、 选择题
1. 下列()不能作为MySQL数据库名。
A. minrisoftB. mingrisoft_01 C. com$comD. 20170609
2. 下列()语句不是数据定义的语句。
A. CREATEB. DROPC. GRANTD. ALTER
3. 下列()语句可以用于查看服务器中所有的数据库名称。
A. SHOW DATABASEB. SHOW DATABASES
C. SHOW ENGINESD. SHOW VARIABLES
4. 下列()语句可以用于将db_library数据库作为当前默认的数据库。
A. CREATE DATABASE db_libraryB. SHOW db_library
C. USE db_libraryD. SELECT db_library
5. 下列关于数据类型的选择方法描述错误的是()。
A. 选择最小的可用类型，如果值永远不超过127，则使用TINYINT比INT强
B. 对于完全都是数字的，可以选择整数类型
C. 浮点类型用于可能具有小数部分的数
D. 以上都不对
6. UNIQUE唯一索引的作用是()。
A. 保证各行在该索引上的值不能为NULL
B. 保证各行在该索引上的值都不能重复
C. 保证唯一索引不能被删除
D. 保证参加唯一索引的各列，不能再参加其他的索引
7. 创建数据表时，使用()语句。
A. ALTER TABLE B. CREATE DATABASE
C. CREATE TABLED. ALERT DATABASE
8. 下列()不是MySQL常用的数据类型。
A. INTB. VARCHARC. CHARD. MONEY
9. 想要删除数据库中已经存在的数据表，可以使用()语句。
A. CREATE TABLEB. DROP DATABASE
C. ALERT TABLED. DROP TABLE
10. 在MySQL中，非空约束可以通过()关键字定义。
A. NOT NULLB. DEFAULT C. CHECKD. UNIQUE
二、  判断题
1. MySQL数据库一旦安装成功，创建的数据库编码也就确定了，是不可以更改的。()
2. 在MySQL中，如果添加的日期类型不合法，系统将报错。()
3. 在删除数据表时，如果表与表之间存在关系，那么可能导致删除失败。()
4. 一个数据表中可以有多个主键约束。()
三、  简答题
1. 简述主键的作用及其特征。
2. 创建、查看、修改、删除数据库的语句分别是什么？
3. 创建、查看、修改、删除数据表的语句分别是什么？
4. 数据表有哪些约束？写出为数据表添加约束的语句。
5. 什么是非空约束？写出其基本语法格式。
6. 什么是默认约束？写出其基本语法格式。