SQL常用函数集锦2147.pdf

《SQL常用函数集锦2147.pdf》由会员分享，可在线阅读，更多相关《SQL常用函数集锦2147.pdf（13页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、一、字符转换函数 1、ASCII()返回字符表达式最左端字符的ASCII 码值。在 ASCII（）函数中，纯数字的字符串可不用括起来，但含其它字符的字符串必须用括起来使用，否则会出错。2、CHAR()将 ASCII 码转换为字符。如果没有输入 0 255 之间的 ASCII 码值，CHAR（）返回 NULL。3、LOWER()和 UPPER()LOWER()将字符串全部转为小写；UPPER()将字符串全部转为大写。4、STR()把数值型数据转换为字符型数据。STR(，length，)length 指定返回的字符串的长度，decimal 指定返回的小数位数。如果没有指定长度，缺省的 length

2、 值为 10，decimal 缺省值为 0。当 length 或者 decimal 为负值时，返回 NULL；当 length 小于小数点左边（包括符号位）的位数时，返回 length 个*；先服从 length，再取 decimal；当返回的字符串位数小于 length，左边补足空格。二、去空格函数 1、LTRIM()把字符串头部的空格去掉。2、RTRIM()把字符串尾部的空格去掉。三、取子串函数 1、left()LEFT(，)返回 character_expression 左起 integer_expression 个字符。2、RIGHT()RIGHT(，)返回 character_exp

3、ression 右起 integer_expression 个字符。3、SUBSTRING()SUBSTRING(，length)返回从字符串左边第 starting_ position 个字符起 length 个字符的部分。四、字符串比较函数 1、CHARINDEX()返回字符串中某个指定的子串出现的开始位置。CHARINDEX(，)其中 substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式。如果没有发现子串，则返回 0 值。此函数不能用于 TEXT 和 IMAGE 数据类型。2、PATINDEX()返回字符串中某个指定的子串出

4、现的开始位置。PATINDEX(，)其中子串表达式前后必须有百分号“%”否则返回值为 0。与 CHARINDEX 函数不同的是，PATINDEX 函数的子串中可以使用通配符，且此函数可用于CHAR、VARCHAR 和 TEXT 数据类型。五、字符串操作函数 1、QUOTENAME()返回被特定字符括起来的字符串。QUOTENAME(，quote_ character)其中 quote_ character 标明括字符串所用的字符，缺省值为“”。2、REPLICATE()返回一个重复 character_expression 指定次数的字符串。REPLICATE(character_expres

5、sion integer_expression)如果 integer_expression 值为负值，则返回 NULL。3、REVERSE()将指定的字符串的字符排列顺序颠倒。REVERSE()其中 character_expression 可以是字符串、常数或一个列的值。4、REPLACE()返回被替换了指定子串的字符串。REPLACE(，)用string_expression3 替换在 string_expression1 中的子串 string_expression2。5、SPACE()返回一个有指定长度的空白字符串。SPACE()如果 integer_expression 值为负值，则

6、返回 NULL。6、STUFF()用另一子串替换字符串指定位置、长度的子串。STUFF(，)如果起始位置为负或长度值为负，或者起始位置大于 character_expression1 的长度，则返回 NULL 值。如果 length 长度大于 character_expression1 中 start_ position 以右的长度，则character_expression1 只保留首字符。六、数据类型转换函数 1、CAST()CAST(AS length)2、CONVERT()CONVERT(length，style)1）data_type 为 SQL Server 系统定义的数据类型，用

7、户自定义的数据类型不能在此使用。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）

8、BIT 类型的转换把非零值转换为 1，并仍以 BIT 类型存储。8）试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“+”，以标识发生了这种截断。9）用 CONVERT（）函数的 style 选项能以不同的格式显示日期和时间。style 是将 DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由 SQL Server 系统提供的转换样式编号，不同的样式编号有不同的输出格式。七、日期函数 1、day(date_expression)返回 date_expression 中的日期值 2、month(date_expression)返回 date_expressio

