1. 使用like,只复制表结构到新表
CREATE TABLE targetTable LIKE sourceTable;
2. 复制表结构及数据到新表
INSERT INTO targetTable SELECT * FROM sourceTable;
3. 可以拷贝一个表中其中的一些字段
CREATE TABLE newadmin AS
(
SELECT username, password FROM admin
)
4. 可以将新建的表的字段改名
CREATE TABLE newadmin AS
(
SELECT id, username AS uname, password AS pass FROM admin
)
5. 可以拷贝一部分数据
CREATE TABLE newadmin AS
(
SELECT * FROM admin WHERE LEFT(username,1) = 's'
)
6. 可以在创建表的同时定义表中的字段信息
CREATE TABLE newadmin
(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
)
AS
(
SELECT * FROM admin
)