SQL宝典

SQLServer数据库的高级操作
(1)批处理
(2)变量
(3)逻辑控制
(4)函数
(5)高级查询

*/

(1)批处理
将多条SQL语句作为一个整体去编译，生成一个执行计划，然后，执行！
理解批处理的关键在于"编译"，对于由多条语句组成的一个批处理，
如果在编译时，其中，有一条出现语法错误，将会导致编译失败！

createtablet
(
aint,
bint
)

--注释
--如果多行注释中包含了批处理的标识符go
--在编译的过程中代码将会被go分割成多个部分来分批编译
--多行注释的标记将会被分隔而导致编译出错
--以下几条语句是三个非常经典的批处理
--你猜一下会添加几条记录！
/*
insertintotvalues(1,1)
go
*/
insertintotvalues(2,2)
go
/*
insertintotvalues(3,3)
*/
go


--查询看添加了几条记录
select*fromt

truncatetablet

(2)变量

--全局变量
SQLServer中全局变量由系统定义、系统维护，用户一般仅可对其进行读取！

--查看SQLServer版本
print@@version

--服务器名称
print@@servername

--系统错误编号
insertintotvalues('a','a')
print@@error

insertintotvalues('a','a')
if@@error=245
print'Error'

--SQLServer版本的语言信息
print@@LANGUAGE

--一周的第一天从星期几算起
print@@datefirst

--CPU执行命令所耗费时间的累加
print@@cpu_busy

--获取最近添加的标识列的值
createtablett
(
aintidentity(3,10),
bint
)
insertintott(b)values(1)
print@@identity
select*fromtt

--局部变量
局部变量由用户定义，仅可在同一个批处理中调用和访问

declare@intAgetinyint
set@intAge=12
print@intAge

declare@strNamevarchar(12)
select@strName='state'
print@strName
selectau_lname,@strNamefromauthors

(3)逻辑控制

--IF条件判断
declare@iint
set@i=12
if(@i>10)
begin--{
print'Dadadada!'
print'Dadadada!'
end--}
else
begin
print'XiaoXiao!'
print'XiaoXiao!'
end

--While循环控制
declare@iint;
set@i=12;
print@i
return;
while(@i<18)
begin
print@i;
set@i=@i+1;
if@i<17
continue;
if@i>15
break;
end;

--CASE分支判断
selectau_lname,state,'犹他州'fromauthorswherestate='UT'
selectau_lname,state,'密西西比州'fromauthorswherestate='MI'
selectau_lname,state,'肯塔基州'fromauthorswherestate='KS'

selectau_lname,state,
casestate
when'UT'then'犹他州'
when'MI'then'密西西比州'
when'KS'then'肯塔基州'
when'CA'then'加利福利亚'
elsestate
end
fromauthors

(4.1)系统函数

--获取指定字符串中左起第一个字符的ASC码
printascii('ABCDEF')
--根据给定的ASC码获取相应的字符
printchar(65)
--获取给定字符串的长度
printlen('abcdef')
--大小写转换
printlower('ABCDEF')
printupper('abcdef')
--去空格
printltrim('abcddfddf')
printrtrim('abcddfddf')
--求绝对值
printabs(-12)
--幂
--3的2次方
printpower(3,2)
printpower(3,3)
--随机数
--0-1000之间的随机数
printrand()*1000
--获取圆周率
printpi()


--获取系统时间
printgetdate()

--获取3天前的时间
printdateadd(day,-3,getdate())
--获取3天后的时间
printdateadd(day,3,getdate())
--获取3年前的时间
printdateadd(year,-3,getdate())
--获取3年后的时间
printdateadd(year,3,getdate())

--获取3月后的时间
printdateadd(month,3,getdate())
--获取9小时后的时间
printdateadd(hour,9,getdate())
--获取9分钟后的时间
printdateadd(minute,9,getdate())

--获取指定时间之间相隔多少年
printdatediff(year,'2005-01-01','2008-01-01')
--获取指定时间之间相隔多少月
printdatediff(month,'2005-01-01','2008-01-01')
--获取指定时间之间相隔多少天
printdatediff(day,'2005-01-01','2008-01-01')

--字符串合并
print'abc'+'def'

print'abcder'

print'abc'+'456'
print'abc'+456

--类型转换
print'abc'+convert(varchar(10),456)