9、n 中的月份值 3、year(date_expression)返回 date_expression 中的年份值 4、DATEADD()DATEADD(，)返回指定日期 date 加上指定的额外日期间隔 number 产生的新日期。5、DATEDIFF()DATEDIFF(，)返回两个指定日期在 datepart 方面的不同之处，即 date2 超过 date1 的差距值，其结果值是一个带有正负号的整数值。6、DATENAME()DATENAME(，)以字符串的形式返回日期的指定部分此部分。由 datepart 来指定。7、DATEPART()DATEPART(，)以整数值的形式返回日期的指定部

10、分。此部分由 datepart 来指定。DATEPART(dd，date)等同于 DAY(date)DATEPART(mm，date)等同于 MONTH(date)DATEPART(yy，date)等同于 YEAR(date)8、GETDATE()以 DATETIME 的缺省格式返回系统当前的日期和时间。9、Sql Server 中一个非常强大的日期格式化函数 Select CONVERT(varchar(100),GETDATE(),0):05 16 2006 10:57AM Select CONVERT(varchar(100),GETDATE(),1):05/16/06 Select C

11、ONVERT(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 C

12、ONVERT(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

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),

14、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)

15、: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(var

16、char(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()

17、,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(va

18、rchar(100),GETDATE(),130):18?1427 10:57:49:907AM Select CONVERT(varchar(100),GETDATE(),131):18/04/1427 10:57:49:920AM 常用：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

19、),GETDATE(),12):060516 Select CONVERT(varchar(100),GETDATE(),23):2006-05-16 八、统计函数 AVG（）-返回的平均价值 count（）-返回的行数 first（）-返回第一个值 last（）-返回最后一个值 max（）-返回的最大价值 min（）-返回最小的价值 total（）-返回的总和 九、数学函数 abs(numeric_expr)求绝对值 ceiling(numeric_expr)取大于等于指定值的最小整数 exp(float_expr)取指数 floor(numeric_expr)小于等于指定值得最大整数 pi

20、()3.1415926.power(numeric_expr,power)返回 power 次方 rand(int_expr)随机数产生器 round(numeric_expr,int_expr)安 int_expr 规定的精度四舍五入 sign(int_expr)根据正数,0,负数,返回+1,0,-1 sqrt(float_expr)平方根 十、系统函数 suser_name()用户登录名 user_name()用户在数据库中的名字 user 用户在数据库中的名字 show_role()对当前用户起作用的规则 db_name()数据库名 object_name(obj_id)数据库对象名 c

21、ol_name(obj_id,col_id)列名 col_length(objname,colname)列长度 valid_name(char_expr)是否是有效标识符 十一、以上函数的部分实例 1:replace 函数 第一个参数你的字符串，第二个参数你想替换的部分，第三个参数你要替换成什么 select replace(lihan,a,b)-lihbn（所影响的行数为 1 行）=2:substring 函数 第一个参数你的字符串，第二个是开始替换位置，第三个结束替换位置 select substring(lihan,0,3);-li（所影响的行数为 1 行）=3:charindex 函数

22、 第一个参数你要查找的 char，第二个参数你被查找的字符串 返回参数一在参数二的位置 select charindex(a,lihan)-4（所影响的行数为 1 行）=4：ASCII 函数 返回字符表达式中最左侧的字符的 ASCII 代码值。select ASCII(lihan)-108（所影响的行数为 1 行）=5：nchar 函数 根据 Unicode 标准的定义，返回具有指定的整数代码的 Unicode 字符。参数是介于 0 与 65535 之间的正整数。如果指定了超出此围的值，将返回 NULL。select nchar(3213)-unicode 字符（所影响的行数为 1 行）=6：

