创建表

  • 一般有两种创建表的方法：
    1. 使用具有交互式创建和管理表的工具；
    2. 表也可以直接用MySQL语句操纵。

表创建基础

  • 为利用CREATE TABLE创建表，必须给出以下信息：
    1. 新表的名字，在关键字CREATE TABLE之后给出；
    2. 表列的名字和定义，用逗号分隔。
1
2
3
4
5
6
7
8
9
10
11
12
13
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(50)    NULL,
    cust_zip        char(50)    NULL,
    cust_country    char(50)    NULL,
    cust_contact    char(50)    NULL,
    cust_email      char(50)    NULL,
    PRIMARY KEY (cust_id)
)   ENGINE = InnoDB;

  • 语句格式化：MySQL语句中忽略空格，以最适合自己的方式安排语句的格式，以便阅读和编辑。

  • 处理现有的表：在创建新表时，指定的表名必须不存在。

    如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单的用创建表语句覆盖他。

    如果仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。

使用NULL值

  • NULL值就是没有值或缺值。

    允许NULL值的列也允许在插入行时不给出该列的值，不允许NULL值的列不接受该列没有值的行。

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建混合了NULL和NOT NULL列的表
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_state      char(50)    NULL,
    vend_zip        char(50)    NULL,
    vend_country    char(50)    NULL,
    PRIMARY KEY (vend_id)
)   ENGINE = InnoDB;

  • NULL为默认设置，如果不指定NOT　NULL，则认为指定的是NULL。

  • 理解NULL：不要把NULL值与空串相混淆。NULL值是没有值，它不是空串。

    如果指定''（两个单引号，其间没有字符），这在NOT NULL列中是允许的。

    空串是一个有效的值，它不是无值。NULL值用关键字NULL而不是空串指定。

主键再介绍

  • 如果主键使用单个列，则它的值必须唯一；

    如果使用多个列，则这些列的组合值必须唯一。

1
2
3
4
5
6
7
8
9
10
-- 创建由多个列组成的主键，以逗号分隔各列名
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;

  • 主键和NULL值：主键为其值唯一标识表中每个行的列。

    主键中只能使用不允许NULL值的列，允许NULL值的列不能作为唯一标识。

使用AUTO_INCREMENT

  • AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。

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

  • 覆盖AUTO_INCREMENT：如果一个列被指定为AUTO_INCREMENT，则它需要使用特殊的值吗？

    可以简单的在INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。

  • 确定AUTO_INCREMENT值：可使用last_insert_id()函数获得这个值，如下：

    SELECT last_insert_id();

指定默认值

  • 如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。

    默认使用CREATE TABLE语句的列定义中的DEFAULT关键字指定。

1
2
3
4
5
6
7
8
9
10
-- quantity列包含订单中每项物品的数量，未给出数量时使用数量1
CREATE TABLE orderitems
(
    order_num   int             NOT NULL,
    order_item  int             NOT NULL,
    prod_id     char(50)        NOT NULL,
    quantity    int             NOT NULL DEFAULT 1,
    item_price  decimal(8, 2)   NOT NULL,
    PRIMARY KEY (order_num, order_item)
)   ENGINE = InnoDB;
  • 不允许函数：与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量。
  • 使用默认值而不是NULL值：使用默认值而不是NULL列，特别是对用于计算或数据分组的列更是如此。

引擎类型

  • 与其他DBMS一样，MySQL有具体管理和处理数据的内部引擎。

    在使用CREATE TABLE语句时，引擎具体创建表，而在使用SELECT语句或进行其他数据库处理时，引擎在内部处理你的请求。多数时候，引擎都隐藏在DBMS内，不需要过多关注它。

  • MySQL与其他DBMS不一样，它有多种引擎。

    它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令。

  • 为什么要发行多种引擎呢？

    因为他们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。

  • 可以忽略数据库引擎。

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

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

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

  • 外键不能跨引擎：使用一个引擎的表不能引用具有使用不同引擎的表的外键。

所支持引擎的完整列表（及他们之间的不同），请参阅http://dev.mysql.com/doc/refman/5.0/en/storage_engines.html

更新表

  • 理想状态下，当表中存储数据以后，该表就不应该再被更新。
  • 为了使用ALTER TABLE更改表结构，必须给出以下信息：
    1. 在ALTER TABLE之后给出要更改的表名（该表必须存在）；
    2. 所做更改的列表。
1
2
3
4
5
6
7
8
9
10
11
12
-- 给表添加一个列
ALTER TABLE vendors
ADD vend_phone CHAR(20);

-- 删除刚添加的列
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：数据库表的更改不能撤销，应该在改动前做一个完整的备份（模式和数据的备份）。

删除表

1
2
-- 删除一个表
DROP TABLE customers2;
  • 删除表没有确认，也不能撤销，执行语句将永久删除表。

重命名表

1
2
3
4
5
6
7
-- 重命名一个表
RENAME TABLE customers2 TO customers;

-- 重命名多个表
RENAME TABLE backup_customers TO customers,
             back_vendors TO vendors,
             back_products TO products;