ORACLE 常用的SQL文法和資料對象 轉

一.資料控制語句(DML)部分

1.INSERT(往資料表裡插入記錄的語句)

INSERTINTO表名(欄位名1,欄位名2,……)VALUES(值1,值2,……);
INSERTINTO表名(欄位名1,欄位名2,……)SELECT(欄位名1,欄位名2,……)FROM另外的表名;

字串類型的欄位值必須用單引號括起來,例如:’GOODDAY’
如果欄位值裡包含單引號’需要進行字串轉換,我們把它替換成兩個單引號''.
字串類型的欄位值超過定義的長度會出錯,最好在插入前進行長度校正.

日期欄位的欄位值可以用當前資料庫的系統時間SYSDATE,精確到秒
或者用字串轉換成日期型函數TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)
TO_DATE()還有很多種日期格式,可以參看ORACLEDOC.
年-月-日小時:分鐘:秒的格式YYYY-MM-DDHH24:MI:SS

INSERT時最大可操作的字串長度小於等於4000個單位元組,如果要插入更長的字串,請考慮欄位用CLOB類型,
方法借用ORACLE裡內建的DBMS_LOB程式包.

INSERT時如果要用到從1開始自動成長的序號,應該先建立一個序號
CREATESEQUENCE序號的名稱(最好是表名+序號標記)INCREMENTBY1STARTWITH1
MAXVALUE99999CYCLENOCACHE;
其中最大的值按欄位的長度來定,如果定義的自動成長的序號NUMBER(6),最大值為999999
INSERT語句插入這個欄位值為:序號的名稱.NEXTVAL

2.DELETE(刪除資料表裡記錄的語句)

DELETEFROM表名WHERE條件;

注意：刪除記錄並不能釋放ORACLE裡被佔用的資料區塊資料表空間.它只把那些被刪除的資料區塊標成unused.

如果確實要刪除一個大表裡的全部記錄,可以用TRUNCATE命令,它可以釋放佔用的資料區塊資料表空間
TRUNCATETABLE表名;
此操作不可回退.

3.UPDATE(修改資料表裡記錄的語句)

UPDATE表名SET欄位名1=值1,欄位名2=值2,……WHERE條件;

如果修改的值N沒有賦值或定義時,將把原來的記錄內容清為NULL,最好在修改前進行非空校正;
值N超過定義的長度會出錯,最好在插入前進行長度校正..

注意事項:
A. 以上SQL語句對錶都加上了行級鎖,
確認完成後,必須加上事物處理結束的命令COMMIT才能正式生效,
否則改變不一定寫入資料庫裡.
如果想撤回這些操作,可以用命令ROLLBACK複原.

B. 在運行INSERT,DELETE和UPDATE語句前最好估算一下可能操作的記錄範圍,
應該把它限定在較小(一萬條記錄)範圍內,.否則ORACLE處理這個事物用到很大的回退段.
程式響應慢甚至失去響應.如果記錄數上十萬以上這些操作,可以把這些SQL語句分段分次完成,
其間加上COMMIT確認事物處理.

二.資料定義(DDL)部分

1.CREATE(建立表,索引,視圖,同義字,過程,函數,資料庫連結等)

ORACLE常用的欄位類型有
CHAR 固定長度的字串
VARCHAR2 可變長度的字串
NUMBER(M,N) 數字型M是位元總長度,N是小數的長度
DATE 日期類型

建立表時要把較小的不為空白的欄位放在前面,可能為空白的欄位放在後面

建立表時可以用中文的欄位名,但最好還是用英文的欄位名

建立表時可以給欄位加上預設值,例如DEFAULTSYSDATE
這樣每次插入和修改時,不用程式操作這個欄位都能得到動作的時間

建立表時可以給欄位加上約束條件
例如不允許重複UNIQUE,關鍵字PRIMARYKEY

2.ALTER (改變表,索引,視圖等)

改變表的名稱
ALTERTABLE表名1TO表名2;

在表的後面增加一個欄位
ALTERTABLE表名ADD欄位名欄位名描述;

修改表裡欄位的定義描述
ALTERTABLE表名MODIFY欄位名欄位名描述;

給表裡的欄位加上約束條件
ALTERTABLE表名ADDCONSTRAINT約束名PRIMARYKEY(欄位名);
ALTERTABLE表名ADDCONSTRAINT約束名UNIQUE(欄位名);

把表放在或取出資料庫的記憶體區
ALTERTABLE表名CACHE;
ALTERTABLE表名NOCACHE;

3.DROP (刪除表,索引,視圖,同義字,過程,函數,資料庫連結等)

刪除表和它所有的約束條件
DROPTABLE表名CASCADECONSTRAINTS;

4.TRUNCATE(清空表裡的所有記錄,保留表的結構)

