mysql 表操作

引用：http://i.mtime.com/1545224/blog/4774844/

MySQL中create table语句的基本语法是：

1. CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]  
2. [table_options] [select_statement]  

TEMPORARY：该关键字表示用MySQL create table新建的表为临时表，此表在当前会话结束后将自动消失。临时表主要被应用于存储过程中，对于目前尚不支持存储过程的MySQL，该关键字一般不用。

IF NOT EXISTS：实际上是在建表前加上一个判断，只有该表目前尚不存在时才执行create table操作。用此选项可以避免出现表已经存在无法再新建的错误。

tbl_name：你所要创建的表的表名。该表名必须符合标识符规则。通常的做法是在表名中仅使用字母、数字及下划线。例如titles、our_sales、my_user1等都应该算是比较规范的表名。

create_definition：这是MySQL create table语句中关键部分所在。在该部分具体定义了表中各列的属性。

create_definition的基本语句是：

1. col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]  
2. [PRIMARY KEY] [reference_definition]  
3. or PRIMARY KEY (index_col_name,...)  
4. or KEY [index_name] (index_col_name,...)  
5. or INDEX [index_name] (index_col_name,...)  
6. or UNIQUE [INDEX] [index_name] (index_col_name,...)  
7. or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)  
8. [reference_definition]  
9. or CHECK (expr) 

col_name：表中列的名字。必须符合标识符规则，而且在表中要唯一。

type：列的数据类型。有的数据类型需要指明长度n，并用括号括起。目前MySQL提供的数据类型详见MySQL进阶_列类型篇。

NOT NULL | NULL：指定该列是否允许为空。如果既不指定NULL也不指定NOT NULL，列被认为指定了NULL。

DEFAULT default_value：为列指定默认值。如果没有为列指定默认值，MySQL自动地分配一个。如果列可以取NULL作为值，缺省值是NULL。如果列被声明为NOT NULL，缺省值取决于列类型：

1、对于没有声明AUTO_INCREMENT属性的数字类型，缺省值是0。对于一个AUTO_INCREMENT列，缺省值是在顺序中的下一个值。

2、对于除TIMESTAMP的日期和时间类型，缺省值是该类型适当的“零”值。对于表中第一个TIMESTAMP列，缺省值是当前的日期和时间。

3、对于除ENUM的字符串类型，缺省是空字符串。对于ENUM，缺省值是第一个枚举值。

AUTO_INCREMENT：设置该列有自增属性，只有整型列才能设置此属性。当你插入NULL值或0到一个AUTO_INCREMENT列中 时，列被设置为value+1，在这里 value是此前表中该列的最大值。AUTO_INCREMENT顺序从1开始。每个表只能有一个AUTO_INCREMENT列，并且它必须被索引。

另转：

通常，企图创建一个已经存在的表时将产生错误。但在两种情况下不会出错。第一种情况，如果给出IF NOT EXISTS 子句，则不创建已经存在的这个表，但也不出错。第二种情况，给出TEMPORARY，则原来名为tbl_name 的表在临时表存在期间对客户机是隐藏的，这样也不会出错。但原来的表对其他客户机仍保持可见。在下一次客户机会话或对临时表明确发布DROP TABLE 后，原来的表再次可见。

createdefinition清单指定希望创建的列和索引。如果通过后跟SELECT 语句的方法创建此表，此清单可省略。table_options 子句允许指定表的各种属性。如果给出后面的select_statement（以任意SELECT语句形式），则利用该SELECT 语句返回的结果创建该表。后面的段落中将对这些子句进行更详细的介绍。

TEMPORARY关键字、IF NOT EXISTS 子句、table_options 子句以及能够利用SELECT语句的结果创建表等都是在MySQL 3.23 中引入的。create_definition 可以是列定义、索引定义、FOREIGN KEY 子句、referencedefinition或CHECK子句。后三者在其数据库系统中是兼容的，但其余的不行。

列定义coldeclaration以列名col_name 和类型col_type 开始，后面可跟几个可选的关键字。列类型可以是附录B 中列出的任意类型。请参阅该附录中的类型专用属性。可跟在列类型之后的可选关键字如下：

■ NULL 或NOT NULL 指出该列是否可以包含NULL 值。如果两者都不指出，缺省为N U L L。
■ DEFAULT defaultvalue 给出该列的缺省值。这种属性不能用于BLOB 或TEXT 类型。

如果无缺省说明，自动分配一个缺省值。对于可取NULL 值的列，缺省值为N U L L。

对于不能为NULL 的列，缺省值如下：

■ 对于数值列，除AUTO_INCREMENT 列外，缺省值为0。而对于AUTO_INCREMENT 列，其缺省值为该列的序列中的下一个数。

■ 对于非TIMESTAMP 的日期和时间类型，缺省值为该类型的“零”值（例如，DATE 类型的“零”值为“0000-00-00”）。对于TIMESTAMP 类型，缺省值为当前日期和时间。

