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
(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...