SQL Server数据库培训（SQL篇）----集合运算及常用函数

1.             集合运算及常用函数

1.1          字符转换函数

1.1.1             ASCII ()

返回字符表达式最左端字符的ASCII 码值。在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。

SELECT ASCII('iTalkbb')

----------

 105       

1.1.2             CHAR ()

将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值，CHAR（） 返回NULL 。

SELECT char(105)

----------

 i  

1.1.3             LOWER ()

LOWER()将字符串全部转为小写；

SELECT lower('iTalkbb'),upper('iTalkbb')

----------  ----------

 italkbb     ITALKBB   

1.1.4             UPPER ()

UPPER()将字符串全部转为大写。

SELECT lower('iTalkbb'),upper('iTalkbb')

----------  ----------

 italkbb     ITALKBB   

1.1.5             STR ()

把数值型数据转换为字符型数据。

STR (<float_expression>[，length[， <decimal>]])

length 指定返回的字符串的长度，decimal 指定返回的小数位数。如果没有指定长度，缺省的length 值为10， decimal 缺省值为0。

当length 或者decimal 为负值时，返回NULL；

当length 小于小数点左边（包括符号位）的位数时，返回length 个*；

先服从length ，再取decimal ；

当返回的字符串位数小于length ，左边补足空格。

SELECT STR(123.45, 6, 1);

----------

  123.5  

1.2          去空格函数

1.2.1             LTRIM ()

把字符串头部的空格去掉。

SELECT ltrim('    iTalkBB')

----------

 iTalkBB 

1.2.2             RTRIM ()

把字符串尾部的空格去掉。

SELECT rtrim('iTalkBB    ')

----------

 iTalkBB 

1.3          取子串函数

1.3.1             LEFT ()

LEFT (<character_expression>， <integer_expression>)

返回character_expression 左起 integer_expression 个字符。

SELECT left('iTalkbb',2)

----------

 iT   

1.3.2             RIGHT ()

RIGHT (<character_expression>， <integer_expression>)

返回character_expression 右起 integer_expression 个字符。

SELECT RIGHT('iTalkbb',2)

----------

 bb     

1.3.3             SUBSTRING ()

SUBSTRING (<expression>， <starting_ position>， length)

返回从字符串左边第starting_ position 个字符起length个字符的部分。

SELECT SUBSTRING('iTalkbb',2,4)

----------

 Talk   

1.4          字符串比较函数

1.4.1             CHARINDEX ()

返回字符串中某个指定的子串出现的开始位置。

CHARINDEX (<’substring_expression’>， <expression>)

其中substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式。如果没有发现子串，则返回0值。

此函数不能用于TEXT 和IMAGE 数据类型。

SELECT CHARINDEX('a','italkbb')

----------

 3    

SELECT CHARINDEX('Hooyes’,’ ItalkBB')

----------

 0    

1.4.2             PATINDEX ()

返回字符串中某个指定的子串出现的开始位置。

PATINDEX (<’%substring _expression%’>， <column_ name>)其中子串表达式前后必须有百分号“%”否则返回值为0。

与CHARINDEX 函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于CHAR、 VARCHAR 和TEXT 数据类型。

SELECT PATINDEX('AB%','1ABCD')

----------

 0    

SELECT PATINDEX('%AB','1ABCD')

----------

 0 

SELECT CHARINDEX('AB','1ABCD')

----------

 2    

SELECT PATINDEX('%[B,C,0-9]%','XYZABC123')

----------

 5  

1.5          字符串操作函数

1.5.1             QUOTENAME ()

返回被特定字符括起来的字符串。

QUOTENAME (<’character_expression’>[， quote_ character]) 其中quote_ character 标明括字符串所用的字符，缺省值为“[]”。

SELECT QUOTENAME('iTalkbb') 

----------

 [iTalkbb] 

SELECT QUOTENAME('iTalkbb','<>')

----------

 <iTalkbb>

