4、复制表子查询
(1)复制表
(1)拷贝表结构
CREATE TABLE newadmin LIKE admin;
(2)拷贝表结构和数据(但约束与索引除外)
CREATE TABLE newadmin AS ( SELECT * FROM admin ) ;
(3)拷贝表结构+数据
CREATE TABLE newadmin LIKE admin;
INSERT INTO newadmin SELECT * FROM admin;
(4)跨数据库拷贝表
CREATE TABLE newadmin LIKE shop.admin;
CREATE TABLE newshop.newadmin LIKE shop.admin;
(5)拷贝一个表中其中的一些字段(指定新名),其中一些数据
CREATE TABLE newadmin AS
(
SELECT id, username AS uname, password AS pass FROM admin WHERE id<10
) ;
(6)在创建表的同时定义表中的字段信息。
create table tt
(
eid int primary key auto_increment
)
as
(
select employee_id as eid,first_name,last_name,email from employees
);