函数
函数是每种编程语言中必不可少的方法,每一个函数代表着一种方法。下面是对我们常用的函数索引。
字符串函数
1.字符转换函数
2.去空格函数
3.取子串函数
4.字符串搜索函数
5.字符串操作函数
1.ASCII( character_expression)函数:返回字符串表达式中最左侧字符的ASCII 代码值。
select ASCII ('s') as 's' ,ASCII ('Sql') as 'sql' ,ASCII(1) as '1';
2.char( integer_expression )函数:将整数类型的ASCII值转换成对应的字符。int在(0~255)之间,超出时返回null值。
select char(115), char(50)
3.left( character_expression , integer_expression )函数:返回字符串左边开始指定个数的字符串、字符或者二进制数据。
select left( 'basketball',3);
4.right( character_expression , integer_expression )函数:返回字符串右边开始指定个数的字符串、字符或者二进制数据。
select right('basketball',3);
5.ltrim(character_expression)函数:除去字符串左边多余的空格。
select '('+ ' basket ' + ')' , '(' + ltrim(' Ball ') + ')' ;
6.rtrim(character_expression)函数:除去字符串右边多余的空格。
select '('+ ' basket ' + ')' , '(' + rtrim(' Ball ') + ')' ;
7.str(float_expression[ , length [ , decimal ] ] )函数:用于将数值数据转换为字符数据。
float_expression :一个带小数点的近似数字(float)数据类型的表达式。
length: 总长度。包括小数点、符号、数字及空格。默认值为10.
decimal:指小数点后的位数。decimal 必须小于或者等于16. 如果decimal大于16,则会截断结果,时期保持为小数点后有16位。
超出指定长度时,返回星号。
select str (12543.14159,2,6) ,str(123.45,2,2); --结果 12543 , **
8.reverse(s) 函数: 将字符串s反转。
select REVERSE('剑云锋'); --锋云剑
9.len(str)函数: 计算字符串长度,即 返回字符表达式中的字符数。函数会包含前导空格和尾随空格在内计数。
len()函数 对相同的单字节和双字节字符串返回相同的值。
select len('no'), len('日期') ,len(12345); --2,2,5
10. charindex(substr , str , [start] )函数: 匹配子串开始位置的函数。在str的start位置开始搜索substr。
select charindex('a' , 'canada' ) , charindex('a' , 'canada' ,4 ) , charindex('da', 'canada' , 4 ) ; -- 2,4,5
11. substring( value_expression, start_expression, length_expression )函数:
返回字符串表达式、二进制表达式、文本表达式或图像表达式的一部分。
value_expression: character、binary、text、ntext 或 image表达式。
start_expression : 指定返回字符的起始位置的整数或者表达式。小于0,返回错误,大于表达式字符串长度,返回一个零长度的表达式。
length_expression : 正整数或指定要返回的value_expression的字符数的表达式。 length_expression<0;生成错误并终止语句。 如果start_expression与length_expression的总和大于value_expression中的字符数,则返回整个值表达式。
select substring('breakfast', 1 , 5 ) , substring('breakfast' , len('breakfast')/2 ,len('breakfast')); --break ,akfast
12.lower(character_expression)函数: 小写转换函数
select lower(' You Are A Boy ! ' ) --you are a boy !
13.upper(character_expression)函数: 大写转换函数
select upper(' You Are A Boy ! ' ) --YOU ARE A BOY !
14.replace( str , str1 , str2 )函数: 替换函数,用str2 替代 str中 所有的str1 。
select replace (' xxx.sqlserver2012.com ' , 'x' , 'w' ) ; -- www.sqlserver2012.com
数学函数
1.ABS(x):绝对值函数
select abs(2) ,abs(-2.2 ) , abs(-22) ; -- 2 , 2.2 , 22
2.PI(): 圆周率函数,返回圆周率。
select pi();
3.sqrt(x): 平方根函数
select sqrt ( 9 ) , sqrt( 20 ) ;
4.rand()/rand(x)函数: 获取随机数的函数。随机产生一个随机浮点值V,范围在0到1之间(0<= v <=1.0)。 若指定x,则他被用作种子值,使用相同的种子将产生重复序列。 如果用同一种子值多次调用rand()函数,将返回同一生成值。
select rand() , rand() , rand(8) , rand(8) , rand(10);
5.round( x , y )函数: 四舍五入函数,y为保留小数点位数:。y为正:小数点右边;y为负:小数点左边。
select round(123.45 , 1 ) , round(123.45 , 0 ) , round(123.45 , -2) --123.50 , 123.00 ,100.00
6.sign(x)函数:符号函数。 返回 正(1) 、零(0) 或 负(-1) 。
select sign(-11) , sign(0) , sign(11) ; -- -1 , 0 , 1
7.ceiling(x) and floor(x)函数:向上、向下取整函数
select ceiling( - 2.22 ) , ceiling ( 2.22 ) ; --向上取整函数, -2 , 3 select floor( -2.22 ), floor( 2.22 ) ;--向下取整函数, -3 , 2
8、power( x , y ) 、square(x) 和 exp(x) 函数: 幂运算函数
select power( 2 , 3 ) , power(2.00 , -2) --2的3次幂:-- 8 , 0.25 select square( 3 ) , square( -2 ) , square( 0 ) ; -- 3的平方,--9 , 4 , 0 select exp( 2 ) ,exp( -2 ) ,exp( 0 ) ; --计算e的2次方, --7.38905609893065,0.135335283236613 , 1
9、log(x) 和log10(x)函数: 对数运算函数:log(x)返回x的自然对数,x相对于基数e的对数。
select log(3) , log(6) ; --以 自然数e 为底的对数 --1.09861228866811 , 1.79175946922805 select log10(1) ,log10(100) ,log10(1000) ; --以10为底的对数 -- 0 , 2 , 3
10. radians(x) 、degrees(x)函数: 角弧度互转函数
- radians(x) 函数:角度转弧度。
- degrees(x) 函数:弧度转角度。
select radians( 90.0 ) ,radians(180.0) ; --π/2 , π select degrees(pi()) , degrees(pi() / 2 ); --180.0 ,90.0
11. 三角函数:sin(x)/asin(x) ;cos(x)/acos(x)
select sin( pi() / 2) , round(sin(pi()),0); select asin( 1 ) ,asin( 0 ) ; select cos(0),cos(PI()),cos(1); select acos(1) ,acos(0) ,round(acos(0.5403),0) select tan(0.3) ,round(tan(pi()/4) ,0 ) ; select atan(0.3093362496096) , atan(1) ; select cot(0.3) ,1/tan(0.3) ,cot(pi()/4) ;
数据类型转换函数
1. cast(x as type)函数:将当前值x的 类型转换为 type值 类型。
select cast ('181221' as date ) , cast(100 as char(3));
2.convert(type , x )函数 :将当前值x的 类型转换为type值 类型。
select convert ( time , '2018-12-21 12:12:12 ') ;
文本和图像函数
1.textptr(column)函数:返回对应Varbinary格式的text、ntext或image字段的文本指针值。
查找到的文本指针值可应用于readtext、writetext和 updatetext语句。
column : 是一个数据类型:text 、ntext、或者image的字段列。
实例:查询t1表中c2字段十六字节文本指针。
--创建数据表t1,c2字段为text类型 create table t1 (c1 int , c2 text ) insert t1 values ('1' , 'This is a text ! ') --使用textptr查询t1表中c2字段的十六字节文本指针。 select c1 ,textptr(c2) from t1 where c1 = 1 ;
2.textvalid('table.column' ,text_ptr)函数:用与检查特定文本指针是否为有效的 text 、ntext 或者 image函数。
table.column:为指定数据表和字段
text_ptr: 为要检查的文本指针
实例: 检查是否存在用于t1表的c2字段中的各个值的有效文本指针。
select c1 , 'This is a text !' = textvalid('t1.c2' , textptr(c2)) from t1 ;
日期和时间函数
1.getdate()函数:获取系统当前日期的函数
select getdate();
2.getutcdate()函数:返回UTC(世界标准时间)日期值。
select getutcdate();
3.Day(d)函数:用于返回指定日期的d是一个月中的第几天,范围是(1,31).
Day(d) &&& datepart(dd,d)
select day('2018-12-21 00:00:00');
4.month(d)函数:返回指定日期d中月份的整数值。
select month (' 2018-12-21 00:00:00');
5.year(d)函数:返回指定日期d中年份的整数值。
select year('2025-12-12') , year('2018-12-21');
6.datename(dp , d)函数:根据dp指定返回日期中的相应部分的值。
dp取值:year 、month、quarter 、dayofyear、 day、 week、weekday 、hour、minute、second等。
select datename(year ,'2018-12-21 12:12:12' ) as '年份' , datename(weekday ,'2018-12-21 12:12:12' ) as '星期', datename(dayofyear , '2018-12-21 12:12:12' ) as '年天数' ;
7.datepart(dp,d)函数:返回指定日期中指定部分的整数值。
dp取值:year 、month、quarter 、dayofyear、 day、 week、weekday 、hour、minute、second等。
select datepart(year ,'2018-12-21 12:12:12' ) as '年份' , datepart(weekday ,'2018-12-21 12:12:12' ) as '星期', datepart(dayofyear , '2018-12-21 12:12:12' ) as '年天数' ;
8.dateadd(dp,num,d)函数:用于执行日期的加运算,返回指定日期加上一个时间段后的新日期。
dp: 指定日期中进行加法运算的部分值:year、month、day、hour、minute、second、millsecond等。
num:指定与dp相加的值,如果该值为非整数值,将舍弃该值的小数部分;
d:为执行加法运算的日期。
select dateadd(year , 1 ,'2018-12-21 00:00:00 '), dateadd(month, 2 , '2018-12-21 00:00:00') , dateadd(hour , 1 , '2018-12-21 00:00:00');
系统函数
系统信息包括:当前使用的数据库名、主机名、系统错误信息以及用户名等内容。
1.col_length(table,column)函数:返回表中指定字段的长度值(int值)。
table:要确定其列长度信息的表的名称,为 nvarchar()类型的表达式。
column: 要确定其长度的列的名称,为nvarchar()类型的表达式。
计算表newtable1中CName字段的长度。
use TestSchool select col_length('newtable1','cName');
2.col_name(table_id ,column_id)函数:返回表中指定字段的名称
table_id : 是表的标识号;
column_id:是列的标识号,类型是int.
显示TestSchool数据库中newtable1表中第一个字段的名称。
select col_name(object_id('TestSchool.dbo.newtable1'), 1 ) ;
3.datalength(expression)函数:返回数据表达式的数据的实际长度,即字节数。
返回值类型为int。null的长度为null。
expression:可以是任何数据类型的表达式。
查找newtable1表中cName字段的长度。
use TestSchool; select datalength (cName) from newtable1 where Cid = 40;
4.db_id(database_name) 函数: 返回数据库的编号,其返回值类型为 SMALLINT类型。
如果没有指定database_name,则返回当前数据库的编号。
查看TestSchool数据库的数据库编号
select db_id('master') , db_id('TestSchool')
5.db_name(database_id)函数:返回数据库的名称,其返回值类型为nvarchar(128) 。database_id是smallint类型的数据。 如果没有指定database_id ,则返回当前数据库的名称。
use master select db_name(),db_name(db_id('TestSchool'));
6.getansinull()(database_name) 函数:返回当前数据库默认的null值。其返回值类型为int。
getansinull()函数对ANSI空值null 返回1;如果没有定义ANSI空值,则返回0。
返回当前数据库默认是否允许空值。
select getansinull('TestSchool')
7.host_id() 函数 : 返回服务器端计算机的标识号,其返回值类型为char(10)。
查看当前服务器端计算机的标识号,可记录那些向数据表中插入数据的计算机终端ID。
select host_id( ) ;
8.host_name()函数:返回服务器端计算机的名称,其返回值类型为nvarchar(128)。
查看当前服务器端计算机的名称
select host_name();
9. object_id(database_name.schema_name.object_name,object_type)函数:返回数据库对象的编号,其返回值类型为int。
object_name:要使用的对象,它的数据类型为varchar或nvarchar。如果object_name的数据类型为varchar,则它将隐式转换为nvarchar。可以选择是否指定数据库和架构名称。
object_type: 指定架构范围的对象类型。
返回TestSchool数据库中newtable1表的对象ID。
select object_id('TestSchool.dbo.newtable1');
10.suser_sid(login_name)函数:根据用户登录名返回用户的SID(Security Identification Number ,安全标识号)。其返回值类型为int。 如不指定login_name,则返回当前用户的SID。
查看当前登录用户的安全标识号
select suser_sid( ' sa' );
11.suser_sname([ server_user_sid ])函数:返回与安全标识号(SID)关联的登录名。如果没有指定derver_user_sid , 则返回当前用户的登录名。其返回值类型为nvarchar(128)。
返回与Windows安全标识号关联的登录名
select suser_sname(0x01);
12.object_name(object_id [,database_id ])函数:返回数据库对象的名称。
database_id要在其中查找对象的数据库的id,数据类型为int。
object_id为要使用的对象的ID,数据类型为int 。假定为指定数据库对象,如果不指定database_id, 则假定为当前数据库上下文中的架构范围内的对象,其返回值类型为sysname。
查看TestSchool数据库中对象ID值为1426104121的对象名称。
select object_name( 1426104121 , db_id('TestSchool ')),object_id('TestSchool.dbo.newtable1') ;
13.user_id(user)函数:根据用户名返回数据库用户的id,其返回值类型为int 。 如果没有指定user,则返回当前用户的数据库id。
显示当前用户的数据库标识号。
use TestSchool select user_id();
14.user_name(id)函数:根据与数据库用户关联的id号返回数据库用户名,其返回值类型为nvarchar(256)。如果没有指定id,则返回当前数据库的用户名。
查找当前数据库名称
use TestSchool; select user_name();
聚合函数
- avg()函数:返回某列的平均值
- count()函数:返回某列的行数
- max()函数:返回某列的最大值
- min()函数:返回某列的最小值
- sum()函数:返回某列值的和
其他函数
- distinct函数 :去除重复值。
- top(n)函数:去当前数据集的前n行。
select distinct Cid from Classes; select top(3) * from newtable; select top 50 percent * from newtable ;
本文部分内容参考至网络,如有错误,敬请指正,如有侵权,请联系修改。