oracle基礎(chǔ)教程鸯匹，看這一篇就夠了~

轉(zhuǎn)自：http://www.blogjava.net/kiant/articles/234781.html

Oracle 筆記（四）雾消、SQL 幾個要點

附錄：

1灾搏、SQL 簡介

2、SQL 操作符

3立润、Oracle 常用數(shù)據(jù)類型

4狂窑、Oracle 函數(shù)

5、[轉(zhuǎn)] Oracle 常用SQL語法

字符串函數(shù)

LENGTH() 字符長度

LENTTHB() 字節(jié)長度桑腮；一個漢字內(nèi)存中占用 2字節(jié)

LTRIM泉哈、RTRIM、TRIM

截串

SUBSTR(表達式破讨，位置丛晦，長度)

Oracle 無左右取串函數(shù)，但可以使用變通方式完成提陶。

左取串： SUBSTR('abcdefg', 1, 3)

右取串： SUBSTR('abcedfg', LENGTH('abcdefg')-3+1, 3)

時間函數(shù)

sysdate烫沙、current_day

設(shè)置時間格式： ALERT SESSION SET NLS_DATE_FORMAT = 'dd-mon-yyyy HH:mi:ss'

求時間： NEXT_DAY(sysdate, '星期三')

轉(zhuǎn)換函數(shù)

TO_CHAR(sysdate, 'yyyy-mm-dd hh24:mi:ss')

TO_DATE('12-3月-04')

TO_NUMBER('333') 必須是能轉(zhuǎn)換

TO_TIMESTAMP('2007-10-10 00:00:00.0', 'yyyy-mm-dd hh24:mi:ssxff') 轉(zhuǎn)換為時間戳格式

聚合函數(shù)

count(*) ：查詢表行數(shù)

count(column) :查詢列行數(shù)，會忽略空值隙笆，注意

ps.聚合函數(shù)不能做為 where 里查詢條件出現(xiàn)（因為聚合是對所有查詢結(jié)果的運算锌蓄？）

其他函數(shù)

USER：當(dāng)前用戶

SUM(DECODE(SEX, '男', 1, 0)) 篩選出行被為男的記錄并加1

SUM(DECODE(SEX, '女', 1, 0)) 篩選出行被為女的記錄并加1

NVL(a2, '非輸入') 布爾值判斷，利用系統(tǒng)對空值進行處理

SELECT DISTINCT a1 FROM aa

表連接

內(nèi)連接：查詢時撑柔，把能夠公共匹配的數(shù)據(jù)完全查詢出來瘸爽。

FROM e, d WHERE e.id = d.id

標(biāo)準(zhǔn)： FROM e JOIN d ON e.id = d.id

外連接：不完全匹配

左連接： FROM e JOIN d ON e.id = d.id(+)

左邊數(shù)據(jù)全部顯示，右邊匹配不上的部分用空值代替

右連接： FROM e JOIN d ON e.id(+) = d.id

（同理左連接）

子查詢

無關(guān)子查詢

相關(guān)子查詢

EXISTS()：根據(jù)子查詢返回是否存在數(shù)據(jù)來決定父查詢铅忿。

UNION：將多個查詢出來的信息行整合成一個結(jié)果集剪决。

SELECT eid, ename FROM e

UNION

SELECT id, name FROM d

ps.UNION 查詢出來的重復(fù)記錄不會顯示，UNION ALL 則顯示全部（包括重復(fù)的）。

INTERSECT: 返回查詢出來信息行的交集昼捍，Oracle 獨有。

利用查詢結(jié)果批量更新：

INSERT INTO e(eid, ename) SELECT id, name FROM d

或者利用查詢結(jié)果創(chuàng)建新表：

CREATE TABLE ttt AS ttt (SELECT * FROM e)

附加：

SQL 簡介

SQL （Structured Query Language肢扯，結(jié)構(gòu)化查詢語言）支持如下類別命令：

數(shù)據(jù)定義語言：CREATE（創(chuàng)建）妒茬、ALTER（更改）、TRUNCATE（截斷）蔚晨、DROP（刪除）命令乍钻。

數(shù)據(jù)操縱語言：INSERT（插入）、SELECT（選擇）铭腕、DELETE（刪除）银择、UPDATE（修改）命令。

事務(wù)控制語言：COMMIT（提交）累舷、SAVEPOINT（保存點）浩考、ROLLBACK（回滾）命令。

數(shù)據(jù)控制語言：GRANT（授予）被盈、REVOKE（回收）命令析孽。

特點：

1、非過程語言只怎，它同時可以訪問多條記錄袜瞬。

2、所有關(guān)系型數(shù)據(jù)庫的通用型語言身堡，可移植性強邓尤。

3、對于數(shù)據(jù)和對象的操作簡單贴谎。

數(shù)據(jù)定義語言

用于改變數(shù)據(jù)庫結(jié)構(gòu)汞扎，包括創(chuàng)建、修改和刪除數(shù)據(jù)庫對象擅这。

