a西華師范大學(xué)

；金,「計(jì)算機(jī)學(xué)院

SQLSERVER高級(jí)應(yīng)用

佳，西華師范大學(xué)

計(jì)算機(jī)學(xué)院內(nèi)容安排

口第一部分T-SQL編程

口第二部分子查詢

口第三部分事務(wù)

口第四部分索引

□第五部分視圖

口第六部分存儲(chǔ)過程

□第七部分觸發(fā)器

2

a西華師范大學(xué)

；金,「計(jì)算機(jī)學(xué)院

第一部分T-SQL編程

a西華師范大學(xué)

等計(jì)算機(jī)學(xué)院

SQL(StructuredQueryLanguage)：結(jié)構(gòu)化查詢語言，

有數(shù)據(jù)定義語言(DDL),例如:CREATE>DROP、ALTER等語

句；

數(shù)據(jù)操作語言(DML),例如:INSERT(插入)、UPDATE

(修改)、DELETE(刪除)語句；

數(shù)據(jù)查詢語言(DQL),例如：SELECT語句；

數(shù)據(jù)控制語言(DCL),例如:GRANT>REVOKE等語句。

T-SQL(Transact-SQL)即事務(wù)SQL,為SQL的擴(kuò)展語言，是微軟公

司對(duì)SQL語言的擴(kuò)充，譬如加入了程序語言中的if,while等語法,

同時(shí)可以使用函數(shù)等數(shù)據(jù)庫對(duì)象，是應(yīng)用程序與SQLServer數(shù)

據(jù)庫引擎溝通的主要語言。

4

不*、西華師范大學(xué)

?計(jì)算機(jī)學(xué)院使用變量

和C語言一樣，變量分為：

口局部變量：

口局部變量必須以標(biāo)記@作為前綴，^n@age

口局部變量的使用也是先聲明，再賦值

口全局變量：

口全局變量必須以標(biāo)記@@作為前綴，如@@丫3二由11

口全局變量由系統(tǒng)定義和維護(hù)，我們只能讀取，不能修

改全局變量的值

5

西華師范大學(xué)

等計(jì)算機(jī)學(xué)院局部變至

□聲明局部變量

DECLARE@變量名數(shù)據(jù)類型

例如：

DECLARE@namevarchar(8)

DECLARE@seatint

口賦值

—SET@變量名二值

0V

一SELECT@變量名二值

例如：

SET@name=，張三’|必須確保篩選出的記錄只有1條

SELECT@name=stuNameFROMstulnfo

WHEREstuNo=,s25302,

6

**西華師范大學(xué)UI」r7*曰一“IC/

①計(jì)算機(jī)學(xué)院局部變堇小例2-1

stuNamestuNostuSexstuAgestuSeat|stuA,ddress

1張秋麗s25301男181北京海淀

2本文才s25302男282地址不詳

Q

李斯文$25303女223河南洛陽

___歐陽俊雄S25304女344地址不詳

[T

&__:梅超風(fēng)s25318女235地址不詳

學(xué)員信息表

問題：編寫T-SQL查找李文才的左右同桌？

分析：

第一步，找出“李文才”的座位號(hào)；

第二步，李文才的座位號(hào)加1或減1

7

西華師范大學(xué)

計(jì)算機(jī)學(xué)院局部變量示例2-2

□參考語句

一

/D*E——：stuNamestuNostuSexstuAgestuSeatstuAddress

E

S工□李文才s25302.男282地址不詳

SE

-

/D*E

S

E;stuNaroestuMostuSexstnAgestuSeatstuAddress

V

1［張秋麗s25301男181北京海淀

S日

售-I李斯文s25303女―122一唇河南洛陽

GO

演示：使用局部變量

8

於、西華師范大學(xué)

W計(jì)算機(jī)學(xué)院全局變量

□全局變量都使用兩個(gè)@標(biāo)志作為前綴

變量含義

@@ERROR最后一個(gè)T-SQL錯(cuò)誤的錯(cuò)誤號(hào)

@@IDENTITY最后一次插入的標(biāo)識(shí)值

@@LANGUAGE當(dāng)前使用的語言的名稱

@@MAX_CONNECTION可以創(chuàng)建的同時(shí)連接的最大數(shù)目

S

@@ROWCOUNT受上一個(gè)SQL語句影響的行數(shù)

@@SERVERNAME本地服務(wù)器的名稱

@@TRANSCOUNT當(dāng)前連接打開的事務(wù)數(shù)

@@VERSIONSQLServer"的版本信息

9

玲、西華師范大學(xué)

W計(jì)算機(jī)學(xué)院全局變量示例

print_'SQLS-rv&r的版本+則/ERS工ON

print'服務(wù)器的名稱：'卜酮SERVERNMIE

INSERTINTOstulnfo1stuNamestuNostuSex.stuAge

VALUES產(chǎn)武松-s25328'/男’，—

一如果大于。表示上一條語句執(zhí)檸有錯(cuò)誤

print'當(dāng)前與昔誤號(hào)'-^convert'：varchar15).：?ia>ERROR)

print'剛才報(bào)名的學(xué)員.座位號(hào)為：'

+convert(varchart5頒@IDENTITY)

UPDATEstuinfoSETstuAge=85

WHEREstuName='李文才'

print'當(dāng)前錯(cuò)誤號(hào)'+convert1varchar'5).頒?ERROR)

捻

錯(cuò)

?誤

_一O

剛

學(xué)

3幫

手

我座位號(hào)（自動(dòng)編號(hào)）

別

息

服

尾*

務(wù)5sI6

X專案沖突。該沖突發(fā)生于數(shù)據(jù)￡

與C1C

可X

iupDACOL1^

句

房0

錯(cuò)

誤

1前a547

西華師范大學(xué)

W計(jì)算機(jī)學(xué)院邏輯控制語句

口IF—ELSE語句

C語言中的if-else語句SQL中的IF-ELSE語句

if（條件）IF（條件）

BEGIN

語句1；語句1

語句2；語句2

}END

