Oracle学习(9):创建和管理表

创建和管理表



表名和列名命名规则


必须以字母开头
必须在1–30 个字符之间
必须只能包含A–Z, a–z, 0–9, _, $, 和#
必须不能和用户定义的其他对象重名
必须不能是Oracle 的保留字
Oracle默认存储是都存为大写
数据库名只能是1~8位,datalink可以是128位,和其他一些特殊字符



CREATE TABLE语句

必须具备:
    ?CREATETABLE权限
    ?存储空间
必须指定:
    ?表名
    ?列名, 数据类型, 数据类型的大小


创建表的列的值的数据类型

技术分享


SQL> create table test1

  2  (tid number,tname varchar2(20));


表已创建。


Default值

l执行insert操作时,可以为其指定默认值
l值、表达式和SQL语句都可以作为默认值
l其他的列名或者是伪列都是非法的
l默认值的类型必须和该列的类型一致

CREATETABLE hire_dates
        (id          NUMBER(8),

         hire_date DATEDEFAULT SYSDATE);



SQL> create table test2

  2  (tid number,
  3   tname varchar2(20),
  4   birthday date default sysdate);


表已创建。


SQL> insert into test2(tid ,tname) values(1,‘Tom‘);


已创建 1 行。


SQL> select * from test2;


       TID TNAME                BIRTHDAY                                                                                                              
---------- -------------------- --------------                                                                                                        
         1 Tom                  27-3月 -12                                                                                                            


已选择 1 行。


查询结果作为新的表


使用ASsubquery 选项,将创建表和插入数据结合起来
指定的列和子查询中的列要一一对应
通过列名和默认值定义列


SQL> --创建表;显示员工的月薪,年薪和年收入
SQL> create table empincome
  2  as
  3  select empno,ename,sal,sal*12 annlsal ,sal*12+nvl(comm,0) income from emp;


表已创建。


SQL> select * from empincome;


     EMPNO ENAME        SAL    ANNLSAL     INCOME                                                                                                     
---------- ---------- ----- ---------- ----------                                                                                                     
      7369 SMITH        800       9600       9600                                                                                                     
      7499 ALLEN       1600      19200      19500                                                                                                     
      7521 WARD        1250      15000      15500                                                                                                     
      7566 JONES       2975      35700      35700                                                                                                     
      7654 MARTIN      1250      15000      16400                                                                                                     
      7698 BLAKE       2850      34200      34200                                                                                                     
      7782 CLARK       2450      29400      29400                                                                                                     
      7788 SCOTT       3000      36000      36000                                                                                                     
      7839 KING        5000      60000      60000                                                                                                     
      7844 TURNER      1500      18000      18000                                                                                                     
      7876 ADAMS       1100      13200      13200                                                                                                     


     EMPNO ENAME        SAL    ANNLSAL     INCOME                                                                                                     
---------- ---------- ----- ---------- ----------                                                                                                     
      7900 JAMES        950      11400      11400                                                                                                     
      7902 FORD        3000      36000      36000                                                                                                     
      7934 MILLER      1300      15600      15600                                                                                                     


已选择14行。




alter table语句修改表

使用ALTER TABLE语句可以:

l追加新的列
l修改现有的列
l删除一个列

SQL> --修改表
SQL> desc test2
 名称                                                                                是否为空? 类型
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 TID                                                                                          NUMBER
 TNAME                                                                                        VARCHAR2(20)
 BIRTHDAY                                                                                     DATE


SQL> --追加照片
SQL> alter table test2 add image blob;


表已更改。


SQL> --修改列
SQL> alter table test2 modify tname varchar2(40);


表已更改。


SQL> --删除列
SQL> alter table test2 drop column image;


表已更改。


SQL> --重命名
SQL> alter table test2 rename column tname to username;


表已更改。


SQL> desc test2
 名称                                                                                是否为空? 类型
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 TID                                                                                          NUMBER
 USERNAME                                                                                     VARCHAR2(40)
 BIRTHDAY                                                                                     DATE


SQL> host cls


删除表

l数据和结构都被删除
l所有正在运行的相关事物被提交
l所有相关索引被删除
lDROPTABLE 语句不能回滚,但是可以闪回

SQL> --删除表 drop table
SQL> select * from tab;


TNAME                          TABTYPE  CLUSTERID                                                                                                     
------------------------------ ------- ----------                                                                                                     
DEPT                           TABLE                                                                                                                  
EMP                            TABLE                                                                                                                  
BONUS                          TABLE                                                                                                                  
SALGRADE                       TABLE                                                                                                                  
EMP10                          TABLE                                                                                                                  
EMP20                          TABLE                                                                                                                  
TESTSAVEPOINT                  TABLE                                                                                                                  
TESTDELETE                     TABLE                                                                                                                  
TEST1                          TABLE                                                                                                                  
TEST2                          TABLE                                                                                                                  
EMPINCOME                      TABLE                                                                                                                  


已选择11行。


SQL> drop table test1;


表已删除。


SQL> select * from tab;


TNAME                          TABTYPE  CLUSTERID                                                                                                     
------------------------------ ------- ----------                                                                                                     
DEPT                           TABLE                                                                                                                  
EMP                            TABLE                                                                                                                  
BONUS                          TABLE                                                                                                                  
SALGRADE                       TABLE                                                                                                                  
EMP10                          TABLE                                                                                                                  
EMP20                          TABLE                                                                                                                  
TESTSAVEPOINT                  TABLE                                                                                                                  
TESTDELETE                     TABLE                                                                                                                  
TEST2                          TABLE                                                                                                                  
EMPINCOME                      TABLE                                                                                                                  
BIN$s9Jekm1bS3KsjXPzlTno6A==$0 TABLE               (放入了回收站)                                                                                                   


