T-SQL：功能使用实例（变量定义赋值、函数、游标、流程控制）

一、T-SQL：transact SQL，提供了变量定义，赋值操作，流程控制，函数等语句供用户使用

背景知识：
一、变量定义：declare @标识符 类型
二、变量赋值：set @变量1 = value1，select @变量1 = value1，@变量2 = value2
三、流程控制：流程空值和程序设计语言中的流程控制类似，流程空值语句是SQL提供给用户用于改变语句执行顺序的控制语句
四、函数：包括系统函数和用户自定义函数
五、游标：可以被视作一个指针，可以指向结果集中的任意一个位置并对其进行处理

名称解释（不区分大小写）

• student学生表：
  • 包含属性列：sno学号、sname学生姓名、age年龄、sex性别、dno学院编号、birthday生日
• sc选课信息表：
  • 包含属性列：sno学号、cno课程号、grade成绩
• dept学院信息表：
  • 包含属性列：dno学院编号、dname学院名称、dean学院负责人
• course课程信息表：
  • 包含属性列：cno课程号、cname课程名称、tname老师名称、credit学分、room教室

下面语句可以直接复制到SQL Server运行

方法不唯一，有问题欢迎留言讨论！代码块见文章末尾

运行时注意：由于函数、视图等必须是批处理语句中仅有的语句，可能需要单独创建SQL文件。
且变量命名不可重复，需要运行请自行创建新的SQL文件。

• 运行方法，选中需要执行的语句

二、代码

–系统数据类型
declare @Lee int
set @Lee = 32
select @Lee
print @Lee
select @@SERVERNAME --返回SQL服务器名称
select @@LANGUAGE --返回当前使用语言名
select @@VERSION --返回SQL服务器安装日期、版本和处理器类型
print APP_NAME() --返回当前会话应用程序
print USER_NAME() --返回用户数据库用户名
print GETDATE() --返回当前时间
print DATENAME(YYYY ,GETDATE())
– 补:返回年份print DATENAME(YYYY,GETDATE()) 或 select DATENAME(YYYY,GETDATE())

– 日期函数
print DATENAME(YYYY,GETDATE())
print DATEPART(MM,GETDATE())

–定义一个实型变量，并将其值输出
declare @f float
set @f = 456.26
print cast(@f as varchar(12))

–定义一个字符变量，并将其处理后输出
declare @mynumber char(20)
set @mynumber = ‘test’
select ‘compute’ + @mynumber as ‘计算结果’

–根据授课班号自定义变量，查询符合要求的学生成绩
declare @cn char(20),@f float
select @cn = ‘218801’,@f = 84
select * from sc where cno = @cn and grade >= @f

–BEGIN……END……的使用
begin
declare @myvar float
set @myvar = 1233.23
begin
print(‘变量@myval 的值为’)
print cast(@myvar as char(20))
end
end

–利用 CASE 查看学生的成绩等级
select sno,cno,
(case
when grade >= 80 then ‘A’
when grade >= 70 then ‘B’
when grade >= 60 then ‘C’
else ‘D’
end)
as 等级
from sc

–创建一个视图，统计每个学生的学习情况。若其平均成绩超过 90，则其学习情况为优秀；若其平均成绩在 80 和 90 之间，则其学习情况为优秀良好；依次类推。
create view study_view
as(
select sc.sno,sname,cno,
case
when (select avg(grade) from sc where sc.sno = student.sno) > 90 then ‘优秀’
when (select avg(grade) from sc where sc.sno = student.sno) > 80 then ‘良好’
when (select avg(grade) from sc where sc.sno = student.sno) > 70 then ‘中等’
when (select avg(grade) from sc where sc.sno = student.sno) > 60 then ‘合格’
else ‘不合格’
end
from student)

–利用 WHILE 结构，求:1+2+3+…+100 的值
declare @s int,@j int
select @j=0,@s=0
while(@j<=100)
begin
select @s = @s + @j,@j = @j + 1
end
print '1+2+3+4+5+6+…+100 = ’ + cast(@s as char)

