《輕輕松松認(rèn)識函數(shù)》PPT課件.ppt

《《輕輕松松認(rèn)識函數(shù)》PPT課件.ppt》由會員分享，可在線閱讀，更多相關(guān)《《輕輕松松認(rèn)識函數(shù)》PPT課件.ppt（44頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1、第5章 輕輕松松認(rèn)識函數(shù),函數(shù)對于任何程序設(shè)計語言來說都是非常關(guān)鍵的部分，它給用戶提供了強(qiáng)大的功能，幫助用戶使用簡捷的代碼實(shí)現(xiàn)復(fù)雜的功能。函數(shù)在Transact-SQL中代表的是能夠完成某個功能并返回值的一個模塊或者代碼塊。Transact-SQL支持兩類函數(shù)：系統(tǒng)內(nèi)置函數(shù)和用戶定義函數(shù)。Transact-SQL的內(nèi)置函數(shù)主要包括聚合函數(shù)、游標(biāo)函數(shù)、數(shù)學(xué)函數(shù)、字符串函數(shù)、系統(tǒng)函數(shù)、日期和時間函數(shù)、元數(shù)據(jù)函數(shù)以及文本和圖像函數(shù)等多種本章主要介紹如下幾種常用的函數(shù)： 數(shù)學(xué)函數(shù) 字符串函數(shù) 日期和時間函數(shù) 數(shù)據(jù)類型轉(zhuǎn)換函數(shù) 聚合函數(shù) 系統(tǒng)函數(shù) 文本和圖像函數(shù),5.1 數(shù)學(xué)函數(shù),Transact-SQ

2、L提供了數(shù)學(xué)運(yùn)算的一組函數(shù)，能夠?qū)?shù)字表達(dá)式進(jìn)行計算，并將結(jié)果返回給用戶。這些函數(shù)是進(jìn)行數(shù)據(jù)運(yùn)算、統(tǒng)計和分析的基礎(chǔ)。,,5.2 字符串函數(shù),字符串函數(shù)可以對字符串執(zhí)行查找、轉(zhuǎn)換等操作，作用于CHAR、VARCHAR、 BINARY、 和VARBINARY 數(shù)據(jù)類型。字符串函數(shù)可以在SELECT語句的SELECT和WHERE子句以及表達(dá)式中使用字符串函數(shù)。,5.2.1 ASCII函數(shù),ASCII函數(shù)返回字符串表達(dá)式最左端字符的ASCII 碼值。語法格式： ASCII(character_expression) character_expression：字符或字符串類型的表達(dá)式。 實(shí)例：查詢‘h’

3、的ASCII碼值。 SELECT ASCII(happy) 返回值為104，類型為int。,5.2.2 CHAR函數(shù),與ASCII函數(shù)的功能恰好相反，CHAR函數(shù)把ASCII碼值換算成對應(yīng)的字符。語法格式： CHAR(integer_expression) integer_expression：0~255之間的整數(shù)，否則返回NULL。 實(shí)例：查詢104對應(yīng)的字符。 SELECT CHAR(104) 返回值為‘h’，類型為char。,5.2.3 CHARINDEX函數(shù),CHARINDEX函數(shù)用于返回字符串中指定表達(dá)式的開始位置。語法格式： CHARINDEX(expression1 ,expre

4、ssion2 [ , start_location ]) expression1：字符串表達(dá)式，指定的搜索串 。 expression2：字符串表達(dá)式，包含要查找的字符序列。 start_location：搜索的起始位置，是可選項。如不指定或者指定為0或者負(fù)值，則搜索從初始位置開始，否則從指定位置開始搜索。,5.2.4 LEFT函數(shù),LEFT函數(shù)返回從字符串左邊開始指定個數(shù)的字符串。語法格式： LEFT(character_expression , integer_expression) character_expression： integer_expression： 實(shí)例：返回字符串前6個

