[size=medium][align=center][b]Table Management[/b][/align][/size]
[size=small][b]1.Cloning a Table[/b][/size]
create table [i]new_table[/i] like [i]original_table[/i];
insert into [i]new_table[/i] select * from [i]original_table[/i];
[size=small][b]2.Saving a Query Result in a Table[/b][/size]
insert into dst_tb1(i,s) select val,name from src_tb1;
将src_tb1表中val和name两列的所有行分别复制到dst_tb1的i和s两列中。
insert into dst_tb1 select * from src_tb1 where val > 100 and name like 'A%;
insert into dst_tb1(i,s) select count(*),name from src_tb1 group by name;
create table dst_tb1 select * from src_tb1 where 0;
创建一个空的目的表
create table dst_tb1 select b,d from src_tb1;
假设src_tb1中有列a、b、c、d,你可以只复制b和d两列到目的表
create table dst_tb1 select c、a、b from src_tb1;
如果源表中列abc按顺序出现,而用户希望复制完成之后各列在目的表中出现顺序为cab
create table dst_tb1( id int not null auto_increment, primary key(id)) select a,b,c from src_tb1;
目的表dst_tb1中4列出现顺序为id,a,b,c。其中abc三列根据源表查询结果赋值,而id列从1开始连续自加,为行序号。
create table dst_tb1 select inv_no,sum(unit_cost*quantity) as total_cost from src_tb1 group by inv_no;
使用total_cost重命名sum(unit_cost*quantity)列
注:使用create table...select 来创建目的表,在带来方便的同时也有一些限制。这主要是由于使用查询结果来创建表,对新表的描述和定义能力不如直接使用create table语句那么强大。对于新创建的目的表mysql不能确定其某些列属性,例如列索引和默认值。可以使用如下技巧,使得目的表中包含这些信息:
(1).如果希望目的表和源表一样,可以使用create table...like
(2).如果希望在目的表中使用索引,可以在sql语句中指明。例如,在scr_tb1中id列是主键,state和city两列作为索引,可以赋予dst_tb1同样的属性。
create table dst_tb1(PRIMARY KEY(id),index(state,city)) select * from src_tb1;
(3).有的属性不能克隆到目的表,例如auto_increment属性和列的默认值。对于这些属性,可以在目的表创建并完成数据复制之后,使用alter table来设置目的表相应的属性。例如,src_tb1的id列不但具有primary key属性,同时具有auto_increment属性,可以这样克隆:
create table dst_tb1(PRIMARY KEY(id)) select * from src_tb1;
alter table dst_tb1 modify id int unsigned not null auto_increment;
[size=small][b]3.Creating Temporary Tables[/b][/size]
create temporary table tb1_name;
普通的建表语句
create temporary table new_table like original_table;
克隆表
create temporary table tb1_name select...;
根据查询结果建表
注:(1).只有在最后一次使用之后,数据库才会自动删除临时表。如果一个临时表在一个数据库连接内多次重复使用,那么每一次重复使用之前应该先使用drop table 显式地删除该临时表(在一个数据库连接中,如果重复两次使用同一个表名创建临时表,而未删除第一次创建的临时表,将导致数据库错误)
(2).应用程序中使用一个与普通表同名的临时表时,应用程序只会对该临时表就行修改。但是如果数据库连接错误断开,而所使用的应用程序自动创建了数据库连接,那么以后对数据库的修改,都将直接作用于普通表,而不是临时表(已经被自动删除)。
[size=small][b]4.Checking or Changing a Table's Storage Engine[/b][/size]
select engine from information_schema.tables where table_schema='test' and table_name='customer';
检测information_schema,或使用show table status,或使用show create table 语句,来确定一张表当前使用的引擎。
show table status like 'customer'\G
show create table customer\G
alter table customer engine=innodb;
使用alter table以及一个engine子句,来改变一张表所用的引擎。
注意,改变一个很大的表所用的存储引擎可能会耗费很长时间,并花费大量CPU以及I/O资源。
[size=small][b]1.Cloning a Table[/b][/size]
create table [i]new_table[/i] like [i]original_table[/i];
insert into [i]new_table[/i] select * from [i]original_table[/i];
[size=small][b]2.Saving a Query Result in a Table[/b][/size]
insert into dst_tb1(i,s) select val,name from src_tb1;
将src_tb1表中val和name两列的所有行分别复制到dst_tb1的i和s两列中。
insert into dst_tb1 select * from src_tb1 where val > 100 and name like 'A%;
insert into dst_tb1(i,s) select count(*),name from src_tb1 group by name;
create table dst_tb1 select * from src_tb1 where 0;
创建一个空的目的表
create table dst_tb1 select b,d from src_tb1;
假设src_tb1中有列a、b、c、d,你可以只复制b和d两列到目的表
create table dst_tb1 select c、a、b from src_tb1;
如果源表中列abc按顺序出现,而用户希望复制完成之后各列在目的表中出现顺序为cab
create table dst_tb1( id int not null auto_increment, primary key(id)) select a,b,c from src_tb1;
目的表dst_tb1中4列出现顺序为id,a,b,c。其中abc三列根据源表查询结果赋值,而id列从1开始连续自加,为行序号。
create table dst_tb1 select inv_no,sum(unit_cost*quantity) as total_cost from src_tb1 group by inv_no;
使用total_cost重命名sum(unit_cost*quantity)列
注:使用create table...select 来创建目的表,在带来方便的同时也有一些限制。这主要是由于使用查询结果来创建表,对新表的描述和定义能力不如直接使用create table语句那么强大。对于新创建的目的表mysql不能确定其某些列属性,例如列索引和默认值。可以使用如下技巧,使得目的表中包含这些信息:
(1).如果希望目的表和源表一样,可以使用create table...like
(2).如果希望在目的表中使用索引,可以在sql语句中指明。例如,在scr_tb1中id列是主键,state和city两列作为索引,可以赋予dst_tb1同样的属性。
create table dst_tb1(PRIMARY KEY(id),index(state,city)) select * from src_tb1;
(3).有的属性不能克隆到目的表,例如auto_increment属性和列的默认值。对于这些属性,可以在目的表创建并完成数据复制之后,使用alter table来设置目的表相应的属性。例如,src_tb1的id列不但具有primary key属性,同时具有auto_increment属性,可以这样克隆:
create table dst_tb1(PRIMARY KEY(id)) select * from src_tb1;
alter table dst_tb1 modify id int unsigned not null auto_increment;
[size=small][b]3.Creating Temporary Tables[/b][/size]
create temporary table tb1_name;
普通的建表语句
create temporary table new_table like original_table;
克隆表
create temporary table tb1_name select...;
根据查询结果建表
注:(1).只有在最后一次使用之后,数据库才会自动删除临时表。如果一个临时表在一个数据库连接内多次重复使用,那么每一次重复使用之前应该先使用drop table 显式地删除该临时表(在一个数据库连接中,如果重复两次使用同一个表名创建临时表,而未删除第一次创建的临时表,将导致数据库错误)
(2).应用程序中使用一个与普通表同名的临时表时,应用程序只会对该临时表就行修改。但是如果数据库连接错误断开,而所使用的应用程序自动创建了数据库连接,那么以后对数据库的修改,都将直接作用于普通表,而不是临时表(已经被自动删除)。
[size=small][b]4.Checking or Changing a Table's Storage Engine[/b][/size]
select engine from information_schema.tables where table_schema='test' and table_name='customer';
检测information_schema,或使用show table status,或使用show create table 语句,来确定一张表当前使用的引擎。
show table status like 'customer'\G
show create table customer\G
alter table customer engine=innodb;
使用alter table以及一个engine子句,来改变一张表所用的引擎。
注意,改变一个很大的表所用的存储引擎可能会耗费很长时间,并花费大量CPU以及I/O资源。