Oracle中創建和管理表詳解

以下是對Oracle中的創建和管理表進行了詳細的分析介紹需要的朋友可以過來參考下  

　　SQL> /*
SQL> 對於表的操作: 創建表修改表(添加新的列改變當前某些列刪除列)刪除表
SQL> 創建表: create table(需要create table的權限)
SQL> 修改表: alter table tablename add/modify/drop
SQL> 刪除表drop table tablename
SQL> */
SQL> show user;
USER 為 "SCOTT"
SQL> 訪問hr用戶下的表
SQL> select * from hremployees;
select * from hremployees
                 *
第 行出現錯誤:
ORA: 表或視圖不存在
SQL> 測試defaul值
SQL> create table test
    (tid number
     tname varchar()
     hiredate date default sysdate);
表已創建
SQL> insert into test(tidtname) values(Mary);
已創建 行
SQL> select * from test;
       TID TNAME                HIREDATE                                                                               
                                                                         
         Mary                 月                                                                              
SQL> rowid rownum都是偽列
SQL> select rowidrownumempno from emp;
ROWID                  ROWNUM      EMPNO                                                                               
                                                                               
AAANAAAEAAAAAsAAT                                                                                                
AAANAAAEAAAAAsAAO                                                                                                
AAANAAAEAAAAAsAAP                                                                                                
AAANAAAEAAAAAsAAQ                                                                                                
AAANAAAEAAAAAsAAR                                                                                                
AAANAAAEAAAAAsAAS                                                                                                
AAANAAAEAAAAAsAAA                                                                                                
AAANAAAEAAAAAsAAB                                                                                                
AAANAAAEAAAAAsAAC                                                                                                
AAANAAAEAAAAAsAAD                                                                                               
AAANAAAEAAAAAsAAE                                                                                               
ROWID                  ROWNUM      EMPNO                                                                               
                                                                               
AAANAAAEAAAAAsAAF                                                                                               
AAANAAAEAAAAAsAAG                                                                                               
AAANAAAEAAAAAsAAH                                                                                               
AAANAAAEAAAAAsAAI                                                                                               
AAANAAAEAAAAAsAAJ                                                                                               
AAANAAAEAAAAAsAAK                                                                                               
AAANAAAEAAAAAsAAL                                                                                               
AAANAAAEAAAAAsAAM                                                                                               
AAANAAAEAAAAAsAAN                                                                                               
已選擇行
SQL> rowid:oracle維護一個地址該地址指向了該行在硬盤上實際存儲的位置
SQL> 關於varchar和char
SQL> create table testchar
    ( c char()
      v varchar());
表已創建
SQL> insert into testchar values(ab);
已創建 行
SQL> select * from testchar;
C     V                                                                                                                
                                                                                                            
