MSSql数据库sql语句函数大集合

聚合函数： 1．AVG 返回组中的平均，空将被忽略。 例如：use northwind // 操作northwind数据库 Go Select avg (unitprice) //从表中选择求unitprice的平均 From products Where categoryid = ‘8’ 2．BINABY_CHECKSUM 可用于检测表中行的更改返回由表达式

聚合函数：

1．AVG 返回组中的平均值，空值将被忽略。
   例如：use  northwind   // 操作northwind数据库
         Go
  Select  avg (unitprice)   //从表中选择求unitprice的平均值
         From  products
         Where  categoryid = ‘8’
2．BINABY_CHECKSUM  可用于检测表中行的更改返回值由表达式的运算结果类型决定
   例如：use northwind
         Go
         Create  table  tablebc(productid int,bchecksum int)   //建立有两个属性的表
         Insert  into tablebc   //向表中插入数据
         Select  priductid,binary_checksum(*)
         From  products
         Update  products   //更新
         Set  productname=’oishi tofu’, unitprice=20 where  productname = ‘tofu’  
         Update  products   //更新
  Set  priductname=’oishi konbu’, unitprice = 5 where priductname=’konbu’
         Update priducts     //更新
         Set  prodctname=’oishi genen shouyu’, unitprice =12
         Where priductname=’genen shouyu’          
   Select  productid    //挑出变化的行
  From tablebc
  Where exists (
         Select productid from products
         Where  product.productid = tablebc.productid and
         binary_checksu (*) tablebc.bchecksum)  //标志出变化的行
3．CHECKSUM  返回在表的行上或在表达式上计算的校验值 CHECKSUM 用于生成哈希索引
   例如：
        Set arithabort on
 se northwind
 Go
        Alter table products
        Add cs_pname as checksum(productname)  //在参数列是添加一个校验值
        Create imdex pname_index on products(cs_pname)  //生成索引
        Go
        Select top 5 cs_pname from products order by cs_pname desc //选择根据索引列的前5 个cs_pname
4．Checksum_agg   返回组中值的校验值。空值冽被忽略。
   例如：
        Use northwind
   Go
 Select checksum_agg(cast(unitsinstock  as int)) //检测products 表的unitsinstock列的更改
        from products
5．Count   返回组中项目的数量
   例如：
  例一：
  Use pubs
  Go
  Select count(distinct city)  //对city中的每一行都计算，并返回非空的数量
  From authors
  Go
  例二：
                Use pubs
  Go
  Select count(*)  // 返回组中项目的数量
  From titles
  Go
  例三：
  Use pubs
  Go
  Select count(*),avg(price) // 选择advance大于$1000的总数和平均price
  From titles
  Where advance >$1000
  Go
6．Count_big  返回组中项目的数量。在使用上和count 基本上是一样的，只是在返回值上有一点区别，count_big 返回是bigint的数据类型值，count返回的是int数据类型值。
7．Grouping  返回一个聚合函数，它产生一个附加列，当用CUBE或ROLLUP运算符添加行时，附加 的列输出值为1，当所添加的行不是由CUBE或ROLLUP产生时，附加列值为0
   例如：
  Use pubs
  Go
  Select royalty,sum(advance) ‘total advance’,  //选择列royalty,聚合 advance数值
  Grouping(royalty) ‘grp’  //grouping 函数
  From titles
                Group by royalty with rollup //group by 与 rollup 相联系产生分组
8．Max  返回表达式的最大值
   例如：
  Use pubs
  Go
  Select max(ytd_sales)  //选择ytd_sales列属性，求其最大值。
  From titles
  Go
9．Min  返回表达式的最小值
   例如：
  Use pubs
  Go
  Select min(ytd_sales)  //选择ytd_sales列属性，求其最小值。
  From titles
  Go
10．Stdev  返回给定表达式中所有值的统计标准偏差。
    例如：
  Use pubs
  Select stdev(royalty)
  From titles
11．Stdevp  返回给定表达式中所有值的填充统计标准偏差。
    例如：
  Use pubs
  Select stdev(royalty)
  From titles
12．sum  返回表达式中所有值的的和，或只返回DISTINCT值。SUM只能用数字列。
    例如：
  Use pubs
  Go
  Select type,sum(price),sum(advance)  //选择type,price的和,advance的和
  From titles
  Where type like ‘%cook’  //匹配结尾字符为cook
  Group by type
  Order by type
  Go
    例如2：
  Use pubs
   Go
  Select type,price,advance
  From titles
  Where type like’%cook’
  Order by type
  Compute sum(price),sum(advance) by type   //根据type的属性计算price和advance
的和
13．Var  返回给定表达式中所有值的统计方差。
    例如：
  Use pubs
  Go
  Selecdt var(royalty)
  From titles
14．Varp  返回给定表达式中所有值的填充的统计方差
    例如：
  Use pubs
  Go
  Select  varp(royalty)
  From titles

时间及日期函数

1．Dateadd  在向指定日期加上一段时间的基础上返回新datetime值。
   例如：
  Use northwind
  Go
  Select dateadd(day,3,Hiredate)   //显示函数dateadd执行结果
  From employees
2.datediff  返回跨两个指定日期和时间边界数。
  例如：
  Use northwind
  Go
  Select datediff(day,Hiredate,getdate()) as no_of_days
  From employees
  go
3.Datename  返回代表指定日期的指定日期部分的字符串。
  例如：
 Select datename(month,getdate()) as ‘monthname’
4．Datepart  返回代表指定日期的指定日期部分的整数。
   例如：
  Select datepart(month,getdate()) as ‘monthnumber’
  Select datepart(m,0),datepart(d,0),datepart(yy,0)
5．Day month year  返回指定日期的天 月 年 的日期部分的整数
   例如：
  Select month(0),day(0),year(0)
6．Getdate  按datetime值的标准内部格式返回当前系统时间和日期
   例如：
  Use northwind
  Go
  Create table sales  //创建sales表
  (
  Sale_id char(11) not null  //sale_id列，类型char(11),not null
  Sale_name varchar(40) not null  //sale_name列，类型varchar(40),not null
  Sale_date datetime defaull getdate()  // sale_date列，类型datetime，默认值getdate()
  )
  Insert into sales (sale_id,sale_name)
     Values(1,’foods’)   //向表内插值
  Select * from sales   //表结果检索
7．Getutcdate  返回表当前CUT时间的datetime值。
   例如：
  Select getutcdatea()

数学函数

1．Abs  返回给定数字的绝对值
2．Acos  返回以弧度表示的角度值，该角度值的余弦为给定的float表达式，也叫反余弦
3．Asin   返回以弧度表示的角度值，也叫反正弦。
   例如：
  Declare @angle float
  Set @angle = -1.01
  Select ‘the asin of the angke is : ’ + convert (varchar,asin(@angle))
4．Atan  返回以弧度表示的角度值，该角度值的正切为给定的float表达式，也叫反正切。
5．Atn2  返回以弧度表示的角度值，该角度值的正切介于两个给定的float表达式之间
   例如：
  Declare @anglel float
  Declare @angle2 float
  Set @anglel = 35.175643
  Set @angle2 =129.44
  Select ‘the atn2 of the angle is : ’ + convert (varchar,atn2(@anglel,@angle2))
  Go
6．Ceiling  返回或等于所给数字表达式的最小整数。
   例如：
  Select ceiling($123.45),ceiling($-123.45),ceiling($0.0)
7．Cos  返回给定表达式中给定角度的三角余弦值
8．Cot  返回给定float表达式指定角度的三角余切值
   例如：
  Declare @angle float
  Set @angle = 124.1332
  Select ‘the cot fo the angle is :’ + convert(varchar,cot(@angle))
9．Degrees  当给出弧度为单位的角度时，返回相应的以度数为单位的角度。
   例如：
  Select ‘the number of degrees in PI/2 radinans is :’ +convert(varchar,degrees((PI()/2)))
10．Exp  返回所给的float表达式的指数值。
11．floor  返回小于或等于所给数字表达式的最大整数。
12．log   返回给定float表达式的自然对数。
13．log10  返回给定float表达式的以10为底的对数。
    例如：
  Declare @var float
  Set @var = 5.175643
  Select ‘the log of the variable is :’ +convert (varchar,log(@var))
14．PI  返回PI的常量值。
15．power  返回给定表达式乘指定次方的值。
   例如：
  Declare @value int,@counter int
  Set @value = 2
  Set @counter = 1
  While @counter   Begin
   Select power(@value,@counter)
  Set nocount on
   Set @counter=@counter +1
  End
  Go