1佩捞、CREATE TABLE 創(chuàng)建表

CREATE TABLE [schema.]table

(columname datetype [, .]);

.表名的最大長度為30個字符；

.表名首字母為字母蕾哟，可以用下劃線一忱、數(shù)字和字母，但不能使用空格和單引號谭确；

.同一用戶模式下的不同表不能有相同的名稱帘营；

.表名、列名逐哈、用戶名芬迄、和其他對象名不區(qū)分大小寫，系統(tǒng)會自動轉(zhuǎn)換成大寫昂秃。

2禀梳、ALTER TABLE 修改表

ALTER TABLE?

MODIFY (column definition);

ADD (column definition);

DROP COLUMN column;

3杜窄、TRUNCATE TABLE 截取表

TRUNCATE TABLE?；

快速刪除記錄并釋放空間算途，不使用事務(wù)處理塞耕，無法回滾，效率高嘴瓤。

DESC?查看表結(jié)構(gòu)

4扫外、DROP TABLE 刪除表

DROP TABLE

數(shù)據(jù)操縱語言

DISTINCT 防止選擇重復(fù)的行。

事務(wù)控制語言

COMMIT 提交并結(jié)束事務(wù)處理廓脆。

SAVEPOINT 保存點筛谚，將很長的事務(wù)處理劃分為較小的部分，用于標(biāo)記事務(wù)中可以應(yīng)用回滾的點停忿。

ROLLBACK 用來撤銷在當(dāng)前的事務(wù)中已完成的操作驾讲。可以回滾整個事務(wù)處理席赂；也可以將事務(wù)回滾到某個保存點蝎毡。

UPDATE xxx;

SAVEPOINT mark1;

DELETE FROM xxx;

SAVEPOINT mark2;

ROLLBACK TO SAVEPOINT mark1;

COMMIT;

數(shù)據(jù)控制語言

為用戶提供權(quán)限控制命令。

授予對象權(quán)限

GRANT SELECT,UPDATE ON order_master

TO MARTIN;

取消對象權(quán)限

REVOKE SELECT,UPDATE ON order_master

FROM MARTIN;

SQL 操作符

算術(shù)操作符

算術(shù)表達式有 NUMBER 數(shù)據(jù)類型的列名氧枣、數(shù)值常量和連接它們的算術(shù)操作符組成沐兵。（+ - * /）

比較操作符

用于比較兩個表達式的值。

=便监、!=扎谎、<、>烧董、<=毁靶、>=、BETWEEN AND （檢查是否在兩個值之間）

[NOT] IN（與列表中的值匹配）

[NOT] LIKE（匹配字符模式逊移， * _ 通配符）

[NOT] IS NULL（檢查是否為空）

邏輯操作符

用于組合生成一個真或假的結(jié)果预吆。AND OR NOT

集合操作符

集合操作符將兩個查詢的結(jié)果組合成一個結(jié)果集合。

UNION（聯(lián)合）返回兩個查詢選定不重復(fù)的行胳泉。(刪除重復(fù)的行)

UNION ALL（聯(lián)合所有）合并兩個查詢選定的所有行拐叉，包括重復(fù)的行。

INTERSECT（交集）只返回兩個查詢都有的行扇商。

MINUS（減集）在第一個查詢結(jié)果中排除第二個查詢結(jié)果中出現(xiàn)的行凤瘦。（第一 -- 第二）

使用集合操作符連接起來的 SELECT 語句中的列應(yīng)遵循以下規(guī)則：

.通過集合操作連接的各個查詢相同列數(shù)，匹配列的數(shù)據(jù)類型案铺；

.這種查詢不應(yīng)含有 LONG 類型的列蔬芥；

.列標(biāo)題來自第一個 SELECT 語句。

SELECT orderno FROM order_master

UNION

SELECT orderno FROM order_detail;

連接操作符（||）

用于將兩個或者多個字符串合并成一個字符串，或者將一個字符串與一個數(shù)值合并在一起笔诵。

SELECT ('供應(yīng)商'|| venname || '的地址是' || venaddress)

FROM vendor_master

Oracle 常用數(shù)據(jù)類型

1返吻、字符數(shù)據(jù)類型

CHAR 固定長度字符串長度 1～2000個字節(jié)，未指定則默認為 1字節(jié)

VARCHAR2 可變長度字符串長度 1～4000個字節(jié)乎婿，定義時必須指定大小

LONG 可變長度字符串最多能存儲 2GB测僵，存儲超過 VARCHAR2 的長文本信息

ps.一個表中只有一列為 LONG 數(shù)據(jù)類型，

.LONG 列不能建立索引次酌，

.存儲過程不能接受 LONG 數(shù)據(jù)類型的參數(shù)

2恨课、數(shù)值數(shù)據(jù)類型

NUMBER 數(shù)據(jù)類型可以存儲正數(shù)舆乔、負數(shù)岳服、零、定點數(shù)(不帶小數(shù)點的希俩？)和精度為38為的浮點數(shù)吊宋。