a     b                                                                                                                
SQL> select concat(c#)concat(v#) from testchar;
CONCAT CONCAT                                                                                                          
                                                                                                          
a    # b#                                                                                                              
SQL> 添加新列
SQL> alter table testchar
    add  hiredate date;
表已更改
SQL> desc testchar;
 名稱                                                              是否為空? 類型
 
 C                                                                          CHAR()
 V                                                                          VARCHAR()
 HIREDATE                                                                   DATE
SQL> 修改表
SQL> alter table testchar
    modify c char();
表已更改
SQL> desc testchar;
 名稱                                                              是否為空? 類型
 
 C                                                                          CHAR()
 V                                                                          VARCHAR()
 HIREDATE                                                                   DATE
SQL> 刪除列
SQL> alter table testchar
    drop hiredate;
drop hiredate
     *
第 行出現錯誤:
ORA: 缺失關鍵字
SQL> ed
已寫入 file afiedtbuf
    alter table testchar
  * drop column hiredate
SQL> /
表已更改
SQL> desc testchar;
 名稱                                                              是否為空? 類型
 
 C                                                                          CHAR()
 V                                                                          VARCHAR()
SQL> host cls
SQL> 刪除表
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID                                                                      
                                                                      
DEPT                           TABLE                                                                                   
EMP                            TABLE                                                                                   
BONUS                          TABLE                                                                                   
SALGRADE                       TABLE                                                                                   
EMP                          TABLE                                                                                   
EMP                         TABLE                                                                                   
TEST                          TABLE                                                                                   
BIN$gNMeyRKWvjhtZZFsA==$ TABLE                                                                                   
TESTDELETE                     TABLE                                                                                   
TESTCHAR                       TABLE                                                                                   
已選擇行
SQL> drop table testdelete;
表已刪除
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID                                                                      
                                                                      
DEPT                           TABLE                                                                                   
EMP                            TABLE                                                                                   
BONUS                          TABLE                                                                                   
SALGRADE                       TABLE                                                                                   
EMP                          TABLE                                                                                   
EMP                         TABLE                                                                                   
TEST                          TABLE                                                                                   
BIN$gNMeyRKWvjhtZZFsA==$ TABLE                                                                                   
TESTCHAR                       TABLE                                                                                   
BIN$aJrSiffTOGcDHfepg==$ TABLE                                                                                   
已選擇行
SQL> 使用purge參數徹底刪除表
SQL> drop table test purge;
表已刪除
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID                                                                      
                                                                      
DEPT                           TABLE                                                                                   
EMP                            TABLE                                                                                   
BONUS                          TABLE                                                                                   
SALGRADE                       TABLE                                                                                   
EMP                          TABLE                                                                                   
EMP                         TABLE                                                                                   
BIN$gNMeyRKWvjhtZZFsA==$ TABLE                                                                                   
TESTCHAR                       TABLE                                                                                   
BIN$aJrSiffTOGcDHfepg==$ TABLE                                                                                   
已選擇行
SQL> oracle的回收站
SQL> 查看回收站
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME                                                 
                                       
TESTDELETE       BIN$aJrSiffTOGcDHfepg==$ TABLE        :::                                       
TESTDELETE       BIN$gNMeyRKWvjhtZZFsA==$ TABLE        :::                                       
SQL> 清空回收站
SQL> purge recyclebin;
回收站已清空
SQL> show recyclebin;
SQL> 關於約束:
SQL> 創建一個表包含所有約束
SQL> create table myuser
    ( userID number constraint pk primary key
      username varchar() constraint c_name not null
      gender   varchar()  constraint c_gender check (gender in (男女))
      email    varchar() constraint c_email not null
                            constraint c_email unique
      deptno   number constraint fk refereneces dept(deptno)
    );
  deptno   number constraint fk refereneces dept(deptno)
  *
第 行出現錯誤:
ORA: 缺失右括號
SQL>   create table myuser
    ( userID number constraint pk primary key
      username varchar() constraint c_name not null
      gender   varchar()  constraint c_gender check (gender in (男女))
      email    varchar() constraint c_email not null
                            constraint c_email unique
      deptno   number constraint fk refereneces dept(deptno)
    );
  deptno   number constraint fk refereneces dept(deptno)
                  *
第 行出現錯誤:
ORA: 此處不允許約束條件說明
SQL> ed
已寫入 file afiedtbuf
      create table myuser
    ( userID number constraint pk primary key
      username varchar() constraint c_name not null
      gender   varchar()  constraint c_gender check (gender in (男女))
      email    varchar() constraint c_email not null
                            constraint c_email unique
      deptno   number constraint fk references dept(deptno)
  * )
SQL> /
表已創建
SQL> desc myuser;
 名稱                                                              是否為空? 類型
 
 USERID                                                            NOT NULL NUMBER
 USERNAME                                                          NOT NULL VARCHAR()
 GENDER                                                                     VARCHAR()
 EMAIL                                                             NOT NULL VARCHAR()
 DEPTNO                                                                     NUMBER
SQL> insert into myuser values(Tom男ddd@com);
已創建 行
SQL> insert into myuser values(Tom男ddd@com);
insert into myuser values(Tom男ddd@com)
*
第 行出現錯誤:
ORA: 違反唯一約束條件 (SCOTTPK)
SQL> insert into myuser values(Tom啊ddd@coddm);
insert into myuser values(Tom啊ddd@coddm)
*
第 行出現錯誤:
ORA: 違反檢查約束條件 (SCOTTC_GENDER)
SQL> 觸發器也可以檢查數據的正確與否
SQL> spool off

From:http://tw.wingwit.com/Article/program/Oracle/201311/19039.html