DDL、DML和函數(shù).ppt

1、第二章,DDL、DML和函數(shù),本章主要目標(biāo),了解 Oracle 數(shù)據(jù)類型 了解數(shù)據(jù)定義語言和數(shù)據(jù)操縱語言 了解事務(wù)控制語言和數(shù)據(jù)控制語言 掌握 SQL 操作符和 SQL 函數(shù),SQL 簡介 2-1,SQL 是 Structured Query Language（結(jié)構(gòu)化查詢語言）的首字母縮寫詞 SQL 是數(shù)據(jù)庫語言，Oracle 使用該語言存儲(chǔ)和檢索信息 表是主要的數(shù)據(jù)庫對(duì)象，用于存儲(chǔ)數(shù)據(jù),通過 SQL可以實(shí)現(xiàn)與 Oracle 服務(wù)器的通信,發(fā)送 SQL 查詢,用戶,SQL 簡介 2-2,SQL 支持下列類別的命令： 數(shù)據(jù)定義語言（DDL） 數(shù)據(jù)操縱語言（DML） 事務(wù)控制語言（TCL） 數(shù)據(jù)控

2、制語言（DCL,數(shù)據(jù)定義語言,CREATE,ALTER,DROP,數(shù)據(jù)操縱語言,INSERT,SELECT,DELETE,UPDATE,事務(wù)控制語言,COMMIT,SAVEPOINT,ROLLBACK,數(shù)據(jù)控制語言,GRANT,REVOKE,Oracle 數(shù)據(jù)類型 5-1,創(chuàng)建表時(shí)，必須為各個(gè)列指定數(shù)據(jù)類型 以下是 Oracle 數(shù)據(jù)類型的類別,數(shù)據(jù)類型,字符,數(shù)值,日期時(shí)間,RAW/LONG RAW,LOB,Oracle 數(shù)據(jù)類型5-2,字符數(shù)據(jù)類型,CHAR,VARCHAR2,LONG,當(dāng)需要固定長度的字符串時(shí)，使用 CHAR 數(shù)據(jù)類型。 CHAR 數(shù)據(jù)類型存儲(chǔ)字母數(shù)字值。 CHAR 數(shù)據(jù)

3、類型的列長度可以是 1 到 2000 個(gè)字節(jié),VARCHAR2數(shù)據(jù)類型支持可變長度字符串 VARCHAR2數(shù)據(jù)類型存儲(chǔ)字母數(shù)字值 VARCHAR2數(shù)據(jù)類型的大小在1至4000個(gè)字節(jié)范圍內(nèi),LONG 數(shù)據(jù)類型存儲(chǔ)可變長度字符數(shù)據(jù) LONG 數(shù)據(jù)類型最多能存儲(chǔ) 2GB,Oracle 數(shù)據(jù)類型 5-3,數(shù)值數(shù)據(jù)類型 可以存儲(chǔ)整數(shù)、浮點(diǎn)數(shù)和實(shí)數(shù) 最高精度為 38 位 數(shù)值數(shù)據(jù)類型的聲明語法： NUMBER ( p, s) P表示精度，S表示小數(shù)點(diǎn)的位數(shù),日期時(shí)間數(shù)據(jù)類型存儲(chǔ)日期和時(shí)間值，包括年、月、日，小時(shí)、分鐘、秒 主要的日期時(shí)間類型有： DATE - 存儲(chǔ)日期和時(shí)間部分，精確到整個(gè)的秒 TIMES