–利用 IF…ELSE，查询课程号为’234901’的学生的总平均成绩，如果大于 90，输出优秀，在 80-90 之间，输出优良，其它输出一般
declare @avg int
select @avg = avg(grade) from sc where sno = ‘234901’
if @avg > 90
print ‘优秀’
else
begin
if @avg > 80
print ‘优良’
else
print ‘一般’
end

–定义一函数，实现如下功能，对于一给定的学号 studentSNO，查询该值在 student 中是否存在，存在返回 1，不存在返回 0。
create function check_id
(@sno char(8))
returns integer
as
begin
declare @num
if(exists(select * from student where sno = @sno))
set @num = 1
else
set @num = 0
return @num
end

–使用下面的 SQL 语句调用第 9 题的函数。要求：当向表 student 中插入一条记录时，首先调用函数 check_id，检查该记录的学号在表 student 中是否存在，不存在，才可以插入。
declare @num int
select @num = XSGL.check_id(‘20081200’)
if @num = 0
insert into student(sno,sname) values(‘20081300’,‘张文’)
–求学生选课的门数，列出其姓名及选课门数
select sname,(select count() from sc where sc.sno = student.sno)as ‘选课门数’ from student
–根据课程号动态查询学生的选课人数
–可以利用变量以及字符串连接字符‘＋’，动态生成 SQL 语句，达到依据条件，进行数据库动态查询目的。其中，连接字符串中若包含单引号’字符，则必须用两个’’,表示一个单引号’字符。
DECLARE @cno char(8)
DECLARE @sql varchar(8000)
SET @cno=‘218801’
–动态生成 SQL 语句
SET @sql='SELECT COUNT() FROM sc WHERE CNO=’’’+@cno+’’’’
EXEC(@sql)
SET @cno=‘203402’
SET @sql=’ SELECT COUNT(*) FROM sc WHERE CNO=’’’+@cno+’’’’
EXEC (@sql)

–用游标结合循环，输出全校各种姓氏及其人数
–定义记录数和循环变量
declare @count int,@i int
–定义姓氏和人数
declare @xs char(6),@rs int
–定义游标
declare @cursor cursor
–为游标赋值
set @cursor = cursor local scroll for
select distinct substring(sname,1,1) as xs,count(*) as 人数 from student
group by substring(sname,1,1)
–变量初始化
select @count=0,@i=0
–打开游标
open @cursor
–@@cursor_rows是系统变量，用于记录游标所指向的结果集的行数
set @count = @@cursor_rows
–打印记录数
print ‘人数’ + cast(@count as char)
–对游标中记录进行操作
if(@count <= 0)
begin
print(‘没有记录’)
end
else
begin
while(@i < @count)
begin
fetch next from @cursor into @xs,@rs
print cast(@i as char) + @xs + cast(@rs as char)
set @i = @i + 1
end
–关闭游标
close @cursor
end

–利用游标结合循环，统计学院各种姓氏的人数
DECLARE @count int
DECLARE @i int
DECLARE @xing char(6)
DECLARE @rs int
DECLARE @sql varchar(8000)
SET @sql=’’
SET @i=0
DECLARE @name_cursOR CURSOR
SET @name_cursOR =CURSOR LOCAL SCROLL FOR
SELECT DISTINCT SUBSTRING(SNAME,1,1) AS xing,count() AS rs
FROM student
GROUP BY SUBSTRING(SNAME,1,1)
OPEN @name_cursOR
SET @count=@@CURSOR_ROWS
IF @count<= 0
BEGIN
PRINT ‘没有记录’
END
ELSE
BEGIN
WHILE(@i<@count-300)
BEGIN
FETCH @name_cursOR INTO @xing,@rs
–动态生成前 300 个姓氏 SQL 语句
SET @sql=@sql+’(SELECT COUNT() FROM student WHERE DNO=a.DNO
AND SUBSTRING(SNAME,1,1)=’’’+@xing+’’’) AS ‘+@xing+’,’
SET @i=@i+1
END
FETCH @name_cursOR INTO @xing,@rs
–动态生成第 301 个姓氏 SQL 语句
SET @sql=@sql+’(SELECT count(*) FROM student WHERE DNO=a.DNO
AND SUBSTRING(SNAME,1,1)=’’’+@xing+’’’) AS '+@xing
CLOSE @name_cursOR
PRINT @sql
SET @sql=‘SELECT DNAME ,’+@sql+‘FROM (SELECT DISTINCT
STUDENT.DNO,DNAME FROM student,DEPT WHERE student.DNO=DEPT.DNO) AS a’
EXEC(@sql)
END

