sql学习笔记（14）------------数据表，创建数据表的sql语句
有关数据表的操作
数据库基本操作
用 MySQL，目前(版本 3.23)你可以在三种基本数据库表格式间选择。当你创建一张表时，你可以告诉 MySQL它应该对于表使用哪个表类型。MySQL将总是创建一个.frm 文件保存表和列定义。视表类型而定，索引和数据将在其他文件中存储。
你能用 ALTER TABLE语句在不同类型的表之间变换。
MyISAM
MyISAM是缺省表格类型，它是基于ISAM代码并且有很多有用的扩展。索引存储在一个有.MYI（MYindex）扩展名的文件并且数据存储在有.MYD（MYData）扩展名的文件中。你能用 myisamchk 实用程序检查/修复 MyISAM表。
ISAM
你也可以使用放弃的 ISAM。这将在不久消失，因为 MyISAM是同一个东西的更好实现。ISAM使用一个 B-tree 索引，这个索引存储在一个有.ISM扩展名的文件中并且数据存储在有.ISD 扩展名的文件中，你可用 isamchk 实用程序检查/修复 ISAM表。ISAM表不是
跨 OS/平台二进制可移植的。
HEAP
HEAP 表格使用一个杂凑（hashed）索引并且存储在内存中。这使他们更快，但是如果 MySQL崩溃，你将失去所有存储的数据。HEAP 作为临时表很可用！
用 SHOW/ DESCRIBE 语句显示数据表的信息
句法：
SHOW TABLES [FROM db_name] [LIKE wild]
or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW TABLE STA TUS [FROM db_name] [LIKE wild]
{DESCRIBE | DESC} tbl_name {col_name | wild}
你能使用 db_name.tbl_name 作为 tbl_name FROM db_name 句法的另一种选择。
SHOW TABLES列出在一个给定的数据库中的表。 你也可以用mysqlshow db_name命令得到这张表。
注意：如果一个用户没有一个表的任何权限，表将不在 SHOW TABLES 或 mysqlshow db_name 中的输出中显示。
SHOW COLUMNS 列出在一个给定表中的列。 如果列类型不同于你期望的是基于CREA TE TABLE语句的那样，注意，MySQL有时改变列类型。
DESCRIBE 语句提供了类似 SHOW COLUMNS 的信息。DESCRIBE 提供关于一张表的列的信息。col_name 可以是一个列名字或包含 SQL 的“%”和“_”通配符的一个字符串。这个语句为了与 Oracle 兼容而提供的。SHOW TABLE STA TUS（在版本 3.23 引入)运行类似 SHOW STA TUS，但是提供每个表的更多信息。你也可以使用 mysqlshow --status db_name 命令得到这张表。
SHOW FIELDS是SHOW COLUMNS一个同义词， SHOW KEYS是SHOW INDEX一个同义词。
你也可以用 mysqlshow db_name tbl_name 或 mysqlshow -k db_name tbl_name 列出一张表的列或索引。
SHOW INDEX 以非常相似于 ODBC的 SQLStatistics 调用的格式返回索引信息。
使用 mysqlshow 工具得到信息
下面简单介绍一下 mysqlshow 实用程序的用法，在得到数据库和表的信息上，使用起来非常方便。
得到已有数据库的列表：
shell> mysqlshow
列出某数据库 db_name 中已有的表：
shell> mysqlshow db_name
列出某数据库表 db_name.tbl_name 的结构信息：
shell>mysqlshow db_name tbl_name
列出一张表的索引：
shell> mysqlshow –k db_name tbl_name
用 CREATE TABLE 语句创建数据表
用 CREA TE TABLE 语句创建表。此语句的完整语法是相当复杂的，因为存在那么多
的可选子句， 但在实际中此语句的应用相当简单。
有意思的是，大多数复杂东西都是一些子句，这些子句 MySQL 在分析后扔掉。
1 、 CREATE TABLE 语句的基本语法
CREA TE TABLE tbl_name(create_definition,...) [TYPE =table_type]
create_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT][PRIMARY KEY]
在 MySQL3.22 或以后版本中， 表名可以被指定为 db_name.tbl_name， 不管有没有当前的数据库都可以。
例如，创建一个访问者留言表：
shell> mysql –u root –p
mysql> create database mytest;
mysql> CREATE TABLE guestbook
-> (
-> visitor VARCHAR(40),
-> comments TEXT,
-> entrydate DATETIME
->);
如果一切正常，祝贺你，你已经建立了你的第一个表！
你所创建的表名为 guestbook， 你可以使用这个表来存储来字你站点访问者的信息。 你是用 REEA TE TABLE 语句创建的这个表，这个语句有两部分：第一部份指定表的名子；第二部份是括在括号中的各字段的名称和属性，相互之间用逗号隔开。表 guestbook 有三个字段：visitor,comments 和 entrydate。visitor 字段存储访问者的名
字， comments 字段存储访问者对你站点的意见，entrydate 字段存储访问者访问你站点的日
期和时间。
注意每个字段名后面都跟有一个专门的表达式。例如，字段名 comments 后面跟有表达式 TEXT。这个表达式指定了字段的数据类型。数据类型决定了一个字段可以存储什么样的数据。因为字段comments 包含文本信息，其数据类型定义为文本型。
2 、如何指定表的类型
你也可以在创建表时指定表的类型，如果不指定表的类型，在 3.22 及以前版本中缺省为 ISAM表，在 3.23 版本中缺省为 MyISAM表。你应该尽量使用 MyISAM表。指定表的类型经常用于创建一个 HEAP 表：
mysql> CREA TE TABLE fast(id int,articles TEXT) TYPE=HEAP;
3 、隐含的列说明的改变
在某些情况下，MySQL隐含地改变在一个 CREA TE TABLE语句给出的一个列说明。
（这也可能在 ALTER TABLE。 ）
长度小于 4 的 VARCHAR 被改变为 CHAR。
如果在一个表中的任何列有可变长度，结果是整个行是变长的。因此, 如果一张表包含任何变长的列(VARCHAR、TEXT 或 BLOB)，所有大于 3 个字符的 CHAR列被改变为 VARCHAR列。 这在任何方面都不影响你如何使用列； 在 MySQL中，VARCHAR只是存储字符的一个不同方法。 MySQL实施这种改变， 是因为它节省空间并且使表操作更快捷。
TIMESTAMP 的显示尺寸必须是偶数且在 2 ～ 14 的范围内。如果你指定 0 显示尺寸或比 14 大，尺寸被强制为 14。从 1～13 范围内的奇数值尺寸被强制为下一个更大的偶数。
你不能在一个TIMESTAMP 列里面存储一个文字 NULL；将它设为 NULL将设置为当前的日期和时间。因为 TIMESTAMP 列表现就是这样，NULL 和 NOT NULL属性不以一般的方式运用并且如果你指定他们，将被忽略。DESCRIBE tbl_name总是报告该 TIMESTAMP 列可能赋予了 NULL值。
如果你想要知道 MySQL 是否使用了除你指定的以外的一种列类型，在创建或改变你的表之后，发出一个DESCRIBE tbl_name 语句即可。
利用 SELECT 的结果创建表
关系数据库的一个重要概念是，任何数据都表示为行和列组成的表，而每条 SELECT
语句的结果也都是一个行和列组成的表。在许多情况下，来自 SELECT 的“表”仅是一个随着您的工作在显示屏上滚动的行和列的图像。在 MySQL 3.23 以前，如果想将SELECT 的结果保存在一个表中以便以后的查询使用，必须进行特殊的安排：
1) 运行 DESCRIBE 或 SHOW COLUMNS 查询以确定想从中获取信息的表中的列类型。
2) 创建一个表，明确地指定刚才查看到的列的名称和类型。
3) 在创建了该表后，发布一条 INSERT ... SELECT 查询，检索出结果并将它们插入所创建的表中。
在 MySQL 3.23 中，全都作了改动。CREA TE TABLE ... SELECT 语句消除了这些浪费时间的东西，使得能利用 SELECT 查询的结果直接得出一个新表。只需一步就可以完成知道或指定所检索的列的数据类型。这使得很容易创建一个完全用所喜欢的数据填充的表，并且为进一步查询作了准备。
如果你在 CREA TE 语句后指定一个 SELECT，MySQL 将为在 SELECT 中所有的单元创键新字段。例如：
mysql> CREA TE 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> CREA TE TABLE test SELECT * from test2;
mysql> CREA TE TABLE test SELECT * from test2 where 0;
如果希望利用 LOAD DA TA 将一个数据文件装入原来的文件中，而不敢肯定是否具有指定的正确数据格式时，创建空拷贝很有用。您并不希望在第一次未得到正确的选项时以原来表中畸形的记录而告终。利用原表的空拷贝允许对特定的列和行分隔符用 LOAD DATA 的选项进行试验，直到对输入数据的解释满意时为止。在满意之后，就可以将数据
装入原表了。
可结合使用 CREA TE TEMPORARY TABLE 与 SELECT 来创建一个临时表作为它自身的拷贝，如：
这允许修改 my_tbl 的内容而不影响原来的内容。在希望试验对某些修改表内容的查询，而又不想更改原表内容时，这样做很有用。为了使用利用原表名的预先编写的脚本，不 需要 为引用 不同 的表而 编辑这 些脚 本；只 需在 脚本的 起始 处增加 CREA TE EMPORARY 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 实用程序