al。a匚IG匚

同C語言一樣：

?ELSE是可選部分

?如果有多條語句，才需要BEGIN-END語句塊

)END

P計(jì)算疝學(xué)院IF-ELSE示例2-1

ExamNostuNowrittenExamLabExam

2—S271811s25303_j8058

Z.S271813s25302J5090

S271816s253017782

AS271818S25328J45]65

學(xué)員成績表

問題：

統(tǒng)計(jì)并顯示本班筆試平均分，如果平均分在70以上，顯示“成績優(yōu)秀”,

并顯示前三名學(xué)員的考試信息；如果在70以下，顯示“本班成績較差”,

并顯示后三名學(xué)員的考試信息。

分析：

第一步，統(tǒng)計(jì)平均成績存入臨時(shí)變量；

第二步，用IF—ELSE判斷；

12

9*西華師范大學(xué).ll.—1/ricc

@計(jì)算機(jī)學(xué)院IF-ELSE示例2-2

窗▼0?IS園|X電值回tWS圖▼?■i|ustuDB三］

DECLARE?myavgfloat

SELECT?myav^-AVG'wri11enExam)fromstuMarks

print'本1ffsp分‘卜convert'varchar51,?myavg,

IF■?myavg70)

BEGIN

print'本班筆試成績優(yōu)秀，前三名的成績?yōu)椤?/p>

SELECTTOP3?:FROMstuMarksORDERBYwrittenExamDESC

END

ELSE

BEGIN

print'本班筆試成統(tǒng)較差，后三名的成績對(duì)

SELECTTOP3+FROMstuMarksORDERBYwrittenExam

END

本班筆試成績較差，后三名的成績?yōu)?/p>

ExamNostuNowrittenExamLabExam

8058

S271811S253037782

S271816S253015090

S271813S25302

3

。露蠹高設(shè)置輸出結(jié)果的格式

口為了文本消息和輸出結(jié)果顯示在同一窗口，需要

設(shè)置輸出結(jié)果的格式

SQL查詢分析器一［查詢—LIROIGGUI.stuBB.LIEOIGGUlUdainistrat

［文件電）編輯?萱詢④I工具矍）窗口世）幫助兇

_L=;—■X—fi-

常規(guī)I編輯器結(jié)果I連接I連接屬性I字體I腳本］=

默認(rèn)結(jié)果目標(biāo)◎）:結(jié)果顯示為文本二I

L.MM!戰(zhàn)湎戢——■P,

結(jié)果輸出格式但）f）結(jié)果顯示為表格氐

結(jié)果保存為文件….

14

西華師范大學(xué)

W計(jì)算機(jī)學(xué)院邏輯控制語句

□WHILE循環(huán)語句

C語言中的while語句SQL中的WHILE語句

while（條件）WHILE（條件）

BEGIN

語句1;語句1

語句2；語句2

break；BREAK

}END

同C語言一樣：

?BREAK表示退出循環(huán)

?如果有多條語句，才需要BEGIN-END語句塊

15

/、西華師范大學(xué)

等計(jì)算機(jī)學(xué)院WHILE示例2-1

ExamNostuNowrittenExamLabExam

2—S271811s25303_j8058

Z.S271813s25302J50]90

S271816s253017782

AS271818S25328J45]65

學(xué)員成績表

問題：

本次考試成績較差，假定要提分，確保每人筆試都通過。提分規(guī)則很簡

