SQLServer（三）

1.作业点评
2.SQL编程的综合案例：图书借阅系统
3.用户自定义函数
4.事务处理机制

-- 李连杰 包括前后两位
declare @sid int
select @sid = [sid] from student where sname='李连杰' --查询sid
select * from student where [sid] in (@sid-1,@sid,@sid+1)  --查询sid对应的学生信息

--执行insert，查看@@error

alter table tb add constraint ck check(len(name)<=6) --约束，要求name长度不能超过6位

insert into tb values('xxx');
print( @@error); --查询错误号，0表示无错误

--6号学生参加 2019-11-4 举行的 语文考试, 姓名、分数
select * from score
declare @cid int
select @cid = cid from course where cname='语文' --课程编号

--多表连接
select s.sname,c.cname,sc.fs, sc.examDate
from score sc , student s, course c
where sc.[sid]=s.[sid] and sc.cid=c.cid
and sc.[sid]=6 and c.cname='语文' and sc.examDate='2019-7-4'

--数学最近一次考试的平均分， 70，“优秀”，前三名
declare @cid int
declare @examDate datetime --考试日期
declare @avg int
select @cid=cid from course where cname='数学' --数学课程编号
select @examDate= max(examDate) from score where cid=@cid  --数学最近一次考试日期
select @avg=avg(fs) from score  --平均分
where cid=@cid and examdate=@examDate
if(@avg>=70) --平均分>=70
begin 
  print('成绩优秀')
	select top 3 * from score 
	where cid=@cid and examDate=@examDate
	order by fs desc --降序
end
else
begin
print('成绩较差')
select top 3 * from score 
	where cid=@cid and examDate=@examDate
	order by fs asc
end


--九九乘法表
declare @row int
declare @col int
declare @s varchar(1000)
set @s=''
set @row=1
set @col=1

while(@row<=9)
begin
	while(@col<=@row)
	begin
		set @s = @s + cast(@row as varchar(10))+' x '+cast(@col as varchar(10))+' =  '+cast(@row*@col as varchar(20))
		set @col = @col + 1
	end
	set @row=@row+1
	set @col = 1
	set @s = @s + char(13)
end

print(@s)



--借书管理系统
--逾期  损坏  丢失

-- 读者表、图书表、借阅表、罚款表

-- 统计并输出当前各种图书的总库存
-- 总库存<1000，显示：库存不足，需要采购
--总库存>=1000 ，显示：库存充足，需要加强管理
declare @sum int
select @sum = sum(bcount) from book --获取总库存量
if(@sum<1000)
	print('库存不足，需要采购')
else
	print('库存充足，需要加强管理')

--查询罚款记录，将  1:逾期归还  2：损坏  3：丢失

select r.rname, b.bname, pdate, 
	case ptype  --针对ptype字段分类
		when 1 then '逾期归还'
		when 2 then '损坏'
		when 3 then '丢失'
	end '罚款原因'
,p.amount 
from punish p
inner join reader r on p.rid=r.rid
inner join book b on p.bid=b.bid


--用户自定义函数 = 标量函数 + 内联表值函数 + 多语句表值函数
--标量函数的定义
drop function Getmax --删除函数
create function GetMax( @a int, @b int )returns int --判断两个数的最大值
as
begin
	declare @max int
	if( @a > @b) 
		set @max = @a
	else
		set @max = @b
	return @max
end

declare @x int  --调用函数先声明变量
declare @y int  
declare @max int 
set @x=10
set @y = 20
--调用标量函数时，必须带上默认数据库的拥有者dbo
set @max = dbo.GetMax(@x, @y) --调用函数
print(@max)


--内联表值函数的定义
create function GetStudentBySex( @sex varchar(50)) returns table --根据性别查询学生信息
as
--不能加入begin - end
	return ( select * from student where sex=@sex)

--调用表值函数
select * from GetStudentBySex( '女')