5、字符以及前20個字符。 SELECT LEFT(I hava many friends,6) SELECT LEFT(I hava many friends,20) 第一條語句返回值為“I hava”，第二條語句返回值為“I hava many friends”，即如果指定長度超過字符串的長度，并不會出錯，而是返回整個字符串。,5.2.5 RIGHT函數(shù),RIGHT函數(shù)與LEFT函數(shù)功能相反，它返回從字符串右邊開始指定個數(shù)的字符串。語法格式： RIGHT(character_expression , integer_expression) character_expression： integ

6、er_expression： 實(shí)例：返回給定字符串后7個字符以及該這七個字符的第一個字符。 SELECT RIGHT(I hava many friends,7) SELECT LEFT(RIGHT(I hava many friends,7),1) 第一條語句返回值為“friends”，第二條語句返回值為‘f’，即先使用RIGHT(I hava many friends,7)求出最后7個字符組成的字符串，然后再使用LEFT函數(shù)求出該字符串的首字符。,5.2.6 LEN函數(shù),LEN函數(shù)用于返回指定字符串的總長度，即包含的字符的個數(shù)。語法格式： LEN(string_expression) st

7、ring_expression n：指定的需計算長度的字符串。 實(shí)例：計算給定字符串的長度。 SELECT LEN(friends) SELECT LEN(朋友) SELECT LEN(2046) 這三條語句的返回值依次為7、2、4，需要注意的是一個漢字也是作為一個字符處理的，同時純數(shù)字組成的串可以不加‘’，但如果是數(shù)字和字符混合出現(xiàn)時必須要加上‘’。,5.2.7 LTRIM函數(shù),LTRIM函數(shù)用于去除指定字符串左邊的空格。語法格式： LTRIM(character_expression) character_expression：指定字符串或二進(jìn)制表達(dá)式。 實(shí)例：去除指定字符串前的空格。 S

