數(shù)據(jù)查詢與更新

SQL

SERVER

2005

案例教程第1頁，共79頁，編輯于2022年，星期一關(guān)于數(shù)據(jù)查詢與更新5.1

簡單數(shù)據(jù)查詢第2頁，共79頁，編輯于2022年，星期一SELECT語句的組成子句解釋SELECT后跟一組列的列表，或者一個星號表示想要返回所有的列FROM后跟一個表或者視圖的名字，或者帶有連接表達(dá)式的多個表WHERE后跟過濾規(guī)則ORDERBY后跟一組列的列表進(jìn)行排序5.1.1

SELECT和FROM子句學(xué)回回XXSS表表中中所所有有的的記記錄錄的的XXHH（（姓名名))、、CCSSRRQQ((出出生生日日期期))列列。。M,,

CCSSRRQQ1、指定返回列名【例5.1】返號）、XM(SELECT

XH,XFROM

XS第3頁，共79頁，編輯于2022年，星期一5.1.1

SELECT和FROM子句2、返回所有列

SELECT

*FROM

XS第4頁，共79頁，編輯于2022年，星期一5.1.1

SELECT和FROM子句EELLEECCTT

XXHH,,

XXMM++""

((""++XXBB++""))

"",,

CCSSRRQQ3、在SELECT子句后加入計算列（1）S（2）FFFRROOMM

XXSSSSEELLEECCTT

XXHH,,

XXMM,,GGEETTDDAATTEE(()),,1100,,

CCSSRRQQRROOMM

XXSS第5頁，共79頁，編輯于2022年，星期一5.1.2

列別名和表別名第7頁，共79頁，編輯于2022年，星期一2、

原始列名

別名SELECT

XH日期FROM

XS學(xué)號,XM姓名,

CSRQ

出生3、別名=原始列名SELECT學(xué)號=XH,姓名=XM,出生日期=CSRQFROM

XS5.1.2列別名和表別名第8頁，共79頁，編輯于2022年，星期一表別名：1、“始表名AS別名”SELECT

XH,

XM,

CSRQFROM

XS

a2、

“原始表名

別名”SELECT

XH,

XM,

CSRQFROM

XS

AS

a5.1.3

WHERE子句例：從XS表中查找姓名為“曹敏”的同學(xué)的學(xué)號，姓名，性別。SELECT

XH,XM,XBFROM

XSWHERE

XM="曹敏"第9頁，共79頁，編輯于2022年，星期一5.1.3

WHERE子句第10頁，共79頁，編輯于2022年，星期一NULL值參與的比較：當(dāng)字段未設(shè)置值或變量未設(shè)值時，其值為

NULL,即不包含任何值、當(dāng)ANSI_NULLS為OFF時(默認(rèn))，任何與NULL值的運算結(jié)果都為NULL,任何與

NULL的比較結(jié)果都為UNKNOW(不確定)。當(dāng)ANSI_NULLS為ON時，除了=和!=或者<>比較運算符外，其他比較運算符和NULL比較也會得到UNKNOWN值。5.1.3

WHERE子句**例1：SELECT

*FROM

XSWHERE

SFZ=NULL

OR

SFZ

!=NULL結(jié)果：空例2：SELECTFROM

XSWHERE

SFZ

is

NULL第11頁，共79頁，編輯于2022年，星期一5.1.3

WHERE子句例3：SET

ANSI_NULLS

OFFSELECT

*FROM

XSWHERE

SFZ=NULL第12頁，共79頁，編輯于2022年，星期一5.1.4

ORDER

BY子句1、單個字段排序

SELECT

*FROM

XSORDER

BY

XHN-UL-L按排按最學(xué)號進(jìn)行排序前面第13頁，共79頁，編輯于2022年，星期一5.1.4

ORDER

BY子句第14頁，共79頁，編輯于2022年，星期一2、多字段排序ORDER

BY后面可以指定多個排序字段，字段之間以“，”分隔。在指定了多列情況下，首先按第一列排序，在第一列相同的情況下按第二列排序，以此類推。5.1.4

ORDER

BY子句例：SELECTXH,XM,XB,DATEDITE())/365

AS

NLFROM

XSORDER

BY

XM,

NL注意：漢字按拼音FFF((DDAAYY,,CCSSRRQQ,,GGEETTDDAA首字字母母排排序序此處有空格第15頁，共79頁，編輯于2022年，星期一5.1.4

ORDER

BY子句降序排序：如果沒有特別說明，值是以升序序列進(jìn)行排序的。你也可以選擇使用ASC關(guān)鍵字指定升序排序。如果你希望以降序排列，則在ORDER

BY后面使用DESC關(guān)鍵字例：

SELECTFROM

XSORDER

B。*YY

CCSSRRQQ

DDEESSCC第16頁，共79頁，編輯于2022年，星期一5.1.5

TOP子句DDEESSCC面的的記記錄錄數(shù)數(shù)占占總總行行數(shù)數(shù)的的百百分分比比))

PPEERRCCEENNTT

