表管理之五：创建表的方法
我们稍微讲一下其他创建表的方法。除了直接法，还有CTAS法，这一种也包含两个方法，
还有一种就是个人认为的一种创建表的方法，with语句创建虚表。with语句在查询当中
作为中间件，简化了一些复杂的查询。
1、基本法：
建表的语法：
Create table [ schema. ] table_name (
Column_name data_type [dafault expression]
[ [constatraint constraint_name ] constraint_def ]
[,... ] )
[tablespace tablespace_name ];
1》创建学生表：主键student_id
SQL> create table students(
2 student_id number(5)
3 constraint student_pk primary key,
4 name varchar2(15) not null,
5 dob date,
6 sex varchar2(6));
Table created.
2》查看表结构：
SQL> desc students;
Name Null? Type
----------------------------------------- -------- ----------------------------
STUDENT_ID NOT NULL NUMBER(5)
NAME NOT NULL VARCHAR2(15)
DOB DATE
SEX VARCHAR2(6)
3》插入表数据：
insert into students values (45211,'Mar','21-Jun-02','man');
insert into students values (45212,'Maro','21-Mar-02','man');
insert into students values (45221,'July','29-Jun-02','woman');
insert into students values (45222,'Marry','12-Jun-03','woman');
insert into students values (45223,'Mare','21-Jun-02','woman');
insert into students values (45214,'Jone','10-Jul-02','man');
insert into students values (45215,'Kaha','31-Aug-04','man');
insert into students(student_id,name) values (45216,'Kaka');
commit;
4》查看表数据：
SQL> select * from students;
STUDENT_ID NAME DOB SEX
---------- --------------- --------- ------
45211 Mar 21-JUN-02 man
45212 Maro 21-MAR-02 man
45221 July 29-JUN-02 woman
45222 Marry 12-JUN-03 woman
45223 Mare 21-JUN-02 woman
45214 Jone 10-JUL-02 man
45215 Kaha 31-AUG-04 man
45216 Kaka
8 rows selected.
2、CTAS法：
SQL> conn scott/tiger
Connected.
SQL> show user
USER is "SCOTT"
1》查看Scott用户下的表：
SQL> select tname from tab;
TNAME
------------------------------
BONUS
DEPT
EMP
SALGRADE
SL_BASE
2》查看表emp表结构：
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
3》查看表emp数据：
SQL> select empno,ename
2 from emp;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
1>>>只创建表结构：
SQL> create table worker as
2 select * from emp
3 where 1=2;
Table created.
SQL> desc worker
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select empno,ename
2 from worker;
no rows selected
只有表结构，没有数据。
2>>>创建表结构的同时，植入数据：
SQL> create table employees1 as
2 select * from emp
3 where sal>3000;
Table created.
SQL> desc employees1;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select empno,ename
2 from employees1;
EMPNO ENAME
---------- ----------
7839 KING
既有表结构，又有数据在表中。
3、with语句创建虚表：
SQL> with
2 t1 as
3 (select 1 as id,'a' as name from dual
4 union all
5 select 2,'b' from dual
6 union all
7 select 3,'c' from dual),
8 t2 as
9 (select 2 as id,'bb' as name from dual
10 union all
11 select 3,'cc' from dual
12 union all
13 select 4,'dd' from dual)
14 select t1.id,t1.name,t2.id,t2.name
15 from t1 right outer join t2
16 on t1.id = t2.id;
ID N ID NA
---------- - ---------- --
2 b 2 bb
3 c 3 cc
4 dd
1》尝试查看t1,t2的表结构：
SQL> desc t1;
ERROR:
ORA-04043: object t1 does not exist
SQL> desc t2;
ERROR:
ORA-04043: object t2 does not exist
2》查看伪表dual的表结构：
SQL> desc dual;
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
从上面的测试中可以看到：CTAS表创建的两种方法中，不同在于一个值创建表结构，没有数据，一个既有表结构，又有数据。
而相同的在于，创建的表不同于它的源表，只复制了源表的表结构，没有复制源表的约束。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31392094/viewspace-2125883/ ，如需转载，请注明出处，否则将追究法律责任。
