軟件開發(fā)魔典從入門到項目實踐項目實踐基礎知識核心應用高級應用行業(yè)應用項目實踐軟件開發(fā)魔典SQLServer從入門到項目實踐清華大學出版社（超值版）核心技術本課時學習重點了解什么是SQL的函數掌握數學函數的用法掌握字符串函數的用法掌握時間和日期函數的用法掌握條件函數的用法掌握系統信息函數的用法聚慕課教育研發(fā)中心學習、授課與教學PPT課件SQL函數簡介1.單行函數：對每一個函數應用在表的記錄中時，只能輸入一行結果，返回一個結果，比如：MOD(x,y)，返回x除以y的余數（x和y可以是兩個整數，也可以是表中的整數列），常用的單行函數有以下幾種。字符函數：對字符串操作。數字函數：對數字進行計算，返回一個數字。轉換函數：可以將一種數據類型轉換為另外一種數據類型。日期函數：對日期和時間進行處理。2.聚合函數：聚合函數同時可以對多行數據進行操作，并返回一個結果。比如SUM(x)返回結果集中x列的總合。聚慕課教育研發(fā)中心學習、授課與教學PPT課件字符串函數ASCII()函數CHAR()函數CHARINDEX函數LEFT()函數RIGHT()函數LEN函數LTRIM()函數RTRIM()函數LOWER()函數UPPER()函數REPLACE函數REVERSE函數STR()函數SUBSTRING()函數聚慕課教育研發(fā)中心學習、授課與教學PPT課件ASCII()函數ASCII(character_expression)函數用于返回字符串表達式中最左側字符的ASCII代碼值。參數character_expression必須是一個char或varchar類型的字符串表達式。新建查詢，運行下面的例子?！纠?.1】查看指定字符的ASCII值，輸入語句如下。SELECTASCII('s'),ASCII('sql'),ASCII(1);執(zhí)行結果如圖6-1所示。

圖6-1ASCII函數聚慕課教育研發(fā)中心學習、授課與教學PPT課件CHAR()函數CHAR(integer_expression)函數將整數類型的ASCII值轉換為對應的字符，integer_expression是一個介于0和255之間的整數。如果該整數表達式不在此范圍內，將返回NULL值?！纠?.2】查看ASCII值115和49對應的字符，輸入語句如下。SELECTCHAR(115),CHAR(49);執(zhí)行結果如圖6-2所示。

圖6-2CHAR函數

聚慕課教育研發(fā)中心學習、授課與教學PPT課件CHARINDEX函數CHARINDEX(str1,str,[start])函數返回子字符串str1在字符串str中的開始位置，start為搜索的開始位置，如果指定start參數，則從指定位置開始搜索；如果不指定start參數或者指定為0或者為負值，則從字符串開始位置搜索?！纠?.3】使用CHARINDEX函數查找字符串中指定子字符串的開始位置，輸入語句如下。SELECTCHARINDEX('a','banana'),CHARINDEX('a','banana',4),CHARINDEX('na','banana',4);執(zhí)行結果如圖6-3所示。

圖6-3CHARINDEX函數聚慕課教育研發(fā)中心學習、授課與教學PPT課件LEFT()函數

LEFT(character_expression,integer_expression)函數返回字符串左邊開始指定個數的字符串、字符或二進制數據表達式。character_expression是字符串表達式，可以是常量、變量或字段。integer_expression為正整數，指定character_expression將返回的字符數。聚慕課教育研發(fā)中心學習、授課與教學PPT課件RIGHT()函數與LEFT()函數相反，RIGHT(character_expression,integer_expression)返回字符串character_expression最右邊integer_expression個字符。聚慕課教育研發(fā)中心學習、授課與教學PPT課件LEN函數聚慕課教育研發(fā)中心學習、授課與教學PPT課件返回字符表達式中的字符數。如果字符串中包含前導空格和尾隨空格，則函數會將它們包含在計數內。LEN對相同的單字節(jié)和雙字節(jié)字符串返回相同的值。LTRIM()函數LTRIM(character_expression)用于去除字符串左邊多余的空格。字符數據表達式character_expression是一個字符串表達式，可以是常量、變量，也可以是字符字段或二進制數據列?！纠?.7】使用LTRIM函數刪除字符串左邊的空格，輸入語句如下。SELECT'('+'book'+')','('+LTRIM('book')+')';執(zhí)行結果如圖6-7所示。

