MsSqlServer



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,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