SQLSERVER高级应用课件

a西华师范大学

；金,「计算机学院

SQLSERVER高级应用

佳，西华师范大学

计算机学院内容安排

口第一部分T-SQL编程

口第二部分子查询

口第三部分事务

口第四部分索引

□第五部分视图

口第六部分存储过程

□第七部分触发器

2

a西华师范大学

；金,「计算机学院

第一部分T-SQL编程

a西华师范大学

等计算机学院

SQL(StructuredQueryLanguage)：结构化查询语言，

有数据定义语言(DDL),例如:CREATE>DROP、ALTER等语

句；

数据操作语言(DML),例如:INSERT(插入)、UPDATE

(修改)、DELETE(删除)语句；

数据查询语言(DQL),例如：SELECT语句；

数据控制语言(DCL),例如:GRANT>REVOKE等语句。

T-SQL(Transact-SQL)即事务SQL,为SQL的扩展语言，是微软公

司对SQL语言的扩充，譬如加入了程序语言中的if,while等语法,

同时可以使用函数等数据库对象，是应用程序与SQLServer数

据库引擎沟通的主要语言。

4

不*、西华师范大学

®计算机学院使用变量

和C语言一样，变量分为：

口局部变量：

口局部变量必须以标记@作为前缀，^n@age

口局部变量的使用也是先声明，再赋值

口全局变量：

口全局变量必须以标记@@作为前缀，如@@丫3二由11

口全局变量由系统定义和维护，我们只能读取，不能修

改全局变量的值

5

西华师范大学

等计算机学院局部变至

□声明局部变量

DECLARE@变量名数据类型

例如：

DECLARE@namevarchar(8)

DECLARE@seatint

口赋值

—SET@变量名二值

0V

一SELECT@变量名二值

例如：

SET@name=，张三’|必须确保筛选出的记录只有1条

SELECT@name=stuNameFROMstulnfo

WHEREstuNo=,s25302,

6

**西华师范大学UI」r7*曰一“IC/

①计算机学院局部变堇小例2-1

stuNamestuNostuSexstuAgestuSeat|stuA,ddress

1张秋丽s25301男181北京海淀

2本文才s25302男282地址不详

Q

李斯文$25303女223河南洛阳

___欧阳俊雄S25304女344地址不详

[T

&__:梅超风s25318女235地址不详

学员信息表

问题：编写T-SQL查找李文才的左右同桌？

分析：

第一步，找出“李文才”的座位号；

第二步，李文才的座位号加1或减1

7

西华师范大学

计算机学院局部变量示例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

於、西华师范大学

W计算机学院全局变量

□全局变量都使用两个@标志作为前缀

变量含义

@@ERROR最后一个T-SQL错误的错误号

@@IDENTITY最后一次插入的标识值

@@LANGUAGE当前使用的语言的名称

@@MAX_CONNECTION可以创建的同时连接的最大数目

S

@@ROWCOUNT受上一个SQL语句影响的行数

@@SERVERNAME本地服务器的名称

@@TRANSCOUNT当前连接打开的事务数

@@VERSIONSQLServer"的版本信息

9

玲、西华师范大学

W计算机学院全局变量示例

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

print'服务器的名称：'卜酮SERVERNMIE

INSERTINTOstulnfo1stuNamestuNostuSex.stuAge

VALUES产武松-s25328'/男’，—

一如果大于。表示上一条语句执柠有错误

print'当前与昔误号'-^convert'：varchar15).：®ia>ERROR)

print'刚才报名的学员.座位号为：'

+convert(varchart5颁@IDENTITY)

UPDATEstuinfoSETstuAge=85

WHEREstuName='李文才'

print'当前错误号'+convert1varchar'5).颁®ERROR)

捻

错

»误

_一O

刚

学

3帮

手

我座位号（自动编号）

别

息

服

尾*

务5sI6

X专案冲突。该冲突发生于数据£

与C1C

可X

iupDACOL1^

句

房0

错

误

1前a547

西华师范大学

W计算机学院逻辑控制语句

口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计算疝学院IF-ELSE示例2-1

ExamNostuNowrittenExamLabExam

2—S271811s25303_j8058

Z.S271813s25302J5090

S271816s253017782

AS271818S25328J45]65

学员成绩表

问题：

统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀”,

并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差”,

并显示后三名学员的考试信息。

分析：

第一步，统计平均成绩存入临时变量；

第二步，用IF—ELSE判断；

12

