T-SQL语言实训

T-SQL语言实训

上机目的：1、掌握局部变量的定义、赋值及使用

2、掌握全局变量的使用及如何输出显示数据

3、掌握IF、WHILE、CASE逻辑控制语句

上机内容：

1、局部变量的使用

表stuinfo

问题：

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

第一步，找出“李文才“的座位号；第二步，李文才的座位号加1或减1 /*--查找李文才的信息--*/

DECLARE @name varchar(8) --学员姓名 SET @name='李文才' --使用SET赋值

SELECT * FROM stuInfo WHERE stuName = @name /*--查找李文才的左右同桌--*/ DECLARE @seat int --座位号

SELECT @seat=stuSeat FROM stuInfo --使用SELECT赋值 WHERE stuName=@name SELECT * FROM stuInfo

WHERE (stuSeat = @seat+1) OR (stuSeat = @seat-1) GO

2、全局变量的使用 变量 @@ERROR @@IDENTITY @@LANGUAGE @@MAX_CONNECTIONS @@ROWCOUNT @@SERVERNAME @@TRANSCOUNT @@VERSION 含义 最后一个T-SQL错误的错误号 最后一次插入的标识值 当前使用的语言的名称 可以创建的同时连接的最大数目 受上一个SQL语句影响的行数 本地服务器的名称 当前连接打开的事务数 SQL Server的版本信息 print 'SQL Server的版本'+@@VERSION print '服务器的名称: '+@@SERVERNAME

INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES('武松','s25328','男','23')

print '当前错误号'+convert(varchar(5),@@ERROR)

print '刚才报名的学员，座位号为:' +convert(varchar(5),@@IDENTITY )

--如果大于0表示上一条语句执行有错误

UPDATE stuinfo SET stuAge=85 WHERE stuName='李文才'

print '当前错误号'+convert(varchar(5),@@ERROR) GO

3、IF－ELSE语句

表:stumarks

问题：

统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息 。 分析：

第一步，统计平均成绩存入临时变量； 第二步，用IF－ELSE判断；

4、WHILE循环语句

表：stumarks 问题：

本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止 。 分析：（要求修改循环条件实现该问题） 第一步，统计没通过的人数 ； 第二步，如果有人没通过，加分； 第三步，循环判断。

DECLARE @n int

WHILE(1=1) --条件永远成立 BEGIN

SELECT @n=COUNT(*) FROM stuMarks

WHERE writtenExam<60 --统计不及格人数 IF (@n>0)

UPDATE stuMarks --每人加2分 SET writtenExam=writtenExam+2 ELSE

BREAK --退出循环 END

print '加分后的成绩如下：' SELECT * FROM stuMarks

5、CASE－END多分支语句 CASE

WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ……

ELSE 其他结果 END

(1)

表：stumarks

问题：

采用美国的ABCDE五级打分制来显示笔试成绩。 A级: 90分以上 B级： 80－89分 C级: 70－79分 D级： 60－69分 E级： 60分以下

print 'ABCDE五级显示成绩如下：' SELECT stuNo, 成绩= CASE

WHEN writtenExam<60 THEN 'E'

WHEN writtenExam BETWEEN 60 AND 69 THEN 'D' WHEN writtenExam BETWEEN 70 AND 79 THEN 'C' WHEN writtenExam BETWEEN 80 AND 89 THEN 'B' ElSE 'A' END FROM stuMarks (2)

表:labexam 练习：

请根据平均分和下面的评分规则，编写T-SQL语句查询学员的成 绩，如上图所示。 优 ：90分以上 良 ：80－89分 中 ：70－79分 差 ：60－69分

不及格 ：60分以下

USE stuDB GO

SELECT 考号=ExamNo,学号=stuNo,笔试=writtenExam,机试=labExam, 平均分=(writtenExam+labExam)/2, 等级=CASE

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

WHEN (writtenExam+labExam)/2 BETWEEN 60 AND 69 THEN '差' WHEN (writtenExam+labExam)/2 BETWEEN 70 AND 79 THEN '中' WHEN (writtenExam+labExam)/2 BETWEEN 80 AND 89 THEN '良' ElSE '优' 加分前 END FROM stuMarks 6、综合练习 练习： 加分后 请根据如下规则对机试成绩进行反复加分，直到平均分超过85分为止。请编写T-SQL语句实现。 90分以上： 不加分 80－89分： 加1分 70－79分： 加2分 60－69分： 加3分 60分以下： 加5分

SELECT * FROM stuMarks --原始成绩 DECLARE @labAvg INT WHILE(1=1) BEGIN

UPDATE stuMarks SET labExam=

CASE

WHEN labExam<60 THEN labExam+5

WHEN labExam between 60 AND 69 THEN labExam+3 WHEN labExam between 70 AND 79 THEN labExam+2 WHEN labExam between 80 AND 89 THEN labExam+1 ELSE labExam END

SELECT @labAvg=AVG(labExam) FROM stuMarks IF @labAvg>=85 BREAK END

SELECT * FROM stuMarks

--加分后的成绩

本文来源：https://www.bwwdw.com/article/bmit.html