--多语句表值函数的定义：查询指定性别的学生成绩
create function  GetScoreBySex(@sex varchar(50)) returns @tab table
(
	 --定义表结构
	 sname varchar(50), 
	 sex varchar(50),
	 dptName varchar(50),--院系
	 cName varchar(50),--课程
	 fs int --分数
)
as
begin
	insert into @tab --插入多表连接的数据到tab变量中
	select s.sname,s.sex,d.dptName,c.cName,sc.fs
	from student s,department d, course c, score sc
	where s.[sid]=sc.[sid] and s.dpt_id=d.dpt_id
		    and c.cid=sc.cid 
		    and s.sex=@sex
	return 
end

--调用多语句表值函数
select * from GetScoreBySex('女')



/**************************************
            事务处理机制
***************************************/
--  ACID    A-B
--1. 原子性 
--2. 一致性  a少 b多
--3. 隔离性  a-b   c-d  e-f
--4. 持久性

begin transaction --开始事务

--事务的两种结局：1 提交（成功） 2.回滚（失败），退回原始状态
create table bank  --银行卡表
(
	account nvarchar(50) primary key, --账号
	amount money --余额
)
go
insert bank values('zhangsan',10000)
insert bank values('lisi',1)
delete from bank
select * from bank

--添加约束，确保每个储户的余额不能为0
alter table bank add constraint ck_money check(amount>=1)

--zhangsan转账给lisi
--1. lisi收到转账金额
--2. zhangsan扣除转账金额
update bank set amount=amount+10000 where account='lisi'
update bank set amount=amount-10000 where account='zhangsan'

--开始事务
declare @err int
set @err = 0
begin transaction
	update bank set amount=amount+10000 where account='lisi'
	set @err = @err + @@error
	update bank set amount=amount-10000 where account='zhangsan'
	set @err = @err + @@error

	if @err = 0 --无错误，提交事务
		commit transaction
	else  --事务失败，需要回滚
		rollback transaction

/*
	使用事务机制的几个原则：
	1. 事务应该尽量的简单，不应太复杂，应为会占用过多的系统资源
	2. 事务所涉及的数据量不能太大
	3. 查询时尽量不要使用事务
	4. 尽量不要出现等待用户输入的情况
*/
			

1.视图的应用
2.无参存储过程的应用
3.带输入型参数的存储过程的应用
4.带输出型参数的存储过程的应用
5. 利用存储过程分页

-- 视图、存储过程、触发器、游标

--表：物理表  视图:虚拟表   eg.联合国
--创建视图：
create view v_scs
as
	select s.sname,c.cname,sc.fs,sc.examDate
	from score sc
	inner join student s on sc.[sid]=s.[sid]
	inner join course c on sc.cid=c.cid 
	
--查看视图
select *from v_scs 
--删除视图 
drop view v_scs

--视图的使用注意事项：
-- 1.不能包括排序 order by 子句
-- 2. 不能引用表变量or临时表


--  存储过程：与C#方法(函数)类似： void xx( ){ }
--创建无参存储过程（实现某种功能）
create procedure sp_view
as
begin
	select * from [v_scs]  --创建存储过程调用已创建的视图
end 

--调用存储过程
execute sp_view


--有参存储过程：查询指定课程名称的成绩记录
create procedure sp_score( @cname nvarchar(50) ) --定义参数名
as
begin
	select s.sname,c.cname,sc.fs,sc.examDate
	from score sc
	inner join student s on sc.[sid]=s.[sid]
	inner join course c on sc.cid=c.cid 
	where c.cname=@cname
end

--调用带参数的过程
declare @cname nvarchar(50)--定义实参
set @cname='语文'
execute sp_score @cname



--添加学生的过程 
create proc sp_insertStudent --创建存储过程
(
	@sname nvarchar(50),
	@sex nvarchar(50),
	@idcard  nvarchar(50),
	@nation nvarchar(50),
	@birthday datetime,
	@dpt_id int,
	@class  nvarchar(50),
	@state int
)
as
begin
	--判断身份证号是否重复
	declare @count int
	select @count=count(*) from student where idcard=@idcard

	if (@count <= 0)
		insert student values(@sname,@sex,@idcard,@nation,@birthday,@dpt_id,@class,@state)
	else
		print('对不起，该身份证号已经存在，不能重复添加')
