SQL基础用法(实例二)

   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 go
1000 
1001 -- 定义一个存储过程
1002 -- 利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书
1003 -- 相对上面的实现方式，以下实现方式更简洁
1004 create procedure pro_GetMaxTitle
1005 as
1006     -- 声明
1007     declare cur_titles cursor
1008     for select title, price from titles
1009     
1010     -- 打开
1011     open cur_titles
1012     
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, @price
1022     -- 判断是否存在书籍信息
1023     if @@fetch_status = 0
1024     begin
1025         print '没有书籍信息!'
1026         goto errNoTitles
1027     end
1028     
1029     fetch cur_titles into @title_temp, @price_temp
1030     -- 判断是否只存在一本书
1031     if @@fetch_status = 0
1032     begin
1033         goto errOnlyOne
1034     end    
1035         
1036     while @@fetch_status = 0
1037     begin
1038         if @price < @price_temp
1039         begin
1040             set @price = @price_temp
1041             set @title = @title_temp
1042         end 
1043         fetch cur_titles into @title_temp, @price_temp
1044     end
1045     
1046 errOnlyOne:
1047     -- 显示处理结果
1048     print '最贵的书是: ' + @title + '  ' + '价格是: ' + convert(varchar(12),@price)
1049     
1050 errNoTitles:
1051     -- 关闭
1052     close cur_titles
1053     
1054     -- 释放
1055     deallocate cur_titles
1056     
1057     
1058 go
1059 
1060 
1061 
1062 
1063 -- 根据作者编号查看其相应的作品年销售量
1064 -- 低于5000,提示: 销售量太低
1065 -- 高于5000,提示: 销售量太高
1066 create procedure pro_sales_avg (@au_id id)
1067 as
1068 if exists(select au_id from authors where au_id = @au_id)
1069 begin
1070     declare TempSales cursor
1071     for 
1072     select title, ytd_sales 
1073     from titleauthor ta join titles t
1074         on ta.title_id = t.title_id
1075     where au_id = @au_id
1076     
1077     open TempSales    
1078     
1079     declare @t varchar(80)
1080     declare @y int
1081     
1082     fetch TempSales
1083     into @t, @y
1084 
1085     while @@fetch_status = 0
1086     begin
1087         if 5000 > @y
1088             print @t + ' ' + convert(varchar(5),@y) + ' 销售量太低'
1089         else
1090             print @t + ' ' + convert(varchar(5),@y) + ' 销售量太高'
1091         fetch TempSales
1092         into @t, @y
1093     end
1094     
1095     close TempSales
1096     deallocate TempSales
1097 end
1098 else
1099     print '作者编号无效！'
1100 go
1101 
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 CURSOR
1113 FOR 
1114 SELECT * FROM authors
1115 
1116 OPEN authors_cursor
1117 
1118 FETCH NEXT FROM authors_cursor
1119 
1120 /*
1121 B. 使用嵌套游标生成报表输出
1122 下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。
1123 */
1124 
1125 SET NOCOUNT ON
1126 
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_lname
1135 FROM authors
1136 WHERE state = "UT"
1137 ORDER BY au_id
1138 
1139 OPEN authors_cursor
1140 
1141 FETCH NEXT FROM authors_cursor 
1142 INTO @au_id, @au_fname, @au_lname
1143 
1144 WHILE @@FETCH_STATUS = 0
1145 BEGIN
1146    PRINT " "
1147    SELECT @message = "----- Books by Author: " + 
1148       @au_fname + " " + @au_lname
1149 
1150    PRINT @message
1151 
1152    -- Declare an inner cursor based   
1153    -- on au_id from the outer cursor.
1154 
1155    DECLARE titles_cursor CURSOR FOR 
1156    SELECT t.title
1157    FROM titleauthor ta, titles t
1158    WHERE ta.title_id = t.title_id AND
1159    ta.au_id = @au_id   -- Variable value from the outer cursor
1160 
1161    OPEN titles_cursor
1162    FETCH NEXT FROM titles_cursor INTO @title
1163 
1164    IF @@FETCH_STATUS <> 0 
1165       PRINT "         <<No Books>>"     
1166 
1167    WHILE @@FETCH_STATUS = 0
1168    BEGIN
1169       
1170       SELECT @message = "         " + @title
1171       PRINT @message
1172       FETCH NEXT FROM titles_cursor INTO @title
1173    
1174    END
1175 
1176    CLOSE titles_cursor
1177    DEALLOCATE titles_cursor
1178    
1179    -- Get the next author.
1180    FETCH NEXT FROM authors_cursor 
1181    INTO @au_id, @au_fname, @au_lname
1182 END
1183 
1184 CLOSE authors_cursor
1185 DEALLOCATE authors_cursor
1186 GO
1187 
1188 -------- Utah Authors report --------
1189  
1190 ----- Books by Author: Anne Ringer
1191          The Gourmet Microwave
1192          Is Anger the Enemy?
1193  
1194 ----- Books by Author: Albert Ringer
1195          Is Anger the Enemy?
1196          Life Without Fear
1197 
1198 
1199 
1200 
1201 
1202 
1203 
1204 
1205 
1206 
1207 (8)触发器
1208 
1209 
1210 
1211 
1212 -- 设定数据库的递归触发器
1213 alter database pubs
1214     set recursive_triggers on
1215 go
1216 
1217 -- 创建数据表，并设定主键、外键以及缺省约束
1218 create table emp_mgr
1219 (
1220 Emp char(30) primary key,
1221 Mgr char(30) null foreign key references emp_mgr(Emp),
1222 NoOfReports int default 0
1223 )
1224 go
1225 
1226 -- 创建插入触发器
1227 create trigger emp_marins
1228 on emp_mgr
1229 for insert
1230 as
1231 declare @e char(30),@m char(30)
1232 declare cur_mgr cursor for 
1233     select emp_mgr.emp
1234     from emp_mgr,inserted
1235     where emp_mgr.emp = inserted.mgr
1236 
1237 open  cur_mgr
1238 
1239 fetch next from cur_mgr into @e
1240 
1241 while @@fetch_status = 0
1242 begin
1243     update emp_mgr
1244     set emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1
1245     where emp_mgr.emp = @e
1246     
1247     fetch next from cur_mgr into @e
1248 end 
1249 
1250 close cur_mgr
1251 
1252 deallocate cur_mgr
1253 
1254 go
1255 
1256 -- 查看数据表相关触发器
1257 sp_helptrigger emp_mgr
1258 go
1259 
1260 
1261 create trigger emp_mgrupd
1262 on emp_mgr
1263 for update
1264 as
1265 if update (mgr)
1266 begin
1267     update emp_mgr
1268     set emp_mgr.NoOfReports = emp_mgr.NoofReports + 1
1269     from inserted
1270     where emp_mgr.emp = inserted.mgr
1271     
1272     update emp_mgr
1273     set emp_mgr.NoOfReports = emp_mgr.NoOfReports -1
1274     from deleted
1275     where emp_mgr.emp = deleted.mgr
1276 
1277 end
1278 
1279 go
1280 
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 go
1288 
1289 select * from emp_mgr
1290 go
1291 
1292 update emp_mgr 
1293 set mgr = 'Harry'
1294 where emp = 'Dave'
1295 go
1296 
1297 select * from emp_mgr
1298 go
1299 
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 时间    smalldatetime
1315 )
1316 
1317 create table 库存
1318 (
1319 货号    char(1) not null,
1320 数量    int not null
1321 )
1322 
1323 
1324 create table 出货
1325 (
1326 货号    char(1) not null,
1327 数量    int not null,
1328 时间    smalldatetime
1329 )
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 smalldatetime
1345 )
1346 as
1347 insert into 进货 values (@h, @s, @j)
1348 
1349 if exists(select * from 库存 where 货号 = @h)
1350     update 库存 set 数量 = 数量 + @s where  货号 = @h
1351 else
1352     insert into 库存 values (@h, @s)
1353 go
1354 
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 smalldatetime
1364 )
1365 as
1366 if exists(select * from 库存 where 货号 = @h)
1367 begin
1368     if exists(select 数量 from 库存 where 货号 = @h and 数量 >= @s)
1369     begin
1370         update 库存 set 数量 = 数量 - @s where  货号 = @h
1371         insert into 出货 values (@h, @s, @j)
1372     end
1373     else
1374         print 'No,you need too more!'
1375 end
1376 else
1377     print 'No, no you need Dongxi!' 
1378 go
1379 
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 smalldatetime
1389 )
1390 as
1391 insert into 进货 values (@h, @s, @j)
1392 go
1393 
1394 create trigger tri进货
1395 on 进货
1396 after insert
1397 as
1398 declare @h char(1)
1399 declare @s int
1400 select @h = 货号, @s = 数量 from inserted
1401 
1402 if exists(select * from 库存 where 货号 = @h)
1403     update 库存 set 数量 = 数量 + @s where 货号 = @h
1404 else
1405     insert into 库存 values (@h, @s)
1406 go
1407 
1408 
1409 
1410 
1411 create proc pro销售
1412 (
1413 @h char(1),
1414 @s int,
1415 @j smalldatetime
1416 )
1417 as
1418 if exists(select * from 库存 where 货号 = @h)
1419     insert into 销售 values (@h, @s, @j)
1420 go
1421 
1422 
1423 create trigger tri销售
1424 on 销售
1425 after insert
1426 as
1427 if (select 数量 from inserted) > (select 数量 from 库存 where 货号 = (select 货号 from inserted))
1428 begin
1429     print 'Nononono!'
1430     rollback 
1431 end
1432 else
1433     update 库存 set 数量 = 数量 - (select 数量 from inserted) where 货号 in (select 货号 from inserted)    
1434 go
1435 
1436 
1437 
1438 
1439 
1440 
1441 -- 部门管理（触发器的递归实现的经典应用之一）
1442 use pubs
1443 go
1444 
1445 drop table departments
1446 go
1447 
1448 -- 定义数据表
1449 create table Departments
1450 (
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)    null
1455 )
1456 go
1457 
1458 -- 添加基本数据
1459 insert into departments
1460 (dep_name, dep_parent, dep_description)
1461 values
1462 ('上海', 0, '省级代理')
1463 go
1464 
1465 insert into departments
1466 (dep_name, dep_parent, dep_description)
1467 values
1468 ('北京', 0, '省级代理')
1469 go
1470 
1471 insert into departments
1472 (dep_name, dep_parent, dep_description)
1473 values
1474 ('湖南', 0, '省级代理')
1475 go
1476 
1477 insert into departments
1478 (dep_name, dep_parent, dep_description)
1479 values
1480 ('长沙', 3, '市级代理')
1481 go
1482 
1483 insert into departments
1484 (dep_name, dep_parent, dep_description)
1485 values
1486 ('株洲', 3, '市级代理')
1487 go
1488 
1489 insert into departments
1490 (dep_name, dep_parent,dep_description)
1491 values
1492 ('湘潭', 3, '市级代理')
1493 go
1494 
1495 insert into departments
1496 (dep_name, dep_parent, dep_description)
1497 values
1498 ('石峰', 5, '区级代理')
1499 go
1500 
1501 insert into departments
1502 (dep_name, dep_parent, dep_description)
1503 values
1504 ('天元', 5, '区级代理')
1505 go
1506 
1507 insert into departments
1508 (dep_name, dep_parent,dep_description)
1509 values
1510 ('芦淞', 5, '区级代理')
1511 go
1512 
1513 select * from departments
1514 go
1515 
1516 
1517 -- 定义添加部门的存储过程
1518 create procedure proc_InsertDepartment
1519 @dep_parent_id    int,
1520 @dep_name    varchar(20),
1521 @dep_id        int output
1522 as
1523 insert into departments (dep_name, dep_parent,dep_description) values (@dep_name, @dep_parent_id, '代理')
1524 select @dep_id =  @@IDENTITY
1525 go
1526 
1527 -- 定义修改指定部门的名称的存储过程
1528 create procedure proc_UpdateDepartment
1529 @dep_id        int,
1530 @dep_name    varchar(20)
1531 as
1532 update departments set dep_name = @dep_name where dep_id = @dep_id
1533 go
1534 
1535 -- 定义删除指定部门以及相应子部门的存储过程
1536 create procedure proc_DeleteDepartment
1537 @dep_id        int
1538 as
1539 delete from departments where dep_id = @dep_id
1540 go
1541 
1542 -- 定义删除触发器(递归)
1543 create trigger tri_del_dep
1544 on departments
1545 for delete
1546 as
1547 -- 获取被删除部门的编号
1548 -- 检查是否存在子部门
1549 if exists(select * from departments where dep_parent in (select dep_id from deleted ))
1550     begin
1551         -- 如果返回值为真，删除相应子部门，激活子部门的删除触发器，进行递归调用
1552         delete from departments where dep_parent in (select dep_id from deleted )
1553     end
1554 go
1555 
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 #au4
1576 FROM OPENQUERY([SEATTLE Mktg], 'SELECT * FROM 产品') 
1577 GO
1578 
1579 select * 
1580 from #au4