logo图片
Oracle教程

Oracle CREATE TABLE AS

CREATE TABLE AS语句用于通过复制现有表的列从现有表创建表。
注意: 如果以这种方式创建表,则新表将包含现有表中的记录。
语法:
CREATE TABLE new_table
AS (SELECT * FROM old_table); 

创建表示例: 复制另一个表的所有列

在此示例中,我们通过复制已存在的表" Customers"中的所有列来创建" newcustomers"表"。
CREATE TABLE newcustomers
AS (SELECT *   FROM customers  WHERE customer_id < 5000);
Table created.
该表被命名为" newcustomers",并且具有与" customers"表相同的列。

创建表示例: 复制另一个表的选定列

语法:
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"的新表。该表包括客户表中指定的列customer_id和customer_name。

创建表示例: 从多个表中复制选定的列

语法:
  CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table_1, old_table_2, ... old_table_n); 
让我们举个例子: 假设您已经创建了两个表" regularcustomers"和" irregularcustomers"。
表" regularcustomers"具有三列rcustomer_id,rcustomer_name和rc_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_id,ircustomer_name和irc_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); 
"内容整理不易,点下广告也是鼓励~"
昵称: 邮箱:
Copyright © 2020 立地货 All Rights Reserved.
备案号:京ICP备14037608号-4