單，先每人都加2分，看是否都通過，如果沒有全部通過，每人再加2分,

再看是否都通過，如此反復(fù)提分，直到所有人都通過為止。

分析：

第一步，統(tǒng)計(jì)沒通過的人數(shù)；

第二步，如果有人沒通過，加分；

第三步，循環(huán)判斷。二

16

西華師范大學(xué)

◎夕計(jì)算機(jī)學(xué)院WHILE示例2-2

口參考語句

例查詢—LIROIGGUI.stuOB.LIKOVGGUI\Adaiidstr&tor—C:\DocMMitsvidSettings^■■■■I

DECLARE@nint

TOILE(1=1)一條件永遠(yuǎn)成立

BEGIN

SELECT@n二COUNT，*)FROMstuMarksWHEREwrittenExam60一統(tǒng)計(jì)不及格人數(shù)

IF窗n0)

UPDATEstuMarksSETwrittenExam二writtenExam+2—每人加2分

ELSE

BREAK一退出循環(huán)

END

print'加分后的成績?nèi)缦拢骸?/p>

SELECT水FROMstuMarks

加分后的成績?nèi)缦?

ExamNostuNowrittenExainLabExain

S271811s253039658

S271813s253026690

S271816s253019382

S271818S253286165

―西華師范大學(xué)，皿4口出J、H-

①計(jì)算機(jī)學(xué)院邏車耳控制語句

口CASE—END多分支語句

CASE

WHEN條件1THEN結(jié)果1

WHEN條件2THEN結(jié)果2

ELSE其他結(jié)果

END

18

盥盤浸晟CASE-END示例2-1

舊xamN口stuNowrittenExaroLabExam

1S271811s253038058

]S271813s253025090

S271816s253017782

JS271818S253284565

學(xué)員成績表

問題：

采用美國的ABCDE五級(jí)打分制來顯示筆試成績。

A級(jí)：90分以上

B級(jí)：80—89分

C級(jí)：70—79分

D級(jí)：60—69分

E級(jí)：60分以下

19

懣；需?CASE—END示例2-2

口參考語句

娥查詢—LIROMGGUI.stuBB.LTBOBG?UI\A<laiiiistr?tor—C:\DocwBeMtsand

—SELECT*FROMstuMarks一原始成績

print'ABCDE五級(jí)顯示成績?nèi)缦拢骸?/p>

SELECTstuNo.成績二CASE

WHENwrittenExam60THEN'E'

WHENwrittenExamBETWEEN60AND69THE.N'

WHENwrittenExamBETWEEN70AND79THEN'C'

WHENwrittenExainBETWEEN80ANH89THEN'B'

EISE'￡

END

FROMstuMarks

ABCDE五級(jí)顯示成績?nèi)缦?

stuNo成績

A

s25303D

s25302A

s25301N

s25328

20

―、西華師范大學(xué)

等計(jì)算機(jī)學(xué)院CASE-END課堂練習(xí)

考號(hào)學(xué)號(hào)筆試機(jī)試1平均分i等級(jí)1

1S271811$25303965877

2S271813S2530266'9078

S271816s253018287

4S271818s253286165二63

學(xué)員成績分析

課堂練習(xí)：

請(qǐng)根據(jù)平均分和下面的評(píng)分規(guī)則，編寫T-SQL語句查詢學(xué)員的

成績，如上圖所示。

優(yōu)：90分以上

良：80—89分

中：70—79分

差：60—69分

不及格：60分以下

21

編Z西華師范大學(xué)CACLLir,4一

?計(jì)算機(jī)學(xué)院CASE-END練習(xí)答案

USEstuDB

GO

SELECT考號(hào)二ExamNo,學(xué)號(hào)二stuNo,筆試二writtenExam,機(jī)試=labExam,

平均分二(writtenExam+labExam)/2,

等級(jí)二CASE

WHEN(writtenExam+labExam)/2<60THEN'不及格'

WHEN(writtenExam+labExam)/2BETWEEN60AND69THEN差

WHEN(writtenExam+labExam)/2BETWEEN70AND79THEN中

WHEN(writtenExam+labExam)/2BETWEEN80AND89THEN'良'

EISE憂

END

FROMstuMarks

22

/、西華師范大學(xué)

等計(jì)算機(jī)學(xué)院課堂綜合練習(xí)

ExamNostiaNovrari11enExamILabExam

1|S271811s253039658

2|S271813s253026690加分前

3|S271816s253019382

4」S271818s253286165

ExamNostmNowiri11enExairiLabExain

1S271811s253039680加分后

2S271813s253026690

S271816s253019390

S271818s253286182

課堂練習(xí)：

則根據(jù)如下規(guī)則對(duì)機(jī)試成績進(jìn)行反復(fù)加分，直到平均分超過85分為止。

請(qǐng)編寫T-SQL語句實(shí)現(xiàn)。

90分以上:不加分

80—89分:加1分

70—79分:加2分

60—69分:加3分

60分以下:加5分

―、西華師范大學(xué)

等計(jì)算機(jī)學(xué)院課堂綜合練習(xí)答案

SELECT*FROMstuMarks一原始成績

DECLARE@labAvgINT聲明變量，用戶臨時(shí)存放平均分

WHILE(1=1)

BEGIN循環(huán)加分

UPDATEstuMarks

SETlabExam=根據(jù)機(jī)試成績酌情加分，

整個(gè)是一個(gè)UPDATE語句

CASE

WHENlabExam<60THENlabExam+5

WHENlabExambetween60AND69THENlabExam+3

WHENlabExambetween70AND79THENlabExam+2

WHENlabExambetween80AND89THENlabExam+1

ELSElabExam

END

SELECT@labAvg=AVG(labExam)FROMstuMarks

-IF@labAvg>=85獲或目前的平向分，判斷是否還繼續(xù)加分

BREAK

END

SELECT*FROMstuMarks一加分后的成績

唐靜,西華師范大學(xué)

宙計(jì)算機(jī)學(xué)院

第二部分子查詢

25

西華師范大學(xué)

計(jì)算機(jī)學(xué)院什么是子查詢3-1

istuNameJstuNostuSexstuAgestuSeatstuAddress

張

秋

麗

S25301男18_jl北京海淀

李

文

才

S25302男31二一地址不詳

李

斯

文

女一鱷河南洛陽

歐

俊

陽S25303

男二二!28新疆威武哈

梅

風(fēng)

5超S25304：4

S25318女235地址不詳

學(xué)員信息表

問題：

編寫T-SQL語句，查看年齡比“李斯文”大的學(xué)員，要求顯示這些學(xué)員

的信息？

分析：

第一步：求出“李斯文”的年齡；

第二步：利用WHERE語句，篩選年齡比“李斯文”大的學(xué)員；

26

―、西華師范大學(xué)

等計(jì)算機(jī)學(xué)院什么是子查詢3-2

□實(shí)現(xiàn)方法一：采用T-SQL變量實(shí)現(xiàn)

DECLARE@ageINT一定義變量，存放李斯文的年齡

SELECT@age=stuAgeFROMstuInfo

WHEREstuName='李斯文'-求出李斯文的年齡

-篩選比李斯文年齡大的學(xué)員

SELECT*FROMstuInfoWHEREstuAge>@age

GO

stuNamestuNostuSexstuAgestuSeatstuAddress

1李文才s25302男313地址不詳

2歐陽俊雄s25304男284新疆威武哈

3梅超風(fēng)S25318女23.5地址不詳

27

什么是子查詢3-3

口實(shí)現(xiàn)方法二：采用子查詢實(shí)現(xiàn)

SELECT*FROMstulnfo

WHEREstuAge>(SELECTstuAgeFROM

stulnfowherestuName-李斯文')

GO|子查詢一

口子查詢在WHERE語句中的一般用法：

SELECT...FROM表1WHERE字段1>(子查詢)

□外面的查詢稱為父查詢，括號(hào)中嵌入的查詢稱為子查詢

口UPDATE、INSERT、DELETE一起使用，語法類似于SELECT

語句

□將子查詢和比較運(yùn)算符聯(lián)合使用，必須保證子查詢返回的值不

能多于一個(gè)

28

11智廉用子查詢替換表連接3?1

111K

stuNamestuNostuSexstuAgestuSeatstuAddress

1張秋麗S25301男181北京海淀

2李文才S25302男313地址不詳

-?.

3李斯文S25303女222河南洛陽

4歐陽俊雄男新疆威武哈

___S2530428J4

___梅超風(fēng)S25318女ria;除地址不詳

ExamNostuNowrittenExamLabExam

1;S271811S253039058

2S271813S253026090

—

3S271816S253018782

學(xué)員信息表和成績表

問題：查詢筆試剛好通過（60分）的學(xué)員。

29

?需畸用子查詢替換表連接3?2

口實(shí)現(xiàn)方法一：采用表連接

SELECTstuNameFROMstulnfo

內(nèi)連接（等值連接）

INNERJOINstuMarks

ONstulnfo.stuNo=stuMarks.stuNo

WHEREwrittenExam=60

GO

）道0鬲I|乂亳也旨國??I|UstuDB

/*—實(shí)頊方法一：采用表旌接一*/

SELECTstuNameFROMstulnfoINNERJOINstuMarks

ONstulnfo.stuNo=stuMarks.stuNoWHEREwrittenExam=60

J

I—-

30

?1鷲％用子查詢替換表連接3-3

口實(shí)現(xiàn)方法二：采用子查詢

SELECTstuNameFROMstulnfo

WHEREstuNo=(SELECTstuNoFROM

stuMarksWHEREwrittenExam=60)

GO

口一般來說，表連接都可以用子查詢替換，但有的子查詢卻不能用

表連接替換

口子查詢比較靈活、方便，常作為增刪改查的篩選條件，適合于操

縱一個(gè)表的數(shù)據(jù)

□表連接更適合于查看多表的數(shù)據(jù)

31

◎IN子查詢4-1

[福3HW|,第噂回物|s|國，|3?■|[U~stuDBzll€s

INSERTINTOstuMarksVALUES「S271817'.'s25318'.60.52)

SELECT*FROMstumarks

一出現(xiàn)編譯錯(cuò)誤的子查詢一*/

SELECTstuNameFROMstulnfo

WHEREstuNo=(SELECTstuNoFROMstuMarksWHEREwrittenExam=60'

GO

ExamNostuNovrittenExamLabExam

S271811S253039058

S271813S253026090

S271816s2530l8782

S271817S253186052

消

務(wù)

級(jí)

行

器

取

:息￥-1

詢

在

查

回

的值512)6,

多>

個(gè)

、

查

子

1用

滸

作表

我a<s<=

達(dá)

一

一

一

：：3：.

問題：查詢筆試剛好通過的學(xué)員名單。

如何解決？

―、西華師范大學(xué)

等計(jì)算機(jī)學(xué)院IN子查詢4-2

口解決方法：采用IN子查詢

SELECTstuNameFROMstuInfo

將=號(hào)改為IN

WHEREstuNoIN

(SELECTstuNoFROMstuMarks

WHEREwrittenExam=60)

GO

置［方隔慟的子查詢嚶趣回多具記錄［信回工畫g冠，

嘉嘉用替換等于(了的比較子查詢

SELECTstuNameFROMstulnfo

WHEREstuNoIH(SELECTstuNoFROMstuMarksWHEREwrittenExani=60J

GO

11

__；stuName

1I李文才

2?梅超風(fēng)

口網(wǎng)格|即消息|

―、西華師范大學(xué)

等計(jì)算機(jī)學(xué)院IN子查詢4-3

stuNamestuNostuSexstuAgestuSeatstuAddress

1張秋麗S25301男181北京海淀

2_李文才S25302男313地址不詳

3李斯文S25303女222河南洛陽

4歐陽俊雄S25304男28新疆威武哈

二梅超風(fēng)S25318女235地址不詳

ExamNoIstuNowrittenExamLabExam

|(S271811S253039058

砥JS271813S2530260'90

^JS271816s253018782

學(xué)員信息表和成績表

滕木藕繇嚼櫓髓B矗單，只需要查看該學(xué)員對(duì)應(yīng)的學(xué)號(hào)

是否在考試成績表stuMarks中出現(xiàn)即可

34

西華師范大學(xué)

W計(jì)算機(jī)學(xué)院IN子查詢4-4

□參考語句

口網(wǎng)格|sD消息

演示：使用IN子查詢

35

西華師范大學(xué)

等計(jì)算機(jī)學(xué)院NOTIN子查詢

問題：查詢未參加考試的學(xué)員名單

分析：加上否定的NOT即可

窗▼自口同，為岫百腌國，■||JstuDB

/*一采用HOTIN干查間查看未參加舍法的學(xué)員名單一*/

SELECTstuNameFROMstulnfo

WHEREstuNoNOTIN(SELECTstuNoFROMstuMarksj'l

GO

stuName

1|歐陽俊雄

口網(wǎng)格回消息

36

編*西華師范大學(xué)一、八-/

金計(jì)算機(jī)學(xué)院EXISTS子查詢4-1

例如：數(shù)據(jù)庫的存在檢測

IFEXISTS(SELECT*FROM

sysDatabasesWHEREname=,stuDB,)

DROPDATABASEstuDB

CREATEDATABASEstuDB

....—建庫代碼略

37

西華師范大學(xué)

W計(jì)算機(jī)學(xué)院EXISTS子查詢4-2

□EXISTS子查詢的語法:

IFEXISTS(子查詢)

語句

□如果子查詢的結(jié)果非空，即記錄條數(shù)1條以上，則

EXISTS(子查詢)將返回真(true),否則返回假

(false)

口EXISTS也可以作為WHERE語句的子查詢，但一般都能

用IN子查詢替換

38

/、西華師范大學(xué)

等計(jì)算機(jī)學(xué)院EXISTS子查詢4-3

ExamNostuNowrittenExamLabExam

1|S271811S253039058

2_JS271813S253026090

3_JS271816S253018782

問題：

檢查本次考試，本班如果有人筆試成績達(dá)到80分以上，則每人提2分;

否則，每人允許提5分

分析：

是否有人筆試成績達(dá)到80分以上，可以采用EXISTS檢測

39

,徐西華師范大學(xué)L、八AA

⑥計(jì)算機(jī)學(xué)院EXISTS子查詢4-4

國■言IS囿I熱電晦3髓G國^V*?■|QstuDB三|￡g*

/*—采用EXISTS子查詢，進(jìn)行酌情加分一*/

IFEXISTS'SELECT*FROMstuMarksWHEREwrittenExam80)

BEGIN

print'本班有人筆試成^高于80分，每人只加2分,加分后的成績?yōu)椋?

UPDATEstuMarksSETwri11enExamwri11enExam

SELECT#FROMstuMarks

END

ELSE

BEGIN

print'本班無人筆試成績高于80分，每人可以加5分，加分后的成金為：’

UPDATEstuMarksSETwrittenExam-writtenExam+5

SELECT*FROMstuMarks

END

GO

<I_____________________________________________________________

本班有人輦試成蒲高于80分，每人只加2分，加分后的成蒲為:J

ExamNostuNowrittenExamLabExam!

S271811S253039258

S271813S253026290

S271816S253018982

S271817S253186252

（所影響的行數(shù)為4行）

演不：1史用匕人22于宣理

40

―西華師范大學(xué)L、八C-I-C-C▲

⑥計(jì)算機(jī)學(xué)NOTEXISTS子查詢2-1

ExamNostuNowritt已riExaikLabExam

匚S271811s253039058

2S271813S2530260-]90

I3S271816S253018782

問題：

檢查本次考試，本班如果沒有一人通過考試（筆試和機(jī)試成績都＞60

分），則試題偏難，每人加3分，否則，每人只加1分

分析：

沒有一人通過考試，即不存在“筆試和機(jī)試成績都＞60分”，可以采

用NOTEXISTS檢測

41

,冰西華師范大學(xué)八cc

w計(jì)算機(jī)學(xué)NOTEXISTS子查詢2-2

窗▼髭口囤[造電亳百觸c|口.|M>.|[□stuDB31€§智&|崎

/*一采用HOTEXISTS子查詢，根據(jù)試題難度加分一*/

IFNOTEXISTS(SELECT*FROMstuJIarksWHEREwrittenExam60ANDlabExam60'

BEGIN

print'本班無人通過考試，試題偏難，每人加3分，加分后的成績?yōu)椋骸?/p>

UPDATEstuMarksSETwri11enExam-wri11enExaiR-^3.,1abExam=labExam+3

SELECT*-FROMstuMarks

END

ELSE

BEGIN

print'本班考試成績一般，每人只加1分，加分后的成績?yōu)椋骸?/p>

UPDATEstuMarksSETwrittenExam-wri11enExam+1.,labExam=labExam+l

SELECTtFROMstuMarks

END

GO

±1

本班考試成^一般，每人只加1分，加分后的成績?yōu)?

ExamNostuNowrittenExamLabExam

S271811S25303

S271813S25302

S271816S25301

S271817s25318

（所影響的行數(shù)為4行）

T-SQL語句的綜合應(yīng)用

stuNameistuNostuSexstuAgestuSeat,stuAddress

應(yīng)到人數(shù)：5人;張秋麗s25301男181北京海淀

李文才S25302男313地址不詳

3李斯文S25303女222河南洛陽

歐陽俊雄S25304男284新疆威武哈

5_梅超風(fēng)S25318女235地址不詳

ExamNostuNowrittenExamiLabExam

1~

實(shí)到人數(shù)4人,S271811S253039359

2

缺考1人S271813S253026391

3S271816S253019083

4S271817S253186353

學(xué)員信息表和成績表

43

窿西華師范大學(xué)、工上人—E

0計(jì)算機(jī)學(xué)院T-SQL語句的綜合應(yīng)用

應(yīng)到人數(shù)j實(shí)到人數(shù)缺考人數(shù)

本次考試的缺考情況

1|5141

姓名學(xué)號(hào)筆試成績機(jī)試成績是否通過

是

1］張秋麗S2530190_89

是比較筆試平均分和機(jī)試

之］李文才S2530297

是平均分，較低者進(jìn)行循

3」李斯文93一

s2530365否環(huán)提分，但提分后最高

王缺考分不能超過97分。加

―歐陽俊雄S25304：缺考否

5-1梅超風(fēng)S253186359分后重新統(tǒng)計(jì)通過情況

總?cè)藬?shù)通過人數(shù)通過率

0露篇"T-SQL語句的綜合應(yīng)用

2.提取學(xué)員的成績信息并保存結(jié)果，包括學(xué)員姓名、學(xué)號(hào)、

筆試成績、機(jī)試成績、是否通過

1）提取的成績信息包含兩表的數(shù)據(jù)，所以考慮兩表連接，使

用左連接（LEFTJOIN）；

SELECTstuName...FROMstulnfo

LEFTJOINstuMarks...

2）要求新加一列“是否通過（isPass）”，可采用

CASE...ENDo為了便于后續(xù)的通過率統(tǒng)計(jì)，通過則為1,

沒通過為0

SELECT...isPass=CASE

WHENwrittenExam>=60……THEN1

ELSE0

END……

3）要求保存提?。ú樵儯┑慕Y(jié)果，可以使用我們曾學(xué)習(xí)過的

SELECT...INTOnewTable語句，生成新表并保存數(shù)據(jù)

圖號(hào)4^^T-SQL語句的綜合應(yīng)用

3.比較筆試平均分和機(jī)試平均分，對(duì)較低者進(jìn)行循環(huán)提分，但提分后最

高分不能超過97分：

1）使用IF語句判斷筆試還是機(jī)試偏低，決定對(duì)筆試還是機(jī)試提分；

2）使用WHILE循環(huán)給每個(gè)學(xué)員加分，缺考的除外，當(dāng)最高分超過97分時(shí)

退出循環(huán)；

3）因?yàn)榻o每位學(xué)員的筆試或機(jī)試提分了，有的學(xué)員可能提分后剛好通過

了，所以需要更新isPass（是否通過）歹人

UPDATEnewTable

SETisPass=CASE

WHENwrittenExam>=60andlabExam>=60THEN1

ELSE0

END

46

?盤混丁T-SQL語句的綜合應(yīng)用

4.提分后，統(tǒng)計(jì)學(xué)員的成績和通過情況：

1)使用別名實(shí)現(xiàn)中文字段名，即SELECT姓名=stuName,學(xué)號(hào)

=stuNo...

2)如果某個(gè)學(xué)員的成績?yōu)镹ULL(空)，則替換為“缺考”，否則原樣顯

示；

3)isPass列中的1替換為是，0替換為否;

SELECT……

，機(jī)試成績=CASE

WHENlabExamISNULLTHEN'缺考’

ELSEconvert(varchar(5),labExam)

END

，是否通過二CASE

WHENisPass=1THEN'S'

ELSE'否'

END……

泰西華師范大學(xué)、工上人―E

◎計(jì)算機(jī)學(xué)院T-SQL語句的綜合應(yīng)用

5.提分后統(tǒng)計(jì)學(xué)員的通過率情況：

1）通過人數(shù)：因?yàn)橥ㄟ^用1表示，沒通過用0表示，所以

isPass列的累加和即是通過人數(shù)；

2）通過率：同理，isPass列的平均值*100即是通過率;

48

/、西華師范大學(xué)

等計(jì)算機(jī)學(xué)院T-SQL參考語句

/*-本次考試的原始數(shù)據(jù)-*/

-SELECT*FROMstulnfo

-SELECT*FROMstuMarks

/*---------統(tǒng)計(jì)考試缺考情況---------------*/

SELECT應(yīng)到人數(shù)=(SELECTcount，)FROMstulnfo),一應(yīng)到人數(shù)為

子查詢表達(dá)式的別名

實(shí)到人數(shù)二(SELECTcountf)FROMstuMarks),

缺考人數(shù)二((SELECTcount。)FROMstulnfo)-(SELECTcount(*)

FROMstuMarks))

49

/、西華師范大學(xué)

等計(jì)算機(jī)學(xué)院T-SQL參考語句

/*—統(tǒng)計(jì)考試通過情況，并將結(jié)果存放在新表newTable中一*/

IFEXISTS(SELECT*FROMsysobjects

WHEREname='newTable')

DROPTABLEnewTable

SELECTstuName.stuInfo.stuNo.writtenExamJabExam,

isPass=CASE

WHENwrittenExam>=60andlabExam>=60THEN1

ELSE0

END

INTOnewTableFROMstulnfo

LEFTJOINstuMarks

ONstulnfo.stuNo=stuMarks.stuNo

-SELECT*FROMnewTable一查看統(tǒng)計(jì)結(jié)果，可用于調(diào)試

50

/、西華師范大學(xué)

等計(jì)算機(jī)學(xué)院T-SQL參考語句

/*-酌情加分：比較筆試和機(jī)試平均分，決定加哪門一*/

DECLARE@avgWrittennumeric(4,1)

DECLARE@avgLabnumeric(4,1)

SELECT@avgWritten=AVG(writtenExam)FROMnewTable

WHEREwrittenExamISNOTNULL

SELECT@avgLab=AVG(labExam)FROMnewTable

WHERElabExamISNOTNULL

IF@avgWritten<@avgLab

WHILE(1=1)-循環(huán)給筆試加分，最高分不能超過97分

BEGIN

UPDATEnewTableSETwrittenExam=writtenExam+1

IF(SELECTMAX(writtenExam)FROMnewTable)>=97

BREAK

END

ELSE…略…--循環(huán)給筆試加分，最高分不能超過97分

西華師范大學(xué)

計(jì)算機(jī)學(xué)院T-SQL參考語句

/*---------顯示考試最終通過情況-----------*/

