Mysql之创建和操纵表

1、建表

表创建基础
为利用CREATE TABLE创建表，必须给出下列信息：
　　 新表的名字，在关键字CREATE TABLE之后给出；
　　 表列的名字和定义，用逗号分隔。

CREATE TABLE语句也可能会包括其他关键字或选项，但至少要包括表的名字和列的细节。

例子：

CREATE TABLE students
(
   stu_id INT NOT NULL AUTO_INCREMENT,
   stu_name CHAR(50) NOT NULL,
   stu_address CHAR(50) NULL,
   PRIMARY KEY(stu_id)
 )ENGINE=INNODB

 表名紧跟在CREATE TABLE关键字后面。实际的表定义（所有列）括在圆括号之中。各列之间用逗号分隔。每列的定义以列名（它在表中必须是唯一的）开始，后跟列的数据类型，表的主键可以在创建表时用PRIMARY KEY关键字指定。（过MySQL语句中忽略空格。语句可以在一个长行上输入，也可以分成许多行。）

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

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

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

 2、主键：

表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。主键可以在创建表时定义（如这里所示），或者在创建表之后定义。

主键不可为NULL

　　多个列组成的主键，应该以逗号分隔的列表给出各列名：

CREATE TABLE orderitem
(
   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

 3、AUTO_INCREMENT

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

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

　　3.1、覆盖AUTO_INCREMENT

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

　　如何在使用AUTO_INCREMENT列时获得这个值呢？可使用last_insert_id()函数获得这个值。如：select last_insert_id()

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

4、指定默认值

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

 默认值不允许函数

一般来说，设置默认值比设置NULL更加的好

5、引擎类型

MySQL有一个具体管理和处理数据的内部引擎。在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。

如果省略ENGINE=语句，则使用默认引擎（很可能是MyISAM），多数SQL语句都会默认使用它。

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

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

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

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

注意：同一数据库之中的表之间的引擎可以混用，但是不同的引擎类型的外键不可使用

 6、更新表

使用ALTER TABLE更改表结构，必须给出下面的信息：

　　 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）；

　　 所做更改的列表。

 6.1添加一列

ALTER TABLE orderitemsss
ADD vend_phone CHAR(20);

 

删除一列

ALTER TABLE orderitemsss
DROP COLUMN vend_phone;

 

定义外键

ALTER TABLE orderitemsss
ADD CONSTRAINT fk_orderitemsss_orders
FOREIGN KEY(order_num) REFERENCES orders (order_num);

 

注意：此为外键约束名：fk_orderitemsss_orders  （叫什么无所谓）

　　　KEY(order_num)：代表的是表中的外键

　　　REFERENCES orders (order_num)：外键引用的表和表的行

 

注意：对于复杂表结构的更改：

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：
 用新的列布局创建一个新表；
 使用INSERT SELECT语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；

 检验包含所需数据的新表；
 重命名旧表（如果确定，可以删除它）；
 用旧表原来的名字重命名新表；
 根据需要，重新创建触发器、存储过程、索引和外键。

 7、删除表

DROP TABLE t888;

 8、重命名表

RENAME TABLE t1 TO t888999;