格式： NUMBER [(precision 精度，數(shù)字總位數(shù) 1～38間

, scale 范圍颜武，小數(shù)點右邊的位數(shù) -84～127間)]

3璃搜、時期時間數(shù)據(jù)類型

DATE 數(shù)據(jù)類型，用于存儲表中日期和時間數(shù)據(jù)鳞上。SYSDATE 函數(shù)功能就是返回當(dāng)前的日期和時間这吻。

TIMESTAMP 數(shù)據(jù)類型，存儲時期篙议、時間和時區(qū)信息唾糯。SYSTIMEATAMP 功能就是返回當(dāng)前日期蛔垢、時間和時區(qū)腊徙。

4、二進制數(shù)據(jù)類型

RAW 二進制數(shù)據(jù)或字節(jié)串長度 1～2000 字節(jié)和橙，定義時應(yīng)指定大小这难，可建索引

LONG RAW 可變長度的二進制數(shù)據(jù) 最大能存儲 2GB舟误，限制等同于 LONG 數(shù)據(jù)類型

5、LOB 數(shù)據(jù)類型

“大對象”數(shù)據(jù)類型姻乓，最多可存儲多達 4GB的信息嵌溢。LOB 可以是外部的，也可以是內(nèi)部的蹋岩，取決于相對于數(shù)據(jù)庫位置堵腹。

CLOB Character LOB 存儲大量的字符數(shù)據(jù)

BLOB Binary LOB 存儲大量的二進制對象（多媒體對象等）

BFILE Binary FIle 能夠?qū)⒍M制文件存儲在數(shù)據(jù)庫外部的操作系統(tǒng)文件中

BFILE 存儲一個 BFILE 定位器，它指向位于服務(wù)器文件系統(tǒng)上的二進制文件星澳。

ps.一個表中可以有多個 LOB 列疚顷，每個 LOB 列可以是不同的 LOB 類型。

6、偽列

Oracle 中的一個表列腿堤，但實際上未存儲表中阀坏。可以從表中查詢笆檀，但是不能插入忌堂，更新或者刪除。

ROWID 返回行記錄的行地址酗洒，通常情況下士修，ROWID 值可以唯一地標(biāo)識數(shù)據(jù)庫中的一行。

作用： .能最快形式訪問表中的一行樱衷。

.能顯示表中的行是如何存儲的棋嘲。

.可以作為表中行的唯一標(biāo)識。

例：SELECT ROWID, * FROM EMP WHERE empno='7900';

ROWNUM 返回一個數(shù)值單表行的次序矩桂，第一行為1沸移，第二行為2.

通過使用 ROWNUM 用戶可以限制查詢返回的行數(shù)(或者分頁？)

例：SELECT * FROM EMP WHERE ROWNUM <= 10;

Oracle 函數(shù)

函數(shù)接受一個或多個參數(shù)并返回一個值侄榴。

單行函數(shù)

也稱標(biāo)量函數(shù)雹锣，對于從表中查詢的每一行，該函數(shù)都返回一個值癞蚕。

單行函數(shù)出現(xiàn)在 SLEECT / WHERE 子句中蕊爵。

1、日期函數(shù)

對日期值進行運算桦山，根據(jù)用途產(chǎn)生日期/數(shù)值類型的結(jié)果攒射。

ADD_MONTHS(d, n) 返回指定日期加上月數(shù)后的日期值

MONTHS_BETWEEN(d1, d2) 返回兩個日期間的月數(shù)

LAST_DAY(d) 返回指定日期當(dāng)前的最后一天的日期值

RONUD(d,[fmt]) 返回指定日期四舍五入格式(YEAR、MONTH度苔、DAY)后的日期值

NEXT_DAY(d,day) 返回指定日期下一個星期幾的日期值

TRUNC(d,[fnt]) 返回指定日期截斷為格式后的日期值

EXTRACT(fmt FROM d) 返回指定日期提取的格式的值

2匆篓、字符函數(shù)

字符函數(shù)接受字符輸入，并返回字符或數(shù)值寇窑。

INITCAP(char) 首字母大寫

LOWER(char) 轉(zhuǎn)換為小寫

UPPER(char) 轉(zhuǎn)換為大寫

LTRIM(char, set) 左裁切

RTRIM(char, set) 右裁切

TRANSLATE(char, from, to) 按字母翻譯

REPLACE(char, search_str, replace_str) 字符串替換

INSTR(char, substr[,pos]) 查找子串位置

SUBSTR(char, pos, len) 取子字符串

CONCAT(char1, char2) 連接字符串

CHR(ascii) 根據(jù) ASCII 碼返回對應(yīng)字符串

LPAD / RPAD 左 / 右填充

LPAD ('function', 15 , '=') 返回 '=======function'

TRAM 開頭或結(jié)尾(或開頭和結(jié)尾)裁剪特定的字符鸦概，默認裁剪空格。

