oracle 学习笔记 - 创建和管理表

伪列rowid

rowid是行地址，用来指示记录在数据文件中的地址。

select rowid,empno,ename,sal from emp;

ROWID                   EMPNO ENAME             SAL
---------------------------------------------------
AAAMfPAAEAAAAAgAAA       7369 SMITH             800
AAAMfPAAEAAAAAgAAB       7499 ALLEN            1600
AAAMfPAAEAAAAAgAAC       7521 WARD             1250
AAAMfPAAEAAAAAgAAD       7566 JONES            2975
AAAMfPAAEAAAAAgAAE       7654 MARTIN           1250
AAAMfPAAEAAAAAgAAF       7698 BLAKE            2850
AAAMfPAAEAAAAAgAAG       7782 CLARK            2450
AAAMfPAAEAAAAAgAAH       7788 SCOTT            3000
AAAMfPAAEAAAAAgAAI       7839 KING             5000
AAAMfPAAEAAAAAgAAJ       7844 TURNER           1500
AAAMfPAAEAAAAAgAAK       7876 ADAMS            1100
AAAMfPAAEAAAAAgAAL       7900 JAMES             950
AAAMfPAAEAAAAAgAAM       7902 FORD             3000
AAAMfPAAEAAAAAgAAN       7934 MILLER           1300 

SQL> select * from emp where rowid='AAAMfPAAEAAAAAgAAI';

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO 
-------------------------------------------------------------------------------------------
      7839 KING       PRESIDENT            17-11月-81           5000                    10 

创建表

　　创建表的一般语句

create table test1
    (tid number,
    tname varchar2(20),
    hiredate date default sysdate);

insert into test1(tid,tname) values(1,'Tom');

select * from test1;

       TID TNAME                HIREDATE              
---------- -------------------- --------------         
         1 Tom                  02-7月 -16              

　　使用as生成表结构和数据

--创建表：保存20号部门的员工
 create table emp20
    as
    select * from emp where deptno=20;


 select * from emp20;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
------------------------------------------------------------------------------------------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20


SQL> create table empinfo
  2  as
  3  select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
  4  from emp e, dept d
  5  where e.deptno=d.deptno;

　　创建带约束条件的表

　　表的约束类型和约束级别。
　　1. 约束类型：primary，foreign key，not null，unique，check
　　2. 约束级别：列级约束（默认）和表级约束（联合主键）

SQL> create table student
  2  (
  3     sid number constraint student_pk primary key,
  4     sname varchar2(20) constraint student_name_notnull not null,
  5     gender varchar2(2) constraint student_gender check (gender in ('男','女')),
  6     email varchar2(40) constraint student_email_unique unique
  7                        constraint student_email_notnull not null,
  8     deptno number constraint student_fk references dept(deptno) on delete set null
  9  );

修改表

追加列，修改列，删除列，重命名列，重命名表。

　　追加列

alter table test1 add photo blob;

　　修改列

 alter table test1 modify tname varchar2(40);

　　删除列

alter table test1 drop column photo;

　　重命名列

alter table test1 rename column tname to username;

　　重命名表

rename test1 to test2;

删除表

　　删除表之后表会被存放到回收站recyclebin中，（注意：管理员没有回收站），删除是DDL语言，不可以回滚（rollback），可以对删除的表进行闪回（flashback）操作

drop table test2;

　　查看回收站

SQL> --查看回收站
SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME          
----------------------------------------------------------------------------- 
TEST2            BIN$tBAo/HbsRMClk8lSDu2kqg==$0 TABLE        2016-07-02:15:24:12 

　　清空回收站

SQL> --清空回收站
SQL> purge recyclebin;

回收站已清空。

　　访问回收站中的表数据

SQL> select * from "BIN$0KNvp4/3Qh+fFdc23V95RQ==$0";

       TID TNAME                 
---------- -------------------- 
         1 Tom                   
         2 Mary    

　　闪回删除

　　（oracle10g有6种闪回方式，oracle有7种闪回方式。闪回删除针对回收站）

SQL> flashback table TESTSAVEPOINT to before drop;

闪回完成。

　　回收站中有两个重名的表

SQL> --回收站中有两个重名的表
SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME             
---------------- ------------------------------ ------------ ------------------- 
TESTSAVEPOINT    BIN$gLUyCIcjSlqdZ0KtTN7/KQ==$0 TABLE        2016-07-02:15:34:00 
TESTSAVEPOINT    BIN$fsuiVf1UTOa5qvlzp6inRg==$0 TABLE        2016-07-02:15:33:33  

#闪回语句该怎么写？

SQL> --flashback table "BIN$fsuiVf1UTOa5qvlzp6inRg==$0" to before drop;

SQL> flashback table TESTSAVEPOINT to before drop;#闪回最后删除的那张表

SQL> flashback table TESTSAVEPOINT to before drop rename to TESTSAVEPOINT_OLD;#针对另外一张同名表，闪回是要重命名

闪回完成。