**TOP子句用來返回排在前面特定數(shù)量的記錄，而不管符合條件的行是多少。有兩種方式用來指定返回的行：指定排在前面的絕對記錄數(shù)，如：SELECT

TOP

(5)*FROM

XSORDER

BY

CSRQ指定排在前SELECT

TOP(0.1FROM

XSOORRDDEERR

BBYY

CCSSRRQQ

DDEESSCC第17頁，共79頁，編輯于2022年，星期一5.1.5

TOP子句WITH

TIES子句WITH

TIES子句用來解決在使用TOP子句按

百分比返回后，與最后一名數(shù)據(jù)相同的記錄不能出現(xiàn)在結(jié)果集中的問題。例SELFROORD：EECCTT

TTOOPP((55))

WWIITTHH

TTIIEESS

**

MM

XXSSEERR

BBYY

CCSSRRQQ

DDEESSCC第18頁，共79頁，編輯于2022年，星期一5.1.6

DISTINCT關(guān)鍵字DISTINCT用來從結(jié)果集中去除重復(fù)的記錄。例：統(tǒng)計學(xué)生共來自于哪些民族。語句1：SELCET

MZFROM

XS語句2：SELCETDISTINCT

MZFROM

XS第19頁，共79頁，編輯于2022年，星期一5.2

分組查詢第20頁，共79頁，編輯于2022年，星期一5.2.1聚合函數(shù)特點：作用在多條記錄上。換句話說，函數(shù)的返回值建立在對多條記錄進(jìn)行統(tǒng)計的基礎(chǔ)上。常用聚合函數(shù)：SUM,

COUNT,

MAX,

MIN,

AVG1、計算總分SELECT

SUM(FS)FROM

CJWHERE

XQDM=21

AND

XSDM=205.2.1

聚合函數(shù)第21頁，共79頁，編輯于2022年，星期一2、計算最高分、最低分和平均分：SELECT

MAX(FS)最高分,MIN(FS)最低分,AVG(FS)平均分FROM

CJWHERE

XQDM=5

AND

XSDM=163483、統(tǒng)計班級人數(shù)

SELECT

COUNT(*)FROM

XSWHERE

BJDM=305.2.2

GROUP

BY

子句第22頁，共79頁，編輯于2022年，星期一GROUP

BY子句將查詢結(jié)果按某一列或多列值分組輸出，值相等的為一組。對查詢結(jié)果分組的目的是使集函數(shù)作用于每一個組，即每一個組都有一個函數(shù)值。5.2.2

GROUP

BY

子句例：查詢每個同學(xué)的最高分、最低分和平均分。

SELECT

XSDM,MAX(FS)最高分,MIN(FS)最低分,AVG(FS)平均分FROM

CJGROUP

BBYY

XXSSDDMM第23頁，共79頁，編輯于2022年，星期一5.2.2

GROUP

BY子句例：每個同學(xué)在不同學(xué)期的最高分、最低分、平均分：SELECT

XSDM,XQDM,MAX(FS)最高分,

MIN(FS)最低分,AVG(FS)平均分FROM

CJGROUP

BY

XSDM,XQDM第24頁，共79頁，編輯于2022年，星期一5.2.2

GROUP

BY子句T

MMAAXX((FFSS))最最高高分分,,MMIINN((FFSS))最最低低分分,,AAVVGG((FFSS))平平均均分分JJBBYY

XXSSDDMM執(zhí)執(zhí)行行后后的的結(jié)結(jié)果果集集中中你你將將無無法法確確定定成成績績屬屬于于哪哪位位注意：出現(xiàn)在GROUP

BY子句后面的字段列表應(yīng)該也出現(xiàn)在

SELECT子句中，否則獲得的數(shù)據(jù)可能沒有意義。如：SELECFROM

CGROUP上述語句 學(xué)生第25頁，共79頁，編輯于2022年，星期一5.2.2

GROUP

BY

子句函函數(shù)數(shù)外外，，都都必必須須出出現(xiàn)現(xiàn)在在GGRROOUUPP

BBYY子子句句后后面面例例如如，，下下面面的的語語句句將將會會報報錯錯：：SSEELLEECCTT

XXSSDDMM,,XXQQDDMM,,MMAAXX((FFSS))最最高高分分,,MMIINN((FFSS))低低分分,,AAVVGG((FFSS))平平均均分分出現(xiàn)在SELECT子句中的字段列表除了聚合。最FROM

CJGROUP

BY

XSDM第26頁，共79頁，編輯于2022年，星期一5.2.3

HAVING子句HAVING子句用來對分組后的數(shù)據(jù)進(jìn)行篩選

例：查詢平均成績大于65分的學(xué)生的最高分、最低分MIN(FS)最和平平均均成成績績。。SDDMM,,MMAAXX((FFSS))最最高高分分,,VVGG((FFSS))平平均均分分SELECT

X低分,AFROM

CJGROUP

BY

XSDM

HAVING

AVG(FS)>65第27頁，共79頁，編輯于2022年，星期一5.2.3

HAVING子句第28頁，共79頁，編輯于2022年，星期一HAVING子句和WHERE子句很相似，均用于設(shè)置數(shù)據(jù)篩選條件。WHERE子句對分組前的數(shù)據(jù)進(jìn)行篩選，條件中不能包含聚合函數(shù)；HAVING子句對分組過后的數(shù)據(jù)進(jìn)行篩選，條件中經(jīng)常包含聚合函數(shù)。HAVING子句必須和GROUP

BY子句聯(lián)合使用5.3聯(lián)接查詢第29頁，共79頁，編輯于2022年，星期一當(dāng)返回的數(shù)據(jù)集中需要包含位于多個表中的數(shù)據(jù)時，需要用到聯(lián)接查詢。及聯(lián)接查詢用于在一個查詢語句中同時返回多個表中的數(shù)據(jù)5.3.1

交叉聯(lián)接(CROSS

JOIN)交叉聯(lián)接返回兩個表的笛卡爾積。即：返回的數(shù)據(jù)集為一個表中的每一行與另一個表中每一行的組合。例：簡單交叉聯(lián)接SELECT

*FROM

KC

CROSS

JOIN

XQ返回的記錄數(shù)量為兩個表記錄數(shù)數(shù)的的乘乘積積OOIINN關(guān)關(guān)鍵鍵字字,,用用““，，””將將兩兩個個表表連連接接起來，上面語句可以省略CROSS

J即：SELECT

*

FROM

KC

,

XQ該返回結(jié)果和交叉聯(lián)接是基本是一致的，只是沒有重復(fù)的字段。第30頁，共79頁，編輯于2022年，星期一5.3.2

內(nèi)聯(lián)接(INNER

JOIN)內(nèi)聯(lián)接是聯(lián)接兩個表最常用的方法，使用比較運算符進(jìn)行表間某(些)列數(shù)據(jù)的比較操作，并列出這些表中與聯(lián)接條件相匹配的數(shù)據(jù)行。內(nèi)聯(lián)接可以理解為從兩個表的笛卡爾積中篩選出完全符合聯(lián)接條件的記錄和姓例：顯示成績表名。SELECT

XS.XH

學(xué)號數(shù),CJ.XSDM,XS.FROM

XS

INNER

JO中的的所所有有數(shù)數(shù)據(jù)據(jù)，，要要求求包包含含學(xué)學(xué)生生學(xué)學(xué)號號,XXSS..XXMM姓姓名名,,KKCCDDMM課課程程代代碼碼,,FFSS分分

XSSDDMMINN

CCJJ

OONN

CCJJ..XXSSDDMM==XXSS..XXSSDDMM第31頁，共79頁，編輯于2022年，星期一5.3.3

外聯(lián)接第32頁，共79頁，編輯于2022年，星期一外聯(lián)接分為三種：左外聯(lián)接(LEFT

OUTER

JOIN或LEFT

JOIN)右外聯(lián)接(RIGHT

OUTER

JOIN或RIGHT

JOIN)全外聯(lián)接(FULL

OUTER

JOIN或FULL

JOIN)與內(nèi)聯(lián)接不同，外聯(lián)接不僅列出與聯(lián)接條件相匹配的行，還會顯示左表(左外聯(lián)接時)、右表(右外聯(lián)接時)或兩個表(全外聯(lián)接時)中所有符合搜索條件的記錄。5.3.3

外聯(lián)接MC1、左外聯(lián)接左邊表中的行都會出現(xiàn)在結(jié)果數(shù)據(jù)集中，如果左邊表中的某一行在右邊表中沒有匹配的行（不滿足聯(lián)接條件），則以空值取代右邊表中的值。例：查看每個專業(yè)使使用用外內(nèi)聯(lián)聯(lián)接接：：SELECT

a.ZYDM,FbR.OBMJMC

ZY

AS

a

LFROMZBYJAASSab

OBJ

AS

b有哪哪些些班班級級。。aaaa....ZZZZYYYYMMMMCCCC,,,,bbbb....BBBBJJJJDDDDMMMM,,,,bbbb....ZZZZYYYYDDDDMMMMAAAASSSSEEEExxxxpppprrrr1111,,,,bb..BBJJEFFTT

JJOOIINNJJNNOOaaII..NNZZYYDDMM

==

bb..ZZYYDDMMOONN

aa..ZZYYDDMM

==

bb..ZZYYDDMM第33頁，共79頁，編輯于2022年，星期一5.3.3

外聯(lián)接2、右外聯(lián)接右外聯(lián)接和左外聯(lián)接類似，不同的是把右邊的表作為外部表(所有右邊表中的行包含在結(jié)果數(shù)據(jù)集中)。例：查看每個專業(yè)有,SELEb.ZYFROM哪些些班班級級。。ECCTT

aa..ZZYYDDMM,,

aa..ZZYYMMCC,,

bb..BBJJDDMM

DDMM

AASS

EExxpprr11,,

bb..BBJJMMCCM

ZZYY

AASS

aa

RRIIGGHHTT

JJOOIINNBBJJ

AASS

bb

OONN

aa..ZZYYDDMM

==

bb..ZZYDM第34頁，共79頁，編輯于2022年，星期一5.3.3外聯(lián)接3、全外聯(lián)接在全外聯(lián)接中，位于FULL

JOIN兩邊的表中的行都會包含在結(jié)果數(shù)據(jù)集中。第35頁，共79頁，編輯于2022年，星期一5.3.4自聯(lián)接列名說明YGDM員工代碼YGXM員工姓名SJLD該員工上級領(lǐng)導(dǎo)代碼自聯(lián)接所聯(lián)接的兩個表在物理上為同一張表，但在邏輯上可看作兩個表，用不同的表別名加以區(qū)分員工表第36頁，共79頁，編輯于2022年，星期一5.3.4自聯(lián)接例：查詢每位員工上級領(lǐng)導(dǎo)

SELECT

A.*,B.YGXMFROM

YG

ALEFT

JOIN

YG

B

ONAA..SSJJLLDD==BB..YYGGDDMM第37頁，共79頁，編輯于2022年，星期一5.3.5

多表聯(lián)接例：顯示學(xué)生信息，包括學(xué)生所在班級名稱、專業(yè)名稱和和系部名稱SELECT

XB.XBMC,

ZY.ZYMC,

BJ.BJMC,

XS.*FFRROOMM

XXSS

IINNNNEERR

JJOOIINNBBJJ

OONN

BBJJ..BBJJDDMM

==XXSS..BBJJDDMM

IINNNNEERR

JJOOIINNZZYY

OONN

BBJJ..ZZYYDDMM

==ZZYY..ZZYYDDMM

IINNNNEERR

JJOOIINNXB

ON

ZY.XBDM

=

XB.XBDM第38頁，共79頁，編輯于2022年，星期一5.4

子查詢第39頁，共79頁，編輯于2022年，星期一有兩種子查詢類型：標(biāo)準(zhǔn)子查詢和相關(guān)子查詢。標(biāo)準(zhǔn)子查詢執(zhí)行一次，結(jié)果反饋給外層查詢，它的返回值跟其外層查詢沒有聯(lián)系（不相關(guān)）。相關(guān)子查詢在子查詢中引用了外層查詢中的表，因此它的執(zhí)行次數(shù)取決于其外層查詢的查詢次數(shù)。5.4.1

標(biāo)準(zhǔn)子查詢與相關(guān)子查詢第40頁，共79頁，編輯于2022年，星期一1、標(biāo)準(zhǔn)子查詢例：查詢與學(xué)號為“0630160219”的同學(xué)同班的學(xué)生信息。步驟1：我們先求這個同學(xué)所在班級的班級代碼：SELECTFROMWHEREBJDMXSXH

=

"0630160219‘步驟2：面語句查詢結(jié)果為23。從XS表中查詢BJDM為23的學(xué)生信息：SELECTFROMWHERE*XSBJDM=235.4.1

標(biāo)準(zhǔn)子查詢與相關(guān)子查詢第41頁，共79頁，編輯于2022年，星期一步驟3：將前兩個語句整合：SELECT*FROMXSWHEREBJDM=(SELECT

BJDMFROM

WHEREXSXH

=

"0630160219")注意事項：1、必須用“（”和“）”將子查詢擴(kuò)起來。

2、處于比較運算符其中一邊的子查詢，其返回值應(yīng)該只能有一行、一列，其返回值的數(shù)據(jù)類型應(yīng)該和運算符另一邊的值（列）是一致或可以被系統(tǒng)自動轉(zhuǎn)換的。3、子查詢中不能檢索包含數(shù)據(jù)類型為text和image的列。5.4.1標(biāo)準(zhǔn)子查詢與相關(guān)子查詢第42頁，共79頁，編輯于2022年，星期一2、相關(guān)子查詢例：查詢存在同名的學(xué)生信息1)：語句基本形式：SELECT

