CUBRID学习笔记 31 通过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');

 

--没有列定义

CREATE TABLE new_tbl1 AS SELECT * FROM a_tbl;

SELECT * FROM new_tbl1;

 

           id  phone

===================================

            1  '111-1111'

            2  '222-2222'

            3  '333-3333'

 

--复制表的内容

CREATE TABLE new_tbl2

(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, phone VARCHAR) AS SELECT * FROM a_tbl;

SELECT * FROM new_tbl2;

 

           id  phone

===================================

            1  '111-1111'

            2  '222-2222'

            3  '333-3333'

 

--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

 

  name                           id  phone

=========================================================

  NULL                            1  '111-1111'

  NULL                            2  '222-2222'

  NULL                            3  '333-3333'

 

--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;

 

          id1          id2

==========================

            1            1

            1            2

            1            3

            2            1

            2            2

            2            3

            3            1

            3            2

            3            3

 

--REPLACE is used on the UNIQUE column

CREATE TABLE new_tbl5(id1 int UNIQUE) REPLACE AS SELECT * FROM new_tbl4;

SELECT * FROM new_tbl5;

 

          id1          id2

==========================

            1            3

            2            3

            3            3