9*西华师范大学.ll.—1/ricc

@计算机学院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'本班笔试成绩优秀，前三名的成绩为‘

SELECTTOP3♦:FROMstuMarksORDERBYwrittenExamDESC

END

ELSE

BEGIN

print'本班笔试成统较差，后三名的成绩对

SELECTTOP3+FROMstuMarksORDERBYwrittenExam

END

本班笔试成绩较差，后三名的成绩为

ExamNostuNowrittenExamLabExam

8058

S271811S253037782

S271816S253015090

S271813S25302

3

。露蠹高设置输出结果的格式

口为了文本消息和输出结果显示在同一窗口，需要

设置输出结果的格式

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

［文件电）编辑®萱询④I工具矍）窗口世）帮助凶

_L=;—■X—fi-

常规I编辑器结果I连接I连接属性I字体I脚本］=

默认结果目标◎）:结果显示为文本二I

L.MM!战湎戢——■P,

结果输出格式但）f）结果显示为表格氐

结果保存为文件….

14

西华师范大学

W计算机学院逻辑控制语句

□WHILE循环语句

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

while（条件）WHILE（条件）

BEGIN

语句1;语句1

语句2；语句2

break；BREAK

}END

同C语言一样：

•BREAK表示退出循环

•如果有多条语句，才需要BEGIN-END语句块

15

/、西华师范大学

等计算机学院WHILE示例2-1

ExamNostuNowrittenExamLabExam

2—S271811s25303_j8058

Z.S271813s25302J50]90

S271816s253017782

AS271818S25328J45]65

学员成绩表

问题：

本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简

单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分,

再看是否都通过，如此反复提分，直到所有人都通过为止。

分析：

第一步，统计没通过的人数；

第二步，如果有人没通过，加分；

第三步，循环判断。二

16

西华师范大学

◎夕计算机学院WHILE示例2-2

口参考语句

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

DECLARE@nint

TOILE(1=1)一条件永远成立

BEGIN

SELECT@n二COUNT，*)FROMstuMarksWHEREwrittenExam60一统计不及格人数

IF窗n0)

UPDATEstuMarksSETwrittenExam二writtenExam+2—每人加2分

ELSE

BREAK一退出循环

END

print'加分后的成绩如下：’

SELECT水FROMstuMarks

加分后的成绩如下:

ExamNostuNowrittenExainLabExain

S271811s253039658

S271813s253026690

S271816s253019382

S271818S253286165

―西华师范大学，皿4口出J、H-

①计算机学院逻车耳控制语句

口CASE—END多分支语句

CASE

WHEN条件1THEN结果1

WHEN条件2THEN结果2

ELSE其他结果

END

18

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

旧xamN口stuNowrittenExaroLabExam

1S271811s253038058

]S271813s253025090

S271816s253017782

JS271818S253284565

学员成绩表

问题：

采用美国的ABCDE五级打分制来显示笔试成绩。

A级：90分以上

B级：80—89分

C级：70—79分

D级：60—69分

E级：60分以下

19

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

口参考语句

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

—SELECT*FROMstuMarks一原始成绩

print'ABCDE五级显示成绩如下：’

SELECTstuNo.成绩二CASE

WHENwrittenExam60THEN'E'

WHENwrittenExamBETWEEN60AND69THE.N'

WHENwrittenExamBETWEEN70AND79THEN'C'

WHENwrittenExainBETWEEN80ANH89THEN'B'

EISE'£

END

FROMstuMarks

ABCDE五级显示成绩如下:

stuNo成绩

A

s25303D

s25302A

s25301N

s25328

20

―、西华师范大学

等计算机学院CASE-END课堂练习

考号学号笔试机试1平均分i等级1

1S271811$25303965877

2S271813S2530266'9078

S271816s253018287

4S271818s253286165二63

学员成绩分析

课堂练习：

请根据平均分和下面的评分规则，编写T-SQL语句查询学员的

成绩，如上图所示。

优：90分以上

良：80—89分

中：70—79分

差：60—69分

不及格：60分以下

21

编Z西华师范大学CACLLir,4一

©计算机学院CASE-END练习答案

USEstuDB

GO

SELECT考号二ExamNo,学号二stuNo,笔试二writtenExam,机试=labExam,

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

等级二CASE

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

WHEN(writtenExam+labExam)/2BETWEEN60AND69THEN差

WHEN(writtenExam+labExam)/2BETWEEN70AND79THEN中

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