*

FROM

XS

WHERE

該同學(xué)存在同名

2)判斷指定學(xué)生是否重名：（SELECT

COUNT(*)

FROM

XS

WHERE

XM="XXX"）>13）將2）代入1），并給xs表別名SELECTFROM

WHERE*XS

a((SELECT

COUNT(*)FROMWHEREXS

b(b.XM

=

"XXX"))

>

1)5.4.1

標(biāo)準(zhǔn)子查詢與相關(guān)子查詢4)將指定學(xué)生xxx替換為外層被查詢的學(xué)生SELECTFROMWHERE*XS

a((SELECT

COUNT(*)FFRROOMM

XXSS

bbWWHHEERREE

((bb..XXMM

==

aa..XXMM))))

>>

11))第43頁，共79頁，編輯于2022年，星期一5.4.2使用ALL、ANY、SOME關(guān)鍵字第44頁，共79頁，編輯于2022年，星期一ALL

在條件語句中，表示必須滿足每一個條件項例：查詢XS表中年齡最大的學(xué)生。SELECT

*FROM

WHEREXSCSRQ

<=

ALL(SELECT

CSRQFROM

XS

)5.4.2

使用ALL、ANY、SOME關(guān)鍵字第45頁，共79頁，編輯于2022年，星期一ANY（SOME）