8、ELECT LTRIM( There are two space in the left) SELECT LTRIM( There are two space and a ‘Tab’ in the left) 第一條語句返回值為“There are two space in the left”，第二條語句返回值為‘ There are two space and a ‘Tab’ in the left’，第二條語句看似空格沒有去除掉，實(shí)際上在字符串前的輸入的‘Tab’鍵造成的，并非空格。,5.2.8 RTRIM函數(shù),RTRIM函數(shù)用于去除指定字符串右邊的空格。語法格式： RTRIM(chara

9、cter_expression) character_expression：指定字符串或二進(jìn)制表達(dá)式。 實(shí)例：去除指定字符串前后的空格。 SELECT RTRIM(LTRIM( There are three space in the left and three space in the rignt ) 該語句的返回值為“There are three space in the left and three space in the rignt”，即先使用LTRIM函數(shù)去除字符串左端的空格，然后對得出的字符處去除右端的空格。,5.2.9 LOWER函數(shù),LOWER函數(shù)用于把指定的字符串中的大

10、寫字符裝換為相應(yīng)的小寫字符。語法格式： LOWER(character_expression) character_expression：指定需要進(jìn)行轉(zhuǎn)換的字符串。 實(shí)例：將字符串中的大寫字符全部轉(zhuǎn)換成小寫字符。 SELECT LOWER(A became a after this function) 該語句的返回值為“a became a after this function”，即經(jīng)過轉(zhuǎn)換大寫字符和小寫字符沒有任何區(qū)別，便于程序的統(tǒng)一處理。,5.2.10 UPPER函數(shù),UPPER函數(shù)用于把指定的字符串中的小寫字符裝換為相應(yīng)的大寫字符。語法格式： UPPER(character_expre

11、ssion) character_expression：指定需要進(jìn)行轉(zhuǎn)換的字符串。 實(shí)例：將字符串中的小寫字符全部轉(zhuǎn)換成大寫字符。 SELECT UPPER(a TO A) 該語句的返回值為“A TO A”。,5.2.11 PATINDEX函數(shù),PATINDEX函數(shù)用于返回指定表達(dá)式中某模式的初始位置，如果在字符串中未能匹配該模式，則返回0。語法格式： PATINDEX(%pattern%,expression) %pattern%： expression： 實(shí)例：返回字符串in在字符串There are many tree in my garden中的初始位置。 SELECT PATINDE

12、X(%in%,There are many tree in my garden) 該語句的返回值為21。,5.2.12 REPALCE函數(shù),PEPLACE函數(shù)用于在一個字符串中用指定的字符串替換另一個字符串。語法格式： REPLACE(string_expresddion1, string_expresddion2, string_expresddion3) string_expresddion1：要搜索的原字符串，可以是字符或二進(jìn)制數(shù)據(jù)類型。 string_expresddion2：原字符串中將要被替換的字符串。 string_expresddion3：指定的進(jìn)行替換的字符串。,5.2.13

13、 REVERSE函數(shù),REVERSE函數(shù)用于返回指定表達(dá)式的逆向形式。語法格式： REVERSE(character_expression) character_expression：指定的用于需要進(jìn)行轉(zhuǎn)換的表達(dá)式，可以是字符串或二進(jìn)制數(shù)據(jù) 實(shí)例：將字符串“A fine day”進(jìn)行整體反轉(zhuǎn)。 SELECT REVERSE(A fine day) 該語句返回值為“yad enif A”，即原字符串的最后一個字符作為新字符串的第一個字符，原字符串的第一個字符作為新字符串的最后一個字符。,5.2.14 STR函數(shù),STR函數(shù)用于將數(shù)字?jǐn)?shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù)。語法格式： STR(float_expres

14、sion,[,length[,decimal]]) float_expression：帶小數(shù)點(diǎn)的近似數(shù)字。 length：包括小數(shù)點(diǎn)、符號、數(shù)字或空格的總長度值，默認(rèn)為10。 decimal：小數(shù)點(diǎn)右邊的位數(shù)。,5.2.15 SUBSTRING函數(shù),SUBSTRING函數(shù)用于獲取指定字符串或二進(jìn)制數(shù)據(jù)的一部分。語法格式： SUBSTRING(expression,start,length) expression：字符串表達(dá)式。 start：指定子串的開始位置。 length：指定子串的長度。,5.3 日期和時間函數(shù),日期和時間函數(shù)主要用于對時間和日期有關(guān)信息的輸入、處理及輸出操作，基本使用方式

15、與其他函數(shù)相同。,5.3.1 DATEADD函數(shù),DATEADD函數(shù)用于返回指定日期加上一個時間段后的新日期時間值。語法格式： DATEADD(datepart , number, date) datepart：返回新值的日期的組成部分，可識別的日期部分為year、month、day、hour、minute、second、millsecond等。 number：與datepart相加的指定值。如果該值為非整數(shù)值，舍棄該值的小數(shù)部分。 date：表達(dá)式，用于返回datetime或日期格式的字符串。,5.3.2 DATENAME函數(shù),DATENAME函數(shù)用于返回指定日期指定部分的字符串。語法格式：

16、 DATENAME(datepart ,date) datepart：返回新值的日期的組成部分。 date：表達(dá)式，用于返回datetime或日期格式的字符串。 實(shí)例：查詢給定日期的星期值。 SELECT DATENAME(weekday,2010-10-10) 該語句的返回值為星期日，即指定時間2010-10-10為星期日。,5.3.3 DATEPART函數(shù),DATENPART函數(shù)用于返回指定日期指定部分的整數(shù)值。語法格式： DATEPART (datepart ,date) datepart：返回新值的日期的組成部分。 date：表達(dá)式，用于返回datetime或日期格式的字符串。 實(shí)例：

17、查詢給定日期的星期值。 SELECT DATEPART(weekday,2010-10-10) 該語句的返回值為1，此處既可以看出該函數(shù)與DATENAME函數(shù)的區(qū)別。,5.3.4 DAY函數(shù),DAY函數(shù)用于返回指定日期的天數(shù)，從功能實(shí)現(xiàn)上進(jìn)行比較，該函數(shù)等價于DATEPART（dd, date）函數(shù)的實(shí)現(xiàn)。語法格式： DAY(day) day：日期表達(dá)式。 實(shí)例：查詢給定日期天數(shù)。 SELECT DAY(2010-10-10) 該語句的返回值為10。,5.3.5 MONTH函數(shù),MONTH函數(shù)用于返回指定日期的月份，從功能實(shí)現(xiàn)上進(jìn)行比較，該函數(shù)等價于DATEPART（mm, date）函數(shù)的實(shí)

18、現(xiàn)。語法格式： MONTH(day) day：日期表達(dá)式。 實(shí)例：查詢給定日期月份。 SELECT MONTH(2010-10-10) 該語句的返回值為10。,5.3.6 YEAR函數(shù),YEAR函數(shù)用于返回指定日期的年份，從功能實(shí)現(xiàn)上進(jìn)行比較，該函數(shù)等價于DATEPART（yy, date）函數(shù)的實(shí)現(xiàn)。語法格式： YEAR(day) day：日期表達(dá)式。 實(shí)例：查詢給定日期的年份。 SELECT YEAR(2010-10-10) 該語句的返回值為2010。,5.3.7 GETDATE函數(shù),GETDATE函數(shù)用于返回系統(tǒng)的日期和時間。語法格式： GETDATE() 參數(shù)：無。 實(shí)例：查詢系統(tǒng)當(dāng)前

19、時間及當(dāng)前天數(shù)。 SELECT GETDATE() SELECT DAY(GETDATE()) 第一條語句返回值為2010-05-30 22:21:45.043，即當(dāng)前的系統(tǒng)日期和時間；第二條語句在取得系統(tǒng)日期和時間的前提下，使用DAY函數(shù)取得當(dāng)前的天數(shù)值。,5.4 數(shù)據(jù)類型轉(zhuǎn)換函數(shù),同時處理不同數(shù)據(jù)類型的值時，SQLServer一般會自動進(jìn)行隱式的類型轉(zhuǎn)換。這種轉(zhuǎn)換對于相近數(shù)據(jù)類型的值是有效的，比如說int型和float型的數(shù)據(jù)，而對于其他的類型，如float型數(shù)據(jù)與字符型數(shù)據(jù)，隱式轉(zhuǎn)換是行不通的，必須使用顯示轉(zhuǎn)換。 T-SQL提供兩個顯示轉(zhuǎn)換函數(shù)，分別是CAST函數(shù)與CONVERT函數(shù)，下

20、面將對這兩個類型轉(zhuǎn)換函數(shù)詳細(xì)介紹。,5.4.1 CAST函數(shù),CAST函數(shù)用于將某種類型的數(shù)據(jù)轉(zhuǎn)換為另一種類型的數(shù)據(jù)。語法格式： CAST (expression AS data_ type) expression：指定的需進(jìn)行轉(zhuǎn)換的表達(dá)式 AS：參數(shù)分隔符 data_ type：目標(biāo)數(shù)據(jù)類型,5.4.2 CONVERT函數(shù),CONVERT函數(shù)用于按照指定的格式將數(shù)據(jù)裝換成另一種類型的數(shù)據(jù)。語法格式： CONVERT (data_ type[ length ], expression [, style]) data_ type：目標(biāo)系統(tǒng)所提供的數(shù)據(jù)類型，bigint和sql_variant。

