CREATE TABLE 表名 AS SELECT 语句

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott

 

 
SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
 
SQL> create table dept2 as select * from dept;
 
Table created
 
SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
 
SQL> select * from dept2;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

SQL> create table dept3 as select * from dept where 1 = 2;
 
Table created
 
SQL> select * from dept3;
 
DEPTNO DNAME          LOC
------ -------------- -------------
 
SQL> select OWNER from user_constraints where table_name = 'DEPT';
 
OWNER
--------------------------------------------------------------------------------
SCOTT
 
SQL> select OWNER from user_constraints where table_name = 'DEPT2';
 
OWNER
--------------------------------------------------------------------------------
 

 

由上面的使用 CREATE TABLE 表名 AS SELECT 语句可以看出：

    1：只会复制表数据和表结构，不会有任何约束。

    2：当 where 条件不成立时，只复制表结构，没有任务数据。