TRIM ([LEADING | TRAILING] trim_char)

LENGTH(char) 返回字符串長度

DECODE 逐個值進行字符串替換

DECODE (expr, search1, result1, search2, result2, [ ,default])

DECODE (ostalus, 'p', '準(zhǔn)備處理', 'c', '已完成')

3甩骏、數(shù)字函數(shù)

數(shù)字函數(shù)接受數(shù)字輸入并返回數(shù)值作為輸出結(jié)果窗市。

ABS(n) 取絕對值

CEIL(n) 向上取值

FLOOR(n) 向下去整

SIN(n) 正弦值

COS(n) 余弦值

POWER(m, n) 指數(shù)函數(shù)

SQRT(n) 平方根

MOD(m, n) 取余

ROUND(m, n) 小數(shù)點后精度四舍五入

TRUNC(m, n) 小數(shù)點后精度截斷

4、轉(zhuǎn)換函數(shù)

轉(zhuǎn)換函數(shù)將一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型饮笛。

TO_CHAR (d|n, [,fmt]) 格式化日期 / 數(shù)值

TO_DATE (char [,fmt]) 將 fmt模型格式的字符串轉(zhuǎn)換為日期型

TO_NUMBER (char) 將包含數(shù)字的的字符串轉(zhuǎn)換為數(shù)值型

5咨察、其他函數(shù)

NVL (exp, exp2) 如果 exp 為空返回 exp2；如果非空返回 exp

NVL2 (exp, exp2, exp3) 如果 exp 為空返回 exp3福青；如果非空返回 exp2

NULLIF (exp1, exp2) 比較兩表達式摄狱，相等返回空值脓诡，不等則返回 exp1

分組函數(shù) / 聚合函數(shù)

分組函數(shù)基于一組行返回結(jié)果，即為每一組行返回單個值媒役。

AVG (columname) 返回指定列的平均值

MAX (columname) 返回指定列的最大值

MIN (columname) 返回指定列的最小值

SUM (columname) 返回指定列的總值

COUNT

COUNT (*) 統(tǒng)計所有行個數(shù)祝谚，包括重復(fù)行和空值得行

COUNT (columname) 統(tǒng)計指定列非空值的個行數(shù)

COUNT (DISTINCR columname) 統(tǒng)計指定列中非重復(fù)，非空值得行個數(shù)

GROUP BY 子句

用于將信息表劃分為組酣衷，對查詢結(jié)果按組進行聚合運算交惯，為每組返回一個結(jié)果。

HAVING 子句

用來指定 GROUP BY 子句的檢索條件穿仪。

分析函數(shù)

分析函數(shù)根據(jù)一組行來計算聚合值席爽。這些函數(shù)通常用來完成對聚集的累積排名、移動平均數(shù)和報表計算啊片。

分析函數(shù)與聚合函數(shù)不同的是他們?yōu)槊拷M記錄返回多個行只锻。

ROW_NUMBER () OVER ([PARTITION BY colum] ORDER BY colum)

為有序組中的每一行返回一個唯一的排序值，序號由 ORDER BY 子句指定钠龙，從 1 開始炬藤，即使具有相等的值御铃，排位也不同碴里。

PARTITION BY colum 按列值進行區(qū)分，各分組內(nèi)在進行排序上真。

RANK () OVER ([PARTITION BY colum] ORDER BY colum)

計算一個值在一個組中的地位咬腋，由 1 開頭，具有相等值得行排位相同睡互，序數(shù)隨后跳躍相應(yīng)的數(shù)值根竿。

DENSE_RANK () OVER ([PARTITION BY colum] ORDER BY colum)

計算一個值在一個組中的地位，由 1 開頭就珠，具有相等值得行排位相同寇壳，并且排位是連續(xù)的。

5妻怎、[轉(zhuǎn)] Oracle 常用SQL語法和數(shù)據(jù)對象

=============================================================================================

一.數(shù)據(jù)控制語句 (DML) 部分

=============================================================================================

1.INSERT (往數(shù)據(jù)表里插入記錄的語句)

INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……);

INSERT INTO 表名(字段名1, 字段名2, ……) SELECT 字段名1, 字段名2, …… FROM 另外的表名;

字符串類型的字段值必須用單引號括起來, 例如: ’GOOD DAY’

如果字段值里包含單引號’ 需要進行字符串轉(zhuǎn)換, 我們把它替換成兩個單引號''.

字符串類型的字段值超過定義的長度會出錯, 最好在插入前進行長度校驗.

日期字段的字段值可以用當(dāng)前數(shù)據(jù)庫的系統(tǒng)時間SYSDATE, 精確到秒

或者用字符串轉(zhuǎn)換成日期型函數(shù)TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)

TO_DATE()還有很多種日期格式, 可以參看ORACLE DOC.

年-月-日小時:分鐘:秒的格式Y(jié)YYY-MM-DD HH24:MI:SS

INSERT時最大可操作的字符串長度小于等于4000個單字節(jié), 如果要插入更長的字符串, 請考慮字段用CLOB類型,

方法借用ORACLE里自帶的DBMS_LOB程序包.

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

CREATE SEQUENCE 序列號的名稱 (最好是表名+序列號標(biāo)記) INCREMENT BY 1 START WITH 1

MAXVALUE 99999 CYCLE NOCACHE;

其中最大的值按字段的長度來定, 如果定義的自動增長的序列號 NUMBER(6) , 最大值為999999

INSERT 語句插入這個字段值為: 序列號的名稱.NEXTVAL

2.DELETE (刪除數(shù)據(jù)表里記錄的語句)

DELETE FROM表名 WHERE 條件;

注意：刪除記錄并不能釋放ORACLE里被占用的數(shù)據(jù)塊表空間. 它只把那些被刪除的數(shù)據(jù)塊標(biāo)成unused.

如果確實要刪除一個大表里的全部記錄, 可以用 TRUNCATE 命令, 它可以釋放占用的數(shù)據(jù)塊表空間

TRUNCATE TABLE 表名;

此操作不可回退.

3.UPDATE (修改數(shù)據(jù)表里記錄的語句)

UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 條件;

如果修改的值N沒有賦值或定義時, 將把原來的記錄內(nèi)容清為NULL, 最好在修改前進行非空校驗;

值N超過定義的長度會出錯, 最好在插入前進行長度校驗..

注意事項:

A. 以上SQL語句對表都加上了行級鎖,

確認完成后, 必須加上事物處理結(jié)束的命令 COMMIT 才能正式生效,

否則改變不一定寫入數(shù)據(jù)庫里.

如果想撤回這些操作, 可以用命令 ROLLBACK 復(fù)原.

B. 在運行INSERT, DELETE 和 UPDATE 語句前最好估算一下可能操作的記錄范圍,

應(yīng)該把它限定在較小 (一萬條記錄) 范圍內(nèi),. 否則ORACLE處理這個事物用到很大的回退段.

程序響應(yīng)慢甚至失去響應(yīng). 如果記錄數(shù)上十萬以上這些操作, 可以把這些SQL語句分段分次完成,

其間加上COMMIT 確認事物處理.

=============================================================================================

二.數(shù)據(jù)定義 (DDL) 部分

=============================================================================================

1.CREATE (創(chuàng)建表, 索引, 視圖, 同義詞, 過程, 函數(shù), 數(shù)據(jù)庫鏈接等)

ORACLE常用的字段類型有

CHAR 固定長度的字符串

VARCHAR2 可變長度的字符串

NUMBER(M,N) 數(shù)字型M是位數(shù)總長度, N是小數(shù)的長度

DATE 日期類型

創(chuàng)建表時要把較小的不為空的字段放在前面, 可能為空的字段放在后面

創(chuàng)建表時可以用中文的字段名, 但最好還是用英文的字段名

創(chuàng)建表時可以給字段加上默認值, 例如 DEFAULT SYSDATE

這樣每次插入和修改時, 不用程序操作這個字段都能得到動作的時間

創(chuàng)建表時可以給字段加上約束條件

例如不允許重復(fù) UNIQUE, 關(guān)鍵字 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 (字段名);

把表放在或取出數(shù)據(jù)庫的內(nèi)存區(qū)

ALTER TABLE 表名 CACHE;

ALTER TABLE 表名 NOCACHE;

3.DROP (刪除表, 索引, 視圖, 同義詞, 過程, 函數(shù), 數(shù)據(jù)庫鏈接等)

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

DROP TABLE 表名 CASCADE CONSTRAINTS;

4.TRUNCATE (清空表里的所有記錄, 保留表的結(jié)構(gòu))

TRUNCATE 表名;

=============================================================================================

三.查詢語句 (SELECT) 部分

=============================================================================================

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 條件;

字段名可以帶入函數(shù)

例如: COUNT(), MIN(字段名), MAX(字段名), AVG(字段名), DISTINCT(字段名),

TO_CHAR(DATE字段名,'YYYY-MM-DD HH24:MI:SS')

---------------------------------------------------------------

NVL(EXPR1, EXPR2)函數(shù)

解釋:

IF EXPR1=NULL

RETURN EXPR2

ELSE

RETURN EXPR1

---------------------------------------------------------------

DECODE(AA﹐V1﹐R1﹐V2﹐R2.)函數(shù)

解釋:

IF AA=V1 THEN RETURN R1

IF AA=V2 THEN RETURN R2

..…

ELSE

RETURN NULL

---------------------------------------------------------------

LPAD(char1,n,char2)函數(shù)

解釋:

字符char1按制定的位數(shù)n顯示壳炎，不足的位數(shù)用char2字符串替換左邊的空位

---------------------------------------------------------------

字段名之間可以進行算術(shù)運算

例如: (字段名1

字段名1)/3

---------------------------------------------------------------

查詢語句可以嵌套

例如: SELECT …… FROM

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

---------------------------------------------------------------

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

