创建和操纵表

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

创建表

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;