|
??
use MyItcast
--求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
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,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,TClassId from newStu
create trigger tr_TblStudent select * from tblStudent
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