EISE忧

END

FROMstuMarks

22

/、西华师范大学

等计算机学院课堂综合练习

ExamNostiaNovrari11enExamILabExam

1|S271811s253039658

2|S271813s253026690加分前

3|S271816s253019382

4」S271818s253286165

ExamNostmNowiri11enExairiLabExain

1S271811s253039680加分后

2S271813s253026690

S271816s253019390

S271818s253286182

课堂练习：

则根据如下规则对机试成绩进行反复加分，直到平均分超过85分为止。

请编写T-SQL语句实现。

90分以上:不加分

80—89分:加1分

70—79分:加2分

60—69分:加3分

60分以下:加5分

―、西华师范大学

等计算机学院课堂综合练习答案

SELECT*FROMstuMarks一原始成绩

DECLARE@labAvgINT声明变量，用户临时存放平均分

WHILE(1=1)

BEGIN循环加分

UPDATEstuMarks

SETlabExam=根据机试成绩酌情加分，

整个是一个UPDATE语句

CASE

WHENlabExam<60THENlabExam+5

WHENlabExambetween60AND69THENlabExam+3

WHENlabExambetween70AND79THENlabExam+2

WHENlabExambetween80AND89THENlabExam+1

ELSElabExam

END

SELECT@labAvg=AVG(labExam)FROMstuMarks

-IF@labAvg>=85获或目前的平向分，判断是否还继续加分

BREAK

END

SELECT*FROMstuMarks一加分后的成绩

唐静,西华师范大学

宙计算机学院

第二部分子查询

25

西华师范大学

计算机学院什么是子查询3-1

istuNameJstuNostuSexstuAgestuSeatstuAddress

张

秋

丽

S25301男18_jl北京海淀

李

文

才

S25302男31二一地址不详

李

斯

文

女一鳄河南洛阳

欧

俊

阳S25303

男二二!28新疆威武哈

梅

风

5超S25304：4

S25318女235地址不详

学员信息表

问题：

编写T-SQL语句，查看年龄比“李斯文”大的学员，要求显示这些学员

的信息？

分析：

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

第二步：利用WHERE语句，筛选年龄比“李斯文”大的学员；

26

―、西华师范大学

等计算机学院什么是子查询3-2

□实现方法一：采用T-SQL变量实现

DECLARE@ageINT一定义变量，存放李斯文的年龄

SELECT@age=stuAgeFROMstuInfo

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

-筛选比李斯文年龄大的学员

SELECT*FROMstuInfoWHEREstuAge>@age

GO

stuNamestuNostuSexstuAgestuSeatstuAddress

1李文才s25302男313地址不详

2欧阳俊雄s25304男284新疆威武哈

3梅超风S25318女23.5地址不详

27

什么是子查询3-3

口实现方法二：采用子查询实现

SELECT*FROMstulnfo