圖6-7LTRIM函數聚慕課教育研究中心學習、授課與教學PPT課件RTRIM()函數RTRIM(character_expression)用于去除字符串右邊多余的空格。字符數據表達式character_expression是一個字符串表達式，可以是常量、變量，也可以是字符字段或二進制數據列。聚慕課教育研究中心學習、授課與教學PPT課件LOWER()函數LOWER(character_expression)將大寫字符數據轉換為小寫字符數據后返回字符表達式。character_expression是指定要進行轉換的字符串?！纠?.9】使用LOWER函數將字符串中所有字母字符轉換為小寫，輸入語句如下。SELECTLOWER('BEAUTIFUL'),LOWER('Well');執(zhí)行結果如圖6-9所示

圖6-9LOWER函數聚慕課教育研究中心學習、授課與教學PPT課件UPPER()函數UPPER(character_expression)將小寫字符數據轉換為大寫字符數據后返回字符表達式。character_expression是指定要進行轉換的字符串。【例6.10】使用UPPER函數或者UCASE函數將字符串中所有字母字符轉換為大寫，輸入語句如下。SELECTUPPER('black'),UPPER('BLacK');執(zhí)行結果如圖6-10所示。

圖6-10UPPER函數聚慕課教育研究中心學習、授課與教學PPT課件REPLACE函數REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1?！纠?.11】使用REPLACE函數進行字符串替代操作，輸入語句如下。SELECTREPLACE('','x','w');執(zhí)行結果如圖6-11所示。

圖6-11REPLACE函數聚慕課教育研究中心學習、授課與教學PPT課件REVERSE函數REVERSE(s)將字符串s反轉，返回的字符串的順序和s字符順序相反?！纠?.12】使用REVERSE函數反轉字符串，輸入語句如下。SELECTREVERSE('abc');執(zhí)行結果如圖6-12所示。

圖6-12REVERSE函數聚慕課教育研究中心學習、授課與教學PPT課件STR()函數STR(float_expression[,length[,decimal]])函數用于將數值數據轉換為字符數據。float_expression是一個帶小數點的近似數字（float）數據類型的表達式。length表示總長度。它包括小數點、符號、數字以及空格。默認值為10。decimal指定小數點后的位數。decimal必須小于或等于16。如果decimal大于16，則會截斷結果，使其保持為小數點后有16位。聚慕課教育研究中心學習、授課與教學PPT課件SUBSTRING()函數SUBSTRING(value_expression,start_expression,length_expression)函數返回字符表達式、二進制表達式、文本表達式或圖像表達式的一部分。value_expression是character、binary、text、ntext或image表達式。start_expression指定返回字符的起始位置的整數或表達式。如果start_expression小于0，會生成錯誤并終止語句。如果start_expression大于值表達式中的字符數，將返回一個零長度的表達式。length_expression是正整數或指定要返回的value_expression的字符數的表達式。如果length_expression是負數，會生成錯誤并終止語句。如果start_expression與length_expression的總和大于value_expression中的字符數，則返回整個值表達式。聚慕課教育研究中心學習、授課與教學PPT課件數學函數絕對值函數ABS(x)和返回圓周率的函數PI()平方根函數SQRT(x)獲取隨機數的函數RAND()和RAND(x)四舍五入函數ROUND(x,y)符號函數SIGN(x)獲取整數的函數CEILING(x)和FLOOR(x)冪運算函數POWER(x,y)、SQUARE(x)和EXP(x)對數運算函數LOG(x)和LOG10(x)角度與弧度相互轉換的函數RADIANS(x)和DEGREES(x)正弦函數SIN(x)和反正弦函數ASIN(x)余弦函數COS(x)和反余弦函數ACOS(x)正切函數、反正切函數和余切函數聚慕課教育研究中心學習、授課與教學PPT課件絕對值函數ABS(x)和返回圓周率的函數PI()ABS(X)返回X的絕對值?！纠?.15】求2，-3.3和-33的絕對值，輸入語句如下。SELECTABS(2),ABS(-3.3),ABS(-33);執(zhí)行結果如圖6-15所示。正數的絕對值為其本身，2的絕對值為2；負數的絕對值為其相反數，-3.3的絕對值為3.3；-33的絕對值為33。PI()返回圓周率π的值。默認的顯示小數位數是6位。

圖6-15ABS函數聚慕課教育研究中心學習、授課與教學PPT課件平方根函數SQRT(x)SQRT(x)返回非負數x的二次方根?！纠?.17】求9，40的二次平方根，輸入語句如下。SELECTSQRT(9),SQRT(40);執(zhí)行結果如圖6-17所示。

