《九章系统函数与进阶查询.ppt》由会员分享，可在线阅读，更多相关《九章系统函数与进阶查询.ppt（28页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、九章系统函数与进阶资料查询 Still waters run deep.流静水深流静水深,人静心深人静心深 Where there is life,there is hope。有生命必有希望。有生命必有希望Chapter 9 Outline9-1:決定性與非決定性函數決定性與非決定性函數 9-2:常用的數學函數常用的數學函數 9-3:常用的時間函數常用的時間函數 9-4:常用的字串函數常用的字串函數 9-5:集總函數集總函數 9-6:使用視覺方式設計查詢使用視覺方式設計查詢 9-7:本章總結本章總結2 29-1 9-1 決定性與非決定性函數決定性與非決定性函數決定性決定性(Determinis
2、tic)(Deterministic)函數：函數：針對一組特定的輸入值針對一組特定的輸入值，如果函數如果函數一直傳回相同的結果一直傳回相同的結果，則函數具完全決定性；，則函數具完全決定性；反之，如果函數傳回的值並不能確定，則為非決定性反之，如果函數傳回的值並不能確定，則為非決定性(Non-deterministic)(Non-deterministic)函數函數 。例如：。例如：對於字串函數中的對於字串函數中的LEFT(LEFT(ABCDEABCDE,2),2)而言，每次執而言，每次執行都一定會傳回行都一定會傳回ABAB =決定性函數決定性函數 取得當天日期的函數取得當天日期的函數GETDAT
3、E()GETDATE()，因不保證每次都會，因不保證每次都會傳回相同的日期傳回相同的日期=非決定性函數非決定性函數 非決定決定性函數在非決定決定性函數在SQL ServerSQL Server中可能會限制其可使用中可能會限制其可使用之處之處 非決定性函數也會被效能最佳化所忽略，因為系統為了非決定性函數也會被效能最佳化所忽略，因為系統為了嚴格確保正確性，因此會略過某些計劃重新調整順序的嚴格確保正確性，因此會略過某些計劃重新調整順序的步驟步驟 3 39-29-2常用的數學函數常用的數學函數 數學函數中除了RAND(亂數)之外，其餘的數學函數都是具決定性的函數。ABSABS函數函數：傳回一數值的絕對
4、值 n nABS(ABS(numeric_expressionnumeric_expression)numeric_expressionnumeric_expression：為一數字運算式：為一數字運算式，可以為數字，可以為數字，可以是欄位，可以是欄位，也可以為運算式，例如：也可以為運算式，例如：3*3-83*3-8。CEILINGCEILING函數函數：傳回一大於或等於一數字運算式的最小整數 n nCEILING(CEILING(numeric_expressionnumeric_expression)select select ceilingceiling(123.45),ceiling(
5、-123.45),ceiling(0.0)(123.45),ceiling(-123.45),ceiling(0.0)124,-123,04 49-29-2常用的數學函數常用的數學函數FLOORFLOOR函數函數：傳回一小於或等於一數字運算式的最大整數 n nFLOOR(FLOOR(numeric_expressionnumeric_expression)select select floofloor(123.45),floor(-123.45),r(123.45),floor(-123.45),floor(123)floor(123)PIPI函數函數：傳回圓周率PI的數值 n nPI()PI
6、()select select pipi()()123,-124,1233.14159265358979 5 59-29-2常用的數學函數常用的數學函數ROUNDROUND函數函數：以宣告的精確度傳回一數字運算式的近似值 n nROUND(ROUND(numeric_expressionnumeric_expression,lengthlength,functiofunction n)lengthlength：如果：如果lengthlength為一正整數，為一正整數，numeric_expressionnumeric_expression將將四捨五入四捨五入(RoundRound)至至leng
7、thlength長度的小數位數；如果長度的小數位數；如果lengthlength為一負整數，為一負整數，numeric_expressionnumeric_expression將四捨五入至小數點將四捨五入至小數點左邊算起左邊算起lengthlength長度的位數長度的位數 。functionfunction：如果本引數為：如果本引數為0 0或從缺，則執行四捨五入；或從缺，則執行四捨五入；如果本引數為非如果本引數為非0 0，則執行切除，則執行切除(TruncateTruncate)，即無條，即無條件捨去之意。件捨去之意。select select roundround(748.58,1),rou
8、nd(748.58,-1),round(748.58,-(748.58,1),round(748.58,-1),round(748.58,-2),round(748.58,1,0),round(748.58,-1,1)2),round(748.58,1,0),round(748.58,-1,1)748.60,750.00,700.00,748.60,740.006 69-29-2常用的數學函數常用的數學函數SQUARE函數函數：傳回一數字運算式的平方值 n nSQUARE(SQUARE(float_expressionfloat_expression)float_expressionfloat
9、_expression：為一有理數的運算式為一有理數的運算式 Select Select squaresquare(2.0)=4.0(2.0)=4.0SQRT函數函數：傳回一數字運算式的平方根 n nSQRT(SQRT(float_expressionfloat_expression)select select sqrtsqrt(4.0)=2.0(4.0)=2.0 RAND函數函數：傳回 0 到 1 的隨機float值n nRAND(seed )RAND(seed )seedseed：這是一個提供初始值的整數的運算式，如果未指定這是一個提供初始值的整數的運算式，如果未指定 seedseed，則
10、，則SQL ServerSQL Server便會隨機指派一個初始值。但只要指便會隨機指派一個初始值。但只要指定初始值之後，以後傳回的結果都會相同定初始值之後，以後傳回的結果都會相同 7 79-39-3常用的時間函數常用的時間函數DAY函數函數：傳回宣告日期的日數 n nDAY(DAY(datedate)datedate為一為一datetimedatetime或或smalldatetimesmalldatetime型態的運算式型態的運算式 select day(03/12/2007)select day(03/12/2007)=12 =12MONTH函數函數：傳回宣告日期的月份 n nMONTH
11、(MONTH(datedate)YEAR函數函數：傳回宣告日期的年份 n nYEAR(YEAR(datedate)select select yearyear(03/12/2007)=(03/12/2007)=200720078 89-39-3常用的時間函數常用的時間函數GETDATE函數與函數與GETUTCDATE：傳回系傳回系統目前的日期與時間統目前的日期與時間/傳回國際標準時間或傳回國際標準時間或格林威治標準時間的日期與時間格林威治標準時間的日期與時間9 99-39-3常用的時間函數常用的時間函數DATENAME函數：傳回代表指定日期的指函數：傳回代表指定日期的指定日期部分之字元字串，例
12、如定日期部分之字元字串，例如：年份、：年份、月份、星期、月份、星期、等。等。n nDATENAME(datepart,date)DATENAME(datepart,date)datepartdatepart：傳入各種引數，如傳入各種引數，如yy,qq,mm,dy,dd,wkyy,qq,mm,dy,dd,wk等等datedate：為一為一datetimedatetime或或smalldatetimesmalldatetime型態的運算式型態的運算式 select select datenamedatename(weekday,2007/03/19),(weekday,2007/03/19),da
13、tename datename(dayofyear,2007/03/19)(dayofyear,2007/03/19)星期一，7810109-39-3常用的時間函數常用的時間函數DATEADD函數函數：根據在指定日期中加上某種間隔來傳回新的datetime值 n nDATEADD(datepart,number,date)DATEADD(datepart,number,date)n nnumbernumber：這是用來增加這是用來增加datepartdatepart的值，如果指定的值，如果指定不是整數的值，則會直接捨棄小數點後數字部不是整數的值，則會直接捨棄小數點後數字部份；如果指定為負值，則
14、就對份；如果指定為負值，則就對datepartdatepart的值做減的值做減少的動作。少的動作。n n select dateadd(day,30,2007/03/12 20:23:05),select dateadd(day,30,2007/03/12 20:23:05),dateadd(hour,30,2007/03/12 20:23:05)dateadd(hour,30,2007/03/12 20:23:05),dateadd(day,-30,2007/03/12 20:23:05)dateadd(day,-30,2007/03/12 20:23:05)2007-04-12 20:23
15、:05,2007-03-14 02:23:05,2007-02-10 20:23:0511119-39-3常用的時間函數常用的時間函數DATEDIFF函數函數：根據指定的基礎來傳回startdate與enddate之間的差距 n nDATEDIFF(datepart,startdate,enddate)DATEDIFF(datepart,startdate,enddate)n nstartdate,enddatestartdate,enddate：為為一一datetimedatetime或或smalldatetimesmalldatetime型態的運算式型態的運算式 n n請問請問2007/0
16、3/12 20:23:052007/03/12 20:23:05與與2007/03/14 08:00:002007/03/14 08:00:00相差幾天？相差幾分鐘？相差幾天？相差幾分鐘？select select datediffdatediff(dayday,2007/03/12 20:23:05,2007/03/14 08:00:00),2007/03/12 20:23:05,2007/03/14 08:00:00),datediffdatediff(mimi,2007/03/12 20:23:05,2007/03/14 08:00:00),2007/03/12 20:23:05,200
17、7/03/14 08:00:00)2,213712129-49-4常用的字串函數常用的字串函數 LEN函數函數：傳回一字串的字元數目而非總位元組數(例如：一個中文字雖佔用2個位元組，但是只算一個字元)，會去除字串最後面的連續空白。n nLEN(LEN(string_expressionstring_expression)string_expressionstring_expression：欲評估的字串運算式欲評估的字串運算式(可以是欄位，可以是欄位，或是字串的運算或是字串的運算)n n如果想要計算字串所佔用的位元組數則可使用如果想要計算字串所佔用的位元組數則可使用DATALENGTHDATAL
18、ENGTH函數函數 13139-49-4常用的字串函數常用的字串函數LOWER函數函數：將一字串內的大寫字母(A-Z)轉換為小寫字母 n nLOWER(LOWER(character_expressioncharacter_expression)select select lowerlower(ABCDEFG)(ABCDEFG)=abcdefgabcdefg UPPER函數函數：將一字串內的小寫字母(a-z)轉換為大寫字母 n nUPPER(UPPER(character_expressioncharacter_expression)selectselect upper upper(abcde
19、fg)(abcdefg)=abcdefgabcdefg 請問一字串A，Lower(Upper(A)是否會與A相同？14149-49-4常用的字串函數常用的字串函數LTRIM函數函數：將一字串前置(左邊)的連續空白去除 n nLTRIM(LTRIM(character_expressioncharacter_expression)select select ltrimltrim(ABCDEF)=(ABCDEF)=ABCDEFABCDEF RTRIM函數函數：將一字串後面(右邊)的連續空白去除 n nRTRIM(RTRIM(character_expressioncharacter_express
20、ion)selectselect rtrim rtrim(ABCDE )+FGHIJ(ABCDE )+FGHIJ=ABCDEFGHIJABCDEFGHIJ 15159-49-4常用的字串函數常用的字串函數LEFT函數函數：取回字串左邊的指定個數字元 n nLEFT(LEFT(character_expression,integer_expressioncharacter_expression,integer_expression)integer_expressioninteger_expression：正整數，指定將傳回的正整數，指定將傳回的character_expression chara
21、cter_expression 字元的數目，如過輸入負數則會字元的數目，如過輸入負數則會發生錯誤。發生錯誤。範例範例範例範例9-4.69-4.69-4.69-4.6：請傳回客戶地址的前：請傳回客戶地址的前6 6碼碼 n nselect select leftleft(address,6)from customers(address,6)from customers RIGHT函數函數：取回字串右邊的指定個數字元 n nRIGHT(RIGHT(character_expression,integer_expressioncharacter_expression,integer_expressio
22、n)範例範例範例範例9-4.79-4.79-4.79-4.7：請傳回客戶地址的最後：請傳回客戶地址的最後3 3碼碼 n nselect select rightright(rtrimrtrim(address),3)from customers(address),3)from customers 16169-49-4常用的字串函數常用的字串函數SUBSTRING 函數：傳回字串中間的一部份 n nSUBSTRING(SUBSTRING(expression,start,length expression,start,length)startstart：指定子字串起始位置的整數指定子字串起始位置
23、的整數 lengthlength：這是一個正整數，指定將傳回的這是一個正整數，指定將傳回的character_expressioncharacter_expression從從startstart開始算起的字元數目，如開始算起的字元數目，如過輸入負數則會發生錯誤過輸入負數則會發生錯誤。Select Select substringsubstring(ABCDE,2,2)=BC(ABCDE,2,2)=BC範例範例範例範例9-4.9 9-4.9 9-4.9 9-4.9 請查詢客戶電話號碼，該公司名稱的第二請查詢客戶電話號碼，該公司名稱的第二個字為美字個字為美字 n nselect cust_name
24、,phone from customers select cust_name,phone from customers where where substringsubstring(cust_name,2,1)=(cust_name,2,1)=美美 17179-5 9-5 集總函數集總函數在資料查詢中，往往需要將所篩選出來的資料作在資料查詢中，往往需要將所篩選出來的資料作一些分類，而將資料分成若干集合，然後在依據一些分類，而將資料分成若干集合，然後在依據這些分類內的數據作一些加總或分析，此時就需這些分類內的數據作一些加總或分析，此時就需要集總函數。例如：現在想要將所有的訂單資料要集總函數。例如
25、：現在想要將所有的訂單資料依經銷商加以分類，再對每一集合作一些統計分依經銷商加以分類，再對每一集合作一些統計分析。析。常用的集總函數包括：常用的集總函數包括：n nCOUNTCOUNT、SUMSUM MAXMAX、AVGAVG以及以及以及以及MINMIN等等集總函數使用語法通常為：集總函數使用語法通常為：select col_name1select col_name1,AG_Fun,AG_Fun(col_name2)from(col_name2)from tbl_name tbl_name group bygroup by col_name1 col_name1 AG_FunAG_Fun為集總
26、函數，而被使用在為集總函數，而被使用在group bygroup by之後的之後的欄位不用使用及總函數，其餘皆要使用。欄位不用使用及總函數，其餘皆要使用。18189-5 9-5 集總函數集總函數COUNT函數函數：傳回一集合內所擁有的資料錄筆數 n nCOUNT(COUNT(ALLALL|DISTINCTDISTINCT expressionexpression|*)ALLALL：表示施用於所有的數值，為預設值表示施用於所有的數值，為預設值 DISTINCTDISTINCT：表示表示COUNTCOUNT傳回唯一且非傳回唯一且非NULLNULL數值的個數值的個數數 expressionexpre
27、ssion：為一非為一非uniqueidentifieruniqueidentifier、texttext、imageimage或或ntextntext型態的運算式型態的運算式 n n範例範例範例範例9-5-29-5-29-5-29-5-2：請查示範資料庫中共銷售多少種產品請查示範資料庫中共銷售多少種產品 select select count(*)count(*)from products from products n n範例範例範例範例9-5.39-5.39-5.39-5.3：請問客戶各下過多少次訂單，請依：請問客戶各下過多少次訂單，請依交易次數的多寡來顯示客戶編號交易次數的多寡來顯示客
28、戶編號。select select count(*)count(*)tradecount,tradecount,cust_numcust_num from orders from orders group by group by cust_numcust_num order by tradecount DESCorder by tradecount DESC19199-5 9-5 集總函數集總函數SUM函數函數：傳回一集合內所有數值或DISTINCT數值的總和，SUM只能應用於數字欄位，它會自動排除NULL。n n範例範例範例範例9-5.49-5.49-5.49-5.4：請查詢已被訂貨中：請查
29、詢已被訂貨中HHTHHT供應商的總供應商的總金額金額 select select sum(total_price)sum(total_price)from items where supp_code=from items where supp_code=HHTHHTn n範例範例範例範例9-5.59-5.59-5.59-5.5：請查詢已被訂貨中各供應商的總金：請查詢已被訂貨中各供應商的總金額額select supp_code,sum(total_price)from items group by supp_code20209-5 9-5 集總函數集總函數MAXMAX函數函數 ：傳回一集合內所有
30、數值的極大值n nMAX(expression)expression：為一常數、欄位或函數，MAX可使用於文字、數字或datetime欄位AVGAVG函數函數：傳回一集合內所有數值或DISTINCT數值的平均值n nAVG(AVG(ALLALL|DISTINCTDISTINCT expressionexpression)MINMIN函數函數：傳回一集合內所有數值的極小值n nMIN(MIN(expressionexpression)21219-5 9-5 集總函數集總函數n n範例範例範例範例9-5.69-5.69-5.69-5.6：請查詢所有供應商所供應貨品的最：請查詢所有供應商所供應貨品的
31、最高、最低以及平均單價高、最低以及平均單價 select supp_code select supp_code 供應商碼供應商碼,maxmax(unit_price)(unit_price)最高單價最高單價,minmin(unit_price)(unit_price)最低單價最低單價,avgavg(unit_price)(unit_price)平均單平均單價價 from products from products group by supp_codegroup by supp_code n n範例範例範例範例9-5.89-5.89-5.89-5.8：請查詢每一訂單的總金額，但是只：請查詢每一
32、訂單的總金額，但是只列出訂單項目超過列出訂單項目超過4 4的訂單總價。的訂單總價。select count(*)select count(*)購買項目購買項目,order_num,sum(total_price),order_num,sum(total_price)total from items group by order_num total from items group by order_num having count(*)having count(*)4 4 order by total order by total 22229-6 9-6 使用視覺方式設計查詢使用視覺方式設計查
33、詢 在SQL Server 2005 Express中除了使用者自行撰寫SQL敘述之外，還可以使用QueryBuilder工具以視覺化的方式設計查詢。圖表窗格準則窗格SQL窗格23239-6 9-6 使用視覺方式設計查詢使用視覺方式設計查詢在這條連結線上按下滑鼠右鍵預設使用為INNER JOIN，只要按該關聯的滑鼠右鍵即可更改。移除移除：就會因為沒有結合條件而變成：就會因為沒有結合條件而變成CROSS JOINCROSS JOIN 選取選取customerscustomers中所有資料列中所有資料列=LEFT OUTER JOIN=LEFT OUTER JOIN 選取選取ordersorder
34、s中所有資料列中所有資料列 就會變成就會變成RIGHT OUTER JOIN RIGHT OUTER JOIN 兩者都勾選就變成兩者都勾選就變成FULL OUTER JOIN FULL OUTER JOIN 24249-6 9-6 使用視覺方式設計查詢使用視覺方式設計查詢設定篩選條件在想要加入篩選欄位的相對位置輸入條件有篩選條件的標示25259-6 9-6 使用視覺方式設計查詢使用視覺方式設計查詢條件加在該欄位下就是AND條件加在該欄位下就是OR26269-6 9-6 使用視覺方式設計查詢使用視覺方式設計查詢1.出現群組依據欄位2.不想Group By的欄就也要選擇集總函數，否則會出現錯誤3.
35、新增交易次數欄位並設定別名5.設定1，表示交易兩次以上4.設定排序27279-7 9-7 本章總結本章總結針對一組特定的輸入值，如果函數一直傳回相同的結果，針對一組特定的輸入值，如果函數一直傳回相同的結果，決定性決定性(DeterministicDeterministic)函數。函數。常用的數學函數常用的數學函數ABSABS、CEILINGCEILING、FLOORFLOOR、PIPI、ROUNDROUND、SQUARESQUARE以及以及SQRTSQRT，它們的功能分別為絕對值、大於，它們的功能分別為絕對值、大於/等等於的最小整數、小於於的最小整數、小於/等於的最大整數、圓周率、近似值、等於
36、的最大整數、圓周率、近似值、平方以及平方根。平方以及平方根。常用的時間函數常用的時間函數DAYDAY、MONTHMONTH、YEARYEAR以及以及GETDATEGETDATE，它們的功能分別為日數、月份、年份以及目前的日期它們的功能分別為日數、月份、年份以及目前的日期/時時間間 GROUPGROUP BY BY子句，其功能為根據選取欄位將篩選資料分成子句，其功能為根據選取欄位將篩選資料分成若干集合。若干集合。使用使用QueryBuilderQueryBuilder工具能以視覺化的方式設計工具能以視覺化的方式設計SQLSQL查詢，提查詢，提供對於供對於SQLSQL敘述不熟悉的使用者一個學習的示範。敘述不熟悉的使用者一個學習的示範。2828