常用的sql语句的总结(T-sql)

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

Written by

说点什么

欢迎讨论

avatar

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据

  Subscribe  
提醒