圖6-17SQRT函數聚慕課教育研究中心學習、授課與教學PPT課件獲取隨機數的函數RAND()和RAND(x)RAND(x)返回一個隨機浮點值v，范圍在0到1之間（即0≤v≤1.0）。若指定一個整數參數x，則它被用作種子值，使用相同的種子數將產生重復序列。如果用同一種子值多次調用RAND函數，它將返回同一生成值。聚慕課教育研究中心學習、授課與教學PPT課件四舍五入函數ROUND(x,y)ROUND(x,y)返回最接近于參數x的數，其值保留到小數點后面y位，若y為負值，則將保留x值到小數點左邊y位。聚慕課教育研究中心學習、授課與教學PPT課件符號函數SIGN(x)SIGN(x)返回參數的符號，x的值為負、零或正時，返回結果依次為-1、0或1。【例6.21】使用SIGN函數返回參數的符號，輸入語句如下。SELECTSIGN(-21),SIGN(0),SIGN(21);執(zhí)行結果如圖6-21所示。

圖6-21SIGN函數聚慕課教育研究中心學習、授課與教學PPT課件獲取整數的函數CEILING(x)和FLOOR(x)CEILING(x)返回不小于x的最小整數值。【例6.22】使用CEILING函數返回最小整數，輸入語句如下。SELECTCEILING(-3.35),CEILING(3.35);執(zhí)行結果如圖6-22所示。

圖6-22CEILING函數-3.35為負數，不小于-3.35的最小整數為-3，因此返回值為-3；不小于3.35的最小整數為4，因此返回值為4。FLOOR(x)返回不大于x的最大整數值。聚慕課教育研究中心學習、授課與教學PPT課件冪運算函數POWER(x,y)、SQUARE(x)和EXP(x)POWER(x,y)函數返回x的y次乘方的結果值?！纠?.24】使用POWER函數進行乘方運算，輸入語句如下。SELECTPOWER(2,2),POWER(2.00,-2);執(zhí)行結果如圖6-24所示。

圖6-24POWER函數可以看到，POWER(2,2)返回2的2次方，結果是4；POWER(2,-2)返回2的-2次方，結果為4的倒數，即0.25。SQUARE(x)返回指定浮點值x的平方。聚慕課教育研究中心學習、授課與教學PPT課件對數運算函數LOG(x)和LOG10(x)LOG(x)返回x的自然對數，x相對于基數e的對數?！纠?.27】使用LOG(x)函數計算自然對數，輸入語句如下。SELECTLOG(3),LOG(6);執(zhí)行結果如圖6-27所示。

圖6-27LOG函數聚慕課教育研究中心學習、授課與教學PPT課件角度與弧度相互轉換的函數RADIANS(x)和DEGREES(x)RADIANS(x)將參數x由角度轉化為弧度?！纠?.29】使用RADIANS函數將角度轉換為弧度，輸入語句如下。SELECTRADIANS(90.0),RADIANS(180.0);執(zhí)行結果如圖6-29所示。

圖6-29RADIANS函數聚慕課教育研究中心學習、授課與教學PPT課件正弦函數SIN(x)和反正弦函數ASIN(x)SIN(x)返回x的正弦，其中x為弧度值。【例6.31】使用SIN函數計算正弦值，輸入語句如下。SELECTSIN(PI()/2),ROUND(SIN(PI()),0);執(zhí)行結果如圖6-31所示。

圖6-31SIN函數聚慕課教育研究中心學習、授課與教學PPT課件余弦函數COS(x)和反余弦函數ACOS(x)COS(x)返回x的余弦，其中x為弧度值?！纠?.33】使用COS函數計算余弦值，輸入語句如下。SELECTCOS(0),COS(PI()),COS(1);執(zhí)行結果如圖6-33所示。

圖6-33COS函數由結果可以看到，COS(0)值為1；COS(PI())值為-1；COS(1)值為0.54030230586814。ACOS(x)返回x的反余弦，即余弦是x的值。若x不在-1到1的范圍之內，則返回NULL。聚慕課教育研究中心學習、授課與教學PPT課件正切函數、反正切函數和余切函數TAN(x)返回x的正切，其中x為給定的弧度值?！纠?.35】使用TAN函數計算正切值，輸入語句如下。SELECTTAN(0.3),ROUND(TAN(PI()/4),0);執(zhí)行結果如圖6-35所示。