WHEREstuAge>(SELECTstuAgeFROM

stulnfowherestuName-李斯文')

GO|子查询一

口子查询在WHERE语句中的一般用法：

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

□外面的查询称为父查询，括号中嵌入的查询称为子查询

口UPDATE、INSERT、DELETE一起使用，语法类似于SELECT

语句

□将子查询和比较运算符联合使用，必须保证子查询返回的值不

能多于一个

28

11智廉用子查询替换表连接3・1

111K

stuNamestuNostuSexstuAgestuSeatstuAddress

1张秋丽S25301男181北京海淀

2李文才S25302男313地址不详

­•.

3李斯文S25303女222河南洛阳

4欧阳俊雄男新疆威武哈

___S2530428J4

___梅超风S25318女ria;除地址不详

ExamNostuNowrittenExamLabExam

1;S271811S253039058

2S271813S253026090

—

3S271816S253018782

学员信息表和成绩表

问题：查询笔试刚好通过（60分）的学员。

29

©需畸用子查询替换表连接3・2

口实现方法一：采用表连接

SELECTstuNameFROMstulnfo

内连接（等值连接）

INNERJOINstuMarks

ONstulnfo.stuNo=stuMarks.stuNo

WHEREwrittenExam=60

GO

）道0鬲I|乂亳也旨国►・I|UstuDB

/*—实顼方法一：采用表旌接一*/

SELECTstuNameFROMstulnfoINNERJOINstuMarks

ONstulnfo.stuNo=stuMarks.stuNoWHEREwrittenExam=60

J

I—-

30

®1鹫％用子查询替换表连接3-3

口实现方法二：采用子查询

SELECTstuNameFROMstulnfo

WHEREstuNo=(SELECTstuNoFROM

stuMarksWHEREwrittenExam=60)

GO

口一般来说，表连接都可以用子查询替换，但有的子查询却不能用

表连接替换

口子查询比较灵活、方便，常作为增删改查的筛选条件，适合于操

纵一个表的数据

□表连接更适合于查看多表的数据

31

◎IN子查询4-1

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

INSERTINTOstuMarksVALUES「S271817'.'s25318'.60.52)

SELECT*FROMstumarks

一出现编译错误的子查询一*/

SELECTstuNameFROMstulnfo

WHEREstuNo=(SELECTstuNoFROMstuMarksWHEREwrittenExam=60'

GO

ExamNostuNovrittenExamLabExam

S271811S253039058

S271813S253026090

S271816s2530l8782

S271817S253186052

消

务

级

行

器

取

:息¥-1

询

在

查

回

的值512)6,

多>

个

、

查

子

1用

浒

作表

我a<s<=

达

一

一

一

：：3：.

问题：查询笔试刚好通过的学员名单。

如何解决？

―、西华师范大学

等计算机学院IN子查询4-2

口解决方法：采用IN子查询

SELECTstuNameFROMstuInfo

将=号改为IN

WHEREstuNoIN

(SELECTstuNoFROMstuMarks

WHEREwrittenExam=60)

GO

置［方隔恸的子查询嘤趣回多具记录［信回工画g冠，

嘉嘉用替换等于(了的比较子查询

SELECTstuNameFROMstulnfo

WHEREstuNoIH(SELECTstuNoFROMstuMarksWHEREwrittenExani=60J

GO

11

__；stuName

1I李文才

2।梅超风

口网格|即消息|

―、西华师范大学

等计算机学院IN子查询4-3

stuNamestuNostuSexstuAgestuSeatstuAddress

1张秋丽S25301男181北京海淀

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

3李斯文S25303女222河南洛阳

4欧阳俊雄S25304男28新疆威武哈

二梅超风S25318女235地址不详

ExamNoIstuNowrittenExamLabExam

|(S271811S253039058

砥JS271813S2530260'90

^JS271816s253018782

学员信息表和成绩表

滕木藕繇嚼橹髓B矗单，只需要查看该学员对应的学号

是否在考试成绩表stuMarks中出现即可

34

西华师范大学

W计算机学院IN子查询4-4

□参考语句

口网格|sD消息

演示：使用IN子查询

35

西华师范大学

等计算机学院NOTIN子查询

问题：查询未参加考试的学员名单

分析：加上否定的NOT即可

窗▼自口同，为岫百腌国，■||JstuDB

/*一采用HOTIN干查间查看未参加舍法的学员名单一*/

SELECTstuNameFROMstulnfo

WHEREstuNoNOTIN(SELECTstuNoFROMstuMarksj'l

GO

stuName

1|欧阳俊雄

口网格回消息

36

编*西华师范大学一、八-/

金计算机学院EXISTS子查询4-1

例如：数据库的存在检测

IFEXISTS(SELECT*FROM

sysDatabasesWHEREname=,stuDB,)

DROPDATABASEstuDB

CREATEDATABASEstuDB

....—建库代码略

37

西华师范大学

W计算机学院EXISTS子查询4-2

□EXISTS子查询的语法:

IFEXISTS(子查询)

语句

□如果子查询的结果非空，即记录条数1条以上，则

EXISTS(子查询)将返回真(true),否则返回假

(false)

口EXISTS也可以作为WHERE语句的子查询，但一般都能

用IN子查询替换

38

/、西华师范大学

等计算机学院EXISTS子查询4-3

ExamNostuNowrittenExamLabExam

1|S271811S253039058

2_JS271813S253026090

3_JS271816S253018782

问题：

检查本次考试，本班如果有人笔试成绩达到80分以上，则每人提2分;

否则，每人允许提5分

分析：

是否有人笔试成绩达到80分以上，可以采用EXISTS检测

39

,徐西华师范大学L、八AA

⑥计算机学院EXISTS子查询4-4

国■言IS囿I热电晦3髓G国^V*►■|QstuDB三|£g*

/*—采用EXISTS子查询，进行酌情加分一*/

IFEXISTS'SELECT*FROMstuMarksWHEREwrittenExam80)

BEGIN

print'本班有人笔试成^高于80分，每人只加2分,加分后的成绩为：'

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

（所影响的行数为4行）

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

40

―西华师范大学L、八C-I-C-C▲

⑥计算机学NOTEXISTS子查询2-1

ExamNostuNowritt已riExaikLabExam

匚S271811s253039058

2S271813S2530260-]90

I3S271816S253018782

问题：

检查本次考试，本班如果没有一人通过考试（笔试和机试成绩都＞60

分），则试题偏难，每人加3分，否则，每人只加1分

分析：

没有一人通过考试，即不存在“笔试和机试成绩都＞60分”，可以采

用NOTEXISTS检测

41

,冰西华师范大学八cc

w计算机学NOTEXISTS子查询2-2

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

/*一采用HOTEXISTS子查询，根据试题难度加分一*/

IFNOTEXISTS(SELECT*FROMstuJIarksWHEREwrittenExam60ANDlabExam60'

BEGIN

print'本班无人通过考试，试题偏难，每人加3分，加分后的成绩为：’

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

SELECT*-FROMstuMarks

END

ELSE

BEGIN

print'本班考试成绩一般，每人只加1分，加分后的成绩为：’

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

SELECTtFROMstuMarks

END

GO

±1

本班考试成^一般，每人只加1分，加分后的成绩为:

ExamNostuNowrittenExamLabExam

S271811S25303

S271813S25302

S271816S25301

S271817s25318

（所影响的行数为4行）

T-SQL语句的综合应用

stuNameistuNostuSexstuAgestuSeat,stuAddress

应到人数：5人;张秋丽s25301男181北京海淀

李文才S25302男313地址不详

3李斯文S25303女222河南洛阳

欧阳俊雄S25304男284新疆威武哈

5_梅超风S25318女235地址不详

ExamNostuNowrittenExamiLabExam

1~

实到人数4人,S271811S253039359

2

缺考1人S271813S253026391

3S271816S253019083

4S271817S253186353

学员信息表和成绩表

43

窿西华师范大学、工上人—E

0计算机学院T-SQL语句的综合应用

应到人数j实到人数缺考人数

本次考试的缺考情况

1|5141

姓名学号笔试成绩机试成绩是否通过

是

1］张秋丽S2530190_89

是比较笔试平均分和机试

之］李文才S2530297

是平均分，较低者进行循

3」李斯文93一

s2530365否环提分，但提分后最高

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

―欧阳俊雄S25304：缺考否

5-1梅超风S253186359分后重新统计通过情况

总人数通过人数通过率

0露篇"T-SQL语句的综合应用

2.提取学员的成绩信息并保存结果，包括学员姓名、学号、

笔试成绩、机试成绩、是否通过

1）提取的成绩信息包含两表的数据，所以考虑两表连接，使

用左连接（LEFTJOIN）；

SELECTstuName...FROMstulnfo

LEFTJOINstuMarks...

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

CASE...ENDo为了便于后续的通过率统计，通过则为1,

没通过为0

SELECT...isPass=CASE

WHENwrittenExam>=60……THEN1

ELSE0

END……

3）要求保存提取（查询）的结果，可以使用我们曾学习过的

SELECT...INTOnewTable语句，生成新表并保存数据

图号4^^T-SQL语句的综合应用

3.比较笔试平均分和机试平均分，对较低者进行循环提分，但提分后最

高分不能超过97分：

1）使用IF语句判断笔试还是机试偏低，决定对笔试还是机试提分；

2）使用WHILE循环给每个学员加分，缺考的除外，当最高分超过97分时

退出循环；

3）因为给每位学员的笔试或机试提分了，有的学员可能提分后刚好通过

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

UPDATEnewTable

SETisPass=CASE

WHENwrittenExam>=60andlabExam>=60THEN1

ELSE0

END

46

®盘混丁T-SQL语句的综合应用

4.提分后，统计学员的成绩和通过情况：

1)使用别名实现中文字段名，即SELECT姓名=stuName,学号

=stuNo...

2)如果某个学员的成绩为NULL(空)，则替换为“缺考”，否则原样显

示；

3)isPass列中的1替换为是，0替换为否;

SELECT……

，机试成绩=CASE

WHENlabExamISNULLTHEN'缺考’

ELSEconvert(varchar(5),labExam)

END

，是否通过二CASE

WHENisPass=1THEN'S'

ELSE'否'

END……

泰西华师范大学、工上人―E

◎计算机学院T-SQL语句的综合应用

5.提分后统计学员的通过率情况：

1）通过人数：因为通过用1表示，没通过用0表示，所以

isPass列的累加和即是通过人数；

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

48

/、西华师范大学

等计算机学院T-SQL参考语句

/*-本次考试的原始数据-*/

-SELECT*FROMstulnfo

-SELECT*FROMstuMarks

/*---------统计考试缺考情况---------------*/

SELECT应到人数=(SELECTcount，)FROMstulnfo),一应到人数为

子查询表达式的别名

实到人数二(SELECTcountf)FROMstuMarks),

