第5章SQL語句基礎(chǔ)

1、1第第5章章 SQL語句基礎(chǔ)語句基礎(chǔ) 在Oracle數(shù)據(jù)庫(kù)中，為了方便管理用戶所創(chuàng)建的數(shù)據(jù)庫(kù)對(duì)象，引入了模式的概念，這樣用戶所創(chuàng)建的數(shù)據(jù)庫(kù)對(duì)象就都屬于該用戶模式。對(duì)于一般的用戶而言，數(shù)據(jù)庫(kù)中的數(shù)據(jù)是以表、視圖行等方式存儲(chǔ)的（表和視圖就是最基本的用戶模式對(duì)象），用戶只需要根據(jù)自己的需求查詢數(shù)據(jù)庫(kù)，然后由數(shù)據(jù)庫(kù)根據(jù)請(qǐng)求執(zhí)行相關(guān)的處理，并且將處理結(jié)果返回給特定的用戶。另外，用戶還可以向數(shù)據(jù)庫(kù)中添加數(shù)據(jù)、刪除特定數(shù)據(jù)。在Oracle中，對(duì)這些用戶模式對(duì)象的操作都是通過SQL語句來完成的。2本章知識(shí)要點(diǎn)：本章知識(shí)要點(diǎn)： Oracle示例中的用戶模式 使用SELECT語句檢索數(shù)據(jù) 檢索多個(gè)表中的數(shù)據(jù) 在O

2、racle中各種函數(shù)的使用 正規(guī)表達(dá)式的使用 分組查詢 插入、更新和刪除數(shù)據(jù) 控制Oracle中的事務(wù)處理35.1 使用的用戶模式使用的用戶模式 為了更好理解Oracle的各種具體操作，下面介紹一下Oracle自帶的示例模式（也稱示例方案）。這些模式在安裝數(shù)據(jù)庫(kù)時(shí)由用戶選擇自動(dòng)安裝，所有這些模式一起形成了相同的虛擬公司的一部分，它們各自都有自己的側(cè)重點(diǎn)。5.1.1 SCOTT模式模式 該模式演示了一個(gè)很簡(jiǎn)單的公司人力資源管理，它也是Oracle早期版本中最經(jīng)常使用的示例模式，該用戶模式的連接密碼為TIGER。通過連接到SCOTT用戶模式，查詢數(shù)據(jù)字典視圖USER_TABLES可以獲知該模式所包

3、含的表。45.1.2 HR模式模式 HR類似于SCOTT模式，也是一個(gè)基本的關(guān)系數(shù)據(jù)庫(kù)的示例模式，其中有部門和員工數(shù)據(jù)表。在HR模式中有7個(gè)表：雇員、部門、地點(diǎn)、國(guó)家、地區(qū)、工作和工作歷。5 65.1.3 其他模式其他模式 除了上面介紹的兩種常用模式外，Oracle附帶的示例方案中還包括：訂單目錄（OE）模式、產(chǎn)品媒體（PM）模式、信息交換（IX）模式和銷售記錄（SH）模式。75.2 SELECT語句的用法語句的用法 用戶對(duì)表或視圖的操作是通過SQL語句來實(shí)現(xiàn)的，SQL語句是一種標(biāo)準(zhǔn)的結(jié)構(gòu)化查詢語言。在眾多的SQL語句中，使用頻率最高的是SELECT語句，該語句主用于檢索數(shù)據(jù)。5.2.1 檢索

4、單表數(shù)據(jù)檢索單表數(shù)據(jù) 檢索單表數(shù)據(jù)是指從單個(gè)表中檢索數(shù)據(jù)，檢索的結(jié)果都是來自于同一個(gè)表中，檢索單表數(shù)據(jù)是檢索數(shù)據(jù)的最基礎(chǔ)的操作。85.2.2 過濾數(shù)據(jù)過濾數(shù)據(jù) 在SELECT語句中可以使用WHERE子句過濾數(shù)據(jù)，只檢索那些滿足過濾條件的數(shù)據(jù)。當(dāng)表中的數(shù)據(jù)非常大時(shí)，這種過濾操作是非常有意義的。通過過濾數(shù)據(jù)，可以從大量的數(shù)據(jù)中獲取自己所需要的數(shù)據(jù)。95.2.3 排序數(shù)據(jù)排序數(shù)據(jù) 在前面介紹的數(shù)據(jù)檢索技術(shù)中，只是把數(shù)據(jù)庫(kù)中的數(shù)據(jù)直接取出來。這時(shí)，結(jié)果集中數(shù)據(jù)的排列順序是由數(shù)據(jù)的物理存儲(chǔ)順序所決定的。這種存儲(chǔ)順序是比較混亂的，并且可以不符合用戶的各種業(yè)務(wù)需求，因此需要對(duì)檢索到的結(jié)果集進(jìn)行排序。在SEL

