藍橋?qū)W習數(shù)據(jù)庫筆記

數(shù)據(jù)庫Oracle筆記

白馬非馬

2016年9月11日

第1章數(shù)據(jù)庫Oracle4

一、主流數(shù)據(jù)庫4

二、結(jié)構(gòu)化查詢語言4

三、Oracle安裝4

第2章查詢和排序4

一、數(shù)據(jù)查詢4

1.基本語句4

2.字段別名4

3.連字運算符||5

4.去重復行DISTINCT5

5.限定行WHERE5

6.優(yōu)先規(guī)貝5

上課實例：5

二、排序6

第3-1章單行函數(shù)7

一、字符函數(shù)7

二、數(shù)字函數(shù)8

三、日期函數(shù)9

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

五、通用函數(shù)11

習題講解12

第3-2章多表查詢15

一、多表查詢15

上課實例：16

二、內(nèi)連接與外連接16

A.內(nèi)連接16

B.外連接17

上課實例：18

補充：19

第4章子查詢和組函數(shù)19

一、子查詢19

1.單行子查詢20

2.多行子查詢21

上課實例：22

二、組函數(shù)（統(tǒng)計函數(shù)）23

1、統(tǒng)計函數(shù)23

2、分組查詢23

注意點：WHERE和HAVING的區(qū)別26

上課實例：26

第5-1章操作數(shù)據(jù)27

一、數(shù)據(jù)增加28

上課實例：28

二、數(shù)據(jù)修改28

上課實例：29

2

三、數(shù)據(jù)刪除29

上課實例：29

四、事務(wù)處理30

五、數(shù)據(jù)偽列30

第5-2章表的管理33

一、常用的數(shù)據(jù)字段33

二、表的創(chuàng)建34

三、表的復制35

四、為表重命名35

五、表的截斷36

六、表的刪除36

七、Oracle10g的新特性：閃回技術(shù)36

八、修改表結(jié)構(gòu)37

九、思考題38

上課實例：39

第5-3章約束39

一、非空約束(NOTNULL)：NK39

二、唯一約束(UNIQUE)：UK40

三、主鍵約束(PrimaryKey)：PK41

四、檢查約束(Check)：CK42

五、主-外鍵約束43

六、修改約束46

七、查詢約束47

上課實例：48

第6章視圖、序列、索引48

-、視圖48

二、同義詞50

三、索引51

四、集合52

五、序列53

上課實例：55

第7章JDBC55

?、通過名稱登錄55

二、查詢Oracle數(shù)據(jù)庫57

三、更新Oracle數(shù)據(jù)庫數(shù)59

3

第1章數(shù)據(jù)庫Oracle

一、主流數(shù)據(jù)庫

l.Oracle數(shù)據(jù)庫

2.MySQL數(shù)據(jù)庫

3.SQLServer

4.DB2

二、結(jié)構(gòu)化查詢語言

SQL包括四部分

數(shù)據(jù)定義語言（DDL），用于定義SQL模式、表、視圖、索引等數(shù)據(jù)庫對象結(jié)構(gòu)。

數(shù)據(jù)操作語言（DML）,用于插入、刪除和更改數(shù)據(jù)。

數(shù)據(jù)查詢語言（DQL）,用于查詢數(shù)據(jù)、通常將數(shù)據(jù)操作語言和數(shù)據(jù)查詢語言系統(tǒng)統(tǒng)稱為數(shù)據(jù)

操作語言。

數(shù)據(jù)控制語言（DCL）,用于對表、視圖等的授權(quán)、完整性規(guī)則的而描述和事務(wù)控制等。

三、Oracle安裝

略

第2章查詢和排序

一、數(shù)據(jù)查詢

1.基本語句

1.SELECT字段列表FROM表名MHERE條件

2.ORDERBY字段名1[ASC|DESC][，字段名2[ASC|DESC]...];

*：表示選擇表中的所有字段

字段名：選擇表中的字段名稱，可以選擇多個字段，各個字段間用逗號分隔。

表達式：山字段、函數(shù)等組成的表達式。

表名：指定包含字段的表。

WHERE條件：查詢的條件，可以通過該條件進行選擇。

ORDERBY字段名：要求在查詢的結(jié)果中進行排序，默認是升序ASC,降序DESC。

2.字段別名

在SELECT所選字段后面可以指定別名，字段名和別名之間用空格分開。在默認情況下,

別名標題用大寫字母顯示，如果別名中包含空格或特殊字符（列如#或&）,或者大小寫敏

感，需要在別名放在雙引號中。最好加匕AS關(guān)鍵字。

4

3.連字運算符||

使用連字運算符，可以進行字段與字段、字段與表達式、字段與常數(shù)之間的連接，來創(chuàng)建

?個字符表達式，連字運算符兩邊的字段被合成一個單個的列輸出。

4.去重復行DISTINCT

SelectDISTINCT...FROM...

5.限定行WHERE

簡單比較條件：=,后三個都是不等于）

BETWEEN...AND...:查詢顯示上下限之間的行。

IN：IN條件也稱為成員條件，用以查詢出所選字段中符合指定的一組值中的一個。

LIKE：LIKE條件也稱為通配符，可以使用兩個通配符來構(gòu)造需要匹配的字符模板，其中“％”

表示零個或多個字符,表示一個字符，這里提到的字符可以說文字也可以是數(shù)字。

■需要匹配“％”和“，時，可以使用ESCAPE選項，該選項指定換碼符是什么。如：

想搜索emp表中jobjd包含“AD_”的雇員職位編號、名字和薪水信息，可以用下面SQL

語句：

SELECTjob_id;name,salFROMempWHEREjobjdLIKE'%AD$_%'ESCAPE*';

此時ESCAPE選項指定美元符“$”為換碼符，即不將美元符號后面的"」當作通配符看待。

Null|包括isnull條件和isnotnull條件。判斷空值時不能使用“="，因為NUII不能等于或

不等于任何值。

邏輯andornot:與或非。

6.優(yōu)先規(guī)則

默認的優(yōu)先順序：

（1）算數(shù)運算

（2）連字運算

（3）比較運算

（4）Is[not]null,like、[not]in?

（5）[not]betweeno

（6）Not邏輯條件。

（7）And邏輯條件。

（8）Or邏輯條件。

上課實例：

一查詢?nèi)繂TI：的全部信息

select*fromEMP;

一查詢?nèi)繂T工的部分信息

selectenarne,jobfromemp;

--查詢所有員工薪水在800~1500的員工

select*fromempwheresalbetween800andl500;

select*fromempwheresal>=800andsal<=1500;

一查詢所有員工工資大于1500的員工

5

select*fromempwheresal>1500;

--查詢所有員工姓名中帶A的員工

select*fromempwhereenamelike'%A%,;

--查詢所有員工工資大于500且獎金大于500的員工信息

select*fromempwheresal>500andcomm>500;

一查詢所有員工工資大于1500或獎金大于1000的員工信息

select*from叩wheresal>1500orcomm>1000;

--查詢所有員工中姓J的員工工資大于1500或獎金大于1000的員工

select*fromempwhereenamelike'J%'and(sal>1500orcomm>1000);

一查詢所有員工中姓名帶A的員工的并按入職日期降序排序

select*fromempwhereenamelike'%A%'orderbyhiredatedesc;

一查詢所有員工的月薪和獎金都按降序排序

select*fromemporderbysaldesc,commdesc;

一查詢所有獎金為空的員工信息

select*fromempwherecommisnull;

一杳詢所有發(fā)獎金的員工信息

select*fromempwherecommisnotnull;

--給列名起別名，給表名起別名

selectenameas"姓名"，salas"月薪"fromscott.emps

wheres.enamelike'%A%'andsal>1500orderbysaldesc;

--查詢月薪為500,1000,1500,2000

select*fromempwheresalin(500,1000,1500,2000);

二、排序

要在一個不明確順序的查詢結(jié)果中對行進行排序，可以使用ORDERBY子句，它必須位于SQL

語句的最后。指定一個表達式、或者一個字段名、作為排序條件。ORDERBY子句的語法：

ORDERBY{COLUMN,EXPR}[ASC|DESC]

針對不同的數(shù)據(jù)類型，其默認升序：

1.對于數(shù)字類型，小的值在前面顯示.

2.對于日期類型，早的日期在前面。

3.對于字符類型，a在前，z在后。

4.對于空值，升序在最后。降序在最前。

6

第3-1章單行函數(shù)

單行函數(shù)主要分為以下五類：字符函數(shù)、數(shù)字函數(shù)、日期函數(shù)、轉(zhuǎn)換函數(shù)、通用函數(shù)；

一、字符函數(shù)

字符函數(shù)的功能主要是進行字符串數(shù)據(jù)的操作，下面給出幾個字符函數(shù)：

?UPPER(字符串|列)：將輸入的字符串變?yōu)榇髮懛祷兀?/p>

?LOWER(字符串|列)：將輸入的字符串變?yōu)樾懛祷兀?/p>

?INITCAP(字符串|列)：開頭首字母大寫；

?LENGTH(字符串|歹U)：求出字符串的長度；

?REPLACE(字符串|歹I」)：進行替換；

?SUBSTR(字符串|歹開始點［，結(jié)束點］)：字符串截?。?/p>

Oracle之中有一點比較麻煩，即使要驗證字符串，也必須編寫完整的SQL語句，所以在

Oracle數(shù)據(jù)庫之中為了用戶查詢方便，所以專門提供了?個“dual”的虛擬表。

范例：觀察轉(zhuǎn)大寫的函數(shù)

SELECTUPPER('hello')FROMdual;

范例：觀察轉(zhuǎn)小寫的操作，將所有的雇員姓名按照小寫字母返回

SELECTLOWER(ename)FROMemp;

范例：將每一個雇員姓名的開頭首字母大寫

SELECTINITCAP(ename)FROMemp；

范例：查詢出每個雇員姓名的長度

SELECTename,LENGTH(ename)FROMemp；

范例：要求查詢出姓名長度正好是5的雇員信息

SELECTename,LENGTH(ename)FROMempWHERELENGTH(ename)-5；

范例：使用字母“一”替換掉姓名中的所有字母“A”

SELECTREPLACE(ename,'A',)FROMemp；

字符串截取操作有兩種語法：

語法一：SUBSTR(字符串|歹ij,開始點)，表示從開始點一直截取到結(jié)尾；

SELECTename,SUBSTR(enamez3)FROMemp；

語法二：SUBSTR(字符串|歹ij,開始點，結(jié)束點)，表示從開始點截取到結(jié)束點，截

取部分內(nèi)容：

SELECTename,SUBSTR(ename,0,3)FROMemp；

7

SELECTename,SUBSTR(ename,1,3)FROMemp；

范例：要求截取每個雇員姓名的后三個字母

?正常思路：通過長度-2確定開始點

SELECTename,SUBSTR(ename,LENGTH(ename)-2)FROMemp；

?新思路：設(shè)置負數(shù)，表示從后指定截取位置；

SELECTename,SUBSTR{ename,-3)FROMemp；

面試題：請問SUBSTR。函數(shù)截取的時候下標從0還是從1開始？

?在Oracle數(shù)據(jù)庫之中，SUBSTR()函數(shù)從0或1開始都是一樣的；

?SUBSTR。也可以設(shè)置為負數(shù)，表示由后指定截取的開始點；

二、數(shù)字函數(shù)

數(shù)字函數(shù)?共有三個：

?ROUND(數(shù)字|列［，保留小數(shù)的位數(shù)］)：四舍五入的操作；

?TRUNC(數(shù)字|列［，保留小數(shù)的位數(shù)］)：舍棄指定位置的內(nèi)容；

?MOD(數(shù)字1,數(shù)字2)：取模，取余數(shù)；

范例：驗證ROUND。函數(shù)

SELECTROUND(903.53567),ROUND(-903.53567),ROUND(903.53567,2),

ROUND(-90353567,1)FROMdual;

ROUND(90353567)ROUND(-90353567)ROUND(90353567.2)ROUND(-90353567.-1)|

范例：驗證TRUNC。函數(shù)

SELECTTRUNC(903.53567),TRUNC(-903.53567),TRUNC(903.53567,2),

TRUNC(-90353567,1)FROMdual;

TRUNC(903.53567)|TRUNC(-903.53567)|TRUNC(903.53567.2)|TRUNq-90353567.-1)

范例：取模操作

SELECTMOD(10,3)FROMdual;

8

三、日期函數(shù)

如果現(xiàn)在要想進行日期的操作，則首先有一個必須要解決的問題，就是如何取得當前的日期,

這個當前日期可以使用“SYSDATE”取得，代碼如下：

SELECTSYSDATEFROMdual;

除了以上的當前日期之外，在日期中也可以進行若干計算：

?II期+數(shù)字=日期，表示若干天之后的II期；

SELECTSYSDATE+3,SYSDATE+300FROMdual;

?日期一數(shù)字=日期，表示若干天前的日期；

SELECTSYSDATE3,SYSDATE300FROMdual;

?日期一日期=數(shù)字，表示的是兩個日期間的天數(shù)，但是肯定是大日期一小日期;

范例：求出每個雇員到今天為止的雇傭天數(shù)

SELECTename,hiredate,SYSDATEhiredateFROMemp；

而且很多的編程語言之中，也都會提出一種概念，日期可以通過數(shù)字表示出來。

除了以上的三個公式之外，也提供了如下的四個操作函數(shù)：

?LAST_DAY（日期）：求出指定日期的最后一天；

范例：求出本月的最后?天日期

SELECTLAST_DAY（SYSDATE）FROMdual;

?NEXT_DAY（日期，星期數(shù)）：求出下一個指定星期X的日期；

范例：求出下一個周一

SELECTNEXT_DAY（SYSDATE,1一星期''）FROMdual;

?ADD_MONTHS（日期，數(shù)字）：求出若干月之后的日期；

范例：求出四個月后的日期

SELECTADD_MONTHS（SYSDATE,4）FROMdual;

?MONTHS_BETWEEN（日期1,日期2）：求出兩個日期之間所經(jīng)歷的月份；

范例：求出每個雇員到今天為止的雇傭月份

SELECTename,hiredate,TRUNC（MONTHS_BETWEEN（SYSDATE,hiredate））FROMemp；

在所有的開發(fā)之中，如果是日期的操作，建議使用以上的函數(shù)，因為這些函數(shù)可以避免閏年

的問題。

9

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

現(xiàn)在已經(jīng)接觸到了Oracle數(shù)據(jù)庫之中的三種數(shù)據(jù)：數(shù)字(NUMBER)、字符串

(VARCHAR2),H期(DATE),轉(zhuǎn)換函數(shù)的主要功能是完成這幾種數(shù)據(jù)間的互相轉(zhuǎn)換

操作，-共有三種轉(zhuǎn)換函數(shù)：

?TO_CHAR(字符串|歹U，格式字符串)：將日期或者是數(shù)字變?yōu)樽址@示；

?TO_DATE(字符串，格式字符串)：將字符串變?yōu)镈ATE數(shù)據(jù)顯示；

?TO_NUMBER(字符串)：將字符串變?yōu)閿?shù)字顯示；

a、TO_CHAR()函數(shù)

在之前查詢過當前的系統(tǒng)11期時間：

SELECTSYSDATEFROMdual;

這個時候是按照“日-月-年”的格式顯示，很明顯這種顯示格式不符合正常的思路，正常是“年

-月-日”，所以這種情況下可以使用TO_CHAR()函數(shù)，但是使用此函數(shù)的話需要一些格式

字符串：年(yyyy),月(mm),日(dd)。

SELECTTO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy')year,

TO_CHAR(SYSDATE,'mm')month,TO_CHAR(SYSDATE,'dd')dayFROMdual;

TO_CHAR(SYSDASE,'YYYY-MM-DD廠丫EARMONTHDAY

?1

但是這個時候的顯示數(shù)據(jù)之中可以發(fā)現(xiàn)會存在前導0,如果要想消除掉這個0的話，可以加

入一個"fm”。

SELECTTO_CHAR(SYSDATE,'fmyyyy-tntn-dd')dayFROMdual；

正常人都加0,所以這個標記知道就行了，可是在Oracle之中，DATE里面是包含了時間

的，但是之前的代碼沒有顯示出時間，要想顯示時間則需要增加標記：

SELECTTO_CHAR(SYSDATE,1fmyyyy-mm-ddhh24:mi：ss')dayFROMdual;

一定要注意，使用TO_CHAR()函數(shù)之后，所有的內(nèi)容都是字符串，不再是之前的DATE

型數(shù)據(jù)，TO_CHAR()函數(shù)也可以用于數(shù)字的格式化上，這個時候每一個“9”表示一位數(shù)字

的概念，而不是數(shù)字9的概念。

SELECTTO_CHAR(89078907890,'L999,999,999,999,999')FROMdual;

10

TO_CHAR(89078907890.'L999.999.

￥89,078,907.890

其中的字母“L”，表示的是“Local”的含義，即：當前的所在的語言環(huán)境下的貨幣符號。

b、TODATE()函數(shù)

此函數(shù)的主要功能是將一個字符串變?yōu)镈ATE型數(shù)據(jù)。

SELECTTO_DATE(,1989-09-12',?yyyy-mm-dd,)FROMdual;

TO_DATE('1989-09-12'/YYYY-MM-

1—

c、TONUMBER()函q：基本不用

TO_NUMBER()函數(shù)?看就知道是將字符串變數(shù)字的：

SELECTTO_NUMBER('1')+TO_NUMBER('2')FROMdual;

但是在Oracle之中真的很智能，所以以上的功能不使用TO_NUMBER()也可完成:

SELECT'l'+'2'FROMdual;

以上結(jié)果為：3

所以現(xiàn)在的TO_NUMBER()函數(shù)基本上已經(jīng)是不考慮了，重點的函數(shù)在TO_CHAR()上,

其次是TO_DATE()函數(shù)。

五、通用函數(shù)

通用函數(shù)主要有兩個：NVL()、DECODEO,這兩個函數(shù)算是Oracle自己的特色函數(shù)了;

a,NVL()函金，處理null

范例：要求查詢出每個雇員的全部年薪

SELECTename,sal,comm,(sal?comm)*12FROMemp；

ENAMESALCOMMl(SAL+COMM)*12

1

2ALLEN1600003000022B00

3

4JONES2975.00

5

6

7

11

這個時候有的雇員的年薪就變成了null,而造成這種問題的關(guān)鍵是在于comm字段上為

null,那么要想解決這個問題，就必須做一種處理：將null變?yōu)?,而這個就是NVL()函

數(shù)的作用。

SELECTename,sal,comm,(sal+NVL(comm,0))*12ZNVL(comm,0)FROMemp；

ENAMESALCOMM(SAL+NVL(COMM,0)f12NVL(COMM,0)

1I9600|

2ALLEN1600003000022800300|

31

4JONES297500357000

5

b、DECODE。函數(shù)：多數(shù)值判斷

DECODE。函數(shù)非常類似于程序中的if…else…語句，唯不同的是DECODE。函數(shù)判斷

的是數(shù)值，而不是邏輯條件。

例如，現(xiàn)在要求顯示全部雇員的職位，但是這些職位要求替換為中文顯示:

?CLERK：辦事員；

?SALESMAN：銷售；

?MANAGER：經(jīng)理；

?ANALYST：分析員；

?PRESIDENT：總裁；

這種判斷肯定是逐行進行判斷，所以這個時候就必須采用DECODE。,而此函數(shù)的語法如

下：

DECODE(數(shù)值|列，判斷值1,顯示值1,判斷值2,顯示值2,判斷值3,顯示值3,...)

范例：實現(xiàn)顯示的操作功能

SELECTempno,ename,job,DECODE(jobJCLERK一辦事員—SALESMAN一銷售人員

'JMANAGER一經(jīng)理一ANALYST'J分析員一PRES工DENT一總裁i)FROMemp；

EMPNOENAMEJOBDECODEtJOB/CLERK17.

1

2

3

4

5

習題講解

1、選擇部門30中的所有員工。

SELECT*FROMempWHEREdeptno-30;

12

2、列出所有辦事員(CLERK)的姓名，編號和部門編號。

SELECTempnozename,deptnoFROMempWHEREjob='CLERK'；

3、找出傭金高于薪金的員工。

SELECT*FROMempWHEREcomm>sal;

4、找出傭金高于薪金的60%的員工。

SELECT*FROMempWHEREcomm>sal*0.6;

5、找出部門10中所有經(jīng)理(MANAGER)和部門20中所有辦事員(CLERK)的詳細資

料.

SELECT*FROMempWHERE(job'MANAGER'ANDdeptno10)OR(job'CLERK'ANDdeptno20);

6、找出部門10中所有經(jīng)理(MANAGER),部門20中所有辦事員(CLERK),既不是

經(jīng)理又不是辦事員但其薪金大于或等于2000的所有員工的詳細資料。

SELECT*FROMempWHERE(job='MANAGER'ANDdeptno=10)OR(job='CLERK'AND

deptno20)OR(jobNOTIN(1MANAGER','CLERK')ANDsal>=2000);

7、找出收取傭金的員工的不同工作。

SELECTDISTINCTjobFROMempWHEREcommISNOTNULL;

8、找出不收取傭金或收取的傭金低于100的員工。

SELECT*FROMempWHEREcommISNULLORcomm<100;

9、找出各月倒數(shù)第3天受雇的所有員工。

每一個雇員的雇傭日期肯定是不…樣的，所以現(xiàn)在必須找到每個雇員雇傭所在月的最后一

天，之后按照“日期-數(shù)字”的方式求出前三天的日期，這個日期必須和雇傭日期相符合才滿

足條件。

SELECT*FROMempWHERELAST_DAY(hiredate)-2hiredate；

10、找出早于12年前受雇的員工。

如果要求年份，最準確的做法是使用總月數(shù)/12；

SELECT*FROMempWHEREMONTHS_BETWEEN(SYSDATE,hiredate)1212;

11、以首字母大寫的方式顯示所有員工的姓名。

SELECTINITCAP(ename)FROMemp；

12、顯示正好為5個字符的員工的姓名。

SELECTenameFROMempWHERELENGTH(ename)=5;

13

13、顯示不帶有“R”的員工的姓名。

SELECTenameFROMempWHEREenameNOTLIKE'%R%'；

14、顯示所有員工姓名的前三個字符。

SELECTSUBSTR(ename,0,3)FROMemp；

15、顯示所有員工的姓名，用“a”替換所有“A。

SELECTREPLACE(ename,'A1,(a')FROMemp；

16、顯示滿10年服務(wù)年限的員工的姓名和受雇日期。

SELECTenamezhiredateFROMempWHEREMONTHS_BETWEEN(SYSDATEzhiredate)/12>10;

17、顯示員工的詳細資料，按姓名排序。

SELECT*FROMempORDERBYename;

18、顯示員工的姓名和受雇日期，根據(jù)其服務(wù)年限，將最老的員工排在最前面。

SELECTename,hiredateFROMempORDERBYhiredate；

19、顯示所有員工的姓名、工作和薪金，按工作的降序排序，若工作相同則按薪金排序。

SELECTename,job,salFROMempORDERBYjobDESC,sal；

20、顯示所有員工姓名、加入公司的年份和月份，按受雇日期所有月排序，若月份相同則

將最早年份的員工排在最前面。

本程序需要從日期之中取出年份和月份，用TO_CHAR()函數(shù)完成。

SELECTename,TO_CHAR(hiredatez'yyyy')year,TO_CH2VR(hiredate,'mm')monthsFROM

empORDERBYmonthszyear；

21、顯示在一個月為30天的情況所有員工的日薪金，忽略余數(shù)。

SELECTename,sal,TRUNC(sal/30)FROMemp；

22、找出在(任何年份的)2月受聘的所有員工。

SELECT*FROMempWHERETO_CHAR(hiredate,'mm')2;

23、對于每個員工，顯示其加入公司的天數(shù)。

SELECTenamezSYSDATEhiredateFROMemp;

24、顯示姓名字段的任何位置包含“A”的所有員工的姓名。

SELECTenameFROMempWHEREenameLIKE'%A%';

25、以年月II的方式顯示所有員工的服務(wù)年限。

14

第一步：求出每個雇員的雇傭年數(shù)：被雇傭的總月數(shù)/12=年數(shù)：

SELECTename,hiredate,

TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12)yearFROMemp；

第二步：求出月數(shù)，以上計算之中被忽略的小數(shù)點實際上都是月份，所以直接取余即可；

SELECTename,hiredate,

TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)12)year,

TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12))monthsFROMemp；

第三步：求出天數(shù)，最準確的做法是在不超過30天的范圍之內(nèi)求；

現(xiàn)在已經(jīng)知道當前的時間使用SYSDATE取出，而雇傭的日期使用hiredate取出，可是

hiredate和SYSDATE之間的差距太大了，所以肯定會有誤差，那么就必須想辦法將

hiredate的11期提升到與SYSDATE差距在30天的范圍之內(nèi)。

在之前學習過兩個函數(shù)：

?MONTHS_BETWEEN()：求出兩個日期間的月數(shù)，如果是：

MONTHS_BETWEEN(SYSDATE,hiredate)求出的是雇傭日期到今天為止的雇傭月

份；

?ADD_MONTHS()：在一個II期上加入指定的月之后的II期,如果說hiredate+與

今天相距的月數(shù)=一個新的日期，而且這個新的日期肯定和SYSDATE相距不超過

30天。

SELECTenamezhiredate,

TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12)yea-,

TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate)z12))monthsz

TRUNC(SYSDATEADD_MONTHS(hiredatezMONTHS_BETWEEN(SYSDATE,hiredate)))dayFROM

emp；

以上的這道程序，屬于日期函數(shù)的綜合應(yīng)用。

第3-2章多表查詢

一、多表查詢

在之前所使用的查詢操作之中，都是從一張表之中查詢出所需要的內(nèi)容，那么如果現(xiàn)在一個

查詢語句需要顯示多張表的數(shù)據(jù),則就必須應(yīng)用到多表查詢的操作，而多表查詢的語法如下:

SELECT［DISTINCT］*|字段as［別名］［，字段as［別名］,...］

FROM表名稱［別名］,［表名稱［別名］,...］

［WHERE條件(S)］

15

[ORDERBY排序字段[ASC|DESC][,排序字段[ASC|DESC],...]];

條件(s)：e.deptno-d.deptnoand...

上課實例：

一查詢某部門員工姓名和部門名

selecte.ename,d.dname^e.deptnofromscott.empe,,scott.deptd

wheree.deptno=d.deptnoande.deptno=20;

--取出某員工姓名和所屬部門名

selecte.enamed.dname^e?deptnofromscott.empe,,scott.deptd

wheree.ename:'JONES'ande.deptno=d.deptno;

--子查詢

--查詢部門名稱包含A開頭的部門員工姓名和部門編號及部門名稱

selecte.enamed?deptnod.dnamefromscott.empe,scott.deptd

whered.deptnoin(selectdeptnofromscott.deptwhered.dnamelike'A%')

ande.deptno=d.deptno;

二、內(nèi)連接與外連接

表TESTA,TESTB,TESTC,各有A,B兩列

AB

00110A

00220A

AB

00110B

00330B

AB

00110C

00440C

連接分為兩種：內(nèi)連接與外連接。

A.內(nèi)連接

內(nèi)連接，即最常見的等值連接，例:

SELECT*

FROMTESTA.TESTB

WHERETESTA.A=TESTB.A

16

結(jié)果

ABAB

00110A00110B

上課實例：

一內(nèi)連接innerjoinon拿出兩張表里完全可以關(guān)聯(lián)的數(shù)據(jù)

select*fromempeinnerjoindeptdond.deptno=e?deptno;

B.外連接

外連接分為左外連接，右外連接和全外連接。

1.左外連接leftouterjoin或者leftjoin

左外連接就是在等值連接的基礎(chǔ)上加上主表中的未匹配數(shù)據(jù)，例：

SELECT*

FROMTESTA

LEFTOUTERJOINTESTB

ONTESTA.A=TESTB.A

Oracle支持另一種寫法

SELECT.

FROMTESTA,TESTB

WHERETESTA.A=TESTB.A(+)

結(jié)果:

ABAB

00110A00110B

00220A

三個表做左外連接

SELECT*

FROMTESTA

LEFTOUTERJOINTESTB

ONTESTA.A=TESTB.A

LEFTOUTERJOINTESTC

ONTESTA.A=TESTC.A

Oracle支持的另外一種寫法

SELECT*

FROMTESTA,TESTB,TESTC

WHERETESTA.A=TESTB.A(+)

ANDTESTA.A=TESTC.A(+)

結(jié)果:

ABABAB

00110A00110B00110C

00220A

2.右夕卜連接rightouterjoin或者rightjoin

右外連接是在等值連接的基礎(chǔ)上加上被連接表的不匹配數(shù)據(jù)

17

SELECT*

