SQL語句大全集合

1、SELECT * FROM service_promotion WHERE gmt_modified = TO_DATE(2001-9-01,yyyy-mm-dd) AND gmt_modified 10) begin - print Dadadada! print Dadadada! end - else begin print XiaoXiao! print XiaoXiao! end- While循環(huán)控制declare i int;set i = 12;print ireturn;while (i 18)begin print i; set i = i + 1; if i 15 brea

2、k;end;- CASE 分支判斷select au_lname, state, 猶他州 from authors where state = UTselect au_lname, state, 密西西比州 from authors where state = MIselect au_lname, state, 肯塔基州 from authors where state = KSselect au_lname, state, case state when UT then 猶他州 when MI then 密西西比州 when KS then 肯塔基州 when CA then 加利福利亞 e

3、lse state endfrom authors(4.1)系統(tǒng)函數(shù)- 獲取指定字符串中左起第一個字符的ASC碼print ascii(ABCDEF)- 根據(jù)給定的ASC碼獲取相應(yīng)的字符print char(65)- 獲取給定字符串的長度print len(abcdef)- 大小寫轉(zhuǎn)換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

4、,3)- 隨機(jī)數(shù)- 0 - 1000 之間的隨機(jī)數(shù)print rand() * 1000 - 獲取圓周率print pi()- 獲取系統(tǒng)時間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(

5、)- 獲取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 + defprint abcd

6、erprint abc + 456print abc + 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

7、123 + 123print 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

8、, 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 datediff(hour, 2001-01-01, 2008-08-08)print datediff(mi, 2001-01-01, 2008-08-08)print date

9、diff(ss, 2001-01-01, 2008-08-08)- 在向指定日期加上一段時間的基礎(chǔ)上，返回新的 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_i

10、d(pubs)print db_name(5)- 利用系統(tǒng)函數(shù)作為默認(rèn)值約束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_birthdayinsert into ttt values (ANiu, 2005-04-01)insert into ttt values (ANiu, getdate()inser

11、t into ttt values (AZhu, default)sp_help tttselect * from ttt（4.2）自定義函數(shù)select title_idfrom titles where type = businessselect stuff(title_id,1,3,ABB), type from titles where type = businessselect count(title_id) from titles where type = businessselect title_id from titles where type = businessselect

12、 *,count(dbo.titleauthor.title_id)FROM dbo.authors INNER JOINselect 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 JOINorder by 作品數(shù)量- 自定義函數(shù)的引子（通過這個子查詢來引入函數(shù)的作用）- 子查詢- 統(tǒng)計每個作者的作品數(shù)- 將父查詢中的作者編號傳入子查詢- 作為查詢條件利

13、用聚合函數(shù)count統(tǒng)計其作品數(shù)量select au_lname, (select count(title_id) from titleauthor as ta where ta.au_id = 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ù)給定的作

14、者編號獲取其相應(yīng)的作品數(shù)量create function GetTitleCountByAuID(au_id varchar(12)returns intbegin 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 TitleC

15、ountgo- 執(zhí)行存儲過程execute pro_CalTitleCount- vb中函數(shù)定義格式function 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 sal

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

17、計算其總銷售量create function 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ù)書籍編號計

18、算其銷量排名create function 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) from titlesselect count(title_id) + 1from titles where dbo.GetTo