在條件語句中，表示滿足其中任何一個條件項例：取要出生日期不是最小的學(xué)生信息SELECT

*FROM

WHEREXSCSRQ

<

ANY(SELECT

CSRQFROM

XS

)5.4.3

EXISTS

和IN子查詢第46頁，共79頁，編輯于2022年，星期一EXISTS

子查詢被稱為“存在子查詢”,它用來判斷子查詢相關(guān)表中是否存在滿足子查詢條件的行，而對于這些行的具體數(shù)據(jù)，子查詢并不關(guān)心也不會被返回。當(dāng)內(nèi)層查詢結(jié)果為“存在”滿足子查詢條件的行，則外層的WHERR子句返回真值，否則返回假值。由于EXISTS

子查詢并不返回滿足條件的數(shù)據(jù)，因此SELECT子句后的字段列表并無多大意義，通常將通過EXISTS引入的子查詢的字段列表設(shè)為“*”，而不使用具體列名。EXISTS

子查詢語法如下：[NOT]EXISTS(子查詢)5.4.3

EXISTS

和IN子查詢第47頁，共79頁，編輯于2022年，星期一例：查詢XS表中曾參加過考試的學(xué)生信息。SELECT*FROMXSWHEREEXISTS(SELECT*FROMCJWHEREXSDM

=

XS.XSDM)5.4.3