缺考人数二((SELECTcount。)FROMstulnfo)-(SELECTcount(*)

FROMstuMarks))

49

/、西华师范大学

等计算机学院T-SQL参考语句

/*—统计考试通过情况，并将结果存放在新表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一查看统计结果，可用于调试

50

/、西华师范大学

等计算机学院T-SQL参考语句

/*-酌情加分：比较笔试和机试平均分，决定加哪门一*/

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)-循环给笔试加分，最高分不能超过97分

BEGIN

UPDATEnewTableSETwrittenExam=writtenExam+1

IF(SELECTMAX(writtenExam)FROMnewTable)>=97

BREAK

END

ELSE…略…--循环给笔试加分，最高分不能超过97分

西华师范大学

计算机学院T-SQL参考语句

/*---------显示考试最终通过情况-----------*/

SELECT姓名=stuName,学号二stuNo

，笔试成绩=CASE

WHENwrittenExamISNULLTHEN'缺考，

ELSEconvert(varchar(5),writtenExam)

END

，机试成绩=CASE

WHENlabExamISNULLTHEN'缺考’

ELSEconvert(varchar(5),labExam)

END

，是否通过二CASE

WHENisPass=1THEN'S'

ELSE'否'

END

FROMnewTable

T-SQL参考语句

/*--显示通过率及通过人数-*/

