创建和操纵表

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

创建表

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

  • 使用具有交互式创建和管理表的工具。
  • 直接用MySQL语句操纵。

可使用SQL的CREATE TABLE语句来创建表。

创建表基础

使用CREATE TABLE语句所需的信息：

  • 新表的名字，位置跟于CREATE TABLE后。
  • 表列的名字和定义，用逗号分隔。

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_zip     char(10)   NULL,
    cust_country char(50)   NULL,
    cust_contact char(50)   NULL,
    cust_email   char(255)  NULL,
    PRIMARY KEY(cust_id)
) ENGINE=InnoDB;

此语句可以看出，表名跟于CREATE TABLE关键字后。表列的定义括在圆括号中，各列用逗号分开。每列的定义以列名开始（列名必须唯一），后跟列的数据类型（MySQL必知必会——第一章了解SQL）。表的主键可以使用PRIMARY KEY关键字指定。

  • 语句格式化 MySQL语句忽略空格，所以语句可以写成一行，或是写成多行。为了方便理解代码，推荐采用某种便于理解和阅读的缩减格式。
  • 处理现有的表 创建新表时，指定的表名必须在库中不存在，否则将会报错。为了防止有重复表名，应先删除该表，再进行创建。如果只是想在该表名不存在时，创建该表，可以在表名后使用IF NOT EXISTS关键字。此关键字检查是否有重复表名，并在表名不重复的情况下创建新表。

使用NULL值

NULL值就是没有值或缺值（MySQL必知必会——第六章过滤数据）。允许NULL值的列，可以在更新和插入时不给出该列的值，但不允许NULL值的列不行。

每个表列是NULL列还是NOT NULL列，在表创建时由表定义规定：

CREATE TABLE orders
(
    order_num   int         NOT NULL AUTO_INCREMENT,
    order_date  datetime    NOT NULL,
    cust_id     int         NOT NULL,
    PRIMARY KEY(order_num)
) ENGINE=InnoDB;

此语句创建的是本专栏所用的orders表。它包含3个列，订单号、订单日期和客户ID。这3个列都必须要值，所以每个列都定义了NOT NULL。这会阻止插入没有值的列。

创建本专栏所用的vendors表：

CREATE TABLE vendors
(
    vend_id         int         NOT NULL AUTO_INCREMENT,
    vend_name       char(50)    NOT NULL,
    vend_address    char(50)    NULL,
    vend_city       char(50)    NULL,
    vend_zip        char(10)    NULL,
    vend_country    char(50)    NULL,
    PRIMARY KEY(vend_id)
) ENGINE=InnoDB;

供应商ID和供应商名是必须的，所以定义为NOT NULL。其余5个列都允许NULL，所以不指定NOT NULL。NULL为默认设置，如果不指定NOT NULL，则为NULL。

  • 理解NULL 不要将NULL值与空串弄混。NULL值是没有值，它不是空串。如果指定空串’’（两个单引号，其间没有字符），这在NOT NULL列是允许的，它是一个有效值。

主键再介绍

主键值是唯一的。每个行都必须有一个唯一的主键值，如果主键由一列构成，则列值唯一，如果主键由几列构成，则各个列的组合值唯一。

目前为止看到的都是单个列作为主键的表，它用类似下面的语句定义：

PRIMARY KEY(vend_id)

为了创建多个列组成的主键，应在括号中给出以逗号分隔的列名：

CREATE TABLE orderitems
(
    order_num   int             NOT NULL,
    order_item  int             NOT NULL,
    prod_id     char(10)        NOT NULL,
    quantity    int             NOT NULL,
    item_price  decimal(8,2)    NOT NULL,
    PRIMARY KEY(order_num, order_item)
) ENGINE=InnoDB;

orderitems表包含orders表中每个订单的细节。每个订单有多个物品，但每个订单不应有多个相同物品信息。因此，订单号（order_num列）和订单物品（order_item列）的组合是唯一的，所以适合作为主键。

  • 主键和NULL值 主键为表中每行的唯一标识的列，所以主键中只能使用不允许NULL值的列，允许NULL值的列，不能作为唯一标识。

使用AUTO_INCREMENT

在customers表和orders表中，都有一个列，作为唯一标识（cust_id列和order_num列）。它们除了是唯一的以外就没有其他的意义，所以在添加新数据时，这些编号可以是任意数值，只要在表中是唯一的。

