SQL Server 参考案例

一、变量和赋值

• 查询出学生人数

-- 普通查询，结果为表
select count(*) as '学生人数' from Student;
go

-- 利用变量查询，定义变量时直接赋值，结果为字符串
declare @i tinyint=(select count(*) from Student)
print '学生人数：'+cast(@i as char(2))+'人';
go

-- 利用set赋值变量查询，结果为字符串
declare @i tinyint
set @i=(select count(*) from Student)
print '学生人数：'+cast(@i as char(2))+'人';
go

-- 利用select赋值变量查询，结果为字符串
declare @i tinyint
select @i=count(*) from Student
print '学生人数：'+cast(@i as char(2))+'人'
go

• 查询出年龄最大的学生姓名

-- 普通查询
select SNO,SName,Sage 
from Student 
where Sage=
(
    select max(Sage) from Student
)
go

-- 利用变量查询
declare @i tinyint
set @i=(select max(Sage) from Student)
select SNO,SName,Sage from Student where Sage=@i
go

• 对一个变量赋值多个值

declare @i tinyint
set @i=(select Sage from Student) -- 报错，set不可以赋值多个值给一个变量
print @i
go

declare @x tinyint
select @x=Sage from Student -- 将多个值依次赋值，并保留最后一个值
print @x
go

• 平均年龄和最大年龄

-- cast和convert，space(整数)，char(10)和char(13)表示换行、回车

declare @a int,@m int
set @a=(select avg(Sage) from Student)
set @m=(select max(Sage) from Student)
print '平均年龄：'+cast(@a as char(2))+'岁'+space(8)
      +'最大年龄：'+convert(char(2),@m)+'岁'
go

declare @a int,@m int
select @a=avg(Sage),@m=max(Sage) from Student
print '平均年龄：'+cast(@a as char(2))+'岁'+char(10)
      +'最大年龄：'+convert(char(2),@m)+'岁'
go

• 字符串拼接

-- 注意空格，char和varchar
declare @i char(10)
set @i='Hello '
print @i+'World!' -- 输出 Hello     World!
go 

declare @i varchar(10)
set @i='Hello '
print @i+'World!' -- 输出 Hello World!
go

• 打印出年龄在20岁到30岁之间的学生姓名

-- 声明变量后，变量默认值为null
declare @i varchar(50),@sno int
set @i=''
select @i=@i+Sname+space(2) from Student
where Sage between 20 and 30
select @sno=count(*) from Student
where Sage between 20 and 30
print '学生姓名为：'+char(10)+@i+char(10)
      +'总计：'+cast(@sno as char(2))+'人'
go

• 赋值NULL

declare @a int,@b int
set @a=100
set @b=200
set @a=(select Sage from Student where 1=0) -- 用set赋值null，接受null值
select @b=Sage from Student where 1=0 -- 用select赋值null，忽略null值
select @a,@b
go

• 局部变量的有效范围

-- 局部变量只在一个批处理内有效
declare @a int,@b int
set @a=100
set @b=200
begin -- 代码块开始
    print @a -- 输出 100
    print @b -- 输出 200
end --代码块结束
print @a --输出 100
go -- 批处理结束
print @b -- 报错，提示未定义变量

二、游标

• 利用游标修改、删除数据

-- 添加数据
insert into Student values(95011,'郭鹏',29,'男','17714198653','guocp3@qq.com')
insert into Student values(95012,'郭鹏',29,'男','17714198653','guocp3@qq.com')
go
-- 声明游标
declare test_cur cursor scroll for
    select SNO,SName from Student
-- 打开游标
open test_cur
-- 声明游标提取数据所要存放的变量
declare @a int,@b varchar(10)
-- 移动游标读取数据，into后面变量数量必须与游标查询结果集的字段数相同
fetch first from test_cur into @a,@b 
-- 判断是否提取成功
while @@fetch_status=0 
    begin
        if @a=95011
        begin
            update Student set Sname='GUOPENG' 
            where current of test_cur -- 修改当前行
        end
        if @a=95012
        begin
        delete Student
        where current of test_cur -- 删除当前行
        end
	-- 移动游标，提取下一行数据
    fetch next from test_cur into @a,@b
    end
-- 关闭游标
close test_cur
-- 删除游标
deallocate test_cur
go

• forward_only 和 scroll

-- forward_only 表示游标只能从数据集开始向数据集结束的方向读取，fetch next 是唯一选项
declare test_cursor1 cursor forward_only for
    select * from Student;

-- scroll 表示游标在定义的数据集中可以向任何方向，任何位置移动
declare test_cursor2 cursor scroll for
    select * from Student;

open test_cursor1;
open test_cursor2;

fetch next from test_cursor1; -- 正确执行
fetch last from test_cursor1; -- 报错 fetch: 提取类型 last 不能与只进游标一起使用
fetch last from test_cursor2; -- 正确执行

• 移动游标

declare test_Cursor cursor scroll for
    select Sname from Student
open test_Cursor
declare @c nvarchar(10)
-- 取下一行
fetch next from test_Cursor into @c
print @c
-- 取最后一行
fetch last from test_Cursor into @c
print @c
-- 取第一行
fetch first from test_Cursor into @c
print @c
-- 取上一行
fetch prior from test_Cursor into