数据库原理：表管理和表中数据操纵

表是数据库中最重要的数据库对象，是数据存储的基本单位。创建完数据库之后，需要在数据库中创建数据表。对数据表的操作是数据库应用的基础。本章首先介绍MySQL支持的数据类型，然后通过建立teaching数据库中教师关系表(t)、学生关系表(s)、课．程关系表©、选课关系表(sc)、授课关系表(tc)等数据表，介绍表的管理和表中数据的操纵。其中，数据表管理包括表的创建、修改、删除、查看等，数据操纵包括数据的添加、修改、删除等。

本章学习目标：能够根据需要，选择合适的数据类型，建立相关数据表并能够对数据表进行基本管理操作，同时，能够对数据表中的数据进行添加、修改和删除。

一、MySQL数据类型

数据表中的每个字段（即每一列）都来自同一个域，属于同一种数据类型。创建数据表之前，需要为表中的每一个字段设置一种数据类型。常见的数据类型包括数字类型、字符串类型、时间日期类型、二进制类型，本节将详细介绍这4种常见的数据类型和其他的一此数据类型。

1.1、数字类型

数字类型包括整数类型和数值类型。整数类型按照取值范围从小到大，包括TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT。数值类型包括精确数值型DECIMAL和近似数值型FLOAT、DOUBLE、REAL。表5-1展示了各种数字类型。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。DECIMAL (P, S)中，P表示数据长度，S表示小数位数。

在实际应用中，用户可以根据字段的具体取值范围选择适合的整数类型。例如，第1章表1-2学生关系s中的字段“年龄(age) "的数据类型可以设置为INT；表1-4选课关系sc中的字段“成绩(score) "的数据类型可以设置为DECIMAL (5, 2)，表示数据长度为5，小数位数为2。FLOAT、DOUBLE和REAL用来存储数据的近似值，当数值的位数太多时，可用它们存取数值的近似值。

1.2、字符串类型

字符串类型用于存储字符串数据，包括CHAR、VARCHAR和TEXT。TEXT类型用于表示非二进制字符串，如文章内容、评论等，其可进一步分为TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。表5-2展示了字符串类型。

关于字符串类型的相关说明如下。

(1) CHAR和VARCHAR类型都用来表示字符串数据。CHAR (M)是固定长度字符串，在保存时，若存入字符数小于M，则在右侧填充空格以达到指定的长度，查询时再将空格去掉。因此，CHAR类型存储的数据末尾不能有空格。VARCHAR (M)是可变长度字符串，最大实际长度由最长的行的大小和使用的字符集确定。M表示最大字符数，如CHAR (10)表示可以存储10个字符。在存储或检索过程中不进行大小写转换。

(2) VARCHAR和TEXT类型是变长类型，其存储需求取决于字符串的实际长度。

注意：由于MySQL在建立数据库时指定了字符集，因此不存在NCHAR、NVARCHAR、NTEXT数据类型。

1.3、时间日期类型

MySQL中表示时间和日期的数据类型包括TIME、DATE、YEAR、DATETIME和TIMESTAMP。每个时间日期类型除有效值范围外，还包括一个"0"值，当输入不合法的MySQL不能表示的值时，系统使用"0"值进行填充。表5-3显示了每个时间日期类型。

关于时间日期类型的相关说明如下。

(1) YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分钟，SS表示秒。

(2) TIME、DATETIME和TIMESTAMP类型可以精确到秒。DATE类型只存储日期，不存储时间。

(3) DATETIME和TIMESTAMP类型既包含日期又包含时间。二者的不同之处除了存储字节和支持范围不同外，DATETIME类型在存储时，按照实际输入的格式存储，和用户所在时区无关；而TIMESTAMP类型中值的存储是以世界标准时间格式保存的，在存储时会按照用户当前时区进行转换，转换成世界标准时间，检索时再转换回当前时区。因此，在查询TIMESTAMP类型数据时，系统会根据用户所在不同时区，显示不同的时间日期值。例如，TIMESTAMP范围中的结束时间是第2147483647秒，于北京时间是2038年1月19日上午11:14: 07，而格林尼治时间为2038年1月19日凌晨03:14: 07。

1.4、二进制类型

存储由"0"和"1"组成的字符串的字段可以定义为二进制类型。MySQL中的二进制类型包括BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。其中，BIT类型以位为单位存储字段值，其他二进制类型以字节为单位存储字段值。表5-4显示了每个二进制类型。