1.5.2             REPLICATE ()

返回一个重复character_expression 指定次数的字符串。

REPLICATE (character_expression integer_expression) 如果integer_expression 值为负值，则返回NULL 。

SELECT REPLICATE('iTalkBB',2)

--------------

 iTalkBBiTalkBB

1.5.3             REVERSE ()

将指定的字符串的字符排列顺序颠倒。

REVERSE (<character_expression>) 其中character_expression 可以是字符串、常数或一个列的值。

SELECT REVERSE('iTalkBB')

----------

 BBklaTi 

1.5.4             REPLACE ()

返回被替换了指定子串的字符串。

REPLACE (<string_expression1>， <string_expression2>， <string_expression3>) 用string_expression3 替换在string_expression1中的子串string_expression2。

SELECT REPLACE('iTalkBB','BB','MOBO')

----------

 iTalkMOBO 

1.5.5             SPACE ()

返回一个有指定长度的空白字符串。

SPACE (<integer_expression>) 如果integer_expression 值为负值，则返回NULL 。

SELECT 'i'+SPACE(2)+'am'

----------

 i  am

1.5.6             STUFF ()

用另一子串替换字符串指定位置、长度的子串。

STUFF (<character_expression1>， <start_ position>， <length>，<character_expression2>)

如果起始位置为负或长度值为负，或者起始位置大于character_expression1 的长度，则返回NULL 值。

如果length 长度大于character_expression1 中 start_ position 以右的长度，则character_expression1 只保留首字符。

SELECT STUFF('www.italkbb.com',5,7,'mobo')

------------

 www.mobo.com

例1：查询信用卡的表，要求只显示前两位和后4位，中间用星号显示。

SELECT  STUFF(CREDIT_CARD,3,LEN(CREDIT_CARD)-6 ,REPLICATE('*',LEN(CREDIT_CARD)-6))  FROMACCOUNT_CC

------------------

40**********9900  

 40**********0130  

 40**********2623  

例2：查询用户Email 要示Email只显示前1位和@后面的内容。

SELECT  top 100STUFF(EMAIL,2,charindex('@',EMAIL)-2,REPLICATE('*',charindex('@',EMAIL)-2)) from MEMBER

order by acctid

--------------------------

t***********@yahoo.com    

 c*******@hotmail.com      

 w*****@comcast.net        

 t***@yahoo.com            

 a**********@hotmail.com   

 l***@gmu.edu              

 y******@gmail.com         

 b*******@gmail.com  

1.5.7             LEN ()

LEN ( string_expression )  计算字符串长度

1.5.8             DATALENGTH ()

DataLength() 函数返回的是字符串字节的长度，包含后缀空格。而Len()函数返回的是字符串的字符长度，不包含后缀的空格。

SELECT LEN('iTalkBB    '),DATALENGTH('iTalkBB    ')

column1     column2   

 ----------    ----------

 7           11        

1.6          数据类型转换函数

1.6.1             CAST ()

CAST (expression AS data_type [ (length ) ] )

1.6.2             CONVERT ()

CONVERT (<data_ type>[ length ]， <expression> [， style])

 

1）data_type为SQL Server系统定义的数据类型，用户自定义的数据类型不能在此使用。

2）length用于指定数据的长度，缺省值为30。

3）把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。

4）TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。

5）IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。

6）把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。

7）BIT类型的转换把非零值转换为1，并仍以BIT类型存储。

8）试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“+”，以标识发生了这种截断。

9）用CONVERT()函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号，不同的样式编号有不同的输出格式。

1.7          日期函数

1.7.1             DAY ()

Day (date_expression)

返回date_expression中的日期值

SELECT  DAY(getdate())

1.7.2             MONTH ()

Month (date_expression)

返回date_expression中的月份值

SELECT  MONTH(getdate())

1.7.3             YEAR ()

Year (date_expression)

返回date_expression中的年份值

