use myitcast--求1--100的和
declare @sum int=0,@number int=1;
while(@numberbegin
set @sum=@sum+@number;
set @number=@number+1;
end
select @sum
--求1--100之间所有基数和
declare @sum int=0,@num int=0;
while(@numbegin
if(@num%20)
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(@sumerror0)
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 tmathwhile(@bjgperson>@count/2)
begin
update tblscore set tmath= tmath +@addscore;
set @bjgperson=(select count(*) from tblscore where tmathset @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--总的人数
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 tenglishset @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
use nonononodelete
select * from tblstudent
--pagecount 总的页数
--count
--页数 5 每页显示几条
--分页的sql语句
declare @count int=(select count(*) from tblstudent)
declare @pagecount int=(ceiling((select count(*) from tblstudent)*1.0/@count))
select * from (select 编号=row_number() over(order by tsid),* from tblstudent ) as t where t.编号
between and
--分页的存储过程
--分页的存储过程
create proc usp_tblstudent
@page int, ---页数
@pagecount int,--条数
@sumpage int output--总页数
as
begin
set @sumpage=ceiling((select count(*) from tblstudent)/@pagecount*1.0) --总页数
select * from
(select 编号=row_number() over(order by tsid),* from tblstudent)as tstu
where tstu.编号 between (@page-1)*@pagecount+1 and @page*@pagecount
end
declare @c int
exec usp_tblstudent 2,3,@c output
select @c
select top 1 * into newstu from tblstudent
select * from newstu
delete from tblstudent where tsid=1
select * from newstu
insert into tblstudent(tsname,tsgender,tsaddress,tsphone,tsage,tsbirthday,tscardid,tclassid) select tsname,tsgender,tsaddress,tsphone,tsage,tsbirthday,tscardid,tclassid from newstu ---一次性插入多条数据
select * from newstu
select * from tblstudent
--创建一个删除的触发器
create trigger tr_tblstudent on tblstudent
after delete
as
begin
insert into newstu select * from deleted
end
--回出现的错误 仅当使用了列列表并且 identity_insert 为 on 时，才能为表'newstu'中的标识列指定显式值。
--解决办法 表设计 表示 改成否ok
select * from newstu
delete from newstu where tsid=1
delete from tblstudent where tsid=2
select * from newstu
select * from newstu
select * from tblstudent
select * from newstu
insert into tblstudent(tsname,tsgender,tsaddress,tsphone,tsage,tsbirthday,tscardid,tclassid) select tsname,tsgender,tsaddress,tsphone,tsage,tsbirthday,tscardid,tclassid from newstu
create trigger tr_tblstudent
select * from tblstudent