CREATE TABLE … AS SELECT 문을 사용하여 SELECT 문의 결과 레코드를 포함하는 새로운 테이블을 생성할 수 있다. 새로운 테이블에 대해 컬럼 및 테이블 제약 조건을 정의할 수 있으며, 다음의 규칙을 적용하여 SELECT 결과 레코드를 반영한다.
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');
--creating a table without column definition
CREATE TABLE new_tbl1 AS SELECT * FROM a_tbl;
SELECT * FROM new_tbl1;
;xr
=== <Result of SELECT Command in Line 1> ===
id phone
===================================
1 '111-1111'
2 '222-2222'
3 '333-3333'
3 rows selected.
--all of column values are replicated from a_tbl
CREATE TABLE new_tbl2
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, phone VARCHAR) AS SELECT * FROM a_tbl;
SELECT * FROM new_tbl2;
;xr
=== <Result of SELECT Command in Line 1> ===
id phone
===================================
1 '111-1111'
2 '222-2222'
3 '333-3333'
3 rows selected.
--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
;xr
=== <Result of SELECT Command in Line 1> ===
name id phone
=========================================================
NULL 1 '111-1111'
NULL 2 '222-2222'
NULL 3 '333-3333'
3 rows selected.
--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;
;xr
=== <Result of SELECT Command in Line 1> ===
id1 id2
==========================
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
9 rows selected.
--REPLACE is used on the UNIQUE column
CREATE TABLE new_tbl5(id1 int UNIQUE) REPLACE AS SELECT * FROM new_tbl4;
SELECT * FROM new_tbl5;
;xr
=== <Result of SELECT Command in Line 1> ===
id1 id2
==========================
1 3
2 3
3 3
3 rows selected.