MySQL中数据表操作详解（3）

利用 select 的结果创建表

关系数据库的一个重要概念是，任何数据都表示为行和列组成的表，而每条 select 语句的结果也都是一个行和列组成的表。在许多情况下，来自 select 的“表”仅是一个随着您的工作在显示屏上滚动的行和列的图像。在 MySQL 3.23 以前，如果想将 select 的结果保存在一个表中以便以后的查询使用，必须进行特殊的安排：

1) 运行 DESCRIBE 或 SHOW COLUMNS 查询以确定想从中获取信息的表中的列类型。

2) 创建一个表，明确地指定刚才查看到的列的名称和类型。

3) 在创建了该表后，发布一条 INSERT ... select 查询，检索出结果并将它们插入所创建的表中。

在 MySQL 3.23 中，全都作了改动。CREATE TABLE ... select 语句消除了这些浪费时间的东西，使得能利用 select 查询的结果直接得出一个新表。只需一步就可以完成任务，不必知道或指定所检索的列的数据类型。这使得很容易创建一个完全用所喜欢的数据填充的表，并且为进一步查询作了准备。

如果你在CREATE语句后指定一个SELECT，MySQL将为在SELECT中所有的单元创键新字段。例如：

mysql> CREATE TABLE test

-> (a int not null auto_increment,primary key (a), key(b))

-> select b,c from test2;

这将创建一个有3个列(a，b，c)的表，其中b，c列的数据来自表test2。注意如果在拷贝数据进表时发生任何错误，表将自动被删除。

可以通过选择一个表的全部内容(无 WHERE 子句)来拷贝一个表，或利用一个总是失败的 WHERE 子句来创建一个空表，如：

mysql> CREATE TABLE test select * from test2;

mysql> CREATE TABLE test select * from test2 where 0;

如果希望利用 LOAD DATA 将一个数据文件装入原来的文件中，而不敢肯定是否具有指定的正确数据格式时，创建空拷贝很有用。您并不希望在第一次未得到正确的选项时以原来表中畸形的记录而告终。利用原表的空拷贝允许对特定的列和行分隔符用 LOAD DATA 的选项进行试验，直到对输入数据的解释满意时为止。在满意之后，就可以将数据装入原表了。

可结合使用 CREATE TEMPORARY TABLE 与 select 来创建一个临时表作为它自身的拷贝，如：

这允许修改 my_tbl 的内容而不影响原来的内容。在希望试验对某些修改表内容的查询，而又不想更改原表内容时，这样做很有用。为了使用利用原表名的预先编写的脚本，不需要为引用不同的表而编辑这些脚本;只需在脚本的起始处增加 CREATE TEMPORARY TABLE 语句即可。相应的脚本将创建一个临时拷贝，并对此拷贝进行操作，当脚本结束时服务器会自动删除这个拷贝。

要创建一个作为自身的空拷贝的表，可以与 CREATE TEMPORARY ... select 一起使用 WHERE 0 子句，例如：

但创建空表时有几点要注意。在创建一个通过选择数据填充的表时，其列名来自所选择的列名。如果某个列作为表达式的结果计算，则该列的“名称”为表达式的文本。表达式不是合法的列名，可在 mysql 中运行下列查询了解这一点：

为了正常工作，可为该列提供一个合法的别称：

如果选择了来自不同表的具有相同名称的列，将会出现一定的困难。假定表 t1 和 t2 两者都具有列 c，而您希望创建一个来自两个表中行的所有组合的表。那么可以提供别名指定新表中惟一性的列名，如：通过选择数据进行填充来创建一个表并会自动拷贝原表的索引。

用ALTER TABLE语句修改表的结构

有时你可能需要改变一下现有表的结构，那么Alter Table语句将是你的合适选择。

增加列

alter table tbl_name add col_name type

例如,给表增加一列weight

mysql>alter table pet add weight int;

删除列

alter table tbl_name drop col_name

例如，删除列weight：

mysql>alter table pet drop weight;

改变列

alter table tbl_name modify col_name type

例如，改变weight的类型：

mysql> alter table pet modify weight samllint;

另一种方法是：

alter table tbl_name change old_col_name col_name type

例如：

mysql> alter table pet change weight weight samllint;

给列更名

mysql>alter table pet change weight wei;

给表更名

alter table tbl_name rename new_tbl

例如，把pet表更名为animal

mysql>alter table pet rename animal;

改变表的类型

另外，可以为列增加或删除索引等属性，不再详述，请参阅附录。

用DROP TABLE 语句删除数据表

DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]

DROP TABLE删除一个或多个数据库表。所有表中的数据和表定义均被删除，故小心使用这个命令!

在MySQL 3.22或以后版本，你可以使用关键词IF EXISTS类避免不存在表的一个错误发生。

例如：

mysql>USE mytest;

mysql>DROP TABLE guestbook;

或者，也可以同时指定数据库和表：

mysql>DROP TABLE mytest. guestbook;

总结

本节讲述了有关表的大部分操作，现在将所述内容总结如下：

MySQL的表的三种类型

如何创建表、删除表

如何改变表的结构、名字

如何使用mysqlshow实用程序