sql必知必会学习记录（十六）- 创建和操纵表

sql必知必会学习记录（十六）- 创建和操纵表

1. 创建表

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

新表的名字，在关键字CREATE TABLE之后给出；
CREATE TABLE(
col_name datatype  ...，
col_name1 datatype ...
)

示例：

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;

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

主键

主键值必须唯一；如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名。主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。

PRIMARY KEY(order_num,order_item)

使用AUTO_INCREMENT

cust_id int NOT NULL AUTO_INCREMENT,

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

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

确定AUTO_INCREMENT值 让MySQL生成（通过自动增量）主键的一个缺点是你不知道这些值都是谁。
考虑这个场景：你正在增加一个新订单。这要求在orders表中创建一行，然后在orderitms表中对订购的每项物品创建一行。order_num在orderitems表中与订单细节一起存储。这就是为什么orders表和orderitems表为相互关联的表的原因。这显然要求你在插入orders行之后，插入orderitems行之前知道生成的order_num。
那么，如何在使用AUTOINCREMENT列时获得这个值呢？可使用last insert_id（）函数获得这个值，如下所示：
SELECT last_insert_id（）
此语句返回最后一个AUTOINCREMENT值，然后可以将它用于后续的MySOL语句。

默认值

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

quantity int NOT NULL DEFAULT 1,

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

数据库引擎

MySQL有一个具体管理和处理数据的内部引擎。在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。多数时候，此引擎都隐藏在DBMS内，不需要过多关注它。MySQL与其他DBMS不一样，它具有多种引擎。它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令。

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

• InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；

• MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；

• MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

引擎类型可以混用。比如一个表使用MyISAM外，其他表都使用InnoDB。但是要注意外键不能跨引擎。

2. 更新表

理想状态下，当表中存储数据以后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。ALTER TABLE的一种常见用途是定义外键。

ALTER TABLE vendors add vend_phone char(20)
这条语句给vendors表增加一个名为vend_phone的列，必须明确其数据类型。
ALTER TABLE vendors DROP COLUMN vend_phone;
删除刚刚添加的列
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders  FOREIGN KEY (order_num) REFERENCES orders(order_num)

3. 删除表

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

4. 重命名表

RENAME TABLE customers2 TO customers; 重命名单个表

RENAME TABLE backup_customers TO customers, backup_vendors TO vendors, backup_products TO products; 重命名多个表