■ 对于非ENUM 的串类型，缺省值为空串。对于ENUM 类型，缺省值为第一个枚举元素。

■ A U TO_INCREMENT 此关键字仅用于整数列类型。A U TO_INCREMENT 列在插入NULL 时是很特殊的，这时插入的值实际为一个大于列中当前最大值的整数。AUTO_INCREMENT 值缺省时从1 开始，第一个值可用AUTO_INCREMENT 表选项确明指定。

这样的列还必须指定为UNIQUE 索引或PRIMARY KEY，而且应该为NOT NULL 列。每个表中至少有一个AUTO_INCREMENT 列。

■ PRIMARY KEY 指定该列为一个PRIMARY KEY。

■ UNIQUE 指定该列为UNIQUE 索引列。自MySQL 3.23 版以来都可指定此属性。

PRIMARY KEY 、UNIQUE、INDEX 和KEY 子句定义索引。PRIMARY KEY 与UNIQUE 指定必须包含唯一值的索引。INDEX 和KEY 意义相同，它们都指定可以包含重复值的索引。索引基于index_columns中所指定的列，其中的每个列都必须是tblname 中的列。
如果有多个列，必须用逗号将它们分隔。对于CHAR 和VARCHAR 列，可对列的前缀进行索引。如果未给出索引名index_name，系统会根据第一个索引列的名称自动选一个。在MySQL 3.23 以前的版本中，索引列必须定义为NOT NULL。自MySQL 3.23 以来，只有PRIMARY KEY 中的列必须定义为NOT NULL。
自MySQL 3.23 以来可使用table options 子句。如果给出此子句，它可以包含下面清单中的一个或多个选项。如果给出多个选项，不应用逗号分隔它们。除非另有说明，否则每个说明符都可用于所有表类型。

■ AUTO_INCREMENT =n 为表生成的第一个AUTO_INCREMENT 值（仅用于MyISAM 表）。

■ AVG_ROW_LENGTH = n 表的近似平均行长。MySQL 用此选项来确定数据文件的最大尺寸，只有具有BLOB 或TEXT 列的表需要此选项，具有这两种列的表可能会增大超过4GB。

■ CHECKSUM = {0 | 1} 如果设置为1，MySQL 将对每个表行保持一个校验和。这样对于表的更新将会有某种轻微的损失，但改善了表的校验过程（仅用于MyISAM 表）。

■ COMMENT = “string” 表的注释。最大长度为60 个字符。

■ D E L AY_KEY_WRITE = {0 | 1} 如果设置为1，表的索引高速缓存只是偶尔刷新，而不是每个插入操作后都刷新（只用于MyISAM 表）。
■ MAX_ROWS = n 计划存储在表中的最多行数。

■ MIN_ROWS = n 计划存储在表中的最少行数。

■ PACK_KEYS = {0 | 1} 如果设置为1，索引块以较平常高的百分比进行压缩。其一般效果是更新性能受损，但检索性能得以改善。如果设置为0，索引块不进行特殊的压缩。在此情形，只 有具有8 个字符或更多字符的CHAR 或VARCHAR 键值进行压缩（只用于MyISAM 和ISAM 表）。

■ PASSWORD =“string” 为加密表的描述文件指定一个口令。一般不起作用；只对具有一定支持协议的顾客可用。

■ TYPE = {ISAM | MYISAM | HEAP} 指定表的存储格式。这些存储格式的特性在第3章的“表存储类型说明符”一节介绍。MySQL 自版本3.23 以来的缺省格式为MyISAM。对MySQL 3.23 以前的版本，CREATE TABLE 总是以ISAM 格式创建表。

如果给出一个select statement子句（如一条SELECT 查询），该表利用查询结果的内容进行创建。惟一索引中重复值的行根据指定的是IGNORE 或REPLACE，或者忽略或者替换现有行。如果两者都未指定，重复值时将出错，其余记录忽略。

下面创建一个具有三列的表。id 列为PRIMARY KEY，其余两列组成一个索引：

create table name
(
id int not null auto_increment,
first_name varchar(30) not null,
last_name  varchar(30) not null,
primary key (id),
index (last_name,first_name)
)

下面创建一个临时表并使其为一个H E A P（内存中）表以加取得更快的速度：

create temporary table tem_tbl
(
id mediumint not null unique,
name char(40)
)
type=heap

下面创建一个表作为另一表的拷贝：

create table prez_copy select * from tbl

下面只用另一表的部分创建一个表：

create table prez_newcopy select id,first_name,last_name from user where id<10

如果指定所要创建的表的创建定义并利用后缀SELECT 语句进行填充，则此定义在表的内容已经插入表中时才使用。例如，可以定义将某个选定的列做成一个PRIMARY KEY：

create table new_tbl (primary key (a)) select id,first_name,last_name from user