已选择11行。

查看回收站

SQL> --Oracle的回收站
SQL> --查看回收站
SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME                                                                                
---------------- ------------------------------ ------------ -------------------                                                                      
TEST1            BIN$s9Jekm1bS3KsjXPzlTno6A==$0 TABLE        2012-03-27:15:30:55                                                                      
SQL> drop table test2 purge;


表已删除。

清空回收站                                                          

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


回收站已清空。


SQL> select * from tab;


TNAME                          TABTYPE  CLUSTERID                                                                                                     
------------------------------ ------- ----------                                                                                                     
DEPT                           TABLE                                                                                                                  
EMP                            TABLE                                                                                                                  
BONUS                          TABLE                                                                                                                  
SALGRADE                       TABLE                                                                                                                  
EMP10                          TABLE                                                                                                                  
EMP20                          TABLE                                                                                                                  
TESTSAVEPOINT                  TABLE                                                                                                                  
TESTDELETE                     TABLE                                                                                                                  
EMPINCOME                      TABLE                                                                                                                  


已选择9行。


通过回收站中的id来查找表


SQL> drop table EMPINCOME;


表已删除。


SQL> select * from tab;


TNAME                          TABTYPE  CLUSTERID                                                                                                     
------------------------------ ------- ----------                                                                                                     
DEPT                           TABLE                                                                                                                  
EMP                            TABLE                                                                                                                  
BONUS                          TABLE                                                                                                                  
SALGRADE                       TABLE                                                                                                                  
EMP10                          TABLE                                                                                                                  
EMP20                          TABLE                                                                                                                  
TESTSAVEPOINT                  TABLE                                                                                                                  
TESTDELETE                     TABLE                                                                                                                  
BIN$91xa7gtoQfiGlzbFlex5HQ==$0 TABLE                                                                                                                  


已选择9行。


SQL> select * from "BIN$91xa7gtoQfiGlzbFlex5HQ==$0";


     EMPNO ENAME        SAL    ANNLSAL     INCOME                                                                                                     
---------- ---------- ----- ---------- ----------                                                                                                     
      7369 SMITH        800       9600       9600                                                                                                     
      7499 ALLEN       1600      19200      19500                                                                                                     
      7521 WARD        1250      15000      15500                                                                                                     
      7566 JONES       2975      35700      35700                                                                                                     
      7654 MARTIN      1250      15000      16400                                                                                                     
      7698 BLAKE       2850      34200      34200                                                                                                     
      7782 CLARK       2450      29400      29400                                                                                                     
      7788 SCOTT       3000      36000      36000                                                                                                     
      7839 KING        5000      60000      60000                                                                                                     
      7844 TURNER      1500      18000      18000                                                                                                     
      7876 ADAMS       1100      13200      13200                                                                                                     


     EMPNO ENAME        SAL    ANNLSAL     INCOME                                                                                                     
---------- ---------- ----- ---------- ----------                                                                                                     
      7900 JAMES        950      11400      11400                                                                                                     
      7902 FORD        3000      36000      36000                                                                                                     
      7934 MILLER      1300      15600      15600                                                                                                     


已选择14行。


约束

l约束是表一级的限制
l如果存在依赖关系,约束可以防止错误的删除数据
l约束的类型:
?NOTNULL
?UNIQUE
?PRIMARYKEY
?FOREIGNKEY
?CHECK



约束规则:

用户可以自定义约束,也可以使用Oracle Server的sys_cn格式命名约束
约束创建的时机:
     ?创建表的时候,同时创建约束
     ?表结构创建完成后
约束可以定义在列一级,或者是表一级
通过数据字典查看约束


有关外键约束的一些知识:

     ?FOREIGNKEY:在子表中,定义了一个表级的约束
     ?REFERENCES:指定表和父表中的列
     ?ONDELETE CASCADE: 当删除父表时,级联删除子表记录
     ?ONDELETE SET NULL: 将子表的相关依赖记录的外键值置为null



SQL> create table test3

  2  (tid number,
  3   tname varchar2(20),
  4   gender varchar2(4) check (gender in (‘男‘,‘女‘))
  5  );


表已创建。


SQL> insert into test3 values(1,‘Tom‘,‘男‘);


已创建 1 行。


SQL> insert into test3 values(2,‘Tom‘,‘啊‘);
insert into test3 values(2,‘Tom‘,‘啊‘)
*
第 1 行出现错误: 
ORA-02290: 违反检查约束条件 (SCOTT.SYS_C005474) 


考虑所有约束创建一张表的实例演示


SQL> create table myperson
  2  (pid varchar2(18) constraint myperson_PK primary key,
  3   pname varchar2(20) constraint myperson_Name not null,
  4   email varchar2(20) constraint myperson_Email_U unique
  5                      constraint myperson_Email_N not null,
  6   gender varchar2(4) constraint myperson_Gender check (gender in (‘男‘,‘女‘)),
  7   deptno number constraint myperson_FK references dept(deptno) ON DELETE CASCADE
  8  );


表已创建。



rowid(行地址指针)

可以通过rowid来访问表中的记录



select rowid,deptno,dname,loc from dept
SQL> /


ROWID                  DEPTNO DNAME          LOC                                                                                                      
------------------ ---------- -------------- -------------                                                                                            
AAAM4oAAEAAAAAMAAA         10 ACCOUNTING     NEW YORK                                                                                                 
AAAM4oAAEAAAAAMAAB         20 RESEARCH       DALLAS                                                                                                   
AAAM4oAAEAAAAAMAAC         30 SALES          CHICAGO                                                                                                  
AAAM4oAAEAAAAAMAAD         40 OPERATIONS     BOSTON                                                                                                   


已选择4行。


SQL> spool off

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。