23、soundex 返回一个由四个字符组成的代码(SOUNDEX)，用于评估两个字符串的相似性。SELECT SOUNDEX(lihan),SOUNDEX(lihon);-L546 L542（所影响的行数为 1 行）=7：char 参数为介于 0 和 255 之间的整数。如果该整数表达式不在此围，将返回 NULL 值。SELECT char(125)-（所影响的行数为 1 行）=8：str 函数 第一个参数必须为数字，第二个参数表示转化成 char 型占的位置，小于参数一位置返回*，大于右对齐 SELECT str(12345,3)-*（所影响的行数为 1 行）SELECT str(12345,1

24、2)-12345（所影响的行数为 1 行）=9：difference 函数 返回一个整数值，指示两个字符表达式的 SOUNDEX 值之间的差异。返回的整数是 SOUNDEX 值中相同字符的个数。返回的值从 0 到 4 不等：0 表示几乎不同或完全不同，4 表示几乎相同或完全相同。SELECT difference(lihan,liha)-3（所影响的行数为 1 行）=10：stuff 函数（四个参数）函数将字符串插入另一字符串。它在第一个字符串中从开始位置删除指定长度的字符；然后将第二个字符串插入第一个字符串的开始位置。SELECT stuff(lihan,2,3,lihan)-llihann

25、（所影响的行数为 1 行）=11：left 函数 返回最左边 N 个字符，由参数决定 select left(lihan,4)-liha（所影响的行数为 1 行）=12 right 函数 返回最右边 N 个字符，由参数决定 select right(lihan,4)-ihan（所影响的行数为 1 行）=13：replicate 函数 我的认为是把参数一复制参数二次 select replicate(lihan,4)-lihanlihanlihanlihan（所影响的行数为 1 行）=14：len 函数 返回参数长度 select len(lihan)-5（所影响的行数为 1 行）=15：rev