例如: 并集 UNION (去掉重復(fù)記錄),

并集 UNION ALL(不去掉重復(fù)記錄),

差集 MINUS,

交集 INTERSECT

分組查詢

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] GROUP BY字段名1

[HAVING 條件] ;

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

SELECT 字段名1, 字段名2, ……

FROM 表名1, [表名2, ……]

WHERE 表名1.字段名 = 表名2. 字段名

[ AND ……] ;

SELECT字段名1, 字段名2, ……

FROM 表名1, [表名2, ……]

WHERE 表名1.字段名 = 表名2. 字段名(+)

[ AND ……] ;

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

查詢結(jié)果集的排序操作, 默認的排序是升序ASC, 降序是DESC

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……]

ORDER BY字段名1, 字段名2 DESC;

----------------------------------------------------------

字符串模糊比較的方法

INSTR(字段名, ‘字符串’)>0

字段名 LIKE ‘字符串%’ [‘%字符串%’]

每個表都有一個隱含的字段ROWID, 它標(biāo)記著記錄的唯一性.

=============================================================================================

四.ORACLE里常用的數(shù)據(jù)對象 (SCHEMA)

=============================================================================================

1.索引 (INDEX)

CREATE INDEX 索引名ON 表名 ( 字段1, [字段2, ……] );

ALTER INDEX 索引名 REBUILD;

一個表的索引最好不要超過三個 (特殊的大表除外), 最好用單字段索引, 結(jié)合SQL語句的分析執(zhí)行情況,

也可以建立多字段的組合索引和基于函數(shù)的索引

ORACLE8.1.7字符串可以索引的最大長度為1578 單字節(jié)

ORACLE8.0.6字符串可以索引的最大長度為758 單字節(jié)

ORACLE DOC上說字符串最大可以建索引的長度約是:數(shù)據(jù)塊的大小(db_block_size)*40%

2.視圖 (VIEW)

CREATE VIEW 視圖名AS SELECT …. FROM …..;

ALTER VIEW視圖名 COMPILE;

視圖僅是一個SQL查詢語句, 它可以把表之間復(fù)雜的關(guān)系簡潔化.

3.同義詞 (SYNONMY)

CREATE SYNONYM同義詞名FOR 表名;

CREATE SYNONYM同義詞名FOR?表名@數(shù)據(jù)庫鏈接名;

4.數(shù)據(jù)庫鏈接 (DATABASE LINK)

CREATE DATABASE LINK數(shù)據(jù)庫鏈接名CONNECT TO 用戶名 IDENTIFIED BY 密碼 USING ‘?dāng)?shù)據(jù)庫連接字符串’;

數(shù)據(jù)庫連接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定義.

數(shù)據(jù)庫參數(shù)global_name=true時要求數(shù)據(jù)庫鏈接名稱跟遠端數(shù)據(jù)庫名稱一樣

數(shù)據(jù)庫全局名稱可以用以下命令查出

SELECT * FROM GLOBAL_NAME;

查詢遠端數(shù)據(jù)庫里的表

SELECT …… FROM?表名@數(shù)據(jù)庫鏈接名;

=============================================================================================

五.權(quán)限管理 (DCL) 語句

=============================================================================================

1.GRANT 賦于權(quán)限

常用的系統(tǒng)權(quán)限集合有以下三個:

CONNECT(基本的連接), RESOURCE(程序開發(fā)), DBA(數(shù)據(jù)庫管理)

常用的數(shù)據(jù)對象權(quán)限有以下五個:

ALL ON 數(shù)據(jù)對象名, SELECT ON 數(shù)據(jù)對象名, UPDATE ON 數(shù)據(jù)對象名,

DELETE ON 數(shù)據(jù)對象名, INSERT ON 數(shù)據(jù)對象名, ALTER ON 數(shù)據(jù)對象名

GRANT CONNECT, RESOURCE TO 用戶名;

GRANT SELECT ON 表名 TO 用戶名;

GRANT SELECT, INSERT, DELETE ON表名 TO 用戶名1, 用戶名2;

2.REVOKE 回收權(quán)限

REVOKE CONNECT, RESOURCE FROM 用戶名;

REVOKE SELECT ON 表名 FROM 用戶名;

REVOKE SELECT, INSERT, DELETE ON表名 FROM 用戶名1, 用戶名2;

=============================================================================================

*********************************************************************************************

=============================================================================================

二.數(shù)據(jù)定義 (DDL) 部分

=============================================================================================

1.CREATE (創(chuàng)建表, 索引, 視圖, 同義詞, 過程, 函數(shù), 數(shù)據(jù)庫鏈接等)

ORACLE常用的字段類型有

CHAR 固定長度的字符串

VARCHAR2 可變長度的字符串

NUMBER(M,N) 數(shù)字型M是位數(shù)總長度, N是小數(shù)的長度

DATE 日期類型

創(chuàng)建表時要把較小的不為空的字段放在前面, 可能為空的字段放在后面