16．radians 对于数字表达式中输入的度数值返回弧度值。
17．rand  返回0到1之间的随机float值。
18．round  返回数字表达式并四舍五入为指定的长度或精度。
   例如：
  Select round(123.9995,3),round(123.9994,3)
19．sign  返回给定表达式的正 零 或负号
   例如：
  Declare @angle gloat
  Declare @value real
  Set @value=-1
  While @value    Begin
    Select sign(@value)
  Set nocount on
   Select @value=value+1
   end
  Set nocount off
20．sin 以近似数字表达式返回给定角度的三角正弦值。
21．sqrt  返回给定表达式的平方根。
    例如：
  Declare @myvalue float
  Set @myvalue = 1.00
  While @myvlaue    Begin
    Select sqrt(@myvalue)
  Select @myvalue = @myvalue+1
  End
22．square  返回给定表达式的平方值。
23．tan  返回给定表达式的正切。

元数据函数

1.col_length  返回列的定义长度
例如：
        Use northwind
        Go
        Create table t1
               ( c1 varchar(40),
                C2 nvarchar(80) )
        Go
        Select col_length(‘t1’,’c1’) as ‘varchar’
        Select col_length(‘t2’,’c2’) as ‘nvarchar’
        Go
        Drop table t1
2.col_name  返回数据库列的名称，该列具有相应的表标识号和列标识号。
例如：
        Use northwind
        Go
        Set nocount off
        Select col_name(object_id(‘employees’),1) as employees
3.columnproperty  返回有关列或过程的参数的信息。
例如：
        Use northwind
        Go
        Select columnproperty (object_id(‘employees’),’title’,’precision’)
4.databaseproperty  返回给定数据库和属性名的命名数据库属性值。
例如：
        Use northwind
        Go
               Select databaseproperty(‘northwind’,’isautoclose’)
5.databasepropertyex  返回指定数据库的指定数据库选项或属性的当前设置。
例如：
        Use northwind
        Go         
               Select databasepropertyex(‘northwind’,isautoclose’)
6.db_id  返回数据库标识ID
例如：
        Select name,db_id(name) as db_id
        From sysdatabases
        Order by dbid
7.db_name 返回数据库名称。
8.file_id  返回当前数据库中给定逻辑文件标识（id）号。
9.file_name  返回指定文件标识（id）号的逻辑文件名。
10.filegroup_id  返回给定文件组名称号
11.filegroup_name 返回给定文件组标识（id）号的文件组名。
12.filegroupproperty  给定文件组和属性名时，返回指定的文件组属性值。
13.fileproperty  给定文件名和属性时返回指定的文件名属性值。
14.fn_listextendedproperty  返回数据库对像的扩展属性值。
例如：
        Use northwind
        Go
        Create table t1 (id int, name char(20))  //创建表T1
        Exec sp_addextendedproperty  ‘caption’,’employee id’,’user’,dbo,’table’,’t1’,
’column’,id     //为表T1列ID添加扩展属性
Exec sp_addextendedproperty  ‘caption’,’employee name’,’user’, dbo, ‘table’,
‘t1’,’column’,name
Select * from ：：fn_listextendedproperty (null,’suer’,’dbo’,table’,t1’,’column’,
             Default)   //列举表T1的扩展属性
15.fulltextserviceproperty  返回有关全文服务级别属性的信息。
原型：fulltextserviceproperty (catalog_name,property)
        参数说明：
        Catalog_name 包含全文目录名称的表达式。
        Property  包含全文目录属性名称的表达式。
               Property 参数值列表
                      Populatestatus  0 = 空闲  1 = 正在进行完全填充  2 = 已暂停
                                            3 = 中止  4 = 正在恢复   5 = 关机
                                            6 = 正在进行增量填充  7 = 生成索引 
                                            8 = 磁盘已满，已暂停  9 = 更改跟踪
例如：
        Use northwind
        Go  
               Select  fulltextcatalogproperty(‘cat_desc’,’itemcount”)
16.fulltextserviceproperty  返回有关全文服务级别属性的信息。
原型：fulltextserviceproperty(property)
              Property 参数说明

例如： 
        Use northwind 
        Go 
            Select indexproperty(object_id(‘categories’),’pk_categories’,’ispadindex’) 
20.    object_id  返回数据库对象标识号。 
原型： 
        Object_id(‘object’) 
例如：     
        Use master 
        Go 
            Select object_id(‘northwind..employees’) 