5、ECT語句中，可以使用ORDER BY子句對(duì)檢索的結(jié)果集進(jìn)行排序。105.2.4 多表檢索多表檢索 在實(shí)際應(yīng)用中，經(jīng)常會(huì)碰到需要檢索的數(shù)據(jù)存在于兩個(gè)或兩個(gè)以上的表中。這時(shí)就需要使用SELECT語句執(zhí)行多表檢索。多表檢索操作比單表檢索復(fù)雜得多。為了更好地理解多表檢索操作，需要理解表的別名，笛卡爾積、內(nèi)連接、外連接、自然連接和交叉連接等概念。115.3 函數(shù)的使用函數(shù)的使用 與其他編程語言一樣，SQL提供了許多內(nèi)置的函數(shù)，使用這些函數(shù)可以大大提高這些計(jì)算機(jī)語言的運(yùn)算、判斷功能。例如，使用字符串函數(shù)可以對(duì)字符串進(jìn)行處理、使用數(shù)學(xué)函數(shù)進(jìn)行數(shù)值運(yùn)算、使用轉(zhuǎn)換函數(shù)對(duì)數(shù)據(jù)類型進(jìn)行轉(zhuǎn)換、使用日期函數(shù)處理日期和

6、時(shí)間等。125.3.1 字符函數(shù)字符函數(shù) 字符函數(shù)用指用于對(duì)字符表達(dá)式進(jìn)行處理的函數(shù)，它也是Oracle系統(tǒng)中廣泛使用的函數(shù)。在使用字符串函數(shù)時(shí)，其輸入值一般是字符數(shù)據(jù)類型，而其輸出結(jié)果則是經(jīng)過處理的字符表達(dá)式。135.3.2 數(shù)字函數(shù)數(shù)字函數(shù) 數(shù)學(xué)函數(shù)可以用于執(zhí)行各種數(shù)據(jù)計(jì)算。在其他編程語言中提供了大量的數(shù)學(xué)函數(shù)，這也是編程語言最早的功能之一。在Oracle系統(tǒng)中，也提供了大量的數(shù)學(xué)函數(shù)，這些函數(shù)大大增強(qiáng)了Oracle系統(tǒng)的科學(xué)計(jì)算能力。14 函數(shù)說明ABS(X)返回X的絕對(duì)值CEIL(X)返回大于等于數(shù)值X的最小整數(shù)COSH(X)返回?cái)?shù)值X的雙曲余弦值EXP(X)返回e的X次冥。（e=2.

7、71828183）FLOOR(X)返回小于等于數(shù)值X的最大整數(shù)LN(X)返回?cái)?shù)值X的自然對(duì)數(shù)。（X必須大于0）LOG(X,Y)返回以X為底的數(shù)值Y的對(duì)數(shù)。（X1,Y0）MOD(X,Y)返回X/Y后的余數(shù)，若Y=0，則返回X（求模運(yùn)算）POWER(X,Y)返回X的Y次冥ROUND(X,Y)執(zhí)行四舍五入運(yùn)算，Y可以省略，當(dāng)省略Y時(shí)，四舍五入到整數(shù)位；當(dāng)X為正數(shù)時(shí)，四舍五入到小數(shù)點(diǎn)后Y位；當(dāng)X為負(fù)數(shù)時(shí)，四舍五入到小數(shù)點(diǎn)前Y位SIGN(X)檢測(cè)數(shù)值的正負(fù)，當(dāng)X0則返回1，當(dāng)X=0返回0SQRT(X)返回?cái)?shù)值X的平方根。(X=0)TRUNC(X,Y)截取數(shù)值X，Y可以省略，當(dāng)省略Y時(shí)則截取X的小數(shù)部分；

8、當(dāng)Y為正數(shù)時(shí)則將X截取到小數(shù)點(diǎn)后Y位；當(dāng)X為負(fù)數(shù)時(shí)則將X截取到小數(shù)點(diǎn)前Y位5.3.3 時(shí)間和日期函數(shù)時(shí)間和日期函數(shù) 在默認(rèn)情況下，日期數(shù)據(jù)的格式是DD-MON-YY。其中，DD表示兩位數(shù)字的日，MON表示3位數(shù)的月份，YY表示兩位數(shù)字的年。在插入數(shù)據(jù)時(shí)，默認(rèn)也采用DD-MON-YY格式插入數(shù)據(jù)。15函數(shù)說明add_months(x,y)在X給定的日期上增加Y個(gè)月。如果Y為負(fù)數(shù)，則表示從X中減去Y個(gè)月last_day(x)返回包含在X中的月份的最后一天months_between(x,y)返回X和Y之間的月數(shù)next_day(x,day)返回緊接著X的下一天，參數(shù)DAY是一個(gè)字符串sysdate

