语法
CREATE {TABLE | CLASS} <table_name>
[( <column_definition> [,<table_constraint>]... )]
[REPLACE] AS <select_statement>
如下
CREATE TABLE a_tbl(
id INT NOT NULL DEFAULT 0 PRIMARY KEY,
phone VARCHAR(10));
INSERT INTO a_tbl VALUES(1,'111-1111'), (2,'222-2222'), (3, '333-3333');
--没有列定义
CREATE TABLE new_tbl1 AS SELECT * FROM a_tbl;
SELECT * FROM new_tbl1;
id phone
===================================
1 '111-1111'
2 '222-2222'
3 '333-3333'
--复制表的内容
CREATE TABLE new_tbl2
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, phone VARCHAR) AS SELECT * FROM a_tbl;
SELECT * FROM new_tbl2;
id phone
===================================
1 '111-1111'
2 '222-2222'
3 '333-3333'
--some of column values are replicated from a_tbl and the rest is NULL
CREATE TABLE new_tbl3
(id INT, name VARCHAR) AS SELECT id, phone FROM a_tbl;
SELECT * FROM new_tbl3
name id phone
=========================================================
NULL 1 '111-1111'
NULL 2 '222-2222'
NULL 3 '333-3333'
--column alias in the select statement should be used in the column definition
CREATE TABLE new_tbl4
(id1 int, id2 int)AS SELECT t1.id id1, t2.id id2 FROM new_tbl1 t1, new_tbl2 t2;
SELECT * FROM new_tbl4;
id1 id2
==========================
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
--REPLACE is used on the UNIQUE column
CREATE TABLE new_tbl5(id1 int UNIQUE) REPLACE AS SELECT * FROM new_tbl4;
SELECT * FROM new_tbl5;
id1 id2
==========================
1 3
2 3
3 3