Skip to content

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

> 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 and grant Description.

Example

  • Example 1
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
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
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
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
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;--去除重复行
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
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;--对结果集进行排序
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
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;--对结果集进行分组
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
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;--指定从结果集的第二行开始返回，返回行数为 1

mysql> select * from t8_1;
+------+------+------+
| n1   | n2   | n3   |
+------+------+------+
|    2 |    2 |    2 |
+------+------+------+
1 row in set (0.00 sec)
  • Example 9
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 创建的新表默认不会带有原表的约束和索引
+-------+-------------------------------------------------------------------------------------------------------------------+
| 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)

--如果希望新表带有原表带约束和索引，则可以在建表后添加
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)