語法

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'

?

--復(fù)制表的內(nèi)容

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

轉(zhuǎn)載于:https://www.cnblogs.com/wang2650/p/5287805.html

總結(jié)

以上是生活随笔為你收集整理的CUBRID学习笔记 31 通过select创建表的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。