spool命令、创建一个表，创建并且copy表，查看别的用户下的表，rowid行地址 索引的时候使用，表的增删改查，删除表，oracle的回收站

1、spool 命令

spool "D:\test.txt"

spool off

SQL> host cls

2、创建一个表

SQL> --条件（1）：有创建表的权限，（2）：有表空间

SQL> desc t4;

 名称                                      是否为空? 类型

 ----------------------------------------- -------- ----------------------------

 TID                                                  NUMBER

 TNAME                                              VARCHAR2(20)

 TNAME2                                             CHAR(6)

 

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID                              

------------------------------ ------- ----------                              

BONUS                          TABLE                                            

DEPT                           TABLE                                           

EMP                            TABLE                                           

EMP10                          TABLE                                            

SALGRADE                       TABLE                                           

STUDENT                        TABLE                                           

T1                             TABLE                                           

T4                             TABLE                                           

 

已选择8行。

 

SQL> drop table t1;

 

表已删除。

 

SQL> drop table t4;

 

表已删除。

 

SQL> --创建表

SQL> create table t4(tid number,tname varchar2(20),tname2 char(6));

 

表已创建。

 

SQL> --向表里面插入数据

SQL> --Oracle的数据类型

SQL> --A:varchar2(size)可变和定长区别

SQL> --B:varchar2()不能超过4096字节

SQL> --C:oracle的数据类型支持可见字符存储和不可字符的存储，存图片

 

3.创建并且copy表，使用create table tablename + as的语法

SQL> --案例：

SQL> create table t5

  2  as

  3  select * from emp;

 

表已创建。

 

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID                              

------------------------------ ------- ----------                              

BIN$5k3GcwZfRjiWlxGs/u26nA==$0 TABLE                                            

BIN$rIQD16aER0KVHp0zrF1+qA==$0 TABLE                                           

BONUS                          TABLE                                           

DEPT                           TABLE                                           

EMP                            TABLE                                           

EMP10                          TABLE                                           

SALGRADE                       TABLE                                           

STUDENT                        TABLE                                           

T4                             TABLE                                           

T5                             TABLE                                           

 

已选择10行。

 

SQL> desc t5;

 名称                                      是否为空? 类型

 ----------------------------------------- -------- ----------------------------

 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> set linesize 120

SQL> set pagesize 120

SQL> select * from t5;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                             

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                             

      7369 SMITH      CLERK           7902 17-12月-80            800                    20                             

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30                             

      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30                             

      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20                             

      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30                             

      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30                             

      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10                             

      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20                             

      7839 KING       PRESIDENT            17-11月-81           5000                    10                             

      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30                             

      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20                             

      7900 JAMES      CLERK           7698 03-12月-81            950                    30                              

      7902 FORD       ANALYST         7566 03-12月-81           3000                    20                             

      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10                              

       

已选择18行。

 

4.查看别的用户下的表

SQL> select * from scott.dept;

 

    DEPTNO DNAME          LOC                                                                                          

---------- -------------- -------------                                                                                 

        10 ACCOUNTING     NEW YORK                                                                                     

        20 RESEARCH       DALLAS                                                                                       

        30 SALES          CHICAGO                                                                                      

        40 OPERATIONS     BOSTON                                                                                       

 

5.rowid行地址 索引的时候使用

SQL> select rowid,empno,ename from emp;

 

ROWID                   EMPNO ENAME                                                                                     

------------------ ---------- ----------                                                                               

AAAR3sAAEAAAACXAAA       7369 SMITH                                                                                     

AAAR3sAAEAAAACXAAB       7499 ALLEN                                                                                    

AAAR3sAAEAAAACXAAC       7521 WARD                                                                                      

AAAR3sAAEAAAACXAAD       7566 JONES                                                                                    

AAAR3sAAEAAAACXAAE       7654 MARTIN                                                                                   

AAAR3sAAEAAAACXAAF       7698 BLAKE                                                                                    

AAAR3sAAEAAAACXAAG       7782 CLARK                                                                                    

AAAR3sAAEAAAACXAAH       7788 SCOTT                                                                                    

AAAR3sAAEAAAACXAAI       7839 KING                                                                                     

AAAR3sAAEAAAACXAAJ       7844 TURNER                                                                                   

AAAR3sAAEAAAACXAAK       7876 ADAMS                                                                                    