–利用 CASE 实现学生表中学院编号到学院名称的映射
select sno,sname,
(case
when substring(sno,5,1) = ‘1’ then ‘机电学院’
when substring(sno,5,1) = ‘2’ then ‘信息学院’
when substring(sno,5,1) = ‘3’ then ‘工商学院’
end) as 学院
from student

–定义一函数，实现如下功能，对于一给定的学号 studentSNO 和课程号 studentCNO 查询该值在 student 和 course 中是否存在，存在返回 1，不存在返回 0。
create function check
(@sno char(8),@cno char(8))
returns integer
as
begin
declare @num int
if(exists(select * from student where sno = @sno) and exists(select * from course where cno = @cno))
set @num = 1
else
set @num = 0
return @num
end

–根据教师名自定义变量，查询符合要求的教师授课情况
declare @tname char(8)
set @tname = ‘张聪’
select * from course where tname = @tname

–求授课班号及选修该授课班号的学生人数
select cno,(select count(*) from sc where sc.cno = course.cno) as 选修人数 from course

–定义一函数，根据学号返回学生的选课门数（参考 INSERT 触发器）
create function choose_lesson
(@sno char(8))
returns integer
as
begin
declare @count int
select @count = count(*) from sc where sno = @sno
return @count
end

–修改学生的成绩，若大于 80 分，增加 5 分，否则，增加 8 分
update sc
set grade = (
case
when(grade > 80) then grade + 5
else grade + 8
end
)

–（1） 按课程名称，统计其平均分，列出其课程名称和平均分
declare @cname char(8)
set @cname = ‘线性代数’
select cname,course.cno,avg(grade) from sc,course where cname = @cname group by cname,course.cno
select * from course where cname = ‘线性代数’

–（2） 求每个学生选课的门数及其平均分，列出其姓名、课程门数及平均分
select sname,student.sno,count(cno),avg(grade) from sc,student group by sname,student.sno

–（3） 定义一函数，依据学生的姓名，查询其所选课程的门数
create function lesson_count
(@sname char(8))
returns integer
as
begin
declare @count int
select @count = count(*) from sc where sno in (select sno from student where sname = @sname)
return @count
end

–（4） 根据学院名称，统计学生人数，列出学院名称和学生人数
declare @dname char(16)
set @dname = ‘机电工程学院’
select dname,count(sno) as ‘人数’
from dept,student
where dname = @dname and dept.dno = student.dno
group by dname

–（5） 若存在学号为‘20081200’的学生，则显示其姓名，否则，显示相应提示信息
declare @sno char(8)
set @sno = ‘20081200’
begin
if(exists(select * from student where sno = @sno))
select * from student where sno = @sno
else
print(‘不存在学生’)
end

–（6） 查找每个学生超过他选修课程平均成绩的课程相关信息，列出学号，课程号，成绩，选课平均成绩
select sno,cno,grade,avg(grade) 平均成绩 from sc a
where grade >= (
select avg(grade) from sc b
where a.sno = b.sno)
group by sno,cno,grade

–（7） 创建一视图，统计每门课程的学习情况。若课程平均成绩超过 90，则其学习情况为优秀；若课程平均成绩在 80 和 90 之间，则其学习情况为优秀良好；依次类推。
create view count_view
as
select cno,
(case
when avg(grade) > 90 then ‘优秀’
when avg(grade) > 80 then ‘良好’
when avg(grade) > 60 then ‘合格’
else ‘不合格’
end)
from sc
group by cno

