首页 >数据库 >mysql教程 >MySQL学习笔记之五 有关数据表操作_MySQL

MySQL学习笔记之五 有关数据表操作_MySQL

WBOY
WBOY原创
2016-06-01 13:12:17740浏览

    MySQL在创建表的时候,创建一个.frm文件保存表和列定义。索引存储在一个有.MYI(MYindex)扩展名的文件并且数据存储在有.MYD(MYData)扩展名的文件中。

    一、用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 STATUS [FROM db_name] [LIKE wild]

    {DESCRIBE | DESC} tbl_name {col_name | wild}

    SHOW TABLES列出在一个给定的数据库中的表。你也可以用mysqlshow db_name命令得到这张表。当然使用mysqlshow时,需要接参数-u 用户名 -p xx;

    SHOW COLUMNS列出在一个给定表中的列。如果列类型不同于你期望的是基于CREATE TABLE语句的那样,注意,MySQL有时改变列类型。

  DESCRIBE语句提供了类似SHOW COLUMNS的信息。DESCRIBE提供关于一张表的列的信息。col_name可以是一个列名字或包含SQL的“%”和“_”通配符的一个字符串。这个语句为了与Oracle 兼容而提供的。

  SHOW TABLE STATUS(在版本3.23引入)运行类似SHOW STATUS,但是提供每个表的更多信息。你也可以使用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实用程序的用法,在得到数据库和表的信息上,使用起来非常方便。当然使用mysqlshow时,需要接参数-u 用户名 -p xx;

    得到已有数据库的列表:

    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 语句创建数据表

    1、CREATE TABLE 语句的基本语法

    CREATE 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,你可以使用这个表来存储来字你站点访问者的信息。你是用REEATE TABLE语句创建的这个表,这个语句有两部分:第一部份指定表的名子;

第二部份是括在括号中的各字段的名称和属性,相互之间用逗号隔开。

    表guestbook有三个字段:visitor,comments 和entrydate。visitor字段存储访问者的名字,comments字段存储访问者对你站点的意见,entrydate字段存储访问者访问你

站点的日期和时间。

    注意每个字段名后面都跟有一个专门的表达式。例如,字段名comments后面跟有表达式TEXT。这个表达式指定了字段的数据类型。数据类型决定了一个字段可以存储什么样

的数据。因为字段comments包含文本信息,其数据类型定义为文本型。

    2、如何指定表的类型

    你也可以在创建表时指定表的类型,如果不指定表的类型,在3.22及以前版本中缺省为ISAM表,在3.23版本中缺省为MyISAM表。你应该尽量使用MyISAM表。指定表的类型经

常用于创建一个HEAP表:

    mysql> CREATE TABLE fast(id int,articles TEXT) TYPE=HEAP;

    3、隐含的列说明的改变

    在某些情况下,MySQL隐含地改变在一个CREATE 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语句即可。

    4、利用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;

  给表更名

    mysql>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;

声明:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn