SQLServer数据库的高级操作

(1)批处理 (2)(2)变量 (3)(3)逻辑控制 (5)（4）函数 (7)(4.1)系统函数 (7)（4.2）自定义函数 (13)(5)高级查询 (23)(6)存储过程 (35)(7)游标 (36)(8)触发器 (50)SQL Server 数据库的高级操作(1) 批处理(2) 变量(3) 逻辑控制(4) 函数(5) 高级查询*/(1)批处理将多条SQL语句作为一个整体去编译，生成一个执行计划，然后，执行！理解批处理的关键在于"编译"，对于由多条语句组成的一个批处理，如果在编译时，其中，有一条出现语法错误，将会导致编译失败！create table t(a int,)-- 如果多行注释中包含了批处理的标识符go-- 在编译的过程中代码将会被go分割成多个部分来分批编译-- 多行注释的标记将会被分隔而导致编译出错-- 以下几条语句是三个非常经典的批处理-- 你猜一下会添加几条记录！/*insert into t values (1,1)go*/insert into t values (2,2)go/*insert into t values (3,3)*/go-- 查询看添加了几条记录select * from ttruncate table t(2)变量-- 全局变量SQL Server中全局变量由系统定义、系统维护，用户一般仅可对其进行读取！-- 查看SQL Server版本print @@version-- 服务器名称print @@servername-- 系统错误编号insert into t values ('a','a')print @@errorinsert into t values ('a','a')if @@error = 245print 'Error'-- SQL Server 版本的语言信息print @@LANGUAGE-- 一周的第一天从星期几算起print @@datefirst-- CPU 执行命令所耗费时间的累加print @@cpu_busy-- 获取最近添加的标识列的值create table tt(a int identity(3, 10),b int)insert into tt (b) values (1)print @@identityselect * from tt-- 局部变量局部变量由用户定义，仅可在同一个批处理中调用和访问declare @intAge tinyintset @intAge = 12print @intAgedeclare @strName varchar(12)select @strName = 'state'print @strNameselect au_lname, @strName from authors(3)逻辑控制-- IF条件判断declare @i intset @i = 12if (@i > 10)begin -- {print 'Dadadada!'print 'Dadadada!' end -- } elsebeginprint 'XiaoXiao!'print 'XiaoXiao!' end-- While循环控制declare @i int;set @i = 12;print @ireturn;while (@i < 18)beginprint @i;set @i = @i + 1;if @i < 17continue;if @i > 15break;end;-- CASE 分支判断select au_lname, state, '犹他州' from authors where state = 'UT' select au_lname, state, '密西西比州' from authors where state = 'MI' select au_lname, state, '肯塔基州' from authors where state = 'KS' select au_lname, state,case statewhen 'UT' then '犹他州'when 'MI' then '密西西比州'when 'KS' then '肯塔基州'when 'CA' then '加利福利亚'else stateendfrom authors（4）函数(4.1)系统函数-- 获取指定字符串中左起第一个字符的ASC码print ascii('ABCDEF')-- 根据给定的ASC码获取相应的字符print char(65)-- 获取给定字符串的长度print len('abcdef')-- 大小写转换print lower('ABCDEF')print upper('abcdef')-- 去空格print ltrim(' abcd dfd df ') print rtrim(' abcd dfd df ') -- 求绝对值print abs(-12)-- 幂-- 3 的 2 次方print power(3,2)print power(3,3)-- 随机数-- 0 - 1000 之间的随机数print rand() * 1000-- 获取圆周率print pi()-- 获取系统时间print getdate()-- 获取3天前的时间print dateadd(day, -3 , getdate()) -- 获取3天后的时间print dateadd(day, 3 , getdate()) -- 获取3年前的时间print dateadd(year, -3 , getdate()) -- 获取3年后的时间print dateadd(year, 3 , getdate()) -- 获取3月后的时间print dateadd(month, 3 , getdate()) -- 获取9小时后的时间print dateadd(hour, 9 , getdate()) -- 获取9分钟后的时间print dateadd(minute, 9 , getdate())-- 获取指定时间之间相隔多少年print datediff(year, '2005-01-01', '2008-01-01')-- 获取指定时间之间相隔多少月print datediff(month, '2005-01-01', '2008-01-01')-- 获取指定时间之间相隔多少天print datediff(day, '2005-01-01', '2008-01-01')-- 字符串合并print 'abc' + 'def'print 'abcder'print 'abc' + '456'print 'abc' + 456-- 类型转换print 'abc' + convert(varchar(10), 456)select title_id, type, price from titles-- 字符串连接必须保证类型一致(以下语句执行将会出错)-- 类型转换select title_id + type + price from titles-- 正确select title_id + type + convert(varchar(10), price) from titlesprint '123' + convert(varchar(3), 123)print '123' + '123'print convert(varchar(12), '2005-09-01',110)-- 获取指定时间的特定部分print year(getdate())print month(getdate())print day(getdate())-- 获取指定时间的特定部分print datepart(year, getdate())print datepart(month, getdate())print datepart(day, getdate())print datepart(hh, getdate())print datepart(mi, getdate())print datepart(ss, getdate())print datepart(ms, getdate())-- 获取指定时间的间隔部分-- 返回跨两个指定日期的日期和时间边界数print datediff(year, '2001-01-01', '2008-08-08') print datediff(month, '2001-01-01', '2008-08-08') print datediff(day, '2001-01-01', '2008-08-08')print datediff(hour, '2001-01-01', '2008-08-08') print datediff(mi, '2001-01-01', '2008-08-08') print datediff(ss, '2001-01-01', '2008-08-08')-- 在向指定日期加上一段时间的基础上，返回新的 datetime 值print dateadd(year, 5, getdate())print dateadd(month, 5, getdate())print dateadd(day, 5, getdate())print dateadd(hour, 5, getdate())print dateadd(mi, 5, getdate())print dateadd(ss, 5, getdate())-- 其他print host_id()print host_name()print db_id('pubs')print db_name(5)-- 利用系统函数作为默认值约束drop table tttcreate table ttt(stu_name varchar(12),stu_birthday datetime default (getdate()))alter table tttadd constraint df_ttt_stu_birthday default (getdate()) for stu_birthday insert into ttt values ('ANiu', '2005-04-01')insert into ttt values ('ANiu', getdate())insert into ttt values ('AZhu', default)sp_help tttselect * from ttt（4.2）自定义函数select title_idfrom titleswhere type = 'business'select stuff(title_id,1,3,'ABB'), typefrom titleswhere type = 'business'select count(title_id) from titles where type = 'business'select title_id from titles where type = 'business'select *,count(dbo.titleauthor.title_id)FROM dbo.authors INNER JOINdbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_idselect au_id, count(title_id)from titleauthorgroup by au_idSELECT dbo.authors.au_id, COUNT(dbo.titleauthor.title_id) AS '作品数量' FROM dbo.authors left outer JOINdbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id GROUP BY dbo.authors.au_idorder by '作品数量'-- 自定义函数的引子（通过这个子查询来引入函数的作用）-- 子查询-- 统计每个作者的作品数-- 将父查询中的作者编号传入子查询-- 作为查询条件利用聚合函数count统计其作品数量select au_lname,(select count(title_id) from titleauthor as tawhere ta.au_id = a.au_id ) as TitleCountfrom authors as aorder by TitleCount-- 是否可以定义一个函数-- 将作者编号作为参数统计其作品数量并将其返回select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount from authorsorder by TitleCount-- 根据给定的作者编号获取其相应的作品数量create function GetTitleCountByAuID(@au_id varchar(12))returns intbeginreturn (select count(title_id)from titleauthorwhere au_id = @au_id)end-- 利用函数来显示每个作者的作品数量create proc pro_CalTitleCountasselect au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount from authorsorder by TitleCountgo-- 执行存储过程execute pro_CalTitleCount-- vb中函数定义格式function GetTitleCountByAuID(au_id as string) as integer .......GetTitleCountByAuID = ?end function-- SALES 作品销售信息select * from sales-- 根据书籍编号查询其销售记录（其中，qty 表示销量）select * from sales where title_id = 'BU1032'-- 根据书籍编号统计其总销售量（其中，qty 表示销量）select sum(qty) from sales where title_id = 'BU1032'-- 利用分组语句（group by），根据书籍编号统计每本书总销售量（其中，qty 表示销量）select title_id, sum(qty) from sales group by title_id-- 是否可以考虑定义一个函数根据书籍编号来计算其总销售量-- 然后，将其应用到任何一条包含了书籍编号的查询语句中select title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSalesfrom titlesorder by TotalSales-- 定义一个函数根据书籍编号来计算其总销售量create function GetTotalSaleByTitleID(@tid varchar(24))returns intbeginreturn(select sum(qty) from sales where title_id = @tid)end-- 统计书籍销量的前10位-- 其中，可以利用函数计算结果的别名作为排序子句的参照列select top 10 title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSales from titlesorder by TotalSales desc-- 根据书籍编号计算其销量排名create function GetTheRankOfTitle(@id varchar(20))returns intbeginreturn(select count(TotalSales)from titleswhere ToalSales >(select TotalSalesfrom titleswhere title_id=@id))end-- 根据书籍编号计算其销量排名select dbo.GetTheRankOfTitle('pc1035') from titlesselect count(title_id) + 1from titleswhere dbo.GetTotalSaleByTitleID(title_id) > dbo.GetTotalSaleByTitleID('pc1035') -- 删除函数drop function GetRankByTitleId-- 根据书籍编号计算其销量排名create function GetRankByTitleId(@tid varchar(24))returns intbeginreturn (select count(title_id) + 1from titleswhere dbo.GetTotalSaleByTitleID(title_id) > dbo.GetTotalSaleByTitleID(@tid)) end-- 在查询语句中利用函数统计每本书的总销量和总排名select title_id, title,dbo.GetTotalSaleByTitleID(title_id) as TotalSales,dbo.GetRankByTitleId(title_id) as TotalRankfrom titlesorder by TotalSales desc-- 查看表结构sp_help titles-- 查看存储过程的定义内容sp_helptext GetRankByTitleIdsp_helptext sp_helptextsp_helptext xp_cmdshell-- [ORDER DETAILS] 订单详细信息select * from [order details]select * from [order details] where productid = 23-- 根据产品编号在订单详细信息表中统计总销售量select sum(quantity) from [order details] where productid = 23-- 构造一个函数根据产品编号在订单详细信息表中统计总销售量create function GetTotalSaleByPID(@Pid varchar(12))returns intbeginreturn(select sum(quantity) from [order details] where productid = @Pid) endselect * from products-- 在产品表中查询，统计每一样产品的总销量select productid, productname, dbo.GetTotalSaleByPID(productid) from products--CREATE FUNCTION LargeOrderShippers ( @FreightParm money )RETURNS @OrderShipperTab TABLE(ShipperID int,ShipperName nvarchar(80),OrderID int,ShippedDate datetime,Freight money)ASBEGININSERT @OrderShipperTabSELECT S.ShipperID, panyName,O.OrderID, O.ShippedDate, O.Freight FROM Shippers AS S INNER JOIN Orders AS OON S.ShipperID = O.ShipViaWHERE O.Freight > @FreightParmRETURNENDSELECT * FROM LargeOrderShippers( $500 )-- 根据作者编号计算其所得版权费create function fun_RoyalTyper ( @au_id id)returns intasbegindeclare @rt intselect @rt = sum(royaltyper) from titleauthor where au_id = @au_id return (@rt)endgoselect top 1 au_lname, au_fname, dbo.fun_RoyalTyper(au_id) as '版权费' from authorsorder by dbo.fun_RoyalTyper(au_id) descgocreate function fun_MaxRoyalTyper_Au_id ()returns idasbegindeclare @au_id idselect @au_id = au_idfrom authorsorder by dbo.fun_RoyalTyper(au_id)return(@au_id)endgoselect dbo.fun_MaxRoyalTyper_Au_id()goselect au_lname, au_fname, dbo.fun_RoyalTyper(au_id) as '版权税' from authorswhere au_id = dbo.fun_MaxRoyalTyper_Au_id()go(5)高级查询select title_id, price from titles-- 查找最高价格select max(price) from titles-- 查找最贵书籍的价格(排序)，如果存在多本价格最贵的书，此方法将会遗漏select top 1 title_id, pricefrom titlesorder by price desc-- 查找最贵书籍的价格(子查询)select title_id, pricefrom titleswhere price = (select max(price) from titles)-- 查询指定出版社出版的书(连接)select p.pub_name as '出版社', t.title as '书籍名称'from publishers as p join titles as t on p.pub_id = t.pub_id where pub_name = 'New Moon Books'-- 查询指定出版社出版的书(子查询)select titlefrom titleswhere pub_id = (select pub_idfrom publisherswhere pub_name = 'New Moon Books')-- 查询指定出版社出版的书(分开查询)select title from titles where pub_id = '0736'select pub_idfrom publisherswhere pub_name = 'New Moon Books'-- 重点-- 理解相关子查询的基础--select * from titles where type = 'business'select * from titles where type = 'business123'select * from titles where 1 = 1-- 在订单表中寻找满足以下条件的订单编号以及相应的客户编号-- 在详细订单表中存在对应的订单编号并且其中包含产品编号为23的产品-- 然后将产品编号为23的产品订购量返回判断是否大于20USE northwindSELECT orderid, customeridFROM orders AS or1WHERE 20 < (SELECT quantity FROM [order details] AS odWHERE or1.orderid = od.orderidAND od.productid = 23)GOSELECT au_lname, au_fnameFROM authorsWHERE 100 IN(SELECT royaltyper FROM titleauthorWHERE titleauthor.au_ID = authors.au_id)select authors.au_lname,authors.au_fnamefrom authors join titleauthor on titleauthor.au_ID=authors.au_id where titleauthor.royaltyper =100USE pubsSELECT au_lname, au_fnameFROM authorsWHERE au_id IN(SELECT au_idFROM titleauthorWHERE title_id IN(SELECT title_idFROM titlesWHERE type = 'popular_comp'))select distinct t.type, a.au_lname, a.au_fnamefrom authors as a join titleauthor as ta on a.au_id = ta.au_id join titles as t on ta.title_id = t.title_idwhere t.type = 'business'-- 查找类型为'business'或是'trad_cook'类型的书籍select * from titles where type = 'business'select * from titles where type = 'trad_cook'-- 查找类型为'business'或是'trad_cook'类型的书籍(Or)select * from titleswhere type = 'business' or type = 'trad_cook'-- 查找类型为'business'或是'trad_cook'类型的书籍(In)select * from titleswhere type in ('business', 'trad_cook')-- 查找来自'KS'或是'UT'的作者select au_lname, state from authorswhere state = 'KS'select au_lname, state from authorswhere state = 'UT'-- 查找来自'KS'或是'UT'的作者(Or)select au_lname, state from authorswhere state = 'UT' or state = 'KS'-- 查找来自'KS'或是'UT'的作者(In)select au_lname, state from authorswhere state in ('UT', 'KS')select au_lname, state from authorswhere state not in ('UT', 'KS')-- 查找出版了类型为'business'类型的书籍的出版社SELECT pub_id FROM titles WHERE type = 'business'SELECT pub_id,pub_nameFROM publishersWHERE pub_id IN ('1389', '0736')-- 查找出版了类型为'business'类型的书籍的出版社(In和子查询) SELECT pub_id,pub_nameFROM publishersWHERE pub_id IN(SELECT pub_idFROM titlesWHERE type = 'business')SELECT title, advanceFROM titlesWHERE advance >(SELECT MAX(advance)FROM publishers INNER JOIN titles ONtitles.pub_id = publishers.pub_id WHERE pub_name = 'Algodata Infosystems' )SELECT title, advanceFROM titlesWHERE advance > all(SELECT advanceFROM publishers INNER JOIN titles ONtitles.pub_id = publishers.pub_id WHERE pub_name = 'Algodata Infosystems' and advance is not null)declare @i intset @i = 12if @i < nullprint 'DDDDD'elseprint 'XXXXX'SELECT advanceFROM publishers INNER JOIN titles ON titles.pub_id = publishers.pub_id WHERE pub_name = 'Algodata Infosystems'select title_id, price from titleswhere price > all(select price from titles where type = 'business' )select title_id, price from titleswhere price >(select max(price) from titles where type = 'business' )select title_id, price from titleswhere price > any(select price from titles where type = 'business')select title_id, price from titleswhere price >(select min(price) from titles where type = 'business' )select price from titles where type = 'business'if exists(select * from titles where type = '123') print 'ZZZZZ'elseprint 'BBBBB'if exists(select * from authorswhere city = 'Berkeley' and state ='UT')print 'Welcome'elseprint 'Bye-Bye'-- 筛选出'business'以及'trad_cook'类型的书籍(联合查询)select title_id, type from titles where type = 'business' unionselect title_id, type from titles where type = 'trad_cook'-- 统计'business'类型的书籍的总价(联合查询)select title, price from titles where type = 'business'unionselect '合计:', sum(price) from titles where type = 'business' -- 统计所有书籍的类型剔除重复(Distinct)select distinct type from titles-- 作者记录的复制(Select Into)select * into au from authorsselect * from au-- 查看数据表结构(Select Into并没有对数据表的约束进行复制)sp_help authorssp_help au-- 分页(子查询的经典应用之一)-- Jobs 职务信息表（pubs 数据库）-- 在实际项目中，显示职务信息时，而职务信息量非常庞大，可能需要将其分为若干个页面来显示-- 比如：每页显示4条记录，那么，第一页将显示1，2，3，4，第二页将显示5，6，7，8。