創(chuàng)建表時可以用中文的字段名, 但最好還是用英文的字段名

創(chuàng)建表時可以給字段加上默認值, 例如 DEFAULT SYSDATE

這樣每次插入和修改時, 不用程序操作這個字段都能得到動作的時間

創(chuàng)建表時可以給字段加上約束條件

例如不允許重復(fù) UNIQUE, 關(guān)鍵字 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 (字段名);

把表放在或取出數(shù)據(jù)庫的內(nèi)存區(qū)

ALTER TABLE 表名 CACHE;

ALTER TABLE 表名 NOCACHE;

3.DROP (刪除表, 索引, 視圖, 同義詞, 過程, 函數(shù), 數(shù)據(jù)庫鏈接等)

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

DROP TABLE 表名 CASCADE CONSTRAINTS;

4.TRUNCATE (清空表里的所有記錄, 保留表的結(jié)構(gòu))

TRUNCATE 表名;

=============================================================================================

*********************************************************************************************

=============================================================================================

================================================================================

三.查詢語句 (SELECT) 部分

================================================================================

SELECT 字段名1, 字段名2, ……

FROM 表名1, [表名2, ……]

WHERE 條件;

--------------------------

字段名可以帶入函數(shù)

例如: COUNT(), MIN(字段名), MAX(字段名), AVG(字段名), DISTINCT(字段名),

TO_CHAR(DATE字段名,'YYYY-MM-DD HH24:MI:SS')

------------------------------

NVL(EXPR1, EXPR2)函數(shù)

解釋:

IF EXPR1=NULL

RETURN EXPR2

ELSE

RETURN EXPR1

------------------------------

DECODE(AA﹐V1﹐R1﹐V2﹐R2.)函數(shù)

解釋:

IF AA=V1 THEN RETURN R1

IF AA=V2 THEN RETURN R2

..…

ELSE

RETURN NULL

------------------------------

LPAD(char1,n,char2)函數(shù)

解釋:

字符char1按制定的位數(shù)n顯示，不足的位數(shù)用char2字符串替換左邊的空位

------------------------------

字段名之間可以進行算術(shù)運算

例如: (字段名1

字段名1)/3

===============================================================

查詢語句可以嵌套

---------------------

例如:

SELECT …… FROM

(

SELECT ……

FROM表名1, [表名2, ……]

WHERE 條件1

)

WHERE 條件2;

===============================================================

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

--------------------------------

例如: 并集 UNION (去掉重復(fù)記錄),

并集 UNION ALL(不去掉重復(fù)記錄),

差集 MINUS,

交集 INTERSECT

===============================================================

分組查詢

------------

SELECT 字段名1, 字段名2, ……

FROM 表名1, 表名2, ……

GROUP BY 字段名1

HAVING 條件

===============================================================

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

-----------------------

SELECT 字段名1, 字段名2, ……

FROM 表名1, [表名2, ……]

WHERE 表名1.字段名 = 表名2. 字段名

[ AND ……] ;

SELECT 字段名1, 字段名2, ……

FROM 表名1, [表名2, ……]

WHERE 表名1.字段名 = 表名2. 字段名(+)

[ AND ……] ;

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

===============================================================

查詢結(jié)果集的排序操作, 默認的排序是升序ASC, 降序是DESC

----------------------------------------------------

SELECT 字段名1, 字段名2, ……

FROM 表名1, [表名2, ……]

ORDER BY 字段名1, 字段名2 DESC;

===============================================================

字符串模糊比較的方法

----------------------

INSTR(字段名, ‘字符串’)>0

字段名 LIKE ‘字符串%’ [‘%字符串%’]

每個表都有一個隱含的字段ROWID, 它標(biāo)記著記錄的唯一性.

=============================================================================================

*********************************************************************************************

=============================================================================================

四.ORACLE里常用的數(shù)據(jù)對象 (SCHEMA)

=============================================================================================

1.索引 (INDEX)

CREATE INDEX 索引名ON 表名 ( 字段1, [字段2, ……] );

ALTER INDEX 索引名 REBUILD;

一個表的索引最好不要超過三個 (特殊的大表除外), 最好用單字段索引, 結(jié)合SQL語句的分析執(zhí)行情況,

也可以建立多字段的組合索引和基于函數(shù)的索引

ORACLE8.1.7字符串可以索引的最大長度為1578 單字節(jié)

ORACLE8.0.6字符串可以索引的最大長度為758 單字節(jié)

ORACLE DOC上說字符串最大可以建索引的長度約是:數(shù)據(jù)塊的大小(db_block_size)*40%

---------------------------------------------------------------------------------------

2.視圖 (VIEW)

CREATE VIEW 視圖名AS SELECT …. FROM …..;

ALTER VIEW視圖名 COMPILE;

視圖僅是一個SQL查詢語句, 它可以把表之間復(fù)雜的關(guān)系簡潔化.

3.同義詞 (SYNONMY)

