CREATE TABLE AS SELECT

설명

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.