一、实验目的
1、实验目的
（1）、掌握利用各种数据类型声明局部变量的方法。
（2）、掌握为局部变量赋值的俩中方法。
（3）、掌握常用系统函数、运算符和表达式的功能和应用。
（4）、掌握Transact-SQL控制流语言的基本功能和分类。
（5）、掌握利用控制流语句实现基本的分支选择和循环处理功能。
（6）、了解其他控制流语句的功能和应用。
（7）、掌握SELECT各个字句的功能和检索数据的方法。
（8）、掌握WHERE字句中LIKE、IN、BETEEN、IS等逻辑运算符的使用。
（9）、掌握COMPUTE语句和聚合函数的使用。
二、实验内容和步骤
1、变量的应用
declare @sno char(8),@name varchar(10),@sex
datetime,@usually int,
@final numeric(4,1)
set @sno='32145467';
set @name='哈哈';
set @sex='男';
select @birthday ='1989-03-09',@usually=90,@final=80
print @sno+@name+@sex
print @birthday
print @usually
print @final nchar(12),@birthday
2、运算符的应用
A、比较运算符
use teaching
go
select * from student where birthday>'1989-01-01'
select * from teacher where department<>'计算机学院
'
B、逻辑运算符
use teaching
go
select * from score where studentno like '09%' and final between 60 and 90
------------------------
select * from teacher where prof in('教授','副教授
')
C、“+”号运算符：
declare @a char(5),@b varchar(5),@c int,@d decimal(5,2)
select @a='123',@b='456.5',@c=321,@d=564.4
print @a+@b
print @a+@d
print @c+@d
select @a='数据库',@b='程序开发'
print @a+@b
print @a+@d
D、位运算符
declare @a int,@b int
select @a=5,@b=12
select @a&@b,@a|@b,@a^@b,~@a
E、数学函数
select ceiling(16.3),ceiling(-16.8),ceiling(0.0)//向上取整
select floor(16.3),floor(-16.8),floor(0.0)//四舍五入
select round(123.456,2),round(123.456,-1),round(173.456,-2),round(123.456,-4)//第二个数字是四舍五入的位数，当负数时是“.”的左边
select round(175.86,0),round(175.86,0,1)
F、时间日期函数
declare @birthday datetime
set @birthday ='1989-08-21'
select @birthday as '生日',datediff(year,@birthday,getdate()) as '年龄'
select getdate() as '当前日期',year(getdate()) as '年份',datepart(month,getdate()) as '月份',
datename(day,getdate()) as '日期
'
G、转换函数
declare @count int,@date datetime
select @count=255,@date=getdate()
print '变量count的值为：'+cast(@count as varchar(5))
print cast('2009-7-07' as smalldatetime)+100
print convert(varchar(10),@date,102)
H、字符函数
declare @str as nchar(25)
set @str='SQL SERVER 2005 数据库应用与开发'
select len(@str),charindex('库应用',@str),substring(@str,5,6),replace(@str,'开发','设计
'),lower(@str),ascii(@str)
3、编写程序，根据姓名查询teaching数据库中学生的基本信息和选课信息，学生姓名通过变量输入。对于不存在的学生姓名输入值，打印提示信息。 use teaching
go
declare @sname nchar(8)
set @sname=' 许海冰'
if exists(select * from student where sname=@sname)
select student.*,courseno,usually,final from student,score
where student.studentno=score.studentno and sname=@sname
else
print '提示：不存在姓名为'+rtrim(ltrim(@sname))+'的学生资料
'
4、编写程序，查询所以学生选修课的期末成绩和对应等级，如学生末选修任何课程则输出提示信息。
use teaching
go
select student.studentno,sname,cname,final,
case
when final>=90 then '优'
when final>=80 then '良'
when final>=70 then '中'
when final>=60 then '及格'
when final<60 then '不及格'
when final is null then '未选修任何课程'
end as level
from student left join score on(student.studentno=score.studentno) left join course on
(course.courseno=score.courseno)
5、编写程序，判断字符变量@ch中存放的是字母字符、数字字符还是其他字符，并输出相关的信息。
declare @ch char
select @ch='d'
if upper(@ch)>='A' and upper(@ch)<='Z'
print @ch+'是字母字符'
else if @ch>='0' and @ch<='9'
print @ch+'是数字字符'
else
print @ch+'是其他字符'
当@ch='3'
时，
当@ch='#'
时，
6、编写程序，判断某个年份是否为闰年，年份由变量输入。 declare @year int
set @year =year(getdate())
if @year%4=0
begin
if @year%100=0
begin
if @year%400=0
print cast(@year as char(4))+'年是闰年'
else
print cast(@year as char(4))+'不年是闰年'
end
else
print cast(@year as char(4))+'年是闰年'
end
else
print cast(@year as char(4))+'不年是闰年
'
7、编写程序，输出在1~3000之间能被17整除的最大数值。 declare @s int ,@i int
select @s=0,@i=3000
while @i>1
begin
if @i%17=0
begin
print '1~3000之间能被整除的最大数值为：'+cast(@i as char(4)) break
end
set @i=@i-1
End
8、查询所有课程的课程编号、课程号和学分
use teaching
go
select courseno,cname,credit
from course
9、查询‘090501’班的所有学生的基本信息。
use teaching
go
select * from student where classno='090501'
10、查询student表中所有年龄大于20岁的男生的名字和年龄。
use teaching
go
select sname,datediff(year,birthday,getdate()) as age
from student
where datediff(year,birthday,getdate())>20 and sex='男
'
11、查询计算机学院教师的专业名称。
use teaching
go
select distinct major
from teacher
where department='计算机学院
'
12、查询选修课程且期末成绩不为空的学生人数。
use teaching
go
select count(distinct studentno) as '选修课程学生的人数' from score where final is not null
13、查询Email使用126邮箱的所有学生的学号、姓名和电子邮箱地址。
use teaching
go
select studentno,sname,email from student
14、查询每名学生的学号、选修课程数目、总成绩，并将查询结果存放到生成的“学生选课统计表”.
方法一：
use teaching
go
create table 学生选课统计表
(
studentno nchar(10) not null,
amount smallint null,
sum smallint null,
)
insert into 学生选课统计表
select student.studentno,count(courseno) as '选修课程数目',sum(final) as '总成绩' from student left join score on(student.studentno=score.studentno)
group by student.studentno
方法二：
use teaching
go
select studentno,count(courseno) as '选修课程数目',sum(final) as '总成绩' into 学生选课统计表
from score
group by studentno
-------------------------------------
select * from 学生选课统计表
select sname,courseno,final
from student,score
where (courseno='c05109'
student.studentno=score.studentno
and final between 90 and 100 or courseno='c05103') and
15、查询score表中选修‘c05109’或‘c05103’课程，并且课程期末成绩在90～100分之间的学生姓名和期末成绩。
use teaching
go
select sname,final
from score,student
where final between 90 and 100 and courseno in('c05109','c05103') and
student.studentno=score.studentno
16、查询student表中所有学生的基本信息，查询结果按班级号classno升序排序，同一班级中的学生按入学成绩point降序排列。
select * from student
order by classno asc,point desc
17、查询选修‘c05109’课程，并且期末成绩在5名的学生学号、课程号和期末成绩。
select top 2 studentno,courseno,final
from score
where courseno='c05109'
order by final desc
18、查询各班学生的人数。
select classno,count(*)
from student
group by classno
19、查询各班期末成绩的最高分和最低分。
select courseno,max(final) as '最高分',min(final) as '最低分'
from score
where final is not null
group by courseno
20、查询教授两门及以上课程的教师编号、课程编号和任课班级。
select teacherno,courseno,classno
from teach_class
where teacherno in(
select teacherno
from teach_class
group by teacherno
having count(*)>=2)
21、查询课程编号以‘c05’开头、被3名及以上学生选修且期末成绩的平均分高于75分的课程号、选修人数和期末成绩平均分，并按平均分降序排序。
select courseno,count(studentno) as '人数',avg(final) as '平均成绩'
from score
where courseno like 'c05%' and final is not null
group by courseno
having count(studentno)>=3 and avg(final)>=75
22、查询所有08级学生的期末成绩平均分，要求利用COMPUTE BY方法显示每一名学生编号、课程号、期末成绩的明细表，以及期末成绩平均分的汇总表
select studentno,courseno,final
from score
where studentno like '08%'
order by studentno
compute avg(final) by studentno
23、查询所有女生入学成绩的最高分，要求利用COMPUTE BY 方法既显明细
有显示汇总结果。
select *
from student
where sex='女'
compute max(point)
四、实验报告总结
（1）、局部变量俩种赋值方法及区别。
赋值时需要使用SET和SELECT命令。与SELECT命令相比，SET命令一次只能为一个变量赋值，而SELECT命令可以同时多个变量赋值。
（2）、数据类型的隐式转换和显示转换。
隐式转换：SQL Server 2005 可以自动对某些表达式进行转换，这种转换称为隐式转换。转换时不必使用CAST 或 CONVERT来进行隐式转换。
显示转换：使用CAST 或 CONVERT函数可以将一种数据类型的表达式强制转换为另一种数据类型。
（3）、GROUP BY 子句中分组依据表达式与SELECT子句中选择列表的对应关系。
当使用GROUP BY 子句时，出现在查询的SELECT列表中的每一列都必须同时出现在GROUP BY 子句中。