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<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KICAyICAodGlkIG51bWJlciw8YnI+CiAgMyAgIHRuYW1lIHZhcmNoYXIyKDIwKSw8YnI+CiAgNCAgIGJpcnRoZGF5IGRhdGUgZGVmYXVsdCBzeXNkYXRlKTs8YnI+Cjxicj4KPGJyPgqx7dLRtLS9qKGjPGJyPgo8YnI+Cjxicj4KU1FMJmd0OyBpbnNlcnQgaW50byB0ZXN0Mih0aWQgLHRuYW1lKSB2YWx1ZXMoMSw="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