EXISTS

和IN子查詢第48頁，共79頁，編輯于2022年，星期一IN關(guān)鍵字用來確定給定的值（測試表達(dá)式）是否與子查詢或列表中的值相匹配，它的作用相當(dāng)于“=ANY”。IN子查詢語法如下：測試表達(dá)式

[

NOT

]

IN

(

子查詢

或者

表達(dá)式列表

)5.4.3

EXISTS和IN子查詢第49頁，共79頁，編輯于2022年，星期一例：查詢位于班級代碼為22、23或24的班級中所有學(xué)生的信息。SELECT

*FROMWHEREXSBJDM

IN

(22,23，24)例：查詢屬于計算機(jī)信息管理專業(yè)（專業(yè)代碼為6）所有學(xué)生的信息。SELECT

*FROMWHEREXSBJDM

IN(SELECT

BJDM

FROM

BJWHERE

ZYDM=6)5.4.4

派生表第50頁，共79頁，編輯于2022年，星期一一個子查詢能夠返回一個由行和列構(gòu)成的數(shù)據(jù)集，我們可以將這個數(shù)據(jù)集再看作一個表來處理，這個由子查詢得出的新表就是我們說的“派生表”派生表有利于提高查詢速度、簡化操作派生表不是存儲在數(shù)據(jù)庫中的對象，它只在查詢期間有效。5.4.4派生表第52頁，共79頁，編輯于2022年，星期一步驟2：將上步結(jié)果集當(dāng)作虛擬表a使用，并與KC、XS表聯(lián)接以獲取姓名、課程名稱等信息SELECT XS.XH,

XS.XM,

KC.KCMC,

a.FSFROMa

JOINXS

ON

XS.XSDM

=

a.XSDM

JOINKC

ON

a.KCDM

=

KC.KCDM5.4.4派生表步驟3：將第一步的語句替換第二步中虛擬表aSELECTFROMXS.XH,

XS.XM,

KC.KCMC,

a.FS(SELEFROM

WHERXS

ONKC

ONECCTT

XXSSDDMM,,

KKCCDDMM,,

XXQQDDMM,,

FFSSCCJJEE

XXQQDDMM

==

55

AANNDD

XXSSDDMM

IINN((SSEELLEECCTT

XXSSDDMMFFRROOMM

XXSSWWHHEERREE

BBJJDDMM

==

2222))))

AASS

aa

JJOOIINNXXSS..XXSSDDMM

==

aa..XXSSDDMM

JJOOIINN

aa..KKCCDDMM

==

KKCC..KKCCDDMM第53頁，共79頁，編輯于2022年，星期一5.5

聯(lián)合查詢第54頁，共79頁，編輯于2022年，星期一UNION關(guān)鍵字可以將兩個或更多相互獨立的

SELECT語句的查詢結(jié)果合并成一個集合，即執(zhí)行聯(lián)合查詢。聯(lián)合查詢要求合并的幾個數(shù)據(jù)集之間應(yīng)該具有相同的字段數(shù)目和類型。例：在同一個數(shù)據(jù)集中返回賀清青同學(xué)所有科目的考試成績和平均成績。5.5

聯(lián)合查詢第55頁，共79頁，編輯于2022年，星期一第一步，返回賀清青同學(xué)所有科目的成績：SELECT

KC.KCMC

科目,ABS(CJ.FS)成績FROMWHERECJ

INNER

JOINKC

ON

CJ.KCDM

=

KC.KCDM

INNER

JOINXS

ON

CJ.XSDM

=

XS.XSDMXS.XM="賀清青‘第二步，返回賀清青同學(xué)所有科目的平均分：SELECT

AVG(ABS(CJ.FS))FROMCJ

INNER

JOINXS

ON

CJ.XSDM=XS.XSDMXS.XM="賀清青"WHERE5.5聯(lián)合查詢第三步，為了滿足兩個結(jié)果集具有相同類型的字段，我們對上一語句改為下面這種形式：SELECT

科目=

"

平均分",

成績=(SELECTAVG(ABS(CJ.FS))FROMWHERECJ

INNER

JOINXS

ON

CJ.XSDM=XS.XSDMXS.XM="賀清青")并：：第四部,將第一步和第三步的結(jié)果用UNION關(guān)鍵字集合select

kc.kcmc

科目,

abs(cj.fs)

成績

FROM CJ

INNER

JOINKC

ON

CJ.KCDM

=

KC.KCDM

INNER

JOINXS

ON

CJ.XSDM

=

XS.XSDMWHERE

XS.XM="賀清青"UNIONSELECT

科目="平均分",成績=(SELECT

AVG(ABS(CJ.FS))FROMCJ

INNER

JOINXS

ON

CJ.XSDM

=

XS.XSDMXS.XM="賀清青")WHERE第56頁，共79頁，編輯于2022年，星期一5.6

通用表表達(dá)式CTE第57頁，共79頁，編輯于2022年，星期一CTE

與派生表類似，不存儲為數(shù)據(jù)庫對象，并且只在查詢期間有效。與派生表的不同，CTE可在同一查詢中引用多次，可自引用。使用CTE

可以獲得提高可讀性和輕松維護(hù)復(fù)雜查詢的優(yōu)點。CTE

由表示CTE的表達(dá)式名稱、可選列列表和定義CTE的查詢組成。CTE的基本語法結(jié)構(gòu)如下：WITH

CTE_NAME[(列名1[,...n])]AS(CTE查詢語句)運行CTE

的語句為：SELECT

*FROM

CTE_NAME5.6

通用表表達(dá)式CTE第58頁，共79頁，編輯于2022年，星期一例查詢班級代碼為22的班級（06級商務(wù)英語1班）在學(xué)期代碼為5的學(xué)期（2006~2007學(xué)年第1期）所有學(xué)生的考試成績，包含學(xué)號，姓名，課程名稱和分?jǐn)?shù)。WITH

CTE_CJAS(SELECT

*FROM

WHERECJXQDM

=

5

AND

XSDM

IN(SELECT

XSDMFROM

XSWHERE

BJDM

=

22)

)XS.XH,

XS.XM,

KC.KCMC,

a.FSCTE_CJ

AS

a

JOINXS

ON

XS.XSDM

=

a.XSDM

JOINKC

ON

a.KCDM

=

KC.KCDMSELECTFROM5.8排名函數(shù)5.8.1

ROW_NUMBERROW_NUMBER函數(shù)用來返回結(jié)果集內(nèi)每行的行號，第一行從1

開始。其語法為：ROW_N行分配號、姓名、序。位于唯例：出SELEC姓FROMWHERNUUMMBBEERR

((

))

OOVVEERR

((

<<oorrddeerr__bbyy__ccllaauussee>>

))OVVEERR后后的的括括號號中中需需要要一一個個OORRDDEERR

BBYY

子子句句，，該該子子句句用用來來確確定定為為一

RROOWW__NNUUMMBBEERR的的順順序序。。查查詢詢0066級級商商務(wù)務(wù)英英語語11班班（（班班級級代代碼碼為為2222））的的所所有有學(xué)學(xué)生生的的學(xué)學(xué)

生生日日期期、、家家庭庭是是地地址址，，要要求求第第一一列列為為行行號號,,并并按按學(xué)學(xué)號號排排CTT

RROOWW__NNUUMMBBEERR(())OOVVEERR((OORRDDEERR

BBYY

XXHH))行行號號,,XXHH學(xué)學(xué)號號,,XXMM名名,,CCSSRRQQ出出生生日日期期,,JJTTDDZZ

家家庭庭地地址址XXSSE

BBJJDDMM==2222第59頁，共79頁，編輯于2022年，星期一5.8.2

RANK與DENSE_RANK第60頁，共79頁，編輯于2022年，星期一RANK與DENSE_RANK函數(shù)都用來進(jìn)行名次的排列。

RANK函數(shù)在排定名次時會以排在前面的記錄數(shù)為依據(jù)，而DENSE_RANK在排定名次時只考慮排在前面的不同

數(shù)值的個數(shù)作為依據(jù)。RANK與DENSE_RANK的語法和ROW_NUMBER函數(shù)相似，即：RANK()

OVER(<order_by_clause>)或DENSE_RANK

(

)

OVER

(<

order_by_clause

>

)5.8.2

RANK與DENSE_RANK第61頁，共79頁，編輯于2022年，星期一例

