--求1--100的和
declare @sum int=0,@number int=1;
while(@number<=100)
begin
set @sum=@sum+@number;
set @number=@number+1;
end
select @sum
--求1--100之间所有基数和
declare @sum int=0,@num int=0;
while(@num<=100)
begin
if(@num%2<>0)
begin
set @sum=@sum+@num;
end
set @num=@num+1;
end
print @sum
----事物
use nonononodelete
select * from bank --该表有一个约束 每个账号里 不能少于10元钱
update bank set balance=balance-1000 where cId='0001'
update bank set balance=balance+1000 where cId='0002'
--执行这两行代码会报这个错误
--消息 547，级别 16，状态 0，第 1 行
--UPDATE 语句与 CHECK 约束"CH_balance"冲突。该冲突发生于数据库"nonononodelete"，表"dbo.bank", column 'balance'。
--语句已终止。
--解决办法 使用事务处理
begin transaction --开始一个事务
declare @sumError int=0;
update bank set balance=balance-1000 where cId='0001'
set @sumError+=@@ERROR
update bank set balance=balance+1000 where cId='0002'
set @sumError+=@@ERROR;
if(@sumError<>0)
begin
--失败了
rollback transaction;
end
else
begin
--成功了
commit transaction
end
select * from bank
--使用事务
---存储过程
exec sp_databases --数据库中所有的数据库
exec sp_tables --数据库中所有的表
exec sp_columns tblStudent --tblStudent 这个表中所有的列
exec sp_help
exec sp_helptext sp_databases
--创建一个存储过程求两个数的和
create proc usp_TwoNumbersAdd
@num1 int,
@num2 int
as
begin
select @num1+@num2
end
--第一种传参数的方法
exec usp_TwoNumbersAdd 1,5
declare @num int=10,@numOne int=20
--第二种传参数的方法
exec usp_TwoNumbersAdd @num1=@num,@num2=@numOne
drop proc usp_TwoNumbersAdd --删除存储过程
--创建一个存储过程计算连个数的差
create proc usp_TwoNumberSub
@numberOne int=20,
@numberTwo int=10
as
begin
select @numberOne-@numberTwo
end
drop proc usp_TwoNumberSub
exec usp_TwoNumberSub 10,20
drop proc usp_TwoNumberSub
--创建一个带输出参数的存储过程
create proc usp_TwoNumberSub
@numberOne int,
@numberTwo int,
@Result int output
as
begin
set @Result=@numberOne-@numberTwo;
end
declare @result int
exec usp_TwoNumberSub 20,10,@Result output --执行存储过程
print @result
--模糊查询 --存储过程 用户传入 张，和年龄 >20返回来有多少条数据 并把这些数据显示出来
create proc usp_myselectstuByNameandAge
@name nvarchar(10),--名字
@age int,--年龄
@count int output--条数
as
begin
--条数
set @count=(select COUNT(*) from tblstudent where tsname like @name+'%' and tsage>@age )
select * from tblstudent where tsname like @name+'%' and tsage>@age
end
declare @ct int
exec usp_myselectstuByNameandAge '张',20, @ct output
select @ct
select * from tblscore
create proc usp_tblScore
@scoreLines int,
@addScore int=2,
@count int
as
begin
set @count=0
--总人数
declare @countPerson int=(select COUNT(*) from TblScore)
--不及格的人数
declare @bjgPerson int=(select COUNT(*) from tblScore where tmath<@scoreLines)
while(@bjgPerson>@count/2)
begin
update tblscore set tmath= tmath +@addScore;
set @bjgPerson=(select COUNT(*) from tblScore where tmath<@scoreLines)
set @count=@count+1;
end
end
declare @cou int=0
exec usp_tblScore 120,2, @cou
select @cou
select * from tblscore
select COUNT(*) from tblscore
--创建一个存储过程如果不及格的人数小于一半每个同学提分
select * from TblScore
create proc usp_TblScoreLine
@scoreLine int,
@addScore int,
@counts int output
as
begin
--没及格的人数
declare @countMeiPersons int=(select COUNT(*) from TblScore where tEnglish<@scoreLine)
--总的人数
declare @countPersons int=(select COUNT(*) from TblScore)
while(@countMeiPersons>@countPersons/2)
begin
update TblScore set tEnglish=tEnglish+@addScore;
set @countMeiPersons=(select COUNT(*) from TblScore where tEnglish<@scoreLine)
set @counts=@counts+1;
end
end
select * from tblscore --tblscore 表
declare @n int
exec usp_TblScoreLine 155,1,@n output
select @n;
select count(*)from tblscore where tenglish=155
us