資料庫函式

SQL2008 運算式：是常量、變數、列或函式等與運算子的任意組合。

1. 字串函式

函式 名稱 引數 示例 說明

ascii(字串運算式)

select ascii('abc') 回傳 97

回傳字串中最左側的字符的ASCII 碼。

char(整數運算式)

select char(100) 回傳 d

把ASCII 碼轉換為字符。

介于0 和 255 之間的整數。如果該整數運算式不在此范圍內，將回傳

NULL 值。

charindex(字串運算式 1, 字串運算式2[,整數運算式])

select charindex('ab','BCabTabD')回傳 3

select charindex('ab','BCabTabD',4)回傳 6

在字串2 中查找字串 1，如果存在回傳第一個匹配的位置，如果不存在回傳0。如果字串 1 和字串 2 中有一個是null 則回傳 null。可以指定在字串

2 中查找的起始位置。

difference(字串運算式 1，字串運算式 2)

select difference('Green','Greene')回傳 4

回傳一個0 到 4 的整數值，指示兩個字符運算式的之間的相似程度。

0 表示幾乎不同或完全不同，

4 表示幾乎相同或完全相同。

注意相似并不代表相等

left(字串運算式，整數運算式)

select left('abcdefg',2) 回傳 ab

回傳字串中從左邊開始指定個數的字符。

right(字串運算式，整數運算式)

select right('abcdefg',2) 回傳 fg

回傳字串中從右邊開始指定個數的字符。

len(字串運算式)

select len('abcdefg')回傳 7

