MSSQLSERVER数据库- 函数

    SQL提供了很多内置的函数，通过这些函数能进行各种复杂的运算和操作。对数据的处理变得灵活。在学习的过程中没有必要记住那么多的数学函数，记住一些常用的就行，在实际的应用中需要到哪些再进行查找。SQL函数的分类可以分为：

• 字符串函数
• 聚合函数
• 数学函数
• 日期函数
• 字符串函数
• 其他函数

 

字符串函数

• 字符串长度：LENGTH
• 字符串转换为小写：LOWER
• 字符串转换为大写：UPPER
• 截去字符串左侧空格：LTRIM
• 截去字符串左侧空格：RTRIM
• 截去字符串两侧空格：TRIM
• 取子字符串：SUBSTRING
• 从左侧取子字符串：LEFT
• 从右侧取子字符串：RIGHT
• 字符串的替换：REPLACE
• 由字符得到ASCII码：ASCII
• 由ASCII码得到字符：CHAR
• 发音匹配度：SOUDEX DIFFERENCE：有的时候我们并不知道一个人姓名的准确拼写，只知道它的发音，这是在公安、医疗、教育等系统中是经常需要的功能，比如 “检索名字发音为和[jeck]类似的人员” ，这时我们就要进行发音的匹配度测试了。SQL 中提供了 SOUNDEX()函数用于计算一个字符串的发音特征值，这个特征值为一个四个字符的字符串， 特征值的第一个字符总是初始字符串中的第一个字符， 而其后则是一个三位数字的数值。下面的 SQL 语句用于查询几个名字的发音特征值：MYSQL,MSSQLServer:
  SELECT SOUNDEX('jack'), SOUNDEX('jeck'), SOUNDEX('joke'),SOUNDEX('juke'), SOUNDEX('look'), SOUNDEX('jobe')
• 下面的 SQL 语句用来计算每个人的姓名发音与“Merry”的相似度：SELECTDIFFERENCE(FName,'Merry')  FROMT_Person 。执行完毕我们就能在输出结果中看到下面的执行结果：
  FName
  T om  Merry  2
  Jim  Merry  1
  Lily  Merry  2
  Kelly  Merry  3
  Sam  Merry  2
  Kerry  Merry  3
  Smith  Merry  0
  BillGates  Merry  1
  可以看到 Kerry、Kelly 与 Merry 的发音相似度非常高。

 

数学函数

1、POWER()函数：用来计算指数的函数。该函数接受两个参数，第一个参数为待求幂的表达式，第二个参数为幂。执行下面的 SQL 语句:

select POWER(3,2);显示结果是9

2、RAND()函数用来生成随机算法。RAND()函数还支持一个参数，这个参数为随机数种子，例如执行下面的结果

select RAND(1000) union all
select RAND(2000) union all
select RAND(3000) union all
select RAND(4000) union all
select RAND(5000) union all
select RAND(6000) 
  
--产生的数字是 
0.732206331499865 
0.750839302758065 
0.769472274016265 
0.788105245274465 
0.806738216532665 
0.825371187790865 

 

　　由此我们可以看出RAND函数括号里面放一个数字后将产生一个固定的随机数。那么如何产生随机的数，例如想随机产生1-10的随机数？SQL语句可以这么写：
　　　　select rand()*11
　　但产生的结果是 2.04988385549992 这么长一串，我们只想要整数部份，不想要小数部份，那么该怎么办？
　　　　select LEFT(rand()*11,1);
　　可是经过多次测试后并没有取出10，因为LEFT只能截取一位数，而10是两位数字。那么该怎么取才正确？正确的取法如下：
　　　　select cast(rand()*11 as int)
3、求圆周率
　　select 2*PI();　　结果：6.28318530717959

其他的数学函数有

• 　　平方根：SQRT
• 　　舍入到最大整数：CEILING
• 　　舍入到最小整数：FLOOR
• 　　四舍五入：ROUND
• 　　正弦值：SIN
• 　　余弦值：COS
• 　　反正弦值：ASIN
• 　　反余弦值：ACOS
• 　　正切值：TAN
• 　　余切值：COT
• 　　圆周率：PI
• 　　弧度制转换为角度制：用来将一个数值从弧度制转换为角度制的函数为 DEGREES ()，它接受一个参数，这个参数为待转换的表达式。在 Oracle 和 DB2 中不支持这个函数，不过根据：角度制=弧度制*180/π
• 　　角度制转换为弧度制：用来将一个数值从角度制转换为弧度制的函数为 RADIANS ()，它接受一个参数，弧度制=角度制*π/180
• 　　求符号
• 　　求自然对数：LOG（以E为底） LOG10（以10为底）

 

日期函数

1. GETDATE():取得当前日期时间
2.  DATEADD(DATEPART,NUMBER,DATE): 计算增加以后的日期，参数datepart为计量单位，number为增量; 可选值见备注;date为待计算日期。例如 DATEADD(DAY,3,DATE) 为计算日期date 3天后的日期
3. DATEDIFF(DATEPART,STARTDATE,ENDDATE):计算两个日期之间的差额，DATEPART为计量。
4. DATEPART（DATEPART,DATE）返回一个日期的特定部份

View Code 
     --查出当前时间
     select getdate()
     --将当前时间将3天
     select dateadd(day,3,getdate()) 获得3天以前
     --将当前时间月份减一个月
     select dateadd(month,-1,getdate()) 获得1个月以前
 
 
     --查出当前数据是今年和今月的全部数据
     select * from table
     where month(date) = month(getdate()) and year(date)= year(getdate())
 
 
     --datediff 取出两个日期的时间差
     select datediff(day,getdate(),'2012-12-21');
     select datediff(second,getdate(),'2012-12-21');
 
     --统计不同入学年数学生个数
     select count(*) year(sBirthday)
     from student
     group by year(sBirthday)
 
 
     --求每个年龄有多少个学生
     select DateDiff(year,sBirthday,getdate()),count(*) from student 
     Group by DateDiff(year,sBirthday,getdate())
 
 
     --datename,datepart
     select datename(year,getdate()) --字符串
     select datepart(year,getdate()) --数字

 

其他函数：

1、类型转换函数

MSSQLSERVER里有两个类型转换函数：CAST(EXPRESSION AS DATE_TYPE）、CONVERT(DATA_TYPE,EXPRESSION)

　　  --CAST的测试例子： 
    selec '平均分数'+cast(23.56 as varchar(5)) 
　　 
　　  --CONVERT的例子 
　　      select convert(int,89.123); 
　　      select convert(decimal(10,2),89.123); 
　　 
　　  --获取 2011-08-09形式的字符串 
　　  select convert(varchar(10),getdate(),20) --20表示显示的日期格式 

 

2、空值处理函数

1. COALESCE()函数：将会返回包括expression在内的所有参数中的第一个非空表达式。如果expression不为空值则返回expression； 否则判断value1是否是空值， 如果value1不为空值则返回value1；否则判断value2是否是空值，如果value2不为空值则返回value3；……以此类推，如果所有的表达式都为空值，则返回NULL。
2. ISNULL的用法：举个例子，如果english字段的成绩是NULL，我们希望在显示的时候不要显示NULL，而用0代码，那么这时候可以这么写：select studentID,isnull(english,0) from score
   select avg(english) from score
3. CASE...END的用法，CASE的写法的表达式如下：

　　 CASE EXPRESSION
  　　　　WHEN VALUE1 THEN RETURNVALUE1
  　　　　WHEN VALUE2 THEN RETURNVALUE2
 　　　　 ELSE DEFAULTRETURN VALUE
　　 END