–（8） 利用游标结合循环，统计各门课程的各种分数的人数。
select * from sc
order by cno

declare @i int,@count int
declare @cursor cursor
declare @rs int,@chengji float,@cno char(20)
declare @sql char(8000)
select @i=0,@count=0,@sql=’’
set @cursor = cursor local scroll for
select cno,grade,count(*) as 人数 from sc
group by cno,grade
order by cno
open @cursor
set @count = @@cursor_rows
print @count

if @count <= 0
begin
print ‘没有记录’
end
else
begin
while @i < @count
begin
print cast(@i as char) + @cno + cast(@chengji as char) + cast(@rs as char)
set @i = @i + 1
end
close @cursor
end

三、代码块

--系统数据类型
declare @Lee int
set @Lee = 32
select @Lee
print @Lee
select @@SERVERNAME --返回SQL服务器名称
select @@LANGUAGE   --返回当前使用语言名
select @@VERSION	--返回SQL服务器安装日期、版本和处理器类型
print APP_NAME()	--返回当前会话应用程序
print USER_NAME()	--返回用户数据库用户名
print GETDATE()		--返回当前时间
print DATENAME(YYYY ,GETDATE())
-- 补:返回年份print DATENAME(YYYY,GETDATE()) 或 select DATENAME(YYYY,GETDATE())

-- 日期函数
print DATENAME(YYYY,GETDATE())
print DATEPART(MM,GETDATE())

--定义一个实型变量，并将其值输出
declare @f float
set @f = 456.26
print cast(@f as varchar(12))

--定义一个字符变量，并将其处理后输出
declare @mynumber char(20)
set @mynumber = 'test'
select 'compute' + @mynumber as '计算结果'

--根据授课班号自定义变量，查询符合要求的学生成绩
declare @cn char(20),@f float
select @cn = '218801',@f = 84
select * from sc where cno = @cn and grade >= @f

--BEGIN……END……的使用
begin
	declare @myvar float
	set @myvar = 1233.23
	begin
		print('变量@myval 的值为')
		print cast(@myvar as char(20))
	end
end

--利用 CASE 查看学生的成绩等级
select sno,cno,
(case
	when grade >= 80 then 'A'
	when grade >= 70 then 'B'
	when grade >= 60 then 'C'
	else 'D'
	end)
as 等级
from sc

--创建一个视图，统计每个学生的学习情况。若其平均成绩超过 90，则其学习情况为优秀；若其平均成绩在 80 和 90 之间，则其学习情况为优秀良好；依次类推。
create view study_view 
as(
	select sc.sno,sname,cno, 
	case
		when (select avg(grade) from sc where sc.sno = student.sno) > 90 then '优秀'
		when (select avg(grade) from sc where sc.sno = student.sno) > 80 then '良好'
		when (select avg(grade) from sc where sc.sno = student.sno) > 70 then '中等'
		when (select avg(grade) from sc where sc.sno = student.sno) > 60 then '合格'
		else '不合格'
		end
	from student)

--利用 WHILE 结构，求:1+2+3+..+100 的值
declare @s int,@j int
select @j=0,@s=0
while(@j<=100)
	begin
		select @s = @s + @j,@j = @j + 1
	end
print '1+2+3+4+5+6+..+100 = ' + cast(@s as char)

--利用 IF…ELSE，查询课程号为'234901'的学生的总平均成绩，如果大于 90，输出优秀，在 80-90 之间，输出优良，其它输出一般
declare @avg int
select @avg = avg(grade) from sc where sno = '234901'
if @avg > 90
	print '优秀' 
else
	begin
	if @avg > 80
		print '优良'
	else
		print '一般'
	end

--定义一函数，实现如下功能，对于一给定的学号 studentSNO，查询该值在 student 中是否存在，存在返回 1，不存在返回 0。
create function check_id
(@sno char(8)) 
returns integer
as
begin
	declare @num
	if(exists(select * from student where sno = @sno))
		set @num = 1
	else
		set @num = 0
	return @num
end