圖6-35TAN函數ATAN(x)返回x的反正切，即正切為x的值。聚慕課教育研究中心學習、授課與教學PPT課件日期和時間函數獲取系統當前日期的函數GETDATE()返回UTC日期的函數GETUTCDATE()獲取天數的函數DAY(d)獲取月份的函數MONTH(d)獲取年份的函數YEAR(d)獲取日期中指定部分字符串值的函數DATENAME(dp,d)獲取日期中指定部分的整數值的函數DATEPART(dp,d)計算日期和時間的函數DATEADD(dp,num,d)聚慕課教育研究中心學習、授課與教學PPT課件獲取系統當前日期的函數GETDATE()GETDATE()函數用于返回當前數據庫系統的日期和時間，返回值的類型為datetime?！纠?.38】使用日期函數獲取系統當期日期，輸入語句如下。SELECTGETDATE();執(zhí)行結果如圖6-38所示。

圖6-38GETDATE函數這里返回的值為筆者電腦上的當前系統時間。聚慕課教育研究中心學習、授課與教學PPT課件返回UTC日期的函數GETUTCDATE()UTCDATE()函數返回當前UTC（世界標準時間）日期值。【例6.39】使用UTCDATE()函數返回當前UTC日期值，輸入語句如下。SELECTGETUTCDATE();執(zhí)行結果如圖6-39所示。

圖6-39UTCDATE函數對比GETDATE()函數的返回值，可以看到，因為讀者位于東8時區(qū)，所以當前系統時間比UTC提前8個小時，所以這里顯示的UTC時間需要減去8個小時的時差。聚慕課教育研究中心學習、授課與教學PPT課件獲取天數的函數DAY(d)DAY(d)函數用于返回指定日期的d是一個月中的第幾天，范圍是從1到31，該函數在功能上等價于DATEPART(dd,d)?！纠?.40】使用DAY函數返回指定日期中的天數，輸入語句如下。SELECTDAY('2018-11-1201:01:01');執(zhí)行結果如圖6-40所示。

圖6-40DAY函數返回結果為12，即11月中的第12天。聚慕課教育研究中心學習、授課與教學PPT課件獲取月份的函數MONTH(d)MONTH(d)函數返回指定日期d中月份的整數值。【例6.41】使用MONTH函數返回指定日期中的月份，輸入語句如下。SELECTMONTH('2018-04-1201:01:01');執(zhí)行結果如圖6-41所示。

圖6-41MONTH函數聚慕課教育研究中心學習、授課與教學PPT課件獲取年份的函數YEAR(d)YEAR(d)函數返回指定日期d中年份的整數值?！纠?.42】使用YEAR函數返回指定日期對應的年份，輸入語句如下。SELECTYEAR('2020-02-03'),YEAR('2018-02-03');執(zhí)行結果如圖6-42所示。

圖6-42YEAR函數聚慕課教育研究中心學習、授課與教學PPT課件獲取日期中指定部分字符串值的函數DATENAME(dp,d)DATENAME(dp,d)根據dp指定返回日期中相應部分的值，例如YEAR返回日期中的年份值，MONTH返回日期中的月份值，dp其他可以取的值有：quarter、dayofyear、day、week、weekday、hour、minute、second等?！纠?.43】使用DATENAME函數返回日期中的指定部分的日期字符串值，輸入語句如下。SELECTDATENAME(year,'2018-11-1201:01:01'),DATENAME(weekday,'2018-11-1201:01:01'),DATENAME(dayofyear,'2018-11-1201:01:01');執(zhí)行結果如圖6-43所示。

圖6-43DATENAME函數由結果可以看到，這里的3個DATENAME函數分別返回指定日期值中的年份值、星期值和該日是一年中的第幾天。聚慕課教育研究中心學習、授課與教學PPT課件獲取日期中指定部分的整數值的函數DATEPART(dp,d)DATEPART(dp,d)函數返回指定日期中相應的部分的整數值。dp的取值與DATENAME函數中的相同。【例6.44】使用DATEPART函數返回日期中指定部分的整數值，輸入語句如下。SELECTDATEPART(year,'2018-11-1201:01:01'),DATEPART(month,'2018-11-1201:01:01'),DATEPART(dayofyear,'2018-11-1201:01:01');執(zhí)行結果如圖6-44所示。

