SQL語言查閱手冊

1、(1)批處理2(2)變量3(3)邏輯控制5（4）函數(shù)7(4.1)系統(tǒng)函數(shù)7（4.2）自定義函數(shù)13(5)高級查詢23(6)存儲過程35(7)游標36(8)觸發(fā)器50sql server 數(shù)據(jù)庫的高級操作(1) 批處理(2) 變量(3) 邏輯控制(4) 函數(shù)(5) 高級查詢*/(1)批處理將多條sql語句作為一個整體去編譯，生成一個執(zhí)行計劃，然后，執(zhí)行！理解批處理的關鍵在于"編譯"，對于由多條語句組成的一個批處理，如果在編譯時，其中，有一條出現(xiàn)語法錯誤，將會導致編譯失??！create table t(a int,b int)- 注釋- 如果多行注釋中包含了批處理的標識符go-

2、 在編譯的過程中代碼將會被go分割成多個部分來分批編譯- 多行注釋的標記將會被分隔而導致編譯出錯- 以下幾條語句是三個非常經(jīng)典的批處理- 你猜一下會添加幾條記錄！/*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中全局變量由系統(tǒng)定義、系統(tǒng)維護，用戶一般僅可對其進行讀??！- 查看sql server版本print version- 服務器

3、名稱print servername- 系統(tǒng)錯誤編號insert into t values ('a','a')print errorinsert into t values ('a','a')if error = 245 print 'error'- sql server 版本的語言信息print language- 一周的第一天從星期幾算起print datefirst- cpu 執(zhí)行命令所耗費時間的累加print cpu_busy- 獲取最近添加的標識列的值create table tt(a int

4、 identity(3, 10),b int)insert into tt (b) values (1)print identityselect * from tt- 局部變量局部變量由用戶定義，僅可在同一個批處理中調(diào)用和訪問declare intage tinyintset intage = 12print intagedeclare strname varchar(12)select strname = 'state'print strnameselect au_lname, strname from authors(3)邏輯控制- if條件判斷declare i ints

5、et i = 12if (i > 10) begin    -   print 'dadadada!'  print 'dadadada!' end    - else begin  print 'xiaoxiao!'  print 'xiaoxiao!' end- while循環(huán)控制declare i int;set i =

6、12;print ireturn;while (i < 18)begin print i; set i = i + 1; if i < 17  continue; if i > 15  break;end;- case 分支判斷select au_lname, state, '猶他州' from authors where state = 'ut'select au_lname, state, '密西西比州' from authors where s

7、tate = 'mi'select au_lname, state, '肯塔基州' from authors where state = 'ks'select au_lname, state,  case state when 'ut' then '猶他州' when 'mi' then '密西西比州' when 'ks' then '肯塔基州' when 'ca' then '

8、加利福利亞' else state endfrom authors（4）函數(shù)(4.1)系統(tǒng)函數(shù)- 獲取指定字符串中左起第一個字符的asc碼print ascii('abcdef')- 根據(jù)給定的asc碼獲取相應的字符print char(65)- 獲取給定字符串的長度print len('abcdef')- 大小寫轉(zhuǎn)換print lower('abcdef')print upper('abcdef')- 去空格print ltrim('    abcd 

9、dfd  df  ')print rtrim('    abcd  dfd  df  ')- 求絕對值print abs(-12)- 冪- 3 的 2 次方print power(3,2)print power(3,3)- 隨機數(shù)- 0 - 1000 之間的隨機數(shù)print rand() * 1000 - 獲取圓周率print pi()- 獲取系統(tǒng)時間print getdate()- 獲取3天前的時間print dateadd(day, -3 , getdate()- 獲取3天后的時間pri

10、nt 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-0

11、1', '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

12、' + 456- 類型轉(zhuǎn)換print 'abc' + convert(varchar(10), 456)select title_id, type, price from titles- 字符串連接必須保證類型一致(以下語句執(zhí)行將會出錯)- 類型轉(zhuǎn)換select title_id + type + price from titles- 正確select title_id + type + convert(varchar(10), price) from titlesprint '123' + convert(varchar(3), 123)print &

13、#39;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(m

14、i, getdate()print datepart(ss, getdate()print datepart(ms, getdate()- 獲取指定時間的間隔部分- 返回跨兩個指定日期的日期和時間邊界數(shù)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 d

15、atediff(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

16、, 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)- 利用系統(tǒng)函數(shù)作為默認值約束drop table tttcreate table ttt(stu_name varchar(12),stu_birthday datetime default (getdate()a

17、lter table tttadd constraint df_ttt_stu_birthday default  (getdate() for stu_birthdayinsert 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）自定義函數(shù)select t

18、itle_idfrom titles where type = 'business'select stuff(title_id,1,3,'abb'), type from titles where type = 'business'select count(title_id) from titles where type = 'business'select title_id from titles where type = 'business'select  *,count(dbo.titleautho

19、r.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 '作品數(shù)量'from dbo.authors  left outer join     

20、 dbo.titleauthor on dbo.authors.au_id = dbo.titleauthor.au_idgroup by dbo.authors.au_idorder by '作品數(shù)量'- 自定義函數(shù)的引子（通過這個子查詢來引入函數(shù)的作用）- 子查詢- 統(tǒng)計每個作者的作品數(shù)- 將父查詢中的作者編號傳入子查詢- 作為查詢條件利用聚合函數(shù)count統(tǒng)計其作品數(shù)量select au_lname,   (select count(title_id)  from titleauthor as ta  where ta.au_id

21、= a.au_id ) as titlecountfrom authors as aorder by titlecount - 是否可以定義一個函數(shù)- 將作者編號作為參數(shù)統(tǒng)計其作品數(shù)量并將其返回select au_id, au_lname, dbo.gettitlecountbyauid(au_id) as titlecount from authorsorder by titlecount- 根據(jù)給定的作者編號獲取其相應的作品數(shù)量create function gettitlecountbyauid(au_id varchar(12)returns intbegin

22、60;return (select count(title_id)   from titleauthor  where au_id = au_id)end - 利用函數(shù)來顯示每個作者的作品數(shù)量create proc pro_caltitlecountasselect au_id, au_lname, dbo.gettitlecountbyauid(au_id) as titlecount from authorsorder by titlecountgo- 執(zhí)行存儲過程execute pro_caltitlecount- vb中函數(shù)定義格式functio

23、n gettitlecountbyauid(au_id as string) as integer  . gettitlecountbyauid = ?end function- sales 作品銷售信息select * from sales- 根據(jù)書籍編號查詢其銷售記錄（其中，qty 表示銷量）select * from sales where title_id = 'bu1032'- 根據(jù)書籍編號統(tǒng)計其總銷售量（其中，qty 表示銷量）select sum(qty) from sales where title_id = 'bu1032

24、'- 利用分組語句（group by），根據(jù)書籍編號統(tǒng)計每本書總銷售量（其中，qty 表示銷量）select title_id, sum(qty) from sales group by title_id- 是否可以考慮定義一個函數(shù)根據(jù)書籍編號來計算其總銷售量- 然后，將其應用到任何一條包含了書籍編號的查詢語句中select title_id, title, dbo.gettotalsalebytitleid(title_id) as totalsalesfrom titlesorder by totalsales- 定義一個函數(shù)根據(jù)書籍編號來計算其總銷售量create functio

25、n gettotalsalebytitleid(tid varchar(24)returns intbegin return(select sum(qty) from sales where title_id = tid)end- 統(tǒng)計書籍銷量的前10位- 其中，可以利用函數(shù)計算結(jié)果的別名作為排序子句的參照列select top 10 title_id, title, dbo.gettotalsalebytitleid(title_id) as totalsalesfrom titlesorder by totalsales desc- 根據(jù)書籍編號計算其銷量排名create fun

26、ction gettherankoftitle(id varchar(20)returns intbegin return(select count(totalsales)   from titles   where toalsales >(  select totalsales   from titles   where title_id=id)end- 根據(jù)書籍編號計算其銷量排名select dbo.gettherankoftitle('pc1035') fr

27、om titlesselect count(title_id) + 1from titles where dbo.gettotalsalebytitleid(title_id) > dbo.gettotalsalebytitleid('pc1035')- 刪除函數(shù)drop function getrankbytitleid- 根據(jù)書籍編號計算其銷量排名create function getrankbytitleid(tid varchar(24)returns intbegin return (select count(title_id) + 1 &#

28、160;from titles   where dbo.gettotalsalebytitleid(title_id) > dbo.gettotalsalebytitleid(tid)end- 在查詢語句中利用函數(shù)統(tǒng)計每本書的總銷量和總排名select title_id, title, dbo.gettotalsalebytitleid(title_id) as totalsales, dbo.getrankbytitleid(title_id) as totalrankfrom titlesorder by totalsales desc- 查看

29、表結(jié)構(gòu)sp_help titles- 查看存儲過程的定義內(nèi)容sp_helptext getrankbytitleidsp_helptext sp_helptext sp_helptext xp_cmdshell- order details 訂單詳細信息select * from order details select * from order details where productid = 23- 根據(jù)產(chǎn)品編號在訂單詳細信息表中統(tǒng)計總銷售量select sum(quantity) from order details where productid = 23- 構(gòu)造一個函數(shù)根據(jù)產(chǎn)品編號

30、在訂單詳細信息表中統(tǒng)計總銷售量create function gettotalsalebypid(pid varchar(12)returns intbegin return(select sum(quantity) from order details where productid = pid)endselect * from products- 在產(chǎn)品表中查詢，統(tǒng)計每一樣產(chǎn)品的總銷量select productid, productname, dbo.gettotalsalebypid(productid) from products- create function larg

31、eordershippers ( freightparm money )returns ordershippertab table   (    shipperid     int,    shippername   nvarchar(80),    orderid       int,    shippeddate  

32、; datetime,    freight       money   )asbegin   insert ordershippertab        select s.shipperid, s.companyname,               o.o

33、rderid, o.shippeddate, o.freight        from shippers as s inner join orders as o              on s.shipperid = o.shipvia        where o.freight > freightparm

34、0;  returnendselect * from largeordershippers( $500 )- 根據(jù)作者編號計算其所得版權費create function fun_royaltyper ( au_id id)returns intasbegin declare rt int select rt = sum(royaltyper) from titleauthor where au_id = au_id return (rt)endgoselect top 1 au_lname, au_fname, dbo.fun_royaltyper(au

35、_id) as '版權費' from authorsorder by  dbo.fun_royaltyper(au_id) descgocreate function fun_maxroyaltyper_au_id ()returns idasbegin  declare au_id id select au_id = au_id from authors order by  dbo.fun_royaltyper(au_id) return(au_id)endgoselect dbo.fun_max

36、royaltyper_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, pric

37、e from titlesorder by price desc- 查找最貴書籍的價格(子查詢)select title_id, price from 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_idwhere pub_name = 'new moon book

38、s'- 查詢指定出版社出版的書(子查詢)select title from titles where pub_id = (select pub_id   from publishers   where pub_name =  'new moon books')- 查詢指定出版社出版的書(分開查詢)select title from titles where pub_id = '0736'select pub_id from publishers where pub_name =  'ne

39、w moon books'- 重點- 理解相關子查詢的基礎- select * from titles where type = 'business'select * from titles where type = 'business123'select * from titles where 1 = 1 - 在訂單表中尋找滿足以下條件的訂單編號以及相應的客戶編號- 在詳細訂單表中存在對應的訂單編號并且其中包含產(chǎn)品編號為23的產(chǎn)品- 然后將產(chǎn)品編號為23的產(chǎn)品訂購量返回判斷是否大于20use northwindselect orderid, custo

40、meridfrom orders as or1where 20 < (select quantity from order details as od             where or1.orderid = od.orderid              and  ductid = 23)goselect au_

41、lname, au_fname from authors where 100 in  ( select royaltyper from titleauthor  where titleauthor.au_id = authors.au_id )  select authors.au_lname,authors.au_fnamefrom authors join  titleauthor on titleauthor.au_id=authors.au_idwhere titleauthor.royaltyper =100 use pub

42、sselect au_lname, au_fnamefrom authorswhere au_id in   (select au_id   from titleauthor   where title_id in      (select title_id      from titles      where type = 'popular_comp') s

43、elect 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

44、'select * from titles where type = 'trad_cook'- 查找類型為'business'或是'trad_cook'類型的書籍(or)select * from titles where type = 'business' or type = 'trad_cook'- 查找類型為'business'或是'trad_cook'類型的書籍(in)select * from titles where type in ('business&

45、#39;, 'trad_cook')- 查找來自'ks'或是'ut'的作者select au_lname, state from authors where state = 'ks'select au_lname, state from authors where state = 'ut'- 查找來自'ks'或是'ut'的作者(or)select au_lname, state from authors where state = 'ut' or state = &#

46、39;ks'- 查找來自'ks'或是'ut'的作者(in)select au_lname, state from authors where state in ('ut', 'ks')select au_lname, state from authors where state not in ('ut', 'ks')- 查找出版了類型為'business'類型的書籍的出版社select pub_id from titles where type = 'business

47、'select pub_id,pub_namefrom publisherswhere pub_id in ('1389', '0736')- 查找出版了類型為'business'類型的書籍的出版社(in和子查詢)select pub_id,pub_namefrom publisherswhere pub_id in   (select pub_id   from titles   where type = 'business') select titl

48、e, advancefrom titleswhere advance >    (    select max(advance)    from publishers inner join titles on       titles.pub_id = publishers.pub_id    where pub_name = 'algodata infosystems'   )select t

49、itle, advancefrom titleswhere advance > all   (    select advance    from publishers inner join titles on       titles.pub_id = publishers.pub_id    where pub_name = 'algodata infosystems' and advance is

50、not null   )declare i intset i = 12if i < null print 'ddddd'else print 'xxxxx'   select advance    from publishers inner join titles on       titles.pub_id = publishers.pub_id    where pub_na

51、me = '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 title

52、swhere 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 = '12

53、3') print 'zzzzz'else  print 'bbbbb'if exists(select * from authors where city = 'berkeley' and state ='ut') print 'welcome'else print 'bye-bye'- 篩選出'business'以及'trad_cook'類型的書籍(聯(lián)合查詢)select title_id, type fr

54、om titles where type = 'business'unionselect title_id, type from titles where type = 'trad_cook'- 統(tǒng)計'business'類型的書籍的總價(聯(lián)合查詢)select title, price from titles where type = 'business'unionselect '合計:', sum(price) from titles where type = 'business'- 統(tǒng)計所有書籍

55、的類型剔除重復(distinct)select distinct type from titles- 作者記錄的復制(select into)select * into au from authorsselect * from au- 查看數(shù)據(jù)表結(jié)構(gòu)(select into并沒有對數(shù)據(jù)表的約束進行復制)sp_help authorssp_help au- 分頁(子查詢的經(jīng)典應用之一)- jobs 職務信息表（pubs 數(shù)據(jù)庫）- 在實際項目中，顯示職務信息時，而職務信息量非常龐大，可能需要將其分為若干個頁面來顯示- 比如：每頁顯示4條記錄，那么，第一頁將顯示1，2，3，4，第二頁將顯示5，6，

56、7，8。- 顯示所有信息select * from jobs- 顯示前 4 信息select top 4 * from jobs- 顯示前 8 信息select top 8 * from jobs- 顯示前 12 信息select top 12 * from jobs- 尋找規(guī)律，每一頁的信息源于前（頁面大小 * 頁碼）條信息的反序結(jié)果的前 頁面大小 條記錄- 比如：第二頁就是前 8 條記錄的反序結(jié)果的前 4 條select top 4 * from (select top 8 * from jobs) as ttorder by job_id desc- 當然，對于期望按升序顯示查詢結(jié)果的要

57、求可以對查詢結(jié)果進行再次排序select * from(select top 4 * from (select top 8 * from jobs) as ttorder by job_id desc) as sttorder by job_id- sql 命令中不支持在 select 的查詢列表中直接使用局部變量- 比如：select top pagesize * from jobs- 那么，可以考慮對sql命令進行拼裝，然后，利用系統(tǒng)存儲過程 sp_executesql 來執(zhí)行exec sp_executesql n'select * from jobs'- 存儲過程的實現(xiàn)

58、- 其中，currentpagesize用于確定最后一頁的頁面大小create proc progetjobsbypagecurrentpagesize int,pagesize int,currentpage intasdeclare strsql nvarchar(400)set strsql = 'select * from  (select top ' + convert(nvarchar(4), currentpagesize) + ' *   from (select top ' + convert(nvarc

59、har(4),(pagesize * currentpage) + ' * from jobs) as tt  order by job_id desc) as stt  order by job_id'exec sp_executesql strsqlgo- 測試exec progetjobsbypage 2, 4, 4 (6)存儲過程- 擴展存儲過程- 查詢系統(tǒng)目錄下文件信息xp_cmdshell 'dir *.*'- 啟動windows系統(tǒng)服務xp_cmdshell 'net start iisad

60、min' (7)游標- 游標的五個基本操作步驟：- 聲明declare cur_titles cursorfor select title, price from titles- 打開open cur_titles- 提取fetch cur_titlesfetch next from cur_titles- 關閉close cur_titles- 釋放deallocate cur_titles - 利用游標遍歷所有書籍信息，通過冒泡排序法進行比較，找出最高價格的書- 這一段為批處理版- 與批處理版相比，存儲過程版更方便調(diào)試以及代碼的重用- 聲明declare cur

61、_titles cursorfor select title, price from titles- 打開open cur_titlesdeclare title varchar(80)declare price numeric(9,4)declare title_temp varchar(80)declare price_temp numeric(9,4)- 提取fetch cur_titles into title, pricefetch cur_titles into title_temp, price_tempwhile fetch_status = 0begin if pr

62、ice < price_temp begin  set price = price_temp  set title = title_temp end  fetch cur_titles into title_temp, price_tempend- 關閉close cur_titles- 釋放deallocate cur_titles- 顯示處理結(jié)果print '最貴的書是: ' + title + '  ' + '價格是: ' + convert(varcha

63、r(12),price)go- 定義一個存儲過程- 利用游標遍歷所有書籍信息，通過冒泡排序法進行比較，找出最高價格的書（游標具體應用的經(jīng)典）- 這段存儲過程的實現(xiàn)代碼相對下面的實現(xiàn)方式略有不同- 代碼重復，但是思路更清晰create procedure pro_getmaxtitleas - 聲明 declare cur_titles cursor for select title, price from titles  - 打開 open cur_titles  - 存儲最貴的書籍信息 declare

64、title varchar(80) declare price numeric(9,4) - 存儲從游標中提取出來的書籍的信息 declare title_temp varchar(80) declare price_temp numeric(9,4)  - 提取 fetch cur_titles into title, price - 判斷是否存在書籍信息 if fetch_status <> 0 begin  print '沒有書籍信息!'&#

65、160; - 關閉  close cur_titles  - 釋放  deallocate cur_titles  - 結(jié)束存儲過程  return  end  fetch cur_titles into title_temp, price_temp  - 判斷是否只存在一本書 if fetch_status <> 0 begin  - 顯示處理結(jié)果  print '最貴的書是: ' + title + '  ' + '價格是: ' + convert(varchar(12),price)  - 關閉