第二十一章 创建和操纵表

第二十一章 创建和操纵表

表的创建、更改和删除的基本知识。

21.1 创建表

MySQL不仅用于表数据操作，而且还可以用来执行数据库和表的所有操作，包表本身的创建和处理。
一般有两种创建表的方法：

1. 使用具有交互式创建和管理表的工具；
2. 表也可以直接使用MySQL语句操纵。

为了用程序创建表，可使用SQL的CREATE TABLE语句。值得注意的是，在使用交互式工具时，实际上使用的是MySQL语句。但是，这些语句不是用户编写的，界面工具会自动生成并执行相应的MySQL语句（更改现有表时也是这样）。

21.1.1 表创建基础

为利用CREATE TABLE创建表，必须给出下列信息：

1. 新表的名字，在关键字CREATE TABLE之后给出；
2. 表列的名字和定义，用逗号分隔。

CREATE TABLE语句也可能会包含其他关键字或选项，但至少要包括表的名字和列的细节。如下创建customers表：

CREATE TABLE customers   
(
	cust_id      int        NOT NULL AUTO_INCREMENT,
	cust_name    char(50)   NOT NULL,
	cust_address char(50)   NULL,
	cust_city    char(50)   NULL,
	cust_state   char(5)    NULL,
	cust_email   char(255)  NULL,
	PRIMARY KEY (cust_id)
)ENGINE=InnoDB；

从上例可以看出，表名紧跟在CREATE TABLE关键字后面。实际的表定义（所有列）括在圆括号之中。各列之间用逗号分隔。每列的定义以列名（它在表中必须是唯一的）开始，后跟列的数据类型（插入链接）。表的主键可以在创建表时用PRIMARY KEY关键字指定。

• 处理现有的表：在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。
  *如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。*这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。

21.1.2 使用NULL值

NULL值就是没有值或缺值。
允许NULL值的列也允许在插入行时不给出该列的值。
不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值。
每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由标的定义规定。如下例：

CREATE TABLE orders
(
	order_num    int       NOT NULL AUTO_INCREMENT,
	order_date   datetime  NULL,      //允许NULL
	cust_id      int       NOT NULL,  //不允许NULL
	PRIMARY KEY (order_num)
)ENGINE=InnoDB；

• NULL为默认设置，如果不指定NOT NULL，则认为指定的是NULL。
• 理解NULL：**不要把NULL值与空串相混淆。**NULL值是没有值，它不是空串。如果指定’’（两个单引号，其间没有字符），这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值。NULL值用关键字NULL而不是空串指定。

21.1.3 主键再介绍

**主键值必须唯一。**即，表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。
单个主键用以下的类似的语句定义：
PRIMARY KEY (vend_id)
为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名，其定义如下：
PRIMARY KEY (order_num，order_item)

• 主键可以在创建表时定义，或者在创建表之后定义。
• *主键和NULL值：*主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。

21.1.4 使用AUTO_INCREMENT

**AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。**每次执行一个INSERT操作时，MySQL自动对该列增量（从而才有这个关键字AUTO_INCREMENT),给该列赋予下一个可用的值。这样给每一行分配一个唯一的id，从而可以用作主键值。

• 每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。
• 覆盖AUTO_INCREMENT：如果一个列被指定为AUTO_INCREMENT，则它需要使用特殊的值吗？你可以简单地在INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。后续的增量奖开始使用该手工插入的值。
• *确定AUTO_INCREMENT:*让MySQL生成（通过自动增量）主键的一个缺点是你不知道这些值都是谁。
  如：你正在增加一个新订单。这要求在orders表中创建一行，然后再orderitms表中对订购的每项物品创建一行。order_num在orderitems表中与订单细节一起存储。这就是为什么orders表和orderitems表为相互关联的表的原因。这显然要求你在插入orders行之后，插入orderitems行之前知道生成的order_num。
  那么，如何在使用AUTO_INCREMENT列时获得这个值呢？可使用last_insert_id()函数获得这个值，如下所示：
  SELECT last_insert_id()
  此语句返回最后一个AUTO_INCREMENT值，然后可以将它用于后续的MySQL语句。

21.1.5 指定默认值

如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT(default)关键字指定。如下：

CREATE TABLE customers   
(
	cust_id      int        NOT NULL AUTO_INCREMENT,
	cust_name    char(50)   NOT NULL  DEFAULT tom，  //名字使用默认值tom
	cust_address char(50)   NULL,
	cust_city    char(50)   NULL,
	cust_state   char(5)    NULL,
	cust_email   char(255)  NULL,
	PRIMARY KEY (cust_id)
)ENGINE=InnoDB；

• *不允许函数：*与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量。
• *使用默认值而不是NULL值：*许多数据库开发人员使用默认值而不是NULL列，特别是对用于计算或数据分组的列更是如此。

21.1.6 引擎类型

与其他DBMS一样，MySQL有一个具体管理和处理数据的内部引擎。在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。多数时候，此引擎都隐藏在DBMS内，不需要过多关注它。
但MySQL与其他DBMS不一样，它具有多种引擎。它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令。
为什么要发行多种引擎呢？因为它们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。

• 如果省略ENGINE=语句，则使用默认引擎（很可能是MyISAM），多数SQL语句都会默认使用它。但并不是所有语句都默认使用它，这就是为什么ENGINE=语句很重要的原因。

以下是几个需要知道的引擎：

1. InooDB是一个可靠的事务处理引擎，它不支持全文本搜索；
2. MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
3. MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

• 引擎类型可以混用。

21.2 更新表

为更新表定义，可使用ALTER TABLE语句。但是，理想状态下，当表中存储数据以后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。
为了使用ALTER TABLE更改表结构，必须给出下面的信息：

1. 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）；
2. 所做更改的列表。

例子如下：

ALTER TABLE vendors
ADD vend_phone CHAR(20);

该语句给vendors表增加一个名为vend_phone的列，必须明确其数据类型。
删除刚刚增加的列，可以如下：

ALTER TABLE vendors
DROP COLUMN vend_phone；

ALTER TABLE的一种常见用途是定义外键。如下图：

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders (order_num);

• 若要更改多个表，可以使用单条ALTER TABLE语句，每个更改用逗号分隔。

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

1. 使用新的列布局创建一个新表；
2. 使用INSERT SELECT语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；
3. 检验包含所需数据的新表；
4. 重命名旧表（如果确定，可以删除它）；
5. 用旧表原来的名字重命名新表；
6. 根据需要，重新创建触发器、存储过程、索引和外键。

• **小心使用ALTER TABLE:**使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增减了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

21.3 删除表

删除表（删除整个表而不是其内容）非常简单，使用DROP TABLE语句即可：
DROP TABLE customers；

• 删除表没有确认，也不能撤销，执行这条语句将永久删除该表。

21.4 重命名表

使用RENAME TABLE语句可以重命名一个表：
RENAME TABLE customers TO customers1;

• RENAME TABLE所做的仅是重命名一个表。可对对多个表重命名，使用多个TO，中间用逗号分隔。

21.5 小结

介绍了几条新SQL语句：

1. CREATE TABLE:用来创建新表；
2. ALTER TABLE:用来更改表列（或其他诸如约束或索引等对象）；
3. DROP TABLE:用来完整地删除一个表。

• 这些语句必须小心使用，并且应在做了备份后使用。