AAAR3sAAEAAAACXAAL       7900 JAMES                                                                                     

AAAR3sAAEAAAACXAAM       7902 FORD                                                                                     

AAAR3sAAEAAAACXAAN       7934 MILLER                                                                                    

AAAR3sAAEAAAACXAAO          1 aaa                                                                                      

AAAR3sAAEAAAACXAAP          2 bb                                                                                        

AAAR3sAAEAAAACXAAQ          3 ccc                                                                                      

AAAR3sAAEAAAACXAAR          4 cccc4                                                                                     

 

已选择18行。

 

SQL> desc t5;

 名称                                                              是否为空? 类型

 ----------------------------------------------------------------- -------- --------------------------------------------

 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)

 

6.表的增删改查

SQL> alter table t5 drop column ename;

 

表已更改。

 

SQL> desc t5;

 名称                                                              是否为空? 类型

 ----------------------------------------------------------------- -------- --------------------------------------------

 EMPNO                                                                      NUMBER(4)

 JOB                                                                        VARCHAR2(9)

 MGR                                                                        NUMBER(4)

 HIREDATE                                                                   DATE

 SAL                                                                        NUMBER(7,2)

 COMM                                                                       NUMBER(7,2)

 DEPTNO                                                                     NUMBER(2)

 

SQL> --向表中添加一列

SQL> alter table t5 add ename varchar2(10);

 

表已更改。

 

SQL> desc t5;

 名称                                                              是否为空? 类型

 ----------------------------------------------------------------- -------- --------------------------------------------

 EMPNO                                                                      NUMBER(4)

 JOB                                                                        VARCHAR2(9)

 MGR                                                                        NUMBER(4)

 HIREDATE                                                                   DATE

 SAL                                                                        NUMBER(7,2)

 COMM                                                                       NUMBER(7,2)

 DEPTNO                                                                     NUMBER(2)

 ENAME                                                                      VARCHAR2(10)

 

SQL> --修改表中的字段

SQL> alter table t5 modify varchar2(20);

SQL> alter table t5 modify ename varchar2(20);

 

表已更改。

 

SQL> desc t5;

 名称                                                              是否为空? 类型

 ----------------------------------------------------------------- -------- --------------------------------------------

 EMPNO                                                                      NUMBER(4)

 JOB                                                                        VARCHAR2(9)

 MGR                                                                        NUMBER(4)

 HIREDATE                                                                   DATE

 SAL                                                                        NUMBER(7,2)

 COMM                                                                       NUMBER(7,2)

 DEPTNO                                                                     NUMBER(2)

 ENAME                                                                      VARCHAR2(20)

 

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID                                                                      

------------------------------ ------- ----------                                                                       

BIN$5k3GcwZfRjiWlxGs/u26nA==$0 TABLE                                                                                   

BIN$rIQD16aER0KVHp0zrF1+qA==$0 TABLE                                                                                    

BONUS                          TABLE                                                                                   

DEPT                           TABLE                                                                                    

EMP                            TABLE                                                                                   

EMP10                          TABLE                                                                                    

SALGRADE                       TABLE                                                                                   

STUDENT                        TABLE                                                                                   

T4                             TABLE                                                                                   

T5                             TABLE                                                                                   

 

已选择10行。

 

7.删除表

SQL> drop table t4;

 

表已删除。

 

8.oracle的回收站

SQL> --A:查看回收站

SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME                                                 

---------------- ------------------------------ ------------ -------------------                                       

T1               BIN$rIQD16aER0KVHp0zrF1+qA==$0 TABLE        2014-10-12:21:13:59                                       

T4               BIN$yrN6U2dUQj+gtVqcgRFodw==$0 TABLE        2014-10-12:21:28:46                                       

T4               BIN$5k3GcwZfRjiWlxGs/u26nA==$0 TABLE        2014-10-12:21:14:05                                       

SQL> --B:清空回收站

SQL> purge recyclebin;

 

回收站已清空。

 

SQL> --C:彻底删除一张表 drop table test1 purge;--加上purge,直接删除一张表，不通过回收站

SQL> --D:还原表（从回收站中返复原表）

SQL> --闪回的内容

SQL> --并不是所有的人都有回收站  管理员没有回收站

SQL> --E:结论:通过回收站的名字，查看原来表的内容，需要双引号

SQL> spool off