《第九章系统函数与进阶资料查询备课讲稿》由会员分享，可在线阅读，更多相关《第九章系统函数与进阶资料查询备课讲稿（28页珍藏版）》请在金锄头文库上搜索。
1、1,第九章系統函數與進階資料查詢,2,Chapter9Outline,9-1:決定性與非決定性函數9-2:常用的數學函數9-3:常用的時間函數9-4:常用的字串函數9-5:集總函數9-6:使用視覺方式設計查詢9-7:本章總結,3,9-1決定性與非決定性函數,決定性(Deterministic)函數：針對一組特定的輸入值，如果函數一直傳回相同的結果，則函數具完全決定性；反之，如果函數傳回的值並不能確定，則為非決定性(Non-deterministic)函數。例如：對於字串函數中的LEFT(ABCDE,2)而言，每次執行都一定會傳回AB=決定性函數取得當天日期的函數GETDATE()，因不保證每次
2、都會傳回相同的日期=非決定性函數非決定決定性函數在SQLServer中可能會限制其可使用之處非決定性函數也會被效能最佳化所忽略，因為系統為了嚴格確保正確性，因此會略過某些計劃重新調整順序的步驟,4,9-2常用的數學函數,數學函數中除了RAND(亂數)之外，其餘的數學函數都是具決定性的函數。ABS函數：傳回一數值的絕對值ABS(numeric_expression)numeric_expression：為一數字運算式，可以為數字，可以是欄位，也可以為運算式，例如：3*3-8。CEILING函數：傳回一大於或等於一數字運算式的最小整數CEILING(numeric_expression)selec
3、tceiling(123.45),ceiling(-123.45),ceiling(0.0),124,-123,0,6,9-2常用的數學函數,ROUND函數：以宣告的精確度傳回一數字運算式的近似值ROUND(numeric_expression,length,function)length：如果length為一正整數，numeric_expression將四捨五入(Round)至length長度的小數位數；如果length為一負整數，numeric_expression將四捨五入至小數點左邊算起length長度的位數。function：如果本引數為0或從缺，則執行四捨五入；如果本引數為非0，則
4、執行切除(Truncate)，即無條件捨去之意。selectround(748.58,1),round(748.58,-1),round(748.58,-2),round(748.58,1,0),round(748.58,-1,1),748.60,750.00,700.00,748.60,740.00,7,9-2常用的數學函數,SQUARE函數：傳回一數字運算式的平方值SQUARE(float_expression)float_expression：為一有理數的運算式Selectsquare(2.0)=4.0SQRT函數：傳回一數字運算式的平方根SQRT(float_expression)se
5、lectsqrt(4.0)=2.0RAND函數：傳回0到1的隨機float值RAND(seed)seed：這是一個提供初始值的整數的運算式，如果未指定seed，則SQLServer便會隨機指派一個初始值。但只要指定初始值之後，以後傳回的結果都會相同,8,9-3常用的時間函數,DAY函數：傳回宣告日期的日數DAY(date)date為一datetime或smalldatetime型態的運算式selectday(03/12/2007)=12MONTH函數：傳回宣告日期的月份MONTH(date)YEAR函數：傳回宣告日期的年份YEAR(date)selectyear(03/12/2007)=200
6、7,9,9-3常用的時間函數,GETDATE函數與GETUTCDATE：傳回系統目前的日期與時間/傳回國際標準時間或格林威治標準時間的日期與時間,10,9-3常用的時間函數,DATENAME函數：傳回代表指定日期的指定日期部分之字元字串，例如：年份、月份、星期、等。DATENAME(datepart,date)datepart：傳入各種引數，如yy,qq,mm,dy,dd,wk等date：為一datetime或smalldatetime型態的運算式selectdatename(weekday,2007/03/19),datename(dayofyear,2007/03/19),星期一，78,1
7、1,9-3常用的時間函數,DATEADD函數：根據在指定日期中加上某種間隔來傳回新的datetime值DATEADD(datepart,number,date)number：這是用來增加datepart的值，如果指定不是整數的值，則會直接捨棄小數點後數字部份；如果指定為負值，則就對datepart的值做減少的動作。selectdateadd(day,30,2007/03/1220:23:05),dateadd(hour,30,2007/03/1220:23:05),dateadd(day,-30,2007/03/1220:23:05),2007-04-1220:23:05,2007-03-14
8、02:23:05,2007-02-1020:23:05,12,9-3常用的時間函數,DATEDIFF函數：根據指定的基礎來傳回startdate與enddate之間的差距DATEDIFF(datepart,startdate,enddate)startdate,enddate：為一datetime或smalldatetime型態的運算式請問2007/03/1220:23:05與2007/03/1408:00:00相差幾天？相差幾分鐘？selectdatediff(day,2007/03/1220:23:05,2007/03/1408:00:00),datediff(mi,2007/03/122
9、0:23:05,2007/03/1408:00:00),2,2137,13,9-4常用的字串函數,LEN函數：傳回一字串的字元數目而非總位元組數(例如：一個中文字雖佔用2個位元組，但是只算一個字元)，會去除字串最後面的連續空白。LEN(string_expression)string_expression：欲評估的字串運算式(可以是欄位，或是字串的運算)如果想要計算字串所佔用的位元組數則可使用DATALENGTH函數,14,9-4常用的字串函數,LOWER函數：將一字串內的大寫字母(A-Z)轉換為小寫字母LOWER(character_expression)selectlower(ABCDEF
10、G)=abcdefgUPPER函數：將一字串內的小寫字母(a-z)轉換為大寫字母UPPER(character_expression)selectupper(abcdefg)=abcdefg請問一字串A，Lower(Upper(A)是否會與A相同？,15,9-4常用的字串函數,LTRIM函數：將一字串前置(左邊)的連續空白去除LTRIM(character_expression)selectltrim(ABCDEF)=ABCDEFRTRIM函數：將一字串後面(右邊)的連續空白去除RTRIM(character_expression)selectrtrim(ABCDE)+FGHIJ=ABCDEF
11、GHIJ,16,9-4常用的字串函數,LEFT函數：取回字串左邊的指定個數字元LEFT(character_expression,integer_expression)integer_expression：正整數，指定將傳回的character_expression字元的數目，如過輸入負數則會發生錯誤。範例9-4.6：請傳回客戶地址的前6碼selectleft(address,6)fromcustomersRIGHT函數：取回字串右邊的指定個數字元RIGHT(character_expression,integer_expression)範例9-4.7：請傳回客戶地址的最後3碼selectri
12、ght(rtrim(address),3)fromcustomers,17,9-4常用的字串函數,SUBSTRING函數：傳回字串中間的一部份SUBSTRING(expression,start,length)start：指定子字串起始位置的整數length：這是一個正整數，指定將傳回的character_expression從start開始算起的字元數目，如過輸入負數則會發生錯誤。Selectsubstring(ABCDE,2,2)=BC範例9-4.9請查詢客戶電話號碼，該公司名稱的第二個字為美字selectcust_name,phonefromcustomerswheresubstring
13、(cust_name,2,1)=美,18,9-5集總函數,在資料查詢中，往往需要將所篩選出來的資料作一些分類，而將資料分成若干集合，然後在依據這些分類內的數據作一些加總或分析，此時就需要集總函數。例如：現在想要將所有的訂單資料依經銷商加以分類，再對每一集合作一些統計分析。常用的集總函數包括：COUNT、SUMMAX、AVG以及MIN等集總函數使用語法通常為：selectcol_name1,AG_Fun(col_name2)fromtbl_namegroupbycol_name1AG_Fun為集總函數，而被使用在groupby之後的欄位不用使用及總函數，其餘皆要使用。,19,9-5集總函數,CO
14、UNT函數：傳回一集合內所擁有的資料錄筆數COUNT(ALL|DISTINCTexpression|*)ALL：表示施用於所有的數值，為預設值DISTINCT：表示COUNT傳回唯一且非NULL數值的個數expression：為一非uniqueidentifier、text、image或ntext型態的運算式範例9-5-2：請查示範資料庫中共銷售多少種產品selectcount(*)fromproducts範例9-5.3：請問客戶各下過多少次訂單，請依交易次數的多寡來顯示客戶編號。,selectcount(*)tradecount,cust_numfromordersgroupbycust_n
15、umorderbytradecountDESC,20,9-5集總函數,SUM函數：傳回一集合內所有數值或DISTINCT數值的總和，SUM只能應用於數字欄位，它會自動排除NULL。範例9-5.4：請查詢已被訂貨中HHT供應商的總金額selectsum(total_price)fromitemswheresupp_code=HHT範例9-5.5：請查詢已被訂貨中各供應商的總金額,selectsupp_code,sum(total_price)fromitemsgroupbysupp_code,21,9-5集總函數,MAX函數：傳回一集合內所有數值的極大值MAX(expression)expres
16、sion：為一常數、欄位或函數，MAX可使用於文字、數字或datetime欄位AVG函數：傳回一集合內所有數值或DISTINCT數值的平均值AVG(ALL|DISTINCTexpression)MIN函數：傳回一集合內所有數值的極小值MIN(expression),22,9-5集總函數,範例9-5.6：請查詢所有供應商所供應貨品的最高、最低以及平均單價selectsupp_code供應商碼,max(unit_price)最高單價,min(unit_price)最低單價,avg(unit_price)平均單價fromproductsgroupbysupp_code範例9-5.8：請查詢每一訂單的總金額，但是只列出訂單項目超過4的訂單總價。selectcount(*)購買項目,order_num,sum(total_price)totalfromitemsgroupbyorder_numhavingcount(*)4orderbytotal,23,9-6使用視覺方式設計查詢,在SQLServer2005Express中除了使用者自行撰寫SQL敘述之外，還可以使用QueryBuilde