SELECT姓名=stuName,學(xué)號(hào)二stuNo

，筆試成績=CASE

WHENwrittenExamISNULLTHEN'缺考，

ELSEconvert(varchar(5),writtenExam)

END

，機(jī)試成績=CASE

WHENlabExamISNULLTHEN'缺考’

ELSEconvert(varchar(5),labExam)

END

，是否通過二CASE

WHENisPass=1THEN'S'

ELSE'否'

END

FROMnewTable

T-SQL參考語句

/*--顯示通過率及通過人數(shù)-*/

SELECT總?cè)藬?shù)二count(*),通過人數(shù)二SUM(isPass),

ilxl^=(convert(varchar(5),AVG(isPass*100))+'%')

FROMnewTable

應(yīng)到人數(shù):實(shí)到人數(shù):缺考人數(shù)

-1.||541

姓名學(xué)號(hào)筆試成乏責(zé)機(jī)試成是否通過

是

11張秋麗S253019089

是

2」李文才S253026397

是

3李斯文S25303箱.65

否

V

歐陽俊雄S25304缺考缺考否

5梅超風(fēng)S253186359

卜總?cè)藬?shù):通過人數(shù)1通過率1

1]5360%

53

a西華師范大學(xué)

；金,「計(jì)算機(jī)學(xué)院

第四部分事務(wù)

54

西華師范大學(xué)K=,

①計(jì)算機(jī)學(xué)院為什么需要事務(wù)5?1

例如，銀行轉(zhuǎn)帳問題：

假定資金從帳戶A轉(zhuǎn)到帳戶B,至少需要兩步：

口帳戶A的資金減少

口然后帳戶B的資金相應(yīng)增加

箱|銀行轉(zhuǎn)帳

55

/、西華師范大學(xué)

等計(jì)算機(jī)學(xué)院為什么需要事務(wù)5-2

CREATETABLEbank_____________________

(|創(chuàng)建帳戶表，存放用戶的帳戶信息

customerNameCHAR(IO),--顧客姓名

currentMoneyMONEY--當(dāng)前余額

)

GO添加約束：根據(jù)銀行規(guī)定，帳戶

ALTERTABLEbank余額不能少于1元，否則視為銷戶

ADDCONSTRAINTCK_currentMoney

CHECK(currentMoney>=1)

GO|張三開戶，開戶金額為iooo元；李四開戶，開戶金額1元

INSERTINTObank(customerName,currentMoney)

VALUES。張三口000)

INSERTINTObank(customerName,currentMoney)

VALUES('李四'」)

西華師范大學(xué)

W計(jì)算機(jī)學(xué)院為什么需要事務(wù)5-3

customerNamecurrentMoney

張三1000.0000

李四1.0000

（所影響的行數(shù)另…2.書）?

口結(jié)果

口目前兩個(gè)帳戶的余額總和為：1000+1=1001元

57

―、西華師范大學(xué)

等計(jì)算機(jī)學(xué)院為什么需要事務(wù)5-4

口模擬實(shí)現(xiàn)轉(zhuǎn)帳:

□從張三的帳戶轉(zhuǎn)帳1000元到李四的帳戶

請(qǐng)問：

customerNamecurrentMoney

執(zhí)行轉(zhuǎn)帳語句后，張三、李四的

帳戶余額為多少？

張二1000.0000

季西匚6666…勺帳尸多—兀

1.0000

111：、t、八IL—I,C、I,I*l\A■iggARA\a4C\C\C\

喊客器;消息547,級(jí)別16,獲悉1,行1

UPDATE語句與COLUMNCHECK約束'CK_currentMoneyJ沖突。

該沖突發(fā)生于數(shù)據(jù)庫’stuDB',表W'，'}"ia門k‘一c：，二，~li】in1〉'ai】r「二門十Me~nF：v，

語句已終止。?張三的帳戶沒有減少

customerNamecurrentMoney?但李四的帳戶卻多了1000元

?1000+1001=2001元

張三：1000.0000：

持四總額多出了1000元！

口結(jié)果

~58

答西華師范大學(xué)0/1/rKT