数据表操作是数据库操作中最基本和最重要的操作。

图5-1是图书销售数据库booksale中存放的图书表books。

■ 图5-1图书表books

01. 表的结构

表的结构也称为“型”(Type)用于描述存储于表中的数据的逻辑结构和属性。定义表就是指定义表的结构使用数据定义语言来实现。在定义表之前首先需要注意以下几个概念。

(1) 表名在同一个数据库中每一个表都应该有一个唯一的名称。表名和数据库的名字一样都应该满足标识符命名规则。

(2) 列名从图5-1中可以看出每个表由若干列组成在同一个表中每个列的名字应该是唯一的列的名字应该符合标识符命名规则。

(3) 列的数据类型表中的每个列都要定义一个数据类型。定义数据类型时需要慎重考虑如果定义的范围太小可能会造成无法存放某些数据如果定义的范围太大可能会造成存储空间的浪费。存储空间的增加将增加系统的I/O操作量从而降低系统的使用效率。

(4) 列中是否允许有空值表中的某些列可能严禁出现空值例如若要求每本图书都必须有图书编号那么“图书编号”列就不允许有空值。某些列例如“作者”列中可能会存在空值也就是说某些图书没有明确作者或作者未知这时这些列就应该定义成允许空值。

2. 表中的数据

表中的数据也称为“值”(Value)是“型”的具体赋值。操纵表中的数据通过数据操纵语言实现。

(1) 数据行 一个数据行也被称为一个元组或一条记录是现实世界中一个物理或逻辑实体的数据描述形式。

(2) 数据列 一个数据列也被称为一个属性或一个字段是同一类型的所有实体在某个属性上的全部值的集合。列是表定义的基本对象定义一个表的主要任务就是定义这个表中的各个列。

(3) 主键 表的主键是表中的某个列或某几个列的组合其值可以唯一标识表中的每个行。一个表只能定义一个主键而且通常都应该定义一个主键。主键的值不能为空值也不能重复。如果存在多个列或列组合同时满足作为主键的条件则应该选择运算效率高的列或列组合作为表的主键。通常数值型的列比字符型的列运算效率高如果同为字符型则取值范围小的列的运算效率通常更高。

(4) 自增列 又称标识列可以将表中具有整数性质的某个列定义为自增列来唯一标识表中的每一行定义的关键词为AUTO_INCREMENT。一个表中最多只能有一个列被定义为自增列。自增列不允许为空值也不允许重复自增列必须是主键或主键的一部分。默认情况下自增列中的第一个值是1后续值自动加1。如果用户设置了一个非1的初始值后续值将在该值基础上自动加1。

提示/

系统数据库information_schema中的数据表为系统数据表如SCHEMATA表(提供了当前MySQL实例中所有数据库的信息SHOW DATABASES的结果取自此表)、TABLES表(提供了关于数据库中的表的信息详细表述了某个表属于哪个schema、表类型、表引擎、创建时间等信息SHOW TABLES FROM schemaname的结果取自此表)、COLUMNS表(提供了表中的列信息详细表述了某张表的所有列以及每个列的信息SHOW COLUMNS FROM schemaname.table_name的结果取自此表)等。

 

02、表的创建

1. 创建表

创建表就是在数据库中建立新表。创建表的基本语法格式如下所示。

语法说明如下。

• table_name是要定义的数据表的表名可以是字母、数字和下画线组成的任意字符串。在同一数据库中数据表名是唯一的不可与已经存在的数据表重名。

• IF NOT EXISTS是可选选项。添加该选项表示指定的数据表不存在时执行创建数据表操作否则忽略此操作。

• column是列的名字DATATYPE是该列的数据类型NOT NULL表示该列中不允许有空值NULL表示该列中允许有空值为默认选项。

• PRIMARY KEY用于定义主键。如果是某个列作为主键则可以直接在该列上定义主键约束如果由多个列组成主键则必须定义表级主键约束其形式为"PRIMARY KEY (column1 ［, column2］ ［, …］ )"。

• AUTO_INCREMENT表示将列定义为自增列。

【例5-2】在图书销售数据库booksale中创建图书表books用于存放图书的信息。

 

定义列时使用NOT NULL表示这个列在存储数据时不允许出现空值否则使用默认的属性NULL表示这个列在存储数据时允许出现空值。