4、TAMP - 存儲(chǔ)日期、時(shí)間和時(shí)區(qū)信息，秒值精確到小數(shù)點(diǎn)后6位,Oracle 數(shù)據(jù)類型 5-4,RAW 數(shù)據(jù)類型用于存儲(chǔ)二進(jìn)制數(shù)據(jù) RAW 數(shù)據(jù)類型最多能存儲(chǔ) 2000 字節(jié) LONG RAW 數(shù)據(jù)類型用于存儲(chǔ)可變長度的二進(jìn)制數(shù)據(jù) LONG RAW 數(shù)據(jù)類型最多能存儲(chǔ) 2 GB,LOB,LOB 稱為“大對(duì)象”數(shù)據(jù)類型，可以存儲(chǔ)多達(dá) 4GB 的非結(jié)構(gòu)化信息，例如聲音剪輯和視頻文件等 LOB 數(shù)據(jù)類型允許對(duì)數(shù)據(jù)進(jìn)行高效、隨機(jī)、分段的訪問,BLOB,CLOB,BFILE,CLOB 即 Character LOB（字符 LOB），它能夠存儲(chǔ)大量字符數(shù)據(jù),BLOB 即 Binary LOB（二進(jìn)制 LO

5、B），可以存儲(chǔ)較大的二進(jìn)制對(duì)象，如圖形、視頻剪輯和聲音文件,BFILE 即 Binary File（二進(jìn)制文件），它用于將二進(jìn)制數(shù)據(jù)存儲(chǔ)在數(shù)據(jù)庫外部的操作系統(tǒng)文件中,Oracle 數(shù)據(jù)類型 5-5,Oracle 中偽列就像一個(gè)表列，但是它并沒有存儲(chǔ)在表中 偽列可以從表中查詢，但不能插入、更新和刪除它們的值 常用的偽列有ROWID和ROWNUM,ROWID 是表中行的存儲(chǔ)地址，該地址可以唯一地標(biāo)識(shí)數(shù)據(jù)庫中的一行，可以使用 ROWID 偽列快速地定位表中的一行,ROWNUM 是查詢返回的結(jié)果集中行的序號(hào)，可以使用它來限制查詢返回的行數(shù),數(shù)據(jù)定義語言,數(shù)據(jù)定義語言用于改變數(shù)據(jù)庫結(jié)構(gòu)，包括創(chuàng)建、更改和

6、刪除數(shù)據(jù)庫對(duì)象 用于操縱表結(jié)構(gòu)的數(shù)據(jù)定義語言命令有： CREATE TABLE ALTER TABLE TRUNCATE TABLE DROP TABLE,數(shù)據(jù)操縱語言,數(shù)據(jù)操縱語言用于檢索、插入和修改數(shù)據(jù) 數(shù)據(jù)操縱語言是最常見的SQL命令 數(shù)據(jù)操縱語言命令包括： SELECT INSERT UPDATE DELETE,DML SELECT 命令 2-1,利用現(xiàn)有的表創(chuàng)建表 語法： CREATE TABLE AS SELECT column_names FROM,SQL CREATE TABLE newitemfile AS SELECT * FROM itemfile,SQL CREATE

7、TABLE newitemfile1 AS SELECT itemcode, itemdesc, qty_hand FROM itemfile,SQL CREATE TABLE newitemfile2 AS SELECT * FROM itemfile WHERE 1 = 2,DML SELECT 命令 2-2,SQL SELECT DISTINCT vencode FROM vendor_master,選擇無重復(fù)的行 在SELECT子句，使用DISTINCT關(guān)鍵字,使用列別名 為列表達(dá)式提供不同的名稱 該別名指定了列標(biāo)題,SQL SELECT itemcode, itemdesc, max