21.    object_name  返回数据库对象名。 
原型： 
        Object_name(object_id) 
    例如： 
        Use northwind 
        Go 
            Select table_catalog,table_name 
            From information_schema.tables 
            Where table_name = object_name(111770580711) 
22.    objectproperty  返回当前数据库中对象的有关信息。 
原型： 
        Objectproperty(id,property) 
参数说明： 
            Id 一个表达式，包含当前数据库中某一个对象的ID。ID的数据类型为INT。 
            Property 一个表达式，包含针对由ID指定的对象将要返回的信息。  
Property 属性值参数说明：

例如： 
        Select typeproperty(‘tinyint’,’precision’)

字符串函数
 
1.ascii 返回字符表达式最左端字符的ASCII代码值。
例如：
        Set nocount on
               Declare @position int,@string char(15)
                      Set @position = 1
                      Set @string = ‘du monde entier’
                      While @position                       Begin 
                      Select 
                      Ascii(substring(@string,@position,1)),
                      Char(Ascii(substring(@string,@position,1)))
                      Set @position =@position+1
                      End
                      Set nocount off
                      Go
2.char  将int ascii代码转换为字符的字符串函数。
3.charindex  返回字符串中指定表达式的起始位置。
原型：
       Charindex(expression1,expression2,[start_location])
       参数说明：
              Expression1 一个表达式，其中包含要寻找的字符的次序。
              Expression2 一个表达式，通常是一个用于指定序列的列。
              [start_logcation] 在expression2中搜索expression1时的起始字符位置。
例如：
       Use pubs
       Go
              Select charindex(‘wonderful’,notes)
                     From titles
              Where title_id=’tc3218’
              Go
在使用[start_logcation]参数时要注意一点。它所能实现的功能是忽略前面的字符，从你给定的字符开始查找expression1在expression2中的位置。
例如：
       declare @t varchar(50)
set @t=’ddfsadawfaafdadfa’
--1
select charindex(’a’,@t,6)
--2
select charindex(’a’,@t,4)
              例1和例2的结果是不一样的。
4.difference  比较两个字符串。
例如：
        Use pubs
        Go
               Select soundex(‘green’)
               Soundex(‘greene’),difference(‘green’,’greene’)
        Go
5.left  返回从字符串左边开始指定个数的字符。
6.len  返回字符串中字符的数量。
7.lower  将大写字符数据转换为小写字符数据后返回字符表达式。
例如：
        Use pubs 
        Go   
        Select lower(substring(tit, le,1,20)) as lower,
                Lower(upper((substring(title,1,20))) as lowerupper
        From titles
        Where price between 11:00 and 20:00
8.ltrim  删除字符串中的起始空格。
9.rtrim  删除字符串中的末尾的空格。
例如：
       Declare @string_to_trim varchar(60)  //声明变量
       Set @string_to_trim = ‘    five spaces are at the beginning of this string’
                                                               //变量赋值
       Select ‘here is the strng without the leading spaces: ’+ ltrim (@string_to_trim)
                                                               //显示函数LTRIM执行结果
10.nchar  根据unicode标准所进行的定义，用给定整数代码返回unicode字符。
例如：
       Declare @position int , @nstring nchar(9)  //声明局部变量
       Set @position = 1    //变量赋值
       Set @nstring = N’k&benhavn’
       Print ‘character #’ + ‘ ’ + ‘unicode character’ + ‘ ‘ + ‘unicode value’ //打印输出 
       While @position        Begin 
              Select @position ,    //显示函数nchar执行结果
              Nchar(unicode(substring(@nstring,@position,1))),
              Convert (nchar(17), substring(@nstring,@position,1)),
              Unicode(substring(@nstring,@position,1))
Select @position = @position+1
       End
       Go
11.patindex  返回指定表达式中某模式第一次出现的位置;如果在全部有效的文本和字符
数据类型中没有找到该模式，则返回零。
       例如：
              Use pubs
              Go
              Select patindex(‘%wonderfull%’,notes)
              From totles
              Where totle_id=’tc3218’
              Go
12.quotename  返回带有分隔符的unicode字符串
13.replace  用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串
表达式。
       例如：
              Select replace (‘abcdefabcfvabcetil’,’abc’,’xxx’)
14.replicate  以指定的次数重复字符表达式。
15.reverse  返回字符天大的反转。
16.right  返回字符串中从右边开始指定个数的integer_expression字符。
例如：