9、()返回當(dāng)前系統(tǒng)的日期current_date()返回本地時(shí)區(qū)的當(dāng)前日期n e w _ t i m e ( x , t i m e _ z o n e 1 ,time_zone2)將時(shí)間X從時(shí)區(qū)TIME_ZONE1轉(zhuǎn)變成時(shí)區(qū)TIME_ZONE2localtimestamp()返回會(huì)話中的日期和時(shí)間5.3.4 轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù) 在操作表中的數(shù)據(jù)時(shí)，經(jīng)常需要將某個(gè)數(shù)據(jù)從一種數(shù)據(jù)類型轉(zhuǎn)變?yōu)榱硗庖环N數(shù)據(jù)類型。這時(shí)就需要使用數(shù)據(jù)轉(zhuǎn)換函數(shù)。例如，如果希望把表示價(jià)格的數(shù)字?jǐn)?shù)據(jù)轉(zhuǎn)變?yōu)樽址麛?shù)據(jù)，就需要使用TO_CHAR()函數(shù)。通常這類函數(shù)遵循如下慣例：函數(shù)名稱后面跟著待轉(zhuǎn)換類型以及輸出類型。165.3.5 統(tǒng)

10、計(jì)函數(shù)統(tǒng)計(jì)函數(shù) 使用統(tǒng)計(jì)函數(shù)可以針對(duì)一組數(shù)據(jù)進(jìn)行計(jì)算，并得到相應(yīng)的結(jié)果。使用這些統(tǒng)計(jì)函數(shù)，可以計(jì)算表中數(shù)據(jù)列的平均值、最大值和最小值等數(shù)據(jù)。17 函數(shù)說明函數(shù)說明AVG(X)平均值MIN(X)最小值COUNT(X)統(tǒng)計(jì)數(shù)量STDDEV(X)標(biāo)準(zhǔn)差MAX(X)最大值SUM(X)匯總值MEDIAN(X)中位數(shù)VARIANCE(X)方差185.3.6 分組技術(shù)分組技術(shù) 在SELECT語句中，可以使用GROUP BY子句進(jìn)行分組操作，并可以使用HAVING子句提供分組條件。 在下面的查詢中，將對(duì)SCOTT模式中的EMP表進(jìn)行分組，以統(tǒng)計(jì)各部門的員工人數(shù)：SQL select deptno,count(

11、*) as 員工數(shù)量 2 from emp 3 group by deptno; DEPTNO 員工數(shù)量 - - 30 6 20 5 10 35.4 子查詢子查詢 在執(zhí)行數(shù)據(jù)操縱的過程中，如果某個(gè)操作需要依賴于另外一個(gè)SELECT語句的結(jié)果，那么可以把SELECT語句嵌入到該操縱語句中，這就形成了一個(gè)子查詢。實(shí)際上，在操作表中的數(shù)據(jù)時(shí)，數(shù)據(jù)并不是孤立的，而是互相關(guān)聯(lián)的。這樣就可以根據(jù)數(shù)據(jù)之間的關(guān)聯(lián)使用相應(yīng)的子查詢，從而實(shí)現(xiàn)復(fù)雜的查詢。195.4.1 子查詢的概念子查詢的概念 在一個(gè)SELECT語句被嵌套在另外一個(gè)SELECT、UPDATE或DELETE等SQL語句中時(shí)，被嵌套的SELECT語句

12、就是子查詢。使用子查詢的原因是，如果希望執(zhí)行某SQL語句，但是該SQL語句還需要依賴于另外一個(gè)SELECT語句的執(zhí)行結(jié)果。205.4.2 單行子查詢單行子查詢 在單行子查詢中，該內(nèi)查詢只返回單行單列值，因此可以把這種子查詢作為一個(gè)學(xué)量。在WHERE子句中，可以使用單行比較運(yùn)算符來比較某個(gè)表達(dá)式與子查詢的結(jié)果?？梢允褂玫膯涡斜容^運(yùn)算符包括：等于“=”、大于“”、大于或等于“=”、小于“”、小于或等于“=”和不等于“/!=”。215.4.3 多行子查詢多行子查詢 多行子查詢可以返回單列多行的數(shù)據(jù)。在這種多行子查詢中，必須使用多行運(yùn)算符來判斷，而不能使用單行運(yùn)算符。使用多行運(yùn)算符可以執(zhí)行與一個(gè)或多個(gè)