21、length：數(shù)據(jù)長度 expression：指定的需進(jìn)行轉(zhuǎn)換的表達(dá)式 style：日期格式 實(shí)例：輸出當(dāng)前系統(tǒng)的日期。 SELECT CONVERT(varchar(12), getdate()) 該語句的返回值為05 31 2010。,5.5 聚合函數(shù),聚合函數(shù)能夠?qū)σ唤M值進(jìn)行計算，并返回一個單一值。聚合函數(shù)經(jīng)常與SELECT語句的GROUPBY字句聯(lián)合使用。,5.5.1 AVG函數(shù),AVG函數(shù)用于計算表達(dá)式的平均值。語法格式： AVG([ ALL | DISTINCT ] expression) ALL：對表達(dá)式的所有值進(jìn)行計算，為默認(rèn)設(shè)置。 DISTINCT：去除重復(fù)值后計算平均值。

22、expression：精確數(shù)字或近似數(shù)字?jǐn)?shù)據(jù)類型的表達(dá)式，不允許使用聚合函數(shù)與子查詢。 實(shí)例：計算班級中學(xué)生的平均年齡。 SELECT AVG(stuAge) from studentInfo SELECT AVG(DISTINCT stuAge) from studentInfo 第一條語句返回值為22，第二條語句返回值為23。返回值不同的原因在于第二條語句使用了‘DISTINCT’，去除了數(shù)據(jù)中的重復(fù)值。,5.5.2 COUNT函數(shù),COUNT函數(shù)用于計算總數(shù)。語法格式： COUNT( { [ [ ALL | DISTINCT ] expression ] | * } ) ALL：對表達(dá)式

23、的所有值進(jìn)行計算，為默認(rèn)設(shè)置。 DISTINCT：去除重復(fù)值后計算總數(shù)。 expression：精確數(shù)字或近似數(shù)字?jǐn)?shù)據(jù)類型的表達(dá)式，不允許使用聚合函數(shù)與子查詢。,5.5.3 MAX函數(shù),MAX函數(shù)用于計算組數(shù)據(jù)中的最大值。語法格式： MAX([ ALL | DISTINCT ] expression) ALL：對表達(dá)式中的所有值進(jìn)行計算，為默認(rèn)設(shè)置。 DISTINCT：去除重復(fù)值后計算最大值，對于MAX函數(shù)來說，使用此設(shè)置五任何意義。 expression：可以是常量、列名、函數(shù)等類型，不允許使用聚合函數(shù)與子查詢。 實(shí)例：求出班級中學(xué)生的最大年齡是多少。 SELECT MAX(stuAge)

24、from studentInfo 該語句返回值為24。,5.5.4 MIN函數(shù),MIN函數(shù)用于計算組數(shù)據(jù)中的最小值。語法格式： MAX([ ALL | DISTINCT ] expression) ALL：對表達(dá)式中的所有值進(jìn)行計算，為默認(rèn)設(shè)置。 DISTINCT：去除重復(fù)值后計算最小值。 expression：可以是常量、列名、函數(shù)等類型，不允許使用聚合函數(shù)與子查詢。 實(shí)例：求出班級中學(xué)生的最小年齡是多少。 SELECT MIN(stuAge) from studentInfo 該語句返回值為20。,5.5.5 SUM函數(shù),SUM函數(shù)用于計算表達(dá)式中所有數(shù)據(jù)的和。語法格式： SUM([ AL

25、L | DISTINCT ] expression) ALL：對表達(dá)式中的所有值進(jìn)行計算，為默認(rèn)設(shè)置。 DISTINCT：去除重復(fù)值后計算數(shù)據(jù)和。 expression：可以是常量、列名、函數(shù)等類型，不允許使用聚合函數(shù)與子查詢。,5.6 系統(tǒng)函數(shù),系統(tǒng)函數(shù)可以顯示T-SQL執(zhí)行運(yùn)算后返回的SQL Server服務(wù)器和數(shù)據(jù)庫中有關(guān)值、對象和設(shè)置的特殊信息。,5.6.1 SERVERPROPERTY函數(shù),SERVERPROPERTY函數(shù)用于查詢有關(guān)服務(wù)器實(shí)例的屬性信息。語法格式： SERVERPROPERTY（argument） 其中，argument指定要查詢的信息標(biāo)志。 實(shí)例：查詢服務(wù)器實(shí)例名

