Oracle数据库对象(共12个)DDL

常用的五个

表 视图 序列 索引 同义词

SQL> --创建表SQL> create table test1  2  (tid number,  3   tname varchar2(20),  4   hidatedate date default sysdate);表已创建。SQL> insert into test1(tid,tname) values(1,'Tom');已创建 1 行。SQL> select * from test1;       TID TNAME                HIDATEDATE                                                                                                            ---------- -------------------- --------------                                                                                                                 1 Tom                  23-10月-12                                                                                                            已选择 1 行。SQL> create table emp10 as select * from emp where deptno=10;表已创建。SQL> select * from emp10;     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM DEPTNO                                                                     ---------- ---------- --------- ---------- -------------- ----- ---------- ------                                                                           7782 CLARK      MANAGER         7839 09-6月 -81      2450                10                                                                           7839 KING       PRESIDENT            17-11月-81      5000                10                                                                           7934 MILLER     CLERK           7782 23-1月 -82      1300                10                                                                     已选择3行。SQL> --快速建表SQL> --创建表：包含员工号 姓名 月薪 年薪 部门名称SQL> create table empincome  2  as  3  select empno,ename,sal,sal*12 annlsal,dname  4  from emp e, dept d  5  where e.deptno=d.deptno;表已创建。SQL> select * from empincome;     EMPNO ENAME        SAL    ANNLSAL DNAME                                                                                                          ---------- ---------- ----- ---------- --------------                                                                                                       7369 SMITH        800       9600 RESEARCH                                                                                                             7499 ALLEN       1600      19200 SALES                                                                                                                7521 WARD        1250      15000 SALES                                                                                                                7566 JONES       2975      35700 RESEARCH                                                                                                             7654 MARTIN      1250      15000 SALES                                                                                                                7698 BLAKE       2850      34200 SALES                                                                                                                7782 CLARK       2450      29400 ACCOUNTING                                                                                                           7788 SCOTT       3000      36000 RESEARCH                                                                                                             7839 KING        5000      60000 ACCOUNTING                                                                                                           7844 TURNER      1500      18000 SALES                                                                                                                7876 ADAMS       1100      13200 RESEARCH                                                                                                            EMPNO ENAME        SAL    ANNLSAL DNAME                                                                                                          ---------- ---------- ----- ---------- --------------                                                                                                       7900 JAMES        950      11400 SALES                                                                                                                7902 FORD        3000      36000 RESEARCH                                                                                                             7934 MILLER      1300      15600 ACCOUNTING                                                                                                     已选择14行。SQL> --rowid 行地址SQL> select rowid,empno,ename from emp;ROWID                   EMPNO ENAME                                                                                                                   ------------------ ---------- ----------                                                                                                              AAANnOAAEAAAAAcAAA       7369 SMITH                                                                                                                   AAANnOAAEAAAAAcAAB       7499 ALLEN                                                                                                                   AAANnOAAEAAAAAcAAC       7521 WARD                                                                                                                    AAANnOAAEAAAAAcAAD       7566 JONES                                                                                                                   AAANnOAAEAAAAAcAAE       7654 MARTIN                                                                                                                  AAANnOAAEAAAAAcAAF       7698 BLAKE                                                                                                                   AAANnOAAEAAAAAcAAG       7782 CLARK                                                                                                                   AAANnOAAEAAAAAcAAH       7788 SCOTT                                                                                                                   AAANnOAAEAAAAAcAAI       7839 KING                                                                                                                    AAANnOAAEAAAAAcAAJ       7844 TURNER                                                                                                                  AAANnOAAEAAAAAcAAK       7876 ADAMS                                                                                                                   ROWID                   EMPNO ENAME                                                                                                                   ------------------ ---------- ----------                                                                                                              AAANnOAAEAAAAAcAAL       7900 JAMES                                                                                                                   AAANnOAAEAAAAAcAAM       7902 FORD                                                                                                                    AAANnOAAEAAAAAcAAN       7934 MILLER                                                                                                                  已选择14行。SQL> --修改表: 追加新列 修改列 删除列  重命名列SQL> desc test1 名称                                                                                是否为空? 类型 ----------------------------------------------------------------------------------- -------- -------------------------------------------------------- TID                                                                                          NUMBER TNAME                                                                                        VARCHAR2(20) HIDATEDATE                                                                                   DATESQL> alter table test1 add photo blob;表已更改。SQL> desc test1 名称                                                                                是否为空? 类型 ----------------------------------------------------------------------------------- -------- -------------------------------------------------------- TID                                                                                          NUMBER TNAME                                                                                        VARCHAR2(20) HIDATEDATE                                                                                   DATE PHOTO                                                                                        BLOBSQL> alter table test1 modify tname varchar2(40);表已更改。SQL> desc test1 名称                                                                                是否为空? 类型 ----------------------------------------------------------------------------------- -------- -------------------------------------------------------- TID                                                                                          NUMBER TNAME                                                                                        VARCHAR2(40) HIDATEDATE                                                                                   DATE PHOTO                                                                                        BLOBSQL> alter table test1 drop column photo;表已更改。SQL> desc test1 名称                                                                                是否为空? 类型 ----------------------------------------------------------------------------------- -------- -------------------------------------------------------- TID                                                                                          NUMBER TNAME                                                                                        VARCHAR2(40) HIDATEDATE                                                                                   DATESQL> alter table test1 rename column tname to username;表已更改。SQL> desc test1; 名称                                                                                是否为空? 类型 ----------------------------------------------------------------------------------- -------- -------------------------------------------------------- TID                                                                                          NUMBER USERNAME                                                                                     VARCHAR2(40) HIDATEDATE                                                                                   DATESQL> --删除表SQL> drop table TESTDELETE;表已删除。SQL> --Oracle的回收站SQL> --查看回收站SQL> show recyclebinORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME                                                                                ---------------- ------------------------------ ------------ -------------------                                                                      TESTDELETE       BIN$oY28BlKeSle/lJNUpKzrMQ==$0 TABLE        2012-10-23:15:42:09                                                                      SQL> --清空回收站SQL> purge recyclebin  2  ;回收站已清空。SQL> --彻底删除表SQL> drop table test1 purge;表已删除。SQL> show recyclebin

一般使用On delete SET　NULL

SQL> -- check约束SQL> create table test2  2  (tid number,  3   tname varchar2(20),  4   gender varchar2(4) check (gender in ('男','女')),  5   sal number check (sal >0 )  6  );表已创建。SQL> insert into test2 values(1,'Tom','男',1000);已创建 1 行。SQL> insert into test2 values(1,'Tom','啊',1000);insert into test2 values(1,'Tom','啊',1000)*第 1 行出现错误: ORA-02290: 违反检查约束条件 (SCOTT.SYS_C006031) SQL> create table myperson(  2   pid varchar2(18) constraint myperson_PK primary key,  3   pname varchar2(4) constraint myperson_Name not null,  4   gender varchar2(4) constraint myperson_Gender check (gender in ('男','女')),  5   email varchar2(30) constraint myperson_Email unique,  6   deptno number constraint myperson_FK references dept(deptno) ON DELETE CASCADE  7  );表已创建。SQL> insert into myperson values('p001','tom','男','[e-mail]',10);已创建 1 行。SQL> insert into myperson values('p002','tom','啊','[e-mail]',10);insert into myperson values('p002','tom','啊','[e-mail]',10)*第 1 行出现错误: ORA-02290: 违反检查约束条件 (SCOTT.MYPERSON_GENDER)