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 @c
print @c
-- 取第三行
fetch absolute 3 from test_Cursor into @c
print @c
-- 取相对目前来说上一行
fetch relative -1 from test_Cursor into @c
print @c
- 打印出年龄在20岁到30岁之间的学生姓名
declare test_cur cursor scroll for
select SName from Student where Sage between 20 and 30
open test_cur
declare @i varchar(50),@name varchar(10)
set @i=''
fetch next from test_cur into @name
set @i=@name+space(2)
while @@fetch_status=0
begin
fetch next from test_cur into @name
set @i=@i+@name+space(2)
end
print @i
close test_cur
deallocate test_cur
go
- Test表中有10万行数据，从第1条数据开始，按照 n(n+1)/2 偏移量取出前10行数据
-- 插入10万行数据
create table Test
(
Sno int primary key,
Sname varchar(20) not null,
Sage tinyint,
Sex char(2)
)
go
declare @i int
set @i=100001
while @i<200001
begin
insert into Test values(@i,'Mike'+cast(@i as varchar(10)),24,'女')
set @i=@i+1
end
go
select count(*) from Test
go
-- 数据操作
declare test_cur cursor scroll for
select Sno,Sname,Sage,Sex from Test
open test_cur
declare @i int,@x int
declare @t1 table -- 声明表变量
(
Sno int primary key,
Sname varchar(20) not null,
Sage tinyint,
Sex char(2)
)
declare @Sno int,@Sname varchar(20),@Sage tinyint,@Sex char(2)
set @i=1
set @x=1
while @i<11
begin
fetch relative @x from test_cur into @Sno,@Sname,@sage,@Sex
insert into @t1 values(@Sno,@Sname,@sage,@Sex)
set @i=@i+1
set @x=@i*(@i+1)/2
end
select * from @t1
close test_cur
deallocate test_cur
go
- 从第5000条数据开始，按照 n(n+1)/2 偏移量取出前10行数据，并用 print 输出
declare test_cur cursor scroll for
select Sno,Sname,Sage,Sex from Test
open test_cur
declare @i int,@x int,@m varchar(100)
declare @t1 table
(
Sno int primary key,
Sname varchar(20) not null,
Sage tinyint,
Sex char(2)
)
declare @Sno int,@Sname varchar(20),@Sage tinyint,@Sex char(2)
set @i=1
set @x=1
set @m=''
fetch absolute 4999 from test_cur into @Sno,@Sname,@Sage,@Sex -- 将指针移动到第4999行,并读取数据
print ' 学号 '+' 姓名'+' 年龄'+' 性别' -- 用空格调整排版
while @i<11
begin
fetch relative @x from test_cur into @Sno,@Sname,@sage,@Sex
insert into @t1 values(@Sno,@Sname,@sage,@Sex)
set @i=@i+1
set @x=@i*(@i+1)/2
set @m=cast(@Sno as char(6))+space(4)+@Sname+space(3)
+cast(@Sage as char(2))+space(4)+@sex -- 用空格调整排版
print @m -- 循环打印
end
close test_cur
deallocate test_cur
go
三、条件和选择
- 检查图书库存情况
-- 库存量=进货量-借出量
declare @bookin int,@borrowed int,@stock int,@bookid int
set @bookid=39003 -- 可利用存储过程填入Bookid
set @bookin=(
select BookIncoming
from Book
where BookID=@bookid
)
set @borrowed=(
select count(*)
from BorrowBook
group by BookID
having BookID=@bookid
)
set @stock=@bookin-isnull(@borrowed,0) -- 注意null的情况
print '库存量：'+cast(@bookin as varchar(6))
print '被借量：'+cast(isnull(@borrowed,0) as varchar(6))
print '库存量：'+cast(@stock as varchar(6))
-- 方法1：if...else...
if @stock<10
print '库存不足！'
else if @stock between 10 and 20
print '库存尚可！'
else
print '库存充足！'
go
-- 方法2：GOTO
if @stock<10
goto T1
else if @stock between 10 and 20
goto T2
else
goto T3
T1: -- 使用GOTO语句可实现代码模块化
print '库存不足！'
goto THEEND
T2:
print '库存尚可！'
goto THEEND
T3:
print '库存充足！'
goto THEEND
THEEND:
go
-- 方法3：CASE...WHEN...THEN...ELSE...END
declare @str varchar(50)
set @str=''
set @str=
(
case when @stock<10 then '库存不足！' -- 使用CASE语句，只能赋值，不能输出
when @stock between 10 and 20 then '库存尚可！'
else '库存充足！'
end
)
print @str
go
- 统计学生成绩表，按以下结构显示
| 学号 | 姓名 | 语文 | 数学 | 英语 | 物理 | 化学 | 总分 | 均分 |
|---|---|---|---|---|---|---|---|---|
| 95001 | 陈晓明 | 89 | 71 | 92 | 64 | 78 | 394 | 78.80 |
-- case语句更改表结构
select t2.SNo as '学号',SName as '姓名',语文,数学,英语,物理,化学,总分,均分
from Stu as t1 left join (
select SNo,
sum(case CNo when 39001 then Result else 0 end) as '语文',
sum(case CNo when 39002 then Result else 0 end) as '数学',
sum(case CNo when 39003 then Result else 0 end) as '英语',
sum(case CNo when 39004 then Result else 0 end) as '物理',
sum(case CNo when 39005 then Result else 0 end) as '化学',
sum(Result) as '总分',
convert(decimal(10,2),sum(Result)*1.0/5) as '均分'
from Score group by SNo
) as t2 on t1.SNo=t2.SNo
go
- 创建表，男性工资应大于5000，女性工资应大于4000
-- case 和 check
create table test
(
Sex char(2),
Salary int,
constraint Check_Salary check
(
case Sex
when '女' then
case when Salary>4000 then 1 else 0 end
when '男' then
case when Salary>5000 then 1 else 0 end
else 0 end=1
)
)
go
- 工资大于5000的，下降10%，2000到4500的，上涨15%
-- case 和 update
update Test1 set Salary=
(
case when Salary>5000 then Salary*0.9
when Salary between 2000 and 4500 then Salary*1.15
else Salary
end
)
go
四、循环
- 在一行中打印10个“#”
declare @i int,@str varchar(50)
set @i=0
set @str=''
while @i<10
begin
set @str=@str+' # ' -- 字符串拼接
set @i=@i+1
end
print @str
go
- break 和 continue
declare @i int
set @i=0
while @i<10
begin
set @i=@i+1
if @i=5
break -- 跳出当前循环，执行end后面语句
print @i
end
go
declare @i int
set @i=0
while @i<10
begin
set @i=@i+1
if @i=5
continue-- 结束当前循环，并开始下一循环
print @i
end
go
- 逐行打印出学生名单
-- 方法一：select赋值
declare @name varchar(100)
set @name=''
select @name=@name+Sname+char(13) from Student
print @name
go
-- 方法二：游标读取数据，@@fetch_status控制循环
declare @name varchar(100),@i varchar(10)
set @name=''
declare Sname_cur cursor for
select Sname from Student
open Sname_cur
fetch next from Sname_cur into @i
while @@fetch_status=0
begin
set @name=@name+@i+char(10)
fetch next from Sname_cur into @i
end
print '学生名单为：'+char(10)+@name
close Sname_cur
deallocate Sname_cur
go
-- 方法三：游标读取数据，@@fetch_status控制循环
declare @name varchar(10)
set @name=''
print '学生名单为：'
declare Sname_cur cursor for
select Sname from Student
open Sname_cur
fetch next from Sname_cur into @name
while @@fetch_status=0
begin
print @name
fetch next from Sname_cur into @name
end
close Sname_cur
deallocate Sname_cur
-- 方法四：游标读取数据，count(*)控制循环
declare @name varchar(10),@a int,@b int
set @name=''
select @a=count(*) from Student
set @b=1
print '学生名单为：'
declare Sname_cur cursor for
select Sname from Student
open Sname_cur
fetch next from Sname_cur into @name
while @b<=@a
begin
print @name
fetch next from Sname_cur into @name
set @b=@b+1
end
close Sname_cur
deallocate Sname_cur
go
- 打印九九乘法表
declare @a int,@b int,@str varchar(max)
set @str=''
set @a=1
while @a<=9
begin
set @b=1
while @b<=@a
begin
set @str=@str+cast(@b as char(1))+'*'+cast(@a as char(1))
+'='+cast((@b*@a) as char(4))
set @b=@b+1
end
set @a=@a+1
set @str=@str+char(13)
end
print @str
go
- 按借书量打印出学生借书情况
declare @i int,@m int,@n int,@str varchar(max)
set @m=0
set @n=(select top 1 count(*) from BorrowBook group by SNO order by count(*) desc)
while @m<=@n
begin
set @i=0
set @str=''
select @str=@str+cast(SNO as char(6)),@str=@str+cast(SName as varchar(8))+space(2),@i=@i+1
from (
select T1.SNO,SName,BookID
from Student as T1 left join BorrowBook as T2
on T1.SNO=T2.SNO
) as T3
group by T3.SNO,T3.SName
having count(T3.BookID)=@m
print '借过'+cast(@m as char(1))+'本书的同学'+char(13)+'总计：'+cast(@i as varchar(2))
+'人'+char(13)+'名单：'+@str+char(13)
set @m=@m+1
end
go
五、存储过程
- 用存储过程实现图书库存情况
-- 在存储过程中执行输出
create procedure proc_Stock(@Bookid int)
as
begin
declare @in int,@borrowed int,@stock int
select @in=BookIncoming from Book where BookID=@Bookid
select @borrowed=count(*) from BorrowBook group by BookID having BookID=@Bookid
set @stock=@in-isnull(@borrowed,0)
print '图书ID号为：'+cast(@Bookid as char(6))
print '进货量为：'+cast(@in as char(3))
print '被借量为：'+cast(isnull(@borrowed,0) as char(3))
print '库存量为：'+cast(@stock as char(3))
if @stock<10
print '库存不足！'
if @stock between 10 and 20
print '库存尚可！'
if @stock >20
print '库存充足！'
print '******************************'+char(13)
end
go
-- 执行存储过程
exec proc_Stock 39006
go
-- 利用游标打印出所有图书库存情况
declare @bookid int
declare cur_Stock cursor for
select BookID from Book
open cur_Stock
print '图书库存情况如下：'+char(13)
fetch next from cur_Stock into @bookid
while @@fetch_status=0
begin
exec proc_Stock @bookid
fetch next from cur_Stock into @bookid
end
close cur_Stock
deallocate cur_Stock
go
-- 在存储过程外执行输出
create procedure proc_Stock(@Bookid int,@in int output,
@borrowed int output,@stock int output)
as
begin
select @in=BookIncoming from Book where BookID=@Bookid
select @borrowed=count(*) from BorrowBook group by BookID having BookID=@Bookid
set @stock=@in-isnull(@borrowed,0)
end
go
--执行存储过程
declare @in int,@borrowed int,@stock int
exec proc_Stock 39001,@in output,@borrowed output,@stock output
print '图书库存情况如下：'
print '进货量为：'+cast(@in as char(3))
print '被借量为：'+cast(isnull(@borrowed,0) as char(3))
print '库存量为：'+cast(@stock as char(3))
if @stock<10
print '库存不足！'
if @stock between 10 and 20
print '库存尚可！'
if @stock >20
print '库存充足！'
go
-- 利用游标打印出所有图书库存情况
declare @bookid int,@in int,@borrowed int,@stock int
declare cur_Stock cursor for
select BookID from Book
open cur_Stock
fetch next from cur_Stock into @bookid
print '图书库存情况如下：'+char(13)
while @@fetch_status=0
begin
exec proc_Stock @bookid,@in output,@borrowed output,@stock output
print '进货量为：'+cast(@in as char(3))
print '被借量为：'+cast(isnull(@borrowed,0) as char(3))
print '库存量为：'+cast(@stock as char(3))
if @stock<10
print '库存不足！'
if @stock between 10 and 20
print '库存尚可！'
if @stock >20
print '库存充足！'
print '******************************'+char(13)
fetch next from cur_Stock into @bookid
end
close cur_Stock
deallocate cur_Stock
go
-- 在存储过程中使用游标
create procedure proc_Stock
as
begin
declare @in int,@borrowed int,@stock int,@bookid int
declare cur_Stock cursor for
select BookID from Book
open cur_Stock
fetch next from cur_Stock into @bookid
print '图书库存情况如下'+char(13)
while @@fetch_status=0
begin
set @in=(select BookIncoming from Book where BookID=@bookid)
set @borrowed=(select count(*) from BorrowBook group by BookID having BookID=@bookid)
set @stock=@in-isnull(@borrowed,0)
print '图书ID号为：'+cast(@bookid as char(6))
print '进货量为：'+cast(@in as char(3))
print '被借量为：'+cast(isnull(@borrowed,0) as char(3))
print '库存量为：'+cast(@stock as char(3))
if @stock<10
print '库存不足！'
if @stock between 10 and 20
print '库存尚可！'
if @stock >20
print '库存充足！'
print '******************************'+char(13)
fetch next from cur_Stock into @bookid
end
close cur_Stock
deallocate cur_Stock
end
go
-- 执行存储过程
exec proc_Stock
go
- 用存储过程实现每本图书被借情况
-- 创建存储过程
create procedure proc_Borrowed(@Bookid int)
as
begin
declare @bookname varchar(50),@borrowedno int,@str varchar(max)
set @str=''
select @bookname=BookName from Book where BookID=@Bookid
select @borrowedno=count(*) from BorrowBook where BookID=@Bookid
select @str=@str+cast(t1.SNO as char(6))+Sname+space(2)
from Student as t1 left join BorrowBook as t2 on t1.SNO=t2.SNO
where t2.BookID=@Bookid
print '图书ID为：'+cast(@Bookid as char(6))
print '图书名称为：'+@bookname
if @borrowedno>0
begin
print '被借次数为：'+cast(@borrowedno as char(4))
print '借书学生为：'+@str
end
else
print '此本图书没有被借过！'
print '*****************************'+char(13)
end
-- 执行存储过程
exec proc_Borrowed 39007
go
-- 利用游标打印出所有图书被借情况
declare @bookid int
declare cur_Borrowed cursor for
select BookID from Book
print '每本图书被借情况如下：'+char(13)
open cur_Borrowed
fetch next from cur_Borrowed into @bookid
while @@fetch_status=0
begin
exec proc_Borrowed @bookid
fetch next from cur_Borrowed into @bookid
end
close cur_Borrowed
deallocate cur_Borrowed
go
- 注意
select count(*) from Book where BookID is null -- 输出 0
select count(*) from Book group by BookID having BookID is null -- 输出 null
六、MERGE关键字
merge可以根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步
-- 创建数据库
create database MergeTest
go
use MergeTest
go
drop table SourceTable
drop table TargetTable
go
create table SourceTable(
id int,
Explain varchar(50)
)
go
insert into SourceTable values(1,'描述1')
insert into SourceTable values(2,'描述2')
insert into SourceTable values(3,'描述3')
insert into SourceTable values(4,'描述4')
go
create table TargetTable(
id int,
Explain varchar(50)
)
go
insert into TargetTable values(1,'在源表里存在，将会被更新')
insert into TargetTable values(2,'在源表里存在，将会被更新')
insert into TargetTable values(5,'在源表里不存在，将会被删除')
insert into TargetTable values(6,'在源表里不存在，将会被删除')
go
select * from TargetTable
select * from SourceTable
go
- Merge语句示例
merge into TargetTable as T
using SourceTable as S
on T.id=S.id --对比目标表和源表中id字段的数据，条件为相等
when matched -- 目标表中满足on后面条件,则执行update
then update set T.Explain=S.Explain
when not matched -- 目标表中没有的id，在源表中有，则执行insert
then insert values(S.id,S.Explain)
when not matched by source -- 目标表中有的id，在源表中没有，则执行delete
then delete; -- merge语句必须以 ' ; ' 结束
go
- 使用OUTPUT子句
merge into TargetTable as T
using SourceTable as S
on T.id=S.id
when matched
then update set T.Explain=S.Explain
when not matched
then insert values(S.id,S.Explain)
when not matched by source
then delete
output $action as 动作,
inserted.id as 插入的id,
inserted.Explain as 插入的Explain, -- inserted表
deleted.id as 删除的id,
deleted.Explain as 删除的Explain; -- deleted表
go
- 使用TOP关键字
merge top (2) TargetTable as T -- 只更新目标表前两行数据
using SourceTable as S
on T.id=S.id
when matched
then update set T.Explain=S.Explain
when not matched
then insert values(S.id,S.Explain)
when not matched by source
then delete
output $action as 动作,
inserted.id as 插入的id,
inserted.Explain as 插入的Explain,
deleted.id as 删除的id,
deleted.Explain as 删除的Explain;
go
- 使用and关键字
merge into TargetTable as T
using SourceTable as S
on T.id=S.id
when not matched and S.id=3
then insert values(S.id,S.Explain)
output $action as 动作,
inserted.id as 插入的id,
inserted.Explain as 插入的Explain,
deleted.id as 删除的id,
deleted.Explain as 删除的Explain;
go
七、复制修改表
- select into from：将查询出来的数据整理到一张新表中保存，表结构与查询结构一致
select *(查询出来的结果) into newtable(新的表名) form where 条件
- insert into select ：为已经存在的表批量添加新数据
insert into 目标表 select *(或者取用自己想要的结构) frome 源表 where 条件
- update select
update table1
set column1 = t2.column1,
column1 = t2.column2,
...
from table1 inner/left/right join table2(select) on table1.column = table2.column
[where conditions]
