SQLServer数据库的高级操作.docx
《SQLServer数据库的高级操作.docx》由会员分享，可在线阅读，更多相关《SQLServer数据库的高级操作.docx（32页珍藏版）》请在冰点文库上搜索。
SQLServer数据库的高级操作
（1）批处理2
（2）变量3
（3）逻辑控制5
（4）函数7
（4.1）系统函数7
（4.2）自定义函数13
（5）高级查询23
（6）存储过程35
（7）游标36
（8）触发器50
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）函数
（4.1）系统函数
--获取指定字符串中左起第一个字符的ASC码
printascii（'ABCDEF'）
--根据给定的ASC码获取相应的字符
printchar（65）
--获取给定字符串的长度
printlen（'abcdef'）
--大小写转换
printlower（'ABCDEF'）
printupper（'abcdef'）
--去空格
printltrim（' abcd dfd df '）
printrtrim（' abcd dfd df '）
--求绝对值
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_name varchar（12）,
stu_birthday datetimedefault（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.authors leftouterJOIN
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
（
ShipperID int,
ShipperName nvarchar（80）,
OrderID int,
ShippedDate datetime,
Freight money
）
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
orderby dbo.fun_RoyalTyper（au_id）desc
go
createfunctionfun_MaxRoyalTyper_Au_id（）
returnsid
as
begin
declare@au_idid
select@au_id=au_id
fromauthors
orderby dbo.fun_RoyalTyper（au_id）
return（@au_id）
end
go
selectdbo.fun_MaxRoyalTyper_Au_id（）
go
selectau_l
升级会员 