圖6-44DATEPART函數聚慕課教育研究中心學習、授課與教學PPT課件計算日期和時間的函數DATEADD(dp,num,d)DATEADD(dp,num,d)函數用于執(zhí)行日期的加運算，返回指定日期值加上一個時間段后的新日期。dp指定日期中進行加法運算的部分值，例如year、month、day、hour、minute、second、millsecond等；num指定與dp相加的值，如果該值為非整數值，將舍棄該值的小數部分；d為執(zhí)行加法運算的日期?！纠?.45】使用DATEADD函數執(zhí)行日期加操作，輸入語句如下。SELECTDATEADD(year,1,'2018-11-1201:01:01'),DATEADD(month,2,'2018-11-1201:01:01'),DATEADD(hour,1,'2018-11-1201:01:01')執(zhí)行結果如圖6-45所示。

圖6-45DATEADD函數DATEADD(year,1,'2011-11-1201:01:01')表示年值增加1，2011加1之后為2014；DATEADD(month,2,'2011-11-1201:01:01')表示月份值增加2，11月增加2個月之后為1月，同時，年值增加1，結果為2014-01-12；DATEADD(hour,1,'2011-11-1201:01:01')表示時間部分的小時數增加1。聚慕課教育研究中心學習、授課與教學PPT課件轉換函數CAST函數CONVERT函數聚慕課教育研究中心學習、授課與教學PPT課件CAST函數CAST(xAStype)函數可以將一個類型的值轉換為另一個類型的值?！纠?.46】使用CAST函數進行數據類型的轉換，輸入語句如下。SELECTCAST('181231'ASDATE),CAST(100ASCHAR(3));執(zhí)行結果如圖6-46所示。

圖6-46CAST和CONVERT函數可以看到，CAST('161231'ASDATE)將字符串值轉換為了相應的日期值；CAST(100ASCHAR(3))將整數數據100轉換為帶有3個顯示寬度的字符串類型，結果為字符串“100”；聚慕課教育研究中心學習、授課與教學PPT課件CONVERT函數CONVERT(type,x)函數也可以將一個類型的值轉換為另一個類型的值?！纠?.47】使用CAST和CONVERT函數進行數據類型的轉換，輸入語句如下。SELECTCONVERT(TIME,'2018-05-0112:11:10');執(zhí)行結果如圖6-47所示。

圖6-47CONVERT函數由結果可以得知，CONVERT('2018-05-0112:11:10',TIME)將datetime類型的值，轉換為time類型值，結果為“12:11:10.0000000”。聚慕課教育研究中心學習、授課與教學PPT課件顯示系統信息函數返回數據庫的名稱OBJECT_ID函數返回表中指定字段的長度值返回表中指定字段的名稱返回數據庫用戶名聚慕課教育研究中心學習、授課與教學PPT課件返回數據庫的名稱DB_NAME(database_id)函數返回數據庫的名稱。其返回值類型為nvarchar(128)。database_id是smallint類型的數據。如果沒有指定database_id，則返回當前數據庫的名稱?！纠?.48】返回指定ID的數據庫的名稱，輸入語句如下。USEmasterSELECTDB_NAME(),DB_NAME(DB_ID('test_db'));執(zhí)行結果如圖6-48所示。

圖6-48DB_NAME函數USE語句將master選擇為當前數據庫，因此DB_NAME()返回值為當前數據庫master；DB_NAME(DB_ID(‘test_db’))返回值為test_db本身。聚慕課教育研究中心學習、授課與教學PPT課件OBJECT_ID函數OBJECT_ID(database_name.schema_name.object_name，object_type)函數返回數據庫對象的編號。其返回值類型為int。object_name為要使用的對象，它的數據類型為varchar或nvarchar。如果object_name的數據類型為varchar，則它將隱式轉換為nvarchar?？梢赃x擇是否指定數據庫和架構名稱。object_type指定架構范圍的對象類型?！纠?.49】返回test_db數據庫中stu_info表的對象ID。SELECTOBJECT_ID('test_db.dbo.stu_info');執(zhí)行結果如圖6-49所示。

圖6-49OBJECT_ID函數聚慕課教育研究中心學習、授課與教學PPT課件返回表中指定字段的長度值COL_LENGTH(table,column)函數返回表中指定字段的長度值。其返回值為INT類型。table為要確定其列長度信息的表的名稱，是nvarchar類型的表達式。column為要確定其長度的列的名稱,是nvarchar類型的表達式?！纠?.50】顯示test_db數據庫中stu_info表中的s_name字段長度，輸入語句如下