SELECT总人数二count(*),通过人数二SUM(isPass),

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

FROMnewTable

应到人数:实到人数:缺考人数

-1.||541

姓名学号笔试成乏责机试成是否通过

是

11张秋丽S253019089

是

2」李文才S253026397

是

3李斯文S25303箱.65

否

V

欧阳俊雄S25304缺考缺考否

5梅超风S253186359

卜总人数:通过人数1通过率1

1]5360%

53

a西华师范大学

；金,「计算机学院

第四部分事务

54

西华师范大学K=,

①计算机学院为什么需要事务5・1

例如，银行转帐问题：

假定资金从帐户A转到帐户B,至少需要两步：

口帐户A的资金减少

口然后帐户B的资金相应增加

箱|银行转帐

55

/、西华师范大学

等计算机学院为什么需要事务5-2

CREATETABLEbank_____________________

(|创建帐户表，存放用户的帐户信息

customerNameCHAR(IO),--顾客姓名

currentMoneyMONEY--当前余额

)

GO添加约束：根据银行规定，帐户

ALTERTABLEbank余额不能少于1元，否则视为销户

ADDCONSTRAINTCK_currentMoney

CHECK(currentMoney>=1)

GO|张三开户，开户金额为iooo元；李四开户，开户金额1元

INSERTINTObank(customerName,currentMoney)

VALUES。张三口000)

INSERTINTObank(customerName,currentMoney)

VALUES('李四'」)

西华师范大学

W计算机学院为什么需要事务5-3

customerNamecurrentMoney

张三1000.0000

李四1.0000

（所影响的行数另…2.书）・

口结果

口目前两个帐户的余额总和为：1000+1=1001元

57

―、西华师范大学

等计算机学院为什么需要事务5-4

口模拟实现转帐:

□从张三的帐户转帐1000元到李四的帐户

请问：

customerNamecurrentMoney

执行转帐语句后，张三、李四的

帐户余额为多少？

张二1000.0000

季西匚6666…勺帐尸多—兀

1.0000

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

喊客器;消息547,级别16,获悉1,行1

UPDATE语句与COLUMNCHECK约束'CK_currentMoneyJ冲突。

该冲突发生于数据库’stuDB',表W'，'}"ia门k‘一c：，二，~li】in1〉'ai】r「二门十Me~nF：v，

语句已终止。•张三的帐户没有减少

customerNamecurrentMoney•但李四的帐户却多了1000元

・1000+1001=2001元

张三：1000.0000：

持四总额多出了1000元！

口结果

~58

答西华师范大学0/1/rKT