# CREATE TABLE AS SELECT ## Syntax Description The `CREATE TABLE AS SELECT` command creates a new table by copying the column definitions and column data from an existing table specified in the `SELECT` query. However, it does not copy the constraints, indexes, views, or other non-data attributes of the original table. ## Syntax Structure ```sql CREATE [TEMPORARY] TABLE [ IF NOT EXISTS ] table_name [ (column_name [, ...] ) ] AS {query} Query can be any select statement in MO syntax. SELECT [ALL | DISTINCT ] select_expr [, select_expr] [[AS] alias] ... [INTO variable [, ...]] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC]] [ NULLS { FIRST | LAST } ] [LIMIT {[offset,] row_count | row_count OFFSET offset}] ``` ## Syntax interpretation - ALL: Default option, which means that all matching rows are returned, including duplicate rows. - DISTINCT: means that only unique rows are returned, that is, repeated rows are removed. - select_expr: Indicates the column or expression to be selected. - AS alias: Specify an alias for the selected column or expression. - [INTO variable [, ...]]: used to store the query result in a variable rather than return it to the client. - [FROM table_references]: Specifies which table or tables to retrieve data from. table_references can be a table name or a complex expression (such as a join) containing multiple tables. - [WHERE where_condition]: Used to filter the result set, only return rows that meet the where_condition condition. - [GROUP BY {col_name | expr | position} [ASC | DESC]]: Used to group the result set by one or more columns or expressions; ASC and DESC are used to specify how grouped inner rows are sorted. - [HAVING where_condition]: Filter the group after the grouping. Usually used with GROUP BY to filter out groupings that do not meet the criteria. - [ORDER BY {col_name | expr | position} [ASC | DESC] [NULLS {FIRST | LAST}]]: Used to sort the result set; ASC and DESC are used to specify the sorting method. - [NULLS {FIRST | LAST}]: Used to specify how to handle the position of NULL values in the sort. - [LIMIT {[offset,] row_count | row_count OFFSET offset}]: Used to limit the number of rows returned. offset specifies which row to return from the result set, 0 is the first row. row_count Specifies the number of rows returned. ## Permissions In Matrixone, executing the `CREATE TABLE AS SELECT` statement requires at least the following permissions: - `CREATE` permission: The user needs to have permission to create tables, which can be achieved through `CREATE` permission. - `INSERT` Permission: Since the `CREATE TABLE AS SELECT` statement inserts selected data into the new table, the user also needs to have permission to insert data into the target table. This can be achieved through the `INSERT` permission. - `SELECT` permission: The user needs to be able to select data in the source data table, so he needs to have SELECT permission. For more permission-related operations, please see [MatrixOne Permission Management](../../privileges.md) and [grant Description](../Data-Control-Language/grant.md). ## Example - Example 1 ```text create table t1(a int default 123, b char(5)); INSERT INTO t1 values (1, '1'),(2,'2'),(0x7ffffffff, 'max'); mysql> create table t2 as select *from t1;--Full table copy Query OK, 3 rows affected (0.02 sec) mysql> desc t2; +-------+-------+-----+-------+----------+-------------+ | Field | Type | Null | Key | Default | Extra | Comment | +-------+-------+-----+-------+----------+-------------+ | a | INT(32) | YES | | 123 | | | | | b | CHAR(5) | YES | | NULL | | | | +-------+-------+-----+-------+----------+-------------+ 2 rows in set (0.01 sec) mysql> select * from t2; +----------------+ | a | b | +----------------+ | 1 | 1 | | 2 | 2 | | 2147483647 | max | +----------------+ 3 rows in set (0.00 sec) ``` - Example 2 ```text create table t1(a int default 123, b char(5)); INSERT INTO t1 values (1, '1'),(2,'2'),(0x7ffffffff, 'max'); mysql> CREATE table test as select a as alias_a from t1;--Specify an alias for the selection column Query OK, 3 rows affected (0.02 sec) mysql> desc test; +----------------+------+---------+----------------+-------------------------+ | Field | Type | Null | Key | Default | Extra | Comment | +----------------+------+---------+----------------+-------------------------+ | alias_a | INT(32) | YES | | 123 | | | | +----------------+------+---------+----------------+-------------------------+ 1 row in set (0.01 sec) mysql> select * from test; +----------------+ | alias_a | +----------------+ | 1 | | 2 | | 2147483647 | +----------------+ 3 rows in set (0.01 sec) ``` - Example 3 ```text create table t1(a int default 123, b char(5)); INSERT INTO t1 values (1, '1'),(2,'2'),(0x7ffffffff, 'max'); mysql> create table t3 as select * from t1 where 1=2;-- Only copy fields, not data Query OK, 0 rows affected (0.01 sec) mysql> desc t3; +-------+-------+-----+-------+----------+-------------+ | Field | Type | Null | Key | Default | Extra | Comment | +-------+-------+-----+-------+----------+-------------+ | a | INT(32) | YES | | 123 | | | | | b | CHAR(5) | YES | | NULL | | | | +-------+-------+-----+-------+----------+-------------+ 2 rows in set (0.01 sec) mysql> select * from t3; Empty set (0.00 sec) ``` - Example 4 ```text create table t1(a int default 123, b char(5)); INSERT INTO t1 values (1, '1'),(2,'2'),(0x7ffffffff, 'max'); mysql> CREATE table t4(n1 int unique) as select max(a) from t1;--The original table data aggregate value is used as the column of the new table Query OK, 1 row affected (0.03 sec) mysql> desc t4; +----------------+------+---------+----------------+-----------------------+ | Field | Type | Null | Key | Default | Extra | Comment | +----------------+------+---------+----------------+-----------------------+ | n1 | INT(32) | YES | UNI | NULL | | | | max(a) | INT(32) | YES | | NULL | | | | +----------------+------+---------+----------------+-----------------------+ 2 rows in set (0.01 sec) mysql> select * from t4;+------+------------+ | n1 | max(a) | +------+------------+ | NULL | 2147483647 | +------+------------+ 1 row in set (0.00 sec) ``` - Example 5 ```text create table t5(n1 int,n2 int,n3 int); insert into t5 values(1,1,1),(1,1,1),(3,3,3); mysql> create table t5_1 as select distinct n1 from t5;--Remove duplicate rows Query OK, 2 rows affected (0.02 sec) mysql> select * from t5_1; +------+ | n1 | +------+ | 1 | | 3 | +------+ 2 rows in set (0.00 sec) ``` - Example 6 ```text create table t6(n1 int,n2 int,n3 int); insert into t6 values(1,1,3),(2,2,2),(3,3,1); mysql> create table t6_1 as select * from t6 order by n3;--Sort the result set Query OK, 3 rows affected (0.01 sec) mysql> select * from t6_1; +------+------+------+ | n1 | n2 | n3 | +------+------+------+ | 3 | 3 | 1 | | 2 | 2 | 2 | | 1 | 1 | 3 | +------+------+------+ 3 rows in set (0.01 sec) ``` - Example 7 ```text create table t7(n1 int,n2 int,n3 int); insert into t7 values(1,1,3),(1,2,2),(2,3,1),(2,3,1),(3,3,1); mysql> CREATE TABLE t7_1 AS SELECT n1 FROM t7 GROUP BY n1 HAVING count(n1)>1;--Group the result set Query OK, 2 rows affected (0.02 sec) mysql> mysql> select * from t7_1; +------+ | n1 | +------+ | 1 | | 2 | +------+ 2 rows in set (0.01 sec) ``` - Example 8 ```text create table t8(n1 int,n2 int,n3 int); insert into t8 values(1,1,1),(2,2,2),(3,3,3); mysql> CREATE TABLE t8_1 AS SELECT * FROM t8 limit 1 offset 1;--Return one row starting at the second row mysql> select * from t8_1; +------+------+------+ | n1 | n2 | n3 | +------+------+------+ | 2 | 2 | 2 | +------+------+------+ 1 row in set (0.00 sec) ``` - Example 9 ```text create table t9 (a int primary key, b varchar(5) unique key); create table t9_1 ( a int primary key, b varchar(5) unique, c int , d int, foreign key(c) references t9(a), INDEX idx_d(d) ); insert into t9 values (101,'abc'),(102,'def'); insert into t9_1 values (1,'zs1',101,1),(2,'zs2',102,1); mysql> create table t9_2 as select * from t9_1; mysql> show create table t9_1; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t9_1 | CREATE TABLE `t9_1` ( `a` INT NOT NULL, `b` VARCHAR(5) DEFAULT NULL, `c` INT DEFAULT NULL, `d` INT DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`), KEY `idx_d` (`d`), CONSTRAINT `018f27eb-0b33-7289-a3c2-af479b1833b1` FOREIGN KEY (`c`) REFERENCES `t9` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT ) | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> show create table t9_2;--CTAS does not copy the source table constraints or indexes by default +-------+-------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------+ | t9_2 | CREATE TABLE `t9_2` ( `a` INT NOT NULL, `b` VARCHAR(5) DEFAULT NULL, `c` INT DEFAULT NULL, `d` INT DEFAULT NULL ) | +-------+-------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) --Add the required constraints and indexes after creating the table ALTER TABLE t9_2 ADD PRIMARY KEY (a); ALTER TABLE t9_2 ADD UNIQUE KEY (b); ALTER TABLE t9_2 ADD FOREIGN KEY (c) REFERENCES t9 (a); ALTER TABLE t9_2 ADD INDEX idx_d3 (d); mysql> show create table t9_2;+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t9_2 | CREATE TABLE `t9_2` ( `a` INT NOT NULL, `b` VARCHAR(5) DEFAULT NULL, `c` INT DEFAULT NULL, `d` INT DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`), KEY `idx_d3` (`d`), CONSTRAINT `018f282d-4563-7e9d-9be5-79c0d0e8136d` FOREIGN KEY (`c`) REFERENCES `t9` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT ) | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ```