計(jì)算機(jī)軟件及應(yīng)用SQL語(yǔ)言

課程目標(biāo)

學(xué)習(xí)如下內(nèi)容

函數(shù)的使用

>字符串函數(shù)、算術(shù)函數(shù)、日期函數(shù)、統(tǒng)計(jì)函數(shù)

>其它函數(shù)

SQL數(shù)據(jù)查詢語(yǔ)言

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

>INSERT>UPDATE>DELETE語(yǔ)句

查詢結(jié)果的集合運(yùn)算

>UNION>INTERSECT、MINUS

?事務(wù)控制命令

SQL函數(shù)的類型（P.27）

函數(shù)

單行(Single-row)移行(Multiple-row)

函數(shù)-A函藪

SQL函數(shù)的類型

字符串函數(shù)

算術(shù)函數(shù)

?：?日期函數(shù)

?：?統(tǒng)計(jì)函數(shù)

其它函數(shù)

單行函數(shù)

＞操作數(shù)據(jù)項(xiàng)

＞接受參數(shù)返回一個(gè)值

＞作用于返回的每一行

＞每行返回一個(gè)結(jié)果

＞可以改變數(shù)據(jù)類型

＞可嵌套使用

functionname(column|expression,[argl,arg2,...])

字符串函數(shù)

函數(shù)返回值

ASCn(char)字符串首字符的ASC碼值

CHR(n)ASC碼值為n的字符

CONCAT(strl,str2)把兩個(gè)列值拼接起來(lái)；“『操作符更通用

INITCAP(char)將字符串中每個(gè)單詞的首字母大寫

INSTR(char,c)返回指定字符的位置

LENGTH(char)計(jì)算字符串的長(zhǎng)度

SUBSTR(char,m[,n])求子串。column中從起始位置m開始長(zhǎng)度

為n的子串

Replace(str1,str2,str3)

字符串函數(shù)

LOWER(char)、將字符串中所有的大寫字母變?yōu)樾?/p>

UPPER(char)（大）寫

LPAD(char1,n[,char2])從左側(cè)用字符串char2補(bǔ)齊字符串

char1至長(zhǎng)度n（右對(duì)齊）

RPAD(char1,n[,char2])從右側(cè)用char2補(bǔ)齊charl至長(zhǎng)度n.

LTRIM(char[,SET])把char中最左側(cè)的若干個(gè)字符去掉，

以使其首字符不在SET中

RTRIM(char[,SET])把char中最右側(cè)的若干個(gè)字符去掉，

以使其尾字符不在SET中

TO_DATE(char[?fmt])將字符型的日期轉(zhuǎn)換為日期型

TONUMBER(char[,f將字符串轉(zhuǎn)換為數(shù)字

mt])

字符處理函數(shù)示例（1）

函數(shù)結(jié)果

CONCAT(TGood!,1String?)GoodString

SUBSTR。String11,3)Str

LENGTH。StringI6

INSTR(!String1,fr?)3

