第5 章
表的基本操作
表是数据库中存储数据的基本单位,由一个或多个字段组成,每个字段需要有对应的
数据类型。MySQL数据库中表的管理涉及表的作用、类型、构成、创建、删除和修改等。
本章先讲述了表的基本概念以及MySQL支持的数据类型和运算符等一些基础知
识;接着讲述了表的基本操作,包括创建、查看、修改、复制和删除表;最后讲述了MySQL
的约束控制,以及如何定义和修改字段的约束条件。
5.1 表的基本概念
数据库与表之间的关系:数据库是由各种数据表组成的;数据表是数据库中最重要
的对象,是用来存储和操作数据的逻辑结构。表由列和行组成,列是表数据的描述,行是
表数据的实例。一个表包含若干个字段或记录。表的操作包括创建新表、修改表和删除
表等,这些操作都是数据库管理中最基本、最重要的操作。
5.1.1 建表原则
为减少数据输入错误,并能使数据库高效工作,设计表时应按照一定的原则对信息进
行分类。同时为确保表结构设计的合理性,通常还要对表进行规范化设计,以消除表中存
在的冗余。保证一个表只围绕一个主题,并使得表容易维护。
5.1.2 数据库表的信息存储分类原则
(1)每个表应该只包含关于一个主题的信息。
当每个表只包含关于一个主题的信息时,就可以独立于其他主题来维护该主题的信
息。例如,应将教师基本信息保存在“教师”表中。如果将这些基本信息保存在“授课”表
中,则在删除某教师的授课信息时会将其基本信息一同删除。
(2)表中不应包含重复信息。
表间也不应有重复信息,每条信息只保存在一个表中,需要时只在一处进行更新即
可,这样效率更高。例如,每个学生的学号、姓名、性别等信息只保存在“学生”表中,而“成
绩”中不再保存这些信息。
表(Table)是数据库中存储数据最常见和最简单的一种形式,数据库可以将复杂的数
据结构用较为简单的二维表来表示。二维表是由行和列组成的,分别都包含着数据,如表
5-1所示。
82 MySQL 数据库技术与应用
表5-1 学生信息表
学号姓名性别年龄
2020081601 张一飞女20
2020081602 李忠诚男18
2020081603 王晴水女21
2020081604 赵洋办男19
每个表都是由若干行和列组成的,在数据库中表中的行称为记录,表中的列则称为这
些记录的字段。
记录也被称为一行数据,是表中的一行。在关系型数据库的表中,一行数据是指一条
完整的记录。
字段是表中的一列,用于保存每条记录的特定信息。如表5-1所示的学生信息表中
的字段包括“学号”“姓名”“性别”和“年龄”。数据表的一列包含了某个特定字段的全部
信息。对
于表的基本操作主要包括创建表、查看表、修改表以及删除表等,这些操作非常重
要,它们是数据库操作的基础。
5.2 创建数据表
创建数据表指的是在已存在的数据库中建立新表。MySQL既可以根据开发需求创
建新表,也可以根据已有的表复制相同的表结构。其中依据已有的表创建相同结构的新
表方式会在后面的章节中讲解,此处仅讲解如何根据需求创建一个简单的新表。
在创建表之前,首先要选择在哪个数据库中创建表。在MySQL自带的客户端软件
中,可以使用如下SQL语句来选择数据库:
USE db_name;
其中“db_name”为选择的数据库名称。如果没有选择数据库而是直接创建表,则会
提示“Nodatabaseselected”错误。如果选择数据库成功,则会提示“Databasechanged”,
之后就可以在选择的数据库中创建新表了。操作数据表时,也可以不使用“USE数据库”
的方式选择数据库,而直接将表名的位置改为“数据库.表名”的形式,就可以在任何数据
库下访问其他数据库中的表。
如果使用Navicat软件,操作可以大大简化,直接双击要选择的数据库即可切换数据
库(如果是第一次选中该数据库,那么数据库图标会由灰色变为绿色,下次再选择该数据
库时只需要单击即可)。
本节将分别讲解在Navicat中如何使用SQL语句创建表以及如何使用图形界面创
建表。
第5 章 表的基本操作 83
5.2.1 使用SQL 语句创建表
在Navicat中,要使用SQL语句创建表,必须在选中的数据库中打开一个SQL语句
执行窗口(在此之前新建一个名为jxgl的数据库),具体操作如图5-1所示。
图5-1 在jxgl数据库中创建SQL语句执行窗口
按照图5-1操作完成后,会看到如图5-2所示的界面,如果界面中数据库一栏中显示
为jxgl,则表示操作成功(此处单击下拉按钮“▼”,可以切换数据库)。
图5-2 选择数据库成功界面
接下来就可以在SQL语句执行窗口中输入创建表的SQL语句,其语法格式如下。
CREATE [TEMPORARY]TABLE [IF NOT EXISTS]表名
(
字段名1, 数据类型[完整性约束条件],
字段名2, 数据类型[完整性约束条件],
…
84 MySQL 数据库技术与应用
字段名n, 数据类型[完整性约束条件]
)
注意:语法格式中的“[]”表示可选的。
在上述语法格式中,CREATETABLE为创建表的固定语法格式。
可选项TEMPORARY表示临时表,临时表指的是一种仅在当前会话中可见,并在
当前会话关闭时自动删除的数据表,它主要用于临时存储数据。临时表的语法很简单,只
需在CREATE与TABLE关键字中间添加TEMPORARY 即可。创建临时表时指定的
数据库可以是MySQL服务器中存在的数据库,也可以是不存在的数据库。若数据库不
存在,则操作临时表时必须使用“数据库.临时表名”指定临时表所在的数据库。除此之
外,临时表中数据的操作与普通表相同,都可以进行SELECT、INSERT、UPDATE 和
DELETE操作,这里不再演示。需要注意的是,SHOW TABLES不能查看指定数据库下
有哪些临时表,并且临时表的表名必须使用ALTER TABLE 修改,而不能使用
RENAMETABLE…TO 修改。
“表名”指的是创建的数据表名称(为了避免数据表重复,通常为数据表添加前缀用于
区分不同的项目。前缀一般选取数据库的前几个字母,并添加一个下画线,比如jxgl_
student)。表名不能与数据库的关键字同名,如CREATE、DATABASE、TABLE等。
“字段名”指的是数据表的列名,为二维表中每一列的列名。不同字段之间的定义使
用“,”隔开,但最后一个字段没有“,”。
“数据类型”为该字段所存储的数据的数据类型。
“完整性约束条件”指的是字段的某些特殊约束条件,关于表的约束,将在后续章节进
行详细讲解。
注意:在MySQL中,如果使用的数据库名、表名或字段名等与保留字冲突,需使用
撇号(')括起来。在MySQL自动生成的代码中,表名或字段名等全部使用撇号括起来。
以创建名为student的表为例进行演示,该表用来存储学生信息,具体信息及数据类
型的选择如表5-2所示,创建student表的SQL语句如例5-1所示。
表5-2 student表相关信息
字段名数据类型描 述
sno VARCHAR(10) 学号,主键
sname VARCHAR(20) 姓名,非空
ssex ENUM '男','女',默认为'男'
sbirth DATE 出生日期
zno CHAR(4) 专业号
sclass VARCHAR(10) 所在班级
第5 章 表的基本操作 85
【例5-1】 使用SQL语句创建student表。
CREATE TABLE student (
sno VARCHAR(10) NOT NULL COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
ssex ENUM('男', '女') NOT NULL DEFAULT '男' COMMENT '性别',
sbirth DATE NOT NULL COMMENT '出生日期',
zno VARCHAR(4) NULL COMMENT '专业号',
sclass VARCHAR(10) NULL COMMENT '班级',
PRIMARY KEY ('sno')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
执行结果如图5-3所示。
图5-3 使用SQL语句创建student表
从图5-3中可以看到“OK”字样,说明表格已经创建成功。“时间:0.045s”表示执行
该SQL语句所耗费的时间。
在操作数据表时,可以不使用“USE数据库”的方式选择数据库,而是直接将表名的
位置改为“数据库.表名”的形式,这样就可以在任何数据库下访问其他数据库中的表。创
建数据库表时,还可以设置表的存储引擎、默认字符集以及压缩类型。
(1)向CREATETABLE语句末尾添加ENGINE选项,即设置该表的存储引擎。语
法格式如下:
ENGINE=存储引擎类型
(2)向CREATETABLE语句末尾添加DEFAULTCHARSET 选项,即设置该表
的字符集。语法格式如下:
DEFAULT CHARSET=字符集类型
(3)如果希望压缩索引中的关键字,使索引关键字占用更少的存储空间,可以通过设
置pack_keys选项实现(注意,该选项仅对MyISAM 存储引擎的表有效)。语法格式
如下:
Pack_keys=压缩类型
注意:对于InnoDB 存储引擎的表而言,MySQL 服务实例会在数据库目录
86
MySQL
数据库技术与应用
StdnIf后缀名为ftdn.rm 。
uetno中自动创建一个名为表名、rm 的表结构定义文件Suetffrm 文件记录了Student表的表结构定义。如果数据库表的存储引擎是MyISAM,则
MySQL 服务实例除了会自动创建frm 表结构定义文件外,还会自动创建一个文件名为
表名、后缀名为MYD(即MYData的简写)的数据文件以及一个文件名为表名、后缀名为
MYI(即MYIndex的简写)的索引文件,其中,MYD 文件用于存放数据,MYI 文件用于存
放索引。
5.2.2
使用图形界面创建表
除了使用SQL 语句创建表外,还可以在图形界面中创建表,这种操作对于初学者而
言更为简单。
同样以创建student表为例。
(1)展开jxgl数据库的目录结构,其中有一个名
为“表”的选项,右击“表”,在弹出的下拉列表中选择
“新建表”,如图5-4所示。
(2)在打开的创建表窗口中按照要求输入字段
名、数据类型和数据长度等信息,其中数据类型可在
下拉列表中根据需求选择。输入完成后的表信息如
图5-5所示。
从图5-5中能够看到位于窗口上方的工具栏,其
中“添加字段”工具提供增加字段功能,“插入字段”工图5-
4
jxgl数据库的目录结构
具提供插入字段功能(在选中字段的上方插入),“删
除字段”则用于删除选中的字段,“主键”能够将选中的字段设置为主键,“上移”和“下移”
可以上下移动选中的字段。
图5-
5
使用图形界面输入表信息
(3)表信息输入完成后,单击左上方的“保存”按钮进行保存,之后便会弹出要求输入
表名的对话框,在输入框中输入student_1,单击“确定”按钮即完成创建表的操作,具体操
作如图5-6所示。
(4)右击以刷新“表”,则会在该节点下显示刚刚创建的student_1表。
第5 章 表的基本操作 87
图5-6 保存创建的表
5.3 查看数据表
在表创建完成后,在很多情况下都会想要查看表的信息,如在插入数据之前查看数据
的类型和长度,或者查看主键和外键的设置等。本节讲述如何使用SQL语句来查看表的
基本结构和详细信息(利用图形界面查看表结构非常简单,读者可以自行研究一下)。
5.3.1 查看所有数据表名
MySQL中提供了专门的SQL语句,用于查看某数据库中存在的所有数据表、指定
模式的数据表或数据表的相关信息。
选择数据库后,可以使用SHOW TABLES语句来显示指定数据库中存放的所有表
名。语法格式如下:
SHOW TABLES [LIKE 匹配模式];
其中,如果省略可选项,则表示查看当前数据库中的所有数据表。如果添加可选项,
则按照“匹配模式”查看数据表。
匹配模式符“%”匹配一个或多个字符,代表任意长度的字符串;匹配模式符“_”则仅
可以匹配一个字符。LIKE后的匹配模式必须使用单引号或双引号括起来。
【例5-2】 显示数据库jxgl中所有的表。
SHOW TABLES;
执行结果如图5-7所示。
图5-7 显示数据库jxgl中所有的表
88 MySQL 数据库技术与应用
5.3.2 查看数据表的基本结构
MySQL提供的DESCRIBE语句可以查看数据表中所有字段或指定字段的信息,包
括字段名和字段类型等。其中,DESCRIBE命令可以简写成DESC。
语法格式1:查看所有字段的信息。
DESCRIBE | DESC 数据表名;
语法格式2:查看指定字段的信息。
DESCRIBE | DESC 数据表名字段名;
【例5-3】 用DESCRIBE和DESC命令显示jxgl数据库中student表的结构。
DESCRIBE student;
或者:
DESC student;
执行结果如图5-8所示。
图5-8 student表结构图
通过DESCRIBE语句能够看到表的字段(Field)、数据类型及长度(Type)、是否允许
空值(Null)、键的设置信息(Key)、默认值(Default)以及附加信息(Extra)。但是如果想
查看关于表更详细的信息,这种方式就不可行了,需要使用一种新的SQL语句,如下:
SHOW CREATE TABLE;
5.3.3 查看数据表的详细结构
使用SHOW CREATETABLE语句,不仅可以查看表的字段、数据类型及长度、是
否允许空值、键的设置信息和默认值等,还可以查看数据库的存储引擎以及字符集等信
息。其语法格式如下:
SHOW CREATE TABLE 表名;
其中SHOW CREATETABLE为查看表详细结构的固定语法格式,“表名”为要查
看的表的名称。查看student表详细结构的SQL语句如例5-4所示。
【例5-4】 使用SHOW CREATETABLE语句查看表的详细结构。
第5 章 表的基本操作 89
SHOW CREATE TABLE student;
执行结果如图5-9所示。
图5-9 查看student表的详细结构
由于页面显示问题,图中CREATETABLE中的表信息并不能完全展现出来,为了
让大家能够清晰地看到表的详细结构,此处将信息复制并整理了一份,如下所示:
CREATE TABLE student (
sno VARCHAR(10) COLLATE utf8_bin NOT NULL COMMENT '学号',
sname VARCHAR(20) COLLATE utf8_bin NOT NULL COMMENT '姓名',
ssex ENUM('男','女') COLLATE utf8_bin NOT NULL DEFAULT '男' COMMENT '性别',
sbirth date NOT NULL COMMENT '出生日期',
zno VARCHAR(4) COLLATE utf8_bin DEFAULT NULL COMMENT '专业号',
sclass VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT
'班级',
PRIMARY KEY (sno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
5.3.4 查看数据表结构
MySQL数据库中的SHOW COLUMNS语句也可以查看表结构,基本语法格式
如下:
#语法格式1
SHOW [FULL]COLUMNS FROM 数据表名[FROM 数据库名];
#语法格式2
SHOW [FULL]COLUMNS FROM 数据库名.数据表名;
在上述语法格式中,可选项FULL表示显示详细内容,在不添加的情况下查询结果
与DESC的结果相同;在添加FULL选项时,此语句不仅可以查看到DESC语句查看的
信息,还可以查看到字段的权限以及COMMENT字段的注释信息等。在SQL语句中可
以通过“FROM 数据库名”或“数据库名.数据表名”的方式查看任意数据库下的数据表结
构信息。
【例5-5】 查看studentinfo数据表结构的详细信息。
SHOW FULL COLUMNS FROM studentinfo;
执行结果如图5-10所示。
从上述执行结果可以看出,SHOW FULLCOLUMNS语句除了查询出与DESC语
句相同的字段外,还会查询出Collation(校对集)、Privileges(权限)和Comment(注释)
字段。
90 MySQL 数据库技术与应用
图5-10 查看数据表结构
5.4 修改数据表
修改表是指修改数据库中已经存在的数据表的结构。表在创建完成后,可能会因为
某些原因需要对表的名称、字段名称、字段的数据类型或者字段的排列位置等进行修改。
有一种方法就是直接删除旧表,然后根据新的需求创建新表,但是如果旧表中已经存在大
量数据,那会增加额外的工作量;另外一种方法则是使用MySQL 中提供的ALTER
TABLE语句来修改表的相关定义,即本节要讲解的内容。
常用的修改表的操作有:修改表名、修改字段数据类型或字段名、增加和删除字段、
修改字段的排列位置、更改表的存储引擎以及删除表的外键约束等。在修改表结构时,可
以使用DESC命令查看修改是否成功。
在使用Navicat软件修改表时,要注意以下两点。
(1)如果要修改表名,只需要右击要修改的表格,然后在弹出的下拉列表中选择“重
命名”选项即可。
(2)如果要修改字段的相关定义,则需右击要修改的表格,然后在弹出的下拉列表中
选择“设计表”选项,进入修改表格界面,修改完成后保存即可。
5.4.1 修改表名
表名是用来区分同一个数据库中不同表的依据,因此在同一个数据库表名具有唯一
性。通过SQL语句ALTERTABLE可以修改表名,其语法格式如下:
格式一:
ALTER TABLE 旧表名RENAME [TO|AS]新表名;
格式二:
RENAME TABLE 旧表名1 TO 新表名1[,旧表名2 TO 新表名2]…;
其中用ALTERTABLE修改数据表名称时,可以直接使用RENAME,或在其后添
加TO 或AS。而RENAMETABLE则必须使用TO,另外此语法可以同时修改多个数
据表的名称。
【例5-6】 使用ALTERTABLE语句修改表名。