26、erse 函数 反转字符串 select reverse(lihan)-nahil（所影响的行数为 1 行）=16：lower 和 upper 函数 参数大小写转化 select lower(upper(lihan)-lihan（所影响的行数为 1 行）=17：ltrim 和 rtrim 函数 删除左边空格和右面空格 select ltrim(lihan )-lihan （所影响的行数为 1 行）select rtrim(lihan)-lihan（所影响的行数为 1 行）追加：排名函数是 SQL Server2005 新加的功能。在 SQL Server2005 中有如下四个排名函数：1.ro

27、w_number 2.rank 3.dense_rank 4.ntile 下面分别介绍一下这四个排名函数的功能及用法。在介绍之前假设有一个 t_table 表，表结构与表中的数据如图 1 所示：图 1 其中 field1 字段的类型是 int，field2 字段的类型是 varchar 一、row_number row_number 函数的用途是非常广泛，这个函数的功能是为查询出来的每一行记录生成一个序号。row_number 函数的用法如下面的 SQL 语句所示：select row_number()over(order by field1)as row_number,*from t_tab

28、le 上面的 SQL 语句的查询结果如图 2 所示。图 2 其中 row_number 列是由 row_number 函数生成的序号列。在使用 row_number 函数是要使用over 子句选择对某一列进行排序，然后才能生成序号。实际上，row_number 函数生成序号的基本原理是先使用 over 子句中的排序语句对记录进行排序，然后按着这个顺序生成序号。over 子句中的 order by 子句与 SQL 语句中的 order by 子句没有任何关系，这两处的 order by 可以完全不同，如下面的 SQL 语句所示：select row_number()over(order by f

29、ield2 desc)as row_number,*from t_table order by field1 desc 上面的 SQL 语句的查询结果如图 3 所示。图 3 我们可以使用 row_number 函数来实现查询表中指定围的记录，一般将其应用到 Web 应用程序的分页功能上。下面的 SQL 语句可以查询 t_table 表中第 2 条和第 3 条记录：with t_rowtable as(select row_number()over(order by field1)as row_number,*from t_table)select*from t_rowtable where r

30、ow_number1 and row_number 4 order by field1 上面的 SQL 语句的查询结果如图 4 所示。图 4 上面的 SQL 语句使用了 CTE，关于 CTE 的介绍将读者参阅SQL Server2005 杂谈（1）：使用公用表表达式（CTE）简化嵌套 SQL。另外要注意的是，如果将 row_number 函数用于分页处理，over 子句中的 order by 与排序记录的order by 应相同，否则生成的序号可能不是有续的。当然，不使用 row_number 函数也可以实现查询指定围的记录，就是比较麻烦。一般的方法是使用颠倒 Top 来实现，例如，查询 t_

31、table 表中第 2 条和第 3 条记录，可以先查出前 3 条记录，然后将查询出来的这三条记录按倒序排序，再取前 2 条记录，最后再将查出来的这 2 条记录再按倒序排序，就是最终结果。SQL 语句如下：select*from(select top 2*from(select top 3*from t_table order by field1)a order by field1 desc)b order by field1 图 5 上面的 SQL 语句查询出来的结果如图 5 所示。这个查询结果除了没有序号列 row_number，其他的与图 4 所示的查询结果完全一样。二、rank rank

32、 函数考虑到了 over 子句中排序字段值相同的情况，为了更容易说明问题，在 t_table 表中再加一条记录，如图 6 所示。图 6 在图 6 所示的记录中后三条记录的 field1 字段值是相同的。如果使用 rank 函数来生成序号，这 3条记录的序号是相同的，而第 4 条记录会根据当前的记录 数生成序号，后面的记录依此类推，也就是说，在这个例子中，第 4 条记录的序号是 4，而不是 2。rank 函数的使用方法与 row_number 函数完全相 同，SQL 语句如下：select rank()over(order by field1),*from t_table order by fi

33、eld1 图 7 上面的 SQL 语句的查询结果如图 7 所示。三、dense_rank dense_rank 函数的功能与 rank 函数类似，只是在生成序号时是连续的，而 rank 函数生成的序号有可能不连续。如上面的例子中如果使用 dense_rank 函数，第 4 条记录的序号应该是 2，而不是 4。如下面的 SQL 语句所示：select dense_rank()over(order by field1),*from t_table order by field1 上面的 SQL 语句的查询结果如图 8 所示。图 8 读者可以比较图 7 和图 8 所示的查询结果有什么不同 四、nti

34、le ntile 函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中，每一个数组元素存放一定数量的记录。ntile 函数为每条记 录生成的序号就是这条记录所有的数组元素的索引（从1 开始）。也可以将每一个分配记录的数组元素称为“桶”。ntile 函数有一个参数，用来指定桶数。下 面的 SQL 语句使用 ntile 函数对 t_table 表进行了装桶处理：select ntile(4)over(order by field1)as bucket,*from t_table 图 9 上面的 SQL 语句的查询结果如图 9 所示。由于 t_table 表的记录总数是 6，

35、而上面的 SQL 语句中的 ntile 函数指定了桶数为 4。也许有的读者会问这么一个问题，SQL Server2005 怎么来决定某一桶应该放多少记录呢？可能t_table 表中的记录数有些少，那么我们假设 t_table 表中有 59 条记录，而桶数是 5，那么每一桶应放多少记录呢？实际上通过两个约定就可以产生一个算法来决定哪一个桶应放多少记录，这两个约定如下：1.编号小的桶放的记录不能小于编号大的桶。也就是说，第 1 捅中的记录数只能大于等于第 2 桶及以后的各桶中的记录。2.所有桶中的记录要么都相同，要么从某一个记录较少的桶开始后面所有捅的记录数都与该桶的记录数相同。也就是说，如果有个

36、桶，前三桶的记录数都是 10，而第 4 捅的记录数是 6，那么第 5 桶和第 6 桶的记录数也必须是 6。根据上面的两个约定，可以得出如下的算法：/mod 表示取余，div 表示取整 if(记录总数 mod 桶数=0)recordCount=记录总数 div 桶数;将每桶的记录数都设为 recordCount else recordCount1=记录总数 div 桶数+1;int n=1;/n 表示桶中记录数为 recordCount1 的最大桶数 m=recordCount1*n;while(记录总数-m)mod (桶数-n)!=0)n+;m=recordCount1*n;recordCount2=(记录总数-m)div (桶数-n);将前 n 个桶的记录数设为 recordCount1 将 n+1 个至后面所有桶的记录数设为 recordCount2