SELECT YEAR(getdate())

1.7.4             DATEADD ()

DATEADD (<datepart>， <number>， <date>)

返回指定日期date 加上指定的额外日期间隔number 产生的新日期。

Datepart 参数是与 integernumber 相加的 date 部分。下表列出了所有有效的 datepart 参数。用户定义的变量等效项是无效的。

Datepart	缩写	备注
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	豪秒
Microsecond	mcs	微秒
Nanosecond	ns	纳秒

 

SELECT top 10 dateadd(day,1,mod_date) from account_extension order by mod_date desc

1.7.5             DATEDIFF ()

DATEDIFF (<datepart>， <date1>， <date2>)

返回两个指定日期在datepart 方面的不同之处，即date2 超过date1的差距值，其结果值是一个带有正负号的整数值。

SELECT DATEDIFF(day, getdate(),dateadd(day,2,getdate()));

----------

 2         

1.7.6             DATENAME ()

DATENAME (<datepart>， <date>)

以字符串的形式返回日期的指定部分此部分。由datepart 来指定。

SELECT DATENAME(year, getdate())

----------

 2011      

1.7.7             DATEPART ()

DATEPART (<datepart>， <date>)

以整数值的形式返回日期的指定部分。此部分由datepart 来指定。

DATEPART (dd， date) 等同于DAY (date)

DATEPART (mm， date) 等同于MONTH (date)

DATEPART (yy， date) 等同于YEAR (date)

1.7.8             GETDATE ()

以DATETIME 的缺省格式返回系统当前的日期和时间。

1.7.9             CONVERT ()

CONVERT (<data_ type>[ length ]， <expression> [， style])

 

SQL Server 中提供多种样式的日期格式化函数

常用：

Select CONVERT (varchar(100), GETDATE(), 8): 10:57:46

Select CONVERT (varchar(100), GETDATE(), 24): 10:57:47

Select CONVERT (varchar(100), GETDATE(), 108): 10:57:49

Select CONVERT (varchar(100), GETDATE(), 12): 060516

Select CONVERT (varchar(100), GETDATE(), 23): 2006-05-16

其它:

Select CONVERT (varchar(100), GETDATE(), 0): 05 16 2006 10:57AM

Select CONVERT (varchar(100), GETDATE(), 1): 05/16/06

Select CONVERT (varchar(100), GETDATE(), 2): 06.05.16

Select CONVERT (varchar(100), GETDATE(), 3): 16/05/06

Select CONVERT (varchar(100), GETDATE(), 4): 16.05.06

Select CONVERT (varchar(100), GETDATE(), 5): 16-05-06

Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06

Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06

Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46

Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM

Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06

Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16

Select CONVERT(varchar(100), GETDATE(), 12): 060516

Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937

Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967

Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47

Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157

Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM

Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16

Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47

Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250

Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM

Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006

Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16

Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006

Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006

Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006

Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006

Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006

Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49

Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM

Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006

Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16

Select CONVERT(varchar(100), GETDATE(), 112): 20060516

Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513

Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547

Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49

Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700

Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827

Select CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AM

Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM

1.8          统计函数

1.8.1             AVG ()

返回的平均值

例1：求出当前系统中所有计划月费的平均值

SELECT AVG(MRC) from calling_plan

----------

 13.6222 

1.8.2             COUNT () 

返回的行数

1.8.3             FIRST () 

返回第一个值

1.8.4             LAST () 

返回最后一个值

1.8.5             MAX ()

返回的最大价值

SELECT MAX(plan_id)  FROM calling_plan

----------

 9700  

1.8.6             MIN ()

返回最小的价值

SELECT MIN(plan_id)  FROM calling_plan

----------

 -1       

1.8.7             TOTAL () 

返回的总和

1.9          数学函数

1.9.1             ABS ()

abs(numeric_expr) 求绝对值

SELECT ABS(-100)

----------

 100       

1.9.2             CEILING ()

ceiling(numeric_expr)   取大于等于指定值的最小整数