--使用下面的 SQL 语句调用第 9 题的函数。要求：当向表 student 中插入一条记录时，首先调用函数 check_id，检查该记录的学号在表 student 中是否存在，不存在，才可以插入。
declare @num int
select @num = XSGL.check_id('20081200')
if @num = 0
insert into student(sno,sname) values('20081300','张文')
--求学生选课的门数，列出其姓名及选课门数
select sname,(select count(*) from sc where sc.sno = student.sno)as '选课门数' from student
--根据课程号动态查询学生的选课人数
--可以利用变量以及字符串连接字符‘＋’，动态生成 SQL 语句，达到依据条件，进行数据库动态查询目的。其中，连接字符串中若包含单引号’字符，则必须用两个’’,表示一个单引号’字符。
DECLARE @cno char(8)
DECLARE @sql varchar(8000)
SET @cno='218801'
--动态生成 SQL 语句
SET @sql='SELECT COUNT(*) FROM sc WHERE CNO='''+@cno+''''
EXEC(@sql)
SET @cno='203402'
SET @sql=' SELECT COUNT(*) FROM sc WHERE CNO='''+@cno+''''
EXEC (@sql)

--用游标结合循环，输出全校各种姓氏及其人数
--定义记录数和循环变量
declare @count int,@i int
--定义姓氏和人数
declare @xs char(6),@rs int
--定义游标
declare @cursor cursor
--为游标赋值
set @cursor = cursor local scroll for
	select distinct substring(sname,1,1) as xs,count(*) as 人数 from student
	group by substring(sname,1,1)
--变量初始化
select @count=0,@i=0
--打开游标
open @cursor
--@@cursor_rows是系统变量，用于记录游标所指向的结果集的行数
set @count = @@cursor_rows
--打印记录数
print '人数' + cast(@count as char)
--对游标中记录进行操作
if(@count <= 0)
	begin
		print('没有记录')
	end
else
	begin
	while(@i < @count)
		begin
		fetch next from @cursor into @xs,@rs
		print cast(@i as char) + @xs + cast(@rs as char)
		set @i = @i + 1
		end
	--关闭游标
	close @cursor
	end

--利用游标结合循环，统计学院各种姓氏的人数
DECLARE @count int
DECLARE @i int
DECLARE @xing char(6)
DECLARE @rs int
DECLARE @sql varchar(8000)
SET @sql=''
SET @i=0
DECLARE @name_cursOR CURSOR
SET @name_cursOR =CURSOR LOCAL SCROLL FOR
 SELECT DISTINCT SUBSTRING(SNAME,1,1) AS xing,count(*) AS rs
 FROM student
 GROUP BY SUBSTRING(SNAME,1,1)
OPEN @name_cursOR
SET @count=@@CURSOR_ROWS
IF @count<= 0
 BEGIN
 PRINT '没有记录'
 END
ELSE
 BEGIN
 WHILE(@i<@count-300)
 BEGIN
 FETCH @name_cursOR INTO @xing,@rs
--动态生成前 300 个姓氏 SQL 语句
 SET @sql=@sql+'(SELECT COUNT(*) FROM student WHERE DNO=a.DNO AND SUBSTRING(SNAME,1,1)='''+@xing+''') AS '+@xing+','
 SET @i=@i+1
 END
 FETCH @name_cursOR INTO @xing,@rs
--动态生成第 301 个姓氏 SQL 语句
 SET @sql=@sql+'(SELECT count(*) FROM student WHERE DNO=a.DNO AND SUBSTRING(SNAME,1,1)='''+@xing+''') AS '+@xing
 CLOSE @name_cursOR
 PRINT @sql
 SET @sql='SELECT DNAME ,'+@sql+'FROM (SELECT DISTINCT STUDENT.DNO,DNAME FROM student,DEPT WHERE student.DNO=DEPT.DNO) AS a'
 EXEC(@sql)
END

--利用 CASE 实现学生表中学院编号到学院名称的映射
select sno,sname,
(case
	when substring(sno,5,1) = '1' then '机电学院'
	when substring(sno,5,1) = '2' then '信息学院'
	when substring(sno,5,1) = '3' then '工商学院'
	end) as 学院
from student

--定义一函数，实现如下功能，对于一给定的学号 studentSNO 和课程号 studentCNO 查询该值在 student 和 course 中是否存在，存在返回 1，不存在返回 0。
create function check
(@sno char(8),@cno char(8))
returns integer
as
begin
	declare @num int
	if(exists(select * from student where sno = @sno) and exists(select * from course where cno = @cno))
		set @num = 1
	else
		set @num = 0
	return @num
end

--根据教师名自定义变量，查询符合要求的教师授课情况
declare @tname char(8)
set @tname = '张聪'
select * from course where tname = @tname

--求授课班号及选修该授课班号的学生人数
select cno,(select count(*) from sc where sc.cno = course.cno) as 选修人数 from course

--定义一函数，根据学号返回学生的选课门数（参考 INSERT 触发器）
create function choose_lesson
(@sno char(8))
returns integer
as
begin
	declare @count int
	select @count = count(*) from sc where sno = @sno
	return @count
end

--修改学生的成绩，若大于 80 分，增加 5 分，否则，增加 8 分
update sc
set grade = (
	case
		when(grade > 80) then grade + 5
		else	grade + 8
	end
)

--（1） 按课程名称，统计其平均分，列出其课程名称和平均分
declare @cname char(8)
set @cname = '线性代数'
select cname,course.cno,avg(grade) from sc,course where cname = @cname group by cname,course.cno
select * from course where cname = '线性代数'

--（2） 求每个学生选课的门数及其平均分，列出其姓名、课程门数及平均分
select sname,student.sno,count(cno),avg(grade) from sc,student group by sname,student.sno

--（3） 定义一函数，依据学生的姓名，查询其所选课程的门数
create function lesson_count
(@sname char(8))
returns integer
as
begin
	declare @count int
	select @count = count(*) from sc where sno in (select sno from student where sname = @sname)
	return @count
end

--（4） 根据学院名称，统计学生人数，列出学院名称和学生人数
declare @dname char(16)
set @dname = '机电工程学院'
select dname,count(sno) as '人数' 
from dept,student 
where dname = @dname and dept.dno = student.dno 
group by dname

--（5） 若存在学号为‘20081200’的学生，则显示其姓名，否则，显示相应提示信息
declare @sno char(8)
set @sno = '20081200'
begin
	if(exists(select * from student where sno = @sno))
		select * from student where sno = @sno
	else
		print('不存在学生')
end

--（6） 查找每个学生超过他选修课程平均成绩的课程相关信息，列出学号，课程号，成绩，选课平均成绩
select sno,cno,grade,avg(grade) 平均成绩 from sc a
where grade >= (
	select avg(grade) from sc b
	where a.sno = b.sno)
group by sno,cno,grade

--（7） 创建一视图，统计每门课程的学习情况。若课程平均成绩超过 90，则其学习情况为优秀；若课程平均成绩在 80 和 90 之间，则其学习情况为优秀良好；依次类推。
create view count_view
as
select cno,
(case
	when avg(grade) > 90 then '优秀'
	when avg(grade) > 80 then '良好'
	when avg(grade) > 60 then '合格'
	else '不合格'
	end)
from sc
group by cno

--（8） 利用游标结合循环，统计各门课程的各种分数的人数。
select * from sc
order by cno

declare @i int,@count int
declare @cursor cursor
declare @rs int,@chengji float,@cno char(20)
declare @sql char(8000)
select @i=0,@count=0,@sql=''
set @cursor = cursor local scroll for
	select cno,grade,count(*) as 人数 from sc
	group by cno,grade
	order by cno
open @cursor
set @count = @@cursor_rows
print @count

if @count <= 0 
	begin
		print '没有记录'
	end
else
	begin
		while @i < @count
			begin
				print cast(@i as char) + @cno + cast(@chengji as char) + cast(@rs as char)
				set @i = @i + 1
			end
		close @cursor
	end