关于二进制类型的相关说明如下。

(1) BIT是位字段类型，如果输入的数据值长度小于设定长度，则在数据值的左边用"0"填充。例如，在数据类型为BIT (3)的字段中添加二进制值"10"，则存储时实际存储"010"。

(2) BINARY是定长的二进制数据类型，VARBINARY是非定长的二进制数据类型。BINARY类型中指定长度后，若数据不足最大长度，则系统在数据右边填充"0"补齐，以达到指定长度。

(3) BLOB可用于存储可变大小的数据，如图片、音频信息。TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型的区别在于可容纳存储范围不同。

对于二进制类型，读者需注意以下事项。

(1) BINARY和VARBINARY类似于CHAR和VARCHAR，但BINARY和VARBINARY包含的是字节字符串而不是字符字符串。

(2) BLOB和字符串类型中的TEXT都可以用来存储长字符串，但其存储方式不同。TEXT 以文本方式存储，英文存储区分大小写，而BLOB是以二进制方式存储，不区分大小写。TEXT 可以指定字符集，BLOB不用指定字符集。

1.5、其他类型

MySQL支持两种复合数据类型ENUM和SET。ENUM类型允许从一个集合中取得一个值，而SET类型允许从一个集合中取得多个值。

ENUM 类型

ENUM类型只允许在给定的集合中取一个值，因此，用户可以在处理相互排斥的数据时使l 用此数据类型。例如，在学生信息表s中学生的性别sex可以设置为"ENUM(‘男’，女）”。ENUM 类型在系统内部用整数表示，并且从1开始用数字做索引。一个ENUM类型最多可以包含65536个元素。

设置为ENUM类型的字段可以从给定集合中取一个值或使用NULL值，若输入其他值，MySQL会在这个字段中插入一个空字符串。如果插入值的大小写与集合中值的大小写不匹配，MySQL会自动将插入值的大小写转换成与集合中大小写一致。

SET类型

SET类型可以从给定集合中取得多个值。若在SET类型字段中插入非给定集合中的值，MySQL会插入一个空字符串。如果插入一个既有合法元素又有非法元素的记录，MySQL将会保留合法的元素，去掉非法的元素。一个SET类型最多可以包含64个元素，且不可能包含两个相同的元素。

二、MySQL数据表管理

在MySQL中，用户可以使用MySQL Workbench或SQL语句的数据定义语言(DDL)来实现对数据表的创建、约束定义、修改、删除和查看。

2.1、创建数据表

创建数据表就是定义数据表的结构。数据表由行和列组成，创建数据表的过程就是定义数据表中列的过程，即定义字段的过程。可以使用MysQL Workbench或SQL语句来创建数据表。

使用CREATE TABLE语句创建数据表，其基本语法格式如下。

1
2
3

CREATE [TEMPORARY] TABLE [IF NOT EXIST] <表名>
[([<字段定义>], ...,|[<索引定义>])]
[table_option] [select_statement];

语法格式说明如下。

(1) TEMPORARY：若使用该关键字，则创建的是临时表。

(2) IF NOT EXIST：用于判断数据库中是否已经存在同名的表，若不存在，则执行CREATE TABLE操作。若数据库中已经存在同名表，创建数据表时会出错，为避免此种情况，可使用IF NOT EXIST进行判断。

(3)<表名>：要创建的表名，最多可有64个字符，如s、sc、c等，不区分大小写，不允许重名，不能使用SQL中的关键字。

(4)<字段定义>的书写格式如下。

1	`<字段名> <数据类型> [DEFAULT] [AUTO_INCREMENT] [COMMENT 'String'] [{<列约束>}]`

上述格式中，部分项目说明如下。

DEFAULT：若某字段设置有默认值，则当该字段未被输入数据时，自动填入设置的默认值。
AUTO_INCREMENT：设置自增属性，只有整型数据类型能够设置。
COMMENT ‘String’：注释。
<列约束>：具体见下一节

(5)<索引定义>：为表中相关字段指定索引。

(6)table_option：表选项，存储引擎、字符集等。

(7)select_statement：定义表的查询语句。

示例：用SQL命令在teaching数据库中建立学生表s。