end

--调用存储过程
exec sp_insertStudent '赵丽颖','女','428176199102162239','汉族','1991-08-16 00:00:00.000',2,'98级机械2班',0
select * from student

--删除院系记录，规则：该院系不存在任何学生
--根据院系名称删除之
create proc sp_deleteDepartment(@dName nvarchar(50))
as
begin
	--获取院系编号
	declare @dptId int
	declare @count int --保存符合条件的学生人数
	select @dptId=dpt_id from department where dptName=@dName

	--判断该院系在学生表中是否拥有学生
	select @count=count(*) from student where dpt_id=@dptId

	if(@count=0)
		delete from department where dpt_id=@dptId
	else
		print('该院系中还有未毕业的学生')
end

select distinct dpt_id from student --查询不重复院系编号
select * from department

--调用
exec sp_deleteDepartment '美术学院'


-- 创建带输出参数的存储过程
-- 根据学号获取姓名和民族
create proc sp_getNameAndNation( 
   	 @sid int, --输入型参数
	 @sname nvarchar(50) output,  --output输出型参数
	 @nation nvarchar(50) output  --output输出型参数
)
as
begin
	select @sname=sname, @nation=nation from student 
	where [sid]=@sid
end

--调用带输出参数的过程
declare @sid int
declare @sname nvarchar(50)
declare @nation nvarchar(50)
set @sid=2
exec sp_getNameAndNation @sid, @sname output,@nation output
--打印输出参数
print('学号为'+cast(@sid as nvarchar(5))+'的姓名：'+@sname+', 民族：'+@nation)


--“查询”指定课程的最近一次考试成绩记录
create procedure sp_lastScore(@cName nvarchar(50))
as
begin
	--课程名称换课程编号
	declare @cid int
	select @cid=cid from course where cName=@cName

	--获取该课程最近考试日期
	declare @maxDate datetime
	select @maxDate=max(examDate) from score where cid=@cid

	select c.cname,sc.fs,sc.examDate
	from score sc , course c 
	where sc.cid=c.cid and sc.cid=@cid and examDate=@maxDate --查询两个实参值
end
--执行存储过程
exec sp_lastScore '语文'
--验证
select* from score where cid=1 order by examDate desc


--存储过程分页（不可能把所有数据放一个页面）
declare @i int
set @i=1
while(@i<=1000)
begin
  insert into tb values('姓名'+cast(@i as nvarchar(50))) --快速插入操作
	set @i=@i+1
end

select * from tb（消耗性能）
truncate table tb --清除数据


--首页（假设每页显示10条记录）
select top 10 * from tb
--非首页：第5页
select top 10 * from tb
where id not in( select top ((5-1)*10) id  from tb ) --不在前40条记录中，查询41-50条记录

execute( 'select top 10 * from tb') --动态sql




--存储过程实现记录的分页查询
create proc sp_pager
(
	@size int , --每页显示的记录数
	@index int --当前页码
)
as
begin
	declare @sql nvarchar(1000) --保存sql语句
	if(@index = 1) --首页
		set @sql = 'select top ' + cast(@size as nvarchar(20)) + ' * from tb'
	else --非首页
		set @sql = 'select top ' + cast(@size as nvarchar(20)) + ' * from tb where id not in( select top '+cast((@index-1)*@size as nvarchar(50))+' id  from tb )'
	execute(@sql)
end

--执行分页存储过程
exec sp_pager 10,3 --查看第3页，每页显示10条记录

--第二种写法实现过程分页
alter proc sp_pager2
(
	@size int,
	@index int
)
as
begin
	select * from ( select row_number() over(order by id ) as [rowId], * from tb ) as b
	where [rowId] between @size*(@index-1)+1  and @size*@index
end

exec sp_pager2 10, 3

--delete from tb where id in(1,3,10,14,25)