26、稱。 SELECT CONVERT(char(15), SERVERPROPERTY(servername)) 該語句返回值為當(dāng)前服務(wù)器所使用的實(shí)例名稱。,5.6.2 DATABASEPROPERTY函數(shù),DATABASEPROPERTY函數(shù)用于查詢指定數(shù)據(jù)庫和屬性名的命名數(shù)據(jù)庫屬性值。語法格式： DATABASEPROPERTY（argument1,argument2） argument：指定數(shù)據(jù)庫。 argument2：要查詢的屬性名稱 實(shí)例：查詢master數(shù)據(jù)庫的‘IsTruncLog’屬性值設(shè)置情況。 SELECT DATABASEPROPERTY(master, IsTruncLo

27、g) 該語句返回值為1，表示該選項設(shè)置成了true。,5.6.3 TYPEOFPROPERTY函數(shù),TYPEOFPROPERTY函數(shù)用于查詢有關(guān)數(shù)據(jù)類型的信息。語法格式： TYPEOFPROPERTY（date_type,property_name） date_type：指定數(shù)據(jù)庫。 property_name：要查詢的屬性名稱 實(shí)例：查詢int與char類型的PRECISION屬性。 SELECT TYPEPROPERTY( int, PRECISION) SELECT TYPEPROPERTY( char, PRECISION) 兩條語句的返回值分別為10、8000。,5.6.4 OBJE

28、CT_ID函數(shù),OBJECT_ID函數(shù)用于查詢數(shù)據(jù)庫對象的ID號。語法格式： OBJECT_ID（object_name,object_type） object_name：指定數(shù)據(jù)庫對象。 object_type：要查詢的屬性名稱 實(shí)例：查詢表studentInfo的ID號。 select object_id(dbo.studentInfo,u) 該語句的返回值為2073058421，如該表不存在則返回NULL。,5.7 文本和圖像函數(shù),文本和圖像函數(shù)用于對文本或圖像輸入值或列執(zhí)行操作，并且提供有關(guān)該值的基本信息。T-SQL中常用的文本函數(shù)有兩個：TEXTPTR函數(shù)和TEXTVALID函數(shù)。,

29、5.7.1 TEXTPTR函數(shù),TEXTPTR函數(shù)用于返回對應(yīng)varbinary格式的text、ntext或者image 列的文本指針值。查找到的文本指針值可應(yīng)用于READTEXT、WRITETEXT和UPDATETEXT語句。語法格式如下： TEXTPTR(column) 其中，column表示將要使用的text、ntext或者image列。 實(shí)例：查詢表studentInfo中stuAdd的十六字節(jié)文本指針。 SELECT TEXTPTR(stuAdd) FROM studentInfo 該語句的返回值為諸如0 xFFFFD107000000004E00000001000000的記錄集。,

30、5.7.2 TEXTVALID函數(shù),TEXTVALID函數(shù)用于檢查特定文本指針是否為有效的text、ntext或image函數(shù)。語法格式如下： TEXTVALID (table.column ,text_ ptr ) table：將要使用表名。 column：要使用的列名。 text_ ptr ：要檢查的文本指針。,5.8 小結(jié),Transact-SQL中函數(shù)代表的是能夠完成某個功能并返回值的一個模塊或者代碼塊，它能夠幫助用于使用簡單代碼實(shí)現(xiàn)復(fù)雜的功能。Transact-SQL支持系統(tǒng)內(nèi)置函數(shù)和用戶定義函數(shù)，本章主要介紹了內(nèi)置函數(shù)，使用這些函數(shù)已經(jīng)基本能夠滿足初級用戶的任務(wù)業(yè)務(wù)需求，自定義函數(shù)將在后續(xù)的章節(jié)中詳細(xì)介紹。,