ORACLE 常用的SQL語法和資料物件

2001-08
餘楓編
.資料控制語句 (DML) 部分

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

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

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

日期欄位的欄位值可以用當前資料庫的系統時間SYSDATE, 精確到秒

或者用字串轉換成日期型函數TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)
TO_DATE()
還有很多種日期格式, 可以參看
ORACLE DOC.
-- 小時:分鐘: 的格式
YYYY-MM-DD HH24:MI:SS

INSERT
時最大可操作的字串長度小於等於4000個單字節, 如果要插入更長的字串, 請考慮欄位用CLOB類型
,
方法借用ORACLE裏自帶的DBMS_LOB套裝程式
.

INSERT
時如果要用到從1開始自動增長的序列號, 應該先建立一個序列號

CREATE SEQUENCE
序列號的名稱 (最好是表名+序列號標記) INCREMENT BY 1 START WITH 1
MAXVALUE 99999 CYCLE NOCACHE;
其中最大的值按欄位的長度來定, 如果定義的自動增長的序列號 NUMBER(6) , 最大值為
999999
INSERT
語句插入這個欄位值為: 序列號的名稱
.NEXTVAL

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

DELETE FROM
表名 WHERE 條件
;

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

如果確實要刪除一個大表裏的全部記錄, 可以用 TRUNCATE 命令, 它可以釋放佔用的資料塊表空間

TRUNCATE TABLE
表名;
此操作不可回退
.

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
日期類型

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

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

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


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

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

改變表的名稱

ALTER TABLE
表名1 TO 表名2;

在表的後面增加一個欄位

ALTER TABLE
表名 ADD 欄位名 欄位名描述;

修改表裏欄位的定義描述

ALTER TABLE
表名 MODIFY欄位名 欄位名描述;

給表裏的欄位加上約束條件

ALTER TABLE
表名 ADD CONSTRAINT 約束名 PRIMARY KEY (欄位名);
ALTER TABLE
表名 ADD CONSTRAINT 約束名 UNIQUE (欄位名
);

把表放在或取出資料庫的記憶體區

ALTER TABLE
表名 CACHE;
ALTER TABLE
表名
NOCACHE;

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

刪除表和它所有的約束條件

DROP TABLE
表名 CASCADE CONSTRAINTS;

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

TRUNCATE
表名
;

.查詢語句 (SELECT) 部分


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

欄位名可以帶入函數

例如: COUNT(*), MIN(欄位名), MAX(欄位名), AVG(欄位名), DISTINCT(欄位名),
TO_CHAR(DATE
欄位名
,'YYYY-MM-DD HH24:MI:SS')

NVL(EXPR1, EXPR2)
函數

解釋:
IF EXPR1=NULL
RETURN EXPR2
ELSE
RETURN EXPR1

DECODE(AA
V1R1V2R2....)函數

解釋:
IF AA=V1 THEN RETURN R1
IF AA=V2 THEN RETURN R2
..…
ELSE
RETURN NULL

LPAD(char1,n,char2)
函數

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


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

查詢語句可以嵌套

例如: SELECT …… FROM
(SELECT …… FROM
表名1, [表名2, ……] WHERE 條件) WHERE 條件
2;

兩個查詢語句的結果可以做集合操作

例如: 並集UNION(去掉重複記錄), 並集UNION ALL(不去掉重複記錄), 差集MINUS, 交集INTERSECT

分組查詢

SELECT
欄位名1, 欄位名2, …… FROM

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 Aspromise 的頭像
    Aspromise

    Aspromise

    Aspromise 發表在 痞客邦 留言(0) 人氣()