MySQL中管理表和索引的相关语句 - MySQL教程

MySQL中创建表是通过create table语句实现的，语法这里就不介绍了，非常复杂，可以去官网上查询，所幸的是， 我们并不需要记住所有的选项，因为大部分都采用默认即可，下面介绍几个比较常用的选项。

1）指定存储引擎

默认的存储引擎由default-storage-engine指定，如果没有指定，则为MyISAM，如果建表时，你不想使用默认的存储 引擎，可以通过如下语句实现：

create table table_name ( ... ) engine=engine_name;

2）只有当表不存在时才创建

create table if not exist table_name ... ;

上述语句表示：如果表已存在，则跳过；否则创建新表。

3）创建临时表

MySQL的临时表有如下特性：

- 临时表只在创建该表的会话里是可见的，对其它会话是隐藏的，会话断开后，临时表自动被删除。

- 当临时表的表名和普通表一样时，当前会话将会优先访问临时表。

临时表的语法如下：

create temporary table temp_table_name ... ;

4）从其它表中创建新表

MySQL提供两种方式从其它表中创建新表：

- create table ... like：创建一个新表为原表的空白副本，也就是说，新表是一个空表，但它拥有原表所有的属 性、索引、约束等（这个语法很有用，Oracle就没有类似的功能），其用法如下：

create table new_table_name like old_table_name;  
insert into new_table_name select * from old_table_name;

- create table ... select:  不会复制所有的数据列属性（保留下来的属性有：字符集、NOLL or NOT NULL ，默认值，注释），不带索引，不包含约束。

2. 删除表

相比于创建表，删除表就简单多了。

1）删除普通表

drop table table_name;

2）删除临时表

drop temporary table table_name;

3）表存在才删除

drop table if exists table_name;

3. 创建索引

你可以使用和Oracle类似的create index语句重建索引，也可以使用MySQL独有的alter table语句添加索引，在 MySQL里，建议用alter table语句，因为它更灵活多能，如：

alter table tbl_name add index index_name (index_cols);  
alter table tbl_name add unique index_name(index_cols);  
alter table tbl_name add primary key(index_cols);  
alter table tbl_name add full text index_name(index_cols);  
alter table tbl_name add spatial index_name(index_cols);

其中，primary key和spatial索引要求索引列必须为NOT NULL，index_name可省略，一般不建议省略，如果省略，系 统会自动取个名字。

该语句支持在一个alter table语句添加多个索引，这样更灵活。

在创建表时，也可以同时创建索引，如：

create table tbl_name  
(  
  ...  
  index index_name (index_cols),  
  primary key (index_cols),  
  unique (index_cols),  
  ...  
);

4. 删除索引

和创建索引类似，删除索引可以用drop index或alter table语句完成，如：

drop index index_name on tbl_name;  
drop index `primary` on tbl_name;  
       
alter table tbl_name drop index index_name;  
alter table tbl_name drop primary index;

5. 改变表结构

1）改变列的数据类型

如果你想改变列的数据类型，可以使用change或modify字句，如下所示：

alter table tbl_name modify col_name mediumint unsigned;

alter table tbl_name change old_col_name new_col_name mediumint unsigned;

从上面可以知道，change功能更强大，它不但能够改变列的数据类型，还可以重命名列，但是它比较麻烦的一点是即 时不想重命名，也要把列名写两遍。

2）改变列的默认值

MySQL可以通过alter column语句设置或移除列的默认值，该语句直接修改.frm文件，因此速度非常快。

ALTER TABLE MyTable ALTER COLUMN foo SET DEFAULT 'bar';

ALTER TABLE MyTable ALTER COLUMN foo DROP DEFAULT;

3）改变表的存储引擎

alter table tbl_name engine=engine_name;

4）重命名表

重命名表可以使用alter table ... rename to 或rename table，如：

alter table tbl_name rename to new_tbl_name;

rename table tbl_name to new_tbl_name;

alter table ... rename每次只能重命名一张表，而rename table可以重命名多张表，如：

rename table t1 to new_t1, t2 to t1;

如果在表名前加上前缀，可以把一个表从一个数据库迁移到另一个数据库。

5）添加新列

ALTER TABLE tbl_name ADD column_name VARCHAR(40) not null default ... COMMENT '....';

6）添加约束

ALTER TABLE tbl_name ADD UNIQUE(col_names);

ALTER TABLE tbl_name ADD PRIMARY KEY(col_names);

7）添加自增长

Alter table tbl_name change id id int(10) not null auto_increment;

查看本栏目更多精彩内容：http://www.bianceng.cn/database/MySQL/

（编辑：佛山站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!