CREATE TABLE 's' (
  'sno' CHAR(10) NOT NULL COMMENT '学号',
  'sn' VARCHAR(45) NOT NULL COMMNT '姓名',
  'sex' ENUM('男','女') NOT NULL DEFAULT '男' COMMENT '性别',
  'age' INT NOT NULL COMMENT '年龄',
  'maj' VARCHAR(45) NOT NULL COMMNET '专业',
  'dept' VARCHAR(45) NOT NULL COMMENT '院系',
  PRIMARY KEY ('sno')
)ENGING=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

执行该语句后，便创建了学生表s。该数据表中含有sno（学号）、sn（姓名）、sex（性别）、age （年龄）、maj（专业）及dept ( 院系）共6个字段，它们的数据类型和字段长度分别为CHAR (10)、VARCHAR (45)、ENUM（男，女）、INT、VARCHAR (45)及VARCHAR (45)。其中，sex字段的默认值为“男”。

同时，在创建数据表时，可以通过语句"ENGINE=存储引擎类型”来设置数据表的存储引擎；通过"DEFAULT CHARSET=字符集类型”来设置数据表的字符集；通过"COLLATE=collation_name"设置校对集，指定排序规则。本例中使用了InnoDB存储引擎和utf8mb4字符集，具体存储引擎类型可参阅4.1节，字符集可参阅4.2节。

2.2、定义表的约束

数据的完整性是指保护数据库中数据的正确性、有效性和相容性，防止错误的数据进入数据库造成无效操作。在定义数据表时可以进一步定义与此表有关的完整性约束条件，如主码、空值等约束。当数据库用户对数据库进行操作时，数据库管理系统会自动检测操作是否符合相关完整性约束。

数据表的约束分为列约束和表约束。其中，列约束是对某一个特定字段的约束，包含在字段定义中，直接跟在该字段的其他定义之后，用空格分隔，不必指定字段名；表约束与字段定义相互独立，不包括在字段定义中，通常用于对多个字段一起进行约束，与字段定义用","分隔，定义表约束时必须指定要约束的字段的名称。

约束主要包括NULL / NOT NULL约束（非空约束）、UNIQUE约束（唯一约束）、PRIMARY KEY约束（主码约束）、FOREIGN KEY约束（外码约束）和CHECK约束（检查约束）。

2.2.1、NULL / NOT NULL约束

NULL：允许为空，表示“不知道”、“不确定”或“没有数据”，其值不是"0"，也不是空白，更不是填入字符串"NULL"。

NOT NULL：不允许为空，表示字段中不允许出现空值。当某一字段一定要输入值才有意义时，可以设置此字段为NOT NULL。

例如，学生表s中的学号(sno)，此主码字段唯一标识一条记录，不允许出现空值。

NULL/NOT NULL约束只能用于定义列约束，其语法格式如下。

1	`<字段名> <数据类型> [NULL\|NOT NULL]`

示例：建立学生表s_null，其中学号sno设置为NOT NULL约束。

CREATE TABLE 's_null' (
  'sno' CHAR(10) NOT NULL COMMENT '学号',
  'sn' VARCHAR(45) COMMNT '姓名',
  'sex' ENUM('男','女') DEFAULT '男' COMMENT '性别',
  'age' INT COMMENT '年龄',
  'maj' VARCHAR(45) COMMNET '专业',
  'dept' VARCHAR(45) COMMENT '院系'
)ENGING=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

为sno字段设置NOT NULL约束后，在s_null表中录入数据时，如果sno为空，系统将给出错误信息。若没有设置NOT NULL约束，则系统默认为NULL。

2.2.2、UNIQUE约束

UNIQUE约束指所有记录中字段的值不能重复出现，用于保证数据表在某一字段或多个字段的组合上取值必须唯一。定义了UNIQUE约束的字段称为唯一码。唯一码允许为空，但系统为保证其唯一性，最多只允许出现一个NULL值。

UNIOUE既可用于列约束，又可用于表约束。UNIOUE用于定义列约束时，其语法格式如下。

1	`<字段名> <数据类型> UNIQUE`

示例：建立学生表s_unique，其中姓名sn设置为UNIQUE约束。

CREATE TABLE 's_unique' (
  'sno' CHAR(10) NOT NULL COMMENT '学号',
  'sn' VARCHAR(45) UNIQUE COMMNT '姓名',
  'sex' ENUM('男','女') DEFAULT '男' COMMENT '性别',
  'age' INT COMMENT '年龄',
  'maj' VARCHAR(45) COMMNET '专业',
  'dept' VARCHAR(45) COMMENT '院系'
)ENGING=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

UNIQUE用于定义表约束时，其语法格式如下。

1	`UNIQUE(<字段名>[{,<字段名>}])`

示例：建立学生表s_unique，定义sn+sex为唯一码，其约束为表约束。

CREATE TABLE 's_unique' (
  'sno' CHAR(10) NOT NULL COMMENT '学号',
  'sn' VARCHAR(45) COMMNT '姓名',
  'sex' ENUM('男','女') DEFAULT '男' COMMENT '性别',
  'age' INT COMMENT '年龄',
  'maj' VARCHAR(45) COMMNET '专业',
  'dept' VARCHAR(45) COMMENT '院系',
  UNIQUE('sn', 'sex')
)ENGING=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

说明：

一个表中可以允许有多个UNIQUE约束，UNIQUE约束可以定义在多个字段上；
使用UNIQUE约束的字段允许为NULL值；
UNIQUE约束用于强制在指定字段上创建一个UNIQUE索引，默认为非聚集索引。

2.2.3、PRIMARY KEY约束

PRIMARY KEY约束用于定义基本表的主码，起唯一标识作用，保证数据表中记录的唯一性。其值不能为NULL、不能重复，以此来保证实体的完整性。一张表只能有一个PRIMARY KEY约束，且其可以作用于一个字段，也可以作用于多个字段的组合。

PRIMARY KEY既可用于列约束，又可用于表约束。PRIMARY KEY用于定义列约束时，其语法格式如下。

1	`<字段名> <数据类型> PRIMARY KEY`

示例：建立学生表s_primary，定义学号sno为表的主码。

CREATE TABLE 's_primary' (
  'sno' CHAR(10) NOT NULL PRIMARY KEY COMMENT '学号',
  'sn' VARCHAR(45) UNIQUE COMMNT '姓名',
  'sex' ENUM('男','女') DEFAULT '男' COMMENT '性别',
  'age' INT COMMENT '年龄',
  'maj' VARCHAR(45) COMMNET '专业',
  'dept' VARCHAR(45) COMMENT '院系',
  UNIQUE('sn', 'sex')
)ENGING=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

PRIMARY KEY用于定义表约束时，即将某些字段的组合定义为主码时，其语法格式如下。

1	`[CONSTRAINT <约束名>] PRIMARY KEY (<字段名>[{,<字段名>}])`

示例：建立选课表sc_primary，定义学号sno和课程号cno为表的主码。

CREATE TABLE 'sc_primary' (
  'sno' CHAR(10) NOT NULL COMMENT '学号',
  'cno' CHAR(10) NOT NULL COMMENT '课程号',
  'score' DECIMAL(5,2) COMMENT '成绩',
  PRIMARY KEY ('sno', 'cno')
)ENGING=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

说明：PRIMARY KEY约束与UNIQUE约束类似，通过建立唯一索引来保证基本表在主码字段取值的唯一性，但它们之间存在以下区别。

(1)在一个基本表中只能定义一个PRIMARY KEY约束，但可定义多个UNIOUE约束。

(2)对于指定为PRIMARY KEY的一个字段或多个字段的组合，其中任何一个字段都不能出现NULL值，而对于UNIQUE所约束的唯一码，则允许为NULL，但是只能有一个NULL值。

(3)不能为同一个字段或一组字段，既定义UNIQUE约束，又定义PRIMARY KEY约束。

2.2.4、FOREIGN KEY约束

FOREIGN KEY约束用于在两个数据表A和B之间建立连接。指定A表中某一个字段或几个字段作为外码，其取值是B表中某一个主码值或唯一码值，或者取空值。其中，包含外码的表A称为从表，包含外码所引用的主码或唯一码的表B称为主表。通过FOREIGN KEY约束可以保证两表间的参照完整性。其语法格式如下。

[CONSTRAINT <约束名>] FOREIGN KEY (<从表A中字段名>[{,<从表A中字段名>}])
REFERENCES <主表B表名> (<主表B中字段名>[{,<主表B中字段名>}])
[ON DELETE {RESTRICT|CASCADE|SET NULL|NO ACTION}]
[ON UPDATE {RESTRICT|CASCADE|SET NULL|NO ACTION}]

注意：在主表B表名后面指定的字段名或字段名的组合必须是主表的主码或候选码。

对主表B进行删除(DELETE)或更新(UPDATE)操作时，若从表A中有一个或多个对应匹配行外码，则主表B的删除或更新行为取决于定义从表A的外码时指定的ON DELETE/ON UPDATE子句。上述语法格式中，部分项目的解释如下。

RESTRICT：拒绝对主表B的删除或更新操作。若有一个相关的外码值在主表B中，则不允许删除或更新B表中主要码值。
CASCADE：在主表B中删除或更新时，会自动删除或更新从表A中对应的记录。
SET NULL：在主表B中删除或更新时，将子表中对应的外码值设置为NULL。
NO ACTION：和RESTRICT相同，InnoDB拒绝对主表B的删除或更新操作。

示例，建立选课表sc_foreign，定义学号sno和课程号cno为表的外码。

CREATE TABLE 'sc_foreign' (
  'sno' CHAR(10) NOT NULL COMMENT '学号',
  'cno' CHAR(10) NOT NULL COMMENT '课程号',
  'score' DECIMAL(5,2) COMMENT '成绩',
  FOREIGN KEY ('cno') REFERENCES 'c' ('con'),
  FOREIGN KEY ('sno') REFERENCES 's' ('son')
)ENGING=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

说明：

(1)主表B必须是数据库中已经存在的数据表，或者是当前正在创建的数据表。如果是后一种情况，则主表B与从表A是同一个表。

(2)必须为主表B定义主码，且主码不能包含空值，但允许在外码中出现空值。

(3)从表A的外码中字段的数目和数据类型，必须和主表B的主码中字段的数目和对应字段的数据类型相同。

注意：FOREIGN KEY约束目前只可以用在使用InnoDB存储引擎创建的数据表中。由其他存储引擎创建的数据表，MySQL服务器能够解析CREATE TABLE语句中的FOREIGN KEY约束子句，但不能使用或保存。

2.2.5、CHECK约束

CHECK约束用来检查数据表中字段值所允许的范围，如月份只能输入整数，而且是限定在1~12的整数。CHECK约束通过限制输入值强制域的完整性，在更新表中数据的时候，系统会检查更新后的数据是否满足CHECK约束中的限定条件。

CHECK既可用于列约束，又可用于表约束，其语法格式如下。

1	`CHECK(<条件>)`

对上述语法格式的相关说明如下。

(1)“条件”用于指定需要检查的限定条件。

(2) MySQL可以使用简单的表达式来实现CHECK约束，也可以使用复杂的表达式作为限定条件，例如在限定条件中加入子查询。

(3)若将CHECK约束子句置于所有字段的定义以及主码约束和外码定义之后，则这种约束也称为CHECK的表约束。这种约束可以同时对表中多个字段设置限定条件。

示例：建立选课表sc_check，定义成绩score的取值范围为0~100。

CREATE TABLE 'sc_check' (
  'sno' CHAR(10) NOT NULL,
  'cno' CHAR(10) NOT NULL,
  'score' DECIMAL(5,2) CHECK(score>=0 AND score<=100),
  PRIMARY KEY ('sno', 'cno')
)ENGING=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

注意：目前的MySQL版本只对CHECK约束进行分析处理，不会报错。

2.3、修改数据表

随着应用环境和需求的变化，我们可能要修改数据库中已经存在的数据表。可以使用MySQL Workbench和SQL语句修改数据表。

MySQL使用SOL中的ALTER TABLE语句来修改表名、修改字段数据类型、修改字段名、添加和删除字段、更改表的存储引擎等。

2.3.1、ADD

ADD用于增加新字段和完整性约束，其语法格式如下。

1	`ALTER TABLE <表名> ADD [<新字段名> <数据类型>] [<完整性约束定义>] [FIRST\|AFTER 已有字段名];`

其中，“FIRST”为可选项，若使用，则将新添加的字段设置为表的第一个字段；“AFTER”为可选项，若使用，则将新添加的字段添加到指定的“已有字段名”之后。

示例，在学生表s中增加一个班号class_no字段。

1 2	`ALTER TABLE s ADD class_no VARCHAR(6);`

示例，在学生表s中,在年龄age字段后增加一个家庭住址address字段。

1 2	`ALTER TABLE s ADD address NVARCHAR(20) AFTER age;`

注意：添加多个字段与添加一个字段有所不同，主要表现在以下两个方面。

添加多个字段时不能指定位置关系，只能添加在数据表的末尾。
添加多个字段时必须用小括号括起来。

示例，在学生表s中增加班号class no和家庭住址address字段。

1 2	`ALTER TABLE s ADD (class no VARCHAR(6), address NVARCHAR(20));`

示例，在学生表s中增加完整性约束定义，使年龄在15~60岁之间。

1 2	`ALTER TABLE s ADD CONSTRAINT s_chk CHECK (age BETWEEN 15 AND 60);`

其中，s_chk为用户定义的CHECK约束名。CONSTRAINT s_chk可以省略，若省略，则系统自动为CHECK约束提供一个约束名。

注意：在增加NOT NULL约束时，语法结构不同于其他完整性约束，如下所示。

1
2
3

ALTER TABLE <数据表名>
CHANGE [COLUMN] <字段名>
<字段名> <数据类型> NOT NULL;

2.3.2、RENAME

RENAME用于修改表名，其语法格式如下。

1 2	`ALTER TABLE <旧表名> RENAME [TO] <新表名>`

示例，把学生表s的名称改为student。

1 2	`ALTER TABLE s RENAME student;`

注意：修改表名并不修改数据表结构，因此，修改表名后的数据表结构与修改表名之前一样。

2.3.3、CHANGE

CHANGE用于修改字段名，其语法格式如下。

1 2	`ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;`

示例，把学生表s中字段名称sn改为sname。

1 2	`ALTER TABLE s CHANGE sn sname VARCHAR(45);`

注意：即使不需要修改字段的数据类型，也不能省略"<新数据类型>"，只需把新数据类型设置为与原字段一致即可。

2.3.4、MODIFY

MODIFY可用于修改字段数据类型和字段排序，其语法格式如下。

1 2	`ALTER TABLE <表名> MODIFY <字段名> <数据类型> [FIRST\|AFTER 字段名2];`

其中，在修改字段数据类型时，"<数据类型>“指修改后字段的新数据类型。在修改字段排序时，若使用FIRST，则将”字段名1“修改为表的第一个字段；若使用AFTER，则将”字段名1“插入”字段名2“后面。在修改字段排序时，”<数据类型>“不可省略。

示例，把学生表s中姓名sn的数据类型由VARCHAR (45)改为CHAR (30)。

1 2	`ALTER TABLE s MODIRY sn CHAR(30);`

示例，把学生表s中的年龄age插到性别sex之前。

1 2	`ALTER TABLE s MODIRY sex ENUM('男', '女') AFTER age;`

2.3.5、ENGINE

ENGINE用于修改表的存储引擎，其语法格式如下。

1 2	`ALTER TABLE <表名> ENGINE=<修改后存储引擎引擎名>;`

示例，把学生表s的存储引擎改为MyISAM。

1 2	`ALTER TABLE s ENGINE=MyISAM;`

注意：若被修改表有外码，则存储引擎不能由InnoDB修改为MyISAM，因为MyISAM不支持外码。

2.3.6、DROP

DROP用于删除字段和完整性约束。

①删除字段的语法格式如下。

1 2	`ALTER TABLE <旧表名> DROP <字段名>；`

示例，删除学生表s中新添加的字段class_no和address。

1 2	`ALTER TABLE s DROP class_no, DROP address;`

②删除完整性约束的语法格式如下。

1 2	`ALTER TABLE <表名> DROP CONSTRAINT <约束名>;`

示例，删除学生表s中的CHECK约束s_chk。

1 2	`ALTER TABLE s DROP CONSTRAINT s_chk;`

删除完整性约束的相关说明如下。

a. 删除主码约束时，由于一个表中只能有一个主码约束，因此不需要指定主码名就可以删除。删除主码约束的语法格式如下。

1 2	`ALTER TABLE <表名> DROP PRIMARY KEY;`

b.删除NOT NULL约束时，语法格式如下。

1 2	`ALTER TABLE <表名> CHANGE [COLUMN] <字段名> <字段名> <数据类型> NULL;`

c. 若在定义完整性约束或添加完整性约束时没有指定约束名，可以通过SHOw CREATE TABLE语句查看数据表结构，从而查看约束名，详见5.2.5小节。

2.4、删除数据表

若某个表已不再使用，可将其删除。删除后，该表的定义和数据均会被删除。我们可以使用MySQL Workbench和SQL语句删除数据表。

在MySQL中，使用SQL中的DROP TABLE语句可以删除一个或多个表，语法格式如下。

1	`DROP TABLE [IF ExISTS] <表名>;`

其中，IF ExISTS为可选项，用于在删除前判断删除的表是否存在，若不存在，DROP TABLE语句可以顺利执行，但会发出警告。若不加IF EXISTS，且被删除的表不存在，则MySQL会报错。

示例：删除学生表s。

1	`DROP TABLE IF EXISTS s;`

2.5、查看数据表

2.5.1、查看已创建的数据表

创建好数据表之后，可以通过SHOW TABLES语句查看数据库中已经创建的数据表。其语法格式如下。

1	`SHOW TABLES;`

2.5.2、查看数据表结构

创建好数据表之后，可以通过MySQL Workbench查看数据表结构，也可以通过SQL中的DESCRIBE (DESC)和SHOW CREATE TABLE语句查看数据表结构。

使用DESCRIBE (DESC)和SHOW CREATE TABLE语句查看数据表结构,语法格式分别如下。

1 2	`DESCRIBE/DESC <表名>; SHOW CREATE TABLE <表名>;`

通过DESCRIBE (DESC)语句可以查看表的字段信息，通过SHOW CREATE TABLE语句可以查看创建表时的详细语句。

示例：使用DESCRIBE语句查看学生表s的结构。

1 2	`USE teaching; DESCRIBE s;`

示例：使用DESC语句查看学生表s的结构。

1 2	`USE teaching; DESC s;`

示例：使用SHOw CRETE TABLE语句查看学生表s的结构。

1 2	`USE teaching; SHOw CREATE TABLE s;`

可以看出，通过DESCRIBE (DESC)语句可以查看学生表s的字段信息，包括字段名、数据类型、约束等。使用SHOW CREAFAB语句可以查看学生表s在创建时的详细语句，还可查看存储引擎和字符集等。

2.5.3、查看数据表中数据

创建好数据表之后，可以通过MySQL Workbench或SQL中的SELECT语句查看数据表中的数据。

三、数据表中数据的操纵

在MySQL中，用户可以使用MySQL Workbench或数据操纵语言(DML)来实现数据表中数据的添加、修改和删除。

3.1、向数据表中添加数据

添加数据是把新记录添加到一个已存在的数据表中。可以使用MySQL Workbench或SQL中的INSERT/REPLACE语句来实现数据表中数据的添加。

MySQL使用INSERT/REPLACE语句添加数据，可以添加一条记录的所有数据值，也可以添加一条记录的部分数据值，还可以添加多条记录。

（1）添加一条新记录

在数据表中添加一条新记录的语法格式如下。

1	`INSERT\|REPLACE INTO <表名>[(<字段名1>[,<字段名2>...])] VALUES(<值>);`

其中，<表名>是指要添加新记录的表；<字段名n>是可选项，指定待添加数据的字段；VAlUES子句指定待添加数据的具体值。字段名的排列顺序不一定要和表定义时的顺序一致，但当指定字段名时，VALUES子句中值的排列顺序必须和指定字段名的排列顺序一致，且个数相等，数据类型一一对应。

示例，分别使用INSERT和REPLACE语句在学生表s中添加一条学生记录（学号为"9"，姓名为“郑冬”，性别为“女”，年龄为"21"，专业为“计算机”，院系为“信息学院”）。

①使用INSERT语句添加记录

1 2	`INSERT INTO s(sno, sn, age, sex, maj, dept) VALUES ('s9', ‘郑冬1, 21, '女'，’计算机'，’信息学院');`

②使用REPLACE语句添加记录

1 2	`REPLACE INTO s(sno, sn, age, sex, maj, dept) VALUES ('s9'，郑冬，21, ‘女’, 计算机，‘信息学院’);`

注意：

①必须用逗号将各个数据分开，字符型数据要用单引号括起来；

②如果INTO子句中没有指定字段名，则新添加的记录必须在每个字段上均有值，且VALUES子句中值的排列顺序要和表中各字段的排列顺序一致；

③ 使用REPLACE语句添加记录时，如果要添加的新记录的主码或UNIQUE约束的字段值已存在于表中，则需删除已有记录后再添加新纪录。

(2)添加一条记录的部分数据值

示例，在选课关系表sc中添加一条选课记录(‘s7’, 'c1)。

1 2	`INSERT INTO sc(sno, cno) VALUES ('s7', 'c1');`

将VALUES子句中的值按照INTO子句中指定字段名的顺序添加到表中，对于INTO子句中没有出现的字段，新添加的记录在这些字段上将被赋NULL值，如上例中score即被赋NULL值。但在表定义时有NOT NULL约束的字段不能取NULL值，添加记录时必须给其赋值。

(3)添加多条记录

使用INSERT|REPLACE语句可以同时插入多条记录，语法格式如下。

1	`ІNSERT\|REPLACE INTO <表名>[(<字段名1>[,<字段名2>...])] VALUES(<值列表1>[,<值列表2>...]);`

示例，在选课关系表sc中添加3条选课记录('s8, 'cl)、 ('s8, 'c2)、 ('s8, ‘c5’)。

INSERT INTO sc (sno, cno)
VALUES 
('s8', 'c1'),
('s8', 'c2'),
('s8', 'c5');

注意：并非所有DBMS均支持多条记录同时添加操作，实际开发中，建议使用逐条插入语句，这样兼容性更好。

3.2、修改数据表中数据

修改数据表中的数据即对数据表中已经存在的数据进行修改。在MySQL中，用户可以使用MySOL Workbench或SQL中的UPDATE语句对表中的一条或多条记录的某些字段值进行修改。

使用SQL语句修改数据表中数据的语法格式如下。

1
2
3

UPDATE <表名>
SET <字段名>=<表达式>[,<字段名>=<表达式>]...
[WHERE <条件>]

其中，<表名>指要修改的表；SET子句给出要修改的字段及其修改后的值；WHERE子句指定待修改的记录应当满足的条件，WHERE子句省略时，修改表中的所有记录。

(1)修改一条记录

示例，把刘杨老师转到工学院。

1
2
3

UPDATE t
SET dept='工学院'
WHERE tn='刘杨'；

(2)修改多条记录

示例，把所有学生的年龄增加1岁。

1 2	`UPDATE s SET age=age+1;`

说明：用户还可以通过子查询来指定满足更新条件的记录。具体内容可参考第6章。

3.3、删除数据表中数据

删除数据表中数据即删除数据表中已经存在的数据。在MySQL中，用户可以通过MySQL Workbench或SQL中的DELETE语句来删除数据表中的一条或多条记录。

使用SQL语句删除数据表中数据的语法格式如下。

1
2
3

DELETE
FROM <表名>
[WHERE <条件>]

其中，<表名>指要删除数据的表；WHERE子句指定待删除的记录应当满足的条件，WHERE子句省略时，数据库系统会删除表中的所有记录。

(1)删除一条记录

示例，删除成绩为90.5分的记录。

1
2
3

DELETE
FROM sc 
WHERE score=90.5;

通过WHERE子句可以把分数为90.5分的记录删除。

(2)删除多条记录

示例，删除所有教师的授课记录

1 2	`DELETE FROM tc;`

执行上述语句后，tc表即为一个空表，但其定义仍存在数据字典中。

删除多条记录可以使用DELETE语句，也可以使用TRUNCATE语句。TRUNCATE语句主要用于清空表数据，其语法格式如下。

1	`TRUNCATE [TABLE]`

示例，删除所有教师的授课记录。

1	`TRUNCATE TABLE tc;`

DELETE和TRUNCATE存在以下区别：DELETE TABLE删除内容、不删除定义、不释放空间；TRUNCATE TABLE删除内容、不删除定义但释放空间。

四、小结

本章介绍了MySQL支持的数据类型等基础知识。数据表是数据库存储数据的基本单位，本章主要讲述了MySQL中数据表的基本操作和数据表中的数据操纵。本章通过MySQL Workbench 和SQL语句两种方式分别介绍了数据表的创建、修改、删除、查看，以及数据表中数据的添加、修改和删除。

五、参考

《数据库原理及应用教程（MySQL版）》

《数据库系统原理自考04735》

数据库

#数据库原理

数据库原理：表管理和表中数据操纵

https://kuberxy.github.io/2024/06/10/数据库原理5：表管理和表中数据操纵/

作者

Mr.x

发布于

2024年6月10日

许可协议

数据库原理：表中数据查询上一篇

数据库原理：数据库的创建和管理下一篇