mysql必知必会(21)：创建和操纵表

一、创建表

1. 两种创建表的方法

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

2. 表创建的基础

利用create table创建表，必须给出以下信息：

• 新表的名字，在关键字create table之后给出
• 表列的名字和定义，用逗号分隔

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;

注意：处理现有的表
在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。

注意：表不存在则创建
如果你仅想在一个表不存在则创建它，应该在表名后给出IF NOT EXISTS。

这样不做检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。

3. 使用NULL值

• NULL：没有值或缺值
• 允许null值的列也允许在插入行时不给出该列的值。

区分：null与空串

不要把null与空串相混淆。

• NULL：没有值
• 空串：一个有效的值，并不是无值

4. 主键

主键值必须唯一。

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

主键可以在创建表时定义，也可以在创建表只会定义。

主键和NULL

主键作为唯一标识表中每个行的列。

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

5. AUTO_INCREMENT

• AUTO_INCREMENT告诉MySQL，本列每当增加一行时，自动增量。
• 每个表只允许一个AUTO_INCREMENT列，而且它必须被索引

确定AUTO_INCREMENT的值

如何在使用AUTO_INCREMENT列时获得这个值？

使用last_insert_id()函数获得这个值

SELECT last_insert_id()

此语句返回最后一个AUTO_INCREMENT的值，然后可以将它用于后续的MySQL语句。

6. 指定默认行

注意：默认行不允许函数

与大多数DBMS不一样，MySQL不允许使用函数作为默认值，只支持常量。

使用默认值而不是NULL

许多数据库开发人员使用默认值而不是NULL值，特别是对用于计算或数据分组的列更是如此。

7. 引擎类型

几个需要知道的引擎：

• InnoDB：可靠的事务处理引擎，不支持全文本搜索
• MEMORY：功能上等同于MyISAM，但由于数据存储在内存中，速度很快
• MyISAM：性能极高的引擎，支持全文本搜索，但不支持事务的处理。

引擎类型可以混用。不同的表可以根据需要的功能使用不同的引擎。

注意：外键不能跨引擎

混用引擎有一个大缺陷：外键不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

二、更新表

1. 更新表的基础

□ 在ALTER TABLE之后给出要更改的表名(表名必须存在，否则报错)
□ 所做更改的列表

• 增加列

ALTER TABLE vendors
ADD vend_phone CHAR(20);

• 删除列

ALTER TABLE vendors
DROP vend_phone;

• 定义外键

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

复杂的表结构更改步骤

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

注意：在使用ALTER TABLE前做一个完整的备份，以防出现错误。

三、删除表

DROP TABLE 表名;

删除表，没有确认，也没有撤销，永久删除该表。

四、重命名表

RENAME TABLE 新表名 TO 旧表名

RENAME TABLE backup_customers TO customers;