SQL8：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)