13、數(shù)據(jù)的比較操作。在Oracle系統(tǒng)中，可以使用的多行比較運(yùn)算符包括：IN（等于列表中的任何一值）、ANY（與子查詢返回的每一個(gè)值進(jìn)行比較）和ALL（與子查詢返回的所有值進(jìn)行比較）。225.4.4 關(guān)聯(lián)子查詢關(guān)聯(lián)子查詢 在前面介紹的子查詢中，內(nèi)查詢和外查詢是分開執(zhí)行的，即內(nèi)查詢的執(zhí)行與外查詢的執(zhí)行是沒有關(guān)系的，而外查詢僅僅使用內(nèi)查詢的最終結(jié)果。如果在子查詢語句中，內(nèi)查詢的執(zhí)行需要借助于外查詢，而外查詢的執(zhí)行又離不開內(nèi)查詢的執(zhí)行。這時(shí)，內(nèi)查詢和外查詢是相互關(guān)聯(lián)的，這種子查詢稱為關(guān)聯(lián)子查詢。235.5 操作數(shù)據(jù)操作數(shù)據(jù) SQL語句除可以查詢數(shù)據(jù)外，還可以對(duì)完成插入、更新和刪除數(shù)據(jù)等操作。在Oracl

14、e 11g中，在創(chuàng)建表后，只有在表中插入數(shù)據(jù)之后，該表的才有意義。如果表中的數(shù)據(jù)不合適，還可以對(duì)那些不合適的數(shù)據(jù)進(jìn)行更新。如果某些數(shù)據(jù)已經(jīng)不再需要，則可以刪除這些數(shù)據(jù)。在操作數(shù)據(jù)的過程中，Oracle是通過種事務(wù)來進(jìn)行管理。245.5.1 插入數(shù)據(jù)插入數(shù)據(jù) 插入數(shù)據(jù)就是將數(shù)據(jù)放置到已經(jīng)創(chuàng)建的表中，Oracle數(shù)據(jù)庫(kù)是通過INSERT語句來實(shí)現(xiàn)插入數(shù)據(jù)的。一般情況下，使用INSERT語句可以一次插入一行數(shù)據(jù)。255.5.2 更新數(shù)據(jù)更新數(shù)據(jù) 在更新數(shù)據(jù)時(shí)，即可以一次更新一列，也可以一次更新多列。如果在UPDATE語句中使用了WHERE條件表達(dá)式，那么只有符合條件的記錄才會(huì)被更新；如果沒有使用WH

15、ERE條件表達(dá)式，那么表示更新表中所有行的數(shù)據(jù)。265.5.3 刪除數(shù)據(jù)刪除數(shù)據(jù) 在刪除操作中，即可以一次刪除一行數(shù)據(jù)，也可以一次刪除多行數(shù)據(jù)，更可以刪除表中的所有數(shù)據(jù)。在DELETE語句中，如果沒有使用WHERE條件表達(dá)式，那么將會(huì)刪除表中的所有數(shù)據(jù)。例如，下面語句將刪除EMP表中的ENAME列為ATG的記錄行：27SQL delete from emp 2 where ename=ATG;已刪除 1 行。5.6 Oracle中事務(wù)處理中事務(wù)處理 在Oracle系統(tǒng)中，在使用INSERT、UPDATE和DELETE語句操作數(shù)據(jù)時(shí)，數(shù)據(jù)庫(kù)中的數(shù)據(jù)并不會(huì)立即改變，用戶還可以通過控制事務(wù)確認(rèn)是否提交或取消先前的操作。285.6.1 事務(wù)的基本概念事務(wù)的基本概念 事務(wù)數(shù)據(jù)庫(kù)系統(tǒng)工作的一個(gè)邏輯單元，它由一個(gè)或多個(gè)SQL語句組成。對(duì)于數(shù)據(jù)庫(kù)而言，事務(wù)是不可分割的工作單元，一個(gè)事務(wù)中的所有SQL語句要么全部執(zhí)行，要么全部不執(zhí)行。也就是說，當(dāng)事務(wù)被提交后，該事務(wù)的操作才直正被保存到數(shù)據(jù)庫(kù)中。如果某個(gè)事務(wù)被回退了，那么該事務(wù)的所有操作都被取消。事務(wù)的回退和提交可以由用戶顯式執(zhí)行，也可以隱含地執(zhí)行。295.6.2 事務(wù)控制事務(wù)控制 Oracle中的事務(wù)是隱式自動(dòng)開始，它不需要用戶顯式地使用語句開始事務(wù)處理。當(dāng)發(fā)生如下情況時(shí)，Oracle認(rèn)為一個(gè)事務(wù)結(jié)束：執(zhí)行COMM