查詢班級代碼為22的班級（06級商務(wù)英語1班）在學(xué)期代碼為5的學(xué)期（2006~2007學(xué)年第1期）所有學(xué)生的總分和名次。步驟1：查詢學(xué)生的總分信息：SELECTFROMXS.XH

學(xué)號,XS.XM姓名,SUM(CJ.FS)總分CJ

INNER

JOINXS

ON

XS.XSDM

=

CJ.XSDM

INNER

JOINKC

ON

CJ.KCDM

=

KC.KCDMGROUP

BY

XS.XH,XS.XM

ORDER

BY

總分5.8.2

RANK與DENSE_RANK步驟2：在SELECT子句中添加RANK函數(shù)，并在ORDER

BY子句中指定排名的依據(jù)：RANK

(

)

OVER

(最終結(jié)果如下SELECT

XS.XHS)總分,RANK()

OVER(OOINFROM CJ

INNEXS

OKCGROUP

BY

XS.X(OORRDDEERR

BBYY

SSUUMM((CCJJ..FFSS))))：學(xué)學(xué)號號,,XXSS..XXMM

姓姓名名,,SSUUMM((CCJJ..FF

RRDDEERR

BBYY

SSUUMM((CCJJ..FFSS))))名名次次

RR

JJOOIINNN

XXSS..XXSSDDMM

==

CCJJ..XXSSDDMM

IINNNNEERR

JJO

OONN

CCJJ..KKCCDDMM

==

KKCC..KKCCDDMM

H,,XXSS..XXMM第62頁，共79頁，編輯于2022年，星期一5.8.2

RANK與DENSE_RANK將上述語句中RANK替換為DENSE_RANK，則執(zhí)行結(jié)果為：第63頁，共79頁，編輯于2022年，星期一5.9

數(shù)據(jù)更新第64頁，共79頁，編輯于2022年，星期一5.9.1

使用INSERT語句插入數(shù)據(jù)1、簡單數(shù)據(jù)插入使用INSERT語句向表中插入單行數(shù)據(jù)的語法如下：INSERT

[INTO]<表名>（字段列表）

VALUES

（值列表）例學(xué)生李金龍剛轉(zhuǎn)到我校06級經(jīng)濟(jì)信息管理班（BJDM

=26

），請將該生數(shù)據(jù)添加到向XS表中。該生的登記信息如下：姓名：李金龍，性別：男，出生日期：1982-2-1。INSERT

INTO

XS(XM,

XB,

CSRQ,

BJDM)VALUES

("李金龍","男","1982-2-1",26)5.9.1

使用INSERT語句插入數(shù)據(jù)第65頁，共79頁，編輯于2022年，星期一如果需要給每個字段指定值，則可省略字段列表，但要保證值列表與表中字段一一對應(yīng)例

BJ表中包括BJDM(班級代碼),ZYDM（專業(yè)代碼）,BJMC（班級名稱）,NJ（年級）,ZT（狀態(tài)）四個字段。新增班級

07軟件技術(shù)4班，專業(yè)代碼13，年級2007，狀態(tài)值為0。INSERT

INTO

BJVALUES(1001,13,"07軟件技術(shù)班",2007,0)5.9.1

使用INSERT語句插入數(shù)據(jù)第66頁，共79頁，編輯于2022年，星期一2、批量數(shù)據(jù)插入在INSERT語句中使用一個SELECT查詢將一個查詢結(jié)果的內(nèi)容添加到表中。其語法格式為：

INSERT

INTO<表名>（字段列表）<查詢語句>例在數(shù)據(jù)庫中新建表XS_2,用來存放已畢業(yè)學(xué)生信息，其結(jié)構(gòu)和XS表完全相同。請將2006級所有學(xué)生學(xué)號、班級、姓名、出生日期、家庭地址信息插入到XS_2中，并將ZT（狀態(tài)）值設(shè)為1。年級信息保存在BJ表中,所以要知道學(xué)生所在年級，需要將BJ表和XS表聯(lián)接。INSERTINTO

XS_2(XH,BJDM,

XM,

CSRQ,

JTDZ,ZT)SELECTFROM

WHEREXH,

BJ.BJDM,XM,

CSRQ,

JTDZ,1XS

INNER

JOIN

BJ

ON

XS.BJDM

=

BJ.BJDMBJ.NJ

=

20065.9.1使用INSERT語句插入數(shù)據(jù)第67頁，共79頁，編輯于2022年，星期一3、將數(shù)據(jù)插入新表可以使用如下語法將查詢到的數(shù)據(jù)插入到一個新表中：SELECT

<字段列表>

INTO

<新表名>

FROM

表名例 將CJ表中KCDM為6的數(shù)據(jù)插入到新表CJ_2中：SELECT

*

INTO

CJ_2FROM

CJWHERE

KCDM=65.9.2使用UPDATE語句修改數(shù)據(jù)第68頁，共79頁，編輯于2022年，星期一UPDATE語法如下：UPDATESET<表名>字段1=字段值1,字段2=字段值2,……<搜索條件表達(dá)式>WHERE例XS表中有些學(xué)生民族為“漢”,有些為“漢族”，將表中民族為

