Oracle建立表(create table as)


CREATE TABLE AS語句用於通過複製現有表的列從現有表來建立新表。

注意:如果以這種方式建立表,則新表將包含現有表中的記錄。

語法:

CREATE TABLE new_table  
AS (SELECT * FROM old_table);

建立表範例1:複製另一個表的所有列

在此範例中,我們通過複製現有表Customers中的所有列來建立newcustomers表。

CREATE TABLE newcustomers  
AS (SELECT *   FROM customers  WHERE customer_id < 5000);

新建立的表命名為newcustomers並具有與customers相同的表欄位和記錄(編號小於5000的所有記錄)。

建立表範例2:複製另一個表的選定列

語法:

CREATE TABLE new_table  
  AS (SELECT column_1, column2, ... column_n  
      FROM old_table);

下面來看另一個例子:

CREATE TABLE newcustomers2  
AS (SELECT customer_id, customer_name  
    FROM customers  
    WHERE customer_id < 5000);

上面的例子將建立一個名為newcustomers2的新表。 此表包含customers表中指定的兩列:customer_idcustomer_name

建立表範例3:從多個表複製選定的列

語法:

CREATE TABLE new_table  
AS (SELECT column_1, column2, ... column_n  
    FROM old_table_1, old_table_2, ... old_table_n);

下面來看一個例子:假設已經建立了兩個表:regularcustomersirregularcustomers

regularcustomers表有三列:rcustomer_idrcustomer_namerc_city

CREATE TABLE  "regularcustomers"   
   (    "RCUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE,   
    "RCUSTOMER_NAME" VARCHAR2(50) NOT NULL ENABLE,   
    "RC_CITY" VARCHAR2(50)  
   )  
/

第二個表:irregularcustomers也有三列:ircustomer_idircustomer_nameirc_city

CREATE TABLE  "irregularcustomers"   
   (    "IRCUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE,   
    "IRCUSTOMER_NAME" VARCHAR2(50) NOT NULL ENABLE,   
    "IRC_CITY" VARCHAR2(50)  
   )  
/

在下面的範例中,將建立一個表名:newcustomers3,從兩個表複製指定列。

範例:

CREATE TABLE newcustomers3  
  AS (SELECT regularcustomers.rcustomer_id, regularcustomers.rc_city, irregularcustomers.ircustomer_name  
      FROM regularcustomers, irregularcustomers  
      WHERE regularcustomers.rcustomer_id = irregularcustomers.ircustomer_id  
      AND regularcustomers.rcustomer_id < 5000);