1 /*   2    3    4 2006年10月01日   5    6 SQL Server 数据库的高级操作   7 (1) 批处理   8 (2) 变量   9 (3) 逻辑控制  10 (4) 视图  11 (5) 函数  12 (6) 高级查询  13   14 */  15   16 (1)批处理  17 将多条SQL语句作为一个整体去编译，生成一个执行计划，然后，执行！  18 理解批处理的关键在于"编译"，对于由多条语句组成的一个批处理，  19 如果在编译时，其中，有一条出现语法错误，将会导致编译失败！  20   21 create table t  22 (  23 a int,  24 b int  25 )  26   27 -- 注释  28 -- 如果多行注释中包含了批处理的标识符go  29 -- 在编译的过程中代码将会被go分割成多个部分来分批编译  30 -- 多行注释的标记将会被分隔而导致编译出错  31 -- 以下几条语句是三个非常经典的批处理  32 -- 你猜一下会添加几条记录！  33 /*  34 insert into t values (1,1)  35 go  36 */  37 insert into t values (2,2)  38 go  39 /*  40 insert into t values (3,3)  41 */  42 go  43   44   45 -- 查询看添加了几条记录  46 select * from t  47   48 truncate table t  49   50 (2)变量  51   52 -- 全局变量  53 SQL Server中全局变量由系统定义、系统维护，用户一般仅可对其进行读取！  54   55 -- 查看SQL Server版本  56 print @@version  57   58 -- 服务器名称  59 print @@servername  60   61 -- 系统错误编号  62 insert into t values (‘a‘,‘a‘)  63 print @@error  64   65 insert into t values (‘a‘,‘a‘)  66 if @@error = 245  67     print ‘Error‘  68   69 -- SQL Server 版本的语言信息  70 print @@LANGUAGE  71   72 -- 一周的第一天从星期几算起  73 print @@datefirst  74   75 -- CPU 执行命令所耗费时间的累加  76 print @@cpu_busy  77   78 -- 获取最近添加的标识列的值  79 create table tt  80 (  81 a int identity(3, 10),  82 b int  83 )  84 insert into tt (b) values (1)  85 print @@identity  86 select * from tt  87   88 -- 局部变量  89 局部变量由用户定义，仅可在同一个批处理中调用和访问  90   91 declare @intAge tinyint  92 set @intAge = 12  93 print @intAge  94   95 declare @strName varchar(12)  96 select @strName = ‘state‘  97 print @strName  98 select au_lname, @strName from authors  99  100 (3)逻辑控制 101  102 -- IF条件判断 103 declare @i int 104 set @i = 12 105 if (@i > 10) 106     begin                -- { 107         print ‘Dadadada!‘ 108         print ‘Dadadada!‘ 109     end                -- } 110 else 111     begin 112         print ‘XiaoXiao!‘ 113         print ‘XiaoXiao!‘ 114     end 115  116 -- While循环控制 117 declare @i int; 118 set @i = 12; 119 print @i 120 --return; 121 while (@i < 18) 122 begin 123     print @i; 124     set @i = @i + 1; 125     if @i < 17 126         continue; 127     if @i > 15 128         break; 129 end; 130 print @i 131  132 -- CASE 分支判断 133 select au_lname, state, ‘犹他州‘ from authors where state = ‘UT‘ 134 select au_lname, state, ‘密西西比州‘ from authors where state = ‘MI‘ 135 select au_lname, state, ‘肯塔基州‘ from authors where state = ‘KS‘ 136  137 select au_lname, state,  138     case state 139     when ‘UT‘ then ‘犹他州‘ 140     when ‘MI‘ then ‘密西西比州‘ 141     when ‘KS‘ then ‘肯塔基州‘ 142     when ‘CA‘ then ‘加利福利亚‘ 143     else state 144     end 145 from authors 146  147  148 (3)视图 149 -- Northwind 数据库中Employees表 150 -- 创建视图显示每个员工的编号(EmployeeID)、姓(FirstName)、名(LastName)以及上级(ReportsTo)的姓 151 use northwind 152 go 153  154 -- 注意：字段ReportsTo指代了每个员工的上级的编号 155 select EmployeeID, FirstName, LastName, ReportsTo  156 from employees 157 go 158  159 -- 寻找每个员工的上级 160 -- 子查询 161 select emp.EmployeeID, emp.FirstName, emp.LastName,  162     (select mag.FirstName from employees as mag where emp.ReportsTo = mag.EmployeeID ) as ManagerFirstName 163 from employees as emp 164 go 165  166  167  168  169  170 (4.1)系统函数 171  172 -- 获取指定字符串中左起第一个字符的ASC码 173 print ascii(‘ABCDEF‘) 174 -- 根据给定的ASC码获取相应的字符 175 print char(65) 176 -- 获取给定字符串的长度 177 print len(‘abcdef‘) 178 -- 大小写转换 179 print lower(‘ABCDEF‘) 180 print upper(‘abcdef‘) 181 -- 去空格 182 print ltrim(‘    abcd  dfd  df  ‘) 183 print rtrim(‘    abcd  dfd  df  ‘) 184 -- 求绝对值 185 print abs(-12) 186 -- 幂 187 -- 3 的 2 次方 188 print power(3,2) 189 print power(3,3) 190 -- 随机数 191 -- 0 - 1000 之间的随机数 192 print rand() * 1000  193 -- 获取圆周率 194 print pi() 195  196  197 -- 获取系统时间 198 print getdate() 199  200 -- 获取3天前的时间 201 print dateadd(day, -3 , getdate()) 202 -- 获取3天后的时间 203 print dateadd(day, 3 , getdate()) 204 -- 获取3年前的时间 205 print dateadd(year, -3 , getdate()) 206 -- 获取3年后的时间 207 print dateadd(year, 3 , getdate()) 208  209 -- 获取3月后的时间 210 print dateadd(month, 3 , getdate()) 211 -- 获取9小时后的时间 212 print dateadd(hour, 9 , getdate()) 213 -- 获取9分钟后的时间 214 print dateadd(minute, 9 , getdate()) 215  216 -- 获取指定时间之间相隔多少年 217 print datediff(year, ‘2005-01-01‘, ‘2008-01-01‘) 218 -- 获取指定时间之间相隔多少月 219 print datediff(month, ‘2005-01-01‘, ‘2008-01-01‘) 220 -- 获取指定时间之间相隔多少天 221 print datediff(day, ‘2005-01-01‘, ‘2008-01-01‘) 222  223 -- 字符串合并 224 print ‘abc‘ + ‘def‘ 225  226 print ‘abcder‘ 227  228 print ‘abc‘ + ‘456‘ 229 print ‘abc‘ + 456 230  231 -- 类型转换 232 print ‘abc‘ + convert(varchar(10), 456) 233  234 select title_id, type, price from titles 235 -- 字符串连接必须保证类型一致(以下语句执行将会出错) 236 -- 类型转换 237 select title_id + type + price from titles 238 -- 正确 239 select title_id + type + convert(varchar(10), price) from titles 240  241 print ‘123‘ + convert(varchar(3), 123) 242 print ‘123‘ + ‘123‘ 243  244 print convert(varchar(12), ‘2005-09-01‘,110) 245  246 -- 获取指定时间的特定部分 247 print year(getdate()) 248 print month(getdate()) 249 print day(getdate()) 250  251 -- 获取指定时间的特定部分 252 print datepart(year, getdate()) 253 print datepart(month, getdate()) 254 print datepart(day, getdate()) 255 print datepart(hh, getdate()) 256 print datepart(mi, getdate()) 257 print datepart(ss, getdate()) 258 print datepart(ms, getdate()) 259  260 -- 获取指定时间的间隔部分 261 -- 返回跨两个指定日期的日期和时间边界数 262 print datediff(year, ‘2001-01-01‘, ‘2008-08-08‘) 263 print datediff(month, ‘2001-01-01‘, ‘2008-08-08‘) 264 print datediff(day, ‘2001-01-01‘, ‘2008-08-08‘) 265 print datediff(hour, ‘2001-01-01‘, ‘2008-08-08‘) 266 print datediff(mi, ‘2001-01-01‘, ‘2008-08-08‘) 267 print datediff(ss, ‘2001-01-01‘, ‘2008-08-08‘) 268  269 -- 在向指定日期加上一段时间的基础上，返回新的 datetime 值 270 print dateadd(year, 5, getdate()) 271 print dateadd(month, 5, getdate()) 272 print dateadd(day, 5, getdate()) 273 print dateadd(hour, 5, getdate()) 274 print dateadd(mi, 5, getdate()) 275 print dateadd(ss, 5, getdate()) 276  277 -- 其他 278 print host_id() 279 print host_name() 280 print db_id(‘pubs‘) 281 print db_name(5) 282  283  284 -- 利用系统函数作为默认值约束 285 drop table ttt 286  287 create table ttt 288 ( 289 stu_name    varchar(12), 290 stu_birthday    datetime default (getdate()) 291 ) 292  293 alter table ttt 294 add constraint df_ttt_stu_birthday default  (getdate()) for stu_birthday 295  296 insert into ttt values (‘ANiu‘, ‘2005-04-01‘) 297 insert into ttt values (‘ANiu‘, getdate()) 298  299 insert into ttt values (‘AZhu‘, default) 300  301 sp_help ttt 302  303 select * from ttt 304  305  306  307 （4.2）自定义函数 308  309 select title_id 310 from titles  311 where type = ‘business‘ 312  313 select stuff(title_id,1,3,‘ABB‘), type  314 from titles  315 where type = ‘business‘ 316  317 select count(title_id) from titles where type = ‘business‘ 318 select title_id from titles where type = ‘business‘ 319  320  321 select  *,count(dbo.titleauthor.title_id) 322 FROM dbo.authors INNER JOIN 323 dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id 324  325 select au_id, count(title_id) 326 from titleauthor 327 group by au_id 328  329 SELECT dbo.authors.au_id, COUNT(dbo.titleauthor.title_id) AS ‘作品数量‘ 330 FROM dbo.authors  left outer JOIN 331       dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id 332 GROUP BY dbo.authors.au_id 333 order by ‘作品数量‘ 334  335 -- 自定义函数的引子（通过这个子查询来引入函数的作用） 336  337 -- 子查询 338 -- 统计每个作者的作品数 339 -- 将父查询中的作者编号传入子查询 340 -- 作为查询条件利用聚合函数count统计其作品数量 341 select au_lname,   342     (select count(title_id)  343     from titleauthor as ta  344     where ta.au_id = a.au_id 345     ) as TitleCount 346 from authors as a 347 order by TitleCount 348  349  350  351  352 -- 是否可以定义一个函数 353 -- 将作者编号作为参数统计其作品数量并将其返回 354 select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount  355 from authors 356 order by TitleCount 357  358 -- 根据给定的作者编号获取其相应的作品数量 359 create function GetTitleCountByAuID(@au_id varchar(12)) 360 returns int 361 begin 362     return (select count(title_id)  363         from titleauthor 364         where au_id = @au_id) 365 end  366  367  368 -- 利用函数来显示每个作者的作品数量 369 create proc pro_CalTitleCount 370 as 371 select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount  372 from authors 373 order by TitleCount 374 go 375  376 -- 执行存储过程 377 execute pro_CalTitleCount 378  379 -- vb中函数定义格式 380 function GetTitleCountByAuID(au_id as string) as integer 381      382     ....... 383  384     GetTitleCountByAuID = ? 385 end function 386  387 -- SALES 作品销售信息 388 select * from sales 389  390 -- 根据书籍编号查询其销售记录（其中，qty 表示销量） 391 select * from sales where title_id = ‘BU1032‘ 392  393 -- 根据书籍编号统计其总销售量（其中，qty 表示销量） 394 select sum(qty) from sales where title_id = ‘BU1032‘ 395  396 -- 利用分组语句（group by），根据书籍编号统计每本书总销售量（其中，qty 表示销量） 397 select title_id, sum(qty) from sales group by title_id 398  399 -- 是否可以考虑定义一个函数根据书籍编号来计算其总销售量 400 -- 然后，将其应用到任何一条包含了书籍编号的查询语句中 401 select title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSales 402 from titles 403 order by TotalSales 404  405 -- 定义一个函数根据书籍编号来计算其总销售量 406 create function GetTotalSaleByTitleID(@tid varchar(24)) 407 returns int 408 begin 409     return(select sum(qty) from sales where title_id = @tid) 410 end 411  412 select count(title_id) + 1 413 from titles  414 where dbo.GetTotalSaleByTitleID(title_id) > dbo.GetTotalSaleByTitleID(‘pc1035‘) 415  416 -- 删除函数 417 drop function GetRankByTitleId 418  419 -- 根据书籍编号计算其销量排名 420 create function GetRankByTitleId(@tid varchar(24)) 421 returns int 422 begin 423     return (select count(title_id) + 1 424         from titles  425         where dbo.GetTotalSaleByTitleID(title_id) > dbo.GetTotalSaleByTitleID(@tid)) 426 end 427  428 -- 统计书籍销量的前10位 429 -- 其中，可以利用函数计算结果的别名作为排序子句的参照列 430 select top 10 title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSales 431 from titles 432 order by TotalSales desc 433  434 -- 书籍销量排名视图 435 create view viewToalSales 436 as 437 select title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSales 438 from titles 439 go 440  441 select * from viewToalSales; 442  443 -- 根据书籍编号计算其销量排名 444 create function GetTheRankOfTitle(@id varchar(20)) 445 returns int 446 begin 447     return(select count(TotalSales) + 1 448         from viewToalSales 449          where TotalSales > dbo.GetTotalSaleByTitleID(@id)) 450 end 451  452 -- 根据书籍编号计算其销量排名 453 select dbo.GetTheRankOfTitle(‘pc1035‘) from titles 454  455 -- 在查询语句中利用函数统计每本书的总销量和总排名 456 select title_id, title, 457     dbo.GetTotalSaleByTitleID(title_id) as TotalSales, 458     dbo.GetRankByTitleId(title_id) as TotalRank 459 from titles 460 order by TotalSales desc 461  462 -- 查看表结构 463 sp_help titles 464 -- 查看存储过程的定义内容 465 sp_helptext GetRankByTitleId 466 sp_helptext sp_helptext  467 sp_helptext xp_cmdshell 468  469  470 -- [ORDER DETAILS] 订单详细信息 471 select * from [order details]  472 select * from [order details] where productid = 23 473 -- 根据产品编号在订单详细信息表中统计总销售量 474 select sum(quantity) from [order details] where productid = 23 475  476 -- 构造一个函数根据产品编号在订单详细信息表中统计总销售量 477 create function GetTotalSaleByPID(@Pid varchar(12)) 478 returns int 479 begin 480     return(select sum(quantity) from [order details] where productid = @Pid) 481 end 482  483  484 select * from products 485 -- 在产品表中查询，统计每一样产品的总销量 486 select productid, productname, dbo.GetTotalSaleByPID(productid) from products 487  488  489 --  490 CREATE FUNCTION LargeOrderShippers ( @FreightParm money ) 491 RETURNS @OrderShipperTab TABLE 492    ( 493     ShipperID     int, 494     ShipperName   nvarchar(80), 495     OrderID       int, 496     ShippedDate   datetime, 497     Freight       money 498    ) 499 AS 500 BEGIN 501    INSERT @OrderShipperTab 502         SELECT S.ShipperID, S.CompanyName, 503                O.OrderID, O.ShippedDate, O.Freight 504         FROM Shippers AS S INNER JOIN Orders AS O 505               ON S.ShipperID = O.ShipVia 506         WHERE O.Freight > @FreightParm 507    RETURN 508 END 509  510 SELECT * FROM LargeOrderShippers( $500 ) 511  512  513 -- 根据作者编号计算其所得版权费 514 create function fun_RoyalTyper ( @au_id id) 515 returns int 516 as 517 begin 518     declare @rt int 519     select @rt = sum(royaltyper) from titleauthor where au_id = @au_id 520     return (@rt) 521 end 522 go 523  524 select top 1 au_lname, au_fname, dbo.fun_RoyalTyper(au_id) as ‘版权费‘  525 from authors 526 order by  dbo.fun_RoyalTyper(au_id) desc 527 go 528  529 create function fun_MaxRoyalTyper_Au_id () 530 returns id 531 as 532 begin     533     declare @au_id id 534     select @au_id = au_id 535     from authors 536     order by  dbo.fun_RoyalTyper(au_id) 537     return(@au_id) 538 end 539 go 540  541 select dbo.fun_MaxRoyalTyper_Au_id() 542 go 543  544  545 select au_lname, au_fname, dbo.fun_RoyalTyper(au_id) as ‘版权税‘   546 from authors 547 where au_id = dbo.fun_MaxRoyalTyper_Au_id() 548 go 549  550 (5)高级查询 551  552  553  554 select title_id, price from titles 555  556 -- 查找最高价格 557 select max(price) from titles 558  559 -- 查找最贵书籍的价格(排序)，如果存在多本价格最贵的书，此方法将会遗漏 560 select top 1 title_id, price  561 from titles 562 order by price desc 563  564 -- 查找最贵书籍的价格(子查询) 565 select title_id, price  566 from titles 567 where price = (select max(price) from titles) 568  569 -- 查询指定出版社出版的书(连接) 570 select p.pub_name as ‘出版社‘, t.title as ‘书籍名称‘ 571 from publishers as p join titles as t on p.pub_id = t.pub_id 572 where pub_name = ‘New Moon Books‘ 573  574 -- 查询指定出版社出版的书(子查询) 575 select title  576 from titles  577 where pub_id = (select pub_id  578         from publishers  579         where pub_name =  ‘New Moon Books‘) 580  581 -- 查询指定出版社出版的书(分开查询) 582 select title from titles where pub_id = ‘0736‘ 583  584 select pub_id  585 from publishers  586 where pub_name =  ‘New Moon Books‘ 587  588  589 -- 重点 590 -- 理解相关子查询的基础 591 --  592 select * from titles where type = ‘business‘ 593 select * from titles where type = ‘business123‘ 594  595 select * from titles where 1 = 1  596  597 -- 在订单表中寻找满足以下条件的订单编号以及相应的客户编号 598 -- 在详细订单表中存在对应的订单编号并且其中包含产品编号为23的产品 599 -- 然后将产品编号为23的产品订购量返回判断是否大于20 600 USE northwind 601  602 SELECT orderid, customerid 603 FROM orders AS or1 604 WHERE 20 < (SELECT quantity FROM [order details] AS od 605              WHERE or1.orderid = od.orderid 606               AND  od.productid = 23) 607 GO 608  609 SELECT au_lname, au_fname  610 FROM authors  611 WHERE 100 IN  612     ( 613     SELECT royaltyper FROM titleauthor  614     WHERE titleauthor.au_ID = authors.au_id 615     )  616      617 select authors.au_lname,authors.au_fname 618 from authors join  titleauthor on titleauthor.au_ID=authors.au_id 619 where titleauthor.royaltyper =100  620  621 USE pubs 622  623 SELECT au_lname, au_fname 624 FROM authors 625 WHERE au_id IN 626    (SELECT au_id 627    FROM titleauthor 628    WHERE title_id IN 629       (SELECT title_id 630       FROM titles 631       WHERE type = ‘popular_comp‘)) 632  633  634  635 select distinct t.type, a.au_lname, a.au_fname 636 from authors as a join titleauthor as ta on a.au_id = ta.au_id 637             join titles as t on ta.title_id = t.title_id 638 where t.type = ‘business‘ 639  640 -- 查找类型为‘business‘或是‘trad_cook‘类型的书籍 641 select * from titles where type = ‘business‘ 642 select * from titles where type = ‘trad_cook‘ 643  644 -- 查找类型为‘business‘或是‘trad_cook‘类型的书籍(Or) 645 select * from titles  646 where type = ‘business‘ or type = ‘trad_cook‘ 647  648 -- 查找类型为‘business‘或是‘trad_cook‘类型的书籍(In) 649 select * from titles  650 where type in (‘business‘, ‘trad_cook‘) 651  652 -- 查找来自‘KS‘或是‘UT‘的作者 653 select au_lname, state from authors  654 where state = ‘KS‘ 655 select au_lname, state from authors  656 where state = ‘UT‘ 657  658 -- 查找来自‘KS‘或是‘UT‘的作者(Or) 659 select au_lname, state from authors  660 where state = ‘UT‘ or state = ‘KS‘ 661  662 -- 查找来自‘KS‘或是‘UT‘的作者(In) 663 select au_lname, state from authors  664 where state in (‘UT‘, ‘KS‘) 665  666 select au_lname, state from authors  667 where state not in (‘UT‘, ‘KS‘) 668  669  670 -- 查找出版了类型为‘business‘类型的书籍的出版社 671 SELECT pub_id FROM titles WHERE type = ‘business‘ 672  673 SELECT pub_id,pub_name 674 FROM publishers 675 WHERE pub_id IN (‘1389‘, ‘0736‘) 676  677  678 -- 查找出版了类型为‘business‘类型的书籍的出版社(In和子查询) 679 SELECT pub_id,pub_name 680 FROM publishers 681 WHERE pub_id IN 682    (SELECT pub_id 683    FROM titles 684    WHERE type = ‘business‘) 685  686  687  688 SELECT title, advance 689 FROM titles 690 WHERE advance >  691    ( 692     SELECT MAX(advance) 693     FROM publishers INNER JOIN titles ON  694       titles.pub_id = publishers.pub_id 695     WHERE pub_name = ‘Algodata Infosystems‘ 696    ) 697  698  699 SELECT title, advance 700 FROM titles 701 WHERE advance > all 702    ( 703     SELECT advance 704     FROM publishers INNER JOIN titles ON  705       titles.pub_id = publishers.pub_id 706     WHERE pub_name = ‘Algodata Infosystems‘ 707     and advance is not null 708    ) 709  710  711 declare @i int 712 set @i = 12 713 if @i < null 714     print ‘DDDDD‘ 715 else 716     print ‘XXXXX‘ 717  718  719  720  721  722  723  724 SELECT advance 725     FROM publishers INNER JOIN titles ON  726       titles.pub_id = publishers.pub_id 727     WHERE pub_name = ‘Algodata Infosystems‘ 728  729  730  731  732 select title_id, price from titles 733 where price > all 734 ( 735 select price from titles where type = ‘business‘ 736 ) 737  738 select title_id, price from titles 739 where price >  740 ( 741 select max(price) from titles where type = ‘business‘ 742 ) 743  744 select title_id, price from titles 745 where price > any 746 ( 747 select price from titles where type = ‘business‘ 748 ) 749  750 select title_id, price from titles 751 where price >  752 ( 753 select min(price) from titles where type = ‘business‘ 754 ) 755  756 select price from titles where type = ‘business‘ 757  758  759 if exists(select * from titles where type = ‘123‘) 760     print ‘ZZZZZ‘ 761 else     762     print ‘BBBBB‘ 763  764 if exists(select * from authors  765 where city = ‘Berkeley‘ and state =‘UT‘) 766     print ‘Welcome‘ 767 else 768     print ‘Bye-Bye‘ 769  770 -- 筛选出‘business‘以及‘trad_cook‘类型的书籍(联合查询) 771 select title_id, type from titles where type = ‘business‘ 772 union 773 select title_id, type from titles where type = ‘trad_cook‘ 774  775 -- 统计‘business‘类型的书籍的总价(联合查询) 776 select title, price from titles where type = ‘business‘ 777 union 778 select ‘合计:‘, sum(price) from titles where type = ‘business‘ 779  780 -- 统计所有书籍的类型剔除重复(Distinct) 781 select distinct type from titles 782  783 -- 作者记录的复制(Select Into) 784 select * into au from authors 785  786 select * from au 787  788 -- 查看数据表结构(Select Into并没有对数据表的约束进行复制) 789 sp_help authors 790 sp_help au 791  792  793 -- 分页(子查询的经典应用之一) 794  795 -- Jobs 职务信息表（pubs 数据库） 796 -- 在实际项目中，显示职务信息时，而职务信息量非常庞大，可能需要将其分为若干个页面来显示 797 -- 比如：每页显示4条记录，那么，第一页将显示1，2，3，4，第二页将显示5，6，7，8。。。。。 798  799 -- 显示所有信息 800 SELECT * FROM jobs 801 -- 显示前 4 信息 802 select top 4 * from jobs 803 -- 显示前 8 信息 804 select top 8 * from jobs 805 -- 显示前 12 信息 806 select top 12 * from jobs 807  808 -- 寻找规律，每一页的信息源于前（页面大小 * 页码）条信息的反序结果的前 页面大小 条记录 809 -- 比如：第二页就是前 8 条记录的反序结果的前 4 条 810 select top 4 *  811 from (select top 8 * from jobs) as tt 812 order by job_id desc 813  814 -- 当然，对于期望按升序显示查询结果的要求可以对查询结果进行再次排序 815 select * from 816 (select top 4 *  817 from (select top 8 * from jobs) as tt 818 order by job_id desc) as stt 819 order by job_id 820  821  822 -- SQL 命令中不支持在 select 的查询列表中直接使用局部变量 823 -- 比如：select top @PageSize * from jobs 824 -- 那么，可以考虑对sql命令进行拼装，然后，利用系统存储过程 sp_executesql 来执行 825 exec sp_executesql N‘Select * from jobs‘ 826  827 -- 存储过程的实现 828 -- 其中，@CurrentPageSize用于确定最后一页的页面大小 829 create proc proGetJobsByPage 830 @CurrentPageSize int, 831 @PageSize int, 832 @CurrentPage int 833 as 834 Declare @strSql nvarchar(400) 835 set @strSql = ‘select * from 836         (select top ‘ + convert(nvarchar(4), @CurrentPageSize) + ‘ *  837         from (select top ‘ + convert(nvarchar(4),(@PageSize * @CurrentPage)) + ‘ * from jobs) as tt 838         order by job_id desc) as stt 839         order by job_id‘ 840 exec sp_executesql @strSql 841 go 842  843 -- 测试 844 exec proGetJobsByPage 2, 4, 4 845  846  847  848 (6)存储过程 849  850  851 -- 扩展存储过程 852  853 -- 查询系统目录下文件信息 854 xp_cmdshell ‘dir *.*‘ 855  856 -- 启动Windows系统服务 857 xp_cmdshell ‘net start iisadmin‘ 858  859  860  861 (7)游标 862  863 -- 游标的五个基本操作步骤： 864  865 -- 声明 866 declare cur_titles cursor 867 for  868 select title, price from titles 869  870 -- 打开 871 open cur_titles 872  873 -- 提取 874 fetch cur_titles 875  876 fetch next from cur_titles 877  878 -- 关闭 879 close cur_titles 880  881 -- 释放 882 deallocate cur_titles 883  884  885  886  887 -- 利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书 888 -- 这一段为批处理版 889 -- 与批处理版相比，存储过程版更方便调试以及代码的重用 890  891 -- 声明 892 declare cur_titles cursor 893 for select title, price from titles 894  895 -- 打开 896 open cur_titles 897  898 declare @title varchar(80) 899 declare @price numeric(9,4) 900  901 declare @title_temp varchar(80) 902 declare @price_temp numeric(9,4) 903  904 -- 提取 905 fetch cur_titles into @title, @price 906  907 fetch cur_titles into @title_temp, @price_temp 908  909 while @@fetch_status = 0 910 begin 911     if @price < @price_temp 912     begin 913         set @price = @price_temp 914         set @title = @title_temp 915     end  916     fetch cur_titles into @title_temp, @price_temp 917 end 918  919 -- 关闭 920 close cur_titles 921  922 -- 释放 923 deallocate cur_titles 924  925 -- 显示处理结果 926 print ‘最贵的书是: ‘ + @title + ‘  ‘ + ‘价格是: ‘ + convert(varchar(12),@price) 927 go 928  929  930 -- 定义一个存储过程 931 -- 利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书（游标具体应用的经典） 932 -- 这段存储过程的实现代码相对下面的实现方式略有不同 933 -- 代码重复，但是思路更清晰 934 create procedure pro_GetMaxTitle 935 as 936     -- 声明 937     declare cur_titles cursor 938     for select title, price from titles 939      940     -- 打开 941     open cur_titles 942      943     -- 存储最贵的书籍信息 944     declare @title varchar(80) 945     declare @price numeric(9,4) 946     -- 存储从游标中提取出来的书籍的信息 947     declare @title_temp varchar(80) 948     declare @price_temp numeric(9,4) 949      950     -- 提取 951     fetch cur_titles into @title, @price 952     -- 判断是否存在书籍信息 953     if @@fetch_status <> 0 954     begin 955         print ‘没有书籍信息!‘ 956         -- 关闭 957         close cur_titles 958         -- 释放 959         deallocate cur_titles 960         -- 结束存储过程 961         return  962     end 963      964     fetch cur_titles into @title_temp, @price_temp 965      966     -- 判断是否只存在一本书 967     if @@fetch_status <> 0 968     begin 969         -- 显示处理结果 970         print ‘最贵的书是: ‘ + @title + ‘  ‘ + ‘价格是: ‘ + convert(varchar(12),@price) 971         -- 关闭 972         close cur_titles 973         -- 释放 974         deallocate cur_titles 975         -- 结束存储过程 976         return  977     end 978          979      980     while @@fetch_status = 0 981     begin 982         if @price < @price_temp 983         begin 984             set @price = @price_temp 985             set @title = @title_temp 986         end  987         fetch cur_titles into @title_temp, @price_temp 988     end 989      990     -- 显示处理结果 991     print ‘最贵的书是: ‘ + @title + ‘  ‘ + ‘价格是: ‘ + convert(varchar(12),@price) 992      993     -- 关闭 994     close cur_titles 995      996     -- 释放 997     deallocate cur_titles 998          999 go1000 1001 -- 定义一个存储过程1002 -- 利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书1003 -- 相对上面的实现方式，以下实现方式更简洁1004 create procedure pro_GetMaxTitle1005 as1006     -- 声明1007     declare cur_titles cursor1008     for select title, price from titles1009     1010     -- 打开1011     open cur_titles1012     1013     -- 存储最贵的书籍信息1014     declare @title varchar(80)1015     declare @price numeric(9,4)1016     -- 存储从游标中提取出来的书籍的信息1017     declare @title_temp varchar(80)1018     declare @price_temp numeric(9,4)1019     1020     -- 提取1021     fetch cur_titles into @title, @price1022     -- 判断是否存在书籍信息1023     if @@fetch_status = 01024     begin1025         print ‘没有书籍信息!‘1026         goto errNoTitles1027     end1028     1029     fetch cur_titles into @title_temp, @price_temp1030     -- 判断是否只存在一本书1031     if @@fetch_status = 01032     begin1033         goto errOnlyOne1034     end    1035         1036     while @@fetch_status = 01037     begin1038         if @price < @price_temp1039         begin1040             set @price = @price_temp1041             set @title = @title_temp1042         end 1043         fetch cur_titles into @title_temp, @price_temp1044     end1045     1046 errOnlyOne:1047     -- 显示处理结果1048     print ‘最贵的书是: ‘ + @title + ‘  ‘ + ‘价格是: ‘ + convert(varchar(12),@price)1049     1050 errNoTitles:1051     -- 关闭1052     close cur_titles1053     1054     -- 释放1055     deallocate cur_titles1056     1057     1058 go1059 1060 1061 1062 1063 -- 根据作者编号查看其相应的作品年销售量1064 -- 低于5000,提示: 销售量太低1065 -- 高于5000,提示: 销售量太高1066 create procedure pro_sales_avg (@au_id id)1067 as1068 if exists(select au_id from authors where au_id = @au_id)1069 begin1070     declare TempSales cursor1071     for 1072     select title, ytd_sales 1073     from titleauthor ta join titles t1074         on ta.title_id = t.title_id1075     where au_id = @au_id1076     1077     open TempSales    1078     1079     declare @t varchar(80)1080     declare @y int1081     1082     fetch TempSales1083     into @t, @y1084 1085     while @@fetch_status = 01086     begin1087         if 5000 > @y1088             print @t + ‘ ‘ + convert(varchar(5),@y) + ‘ 销售量太低‘1089         else1090             print @t + ‘ ‘ + convert(varchar(5),@y) + ‘ 销售量太高‘1091         fetch TempSales1092         into @t, @y1093     end1094     1095     close TempSales1096     deallocate TempSales1097 end1098 else1099     print ‘作者编号无效！‘1100 go1101 1102 exec pro_sales_avg ‘213-46-8915‘1103 1104 1105 1106 /*1107 示例1108 A. 使用简单游标和语法1109 打开该游标时所生成的结果集包括 pubs 数据库的 authors 表中的所有行和列。可以更新该游标，对该游标所做的所有更新和删除均在提取中表现出来。因为没指定 SCROLL 选项，FETCH NEXT 是唯一可用的提取选项。1110 */1111 1112 DECLARE authors_cursor CURSOR1113 FOR 1114 SELECT * FROM authors1115 1116 OPEN authors_cursor1117 1118 FETCH NEXT FROM authors_cursor1119 1120 /*1121 B. 使用嵌套游标生成报表输出1122 下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。1123 */1124 1125 SET NOCOUNT ON1126 1127 DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),1128    @message varchar(80), @title varchar(80)1129 1130 PRINT "-------- Utah Authors report --------"1131 1132 DECLARE authors_cursor CURSOR 1133 FOR 1134 SELECT au_id, au_fname, au_lname1135 FROM authors1136 WHERE state = "UT"1137 ORDER BY au_id1138 1139 OPEN authors_cursor1140 1141 FETCH NEXT FROM authors_cursor 1142 INTO @au_id, @au_fname, @au_lname1143 1144 WHILE @@FETCH_STATUS = 01145 BEGIN1146    PRINT " "1147    SELECT @message = "----- Books by Author: " + 1148       @au_fname + " " + @au_lname1149 1150    PRINT @message1151 1152    -- Declare an inner cursor based   1153    -- on au_id from the outer cursor.1154 1155    DECLARE titles_cursor CURSOR FOR 1156    SELECT t.title1157    FROM titleauthor ta, titles t1158    WHERE ta.title_id = t.title_id AND1159    ta.au_id = @au_id   -- Variable value from the outer cursor1160 1161    OPEN titles_cursor1162    FETCH NEXT FROM titles_cursor INTO @title1163 1164    IF @@FETCH_STATUS <> 0 1165       PRINT "         <<No Books>>"     1166 1167    WHILE @@FETCH_STATUS = 01168    BEGIN1169       1170       SELECT @message = "         " + @title1171       PRINT @message1172       FETCH NEXT FROM titles_cursor INTO @title1173    1174    END1175 1176    CLOSE titles_cursor1177    DEALLOCATE titles_cursor1178    1179    -- Get the next author.1180    FETCH NEXT FROM authors_cursor 1181    INTO @au_id, @au_fname, @au_lname1182 END1183 1184 CLOSE authors_cursor1185 DEALLOCATE authors_cursor1186 GO1187 1188 -------- Utah Authors report --------1189  1190 ----- Books by Author: Anne Ringer1191          The Gourmet Microwave1192          Is Anger the Enemy?1193  1194 ----- Books by Author: Albert Ringer1195          Is Anger the Enemy?1196          Life Without Fear1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 (8)触发器1208 1209 1210 1211 1212 -- 设定数据库的递归触发器1213 alter database pubs1214     set recursive_triggers on1215 go1216 1217 -- 创建数据表，并设定主键、外键以及缺省约束1218 create table emp_mgr1219 (1220 Emp char(30) primary key,1221 Mgr char(30) null foreign key references emp_mgr(Emp),1222 NoOfReports int default 01223 )1224 go1225 1226 -- 创建插入触发器1227 create trigger emp_marins1228 on emp_mgr1229 for insert1230 as1231 declare @e char(30),@m char(30)1232 declare cur_mgr cursor for 1233     select emp_mgr.emp1234     from emp_mgr,inserted1235     where emp_mgr.emp = inserted.mgr1236 1237 open  cur_mgr1238 1239 fetch next from cur_mgr into @e1240 1241 while @@fetch_status = 01242 begin1243     update emp_mgr1244     set emp_mgr.NoOfReports = emp_mgr.NoOfReports + 11245     where emp_mgr.emp = @e1246     1247     fetch next from cur_mgr into @e1248 end 1249 1250 close cur_mgr1251 1252 deallocate cur_mgr1253 1254 go1255 1256 -- 查看数据表相关触发器1257 sp_helptrigger emp_mgr1258 go1259 1260 1261 create trigger emp_mgrupd1262 on emp_mgr1263 for update1264 as1265 if update (mgr)1266 begin1267     update emp_mgr1268     set emp_mgr.NoOfReports = emp_mgr.NoofReports + 11269     from inserted1270     where emp_mgr.emp = inserted.mgr1271     1272     update emp_mgr1273     set emp_mgr.NoOfReports = emp_mgr.NoOfReports -11274     from deleted1275     where emp_mgr.emp = deleted.mgr1276 1277 end1278 1279 go1280 1281 1282 insert emp_mgr(emp,mgr) values (‘Harry‘,null)1283 insert emp_mgr(emp,mgr) values (‘Alice‘,‘Harry‘)1284 insert emp_mgr(emp,mgr) values (‘Paul‘,‘Alice‘)1285 insert emp_mgr(emp,mgr) values (‘Joe‘,‘Alice‘)1286 insert emp_mgr(emp,mgr) values (‘Dave‘,‘Joe‘)1287 go1288 1289 select * from emp_mgr1290 go1291 1292 update emp_mgr 1293 set mgr = ‘Harry‘1294 where emp = ‘Dave‘1295 go1296 1297 select * from emp_mgr1298 go1299 1300 1301 1302 -- “进销存”系统（触发器的经典应用之一）1303 1304 某“进销存”系统需要记录进货的信息以及出货的信息，并且当用户记录这些信息的同时，库存信息也需要进行相应的调整，1305 比如：记录进货信息时，如果该货品是新货，在库存表中还不存在任何信息时，则需要添加一条库存信息（Insert），1306 否则，只需要对相应的库存记录进行更新（Update）；然而，在记录出货信息时，如果该货品在库存表中的库存量小于出货量时，1307 则需抛出一个用户自定义的“应用错误”（raise_appliction_error），否则，只需要对相应的库存记录进行更新（Update）。1308 那么，我们如何来作到数据库系统的自动完成。1309 1310 create table 进货1311 (1312 货号    char(1) not null,1313 数量    int not null,1314 时间    smalldatetime1315 )1316 1317 create table 库存1318 (1319 货号    char(1) not null,1320 数量    int not null1321 )1322 1323 1324 create table 出货1325 (1326 货号    char(1) not null,1327 数量    int not null,1328 时间    smalldatetime1329 )1330 1331 drop table 库存1332 drop table 进货1333 drop table 出货1334 1335 select * from 进货1336 select * from 库存1337 select * from 出货1338 1339 1340 create proc pro进货1341 (1342 @h char(1),1343 @s int,1344 @j smalldatetime1345 )1346 as1347 insert into 进货 values (@h, @s, @j)1348 1349 if exists(select * from 库存 where 货号 = @h)1350     update 库存 set 数量 = 数量 + @s where  货号 = @h1351 else1352     insert into 库存 values (@h, @s)1353 go1354 1355 execute pro进货 ‘A‘, 120, ‘2005-12-28‘1356 execute pro进货 ‘A‘, 180, ‘2005-12-29‘1357 1358 1359 create proc pro出货1360 (1361 @h char(1),1362 @s int,1363 @j smalldatetime1364 )1365 as1366 if exists(select * from 库存 where 货号 = @h)1367 begin1368     if exists(select 数量 from 库存 where 货号 = @h and 数量 >= @s)1369     begin1370         update 库存 set 数量 = 数量 - @s where  货号 = @h1371         insert into 出货 values (@h, @s, @j)1372     end1373     else1374         print ‘No,you need too more!‘1375 end1376 else1377     print ‘No, no you need Dongxi!‘ 1378 go1379 1380 execute pro出货 ‘D‘, 120, ‘2005-12-28‘1381 execute pro出货 ‘A‘, 680, ‘2005-12-29‘1382 execute pro出货 ‘A‘, 80, ‘2005-12-29‘1383 1384 create proc pro进货1385 (1386 @h char(1),1387 @s int,1388 @j smalldatetime1389 )1390 as1391 insert into 进货 values (@h, @s, @j)1392 go1393 1394 create trigger tri进货1395 on 进货1396 after insert1397 as1398 declare @h char(1)1399 declare @s int1400 select @h = 货号, @s = 数量 from inserted1401 1402 if exists(select * from 库存 where 货号 = @h)1403     update 库存 set 数量 = 数量 + @s where 货号 = @h1404 else1405     insert into 库存 values (@h, @s)1406 go1407 1408 1409 1410 1411 create proc pro销售1412 (1413 @h char(1),1414 @s int,1415 @j smalldatetime1416 )1417 as1418 if exists(select * from 库存 where 货号 = @h)1419     insert into 销售 values (@h, @s, @j)1420 go1421 1422 1423 create trigger tri销售1424 on 销售1425 after insert1426 as1427 if (select 数量 from inserted) > (select 数量 from 库存 where 货号 = (select 货号 from inserted))1428 begin1429     print ‘Nononono!‘1430     rollback 1431 end1432 else1433     update 库存 set 数量 = 数量 - (select 数量 from inserted) where 货号 in (select 货号 from inserted)    1434 go1435 1436 1437 1438 1439 1440 1441 -- 部门管理（触发器的递归实现的经典应用之一）1442 use pubs1443 go1444 1445 drop table departments1446 go1447 1448 -- 定义数据表1449 create table Departments1450 (1451 dep_id        int        not null    identity(1,1)    primary key,1452 dep_name    varchar(18)    not null    unique,1453 dep_parent    int        not null,1454 dep_description    varchar(42)    null1455 )1456 go1457 1458 -- 添加基本数据1459 insert into departments1460 (dep_name, dep_parent, dep_description)1461 values1462 (‘上海‘, 0, ‘省级代理‘)1463 go1464 1465 insert into departments1466 (dep_name, dep_parent, dep_description)1467 values1468 (‘北京‘, 0, ‘省级代理‘)1469 go1470 1471 insert into departments1472 (dep_name, dep_parent, dep_description)1473 values1474 (‘湖南‘, 0, ‘省级代理‘)1475 go1476 1477 insert into departments1478 (dep_name, dep_parent, dep_description)1479 values1480 (‘长沙‘, 3, ‘市级代理‘)1481 go1482 1483 insert into departments1484 (dep_name, dep_parent, dep_description)1485 values1486 (‘株洲‘, 3, ‘市级代理‘)1487 go1488 1489 insert into departments1490 (dep_name, dep_parent,dep_description)1491 values1492 (‘湘潭‘, 3, ‘市级代理‘)1493 go1494 1495 insert into departments1496 (dep_name, dep_parent, dep_description)1497 values1498 (‘石峰‘, 5, ‘区级代理‘)1499 go1500 1501 insert into departments1502 (dep_name, dep_parent, dep_description)1503 values1504 (‘天元‘, 5, ‘区级代理‘)1505 go1506 1507 insert into departments1508 (dep_name, dep_parent,dep_description)1509 values1510 (‘芦淞‘, 5, ‘区级代理‘)1511 go1512 1513 select * from departments1514 go1515 1516 1517 -- 定义添加部门的存储过程1518 create procedure proc_InsertDepartment1519 @dep_parent_id    int,1520 @dep_name    varchar(20),1521 @dep_id        int output1522 as1523 insert into departments (dep_name, dep_parent,dep_description) values (@dep_name, @dep_parent_id, ‘代理‘)1524 select @dep_id =  @@IDENTITY1525 go1526 1527 -- 定义修改指定部门的名称的存储过程1528 create procedure proc_UpdateDepartment1529 @dep_id        int,1530 @dep_name    varchar(20)1531 as1532 update departments set dep_name = @dep_name where dep_id = @dep_id1533 go1534 1535 -- 定义删除指定部门以及相应子部门的存储过程1536 create procedure proc_DeleteDepartment1537 @dep_id        int1538 as1539 delete from departments where dep_id = @dep_id1540 go1541 1542 -- 定义删除触发器(递归)1543 create trigger tri_del_dep1544 on departments1545 for delete1546 as1547 -- 获取被删除部门的编号1548 -- 检查是否存在子部门1549 if exists(select * from departments where dep_parent in (select dep_id from deleted ))1550     begin1551         -- 如果返回值为真，删除相应子部门，激活子部门的删除触发器，进行递归调用1552         delete from departments where dep_parent in (select dep_id from deleted )1553     end1554 go1555 1556 -- 启动直接递归触发器1557 exec sp_dboption ‘pubs‘,‘recursive triggers‘,‘true‘1558 1559 -- 启动间接递归触发器1560 exec sp_configure ‘nested triggers‘,‘1‘ 1561 1562 1563 1564 (9)链接服务1565 -- 在SQL Server上创建一个链接服务：与其他数据库建立连接，将其数据表或是视图作为本地信息源访问1566 -- 比如：将Access数据库Northwind作为一个链接服务源1567 EXEC sp_addlinkedserver 1568    @server = ‘SEATTLE Mktg‘, 1569    @provider = ‘Microsoft.Jet.OLEDB.4.0‘, 1570    @srvproduct = ‘OLE DB Provider for Jet‘,1571    @datasrc = ‘C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb‘1572 1573 1574 SELECT *1575 into #au41576 FROM OPENQUERY([SEATTLE Mktg], ‘SELECT * FROM 产品‘) 1577 GO1578 1579 select * 1580 from #au4