CREATE SYNONYM同義詞名FOR 表名;

CREATE SYNONYM同義詞名FOR?表名@數(shù)據(jù)庫鏈接名;

4.數(shù)據(jù)庫鏈接 (DATABASE LINK)

CREATE DATABASE LINK數(shù)據(jù)庫鏈接名CONNECT TO 用戶名 IDENTIFIED BY 密碼 USING ‘?dāng)?shù)據(jù)庫連接字符串’;

數(shù)據(jù)庫連接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定義.

數(shù)據(jù)庫參數(shù)global_name=true時要求數(shù)據(jù)庫鏈接名稱跟遠端數(shù)據(jù)庫名稱一樣

數(shù)據(jù)庫全局名稱可以用以下命令查出

SELECT * FROM GLOBAL_NAME;

查詢遠端數(shù)據(jù)庫里的表

SELECT …… FROM?表名@數(shù)據(jù)庫鏈接名;

=============================================================================================

*********************************************************************************************

=============================================================================================

五.權(quán)限管理 (DCL) 語句

=============================================================================================

1.GRANT 賦于權(quán)限

常用的系統(tǒng)權(quán)限集合有以下三個:

CONNECT(基本的連接), RESOURCE(程序開發(fā)), DBA(數(shù)據(jù)庫管理)

常用的數(shù)據(jù)對象權(quán)限有以下五個:

ALL ON 數(shù)據(jù)對象名, SELECT ON 數(shù)據(jù)對象名, UPDATE ON 數(shù)據(jù)對象名,

DELETE ON 數(shù)據(jù)對象名, INSERT ON 數(shù)據(jù)對象名, ALTER ON 數(shù)據(jù)對象名

GRANT CONNECT, RESOURCE TO 用戶名;

GRANT SELECT ON 表名 TO 用戶名;

GRANT SELECT, INSERT, DELETE ON表名 TO 用戶名1, 用戶名2;

2.REVOKE 回收權(quán)限

REVOKE CONNECT, RESOURCE FROM 用戶名;

REVOKE SELECT ON 表名 FROM 用戶名;

REVOKE SELECT, INSERT, DELETE ON表名 FROM 用戶名1, 用戶名2;

=============================================================================================

*********************************************************************************************

=============================================================================================

--------------------------------------------------------------------------------

=====================================================================

使用表達式

=====================================================================

Select ename || ' 是一位 ' || job As 雇員細節(jié),

to_char(hiredate,'yyyy-mm-dd') As 雇傭時間,

sal*1.2

From emp;

=====================================================================

取消重復(fù)行 distinct

=====================================================================

Select Distinct deptno, job From emp;

=====================================================================

指定列排序 order by

=====================================================================

asc 升序逼侦，desc 降序

--------------------------------------------

Select * From emp

where sal between 1500 and 3000

Order By deptno Desc,ename;

如果使用dsitinct,排序列必須是選擇列

select distinct depton, job

from emp

order by job;

---------------------------------------------

order by 子句必須是最后一個子句

=====================================================================

============================================================

分組查詢語句

============================================================

1匿辩。組處理函數(shù)不能出現(xiàn)在 where 子句中

2。選擇列表中的列榛丢、表達式铲球，必須出現(xiàn)在 group by 子句中

3。組處理函數(shù)中可以指定 all 和 distinct

============================================================

分組函數(shù)

---------------------------------------

select avg(sal) as avg1,

avg(distinct sal) as avg2,

max(sal) as max,

min(sal) as min,

sum(sal) as sum,

count(*) as cnt1,

count(sal) as cnt2,

count(distinct sal) as cnt3

from emp

where deptno = 30;

============================================================

單列分組

---------------------------------------------

select deptno, avg(sal), max(sal) from emp

group by deptno;

select deptno, avg(sal), max(sal) from emp

group by deptno

order by avg(sal);

============================================================

多列分組

---------------------------------------------

select deptno, job, avg(sal), max(sal), from emp

group by deptno, job;

============================================================

rollup 用于生成橫向統(tǒng)計信息

Select deptno, job, Avg(sal), Max(sal) From emp

Group By rollup(deptno,job)

cube 用于生產(chǎn)縱向統(tǒng)計信息

Select deptno, job, Avg(sal), Max(sal) From emp

Group By Cube(deptno,job)

============================================================

having 子句

============================================================

select deptno, avg(sal), max(sal) from emp

group by deptno

having avg(sal) > 2000;

============================================================

===========================================================

不等連接

----------------------------

Select e.ename, e.sal, s.grade

From emp e,salgrade s

Where e.sal Between s.losal And s.hisal

And e.deptno = 30

===========================================================

自連接

---------------------------------------

Select e.ename As 雇員, p.ename As 管理員

From emp e,emp p

Where e.mgr = p.empno

And e.deptno = 30

===========================================================

合并查詢

===========================================================

union --- 兩個集合的并集晰赞，去掉重復(fù)行稼病，按第一列結(jié)構(gòu)排序

-------------------------