如果数据表books已经存在再运行上面的命令系统会提示错误信息“Table 'books' already exists”为了防止这种错误发生在创建数据表时可以在“数据表名称”前添加IF NOT EXISTS这样命令执行后只是返回一条警告信息“Query OK, 0 rows affected, 1 warning (0.01 sec)”而已。

【例5-3】在图书销售数据库booksale中创建顾客表customers用于存放顾客的信息。

定义列cstid时使用PRIMARY KEY表示将该列定义为表的主键。定义主键时系统自动将该列定义为NOT NULL即不允许空。

【例5-4】在图书销售数据库booksale中创建订单表orders用于存放订单的信息。

定义列orderid时使用AUTO_INCREMENT表示将该列定义为自增列系统会自动在该列中生成不重复的整数序列值。定义列的AUTO_INCREMENT属性时必须将该列定义为主键或主键的一部分。

定义列orderdate时使用数据类型TIMESTAMP并且将默认值设置为current_timestamp表示插入记录时系统会自动将系统当前日期时间存入该列中。默认值约束的设置见6.3.4节。

【例5-5】在图书销售数据库booksale中创建订单项目表orderitems用于存放订单项目的信息。

该表的主键由两列组成所以这里需要使用表级主键。因为主键所在列都不允许出现空值所以即使定义主键所在列时没有使用NOT NULL系统也会自动为该列增加非空属性。

添加IF NOT EXISTS参数表示要创建的orderitems表只有在不存在时才执行该创建表命令。

 

2. 创建带JSON类型的表

新的数据类型JSON的引用可以将复杂数据存储在一个数据列中易于存储。

【例5-6】在图书销售数据库booksale中创建带有JSON类型的表t_json用于存放售货员信息然后查看数据库中已经存在的数据表。

该表的主键由一列组成可以采用列级主键也可以采用表级主键这里使用的表级主键。因为主键所在列都不允许出现空值所以无论该列是否定义NOT NULL系统都会自动为该列增加非空属性。

3. 表的复制

使用上述的CREATE TABLE命令可以根据实际需要创建表是实际开发中较常用的方式。而CREATE TABLE LIKE命令则可以对源表的模式进行复制从现有的数据表中精确地复制表的定义(不复制其数据)其创建的表除了表名和源表不一样外其余所有的细节都是一样的。复制表的基本语法格式如下所示。

语法说明如下。

• LIKE old_table_name是基于表old_table_name的定义创建空表table_name包括原始表中定义的任何列属性和索引。该子句可加括号也可不加括号。

• table_name是生成的新表名。

• TEMPORARY是可选选项用于创建临时表。临时表仅在当前会话中可见并在会话关闭时自动丢弃。

• IF NOT EXISTS是可选选项。添加该选项表示指定的数据表不存在时执行数据表复制操作否则忽略此操作。

【例5-7】在图书销售数据库booksale中创建和图书表books一样结构的临时表图书备份表booksbak。

booksbak表和books表的结构一模一样。当退出MySQL再次登录后该临时表将不再存在。SHOW TABLES命令不能看到临时表。

4. 查看表结构

查看表结构是指查看数据库中已存在的表的定义。查看表结构的语句包括DESCRIBE语句和SHOW CREATE TABLE语句通过这两个语句可以查看表的数据列名、数据列的数据类型和完整性约束条件等。

1) DESCRIBE语句查看表定义

可以使用DESCRIBE(可以缩写为DESC)命令查看表的基本定义包括数据列的列名、数据类型、是否为空、是否为主键、默认值、自增列等其基本语法格式如下所示。

【例5-8】查看orders表的结构。

执行结果如图5-2所示。

■ 图5-2查看Orders表的结构

2) SHOW CREATE TABLE语句查看表详细定义

可以使用SHOW CREATE命令查看定义表的SQL语句从而得到表的详细结构包括列的名称、数据类型、是否为空、默认值、表的存储引擎、字符编码等比使用DESC命令显示的信息要全面。SHOW CREATE TABLE命令的基本语法格式如下所示。

【例5-9】查看books表的结构。

执行结果如图5-3所示。

■ 图5-3查看books表的结构

提示/

图5-3是在命令提示符下显示的结果在显示内容较长的情况下使用“＼G”选项可以更好地显示结果。如果在客户端工具Navicat中由于显示列宽度有限可以将其复制出来查看。

03、表的修改

