数据库专题训练实验-------Transact-SQL编程
- 掌握常用函数的使用方法。
- 掌握流程控制语句的使用方法。
Sql server 2005
- 启动SQL Server 2005 查询编辑器。
- 应用转换函数。
- 应用聚合函数。
- 应用字符串函数。
- 应用IF··· ELSE语句。
- 应用WHILE语句。
- 创建一个名为XSCJ的数据库,在XSCJ数据库中创建如下关系表:学生信息表S,课程表C,成绩表SC。
学生基本信息表S:
Sno | Sname | Ssex | Sage | Sdept |
S1 | 李强 | 男 | 19 | 计算机 |
S2 | 王松 | 男 | 20 | 通信工程 |
S3 | 李丽 | 女 | 18 | 电子 |
S4 | 张平 | 女 | 21 | 计算机 |
S5 | 何晴 | 女 | 19 | 通信工程 |
S6 | 王小可 | 男 | 20 | 计算机 |
S7 | 张欢 | 男 | 20 | 电子 |
课程信息表C: 学生成绩表SC:
Sno | Cno | Score |
S1 | C2 | 85 |
S2 | C3 | 72 |
S3 | C4 | 90 |
S4 | C1 | 84 |
S5 | C2 | 58 |
S6 | C3 | 88 |
S3 | C1 | 69 |
S1 | C5 | 88 |
S1 | C3 | 95 |
Cno | Cname | Cteacher |
C1 | C语言 | 刘军 |
C2 | C++程序设计 | 李彤 |
C3 | 操作系统 | 吴明 |
C4 | 数据库 | 李白 |
C5 | 计算机网络 | 苏志朋 |
C6 | 数据结构 | 刘军 |
- 应用转换函数
- 打开“SQL Server Manageement Studio” 窗口。
- 单击“标准”工具栏上的“新建查询”按钮,打开“查询编辑器”窗口。
- 在窗口内直接输入以下语句,求课程表中课程号为“c5”的课程名称的长度,并输入结果。
USE XSCJ DECLARE @length int SELECT @length=LEN(Cname) FROM C WHERE Cno='c5' Print 'C 5号课程名称的长度为:'+ CONVERT(varchar(10),@length) |
4)单击“SQL编辑器” 工具栏上的“分析”按钮,检查输入的T-SQL语句是否有语法错误。如果有语法错误,则进行修改,直到没有语法错误为止。
5)确保无语法错误后,单击“SQL编辑器”工具栏上的“执行”按钮。
- 应用聚合函数
1)在“查询编辑器”窗口内输入以下语句,统计选课成绩表SC表中的学生人数,并输出结果。
USE XSCJ DECLARE @Count int SELECT @Count = COUNT(distinct Sno) FROM SC PRINT '选课人数共有'+CAST(@Count AS varchar(10))+'学生名' |
- 应用IF···ELSE 语句
1) 在“查询编辑器”窗口内输入以下语句,查询学号为“S 3” 的学生的平均分是否超过了75分, 若超过则输出“ X X 达到毕业资格要求”, 否则输出 “XX不能按期毕业”。
USE XSCJ DECLARE @sno char(6), @sname char(8) SET @sno ='s3' IF (SELECT AVG(score) FROM SC WHERE Sno=@sno) >=75 BEGIN SELECT @sname =Sname FROM S WHERE Sno=@sno PRINT @Sname+'达到毕业资格要求!!!!' END ELSE PRINT @sname+'XX不能按期毕业!' |
- 应用CASE语句
- 声明变量@cj为float型,变量@str为varchar(60), @cj赋值90。
- 编写程序确定该分数对应的等级,规则如下:大于等于60并小于70为‘及格’,大于等于70并小于80为‘中等’,大于等于80并小于90为‘良好’,大于等于90并小于等于100为‘优秀’,并且分数应在0到100之间,否则就会提示“成绩错误,成绩应在0到100之间”。
DECLARE @cj float, @str varchar(60) SET @cj=90 SET @str= CASE WHEN @cj>100 or cj<0 THEN ‘成绩输入错误,成绩应在0到100之间’ WHEN @cj>=60 and cj<70 THEN ‘及格’ WHEN @cj>=70 and cj<80 THEN ‘中等’ WHEN @cj>=80 and cj<90 THEN ‘优良’ WHEN @cj>=90 and cj<=100 THEN ‘优秀’ ELSE ‘不及格’ END PRINT ‘该学生的成绩等级是:’+@str GO |
- 综合应用
在XSCJ数据库中,按照如下脚本创建一个用户表,并向其中插入3行记录。然后编写一个程序,能实现以下功能:对于给定变量@hyuser=’stu1’,@hypwd=’111111’,①判断该用户是否存在;②该用户密码是否正确;③用户名和密码都正确则提示成功登录信息,否则提示错误信息。
USE XSCJ
CREATE TABLE hyuser(
hyid int identity(1,1) primary key,
hyname varchar(50) unique,
hypwd varchar(50)
)
INSERT INTO hyuser(hyname,hypwd) VALUES(‘stu1’, ‘111111’)
INSERT INTO hyuser(hyname,hypwd) VALUES(‘stu2’, ‘222222’)
INSERT INTO hyuser(hyname,hypwd) VALUES(‘stu3’, ‘333333’)
DECLARE @hyuser varchar(50), @hypwd varchar(50) SET @hyuser=’stu1’ SET @hypwd=’111111’ if exists(select * from hyuser where hyname=@hyuser) print ‘该用户存在!’ else print ‘该用户不存在!’ if exists(select * from hyuser where hyname=@hyuser and hypwd=@hypwd) print ‘成功登陆!’ else print ‘该用户密码不正确!’ GO |
- 用程序举例说明BREAK语句和CONTINUE语句的区别。
【例】从SC表中查询3号学生的平均成绩。如果平均成绩小于90分,则将他的各门课程成绩提高5分,然后再求平均成绩。看平均成绩是否高于90分,如果满足条件,则退出WHILE循环,否则同样每门课提高5分。最后输出其平均成绩。
USE xscj declare @avgscore int set @avgscore=(SELECT AVG(score) FROM sc where sno='3') WHILE @avgscore< 90 BEGIN UPDATE sc SET score = score+5 WHERE sno='3' set @avgscore=(SELECT AVG(score) FROM sc where sno='3') IF @avgscore> 90 BREAK ELSE CONTINUE END PRINT @avgscore |