Oracle建立表語句(Create table)文法詳解及樣本

標籤：blank   繼承   date   comment   replace   int   二進位   lob   reg   

Oracle建立表語句(Create table)文法詳解及樣本  

 

建立表(Create table)文法詳解
1. ORACLE常用的欄位類型
ORACLE常用的欄位類型有
VARCHAR2 (size) 可變長度的字串, 必須規定長度
CHAR(size) 固定長度的字串, 不規定長度預設值為１
NUMBER(p,s) 數字型p是位元總長度, s是小數的長度, 可存負數
最長38位. 不夠位時會四捨五入.
DATE 日期和時間類型
LOB 超長字元, 最大可達4G
CLOB 超長文本字串
BLOB 超長二進位字串
BFILE 超長二進位字串, 儲存在資料庫外的檔案裡是唯讀.
數字欄位類型位元及其四捨五入的結果
原始數值1234567.89
數字欄位類型位元 儲存的值
Number 1234567.89
Number(8) 12345678
Number(6) 錯
Number(9,1) 1234567.9
Number(9,3) 錯
Number(7,2) 錯
Number(5,-2) 1234600
Number(5,-4) 1230000
Number(*,1) 1234567.9
2. 建立表時給欄位加預設值 和約束條件
建立表時可以給欄位加上預設值
例如 : 日期欄位 DEFAULT SYSDATE
這樣每次插入和修改時, 不用程式操作這個欄位都能得到動作的時間
建立表時可以給欄位加上約束條件
例如: 非空 NOT NULL
不允許重複 UNIQUE
關鍵字 PRIMARY KEY
按條件檢查 CHECK (條件)
外鍵 REFERENCES 表名(欄位名)
3. 建立表的例子
CREATE TABLE DEPT(
EPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)) ;
CREATE TABLE region(
ID number(2) NOT NULL PRIMARY KEY,
postcode number(6) default ‘0‘ NOT NULL,
areaname varchar2(30) default ‘ ‘ NOT NULL);
4. 建立表時的命名規則和注意事項
1)表名和欄位名的命名規則：必須以字母開頭，可以含符號A-Z,a-z,0-9,_,$,#
2)大小寫不區分
3)不用SQL裡的保留字, 一定要用時可用雙引號把字串括起來．
4)用和實體或屬性相關的英文符號長度有一定的限制
注意事項:
1)建表時可以用中文的欄位名, 但最好還是用英文的欄位名
2)建立表時要把較小的不為空白的欄位放在前面, 可能為空白的欄位放在後面
3)建表時如果有唯一關鍵字或者唯一的約束條件，建表時自動建了索引
4)一個表的最多欄位個數也是有限制的，254個.
5. 約束名的命名規則和文法
約束名的命名規則約束名如果在建表的時候沒有指明，系統命名規則是SYS_Cn(n是數字)
約束名字串的命名規則同於表和欄位名的命名規則
6. 使用約束時的注意事項
約束裡不能用系統函數,如SYSDATE和別的表的欄位比較
可以用本表內欄位的比較
想在交易處理後, 做約束的

檢查
SQL> alter session set constraints deferred.
7. 由實體關聯圖到建立表的例子 s_dept
前提條件:已有region表且含唯一關鍵字的欄位id
SQL> CREATE TABLE s_dept
(id NUMBER(7)
CONSTRAINT s_dept_id_pk PRIMARY KEY,
name VARCHAR2(25)
CONSTRAINT s_dept_name_nn NOT NULL,
region_id NUMBER(7)
CONSTRAINT s_dept_region_id_fk REFERENCES region (id),
CONSTRAINT s_dept_name_region_id_uk UNIQUE(name, region_id));
8. 較複雜的建立表例子
SQL> CREATE TABLE s_emp
(id NUMBER(7)
CONSTRAINT s_emp_id_pk PRIMARY KEY,
last_name VARCHAR2(25)
CONSTRAINT s_emp_last_name_nn NOT NULL,
first_name VARCHAR2(25),
userid VARCHAR2(8)
CONSTRAINT s_emp_userid_nn NOT NULL
CONSTRAINT s_emp_userid_uk UNIQUE,
start_date DATE DEFAULT SYSDATE,
comments VARCHAR2(25),
manager_id NUMBER(7),
title VARCHAR2(25),
dept_id NUMBER(7)
CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept(id),
salary NUMBER(11,2),
commission_pct NUMBER(4,2)
CONSTRAINT s_emp_commission_pct_ck CHECK
(commission_pct IN(10,12.5,15,17.5,20)));
8. 通過子查詢建表
通過子查詢建表的例子
SQL>CREATE TABLE emp_41 AS SELECT id, last_name, userid, start_date
FROM s_emp WHERE dept_id = 41;
SQL> CREATE TABLE A as select * from B where 1=2;
只要表的結構.
10. 用子查詢建表的注意事項
1)可以關連多個表及用集合函數產生新表,注意選擇出來的欄位必須有合法的欄位名稱,且不能重複。
2)用子查詢方式建立的表，只有非空NOT NULL的約束條件能繼承過來, 其它的約束條件和預設值都沒有繼承過來.
3)根據需要，可以用alter table add constraint ……再建立其它的約束條件，如primary key等.
11. Foreign Key的選擇性參數ON DELETE CASCADE
在建立Foreign Key時可以加選擇性參數:
ON DELETE CASCADE它的含義是如果刪除外鍵主表裡的內容，子表裡相關的內容將一起被刪除.
如果沒有ON DELETE CASCADE參數，子表裡有內容，父表裡的主關鍵字記錄不能被刪除掉.
12. 如果資料庫表裡有不滿足的記錄存在，建立約束條件將不會成功.
13. 給表建立和刪除同義字的例子
SQL> CREATE SYNONYM d_sum
2 FOR dept_sum_vu;
SQL> CREATE PUBLIC SYNONYM s_dept
2 FOR alice.s_dept;
SQL> DROP SYNONYM s_dept;

Oracle建立表語句(Create table)文法詳解及樣本