数据库专题训练------T-SQL编程

数据库专题训练实验-------Transact-SQL编程

【实验目的】

1. 掌握常用函数的使用方法。
2. 掌握流程控制语句的使用方法。

【实验环境】

Sql server 2005

【实验重点及难点】

1. 启动SQL　Server 2005 查询编辑器。
2. 应用转换函数。
3. 应用聚合函数。
4. 应用字符串函数。
5. 应用IF··· ELSE语句。
6. 应用WHILE语句。

【实验内容】

1. 创建一个名为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	数据结构	刘军

1. 应用转换函数

1. 打开“SQL　Server Manageement Studio” 窗口。
2. 单击“标准”工具栏上的“新建查询”按钮，打开“查询编辑器”窗口。
3. 在窗口内直接输入以下语句，求课程表中课程号为“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. 应用聚合函数

　 1）在“查询编辑器”窗口内输入以下语句，统计选课成绩表SC表中的学生人数，并输出结果。

USE XSCJ DECLARE @Count int SELECT  @Count =  COUNT(distinct Sno)  FROM  SC PRINT  '选课人数共有'+CAST(@Count AS varchar(10))+'学生名'

1. 应用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不能按期毕业！'

1. 应用CASE语句
   1. 声明变量@cj为float型，变量@str为varchar(60)， @cj赋值90。
   2. 编写程序确定该分数对应的等级，规则如下：大于等于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

1. 综合应用

在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

1. 用程序举例说明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