FROMTESTA

RIGHTOUTERJOINTESTB

ONTESTA.A=TESTB.A

Oracle支持的另一種寫法

SELECT*

FROMTESTA,TESTB

WHERETESTA.A(+)=TESTB.A

結(jié)果:

ABAB

00110A00110B

00330B

3.全外連接

fullouterjoin或者fulljoin

全外連接是在等值連接的基礎(chǔ)上將左表和右表的未匹配數(shù)據(jù)都加上

SELECT*

FROMTESTA

FULLOUTERJOINTESTB

ONTESTA.A=TESTB.A

全外連接的等價寫法，對同一表先做左連接，然后右連接

SELECTTESTA.*,TESTB.*

FROMTESTA

LEFTOUTERJOINTESTB

ONTESTA.A=TESTB.A

UNION

SELECTTESTA.*,TESTB.*

FROMTESTB

LEFTOUTERJOINTESTA

ONTESTA.A=TESTB.A

結(jié)果:

ABAB

00110A00110B

00220A

00330B

上課實例：

--外連接

--右連接rightjoinon

select*fromemperightjoindeptdone.deptno=d.deptno;

--左連接leftjoinon

select*fromempeleftjoindeptdone.deptno=d?deptno;

--全連接fullouterjoinon

select*fromempefullouterjoindeptdone.deptno=d.deptno;

18

補充：

a.交叉連接(CROSSJOIN)：用于產(chǎn)生笛卡爾積

SELECT*FROMempCROSSJOINdept;

笛卡爾枳本身并不是屬于無用的內(nèi)容，在某些情況下還是需要使用的。

b.自然連接(NATURALJOIN)：自動找到匹配的關(guān)聯(lián)字段，消除掉笛卡爾積

SELECT*FROMempNATURALJOINdept;

但是并不是所有的字段都是關(guān)聯(lián)字段，設(shè)置關(guān)聯(lián)字段需要通過約束指定；

CJOIN...USING子句：用戶自己指定一個消除笛卡爾積的關(guān)聯(lián)字段

SELECT*FROMempJOINdeptUSING(deptno);

dJOIN…ON子句：用戶自己指定?個可以消除笛卡爾積的關(guān)聯(lián)條件

SELECT*FROMempJOINdeptON(emp.deptno=dept.deptno);

e.連接方向的改變：

?左(夕卜)連接：LEFTOUTERJOIN...ON;

?右(外)連接：RIGHTOUTERJOIN...ON;

?全(外)連接：FULLOUTERJOIN.ON;-＞把兩張表中沒有的數(shù)據(jù)都顯示

SELECT*FROMempRIGHTOUTERJOINdeptON(emp.deptno=dept.deptno);

再次強調(diào)：多表查詢的性能肯定不高，而且性能一定要在大數(shù)據(jù)量的情況下才能夠發(fā)現(xiàn)。

第4章子查詢和組函數(shù)

一、子查詢

子查詢=簡單查詢+限定查詢+多表查詢+統(tǒng)計查詢的綜合體；

在之前強調(diào)過多表查詢不建議大家使用，因為性能很差，但是多表查詢最有利的替代者就是

子查詢，所以子查詢在實際的開發(fā)之中使用的相當?shù)亩啵?/p>

所謂的子查詢指的就是在一個查詢之中嵌套了其他的若干查詢，嵌套子查詢之后的查詢

SQL語句如下：

SELECT［DISTINCT］*分組字段1［別名］［，分組字段2［別名］，…］統(tǒng)計函數(shù)，(

19

SELECT［DISTINCT］*分組字段［［別名］［，分組字段2［別名］,...］統(tǒng)計函數(shù)

FROM表名稱［別名］,［表名稱［別名］,...］

［WHERE條件（S）］

［GROUPBY分組字段1［,分組字段2，…］］

［HAVING分組后的過濾條件（可以使用統(tǒng)計函數(shù)）］

［ORDERBY排序字段ASC|DESC［,排序字段ASC|DESC］］）

FROM表名稱［別名］,［表名稱［別名］,（

SELECT［DISTINCT］*分組字段1［別名］［，分組字段2［別名］，…］統(tǒng)計函數(shù)

FROM表名稱［別名］,［表名稱［別名］，…］

［WHERE條件