“漢族”的數(shù)據(jù)統(tǒng)一改為民族為“漢”。UPDATE

XSSET

MZ="漢"

WHERE

MZ="漢族"5.9.3

使用DELETE刪除數(shù)據(jù)第69頁，共79頁，編輯于2022年，星期一DELETE語句基本語法如下：DELETE

[FROM]

<表名>WHERE

<搜索條件表達(dá)式>例

ZY表中，如果一個專業(yè)沒有任何班級屬于它，則將這個專業(yè)從ZY表中刪除。DELETE

ZYWHERE

ZYDM

NOT

IN(SELECT

ZYDM

FROM

BJ)5.9.4

TRUNCATE

TABLE語句第70頁，共79頁，編輯于2022年，星期一用DELETE語句和TRUNCATE

TABLE語句都能夠刪除表中的所有數(shù)據(jù)，但是使用TRUNCATE

TABLE語句比用DELETE語句快得多，原因如下：

使用DELETE語句，系統(tǒng)將一次一行地處理要刪除的表中的記錄，在從表中刪除行之前，在事務(wù)處理日志中記錄相關(guān)的刪除操作和刪除行中的列值，以防止刪除失敗時，可以使用事務(wù)處理日志來恢復(fù)數(shù)據(jù)。

TRUNCATETABLE則一次性完成刪除與表有關(guān)的所有數(shù)據(jù)頁的操作。另外，TRUNCATE

TABLE語句并不更新事務(wù)處理日志。TRUNCATE

TABLE語句語法如下：

TRUNCATE

TABLE<表名>5.10事務(wù)第71頁，共79頁，編輯于2022年，星期一5.10.1事務(wù)的基本概念事務(wù)是一種機(jī)制，是一個操作序列，它包含了一組數(shù)據(jù)庫操作命令，所有的命令作為一個整體一起向系統(tǒng)提交或撤消操作請求，即要么都執(zhí)行，要么都不執(zhí)行。因此，事務(wù)是一個不可分割的工作邏輯單元。在數(shù)據(jù)庫系統(tǒng)上執(zhí)行并發(fā)操作時，事務(wù)是作為最小的控制單元來使用的。5.10.1

事務(wù)的基本概念第72頁，共79頁，編輯于2022年，星期一事務(wù)具有以下幾個屬性：

原子性

每一個事務(wù)是一個工作單元。它不能被分割成更小的部分。這個屬性意味著在事務(wù)中定義的一切數(shù)據(jù)更改要么都完成，要么都不完成。

一致性

一個事務(wù)不能違背定義在數(shù)據(jù)庫中的任何完整性檢查。為了維護(hù)一致性，所有的規(guī)則、約束、檢查和觸發(fā)都會應(yīng)用在事務(wù)中。由于所有的數(shù)據(jù)更改在事務(wù)期間內(nèi)進(jìn)行，這些數(shù)據(jù)在事務(wù)開始和事務(wù)結(jié)束前會被確保為一致的。

隔離性

事務(wù)必須與其他事務(wù)進(jìn)行的數(shù)據(jù)更改相隔離。這意味著沒有其他操作可以改變

中間態(tài)(沒有提交的)的數(shù)據(jù)。為了避免中間態(tài)數(shù)據(jù)被更改，事務(wù)必須要么等待來自其他事務(wù)的更改被提交，要么只能查看到處于上一個提交狀態(tài)的數(shù)據(jù)。

持久性

在一個事務(wù)完成，并且客戶端應(yīng)用程序已經(jīng)被提示這個事務(wù)已經(jīng)成功完成后，無論發(fā)生任何系統(tǒng)錯誤，這些更改的數(shù)據(jù)將永久存在。5.10.1事務(wù)的基本概念第73頁，共79頁，編輯于2022年，星期一

SQL

Server數(shù)據(jù)庫引擎會通過事務(wù)日志強(qiáng)制執(zhí)行事務(wù)的物理一致性，并且保證事務(wù)的持久性。SQL

Server還會強(qiáng)制對約束、數(shù)據(jù)類型以及其他內(nèi)容執(zhí)行一切一致性檢查以確保邏輯上的一致性。這都由SQL

Server自動地強(qiáng)制執(zhí)行。但是，諸如轉(zhuǎn)賬之類的業(yè)務(wù)處理，還必須由開發(fā)人員來自己定義事務(wù)。定義一個事務(wù)時需要考慮以下方面：

定義事務(wù)的外延。開發(fā)者必須定義事務(wù)從哪里開始，到哪里結(jié)束。一個事務(wù)總應(yīng)該越短越好，但是同時要做到盡量符合實際業(yè)務(wù)過程的需要。定義錯誤管理。在事務(wù)中并不是所有的錯誤都會自動回滾。開發(fā)者有責(zé)任實現(xiàn)錯誤管理。

定義隔離級別。隔離不同的事務(wù)總是會有與