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

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

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子句后面列出所有列名

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

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

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

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

布爾表達(dá)式1

AND布爾表達(dá)式2

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

布爾表達(dá)式1

OR布爾表達(dá)式2

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

…

AND

…NOT

BETWEEN…

AND

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

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

空值不是一個確定的值，所以不可以用等于或不等于來比較或衡量；

空值只能說是空值（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語句中且只有在指定ORDER

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

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

|

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

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

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

AVG(ListPrice)FROM

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

Count(DISTINCT

Color)FROM

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

|

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

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

MAX(ListPrice)FROM

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

MIN(ListPrice)FROM

Production.ProductSUM34

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

|

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

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

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

SUM(ListPrice)FROM

Production.Product2.GROUP

BY分組查詢與計算36????

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

GROUP

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

GROUP

BY37

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

Cno,

COUNT(Sno)

FROM

SC

GROUPBY

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

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

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

Group

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

[ALL]SELECT語句2UNION

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

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

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

SELECT

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

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

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

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

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

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

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

表1

[

INNER

]

JOIN

表2

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

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

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

s1.sname="劉晨"and

s2.sname!="劉晨"55例2：雇員號雇員姓名經(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.雇員號=E2.經(jīng)理經(jīng)理姓名57自連接查詢已知雇員關(guān)系：雇員（雇員號，雇員姓名，經(jīng)理）請根據(jù)此關(guān)系列出經(jīng)理及其雇員（被其領(lǐng)導(dǎo)）的清單。58例3：查詢每一門課的間接選修課，列出：課程號，間接選修課編號59CnoCnameCpnoCcredit1數(shù)據(jù)庫542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27PASCAL語言64Select

o,second.cpnoFrom

course

first

join

course

secondOn

first.cpno=o60Cnopcno173556外連接61

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

HAVING搜索條件。?語法格式：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語言中，一個SELECT－FROM－WHERE語句稱為一個查詢塊。

子查詢是一個SELECT查詢，它嵌套在

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

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

WHERE/HAVING

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

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

表達(dá)式

[NOT] IN

(子查詢)

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

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

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

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

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