CREATE TABLE LIKE

• CREATE TABLE ... LIKE可以用來復制表結構，源表上的索引和約束也會復制。
• CREATE TABLE ... LIKE不能復制表數據。
• CREATE TABLE ... LIKE只能復制基表，不能復制視圖。
• CREATE TABLE ... LIKE可以復制CHECK約束，但是復制后的約束名稱是自動生成的。
• CREATE TABLE ... LIKE不能復制外鍵約束，也不會保留DATA DIRECTORY和INDEX DIRECTORY表創建參數。
• CREATE TABLE ... LIKE不會復制表的臨時屬性。要復制臨時表請使用CREATE TEMPORARY TABLE LIKE。
• 當源表被LOCK TABLES鎖定時，CREATE TABLE LIKE語句無法成功執行。

用法示例：

CREATE TABLE t1 LIKE t2;
CREATE TEMPORARY TABLE t1 LIKE t2;

CREATE TABLE SELECT

• CREATE TABLE ... SELECT可以用來復制表結構，但不會復制索引（主鍵也不會）。
• CREATE TABLE ... SELECT可以復制表數據（可以通過WHERE過濾）。
• CREATE TABLE ... SELECT可以復制基表，也可以復制視圖。
• CREATE TABLE ... SELECT不能復制外鍵約束。
• CREATE TABLE ... SELECT支持在建表時定義字段，從源表復制過來的不同名字段會作為新增字段，同名的字段則會被覆蓋。
• CREATE TABLE ... SELECT支持在建表時為字段定義約束和索引。

用法示例：

CREATE TABLE t1 SELECT * FROM t2 where 1=2;             --僅復制表結構
CREATE TABLE t1 SELECT * FROM t2;                       --復制全量數據
CREATE TABLE t1 SELECT name,age FROM t2 where age>30;   --復制部分數據--復制另外兩張表關聯查詢的結果集
CREATE TABLE t1SELECT artist.name, COUNT(work.artist_id) AS number_of_worksFROM artist LEFT JOIN work ON artist.id = work.artist_idGROUP BY artist.id;--復制的列作為新增字段
CREATE TABLE t1 (a int) SELECT name,age FROM t2;  --復制的不同列作為新增字段，同名的列被覆蓋      
CREATE TABLE t1 (a int, name varchar(30)) SELECT name,age FROM t2; --為復制過來的字段定義索引和約束
CREATE TABLE t1 (name varchar(30) not null) SELECT name,age FROM t2; 
CREATE TABLE t1 (unique(employee_id)) SELECT employee_id,name FROM t3; 

實驗：

SQL> create table t2 (name varchar(30), age int not null);
SQL> create table t3 (employ_id int, name varchar(30), age int);
SQL> insert into t2 values ('Joe',35),('Rachel',31),('Monica',33);
SQL> insert into t3 values (11,'MJoe',35),(12,'MRachel',31),(13,'MMonica',33);
SQL> commit;SQL> select * from t2;
+--------+-----+
| name   | age |
+--------+-----+
| Joe    |  35 |
| Rachel |  31 |
| Monica |  33 |
+--------+-----+
3 rows in set (0.00 sec)SQL> select * from t3;
+-----------+---------+------+
| employ_id | name    | age  |
+-----------+---------+------+
|        11 | MJoe    |   35 |
|        12 | MRachel |   31 |
|        13 | MMonica |   33 |
+-----------+---------+------+
3 rows in set (0.00 sec)--僅復制表結構
SQL> CREATE TABLE t1 SELECT * FROM t2 where 1=2; 
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0SQL> select * from t1;
Empty set (0.00 sec)SQL> desc t1;
+-----------+-----------------+------+-----+---------+--------------------------+
| Field     | Type            | Null | Key | Default | Extra                    |
+-----------+-----------------+------+-----+---------+--------------------------+
| my_row_id | bigint unsigned | NO   | PRI | NULL    | auto_increment INVISIBLE |
| name      | varchar(30)     | YES  |     | NULL    |                          |
| age       | int             | NO   |     | NULL    |                          |
+-----------+-----------------+------+-----+---------+--------------------------+
3 rows in set (0.00 sec)--復制的列作為新增字段
SQL> CREATE TABLE t1 (a int) SELECT name,age FROM t2; 
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0SQL> select * from t1;
+------+--------+-----+
| a    | name   | age |
+------+--------+-----+
| NULL | Joe    |  35 |
| NULL | Rachel |  31 |
| NULL | Monica |  33 |
+------+--------+-----+
3 rows in set (0.00 sec)--復制的不同列作為新增字段，同名的列被覆蓋      
SQL> CREATE TABLE t1 (a int, name varchar(30)) SELECT name,age FROM t2; 
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0SQL> select * from t1;
+------+--------+-----+
| a    | name   | age |
+------+--------+-----+
| NULL | Joe    |  35 |
| NULL | Rachel |  31 |
| NULL | Monica |  33 |
+------+--------+-----+
3 rows in set (0.00 sec)--為復制過來的字段定義約束
SQL> CREATE TABLE t1 (name varchar(40) not null default 'Tony') SELECT name,age FROM t2; 
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0SQL> desc t1;
+-----------+-----------------+------+-----+---------+--------------------------+
| Field     | Type            | Null | Key | Default | Extra                    |
+-----------+-----------------+------+-----+---------+--------------------------+
| my_row_id | bigint unsigned | NO   | PRI | NULL    | auto_increment INVISIBLE |
| name      | varchar(40)     | NO   |     | Tony    |                          |
| age       | int             | NO   |     | NULL    |                          |
+-----------+-----------------+------+-----+---------+--------------------------+
3 rows in set (0.00 sec)--為復制過來的字段定義索引
SQL> CREATE TABLE t1 (unique(employ_id)) SELECT employ_id,name FROM t3;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0SQL> desc t1;
+-----------+-----------------+------+-----+---------+--------------------------+
| Field     | Type            | Null | Key | Default | Extra                    |
+-----------+-----------------+------+-----+---------+--------------------------+
| my_row_id | bigint unsigned | NO   | PRI | NULL    | auto_increment INVISIBLE |
| employ_id | int             | YES  | UNI | NULL    |                          |
| name      | varchar(30)     | YES  |     | NULL    |                          |
+-----------+-----------------+------+-----+---------+--------------------------+
3 rows in set (0.01 sec)

References
【1】https://dev.mysql.com/doc/refman/8.0/en/create-table-select.html
【2】https://dev.mysql.com/doc/refman/8.0/en/create-table-like.html