常用SQL Server 小语法、函数 等的实例汇总

2017-10-25 10:38:28来源:oschina作者:ToxicLovers人点击

分享

重点内容常用SQL Serve函数及语法的部分实例汇总本文介绍SQL Server 中常用的几种函数:


函数一:ISNULL (check _ expression, replacement _ value)


ISNULL()使用指定的替换值替换 NULL。check _ expression用来检查判断字段或变量,判断是否为空,如果为空,则用replacement _ value来替换他;如果不为空,那么返回check _ expression。举例说明:这里写图片描述上图中,执行董事长disabled是1,意思是该岗位已经被撤销。如果要显示出来所有存在的岗位,可以用到ISNULL


SELECT * FROM TABLE WHERE ISNULL(Disabled,0)=0

这样就可以把执行董事去掉了。


函数二:CAST (expression AS data _ type)


CAST() 是将一种数据类型的表达式转换为另一种数据类型的表达式。举例说明:1010068,取这个数字的右边五位,因为这是个int整形,直接取得话肯定取不到,需要转化为varchar。


right (cast (1010068 as varchar(10)),5)

函数三:RIGHT(str, len)


RIGHT()的功能是将一个字符串从右边数len 个字符 (从1开始)进行返回。如果 string 参数中包含Null,则返回 NullLength指明要返回的字符数目,如果为 0,返回零长度字符串;如果此数大于或等于 string 参数中的所有字符数目,则返回整个字符串。类似的还有left(str,len),这里就不做例子说明了。


函数四:DATEDIFF(datepart, startdate, enddate)


DATEDIFF()是日期计算函数,函数返回两个日期之间的时间。detepart 是要比较的日期类型:这里写图片描述stratdate是开始时间,enddate是结束时间,计算过程是结束时间减去开始时间,enddate-stratdate,可以根据结果的正负值进行一些常规判断。


SELECT DATEDIFF(d,'2008-5-29','2008-12-30') AS DiffDate

这里写图片描述


函数五:DATEADD(datepart,number,date)


DATEADD()是在日期中添加或减去指定的时间间隔。Datepart的类型和作用同上,number是要添加的数量,date是要添加的时间基础。DATEADD(d,1,getdate()) 可以得到明天的日期。PS(getdaste() 获取系统的时间)。


函数六:CHARINDEX ( expression1,expression2[ ,start_location ])


CHARINDEX()是返回字符或者字符串在另一个字符串中的起始位置。expression1 是要查找的字符串,expression2是要查找的字符串元,start_location 是查找的起始位置,默认没有的话是从0开始,也就是开头。返回值是找到的位置,例如


CHARINDEX('SQL', 'Microsoft SQL Server')

结果就是11。


函数七:ROUND ( numeric_e-xpression , length [ , function ] )


返回数字表达式并四舍五入为指定的长度或精度。

numeric_e-xpression: 要进行四舍五入的表达式 ;length: 要四舍五入的精度;function: 要执行的操作类型,当指定 0 以外的值时,将截断 numeric_e-xpression;返回值是与 numeric_e-xpression 一样的类型。举例说明:


SELECT ROUND(123.75633, 2, 1), ROUND(123.75633, 2)

前一个是:123.75000,后一个是:123.76000。因为前者在进行四舍五入之前,小数点后已经被截取,保留了2位。而后者则没有被截取,四舍五入时自然就会得到123.76000注释:ROUND 始终返回一个值。如果 length 是负数且大于小数点前的数字个数,ROUND 将返回 0。示例 结果ROUND(748.58, -4) 0当 length 是负数时,无论什么数据类型,ROUND 都将返回一个四舍五入的 numeric_e-xpression。示例 结果ROUND(748.58, -1) 750.00ROUND(748.58, -2) 700.00ROUND(748.58, -3) 1000.00PS(摘录自http://blog.csdn.net/feixianxxx/article/details/4423676)


函数八:ceiling()和floor()


ceiling(3.32):向上取整,结果是4;
floor(3.32):向下取整,结果是3。

函数九:EXISTS()


定义:用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False。
*if exists(select * from table)*可以用来进行判断。
另一种用法where里面,例如两个表
图书表
![图书表](http://img.blog.csdn.net/20170523173650696?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvcXFfMzI1NDMzNzc=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
借阅表
![借阅表](http://img.blog.csdn.net/20170523173710337?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvcXFfMzI1NDMzNzc=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)

查找借阅过得书


select * from book where exists(select * from borrow as b where book.BID=b.BID)

等同于:


select * from book where BID in (selectBID from borrow)

结果表


函数十:SUBSTRING(expression, start, length)


相当于字符串截取函数expression:字符元或者是字段名;start:截取的起始位置,从0开始;length:截取的长度;例如:select SUBSTRING(‘ceshi ‘, 1, 2)结果就是ce


函数十一:replace(string1,string2,string3)


在字符串sting1中寻找是sting2 字符串 并用string3 替换它。for examp:select replace ('abcdefgabc','abc','123');结果是:123defg123


函数十二:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)


解释:row_number () 从1 开始给每一条记录返回一个数字;partition by column 对该字段相同的行,进行了统计;order by column 对字段进行分组并排序。


## **

小方法

: ##1、 获取特定月份的一号到31号或者25号到下个月26号


DateAdd(Day,0,DateAdd(Month,Datediff(Month,0,@P_TERM),0))--月1号
DateAdd(Day,0,DateAdd(Month,Datediff(Month,0,@P_TERM)+1,-1)), --月最后一天
DateAdd(Day,0,DateAdd(Month,Datediff(Month,0,@P_TERM),25))--24号
DateAdd(Day,0,DateAdd(Month,Datediff(Month,0,@P_TERM)+1,+25)), --次月26号

2、判断某个日期是否是周六


DATEPART(WEEKDAY,DATEADD(DAY,0,@time))=7

1——-周天2——-周一3——-周二4——-周三5——-周四6——-周五7——-周六


3、递归运算


WITH cteTree
AS (SELECT EV_MD_Employee.EmpID,EV_MD_Employee.ParentEmp FROM EV_MD_Employee WHERE ParentEmp = @empid --第一个查询作为递归的基点(锚点) UNION ALL SELECT EV_MD_Employee.EmpID ,EV_MD_Employee.ParentEmp --第二个查询作为递归成员, 下属成员的结果为空时,此递归结束。 FROM cteTree INNER JOIN EV_MD_Employee ON cteTree.EmpID= EV_MD_Employee.ParentEmp) SELECT * FROM cteTree

4、横表转纵表


select id,name,quarter,profile ---quarter,profile,固定列
from test
unpivot
(
profile
for quarter in ([Q1],[Q2],[Q3],[Q4])--括号里面是横表的字段
)
as unpvt --最后可以加where条件

这里写图片描述转换后这里写图片描述


5、修改表名


EXEC sp_rename ‘表名.[原列名]’, ‘新列名’, ‘column’


sp_rename –更改当前数据库中用户创建对象(如表、列或用户定义数据类型)的名称。

微信扫一扫

第七城市微信公众平台