數(shù)據(jù)查詢和數(shù)據(jù)操作課件

車

蕾北京信息科技大學(xué)信息管理學(xué)院第6章數(shù)據(jù)查詢與數(shù)據(jù)操作主要內(nèi)容26.1數(shù)據(jù)查詢單表查詢連接查詢子查詢6.2數(shù)據(jù)操作插入數(shù)據(jù)刪除數(shù)據(jù)更新數(shù)據(jù)6.1

SQL查詢語(yǔ)句的基本結(jié)構(gòu)SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[,…n]

/*需要哪些列*//*把查詢結(jié)果/*來(lái)自于哪/*根據(jù)/*按什INTO<新表>保存到哪個(gè)新表*/

FROM<數(shù)據(jù)源>些表*/

[WHERE<檢索條件表達(dá)式>]什么條件篩選元組*/

[GROUP

BY<分組依據(jù)列>]么分組*/[HAVING<分組提取條件>]/*根據(jù)3

SELECT

<目標(biāo)列名序列>（需要哪些列）4（從哪些表）（根據(jù)什么條FROM

<表名序列>

WHERE<查詢條件>件）51.查詢指定列6查詢表中用戶感興趣的部分屬性列在SELECT子句的<目標(biāo)列表達(dá)式>中指定要查詢的屬性2.查詢?nèi)苛?查詢表中的所有屬性列有兩種方法：在SELECT子句后面列出所有列名

若列的顯示順序與其在原表中的順序相同，也可以簡(jiǎn)單地將<目標(biāo)列表達(dá)式>指定為“*”。3.查詢經(jīng)過(guò)計(jì)算的列8

SELECT子句的<目標(biāo)列表達(dá)式>不僅可以是表中的屬性列，也可以是算術(shù)表達(dá)式、字符串常量、函數(shù)等?？梢灾付▌e名來(lái)改變查詢結(jié)果的列標(biāo)題，這對(duì)于含算術(shù)表達(dá)式、常量、函數(shù)名的目9改變列標(biāo)題的語(yǔ)法格式為：列名|表達(dá)式[AS]列別名或列別名=列名|表達(dá)式101.消除取值相同的行11要去掉結(jié)果表中的重復(fù)行，可用DISTINCT實(shí)現(xiàn)2.查詢滿足條件的元組12

查詢滿足條件的元組可通過(guò)WHERE子句實(shí)現(xiàn)常用的查詢條件13（1）比較大小14(<,>,<=,>=,=,<>)（2）邏輯查詢15

邏輯查詢是由邏輯運(yùn)算符AND、OR、NOT及其組合作為條件的查詢。使用邏輯運(yùn)算符AND的一般格式為：

布爾表達(dá)式1

AND布爾表達(dá)式2

AND…AND布爾表達(dá)式n使用邏輯運(yùn)算符OR的一般格式為：

布爾表達(dá)式1

OR布爾表達(dá)式2

OR…OR布爾表達(dá)式n（3）確定范圍16BETWEEN

…

AND

…NOT

BETWEEN…

AND

…作用：查找屬性值在或不在指定范圍內(nèi)的元組說(shuō)明：BETWEEN后是范圍的下限（低值）AND后是范圍的上限（高值）（4）確定集合17作用：用來(lái)查找屬性值屬于指定集合的元組列名|表達(dá)式[NOT]IN(常量1,常量2,…,常量（5）字符匹配（LIKE）一般形式為：列名[NOT]LIKE匹配串作用：查找指定列名與匹配串常量匹配的元組。

匹配串類型：匹配串可以是字符串常量，也可以含有通配符。18注意：20

空值不是一個(gè)確定的值，所以不可以用等于或不等于來(lái)比較或衡量；

空值只能說(shuō)是空值（ISNULL）或不是空值（IS

NOT

NULL）。21使用Top限制結(jié)果集23TOP子句限制返回到結(jié)果集中的行數(shù)。格式：TOP

(n)

[

PERCENT

]

[

WITH

TIES

]n為非負(fù)整數(shù)；TOP(n)：表示取查詢結(jié)果的前n行；

TOP(n)PERCENT：表示取查詢結(jié)果的前n%行；

WITH

TIES：表示包括并列的結(jié)果。只能在

SELECT語(yǔ)句中且只有在指定ORDER

BY子句之后，才能使用“WITH

TIES”。分組與匯總查詢1.聚合函數(shù)與匯總函數(shù)24AVG25功能：返回表達(dá)式的平均值（忽略任何空值）。語(yǔ)法：AVG([ALL

|

DISTINCT]expression)參數(shù)說(shuō)明：

ALL：對(duì)所有的值進(jìn)行聚合函數(shù)運(yùn)算。ALL是默認(rèn)值。

DISTINCT：指定AVG只在每個(gè)值的唯一實(shí)例上執(zhí)行，而不管該值出現(xiàn)了多少次。expression：是精確數(shù)值或近似數(shù)值數(shù)據(jù)類別例：查詢AdventureWorks數(shù)據(jù)庫(kù)的Production.Product表中所有產(chǎn)品的平均銷售價(jià)格（ListPrice）SELECT

AVG(ListPrice)FROM

Production.Product26例：統(tǒng)計(jì)AdventureWorks數(shù)據(jù)庫(kù)的Production.Product表中的產(chǎn)品29SELECT

Count(DISTINCT

Color)FROM

Production.ProductMAX30功能：返回表達(dá)式的最大值（忽略任何空值）。對(duì)于字符列，MAX查找按排序序列排列的最大值。語(yǔ)法：MAX([ALL

|

DISTINCT]expression)參數(shù)說(shuō)明：同函數(shù)AVG的參數(shù)說(shuō)明。返回值類型：與表達(dá)式類型相同。例：查詢AdventureWorks數(shù)據(jù)庫(kù)的

Production.Product表中所有產(chǎn)品的最高銷售價(jià)格（ListPrice）31SELECT

MAX(ListPrice)FROM

Production.Product例：查詢AdventureWorks數(shù)據(jù)庫(kù)33的Production.Product表中所有產(chǎn)品的最低銷售價(jià)格（ListPrice）SELECT

MIN(ListPrice)FROM

Production.ProductSUM34

功能：返回表達(dá)式中所有值的和或僅非重復(fù)值的和（忽略任何空值）。SUM只能用于數(shù)字列。語(yǔ)法：SUM([ALL

|

DISTINCT]expression)參數(shù)說(shuō)明：同函數(shù)AVG的參數(shù)說(shuō)明。

返回值類型：以最精確的表達(dá)式數(shù)據(jù)類型返回所有表達(dá)式值的和例：查詢AdventureWorks數(shù)據(jù)庫(kù)的

Production.Product表中所有產(chǎn)品的銷售價(jià)格（ListPrice）總和35SELECT

SUM(ListPrice)FROM

Production.Product2.GROUP

BY分組查詢與計(jì)算36????

作用：可以控制計(jì)算的級(jí)別：對(duì)全表還是對(duì)一組。目的：細(xì)化計(jì)算函數(shù)的作用對(duì)象。分組語(yǔ)句的一般形式：SELECT<分組依據(jù)列>[,…n],<聚合函數(shù)>[,…n]FROM<數(shù)據(jù)源>[WHERE<檢索條件表達(dá)式>]

GROUP

BY<分組依據(jù)列>[,…n][HAVING<分組提取條件>]1.

GROUP

BY37

例1．統(tǒng)計(jì)每門課程的選課人數(shù)，列出課程號(hào)和人數(shù)SELECT

Cno,

COUNT(Sno)

FROM

SC

GROUPBY

Cno對(duì)查詢結(jié)果按Cno的值分組，所有具有相同Cno值的元組為一組，然后再對(duì)每一組使用COUNT計(jì)算，求得每組的學(xué)生人數(shù)。分組結(jié)果計(jì)算cno,count(sno)38計(jì)算cno,count(sno)計(jì)算cno,count(sno)計(jì)算cno,count(sno)計(jì)算cno,count(sno)分組查詢每名學(xué)生的選課門數(shù)和平均成績(jī)計(jì)算count(cno)avg(grade)計(jì)算count(cno)avg(grade)計(jì)算count(cnoavg(grade)計(jì)算count(cnoavg(grade)計(jì)算count(cno)avg(grade)計(jì)算count(cno)avg(grade)結(jié)果39查詢各系中年齡大于20歲的學(xué)生人數(shù)計(jì)算sdept,count(sn計(jì)算sdept,count(sn取滿足條件的（sage>20）分組結(jié)果40Where、Group

By和Having子句的作用和執(zhí)行順序41

Where子句用來(lái)篩選From子句中指定的數(shù)據(jù)源所產(chǎn)生的行；

Group

By子句用來(lái)對(duì)Where子句產(chǎn)生的結(jié)果進(jìn)行分組；Having子句用來(lái)對(duì)分組后的結(jié)果再進(jìn)行篩選。使用CASE子句對(duì)查詢結(jié)果進(jìn)行分析43將兩個(gè)或更多查詢的結(jié)果集組合為單個(gè)結(jié)果集，該結(jié)果集包含聯(lián)合查詢中的所有查詢的全部行。SELECT語(yǔ)句1UNION

[ALL]SELECT語(yǔ)句2UNION

[ALL]…SELECT語(yǔ)句n44合并查詢使用UNION的兩個(gè)基本規(guī)則是：45

所有查詢語(yǔ)句中列的個(gè)數(shù)和列的順序必須相同

所有查詢語(yǔ)句中對(duì)應(yīng)列的數(shù)據(jù)類型必須兼容。

SELECT

INTO語(yǔ)句創(chuàng)建一個(gè)新表，并用

SELECT的結(jié)果集填充該表。新表的結(jié)構(gòu)由選擇列表中表達(dá)式的特性定義。SELECT

INTO子句的大致格式為：SELECT子句INTO<新表>FROM<數(shù)據(jù)源>…46保存查詢結(jié)果到新表這個(gè)語(yǔ)句包含兩個(gè)功能：47根據(jù)查詢語(yǔ)句創(chuàng)建一個(gè)新表。

執(zhí)行查詢語(yǔ)句并將查詢的結(jié)果保存到新表中。被插入表的類型48臨時(shí)表局部臨時(shí)表：#新表名局部于當(dāng)前連接全局臨時(shí)表：##新表名可在所有連接中使用永久表：新表名存儲(chǔ)在磁盤上6.1.2連接查詢49

若一個(gè)查詢同時(shí)涉及兩個(gè)或兩個(gè)以上的表，則稱之為連接查詢。連接查詢是關(guān)系數(shù)據(jù)庫(kù)中最主要的查詢連接查詢包括內(nèi)連接、外連接和交叉連接等。表別名50表別名的作用

一是可以簡(jiǎn)化表名的書(shū)寫，特別是當(dāng)表名比較長(zhǎng)或是中文時(shí)；

二是在自連接（節(jié)中介紹）中要求必須為表名指定表別名。格式：<表名>[AS]<表別名>內(nèi)連接51<比較運(yùn)算符>常用的內(nèi)連接語(yǔ)法格式為：FROM

表1

[

INNER

]

JOIN

表2

on[<表名1>.]<列名1>[<表名2>.]<列名2>1.一般內(nèi)連接52本信息及其修課情況表。532自連接54為特殊的內(nèi)連接相互連接的表物理上為同一張表。

必須為兩個(gè)表取別名，使之在邏輯上成為兩個(gè)表。注意Where子句的使用系系的s1

學(xué)生的姓名和所在s2連接where

s1.sname="劉晨"and

s2.sname!="劉晨"55例2：雇員號(hào)雇員姓名經(jīng)理E3趙涌NullE4錢潮E3E6孫潔E3E8李楠E6經(jīng)理姓名領(lǐng)導(dǎo)雇員姓名趙涌領(lǐng)導(dǎo)錢潮趙涌領(lǐng)導(dǎo)孫潔孫潔領(lǐng)導(dǎo)李楠56E1E2E1.雇員號(hào)=E2.經(jīng)理經(jīng)理姓名57自連接查詢已知雇員關(guān)系：雇員（雇員號(hào)，雇員姓名，經(jīng)理）請(qǐng)根據(jù)此關(guān)系列出經(jīng)理及其雇員（被其領(lǐng)導(dǎo)）的清單。58例3：查詢每一門課的間接選修課，列出：課程號(hào)，間接選修課編號(hào)59CnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27PASCAL語(yǔ)言64Select

o,second.cpnoFrom

course

first

join

course

secondOn

first.cpno=o60Cnopcno173556外連接61

外連接會(huì)返回FROM子句中提到的至少一個(gè)表的所有行，只要這些行符合任何WHERE或

HAVING搜索條件。?語(yǔ)法格式：FROM表1

LEFT

|

RIGHT

|

FULL[OUTER]JOIN表2

ON<連接條件>

左連接的含義是不管表1中的元組是否滿足連接條件，均輸出表1的元組；如果是在連接條件上匹配的元組，則表2返回相應(yīng)值，否則表2返回空值。

右連接的含義是不管表2中的元組是否滿足連接條件，均輸出表2的元組；如果是在連接條件上匹配的元組，則表1返回相應(yīng)值，否則表1返回空值。626.1.3子查詢63

在SQL語(yǔ)言中，一個(gè)SELECT－FROM－WHERE語(yǔ)句稱為一個(gè)查詢塊。

子查詢是一個(gè)SELECT查詢，它嵌套在

SELECT、INSERT、UPDATE、DELETE語(yǔ)句的WHERE或HAVING子句內(nèi)，或其它子查詢中

子查詢的SELECT查詢總是使用圓括號(hào)括起來(lái)包括子查詢的語(yǔ)句通常采用以下格式中的一種64WHERE/HAVING表達(dá)式[NOT]IN(子查詢)

WHERE/HAVING

表達(dá)式比較運(yùn)算符（子查詢）WHERE/HAVING

[NOT]EXISTS（子查詢）帶IN的子查詢使用IN運(yùn)算符的子查詢的語(yǔ)法格式為：WHERE

表達(dá)式

[NOT] IN

(子查詢)

執(zhí)行順序是：先執(zhí)行子查詢，然后在子查詢的結(jié)果基礎(chǔ)上再執(zhí)行外層查詢。

子查詢返回的結(jié)果是僅包含單個(gè)列的集合，外層查詢就是在這個(gè)集合上使用IN運(yùn)算符進(jìn)行比較，所以子查詢中SELECT子句里只能有一個(gè)目標(biāo)列表達(dá)式，并且外層查詢中使用IN運(yùn)算符的列要與該目標(biāo)列表達(dá)式的數(shù)據(jù)類型65

相同、語(yǔ)帶有比較運(yùn)算符的子查詢66使用比較運(yùn)算符的子查詢的語(yǔ)法格式為：WHERE表達(dá)式比較運(yùn)算符(子查詢)

比較運(yùn)算符可以是：=、<、>、<=、>=等。子查詢一定要在比較運(yùn)算符后面。

執(zhí)行順序是：先執(zhí)行子查詢，然后在子查詢的結(jié)果基礎(chǔ)上再執(zhí)行外層查詢。6.2數(shù)據(jù)操作67插入數(shù)據(jù)刪除數(shù)據(jù)更新數(shù)據(jù)插入一