SQL Server函数大全-

1、SQL函数大全-聚合函数use pubsgoselect avg(distinct price -算平均数from titleswhere type='business'gouse pubsgoselect max(ytd_sales -最大数from titlesgouse pubsgoselect min(ytd_sales -最小数from titlesgouse pubsgoselect type,sum(price,sum(advance -求和from titlesgroup by typeorder by typegouse pubsgoselect count(

2、distinct city -求个数from authorsgouse pubsgoselect stdev(royalty -返回给定表达式中所有值的统计标准偏差from titlesgouse pubsgoselect stdevp(royalty -返回表达式中所有制的填充统计标准偏差gouse pubsgoselect var(royalty -返回所有值的统计方差from titlesgouse pubsgoselect varp(royalty -返回所有值的填充的统计方差from titlesgo-数学函数select sin(23.45,atan(1.234,rand(,PI(

3、,sign(-2.34 -其中rand是获得一个随机数-配置函数SELECT VERSION -获取当前数据库版本SELECT LANGUAGE -当前语言-时间函数select getdate( as 'wawa_getdate' -当前时间select getutcdate( as 'wawa_getutcdate' -获取utc时间select day(getdate( as 'wawa_day' -取出天select month(getdate( as 'wawa_month' -取出月select year(getdat

4、e( as 'wawa_year' -取出年select dateadd(d,3,getdate( as wawa_dateadd -加三天,注意'd'表示天,'m'表示月,'yy'表示年,下面一样select datediff(d,'2004-07-01','2004-07-15' as wawa_datediff -计算两个时间的差select datename(d,'2004-07-15' as wawa_datename -取出时间的某一部分select datepart(d

5、,getdate( as wawa_datepart -取出时间的某一部分,和上面的那个差不多-字符串函数select ascii(123 as '123',ascii('123' as '"123"',ascii('abc' as '"abc"' -转换成ascii码select char(123,char(321,char(-123 -根据ascii转换成字符select lower('ABC',lower('Abc',upper('

6、;Abc',upper('abc' -转换大小写select str(123.45,6,1, str(123.45,2,2 -把数值转换成字符串select ltrim(' "左边没有空格"' -去空格select rtrim('"右边没有空格" ' -去空格select ltrim(rtrim(' "左右都没有空格" ' -去空格select left('sql server',3,right('sql server',6 -取左

7、或者取右use pubsselect au_lname,substring(au_fname,1,1 -取子串order by au_lnameselect charindex('123','abc123def',2 -返回字符串中指定表达式的起始位置select patindex('123','abc123def',patindex('%123%','abc123def' -返回表达式中某模式第一次出现的起始位置select quotename('abc','',q

8、uotename('abc' -返回由指定字符扩住的字符串select reverse('abc',reverse('上海' -颠倒字符串顺序select replace('abcdefghicde','cde','xxxx' -返回呗替换了指定子串的字符串select space(5,space(-2-系统函数select host_name( as 'host_name',host_id( as 'host_id',user_name( as 'user_

9、name',user_id( as 'user_id',db_name( as 'db_name'-变量的定义使用-声明局部变量declare mycounter intdeclare last_name varchar(30,fname varchar(20,state varchar(2 -一下声明多个变量-给变量赋值use northwindgodeclare firstnamevariable varchar(20,regionvariable varchar(30set firstnamevariable='anne' -可以用

10、set,也可以用select给变量赋值,微软推荐用set,但select在选择一个值直接赋值时很有用set regionvariable ='wa'select lastname,firstname,title -用声明并赋值过的变量构建一个Select语句并查询from employeeswhere firstname= firstnamevariable or region=regionvariablego-全局变量select version -返回数据库版本select error -返回最后的一次脚本错误select identity -返回最后的一个自动增长列的id

11、-while,break,continue的使用-首先计算所有数的平均价格,如果低于30的话进入循环让所有的price翻倍,-里面又有个if来判断如果最大的单价还大于50的话,退出循环,否则继续循环,知道最大单价大于50就break出循环,呵呵,-我分析的应该对吧.use pubsgowhile (select avg(price from titles <$30beginupdate titlesset price=price*2select max(price from titlesif(select max(price from titles >$50breakelsecon

12、tinueendprint 'too much for the marker to bear'-事务编程经典例子-begin transaction是开始事务,commit transaction是提交事务,rollback transaction是回滚事务-这个例子是先插入一条记录,如果出现错误的话就回滚事务,也就是取消,并直接return(返回,如果没错的话就commit 提交这个事务了哦-上面的那个return返回可以返回一个整数值,如果这个值是0的话就是执行的时候没出错,如果出错了就是一个负数,-这个return也可以用在存储过程中,可用用exec return_sta

13、tus= pro_name来获取这个值use pubsgobegin tran mytraninsert into stores(stor_id,stor_namevalues('333','my books'goinsert into discounts(discounttype,stor_id,discountvalues('清仓甩卖','9999',50.00if error<>0beginrollback tran mytranprint '插入打折记录出错'returnendcommit tr

14、an mytran-事务处理的保存点示例-做了事务保存点后可以rollback(回滚到指定的保存点,不至于所有的操作都不能用use pubsgoselect * from storesbegin transaction testsavetraninsert into stores(stor_id,stor_namesave transaction before_insert_data2goinsert into stores(stor_id,stor_namevalues('5678','foreat Books'gorollback transaction before_insert_data2select * from stores-存储存储过程use pubsif exists(select name from sysobjects where name= 'proc_calculate_ta