CREATE TABLE 表名 AS SELECT 语句

最新推荐文章于 2024-04-10 07:47:25 发布

转载最新推荐文章于 2024-04-10 07:47:25 发布 · 1k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

原文链接：http://ears.iteye.com/blog/1470605

Oracle 专栏收录该内容

9 篇文章

订阅专栏

本文探讨了使用SQL语句'CREATE TABLE...AS SELECT...'进行表复制的方法，指出该操作仅复制表数据和结构，不包含约束。并演示了如何在特定条件下仅复制表结构。

SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
OPERATIONS     BOSTON
 
SQL> create table dept2 as select * from dept;
 
Table created
 
SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
OPERATIONS     BOSTON
 
SQL> select * from dept2;
 
DEPTNO DNAME          LOC
------ -------------- -------------
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
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
--------------------------------------------------------------------------------

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

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

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

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