selecttitle_id,type,pricefromtitles
--字符串连接必须保证类型一致(以下语句执行将会出错)
--类型转换
selecttitle_id+type+pricefromtitles
--正确
selecttitle_id+type+convert(varchar(10),price)fromtitles

print'123'+convert(varchar(3),123)
print'123'+'123'

printconvert(varchar(12),'2005-09-01',110)

--获取指定时间的特定部分
printyear(getdate())
printmonth(getdate())
printday(getdate())

--获取指定时间的特定部分
printdatepart(year,getdate())
printdatepart(month,getdate())
printdatepart(day,getdate())
printdatepart(hh,getdate())
printdatepart(mi,getdate())
printdatepart(ss,getdate())
printdatepart(ms,getdate())

--获取指定时间的间隔部分
--返回跨两个指定日期的日期和时间边界数
printdatediff(year,'2001-01-01','2008-08-08')
printdatediff(month,'2001-01-01','2008-08-08')
printdatediff(day,'2001-01-01','2008-08-08')
printdatediff(hour,'2001-01-01','2008-08-08')
printdatediff(mi,'2001-01-01','2008-08-08')
printdatediff(ss,'2001-01-01','2008-08-08')

--在向指定日期加上一段时间的基础上，返回新的datetime值
printdateadd(year,5,getdate())
printdateadd(month,5,getdate())
printdateadd(day,5,getdate())
printdateadd(hour,5,getdate())
printdateadd(mi,5,getdate())
printdateadd(ss,5,getdate())

--其他
printhost_id()
printhost_name()
printdb_id('pubs')
printdb_name(5)


--利用系统函数作为默认值约束
droptablettt

createtablettt
(
stu_namevarchar(12),
stu_birthdaydatetimedefault(getdate())
)

altertablettt
addconstraintdf_ttt_stu_birthdaydefault(getdate())forstu_birthday

insertintotttvalues('ANiu','2005-04-01')
insertintotttvalues('ANiu',getdate())

insertintotttvalues('AZhu',default)

sp_helpttt

select*fromttt



（4.2）自定义函数

selecttitle_id
fromtitles
wheretype='business'

selectstuff(title_id,1,3,'ABB'),type
fromtitles
wheretype='business'

selectcount(title_id)fromtitleswheretype='business'
selecttitle_idfromtitleswheretype='business'


select*,count(dbo.titleauthor.title_id)
FROMdbo.authorsINNERJOIN
dbo.titleauthorONdbo.authors.au_id=dbo.titleauthor.au_id

selectau_id,count(title_id)
fromtitleauthor
groupbyau_id

SELECTdbo.authors.au_id,COUNT(dbo.titleauthor.title_id)AS'作品数量'
FROMdbo.authorsleftouterJOIN
dbo.titleauthorONdbo.authors.au_id=dbo.titleauthor.au_id
GROUPBYdbo.authors.au_id
orderby'作品数量'

--自定义函数的引子（通过这个子查询来引入函数的作用）

--子查询
--统计每个作者的作品数
--将父查询中的作者编号传入子查询
--作为查询条件利用聚合函数count统计其作品数量
selectau_lname,
(selectcount(title_id)
fromtitleauthorasta
whereta.au_id=a.au_id
asTitleCount
fromauthorsasa
orderbyTitleCount




--是否可以定义一个函数
--将作者编号作为参数统计其作品数量并将其返回
selectau_id,au_lname,dbo.GetTitleCountByAuID(au_id)asTitleCount
fromauthors
orderbyTitleCount

--根据给定的作者编号获取其相应的作品数量
createfunctionGetTitleCountByAuID(@au_idvarchar(12))
returnsint
begin
return(selectcount(title_id)
fromtitleauthor
whereau_id=@au_id)
end


--利用函数来显示每个作者的作品数量
createprocpro_CalTitleCount
as
selectau_id,au_lname,dbo.GetTitleCountByAuID(au_id)asTitleCount
fromauthors
orderbyTitleCount
go

--执行存储过程
executepro_CalTitleCount

--vb中函数定义格式
functionGetTitleCountByAuID(au_idasstring)asinteger

.......

GetTitleCountByAuID=?
endfunction

--SALES作品销售信息
select*fromsales

--根据书籍编号查询其销售记录（其中，qty表示销量）
select*fromsaleswheretitle_id='BU1032'

--根据书籍编号统计其总销售量（其中，qty表示销量）
selectsum(qty)fromsaleswheretitle_id='BU1032'

--利用分组语句（groupby），根据书籍编号统计每本书总销售量（其中，qty表示销量）
selecttitle_id,sum(qty)fromsalesgroupbytitle_id

--是否可以考虑定义一个函数根据书籍编号来计算其总销售量
--然后，将其应用到任何一条包含了书籍编号的查询语句中
selecttitle_id,title,dbo.GetTotalSaleByTitleID(title_id)asTotalSales
fromtitles
orderbyTotalSales

--定义一个函数根据书籍编号来计算其总销售量
createfunctionGetTotalSaleByTitleID(@tidvarchar(24))
returnsint
begin
return(selectsum(qty)fromsaleswheretitle_id=@tid)
end

--统计书籍销量的前10位
--其中，可以利用函数计算结果的别名作为排序子句的参照列
selecttop10title_id,title,dbo.GetTotalSaleByTitleID(title_id)asTotalSales
fromtitles
orderbyTotalSalesdesc


--根据书籍编号计算其销量排名
createfunctionGetTheRankOfTitle(@idvarchar(20))
returnsint
begin
return(selectcount(TotalSales)
fromtitles
whereToalSales>(
selectTotalSales
fromtitles
wheretitle_id=@id))
end

--根据书籍编号计算其销量排名
selectdbo.GetTheRankOfTitle('pc1035')fromtitles

selectcount(title_id)+1
fromtitles
wheredbo.GetTotalSaleByTitleID(title_id)>dbo.GetTotalSaleByTitleID('pc1035')

--删除函数
dropfunctionGetRankByTitleId

--根据书籍编号计算其销量排名
createfunctionGetRankByTitleId(@tidvarchar(24))
returnsint
begin
return(selectcount(title_id)+1
fromtitles
wheredbo.GetTotalSaleByTitleID(title_id)>dbo.GetTotalSaleByTitleID(@tid))
end

--在查询语句中利用函数统计每本书的总销量和总排名
selecttitle_id,title,
dbo.GetTotalSaleByTitleID(title_id)asTotalSales,
dbo.GetRankByTitleId(title_id)asTotalRank
fromtitles
orderbyTotalSalesdesc

--查看表结构
sp_helptitles
--查看存储过程的定义内容
sp_helptextGetRankByTitleId
sp_helptextsp_helptext
sp_helptextxp_cmdshell


--[ORDERDETAILS]订单详细信息
select*from[orderdetails]
select*from[orderdetails]whereproductid=23
--根据产品编号在订单详细信息表中统计总销售量
selectsum(quantity)from[orderdetails]whereproductid=23

--构造一个函数根据产品编号在订单详细信息表中统计总销售量
createfunctionGetTotalSaleByPID(@Pidvarchar(12))
returnsint
begin
return(selectsum(quantity)from[orderdetails]whereproductid=@Pid)
end


select*fromproducts
--在产品表中查询，统计每一样产品的总销量
selectproductid,productname,dbo.GetTotalSaleByPID(productid)fromproducts


--
CREATEFUNCTIONLargeOrderShippers(@FreightParmmoney
RETURNS@OrderShipperTabTABLE
(
ShipperIDint,
ShipperNamenvarchar(80),
OrderIDint,
ShippedDatedatetime,
Freightmoney

AS
BEGIN
INSERT@OrderShipperTab
SELECTS.ShipperID,S.CompanyName,
O.OrderID,O.ShippedDate,O.Freight
FROMShippersASSINNERJOINOrdersASO
ONS.ShipperID=O.ShipVia
WHEREO.Freight>@FreightParm
RETURN
END

SELECT*FROMLargeOrderShippers(＄500


--根据作者编号计算其所得版权费
createfunctionfun_RoyalTyper(@au_idid)
returnsint
as
begin
declare@rtint
select@rt=sum(royaltyper)fromtitleauthorwhereau_id=@au_id
return(@rt)
end
go

selecttop1au_lname,au_fname,dbo.fun_RoyalTyper(au_id)as'版权费'
fromauthors
orderbydbo.fun_RoyalTyper(au_id)desc
go

createfunctionfun_MaxRoyalTyper_Au_id()
returnsid
as
begin
declare@au_idid
select@au_id=au_id
fromauthors
orderbydbo.fun_RoyalTyper(au_id)
return(@au_id)
end
go

selectdbo.fun_MaxRoyalTyper_Au_id()
go


selectau_lname,au_fname,dbo.fun_RoyalTyper(au_id)as'版权税'
fromauthors
whereau_id=dbo.fun_MaxRoyalTyper_Au_id()
go

(5)高级查询



selecttitle_id,pricefromtitles

--查找最高价格
selectmax(price)fromtitles

--查找最贵书籍的价格(排序)，如果存在多本价格最贵的书，此方法将会遗漏
selecttop1title_id,price
fromtitles
orderbypricedesc

--查找最贵书籍的价格(子查询)
selecttitle_id,price
fromtitles
whereprice=(selectmax(price)fromtitles)

--查询指定出版社出版的书(连接)
selectp.pub_nameas'出版社',t.titleas'书籍名称'
frompublishersaspjointitlesastonp.pub_id=t.pub_id
wherepub_name='NewMoonBooks'

--查询指定出版社出版的书(子查询)
selecttitle
fromtitles
wherepub_id=(selectpub_id
frompublishers
wherepub_name='NewMoonBooks')

--查询指定出版社出版的书(分开查询)
selecttitlefromtitleswherepub_id='0736'

selectpub_id
frompublishers
wherepub_name='NewMoonBooks'


--重点
--理解相关子查询的基础
--
select*fromtitleswheretype='business'
select*fromtitleswheretype='business123'

select*fromtitleswhere1=1

--在订单表中寻找满足以下条件的订单编号以及相应的客户编号
--在详细订单表中存在对应的订单编号并且其中包含产品编号为23的产品
--然后将产品编号为23的产品订购量返回判断是否大于20
USEnorthwind

SELECTorderid,customerid
FROMordersASor1
WHERE20<(SELECTquantityFROM[orderdetails]ASod
WHEREor1.orderid=od.orderid
ANDod.productid=23)
GO

SELECTau_lname,au_fname
FROMauthors
WHERE100IN
(
SELECTroyaltyperFROMtitleauthor
WHEREtitleauthor.au_ID=authors.au_id


selectauthors.au_lname,authors.au_fname
fromauthorsjointitleauthorontitleauthor.au_ID=authors.au_id
wheretitleauthor.royaltyper=100

USEpubs

SELECTau_lname,au_fname
FROMauthors
WHEREau_idIN
(SELECTau_id
FROMtitleauthor
WHEREtitle_idIN
(SELECTtitle_id
FROMtitles
WHEREtype='popular_comp'))



selectdistinctt.type,a.au_lname,a.au_fname
fromauthorsasajointitleauthorastaona.au_id=ta.au_id
jointitlesastonta.title_id=t.title_id
wheret.type='business'

--查找类型为'business'或是'trad_cook'类型的书籍
select*fromtitleswheretype='business'
select*fromtitleswheretype='trad_cook'

--查找类型为'business'或是'trad_cook'类型的书籍(Or)
select*fromtitles
wheretype='business'ortype='trad_cook'

--查找类型为'business'或是'trad_cook'类型的书籍(In)
select*fromtitles
wheretypein('business','trad_cook')

--查找来自'KS'或是'UT'的作者
selectau_lname,statefromauthors
wherestate='KS'
selectau_lname,statefromauthors
wherestate='UT'

--查找来自'KS'或是'UT'的作者(Or)
selectau_lname,statefromauthors
wherestate='UT'orstate='KS'

--查找来自'KS'或是'UT'的作者(In)
selectau_lname,statefromauthors
wherestatein('UT','KS')

selectau_lname,statefromauthors
wherestatenotin('UT','KS')


--查找出版了类型为'business'类型的书籍的出版社
SELECTpub_idFROMtitlesWHEREtype='business'

SELECTpub_id,pub_name
FROMpublishers
WHEREpub_idIN('1389','0736')


--查找出版了类型为'business'类型的书籍的出版社(In和子查询)
SELECTpub_id,pub_name
FROMpublishers
WHEREpub_idIN
(SELECTpub_id
FROMtitles
WHEREtype='business')



SELECTtitle,advance
FROMtitles
WHEREadvance>
(
SELECTMAX(advance)
FROMpublishersINNERJOINtitlesON
titles.pub_id=publishers.pub_id
WHEREpub_name='AlgodataInfosystems'



SELECTtitle,advance
FROMtitles
WHEREadvance>all
(
SELECTadvance
FROMpublishersINNERJOINtitlesON
titles.pub_id=publishers.pub_id
WHEREpub_name='AlgodataInfosystems'
andadvanceisnotnull



declare@iint
set@i=12
if@i<null
print'DDDDD'
else
print'XXXXX'







SELECTadvance
FROMpublishersINNERJOINtitlesON
titles.pub_id=publishers.pub_id
WHEREpub_name='AlgodataInfosystems'




selecttitle_id,pricefromtitles
whereprice>all
(
selectpricefromtitleswheretype='business'
)

selecttitle_id,pricefromtitles
whereprice>
(
selectmax(price)fromtitleswheretype='business'
)

selecttitle_id,pricefromtitles
whereprice>any
(
selectpricefromtitleswheretype='business'
)

selecttitle_id,pricefromtitles
whereprice>
(
selectmin(price)fromtitleswheretype='business'
)

selectpricefromtitleswheretype='business'


ifexists(select*fromtitleswheretype='123')
print'ZZZZZ'
else
print'BBBBB'

ifexists(select*fromauthors
wherecity='Berkeley'andstate='UT')
print'Welcome'
else
print'Bye-Bye'

--筛选出'business'以及'trad_cook'类型的书籍(联合查询)
selecttitle_id,typefromtitleswheretype='business'
union
selecttitle_id,typefromtitleswheretype='trad_cook'

--统计'business'类型的书籍的总价(联合查询)
selecttitle,pricefromtitleswheretype='business'
union
select'合计:',sum(price)fromtitleswheretype='business'

--统计所有书籍的类型剔除重复(Distinct)
selectdistincttypefromtitles

--作者记录的复制(SelectInto)
select*intoaufromauthors

select*fromau

--查看数据表结构(SelectInto并没有对数据表的约束进行复制)
sp_helpauthors
sp_helpau


--分页(子查询的经典应用之一)

--Jobs职务信息表（pubs数据库）
--在实际项目中，显示职务信息时，而职务信息量非常庞大，可能需要将其分为若干个页面来显示
--比如：每页显示4条记录，那么，第一页将显示1，2，3，4，第二页将显示5，6，7，8。。。。。

--显示所有信息
SELECT*FROMjobs
--显示前4信息
selecttop4*fromjobs
--显示前8信息
selecttop8*fromjobs
--显示前12信息
selecttop12*fromjobs

--寻找规律，每一页的信息源于前（页面大小*页码）条信息的反序结果的前页面大小条记录
--比如：第二页就是前8条记录的反序结果的前4条
selecttop4*
from(selecttop8*fromjobs)astt
orderbyjob_iddesc

--当然，对于期望按升序显示查询结果的要求可以对查询结果进行再次排序
select*from
(selecttop4*
from(selecttop8*fromjobs)astt
orderbyjob_iddesc)asstt
orderbyjob_id


--SQL命令中不支持在select的查询列表中直接使用局部变量
--比如：selecttop@PageSize*fromjobs
--那么，可以考虑对sql命令进行拼装，然后，利用系统存储过程sp_executesql来执行
execsp_executesqlN'Select*fromjobs'

--存储过程的实现
--其中，@CurrentPageSize用于确定最后一页的页面大小
createprocproGetJobsByPage
@CurrentPageSizeint,
@PageSizeint,
@CurrentPageint
as
Declare@strSqlnvarchar(400)
set@strSql='select*from
(selecttop'+convert(nvarchar(4),@CurrentPageSize)+'*
from(selecttop'+convert(nvarchar(4),(@PageSize*@CurrentPage))+'*fromjobs)astt
orderbyjob_iddesc)asstt
orderbyjob_id'
execsp_executesql@strSql
go

--测试
execproGetJobsByPage2,4,4



(6)存储过程


--扩展存储过程

--查询系统目录下文件信息
xp_cmdshell'dir*.*'

--启动Windows系统服务
xp_cmdshell'netstartiisadmin'



(7)游标

--游标的五个基本操作步骤：

--声明
declarecur_titlescursor
forselecttitle,pricefromtitles

--打开
opencur_titles

--提取
fetchcur_titles

fetchnextfromcur_titles

--关闭
closecur_titles

--释放
deallocatecur_titles




--利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书
--这一段为批处理版
--与批处理版相比，存储过程版更方便调试以及代码的重用

--声明
declarecur_titlescursor
forselecttitle,pricefromtitles

--打开
opencur_titles

declare@titlevarchar(80)
declare@pricenumeric(9,4)

declare@title_tempvarchar(80)
declare@price_tempnumeric(9,4)

--提取
fetchcur_titlesinto@title,@price

fetchcur_titlesinto@title_temp,@price_temp

while@@fetch_status=0
begin
if@price<@price_temp
begin
set@price=@price_temp
set@title=@title_temp
end
fetchcur_titlesinto@title_temp,@price_temp
end

--关闭
closecur_titles

--释放
deallocatecur_titles

--显示处理结果
print'最贵的书是:'+@title+''+'价格是:'+convert(varchar(12),@price)
go


--定义一个存储过程
--利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书（游标具体应用的经典）
--这段存储过程的实现代码相对下面的实现方式略有不同
--代码重复，但是思路更清晰
createprocedurepro_GetMaxTitle
as
--声明
declarecur_titlescursor
forselecttitle,pricefromtitles

--打开
opencur_titles

--存储最贵的书籍信息
declare@titlevarchar(80)
declare@pricenumeric(9,4)
--存储从游标中提取出来的书籍的信息
declare@title_tempvarchar(80)
declare@price_tempnumeric(9,4)

--提取
fetchcur_titlesinto@title,@price
--判断是否存在书籍信息
if@@fetch_status<>0
begin
print'没有书籍信息!'
--关闭
closecur_titles
--释放
deallocatecur_titles
--结束存储过程
return
end

fetchcur_titlesinto@title_temp,@price_temp

--判断是否只存在一本书
if@@fetch_status<>0
begin
--显示处理结果
print'最贵的书是:'+@title+''+'价格是:'+convert(varchar(12),@price)
--关闭
closecur_titles
--释放
deallocatecur_titles
--结束存储过程
return
end


while@@fetch_status=0
begin
if@price<@price_temp
begin
set@price=@price_temp
set@title=@title_temp
end
fetchcur_titlesinto@title_temp,@price_temp
end

--显示处理结果
print'最贵的书是:'+@title+''+'价格是:'+convert(varchar(12),@price)

--关闭
closecur_titles

--释放
deallocatecur_titles

go

--定义一个存储过程
--利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书
--相对上面的实现方式，以下实现方式更简洁
createprocedurepro_GetMaxTitle
as
--声明
declarecur_titlescursor
forselecttitle,pricefromtitles

--打开
opencur_titles

--存储最贵的书籍信息
declare@titlevarchar(80)
declare@pricenumeric(9,4)
--存储从游标中提取出来的书籍的信息
declare@title_tempvarchar(80)
declare@price_tempnumeric(9,4)

--提取
fetchcur_titlesinto@title,@price
--判断是否存在书籍信息
if@@fetch_status=0
begin
print'没有书籍信息!'
gotoerrNoTitles
end

fetchcur_titlesinto@title_temp,@price_temp
--判断是否只存在一本书
if@@fetch_status=0
begin
gotoerrOnlyOne
end

while@@fetch_status=0
begin
if@price<@price_temp
begin
set@price=@price_temp
set@title=@title_temp
end
fetchcur_titlesinto@title_temp,@price_temp
end

errOnlyOne:
--显示处理结果
print'最贵的书是:'+@title+''+'价格是:'+convert(varchar(12),@price)

errNoTitles:
--关闭
closecur_titles

--释放
deallocatecur_titles


go




--根据作者编号查看其相应的作品年销售量
--低于5000,提示:销售量太低
--高于5000,提示:销售量太高
createprocedurepro_sales_avg(@au_idid)
as
ifexists(selectau_idfromauthorswhereau_id=@au_id)
begin
declareTempSalescursor
for
selecttitle,ytd_sales
fromtitleauthortajointitlest
onta.title_id=t.title_id
whereau_id=@au_id

openTempSales

declare@tvarchar(80)
declare@yint

fetchTempSales
into@t,@y

while@@fetch_status=0
begin
if5000>@y
print@t+''+convert(varchar(5),@y)+'销售量太低'
else
print@t+''+convert(varchar(5),@y)+'销售量太高'
fetchTempSales
into@t,@y
end

closeTempSales
deallocateTempSales
end
else
print'作者编号无效！'
go

execpro_sales_avg'213-46-8915'



/*
示例
A.使用简单游标和语法
打开该游标时所生成的结果集包括pubs数据库的authors表中的所有行和列。可以更新该游标，对该游标所做的所有更新和删除均在提取中表现出来。因为没指定SCROLL选项，FETCHNEXT是唯一可用的提取选项。
*/

DECLAREauthors_cursorCURSOR
FOR
SELECT*FROMauthors

OPENauthors_cursor

FETCHNEXTFROMauthors_cursor

/*
B.使用嵌套游标生成报表输出
下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。
*/

SETNOCOUNTON

DECLARE@au_idvarchar(11),@au_fnamevarchar(20),@au_lnamevarchar(40),
@messagevarchar(80),@titlevarchar(80)

PRINT"--------UtahAuthorsreport--------"

DECLAREauthors_cursorCURSOR
FOR
SELECTau_id,au_fname,au_lname
FROMauthors
WHEREstate="UT"
ORDERBYau_id

OPENauthors_cursor

FETCHNEXTFROMauthors_cursor
INTO@au_id,@au_fname,@au_lname

WHILE@@FETCH_STATUS=0
BEGIN
PRINT""
SELECT@message="-----BooksbyAuthor:"+
@au_fname+""+@au_lname

PRINT@message

--Declareaninnercursorbased
--onau_idfromtheoutercursor.

DECLAREtitles_cursorCURSORFOR
SELECTt.title
FROMtitleauthorta,titlest
WHEREta.title_id=t.title_idAND
ta.au_id=@au_id--Variablevaluefromtheoutercursor

OPENtitles_cursor
FETCHNEXTFROMtitles_cursorINTO@title

IF@@FETCH_STATUS<>0
PRINT"<<NoBooks>>"

WHILE@@FETCH_STATUS=0
BEGIN

SELECT@message=""+@title
PRINT@message
FETCHNEXTFROMtitles_cursorINTO@title

END

CLOSEtitles_cursor
DEALLOCATEtitles_cursor

--Getthenextauthor.
FETCHNEXTFROMauthors_cursor
INTO@au_id,@au_fname,@au_lname
END

CLOSEauthors_cursor
DEALLOCATEauthors_cursor
GO

--------UtahAuthorsreport--------

-----BooksbyAuthor:AnneRinger
TheGourmetMicrowave
IsAngertheEnemy?

-----BooksbyAuthor:AlbertRinger
IsAngertheEnemy?
LifeWithoutFear










(8)触发器




--设定数据库的递归触发器
alterdatabasepubs
setrecursive_triggerson
go

--创建数据表，并设定主键、外键以及缺省约束
createtableemp_mgr
(
Empchar(30)primarykey,
Mgrchar(30)nullforeignkeyreferencesemp_mgr(Emp),
NoOfReportsintdefault0
)
go

--创建插入触发器
createtriggeremp_marins
onemp_mgr
forinsert
as
declare@echar(30),@mchar(30)
declarecur_mgrcursorfor
selectemp_mgr.emp
fromemp_mgr,inserted
whereemp_mgr.emp=inserted.mgr

opencur_mgr

fetchnextfromcur_mgrinto@e

while@@fetch_status=0
begin
updateemp_mgr
setemp_mgr.NoOfReports=emp_mgr.NoOfReports+1
whereemp_mgr.emp=@e

fetchnextfromcur_mgrinto@e
end

closecur_mgr

deallocatecur_mgr

go

--查看数据表相关触发器
sp_helptriggeremp_mgr
go


createtriggeremp_mgrupd
onemp_mgr
forupdate
as
ifupdate(mgr)
begin
updateemp_mgr
setemp_mgr.NoOfReports=emp_mgr.NoofReports+1
frominserted
whereemp_mgr.emp=inserted.mgr

updateemp_mgr
setemp_mgr.NoOfReports=emp_mgr.NoOfReports-1
fromdeleted
whereemp_mgr.emp=deleted.mgr

end

go


insertemp_mgr(emp,mgr)values('Harry',null)
insertemp_mgr(emp,mgr)values('Alice','Harry')
insertemp_mgr(emp,mgr)values('Paul','Alice')
insertemp_mgr(emp,mgr)values('Joe','Alice')
insertemp_mgr(emp,mgr)values('Dave','Joe')
go

select*fromemp_mgr
go

updateemp_mgr
setmgr='Harry'
whereemp='Dave'
go

select*fromemp_mgr
go

loading...