显然最简单的编号是下一个编号（大于当前最大编号的编号）。我们可以使用SELECT语句得出最大的数（使用Max()函数，MySQL必知必会——第十二章汇总数据），然后对这个数加1。但这样并不可靠（可能使用SELECT找出编号的同时，别人也执行了INSERT导致编号被占用），并且效率不高（执行了额外的MySQL操纵）。

最佳的办法是使用AUTO_INCREMENT，它告诉MySQL，本列每当增加一行时自动增量。每执行一个INSERT操纵时，MySQL自动对该列增量，给该列赋予下一个可用的值。

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，成为主键）。

  • 覆盖AUTO_INCREMENT 如果一个列被定义为AUTO_INCREMENT，我们可以在INSERT语句中指定一个值（表中唯一），在后续的增量将使用手动插入的值。原理：AUTO_INCREMENT只记录最大的列值，增量以该值为基础。
  • 确定AUTO_INCREMENT值 MySQL生成主键的一个缺点是你不知道这些值是什么。但我们可以通过last_insert_id()函数获得这个值：
SELECT last_insert_id()

指定默认值

如果在插入时不给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。

CREATE TABLE orderitems
(
    order_num   int             NOT NULL,
    order_item  int             NOT NULL,
    prod_id     char(10)        NOT NULL,
    quantity    int             NOT NULL DEFAULT 1,
    item_price  decimal(8,2)    NOT NULL,
    PRIMARY KEY(order_num, order_item)
) ENGINE=InnoDB;

此语句创建包含组成订单的各物品的orderitems表。quantity列包含订单中每项物品的数量。DEFAULT 1指示MySQL在插入时未给出数量的情况下使用数量1。

  • 不允许函数 与大多数DBMS不同，MySQL不允许使用函数作为默认值，它只支持常量。
  • 使用默认值而不是NULL值 在插入时若不给值，最好使用默认值而不是NULL值。这有益于计算或数据分组等。

引擎类型

目前使用的CREATE TABLE语句都以ENGINE=InnoDB结尾。

与其他DBMS一样，MySQL有一个具体管理和处理数据的内部引擎。在使用CREATE TABLE语句时，由该引擎具体创建表；在使用SELECT语句或进行其他数据库处理时，由该引擎在内部处理你的请求。一般，我们无需多关注引擎。

但MySQL与其他DBMS不一样，它具有多种引擎。它们各自具有不同的功能和特征，为不同的任务选择正确的引擎能获得良好的功能和灵活性。

如果省略ENGINE语句，则会使用默认引擎（一般为MyISAM）。

常用的引擎：

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

引擎类型可以混用。本专栏所用数据库除productnotes表使用MyISAM（支持全文本搜索）外，其他表使用InnoDB。MySQL必知必会作者希望支持事务处理。

外键不能跨引擎 混用引擎类型有一个大的缺陷。外键不能跨引擎。

更新表

为更新表的定义，可用ALTER TABLE语句。但理想状态下，表中存储数据后，该表不应该再次被更新。

使用ALTER TABLE所需信息：

  • 在ALTER TABLE后给出要更改的表名（该表必须存在）。
  • 所做更改的列表。

给表添加列：

ALTER TABLE vendors
ADD vend_phone CHAR(20);

此语句给vendors表增加一个名为vend_phone的列，必须明确其数据类型。

删除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 orderitems
ADD CONSTRAINT fk_orderitems_products FOREIGN KEY(prod_id)
REFERENCES products(prod_id);

进行复杂的表结构更改一般需要手动删除过程，步骤如下：

  1. 用新的列布局创建一个新表；
  2. 使用INSERT SELECT语句（）复制数据从旧表到新表。
  3. 检验包含所需数据的新表。
  4. 重命名旧表（可以删除）。
  5. 用旧表名重新命名新表。
  6. 根据需要，重新创建触发器、存储过程、索引和外键。

小心使用ALTER TABLE 使用ALTER TABLE要极为小心，最好使用前先做一个完整的备份。数据库表的更改不能撤销。

  • ALTER TABLE其他子句 ALTER TABLE还拥有很多其他的子句，可自主查询。

删除表

删除表非常简单，使用DROP TABLE语句即可：

DROP TABLE customers2;

这条语句删除customers2表（如果存在）。删除表没有二次确认，也不能撤销，执行这条语言将永远删除该表。谨慎使用！！！

重命名表

使用RENAME TABLE语句可以重命名一个表：

RENAME TABLE customers2 TO customers;

此语句将customers2重命名为customers。此语句也可以将多个表命名（用逗号分隔）：

RENAME TABLE backup_customers TO customers,
             backup_vendors TO vendors,
             backup_products TO products;