select len('abcdefg

') 回傳 7

回傳指定字串運算式的字符數，其中不包含尾隨空格。

lower(字串運算式)

select lower('ABCDEF')回傳 abcdef

回傳大寫字符資料轉換為小寫的字符運算式。

upper(字串運算式)

select upper('abcdef')回傳 ABCDEF

回傳小寫字符資料轉換為大寫的字符運算式。

ltrim(字串運算式)

select ltrim('

abc')回傳 abc

回傳洗掉了前導空格之后的字符運算式。

rtrim(字串運算式)

select rtrim('abc

')回傳 abc

回傳洗掉了尾隨空格之后的字符運算式。

patindex(字串運算式 1，字串運算式 2)

select patindex('%ab%','123ab456')回傳4

select patindex('ab%','123ab456')回傳0

select patindex('___ab%','123ab456')回傳1

select patindex('___ab_','123ab456')回傳0

在字串運算式

1 中可以使用通配符，此字串的第一個

字符和最后一個字符通常是

%。

%表示任意多個字符，_表示任意字符

回傳字串運算式

2 中字串運算式 1 所指定模式第一次出現的起始位置。沒有找到回傳0

reverse(字串運算式)

select reverse('abcde')回傳 edcba

回傳指定字串反轉后的新字串

space(整數運算式)

select 'a'+space(2)+'b' 回傳 a b

回傳由指定數目的空格組成的字串。

str

(float 型小數[,總長度[,小數點

后保留的位數]])

select str(123.451)回傳

123(123前面有空格)

select str(123.451,3)回傳123

select str(123.451,7,3)回傳123.451

select str(123.451,7,1)回傳123.5

select str(123.451,5,3)回傳123.5

select str(123.651,2)回傳**

回傳由數字轉換成的字串。回傳字符數不到總長度的前面補

空格，超過總長度的截斷小數位。如果需要截斷整數位則回傳

**。

注意在截斷時遵循四舍五入

總長度。它包括小數點、符號、數字以及空格。默認值為

10。

小數點后最多保留

16 位。默認不保留小數點后面的數字

stuff

(字串運算式 1，開始位置，長度，字串運算式 2)

select stuff('abcdef',2,2,'123')

回傳 a123def

在字串運算式

1 中在指定的開始位置洗掉指定長度的字符，

并在指定的開始位置處插入字串運算式

2。回傳新字串

substring(字串運算式，開始位置，長度)

select substring('abcdef',2,2)回傳bc

回傳子字串

replace(字串運算式 1，字串運算式 2，字串運算式 3)

Select replace('abcttabchhabc','abc','123')

回傳 123tt123hh123

用字串運算式

3 替換字串運算式 1 中出現的所有字串表

達式

2 的匹配項。回傳新的字串

2. 日期和時間函式

函式名稱

引數 示例 說明

dateadd(日期部分，數字，日期)

select dateadd(year,45,'1990-12-11')

回傳 2035-12-11 00:00:00.000

select dateadd(month,45,'1990-12-11')

回傳 1994-09-11 00:00:00.000

select dateadd(mm,45,'1990-12-11')

回傳 1994-09-11 00:00:00.000

select dateadd(qq,12,'1990-12-11')

回傳 1993-12-11 00:00:00.000

select dateadd(hh,12,'1990-12-11')

回傳 1990-12-11 12:00:00.000

select dateadd(yy,-12,'1990-12-11')

回傳 1978-12-11 00:00:00.000

回傳給指定日期加上一個時間間隔后的新的日期值。

數字：用于與指定的日期部分相加的值。如果指定了非整數值，

則將舍棄該值的小數部分，舍棄時不遵循四舍五入。

日期：指定的原日期

在此函式中

dw，dy，dd 效果一樣都表示天

datediff(日期部分，開始日期，結束日期)

select

datediff(yy,'1990-12-11','2008-9-10')

回傳兩個指定日期的指定日期部分的差的整數值。

在計算時由結束日期減去開始日期

回傳 18

selectdatediff(mm,'2007-12-11','2008-9-10')

回傳 9

在此函式中

dw，dy，dd 效果一樣都表示天

datename(日期部分，日期)

select datename(mm,'2007-12-11')回傳 12

select datename(dw,'2007-12-11')回傳星期二

select datename(dd, '2007-12-11')回傳 11

回傳表示指定日期的指定日期部分的字串。

dw 表示一星期中星期幾，wk 表示一年中的第幾個星期

dy 表示一年中的第幾天

datepart

(日期部分，日期)

select datepart(mm,'2007-12-11')回傳 12

select datepart(dw,'2007-12-11')回傳 3

select datepart(dd, '2007-12-11')回傳 11

回傳表示指定日期的指定日期部分的整數。

wk 表示一年中的第幾個星期

dy 表示一年中的第幾天,

dw 表示一星期中星期幾，回傳整數默認 1 為星期天

getdate無引數

select getdate()

回傳 2009-04-28 18:57:24.153

回傳當前系統日期和時間。

day

(日期)

select day('2007-12-11')回傳 11

回傳一個整數，表示指定日期的天的部分。

等價于

datepart(dd, 日期)

month

(日期)

select month('2007-12-11')回傳 12

回傳一個整數，表示指定日期的月的部分。

等價于

datepart(mm, 日期)

year

(日期)

select year('2007-12-11')回傳2007

回傳一個整數，表示指定日期的年的部分。

等價于

datepart(yy, 日期)

getutcdate

無引數

select getutcdate()

回傳2009-04-28 10:57:24.153

回傳表示當前的

UTC(世界標準時間)時間。即格林尼治時間

(

GMT)

3. 日期部分(指定要回傳新值的日期的組成部分。下表列出了 Microsoft SQL Server 2008 可識別的日期部分及其縮寫。)

日期部分

含義

縮寫year年yy, yyyy

quarter季qq, q

month月mm, m

dayofyear天(請看函式中的說明)dy, y

day天(請看函式中的說明)dd, d

week星期wk, ww

weekday天(請看函式中的說明)dw, w

hour小時hh

minute分鐘mi, n

second秒ss, s

millisecond毫秒ms

4. 數學函式

函式名稱 引數 示例 說明

abs(數值運算式)

select abs(-23.4)回傳 23.4

回傳指定數值運算式的絕對值(正值)

pi無引數

select pi()回傳 3.14159265358979

回傳π的值

cos(浮點運算式)

select cos(pi()/3)回傳 0.5

回傳指定弧度的余弦值

sin(浮點運算式)

select sin(pi()/6)回傳 0.5

回傳指定弧度的正弦值

cot(浮點運算式)

select cot(pi()/4)回傳 1

回傳指定弧度的余切值

tan(浮點運算式)

select tan(pi()/4)回傳 1

回傳指定弧度的正切值

acos(浮點運算式)

select acos(0.5)回傳 1.0471975511966

回傳其余弦是所指定的數值運算式的弧度，求反余弦

asin(浮點運算式)

select asin(0.5)回傳 0.523598775598299

回傳其正弦是所指定的數值運算式的弧度，求反正弦

atan(浮點運算式)

select atan(1)回傳0.785398163397448

回傳其正切是所指定的數值運算式的弧度，求反正切

degrees

(數值運算式)

select degrees(pi()/4) 回傳45

回傳以弧度指定的角的相應角度。

radians(數值運算式)

select radians(180.0)

回傳3.1415926535897931

回傳指定度數的弧度值。注意如果傳入整數值則回傳的結果將

會省略小數部分

exp(浮點運算式)

select exp(4)回傳54.5981500331442

回傳求

e 的指定次冪，e=2.718281…

log(浮點運算式)

select log(6)回傳1.79175946922805

回傳以 e 為底的對數，求自然對數。

Log10(浮點運算式)

select log10(100)回傳2

回傳以 10 為底的對數

ceiling(數值運算式)

select ceiling(5.44)回傳6

select ceiling(-8.44)回傳-8

回傳大于或等于指定數值運算式的最小整數。

floor(數值運算式)

select floor(5.44)回傳5

select floor(-8.44)回傳-9

回傳小于或等于指定數值運算式的最大整數。

power(數值運算式 1，數值運算式 2)

select power(5,2)回傳25

回傳數值運算式

1 的數值運算式 2 次冪

sqrt(數值運算式)

select sqrt(25)回傳5

回傳數值運算式的平方根

sign(數值運算式)

select sign(6)回傳1

select sign(-6)回傳-1

select sign(0)回傳0

運算式為正回傳+1

運算式為負回傳-1

運算式為零回傳0

rand([整數運算式])

select rand(100)回傳0.715436657367485

select rand()回傳0.28463380767982

select rand()回傳0.0131039082850364

回傳從0 到 1 之間的隨機 float 值。整數運算式為種子，使用相同的種子產生亂數相同。即使用同一個種子值重復呼叫RAND() 會回傳相同的結果。不指定種子則系統會隨機生成種子。

round(數值運算式[,長度[,操作方式]])

select round(1236.555,2)回傳1236.560

select round(1236.555,2,1)回傳1236.550

select round(1236.555,0)回傳1237.000

select round(1236.555,-1)回傳1240.000

select round(1236.555,-1,1)回傳1230.000

select round(1236.555,-2)回傳1200.000

select round(1236.555,-3)回傳1000.000

select round(1236.555,-4)回傳0.000

select round(5236.555,-4)出現錯誤

select round(5236.555,-4,1)回傳0.000

回傳一個數值，舍入到指定的長度。注意回傳的數值和原數值

的總位數沒有變化。

長度：舍入精度。如果長度為正數，則將數值舍入到長度指定的小數位數。如果長度為負數，則將數值小數點左邊部分舍入到長度指定的長度。注意如果長度為負數并且大于小數點前的數字個數，則將回傳0。如果長度為負數并且等于小數點前的數字個數且操作方式為四舍五入時，最前面的一位小于5 回傳 0，大于等于 5 導致錯誤出現，如果操作方法不是四舍五入時則不會出現錯誤，回傳結果一律為0。

操作方式：默認為 0 遵循四舍五入，指定其他整數值則直接截斷。

5. 資料型別轉換函式

(以下兩種函式功能類似,但是 convert 在進行日期轉換時還提供了豐富的樣式，cast 只能進行普通的日期轉換)

函式名稱 引數 示例 描述

convert(資料型別[(長度)]，運算式[，樣式])

select convert(nvarchar,123) 回傳123

select N'年齡：'+convert(nvarchar,23)

回傳 年齡：23(注意：如果想要在結果中正確顯示中

文需要在給定的字串前面加上 N，加 N 是為了使資料庫識別Unicode 字符)

select convert(nvarchar ,getdate())

將一種資料型別的運算式顯式轉換為另一種資料型別的運算式。長度：如果資料型別允許設定長度，可以設定長度，例如varchar(10)樣式：用于將日期型別資料轉換為字符資料型別的日期格式的樣式。見下表

回傳 04 28 2009 10:21PM

select convert(nvarchar ,getdate(),101)

回傳 04/28/2009

select convert(nvarchar ,getdate(),120)

回傳 2009-04-28 12:22:21

Select convert(nvarchar(10) ,getdate(),120)

回傳 2009-04-28

cast(運算式 as 資料型別[(長度)])

select cast(123 as nvarchar)回傳 123

select N'年齡:'+cast(23 as nvarchar)

回傳 年齡：23

將一種資料型別的運算式顯式轉換為另一種資料型別的運算式。日期型別資料轉為字符資料型別的日期格式的部分樣式表當兩個不同資料型別的運算式用運算子組合后，資料型別優先級規則指定將優先級較低的資料型別優先轉換為優先級較高的型。 如果此轉換不是所支持的隱式轉換，則回傳錯誤。 當兩個運算元運算式具有相同的資料型別時，運算的結果便為該資料型別。如果需要把優先級高的資料型別轉換優先級低的資料型別時需要使用資料型別轉換函式進行顯示轉換。

SQL Server 2005 對資料型別使用以下優先級順序(先高后低)：

不帶世紀數位 (yy)

帶世紀數位 (yyyy)

標準

輸入/輸出-

0 或 100

默認設定mon dd yyyy hh:miAM(或 PM)1 101

美國

mm/dd/yyyy 2 102 ANSI yy.mm.dd 3103

英國/法國

dd/mm/yy 4 104

德國

dd.mm.yy 5 105

意大利

dd-mm-yy 120

ODBC 規范

yyyy-mm-ddhh:mi:ss(24h)

1、用戶定義資料型別(最高) 2、sql_variant 3、xml 4、datetime 5、smalldatetime 6、float 7、real 8、decimal 9、money 10、smallmoney 11、bigint 12、int 13、smallint 14、tinyint 15、bit 16、ntext 17、text 18、image 19、timestamp 20、uniqueidentifier

21、nvarchar(包括 nvarchar(max)) 22、nchar 23、varchar (包括 varchar(max)) 24、char 25、varbinary(包括 varbinary(max)) 26、binary(最低)

系統函式

函式名稱 引數 示例 描述

newid無引數

select newid()

回傳 2E6861EF-F4DB-4FFE-85EA-638242F2E5F2

select newid()

回傳 09BBDE6F-47C2-4E2E-81E8-AFC50592280C

回傳一個GUID(全域唯一表示符)值

isnumeric(任意運算式)

select isnumeric(1111)回傳1

select isnumeric('123rr')回傳0

select isnumeric('123')回傳 1

判斷運算式是否為數值型別或者是否可以轉換成數值。是回傳1，不是回傳 0

isnull(任意運算式 1，任意運算式 2)

select isnull(null,N'沒有值')回傳 沒有值

select isnull(N'具體的值',N'沒有值')

回傳 具體的值

如果任意運算式

1 不為 NULL，則回傳它的值;否則，在將任意運算式

2 的型別轉換為任意運算式 1 的型別(如果這兩個型別不同)后，回傳任意表式

2 的值。

isdate(任意運算式)

select isdate(getdate()) 回傳1

select isdate('1988-1-1')回傳1

select isdate('198')回傳0

確定輸入運算式是否為有效日期或可轉成有效的日期。是回傳1，不是回傳 0

排名函式

排名函式的常用使用格式：函式名() over (order by 列名 [asc|desc][,列名……])

注意：回傳結果集會根據使用排名函式時指定的列進行排序，因此不要在 from 子句后面再次使用 order by，會導致排名混亂。

函式名稱

引數 示例 描述

row_number無引數

select roductID,[Name],ListPrice, row_number()

over(order by ListPrice desc) as rank from Production.Product

為結果集內每一行進行編號，從1開始后面行依次加 1。

rank無引數

select ProductID,[Name],ListPrice,rank()

over(order by ListPrice desc) as rank

from Production.Product

如果兩個或多個行與一個排名關聯，則每個關聯行將得到相同的排名，排名不連續。例如，如果有價格最高的兩種產品的價格相同，它們將列第一。由于已有兩行排名在前，所以具有下一個最高價格的產品將排名第三。該排名等于該行之前的所有行數加一。因此，

RANK 函式并不總回傳連續整數。

dense_rank

無引數

select

ProductID,[Name],ListPrice,dense_rank()

over(order by ListPrice desc) as rank

from Production.Product

如果兩個或多個行與一個排名關聯，則每個關聯行將得到相同的排名，排名連續。

例如，如果有價格最高的兩種產品的價格相同，它們將并列第一，下一個最高價

格的產品將排名第二。因此，

DENSE_RANK 函式回傳的數字沒有間斷，并且始

終具有連續的排名。

聚合函式

聚合函式對一組值執行計算

,并回傳單個值。除了 COUNT 以外，聚合函式都會忽略空值。聚合函式經常與 SELECT 陳述句的 GROUP BY 子

句一起使用。聚合函式的常用使用格式：函式名([all|distinct] 運算式)

all:默認值，對所有的值進行聚合函式運算包含重復值。distinct: 消

除重復值后進行聚合函式運算。

函式名稱 示例 描述

avg

SELECT avg(VacationHours)as '平均休假小時數'

FROM HumanResources.Employee

WHERE Title LIKE 'Vice President%'

回傳 25

回傳組中各值的平均值。空值將被忽略。 運算式為數值運算式。

count

SELECT count(*)FROM Production.Product 回傳504

SELECT count(Color)FROM Production.Product 回傳 256

SELECT count(distinct Color)FROM Production.Product

回傳 9

回傳組中的項數。COUNT(*) 回傳組中的項數。包括 NULL 值和重復項。如果指定

運算式則忽略空值。運算式為任意運算式。

min

select min(ListPrice)from Production.Product回傳0

回傳組中的最小值。空值將被忽略。運算式為數值運算式，字串運算式，日期。

max

select max(ListPrice) from Production.Product

回傳3578.27

回傳組中的最大值。空值

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/87848.html

標籤：軟件測試

上一篇：SqlServer常用欄位型別

下一篇：菜鳥想問個關于Gradle的問題