declare @uname nvarchar(10)='sk' set @uname='yzk' print @uname select @uname --全局变量 select @@VERSION select * from Employee insert into Employee(eName,eCode,edid) values('zjy',14,4) select @@IDENTITY insert into Employee values('yhb') print @@error print @@rowcount ---================if语句 --判断一个值是奇数还是偶数 declare @i int=10 if(@i%2=0) begin print '偶数' end else begin print '奇数' end --===========循环语句 --求1-100的和 declare @j int=1 declare @sum int=0 while(@j<=100) begin set @sum+=@j set @j+=1 end print @sum --=============开窗函数 --排序 select *,RANK() over(order by tenglish desc) from TblScore --order by tEnglish desc --划区 select * from TblOrders --传统的统计汇总 --这个语句表示对整个数据进行划区 select oname,SUM(ocount) from TblOrders group by oname select *,SUM(ocount) over(partition by oname) from TblOrders --36 select *,SUM(ocount) over() from TblOrders --196 --排号 select * from TblOrders delete from TblOrders where oname='糖果' select *,row_number() over(order by oprice desc) from TblOrders -- select distinct oname,oprice,max(ocount) over(partition by oname) from TblOrders --分页,要求3条数据显示一页 select * from( select *,row_number() over(order by oprice desc) as num from TblOrders ) t1 where num between 4 and 6 --找各班中城市人最多的城市名称 use ItCastCn select * from TblStudent select distinct tSClassId,MAX(counter) over(partition by tsclassid) from( select distinct tSClassId,tSAddress,COUNT(*) over(partition by tsaddress) as counter from TblStudent ) t1 -- select tSAddress ,tSClassId from( select rank() OVER(partition by tsclassid ORDER BY num DESC )AS number,* from ( select tSAddress,tSClassId ,count(tSAddress) as num from TblStudent group by tSClassId,tSAddress )a )b where number=1 --============视图 use hem09 select *,row_number() over(order by oprice desc) as num from TblOrders create view ordersWithNum as select *,row_number() over(order by oprice desc) as num from TblOrders select * from ordersWithNum --查看视图中的代码 exec sp_helptext ordersWithNum --=============事务 --比如订火车票 --下一个订单,这个订单中,包含多个购买信息 --orders,orders_detail --合作事务来处理这种模型 --关键字:tran[saction] --开启事务:begin tran --提交事务:commit tran --回滚事务:rollback tran --示例 select * from TblOrders declare @errornum int=0 begin tran insert into tblorders values('五粮液',2,300) set @errornum+=@@error insert into TblOrders values('包子',3,1) set @errornum+=@@error if(@errornum>0) begin rollback tran end else begin commit tran end select * from TblOrders begin tran insert into tblorders values('ee',100,300) --commit tran rollback tran set implicit_transactions on --===============存储过程 select ' abc ' select rtrim(LTRIM(' abc ')) --'abc' --存储过程完成一段sql代码的封装 create proc trim --参数列表,多个间用逗号分隔 @str varchar(10) as --自定义代码段 declare @str1 varchar(10) set @str1=LTRIM(RTRIM(@str)) print @str1 --使用存储过程 exec trim ' abc ' --'abc' --===带输出参数的存储过程 --求两个数的和 create proc sum1 @num1 int, @num2 int, @result int output--表示这个参数可以将结果带出存储过程 as set @result=@num1+@num2 declare @r1 int exec sum1 1,2,@r1 output--必须要写output关键字,否则调用出错 print @r1 --===参数带默认值的存储过程,注意:默认值必须是最后一个参数 alter proc multi @num1 int, @num2 int=10 output--output的功能类似于C#中的ref修饰参数 as set @num2=@num2*@num1 print @num2 --测试带出值的效果 declare @num int=2 exec multi 3,@num output select @num --测试默认值 exec multi 3 -------==============索引 select * from TblOrders where oprice>100 --创建索引 create index index_name on tblorders(oname)
例子:提交学生成绩,以使平均分提高
create proc AddScore @growth int,--分数的增量 @avg float=60--指定的平均分 as declare @avg1 float select @avg1=AVG(tenglish) from TblScore while(@avg1<@avg)--当实际平均分低于指定的平均分时,开始进行增加 begin update TblScore set tEnglish+=@growth update TblScore set tEnglish=100 where tEnglish>100 select @avg1=AVG(tenglish) from TblScore end print 'OK'+cast(@avg as varchar(10)) select AVG(tenglish) from TblScore exec addscore 1,84
说点什么
欢迎讨论