LPAD(sal,10/*T)******5000

TRIM(SFROM!SSMITHJMITH

字符處理函數(shù)示例(2)

selectempno,lower(ename),IsDoing；initcap(job)fromemp;

selectempno,concat(concat(concat(ename/^conca^lsVf))job)

fromemp;

selectempno,substr(ename,l,l)||7||lower(ename),substr(job,293)

fromemp;

selectempno,lpad(empno,10/#1)fromemp;

算術(shù)函數(shù)

函數(shù)返回值

ABS(n)返回n的絕對(duì)值

CELL(n)返回大于或等于n的最小整數(shù)

EXP(n)返回e的n次幕

FLOOR(n)返回小于或等于n的最大整數(shù)

LN(n)返回以E為底的n的對(duì)數(shù)

LOG(m,n)返回以m為底的n的對(duì)數(shù)

MOD(m,n)返回m除以n的余數(shù)

PI()返回常數(shù)3.1415926535897931

POWER(m,n)返回m的n次方

算術(shù)函數(shù)

ROUND(m[,n])對(duì)m進(jìn)行四舍五入（n大于0時(shí)，將m四舍

五入到小數(shù)點(diǎn)右邊n位，n等于零時(shí)，表示

對(duì)m進(jìn)行取整，n小于0時(shí)，則小數(shù)點(diǎn)左邊

的數(shù)字位置被圓整。

SIGN(n)判斷n的正負(fù)（n大于0返回，n等于0返回0,

n小于0返回-1

SQRT(n)返回n的平方根

SQUARE(n)返回n的平方

TRUNC(m[,n])對(duì)m進(jìn)行截?cái)嗖僮鳎╪截?cái)嗟叫?shù)點(diǎn)后第n

位，如果n未給出，則系統(tǒng)默認(rèn)為0,n也可

以為負(fù)數(shù)，表示小數(shù)點(diǎn)左邊的數(shù)字位置被

刪除成零。

算術(shù)函數(shù)示例（1）

ROUND:四舍五入

TRUNC:截取

MOD:模

算術(shù)函數(shù)示例（2）

selectsal/22fromemp;

selectename,round(sal/22,0),round(sal/22,-1)fromemp;

selectround(20,-2),round(35,-1)fromdual;

selecttrunc(35,-l),trunc(12356.67,-2)fromdual;

日期函數(shù)

默認(rèn)的日期格式：DD-MON-YY

函數(shù)返回值

ADD_MONTHS(d,n)返回日期d添加n個(gè)月的日期

LASTDAY(d)返回d所在月份最后一天的日期

MONTHS_BETWEEN(dl,d2)日期dl和d2之間相隔的月數(shù)

NEXT_DAY(d,day)d后第一周指定的日期。

ROUND(d,format)日期d按format格式進(jìn)行舍入

TRUNC(d,format)返回截尾到由format指定單位

的日期上

SYSDATE返回系統(tǒng)當(dāng)前日期和時(shí)間。

使用日期函數(shù)示例(1)

MONTHS_BETWEENfOI-SEP-95','11-JAN-94')

-------?19.6774194

ADD_MONTHS('11-JAN-94',6)—'11-JUL-94,

NEXT_DAT("01-SEP-95','FRIDAT')—'08?SEP-95'

LAST_DAr('O1-SEP-95')—'30-SEP-95'

ROUND(,25-JUL-95,,?MONTH')-?01-AUG-95

ROUND('25-JUL-gS\'YEAR')-a01-JAN-96

TRUNC('25?JUL-95','MONTH')—201-JUL-95

TRUNC('25?JUL?95','YEAR')-a01-JAN-95距

日期函數(shù)示例（2）

selectsysdatefromdual;

selectlast_day(sysdate)fromdual;

selectadd_months(sysdate,2)fromdual;

selectmonths_between(sysdate/12-3月-03")

fromdual;

selectnext_day(sysdate,1)fromdual;

日期運(yùn)算總結(jié)

運(yùn)算結(jié)果描述

日期十?dāng)?shù)字日期在某個(gè)日期上添加指定數(shù)量的日

日期?數(shù)字日期|在某個(gè)日期上減去指定數(shù)量的日

日期■日期日的數(shù)番兩個(gè)日期之間相差的日數(shù)

日期十小時(shí)數(shù)/24日期在某個(gè)日期上添加指定數(shù)量的小時(shí)

日期和日期之間不能執(zhí)行加運(yùn)算

數(shù)據(jù)類型轉(zhuǎn)換函數(shù)

TOCHAR(d,format)

TO_CHAR(num,format)

TO_NUMBER(string,format)

TO_DATE(string,format)

使用數(shù)據(jù)類型轉(zhuǎn)換函數(shù)

,,

selectempno?ename,to_char(hiredate,yyyy-mm-dd)

fromemp;

selectto_char(sysdate,,yy,)fromdual;

createtabletest(dldate);

insertintotestvalues，19-10月-899)

select*fromtestwhere(to_char(dl/yy,)=89)

selectempno,ename,hiredate,to_char(sal/$99,999,)as

salaryfromemp;

selectto_char(sysdate/dd-mm-yy:hh:mi:ss,)fromdual;

selecttodateC*101319607mmddyyyy,)fromdual;

其它函數(shù)

NVL函數(shù)：

A功能：把可能包含了空值的表達(dá)式1,轉(zhuǎn)換成有實(shí)際意義的

數(shù)據(jù)表達(dá)式2o

A格式：NVL(表達(dá)式1,表達(dá)式2)

A作用的數(shù)據(jù)類型：可用于字符型、數(shù)值型、日期型.

A注意事項(xiàng)：轉(zhuǎn)換前后的類型必須一致：

-NVL(comm,0)

-NVL(hiredate,'O1-JAN-97")

-NVLUob,'NoJobYet')

DECODE函數(shù)

?：?類似于其他語(yǔ)言的或者

,用于分支判斷

DECODE(col/expressionsearchi,resultl

[,search2,result2f...,]

[,default])

decode函數(shù)介紹

語(yǔ)法：

DECODE(expression,value1,returnedvalue1,...

valueN,returned_valueN

[defaultreturned_value])

含義：

當(dāng)expression取值為valueN時(shí)，DEC0DE函數(shù)的返回值為

returnedvalueN

defaultreturnedvalue可選項(xiàng)，當(dāng)expression不等于

Valuel至valueN的值時(shí)，DECODE函數(shù)的返回值為

defaultreturned_value。

Selectempno,initcap(enae),sal+nvl(comm,0)astotaIsa1

Fromemp

Orderbytotalsal;

EMPNOINITCAP(ENTOTALSAL

7369Smith800

7900James950

7876Adams1100

7934Miller1300

7844Turner1500

7521Ward1750

7499Allen1900

7782Clark2450

例1

Selecteno,cname,tno

decode(tno,825,4ZE萍',804，'李誠(chéng)，，856,'李旭'，）

Fromcourse;

enocnametnoDEC

3-105計(jì)算機(jī)導(dǎo)論825王萍

3-245操作系統(tǒng)804李誠(chéng)

6-166數(shù)字電路856李旭

9-888高等數(shù)學(xué)825王萍

例2:

selectsum(sal)fromemp

groupbydecode(&xx,1,job,2,deptno)

輸入xx的值：2

原值2:groupbydecode(&xx,l,job,2,deptno)

新值2:groupbydecode(2,l,job,2,deptno)

SUM(SAL)

8750

10875

9400

統(tǒng)計(jì)函數(shù)

函數(shù)格式功能

AVGAVG（［DISTINCT|ALL］v列名〉）計(jì)算一列值的平均值（要求數(shù)值

列）

COUNTCOUNT（［DISTINCT|ALL］*）統(tǒng)計(jì)元組個(gè)數(shù)

COUNTCOUNT（［DISTINCT|ALL］<歹U統(tǒng)計(jì)一列中值的個(gè)數(shù)

名〉）

MAXMAX（［DISTINCT|ALL］〈歹U名〉）求一列值中的最大值

MINMIN（［DISTINCT|ALL］v列名〉）求一列值中的最小值

SUMSUM（［DISTINCT|ALL］〈歹U名〉）計(jì)算一列值的總和（要求數(shù)值列）

STDDESTDDEV（〈歹名〉）?,計(jì)算一列值的標(biāo)準(zhǔn)差

V

VARIANVARIANCE（〈列名〉）計(jì)算一列值的方差

CE

SQL數(shù)據(jù)查詢語(yǔ)言

查詢語(yǔ)句的一般結(jié)構(gòu)

單表查詢

多表查詢

嵌套查詢

分組查詢

集合查詢

查詢語(yǔ)句的一般結(jié)構(gòu)

孝命令：SELECT

格式:

SELECT[ALL|DISTINCT]＜目標(biāo)列表達(dá)式＞[,＜目標(biāo)列表達(dá)式

習(xí)…

FROM〈表名或視圖名＞[,〈表名或視圖名刁…

A[HEREv條件表達(dá)式刁

>[GROUPBY〈歹名1>[HAVING<條件表達(dá)式>]]

>[ORDERBY〈歹lj名2>[ASC|DESC]]；

描述:

＞根據(jù)WHERE子句的條件表達(dá)式，從FROM子句指定的基本表或

視圖中找出滿足條件的元組，再按SELECT子句中的目標(biāo)列表達(dá)

式，選出元組中的屬性值形成結(jié)果表。如果有GROUPBY子句

則將結(jié)果按〈列名”的值進(jìn)行分組，該屬性列相等的元組為一個(gè)

組。通常會(huì)在每組中作用集函數(shù)。如果GROUP子句帶有

HAVING短語(yǔ)，則只有滿足指定條件的組才予輸出。如果有

ORDERBY子句，則結(jié)果表還要按〈列名2＞的值升序或降序排列。

單表查詢

?無(wú)條件查詢

有條件查詢

排序

單表查詢-無(wú)條件查詢(1)

查詢所有列

>SQL>selectdept:

查詢選若干列

ASQL>selectdeptnodnamefromdept;

ASQL>selectempno,sal*0.18fromemp;

單表查詢-無(wú)條件查詢(2)

?：?SELECT中的〈目標(biāo)表達(dá)式＞可以是表中的列，也可

以是表達(dá)式，包括算術(shù)表達(dá)式、字符串常數(shù)、函數(shù)

等。(字符串用單引號(hào)定界)。

1.使用算術(shù)表達(dá)式(+、?、*、/)

Selectempno,fromemp;

2.使用字符常量

Selectempno,,enamefromemp;

3.使用函數(shù)

Selectempno,(ename)fromemp;

單表查詢-無(wú)條件查詢(3)

4.改變列標(biāo)題

Selectempno9sal,

job,deptfromemp;

5.使用連接字符串

Selectename||||'1"||"monthsalary=|||sal

asmonthyfromemp

6.消除重復(fù)行

>SQL>selectenamefromemp;

>SQL>selectenamefromemp;

單表查詢-有條件查詢(1)

查詢滿足條件的元組可以通過(guò)WHERE子句實(shí)現(xiàn)。

>［例］:

selectempno,ename,salfromemp;

>結(jié)果

EMPNOENAMESAL

7566JONES2975

7698BLAKE2850

7782CLARK2450

7788SCOTT3000

7839KING5000

7902CLARK3000

已選擇6行。

單表查詢-有條件查詢(2)

WHERE常用的查詢條件

查詢謂詞

條件

比較大小二,>,V，>=,<=,<>

確定范圍BETWEENAND,NOTBETWEEN

AND

確定集合IN,NOTIN

字符匹配LIKE,NOTLIKE

空值ISNULL,ISNOTNULL

多重條件AND,OR

單表查詢-有條件查詢(3)

1.比較大小

>selectempno9ename9salfromempwheredeptno=10;

>Selectempno代name9salfromempwheresal>1500

2■確定范圍

?謂詞BETWEENAND與NOTBETWEENAND°

。selectempno9ename9sal9deptnofromempwhere

deptno1020

?selectempno,ename,sal,deptnofromempwheresal

10002000

單表查詢-有條件查詢(4)

3.確定集合

?：?謂詞IN可以用來(lái)查找屬性值屬于指定集合

的元組。

?selectempno9ename9sal9deptnofromemp

wheredeptno(10,30)；

單表查詢-有條件查詢（5）

4.字符匹配

?謂詞可以用來(lái)進(jìn)行字符串的匹配

?一般格式：[NOT]LIKEY匹配串丁

三注釋

?！雌ヅ浯悼梢允且粋€(gè)完整的字符串，也可以含

有通配符的字符串。通配符包括'%，、"Jo

。%（百分號(hào)）代表任意長(zhǎng)（長(zhǎng)度為0）字符串。

?_（下劃線）代表任意單個(gè)字符。

?selectempno9ename9sal9deptnofromempwhere

enamelike'S%';

單表查詢-有條件查詢(6)

5■空值___________________

?：?涉及空值查詢時(shí)使用ISNULL或ISNOT

NULL,這里的IS不能用=替代。

。selectempno9ename9sal9deptnofromemp

wherecomm

?selectempno9ename9sal9deptnofromemp

wherecomm

單表查詢-有條件查詢(7)

6■多重條件

?用邏輯運(yùn)算符NOT、AND和OR來(lái)聯(lián)結(jié)多個(gè)查詢條件。

。優(yōu)先級(jí)：NOT、AND、OR(用戶可以用括號(hào)改變優(yōu)先級(jí))。

IN謂詞實(shí)際上是多個(gè)OR運(yùn)算的縮寫。

。selectempno9ename9sal9deptnofromempwhere

commisnotnullsal>1500;

?selectename,job,salfromemp

where(job=fSALESMAN1orjob=6CLERK1)

andsal>1500;

單表查詢-有條件查詢(8)

7.SELECT的交互查詢

>使用替代變量

>Definedepartnumber=20;

>Select*fromempwheredeptno=

&departnumber;

查詢排序（1）

?：?用ORDERBY從句排序

>ASC:升序（缺?。?/p>

>DESC:降序

>表達(dá)式排序

>多列排序

>別名排序

>ORDERBY從句要放在SELECT語(yǔ)句的最后.

查詢排序（2）-升序

±OracleSQL+Plus

文件⑹編輯（日搜索⑤選項(xiàng)@幫助⑻

SQL>selectempno,ename,job,salfromemp

2orderbysal;

EMPNOENAMEJOBSAL

7369SMITHCLERK800

7900JAMESCLERK950

7876ADAMSCLERK1100

7521WARDSALESMAN1250

7654MARTINSALESMAN1250

7934MILLERCLERK1300

7844TURNERSALESMAN1500

7499ALLENSALESMAN1600

7782CLARKMANAGER2450

7698BLAKEMANAGER2850

7566JONESMANAGER2975

7788SCOTTANALYST3000

7902FORDANALYST3000

7839KINGPRESIDENT5000

己選擇1咯。默認(rèn)情況下是升序排列

查詢排序（3）-降序

土OracleSQL*Plus

文件（E）編輯（日搜索（分選項(xiàng)（Q）幫助（由

1selectenpno,ename,job,salFromemp

2*orderbysaldesc

EMPNOENAMEJOBSAL

7839KINGPRESIDENT5000

7788SCOTTANALYST3000

7902FORDANALYST3000

7566JONESMANAGER2975

7698BLAKEMANAGER2850

7782CLARKMANAGER2450

7499fiLLENSALESMAN1600

7844TURNERSALESMAN1500

7934MILLERCLERK1300

7521WARDSALESMAN1250

7654MARTINSALESMAN1250

7876ADAMSCLERK1100

7900JAMESCLERK950

7369SMITHCLERK800

己選擇1咯。

查詢排序（4）-表達(dá)式排序

±OracleSQL*Plus

文件⑹編輯⑹搜索⑸選項(xiàng)⑼幫助出

SQL>selectename,hiredate.deptno,,sal*12

2Fromemp

3orderbysal*12desc;

ENAMEHIREDATEDEPTNOSALSAL*12

KING17-11月-8110500060000

SCOTT19-4月-8720300036000

FORDG3-12月-8120300036000

024目

JONES--8120297535700

01-5m.

BLAKE-8130285034200

09目

-^6

CLARK20-2m.-8110245029400

ALLEN-8130160019200

08-9目

TURNER23-1m.-8130150018000

MILLER2--8210130015600

22目

-

WARD289m.-8130125015080

MARTIN23--8130125015000

5nR

ADAMS.-8720110013208

JAMESG3-12月-813095011400

SMITH17-12月-80208009600

己選擇行。

查詢排序（5）-多列排序

±OracleSQL*Plus

文件（日編輯（日搜索選項(xiàng)（6幫助（由

SQL>selectename,sal,deptno

2Fronemp

3orderbydeptno.saldesc;

ENAMESfiLDEPTNO

KING500010

CLARK245010

MILLER130010

SCOTT380020

FORD300020

JONES297520

ADAMS110020

SMITH80020

BLAKE285030

ALLEN160030

TURNER150030

WARD125030

MARTIN125030

JAMES95030

己選擇14行。

查詢-排序（6）-別名排序

土OracleSQL*Plus

文件（日編輯（巳搜索（切選項(xiàng)◎幫助（d）

SQL>selectempno,ename,sal*12annsal

2fromemp

3orderbyannsal;

EMPNOENAMEANNSAL

7369SMITH9600

7900JAMES11400

7876ADAMS13200

7521WARD15000

7654MARTIN15600

7934MILLER15600

7844TURNER18000

7499ALLEN19200

7782CLARK29400

7698BLAKE34200

7566JONES35700

EMPNOENAMEANNSAL

7788SCOTT36000

7902FORD36000

7839KING60000

己選擇1嶺亍。

常用的統(tǒng)計(jì)函數(shù)

函數(shù)格式功能

AVGAVG（［DISTINCT|ALL］v歹ij名〉）計(jì)算一列值的平均值（要求數(shù)

值列）

COUNTCOUNT（［DISTINCT|ALL］*）統(tǒng)計(jì)兀組個(gè)數(shù)

COUNTCOUNT（［DISTINCT|ALL］〈歹U名統(tǒng)計(jì)一列中非空值的個(gè)數(shù)

＞）

MAXMAX（［DISTINCT|ALL］v列名》）求一列值中的最大值

MINMIN（［DISTINCT|ALL］v列名〉）求一列值中的最小值

SUMSUM（［DISTINCT|ALL］〈歹U名＞）計(jì)算一列值的總和（要求數(shù)值

列）

STDDEVSTDDEV（〈歹U名〉）..計(jì)算一列值的標(biāo)準(zhǔn)差

VARIANCEVARIANCE。!^〉）計(jì)算一列值的方差

只有COUNT（*）函數(shù)不忽略空值。

例1：求出表emp中所有雇員的平均工資

SQL>selectavg(sal)fromemp;

AVG(SAL)

2073.2143

例2：計(jì)算工資在1000.3000之間的雇員人數(shù)

SQL>selectcount(empno)fromempwheresal

between1000and3000;

COUNT(EMPNO)

11

AVG,SUM

?函數(shù)AVG和SUM應(yīng)用于數(shù)值型.

SQL>SELECTAVG(sal),MAX(sal),

2MIN(sal)zSUM(sal)

3FROMemp

4WHEREjobLIKESALES%1;

MIN,MAX

MIN和MAX可用于任意類型.

SQL>SELECTMIN(hiredate),MAX(hiredate)

COUNT

?：?COUNT(*)返回符合條件的記錄數(shù).

SQL>SELECTCOUNT(*)

2FROMemp

3WHEREdeptno=30;

COUNT(叩加返回的的行數(shù).

SQL>SELECTCOUNT(comm)

2FROMemp

3WHEREdeptno=30;

集合函數(shù)與Null

?集合函數(shù)忽略列中的null值（COUNT除外）.

SQL>SELECTAVG(comm)

2FROMemp;

在集合函數(shù)中可以使用NVL函數(shù)

NVL函數(shù)強(qiáng)制集合函數(shù)包括null值.

SQL>SELECTAVG(NVL(comm,0))

2FROMemp;

r

分組查詢(1)

將查詢結(jié)果表按某一列或多列值分組，值

相等的為一組。

EMP

DEPTNOSAL

102450

1050002916.6667

101300

2080DEPTNOAVG(SAL)

20110

20300

20300

20297

30160

30285

30125

3095

30150

30125

分組查詢(2)

SELECT[column^group_function(column)

FROMtable

[WHEREcondition]

[ORDERBYcolumn];

分組查詢(3)

A如果未對(duì)查詢分組，集函數(shù)將作用于整個(gè)查

詢結(jié)果；如果分組后集函數(shù)將作用于每一個(gè)組,

即每一個(gè)分組都有一個(gè)集函數(shù)。

A在包含GROUPBY子句的查詢語(yǔ)句中，

SELECT子句后面的(除聚集函

數(shù)外)，均應(yīng)該包含在GROUPBY子句中。

SQL>SELECTdeptno,AVG(sal)

2FROMemp

3GROUPBYdeptno;

分組查詢(4)

A［例］計(jì)算每個(gè)工種的平均工資

SOL>SELECTJOB,AVG(SAL)FROMEMP

GROUPBYJOB;

A結(jié)果

JOBAVG(SAL)

ANALYST3000

CLERK950

MANAGER2758.33333

PRESIDENT5000

SALESMAN1400

分組查詢(5)

。GROUPBY子句

>［例］查詢每個(gè)部門各工種的雇員數(shù)

SOL>SELECTJOB,DEPTNO,COUNTY*)

FROMEMPGROUPBYDEPTNO,JOB;

結(jié)果

JOBDEPCOUNT(*)

MANAGER101

PRESIDENT101

ANALYST202

CLERK202

MANAGER201

CLERK301

MANAGER301

SALESMAN304

分組查詢(6)

使用多個(gè)列分組

EMP

DEPTNOJOBSAL

10MANAGER2450

DEPTNOJOBSUM(SAL)

10PRESIDENT5000

10CLERK1300

10CLERK1300

20CLERK800

10MANAGER2450

20CLERK1100

10PRESIDENT5000

20ANALYST3000

20ANALYST6000

20ANALYST3000

20CLERK1900

20MANAGER2975

20MANAGER2975

30SALESMAN1600

30CLERK950

30MANAGER2850

30MANAGER2850

30SALESMAN1250

30SALESMAN5600

30CLERK950

30SALESMAN1500

30SALESMAN1250

分組查詢(7)

在GROUPBY從句中使用多個(gè)列

SQL>SELECTdeptno,job,sum(sal)

2FROMemp

3GROUPBYdeptno,job;;

DEPTNOJOBSUM(SAL)

10CLERK1300

10MANAGER2450

10PRESIDENT5000

20ANALYST6000

20CLERK1900

9rowsselected.

102450

105000

101300

20800

201100DEPTNOMAX(SAL)

203000

203000105000

202975203000

分組查詢(9)

?：?使用HAVING從句選擇滿足條件分組

執(zhí)行次序：

＞對(duì)行進(jìn)行分組.

＞對(duì)每組數(shù)據(jù)執(zhí)行組函數(shù).

＞返回符合HAVING從句的分組結(jié)果.

SELECTcolumn,group_function

FROMtable

[WHEREcondition]

[GROUPBYgroup_by_expression]

[HAVINGgroup_condition]

[ORDERBYcolumn];

WHERE與HAVING

行作用對(duì)象不同。

WHERE作用于基本表或視圖，從中選擇

滿足條件的元組

HAVING短語(yǔ)作用于組，從中選擇滿足條

件的組。

［例］查詢平均工資超過(guò)2000的部門，并按部門號(hào)進(jìn)行排序

SQL>SELECTdeptno,avg(sal)fromemp

_____GROUPBYdeptno

HAVINGAVG(SAL)>2000

ORDERBYdeptno;

A結(jié)果

DEPTNOAVG(SAL)

ITT-------3725--------------------

202175

selectdeptno,max(sal)

fromemp

groupbydeptno;

selectename9deptno,max(sal)

fromemp、?

groupbydeptno;

selectename,sal

fromemp

where(deptno,sal)IN

;該語(yǔ)句執(zhí)行什么結(jié)果?

(selectdeptno,max(sal)

fromemp

groupbydeptno);

SQL>selectdeptnovmax(sal)

2Fromenp

3groupbydeptno;

DEPTNOMAX(SAL)

105000

203000

302850

SQL>select:ename,deptno,max(sal)

2Frontemp

3groupbydeptzno;

selectename,deptzno,max(sal)

ERROR位于笫1行：

ORA-00979：不是GROUPBV表達(dá)式

?在包含GROUPBY子句的查詢語(yǔ)句中，SELECT子

句后面的所有字段列表（除聚集函數(shù)外），均應(yīng)該包

含在GROUPBY子句中。（必須保證是有意義的前

提下）

SQL>selectename,deptno,sal

2Fromemp

3where(deptno,sal)IN

H(selectdeptno,max(sal)

5Fromenp

6groupbydeptno);

ENAMEDEPTNOSAL

KING105000

SCOTT203000

FORD203000

BLAKE302850

Sql>selectdeptno,avg(sal)

fromemp;

從多個(gè)表中得到數(shù)據(jù)

EMPDEPT

DEPTNODNAMELOC

10ACCOUNTINGNEWYORK

20RESEARCHDALLAS

30SALESCHICAGO

40OPERATIONSBOSTON

EMPNODEPTNOLOC

1o

78393oNEWYORK

76981oCHICAGO

77822oNEWYORK

75663oDALLAS

76543oCHICAGO

7499CHICAGO

14rowsselected.

連接(Join)是什么？

使用連接(join)從多個(gè)表中查詢數(shù)據(jù).

SELECTtablel.column,table2.column

FROMtablel,table2

WHEREtablel.columnl=table2.column2;

＞連接條件寫在WHERE從句中.

＞同一列名出現(xiàn)在多個(gè)表中時(shí)，列名前要

加上表名來(lái)區(qū)分.

連接查詢

。連接操作是從笛卡爾積中選擇滿足條件的元組

?:?連接類型：

?交叉連接：笛卡爾積。

?內(nèi)連接：使用比較運(yùn)算符進(jìn)行表間某列或某

些列數(shù)據(jù)的比較操作，并列出這些表中與連

接條件相匹配的數(shù)據(jù)行。內(nèi)連接分為

、和O

?外連接：外連接不僅要列出與連接條件相匹

配的數(shù)據(jù)行，還要列出左表、右表或兩個(gè)表

中所有不符合查詢條件的數(shù)據(jù)行。外連接分

為、利。

笛卡爾(Cartesian)積

＞以下情況可以出現(xiàn)笛卡爾積：

W連接條件省略

-連接條件非法

b一個(gè)表的所有行被連接到另一個(gè)表的

所有行

＞為了避免笛卡爾積，一定要在WHERE

從句中包含合法的連接條件。

>SQL>SELECTencnne,dnamefromemp,dept;

生成笛卡爾積

EMP（14行）DEPT（4行）

EMPNOENAME…DEPTNODEPTNODNAMELOC

???

7839KING…1010ACCOUNTINGNEWYORK

7698BLAKE…3020RESEARCHDALLAS

???30SALESCHICAGO

7934MILLER…1040OPERATIONSBOSTON

ENAMEDNAME

KINGACCOUNTING

“笛卡爾積：BLAKEACCOUNTING

14*4=56行"f???

KINGRESEARCH

BLAKERESEARCH

???

56rowsselected.

內(nèi)連接

等值與非等值連接查詢

>連接條件的一般格式是：

>［〈表名1〉.］<列名1>［〈表名2>.］<列名2>

>也可以采用下面的形式：