----检查图书的库存 : 提供一个图书的ID,求出库存的值,根据值来判断
Declare @BookId int = 39006 --提供图书的Id
--判断图书ID是否存在
Declare @BookStore int =(Select BookSumNo from LibraryDB.dbo.Book Where BookID=@BookId)
Declare @BookBorrowed int =
(
Select Count(BorrowDate)
from
(
Select T1.BookID,BorrowDate
from LibraryDB.dbo.Book AS T1 Left Outer Join LibraryDB.dbo.BorrowBook AS T2 on T1.BookID=T2.BookID
) AS T3
Group by T3.BookID
Having BookID=@BookId
)
Declare @BookName varchar(50)=(Select BookName from LibraryDB.dbo.Book Where BookID=@BookId)
Print '图书信息如下:'
Print '图书ID:'+ CAST(@BookId As varchar(10)) +' 图书名称:'+@BookName +char(13)+
'图书量:'+ CAST(@BookStore As varchar(3)) +' 库存量:' + CAST((@BookStore-@BookBorrowed) As varchar(3))
if(@BookStore-@BookBorrowed>20) Print '库存充足!'
else if((@BookStore-@BookBorrowed) between 10 and 20) Print '库存尚可!'
else print '库存不足!'
go
--Create Table Check ------CASE和Check整合: check--约束
---举例: 男性 必须要大于4000 ,如果女性 必须要大于3000
use TestDB
go
Create Table Employee
(
EmpId int,
EmpName Char(10) not Null,
Gender char(2),
Salary int,
Constraint Pk_EmpId Primary key ClusterED(EmpId),
Constraint Check_Salary Check
(
CASE When Gender='男' then CASE when Salary>4000 Then 1 Else 0 End
when Gender='女' then CASE when Salary>3000 Then 1 Else 0 End
Else 0
End
)
)
Insert into Employee01 Values(39001,'张三','女',3500)
Insert into Employee01 Values(39002,'李四','男',5500)
---CASE 和 Update 整合
--案例: 公司准备给员工涨工资,如果工资4000以下,涨20%,如果工资在4000-6000,涨10% ,如果工资大于6000 ,涨5%
Select * from Employee
Update TestDB.dbo.Employee01
Set Salary=
(
CASE when Salary<4000 then Salary*1.2
when Salary between 4000 and 6000 then Salary*1.1
else Salary*1.05
End
)
go
--============while循环 ==============
--【1】定义游标
Declare Cur_Student Cursor for Select SName from Student
--【2】打开游标
open cur_Student
--【3】使用游标
Declare @TotalNames varchar(max)='',@CurrentName varchar(20)
While(1=1)
Begin
Fetch Next from cur_Student into @CurrentName
if(@@FETCH_STATUS =-1) Break
Set @TotalNames=@TotalNames+' '+@CurrentName
End
Print '所有的学生姓名:' + @TotalNames
--【4】关闭游标
Close Cur_Student
--【5】删除游标
DeAllocate cur_Student