SELECT CEILING(12.35),CEILING(-12.35)

column1     column2   

 ----------     ----------

 13          -12       

1.9.3             FLOOR ()

floor(numeric_expr)    取小于等于指定值得最大整数

SELECT FLOOR(12.35),FLOOR(-12.35)

column1     column2   

 ----------     ----------

 12          -13   

1.9.4             ROUND ()

round(numeric_expr,int_expr) 按int_expr规定的精度四舍五入

SELECT ROUND(12.545,1),ROUND(12.545,2),ROUND(12.545,0)

column1     column2     column3     

 ----------     ----------      ----------      

 12.5        12.55        13         

1.9.5             EXP ()

exp(float_expr)   取指数，该函数是以e为底的指数函数，即对e的自乘（e是一个参数表达式中常用的数学常数,约等于2.718281828459045）

SELECT EXP(2)

----------------

 7.38905609893065

1.9.6             PI ()

Pi ()  数学中的圆周率

SELECT PI()

-----------------

 3.141592653589793

1.9.7             POWER ()

power(numeric_expr, power)   返回numeric_expr 的power次方      

SELECT POWER(4,2)

----------

 16     

1.9.8             RAND ()

Rand ([int_expr])   返回小于0的正数随机数

1.9.9             SIGN ()

sign(int_expr)   根据正数,0,负数,对应返回+1,0,-1

SELECT SIGN(-100),SIGN(0),SIGN(121)

column1     column2     column3   

 ----------     ----------      ----------

 -1          0           1         

1.9.10         SQRT ()

sqrt(float_expr)  平方根

SELECT SQRT(121)

----------

 11        

1.10      系统函数

1.10.1         suser_name()      

用户登录名

1.10.2         user_name()       

用户在数据库中的名字

1.10.3         user              

用户在数据库中的名字

1.10.4         show_role()       

对当前用户起作用的规则

1.10.5         db_name()         

数据库名                    

1.10.6         object_name(obj_id)

数据库对象名     

1.10.7         col_name(obj_id,col_id)

列名       

1.10.8         col_length(objname,colname) 

列长度

1.10.9         valid_name(char_expr)    

是否是有效标识符

1.11      其它函数

1.11.1         ROW_NUMBER()

SQL2005

例1:

SELECT row_number() over(order by MRC )

as row_number,MRC,plan_id

from calling_plan

where MRC>0

例2:

SELECT row_number() over(PARTITION BY AREA order by MRC )

as row_number,MRC,plan_id,AREA

from calling_plan where MRC>0

1.11.2         RANK ()

SQL2005

例1：

SELECT rank() over(PARTITION BY AREA order by MRC )

as rank,MRC,plan_id,AREA

from calling_plan where MRC>0

1.11.3         DENSE_RANK()

SQL2005

SELECT dense_rank() over(PARTITION BY AREA order by MRC )

as rank,MRC,plan_id,AREA

from calling_plan where MRC>0

1.11.4         NTILE ()

这个函数按照ntile(n)中的N,把记录强制分成多少段。

1.11.5         SOUNDEX ()

SOUNDEX (character_expression)

SOUNDEX 将字母数字字符串转换成由四个字符组成的代码，用于查找发音相似的词或名称。代码的第一个字符是character_expression 的第一个字符，代码的第二个字符到第四个字符是数字。除非元音字母是字符串的第一个字母，否则character_expression 中的元音字母将被忽略。可以嵌套字符串函数。

1.11.6         DIFFERENCE ()

DIFFERENCE (character_expression, character_expression)

返回的整数是 SOUNDEX 值中相同字符的个数。返回的值从 0 到 4 不等：0 表示几乎不同或完全不同，4 表示几乎相同或完全相同。

SELECT SOUNDEX('Green'), SOUNDEX('Greene'), DIFFERENCE('Green’,’ Greene')

column1     column2     column3   

 ----------     ----------       ----------

 G650        G650        4