修改表是指修改数据库中已存在的表的定义。表创建好以后可以根据需要使用ALTER TABLE语句修改表的结构包括在表中增加新列、修改列的属性以及删除列等。

1. 增加列

增加新列的基本语法格式如下所示。

语法说明如下。

• FIRST表示新增加的列作为表的第一列也可以使用AFTER columnname的形式将新增加的列指定到columnname所表示的列之后默认情况下新增加的列是表的最后一列

• table_name是要修改的数据表的表名该表必须是数据库中已经存在的表。

• ADD COLUMN是增加新列的命令关键字其中COLUMN关键字可以省略。

• columndefinition是对新增加列的完整定义。

【例5-10】在图书表books中新增一个新列press用于存放出版社名称。该列数据类型为VARCHAR(50)允许空值。

关键词NULL表示该列允许空值由于NULL是默认设置所以该关键词可以省略。也可以通过以下两条语句完成增加列操作。

多加了一个关键字FIRST表示press列在表中第一的位置。

多加了一个关键字AFTER表示press列在author列的后面。

这三条命令添加的列名相同实操操作完一个命令后应先删除该列再继续下一个命令。

提示/

如果表中已经有数据那么在表中增加一个新列时新列中是没有数据的所以如果将增加的新列设置成不允许有空值必然产生错误。可以有两种方法解决这个问题一种是首先将新列定义成允许有空值然后向新列中输入数据后再将这个列修改为不允许有空值另一种是在添加新列时为该列定义一个默认值。

2. 修改列

修改列的基本语法格式如下所示。

语法说明如下。

• FIRST表示将修改的列调整为表的第一列也可以使用AFTER columnname的形式将修改的列指定到columnname所表示的列之后。

• table_name是要修改的数据表的表名该表必须是数据库中已经存在的表。

• MODIFY COLUMN是修改列的命令关键字其中COLUMN关键字可以省略。

• columndefinition是对修改列的完整定义。

语法说明如下。

• columndefinition是对修改列的完整定义该定义中列名可以重新命名。

•  oldcolumnname是要修改列的列名。

提示/

通过该语句不仅可以修改列的属性也可以修改列的名称。

【例5-11】修改图书表books中的出版社列press将数据类型修改为VARCHAR(20)不允许空值并将位置修改为位于作者列author之后。

【例5-12】修改图书表books将图书编号列bookid修改为自增、主键列。

【例5-13】修改订单表orders删除订单编号列orderid的自增属性。

订单编号列orderid的为空性属性和主键属性不变。

3. 删除列

删除列的基本语法格式如下所示。

将图书表books中的出版社列press的名称改为publisher其他属性不变。

语法说明如下。

• columnname是要删除列的列名。

• table_name是要修改的数据表的表名该表必须是数据库中已经存在的表。

• DROP COLUMN是删除列的命令关键字其中COLUMN关键字可以省略。

【例5-15】删除图书表books中的出版社列publisher。

4. 重命名表

数据库系统通过表名来区分不同的表表名在同一个数据库中唯一标识一张表。重命名表的基本语法格式如下所示。

语法说明如下。

• new_table_name是数据表修改后的新表名该表名在数据库中不能存在。

• table_name是要修改的数据表的表名该表必须是数据库中已经存在的表。

• RENAME［TO］是重命名表的命令关键字其中TO关键字可以省略。

【例5-16】将顾客表customers的名称重命名为users。

数据库booksale中customers表已经不存在了取而代之的是users表。

04、表的删除

删除表是指删除数据库中已存在的表。删除表将同时删除表中的数据。因此删除表操作要想好了再做。创建表时可能存在外键约束被关联的父表删除比较复杂。这里只讲没有关联的普通表的删除关联表的删除在讲解外键约束时再讲解。

删除表的基本语法格式如下所示。

语法说明如下。

• RESTRICT | CASCADE是可选选项。RESTRICT是确保只有不存在相关视图和完整性约束的表才能删除。CASCADE是任何相关视图和完整性约束一并被删除。

•  table_name是要删除的数据表的表名可以一次性删除多个数据表。

• IF EXISTS是可选选项。添加该选项表示指定的数据表存在时执行删除数据表操作否则忽略此操作。

【例5-17】删除顾客表users。

数据库booksale中users表必须存在否则命令执行将提示错误信息“ERROR 1051 (42S02): Unknown table 'booksale.***'”。