8、_level, max_level*2 AS NEW_MAXLEVEL FROM itemfile,SQL SELECT itemcode,itemdesc, max_level, max_level* 2 “New Maximum Level” FROM itemfile,DML INSERT 命令 2-1,插入日期類型的值 日期數(shù)據(jù)類型的默認(rèn)格式為“DD-MON-RR” 使用日期的默認(rèn)格式 使用TO_DATE函數(shù)轉(zhuǎn)換,INSERT INTO my_table (date_col) VALUES (TO_DATE(2005-10-18, YYYY-MM-DD,INSERT INTO orde

9、r_master VALUES(o001, 12-5月-05, V002, c, 25-5月-05,DML INSERT 命令 2-2,插入來自其它表中的記錄 語法： INSERT INTO (cloumn_list) SELECT column_names FROM,SQL INSERT INTO newvendor_master SELECT * FROM vendor_master,SQL INSERT INTO newvendor_master(vencode,venname) SELECT vencode, venname FROM vendor_master,事務(wù)控制語言,事務(wù)是最

10、小的工作單元，作為一個(gè)整體進(jìn)行工作 保證事務(wù)的整體成功或失敗，稱為事務(wù)控制 用于事務(wù)控制的語句有： COMMIT - 提交并結(jié)束事務(wù)處理 ROLLBACK - 撤銷事務(wù)中已完成的工作 SAVEPOINT 標(biāo)記事務(wù)中可以回滾的點(diǎn),SQL UPDATE order_master SET del_date = 30-8月-05 WHERE orderno SAVEPOINT mark1; SQL DELETE FROM order_master WHERE orderno = o002; SQL SAVEPOINT mark2; SQL ROLLBACK TO SAVEPOINT mark1; SQ

11、L COMMIT,數(shù)據(jù)控制語言,數(shù)據(jù)控制語言為用戶提供權(quán)限控制命令 用于權(quán)限控制的命令有： GRANT 授予權(quán)限 REVOKE 撤銷已授予的權(quán)限,SQL GRANT SELECT, UPDATE ON order_master TO MARTIN,SQL GRANT UPDATE(qty_hand, re_level) ON itemfile TO MARTIN,SQL GRANT SELECT ON vendor_master TO accounts WITH GRANT OPTION,SQL REVOKE SELECT, UPDATE ON order_master FROM MARTIN

12、,SQL 操作符,SQL 操作符,集合操作符,邏輯操作符,比較操作符,算術(shù)操作符,連接操作符,Oracle 支持的 SQL 操作符分類如下,算術(shù)操作符,算術(shù)操作符用于執(zhí)行數(shù)值計(jì)算 可以在SQL語句中使用算術(shù)表達(dá)式，算術(shù)表達(dá)式由數(shù)值數(shù)據(jù)類型的列名、數(shù)值常量和連接它們的算術(shù)操作符組成 算術(shù)操作符包括加(+)、減(-)、乘(*)、除(,SQL SELECT itemdesc, max_level - qty_hand avble_limit FROM itemfile WHERE p_category=spares,SQL SELECT itemdesc, itemrate*(max_level -

13、 qty_hand) FROM itemfile WHERE p_category=spares,比較操作符,比較操作符用于比較兩個(gè)表達(dá)式的值 比較操作符包括 =、!=、=、BETWEENAND、IN、LIKE 和 IS NULL等,SQL SELECT itemdesc, re_level FROM itemfile WHERE qty_hand max_level/2,SQL SELECT orderno FROM order_master WHERE del_date IN (06-1月-05,05-2月-05,SQL SELECT vencode,venname,tel_no FROM

14、 vendor_master WHERE venname LIKE j_s,邏輯操作符,SQL SELECT * FROM order_master WHERE odate 10-5月-05 AND del_date 26-5月-05,顯示 2005-5月-10 至 2005-5月-26的訂單信息,邏輯操作符用于組合多個(gè)計(jì)較運(yùn)算的結(jié)果以生成一個(gè)或真或假的結(jié)果。 邏輯操作符包括與(AND)、或(OR)和非(NOT,集合操作符,集合操作符將兩個(gè)查詢的結(jié)果組合成一個(gè)結(jié)果,集合操作符,UNION,UNION ALL,INTERSECT,MINUS,INTERSECT 操作符只返回兩個(gè)查詢的公共行,SQ

15、L SELECT orderno FROM order_master INTERSECT SELECT orderno FROM order_detail,SQL SELECT orderno FROM order_master MINUS SELECT orderno FROM order_detail,MINUS 操作符返回從第一個(gè)查詢結(jié)果中排除第二個(gè)查 詢中出現(xiàn)的行,連接操作符,連接操作符用于將多個(gè)字符串或數(shù)據(jù)值合并成一個(gè)字符串,SQL SELECT (venname| 的地址是 |venadd1| |venadd2 | |venadd3) address FROM vendor_mas

16、ter WHERE vencode=V001,通過使用連接操作符可以將表中 的多個(gè)列合并成邏輯上的一行列,操作符的優(yōu)先級(jí),SQL 操作符的優(yōu)先級(jí)從高到低的順序是： 算術(shù)操作符 -最高優(yōu)先級(jí) 連接操作符 比較操作符 NOT 邏輯操作符 AND 邏輯操作符 OR 邏輯操作符 -最低優(yōu)先級(jí),SQL 函數(shù),Oracle 提供一系列用于執(zhí)行特定操作的函數(shù) SQL 函數(shù)帶有一個(gè)或多個(gè)參數(shù)并返回一個(gè)值 以下是SQL函數(shù)的分類,SQL 函數(shù),單行函數(shù),分析函數(shù),分組函數(shù),單行函數(shù)分類,單行函數(shù)對(duì)于從表中查詢的每一行只返回一個(gè)值 可以出現(xiàn)在 SELECT 子句中和 WHERE 子句中 單行函數(shù)可以大致劃分為：

17、日期函數(shù) 數(shù)字函數(shù) 字符函數(shù) 轉(zhuǎn)換函數(shù) 其他函數(shù),日期函數(shù),日期函數(shù)對(duì)日期值進(jìn)行運(yùn)算，并生成日期數(shù)據(jù)類型或數(shù)值類型的結(jié)果 日期函數(shù)包括： ADD_MONTHS MONTHS_BETWEEN LAST_DAY ROUND NEXT_DAY TRUNC EXTRACT,字符函數(shù) 2-1,字符函數(shù),字符函數(shù)接受字符輸入并返回字符或數(shù)值,字符函數(shù) 2-2,SQL SELECT CHR(67) FROM dual,以下是一些其它的字符函數(shù)： CHR和ASCII LPAD和RPAD TRIM LENGTH DECODE,SQL SELECT LPAD(function,15,=) FROM dual,SQ

18、L SELECT TRIM(9 from 9999876789999) FROM dual,SQL SELECT LENGTH(frances) FROM dual,SQL SELECT vencode, DECODE(venname,frances,Francis) name FROM vendor_master WHERE vencode=v001,數(shù)字函數(shù),數(shù)字函數(shù)接受數(shù)字輸入并返回?cái)?shù)值結(jié)果,數(shù)字函數(shù),轉(zhuǎn)換函數(shù),轉(zhuǎn)換函數(shù)將值從一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型 常用的轉(zhuǎn)換函數(shù)有： TO_CHAR TO_DATE TO_NUMBER,SELECT TO_CHAR(sysdate,YYYY年f

19、mMM月fmDD日 HH24:MI:SS) FROM dual,SELECT TO_CHAR(itemrate,C99999) FROM itemfile,SELECT TO_DATE(2005-12-06 , yyyy-mm-dd) FROM dual,SELECT TO_NUMBER(100) FROM dual,其它函數(shù),以下是幾個(gè)用來轉(zhuǎn)換空值的函數(shù)： NVL NVL2 NULLIF,SELECT itemdesc, NVL(re_level,0) FROM itemfile,SELECT itemdesc, NVL2(re_level,re_level,max_level) FROM

20、itemfile,SELECT itemdesc, NULLIF(re_level,max_level) FROM itemfile,分組函數(shù),分組函數(shù)基于一組行來返回結(jié)果 為每一組行返回一個(gè)值,AVG,MIN,MAX,SUM,COUNT,SELECT AVG(re_level) FROM itemfile WHERE p_category=accessories,SELECT MAX(max_level) FROM itemfile,SELECT SUM(itemrate*max_level) FROM itemfile,SELECT COUNT(*) FROM itemfile,SELEC

21、T COUNT(itemrate) FROM itemfile,SELECT COUNT(DISTINCT qty_hand) FROM itemfile,分組函數(shù),GROUP BY和HAVING子句,GROUP BY子句 用于將信息劃分為更小的組 每一組行返回針對(duì)該組的單個(gè)結(jié)果 HAVING子句 用于指定 GROUP BY 子句檢索行的條件,SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category,SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category HAVING p_category NOT IN (accessories,分析函數(shù) 2-1,分