TRUNCATE表名;

三.查詢語句(SELECT)部分

SELECT欄位名1,欄位名2,……FROM表名1,[表名2,……>WHERE條件;

欄位名可以帶入函數
例如:COUNT(*),MIN(欄位名),MAX(欄位名),AVG(欄位名),DISTINCT(欄位名),
TO_CHAR(DATE欄位名,'YYYY-MM-DDHH24:MI:SS')

NVL(EXPR1,EXPR2)函數
解釋:
IFEXPR1=NULL
RETURNEXPR2
ELSE
RETURNEXPR1

DECODE(AA﹐V1﹐R1﹐V2﹐R2....)函數
解釋:
IFAA=V1THENRETURNR1
IFAA=V2THENRETURNR2
..…
ELSE
RETURNNULL

LPAD(char1,n,char2)函數
解釋:
字元char1按制定的位元n顯示，不足的位元用char2字串替換左邊的空位

欄位名之間可以進行算術運算
例如:(欄位名1*欄位名1)/3

查詢語句可以嵌套
例如:SELECT……FROM
(SELECT……FROM表名1,[表名2,……>WHERE條件)WHERE條件2;

兩個查詢語句的結果可以做集合操作
例如:並集UNION(去掉重複記錄),並集UNIONALL(不去掉重複記錄),差集MINUS,交集INTERSECT

分組查詢
SELECT欄位名1,欄位名2,……FROM表名1,[表名2,……>GROUPBY欄位名1
[HAVING條件>;

兩個以上表之間的串連查詢

SELECT欄位名1,欄位名2,……FROM表名1,[表名2,……>WHERE
表名1.欄位名=表名2.欄位名[AND……>;

SELECT欄位名1,欄位名2,……FROM表名1,[表名2,……>WHERE
表名1.欄位名=表名2.欄位名(+)[AND……>;

有(+)號的欄位位置自動補空值

查詢結果集的排序操作,預設的排序是升序ASC,降序是DESC

SELECT欄位名1,欄位名2,……FROM表名1,[表名2,……>
ORDERBY欄位名1,欄位名2DESC;

字串模糊比較的方法

INSTR(欄位名,‘字串’)>0
欄位名LIKE‘字串%’[‘%字串%’>

每個表都有一個隱含的欄位ROWID,它標記著記錄的唯一性.

四.ORACLE裡常用的資料對象(SCHEMA)

1.索引(INDEX)

CREATEINDEX索引名ON表名(欄位1,[欄位2,……>);
ALTERINDEX索引名REBUILD;

一個表的索引最好不要超過三個(特殊的大表除外),最好用單欄位索引,結合SQL語句的分析執行情況,
也可以建立多欄位的複合式索引和基於函數的索引

ORACLE8.1.7字串可以索引的最大長度為1578單位元組
ORACLE8.0.6字串可以索引的最大長度為758單位元組

2.視圖(VIEW)

CREATEVIEW視圖名ASSELECT….FROM…..;
ALTERVIEW視圖名COMPILE;

視圖僅是一個SQL查詢語句,它可以把表之間複雜的關係簡潔化.

3.同義字(SYNONMY)
CREATESYNONYM同義字名FOR表名;
CREATESYNONYM同義字名FOR表名@資料庫連結名;

4.資料庫連結(DATABASELINK)
CREATEDATABASELINK資料庫連結名CONNECTTO使用者名稱IDENTIFIEDBY密碼USING‘資料庫連接字串’;

資料庫連接字串可以用NET8EASYCONFIG或者直接修改TNSNAMES.ORA裡定義.

資料庫參數global_name=true時要求資料庫連結名稱跟遠端資料庫名稱一樣

資料庫全域名稱可以用以下命令查出
SELECT*FROMGLOBAL_NAME;

查詢遠端資料庫裡的表
SELECT……FROM表名@資料庫連結名;

五.許可權管理(DCL)語句

1.GRANT 賦於許可權
常用的系統許可權集合有以下三個:
CONNECT(基本的串連),RESOURCE(程式開發),DBA(資料庫管理)
常用的資料對象許可權有以下五個:
ALL ON資料對象名, SELECTON資料對象名, UPDATEON資料對象名,
DELETE ON資料對象名,INSERTON資料對象名,ALTERON資料對象名

GRANTCONNECT,RESOURCETO使用者名稱;
GRANTSELECTON表名TO使用者名稱;
GRANTSELECT,INSERT,DELETEON表名TO使用者名稱1,使用者名稱2;

2.REVOKE回收許可權

REVOKECONNECT,RESOURCEFROM使用者名稱;
REVOKESELECTON表名FROM使用者名稱;
REVOKESELECT,INSERT,DELETEON表名FROM使用者名稱1,使用者名稱2;