19、talSaleByTitleID(title_id) dbo.GetTotalSaleByTitleID(pc1035)- 刪除函數(shù)drop function GetRankByTitleId- 根據(jù)書籍編號計算其銷量排名create function GetRankByTitleId(tid varchar(24)returns intbegin return (select count(title_id) + 1 from titles where dbo.GetTotalSaleByTitleID(title_id) dbo.GetTotalSaleByTitleID(tid)end-

20、在查詢語句中利用函數(shù)統(tǒng)計每本書的總銷量和總排名select title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSales, dbo.GetRankByTitleId(title_id) as TotalRankfrom titlesorder by TotalSales desc- 查看表結(jié)構(gòu)sp_help titles- 查看存儲過程的定義內(nèi)容sp_helptext GetRankByTitleIdsp_helptext sp_helptext sp_helptext xp_cmdshell- ORDER DETAILS

21、 訂單詳細(xì)信息select * from order details select * from order details where productid = 23- 根據(jù)產(chǎn)品編號在訂單詳細(xì)信息表中統(tǒng)計總銷售量select sum(quantity) from order details where productid = 23- 構(gòu)造一個函數(shù)根據(jù)產(chǎn)品編號在訂單詳細(xì)信息表中統(tǒng)計總銷售量create function GetTotalSaleByPID(Pid varchar(12)returns intbegin return(select sum(quantity) from order

22、details where productid = Pid)endselect * from products- 在產(chǎn)品表中查詢，統(tǒng)計每一樣產(chǎn)品的總銷量select productid, productname, dbo.GetTotalSaleByPID(productid) from products- CREATE FUNCTION LargeOrderShippers ( FreightParm money )RETURNS OrderShipperTab TABLE ( ShipperID int, ShipperName nvarchar(80), OrderID int, Shi

23、ppedDate datetime, Freight money )ASBEGIN INSERT OrderShipperTab SELECT S.ShipperID, S.CompanyName, O.OrderID, O.ShippedDate, O.Freight FROM Shippers AS S INNER JOIN Orders AS O ON S.ShipperID = O.ShipVia WHERE O.Freight FreightParm RETURNENDSELECT * FROM LargeOrderShippers( $500 )- 根據(jù)作者編號計算其所得版權(quán)費cr

24、eate 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_id) as 版權(quán)費 from authorsorder by dbo.fun_RoyalTyper(au_id) descgocreate function fun_MaxRoy

25、alTyper_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_MaxRoyalTyper_Au_id()goselect au_lname, au_fname, dbo.fun_RoyalTyper(au_id) as 版權(quán)稅 from authorswhere au_id = dbo.fun_MaxRoyalTyper_Au_id()go(5)高級查詢

26、select title_id, price from titles- 查找最高價格select max(price) from titles- 查找最貴書籍的價格(排序)，如果存在多本價格最貴的書，此方法將會遺漏select top 1 title_id, price from titlesorder by price desc- 查找最貴書籍的價格(子查詢)select title_id, price from titleswhere price = (select max(price) from titles)- 查詢指定出版社出版的書(連接)select p.pub_name as 出

27、版社, t.title as 書籍名稱from publishers as p join titles as t on p.pub_id = t.pub_idwhere pub_name = New Moon Books- 查詢指定出版社出版的書(子查詢)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 = 0736select

28、 pub_id from publishers where pub_name = New Moon Books- 重點- 理解相關(guān)子查詢的基礎(chǔ)select * from titles where type = businessselect * from titles where type = business123select * from titles where 1 = 1 - 在訂單表中尋找滿足以下條件的訂單編號以及相應(yīng)的客戶編號- 在詳細(xì)訂單表中存在對應(yīng)的訂單編號并且其中包含產(chǎn)品編號為23的產(chǎn)品- 然后將產(chǎn)品編號為23的產(chǎn)品訂購量返回判斷是否大于20USE northwindSELEC

29、T orderid, customeridFROM orders AS or1WHERE 20 ( SELECT MAX(advance) FROM publishers INNER JOIN titles ON titles.pub_id = publishers.pub_id WHERE pub_name = Algodata Infosystems )-SELECT title, advanceFROM titlesWHERE advance all ( SELECT advance FROM publishers INNER JOIN titles ON titles.pub_id =

30、 publishers.pub_id WHERE pub_name = Algodata Infosystems and advance is not null )-declare i intset i = 12if i 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

31、 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 ZZZZZelse print BBBBBif exists(select * f

32、rom authors where city = Berkeley and state =UT) print Welcomeelse print Bye-Bye- 篩選出business以及trad_cook類型的書籍(聯(lián)合查詢)select title_id, type from titles where type = businessunionselect title_id, type from titles where type = trad_cook- 統(tǒng)計business類型的書籍的總價(聯(lián)合查詢)select title, price from titles where type

33、= businessunionselect 合計:, sum(price) from titles where type = business- 統(tǒng)計所有書籍的類型剔除重復(fù)(Distinct)select distinct type from titles- 作者記錄的復(fù)制(Select Into)select * into au from authorsselect * from au- 查看數(shù)據(jù)表結(jié)構(gòu)(Select Into并沒有對數(shù)據(jù)表的約束進(jìn)行復(fù)制)sp_help authorssp_help au- 分頁(子查詢的經(jīng)典應(yīng)用之一)- Jobs 職務(wù)信息表（pubs 數(shù)據(jù)庫）- 在實際項

34、目中，顯示職務(wù)信息時，而職務(wù)信息量非常龐大，可能需要將其分為若干個頁面來顯示- 比如：每頁顯示4條記錄，那么，第一頁將顯示1，2，3，4，第二頁將顯示5，6，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

35、* from (select top 8 * from jobs) as ttorder by job_id desc- 當(dāng)然，對于期望按升序顯示查詢結(jié)果的要求可以對查詢結(jié)果進(jìn)行再次排序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命令進(jìn)行拼裝，然后，利用系統(tǒng)

36、存儲過程 sp_executesql 來執(zhí)行exec sp_executesql NSelect * from jobs- 存儲過程的實現(xiàn)- 其中，CurrentPageSize用于確定最后一頁的頁面大小create proc proGetJobsByPageCurrentPageSize int,PageSize int,CurrentPage intasDeclare strSql nvarchar(400)set strSql = select * from (select top + convert(nvarchar(4), CurrentPageSize) + * from (sel

37、ect top + convert(nvarchar(4),(PageSize * CurrentPage) + * from jobs) as tt order by job_id desc) as stt order by job_idexec sp_executesql strSqlgo- 測試exec proGetJobsByPage 2, 4, 4 (6)存儲過程- 擴(kuò)展存儲過程- 查詢系統(tǒng)目錄下文件信息xp_cmdshell dir *.*- 啟動Windows系統(tǒng)服務(wù)xp_cmdshell net start iisadmin (7)游標(biāo)- 游標(biāo)的五個基本操作步驟：- 聲明dec

38、lare cur_titles cursorfor select title, price from titles- 打開open cur_titles- 提取fetch cur_titlesfetch next from cur_titles- 關(guān)閉close cur_titles- 釋放deallocate cur_titles- 利用游標(biāo)遍歷所有書籍信息，通過冒泡排序法進(jìn)行比較，找出最高價格的書- 這一段為批處理版- 與批處理版相比，存儲過程版更方便調(diào)試以及代碼的重用- 聲明declare cur_titles cursorfor select title, price from tit

39、les- 打開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 price price_temp begin set price = price_temp set tit

40、le = title_temp end fetch cur_titles into title_temp, price_tempend- 關(guān)閉close cur_titles- 釋放deallocate cur_titles- 顯示處理結(jié)果print 最貴的書是: + title + + 價格是: + convert(varchar(12),price)go- 定義一個存儲過程- 利用游標(biāo)遍歷所有書籍信息，通過冒泡排序法進(jìn)行比較，找出最高價格的書（游標(biāo)具體應(yīng)用的經(jīng)典）- 這段存儲過程的實現(xiàn)代碼相對下面的實現(xiàn)方式略有不同- 代碼重復(fù)，但是思路更清晰create procedure pro_Get

41、MaxTitleas - 聲明 declare cur_titles cursor for select title, price from titles - 打開 open cur_titles - 存儲最貴的書籍信息 declare title varchar(80) declare price numeric(9,4) - 存儲從游標(biāo)中提取出來的書籍的信息 declare title_temp varchar(80) declare price_temp numeric(9,4) - 提取 fetch cur_titles into title, price - 判斷是否存在書籍信息 i

42、f fetch_status 0 begin print 沒有書籍信息! - 關(guān)閉 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) - 關(guān)閉 close cur_titles - 釋放 deallocate cur_titles - 結(jié)束存儲過程 return end- while fetch_status = 0 begin if price price_temp begin set price = price_temp set title =