转自http://www.cnblogs.com/qixuejia/archive/2010/07/14/1777105.html
1.聚合函数
(1)AVG
函数功能返回组中值的平均值。空值将被忽略
语法AVG([ ALL | DISTINCT ] expression)
(2)MAX
函数功能返回表达式的最大值
语法MAX([ ALL | DISTINCT ] expression)
(3)MIN
函数功能返回表达式的最小值
语法MIN([ ALL | DISTINCT ] expression)
(4)SUM
函数功能返回表达式中所有值的和，或只返回DISTINCT值。SUM只能用于数字列。空值将被忽略
语法SUM([ ALL | DISTINCT ] expression)
(5)COUNT
函数功能返回组中项目的数量
语法COUNT({[ ALL | DISTINCT ] expression ]|*})
2.时间和日期函数
(1)DATEADD
函数功能在向指定日期加上一段时间的基础上，返回新的datetime值
语法DATEADD(datepart,number,date)
样例SELECTDATEADD(day,21,pubdate)AStimeframeFROMtitles
(2)DATEPART
函数功能返回代表指定日期的指定日期部分的整数
语法DATEPART(datepart,date)
样例SELECTDATEPART(month,GETDATE())AS'Month Number'
(3)DAY
函数功能返回代表指定日期的天的日期部分的整数
语法DAY(date)
(4)GETDATE
函数功能按datetime值的Microsoft?SQLServer?标准内部格式返回当前系统日期和时间
语法GETDATE()
(5)GETUTCDATE
函数功能返回表示当前UTC时间（世界时间坐标或格林尼治标准时间）的datetime值。当前的UTC时间得自当前的本地时间和运行SQLServer的计算机操作系统中的时区设置
语法GETUTCDATE()
(6)MONTH
函数功能返回代表指定日期月份的整数
语法MONTH(date)
样例SELECT"Month Number"=MONTH('03/12/1998')
(7)YEAR
函数功能返回表示指定日期中的年份的整数
语法YEAR(date)
3.数学函数
(1)ABS
函数功能返回给定数字表达式的绝对值
语法ABS(numeric_expression)
(2)RAND
函数功能返回0到之间的随机float值
语法RAND([ seed ])：seed是给出种子值或起始值的整型表达式（tinyint、smallint或int）
(3)ROUND
函数功能返回数字表达式并四舍五入为指定的长度或精度
语法ROUND(numeric_expression,length[ , function ])
(4)FLOOR
函数功能返回小于或等于所给数返回小于或等于所给数字表达式的最大整数字表达式的最大整数
语法FLOOR(numeric_expression)
(5)SIGN
函数功能返回给定表达式的正(+1)、零(0)或负(-1)号
语法SIGN(numeric_expression)
(6)SQUARE
函数功能返回给定表达式的平方
语法SQUARE(float_expression)
样例
4.系统函数
(1)CAST和CONVERT
函数功能将某种数据类型的表达式显式转换为另一种数据类型。CAST和CONVERT提供相似的功能
语法CAST(expressionASdata_type)
CONVERT(data_type[(length)],expression [, style])
样例DECLARE@myvaldecimal(5,2)
SET@myval=193.57
SELECTCAST(CAST(@myvalASvarbinary(20))ASdecimal(10,5))
-- Or, using CONVERT
SELECTCONVERT(decimal(10,5),CONVERT(varbinary(20),@myval))
(2)COALESCE
函数功能返回其参数中第一个非空表达式
语法COALESCE(expression [ ,...n ])
样例SELECTCAST(COALESCE(hourly_wage*40*52,salary,commission*num_sales)ASmoney)AS'Total Salary'FROMwages
(3)DATALENGTH
函数功能返回任何表达式所占用的字节数
语法DATALENGTH(expression)
(4)@@ERROR
函数功能返回最后执行的Transact-SQL语句的错误代码
语法@@ERROR
样例IF@@ERROR=547
print"A check constraint violation occurred"
(5)ISDATE
函数功能确定输入表达式是否为有效的日期
语法ISDATE(expression)
(6)ISNULL
函数功能使用指定的替换值替换NULL
语法ISNULL(check_expression,replacement_value)
(7)ISNUMERIC
函数功能确定表达式是否为一个有效的数字类型
语法ISNUMERIC(expression)
(8)NULLIF
函数功能如果两个指定的表达式相等，则返回空值
语法NULLIF(expression,expression)
(9)@@ROWCOUNT
函数功能返回受上一语句影响的行数
语法@@ROWCOUNT
5.游标函数
(1)@@CURSOR_ROWS
函数功能返回连接上最后打开的游标中当前存在的合格行的数量。为提高性能，Microsoft?SQLServer?可以异步填充大键集和静态游标。可调用@@CURSOR_ROWS，以确定当它被调用时，符合游标的行的数目被进行了检索
语法@@CURSOR_ROWS
样例SELECT@@CURSOR_ROWS
DECLAREauthors_cursorCURSORFOR
SELECTau_lnameFROMauthors
OPENauthors_cursor
FETCHNEXTFROMauthors_cursor
SELECT@@CURSOR_ROWS
CLOSEauthors_cursor
DEALLOCATEauthors_cursor
(2)@@FETCH_STATUS
函数功能返回被FETCH语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态
语法@@FETCH_STATUS
样例DECLAREEmployee_CursorCURSORFOR
SELECTLastName,FirstNameFROMNorthwind.dbo.Employees
OPENEmployee_Cursor
FETCHNEXTFROMEmployee_Cursor
WHILE@@FETCH_STATUS=0
BEGIN
FETCHNEXTFROMEmployee_Cursor
END
CLOSEEmployee_Cursor
DEALLOCATEEmployee_Cursor
6.元数据函数
(1)COL_LENGTH
函数功能返回列的定义长度（以字节为单位）
语法COL_LENGTH('table','column')
(2)COL_NAME
函数功能返回数据库列的名称，该列具有相应的表标识号和列标识号
语法COL_NAME(table_id,column_id)
(3)INDEX_COL
函数功能返回索引列名称
语法INDEX_COL('table',index_id,key_id)
(3)DB_NAME
函数功能返回数据库名
语法DB_NAME(database_id)
(3)OBJECT_ID
函数功能返回数据库对象标识号
语法OBJECT_ID('object')
样例SELECTOBJECT_ID('pubs..authors')
(4)OBJECT_NAME
函数功能返回数据库对象名
语法OBJECT_NAME(object_id)
(5)@@PROCID
函数功能返回当前过程的存储过程标识符(ID)
语法@@PROCID
(6)TYPEPROPERTY
函数功能返回有关数据类型的信息
语法TYPEPROPERTY(type,property)
样例SELECTTYPEPROPERTY('tinyint','PRECISION')
7.字符串函数
(1)ASCII
函数功能返回字符表达式最左端字符的ASCII代码值
语法ASCII(character_expression)
(2)CHAR
函数功能将intASCII代码转换为字符的字符串函数
语法CHAR(integer_expression)
(3)CHARINDEX
函数功能返回字符串中指定表达式的起始位置
语法CHARINDEX(expression1,expression2 [ , start_location ])
参数expression1:一个表达式，其中包含要寻找的字符的次序。expression1是一个短字符数据类型分类的表达式。
expression2:一个表达式，通常是一个用于搜索指定序列的列。expression2属于字符串数据类型分类。
(4)DIFFERENCE
函数功能以整数返回两个字符表达式的SOUNDEX值之差
语法DIFFERENCE(character_expression,character_expression)
(5)LEFT
函数功能返回从字符串左边开始指定个数的字符
语法LEFT(character_expression,integer_expression)
(6)LEN
函数功能返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格
语法LEN(string_expression)
(7)LOWER
函数功能将大写字符数据转换为小写字符数据后返回字符表达式
语法LOWER(character_expression)
(8)LTRIM
函数功能删除起始空格后返回字符表达式
语法LTRIM(character_expression)
(9)NCHAR
函数功能根据Unicode标准所进行的定义，用给定整数代码返回Unicode字符
语法NCHAR(integer_expression)
样例
(10)PATINDEX
函数功能返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零
语法PATINDEX('%pattern%',expression)
(11)PEPLACE
函数功能用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式
语法REPLACE('string_expression1','string_expression2','string_expression3')
(12)REVERSE
函数功能返回字符表达式的反转
语法REVERSE(character_expression)
(13)RIGHT
函数功能返回字符串中从右边开始指定个数的integer_expression字符
语法RIGHT(character_expression,integer_expression)
(14)RTRIM
函数功能截断所有尾随空格后返回一个字符串
语法RTRIM(character_expression)
(15)SOUNDEX
函数功能返回由四个字符组成的代码(SOUNDEX)以评估两个字符串的相似性
语法SOUNDEX(character_expression)
--聚合函数
usepubs
go
selectavg(distinctprice)--算平均数
fromtitles
wheretype='business'
go
usepubs
go
selectmax(ytd_sales)--最大数
fromtitles
go
usepubs
go
selectmin(ytd_sales)--最小数
fromtitles
go
usepubs
go
selecttype,sum(price),sum(advance)--求和
fromtitles
groupbytype
orderbytype
go
usepubs
go
selectcount(distinctcity)--求个数
fromauthors
go
usepubs
go
selectstdev(royalty)--返回给定表达式中所有值的统计标准偏差
fromtitles
go
usepubs
go
selectstdevp(royalty)--返回表达式中所有制的填充统计标准偏差
fromtitles
go
usepubs
go
selectvar(royalty)--返回所有值的统计方差
fromtitles
go
usepubs
go
selectvarp(royalty)--返回所有值的填充的统计方差
fromtitles
go
--数学函数
selectsin(23.45),atan(1.234),rand(),PI(),sign(-2.34)--其中rand是获得一个随机数
--配置函数
SELECT@@VERSION--获取当前数据库版本
SELECT@@LANGUAGE--当前语言
--时间函数
selectgetdate()as'wawa_getdate'--当前时间
selectgetutcdate()as'wawa_getutcdate'--获取utc时间
selectday(getdate())as'wawa_day'--取出天
selectmonth(getdate())as'wawa_month'--取出月
selectyear(getdate())as'wawa_year'--取出年
selectdateadd(d,3,getdate())aswawa_dateadd--加三天,注意'd'表示天,'m'表示月,'yy'表示年,下面一样
selectdatediff(d,'2004-07-01','2004-07-15')aswawa_datediff--计算两个时间的差
selectdatename(d,'2004-07-15')aswawa_datename--取出时间的某一部分
selectdatepart(d,getdate())aswawa_datepart--取出时间的某一部分,和上面的那个差不多
--字符串函数
selectascii(123)as'123',ascii('123')as'"123"',ascii('abc')as'"abc"'--转换成ascii码
selectchar(123),char(321),char(-123)--根据ascii转换成字符
selectlower('ABC'),lower('Abc'),upper('Abc'),upper('abc')--转换大小写
selectstr(123.45,6,1),str(123.45,2,2)--把数值转换成字符串
selectltrim(' "左边没有空格"')--去空格
selectrtrim('"右边没有空格" ')--去空格
selectltrim(rtrim(' "左右都没有空格" '))--去空格
selectleft('sql server',3),right('sql server',6)--取左或者取右
usepubs
selectau_lname,substring(au_fname,1,1)--取子串
fromauthors
orderbyau_lname
selectcharindex('123','abc123def',2)--返回字符串中指定表达式的起始位置
selectpatindex('123','abc123def'),patindex('%123%','abc123def')--返回表达式中某模式第一次出现的起始位置
selectquotename('abc','{'),quotename('abc')--返回由指定字符扩住的字符串
selectreverse('abc'),reverse('上海')--颠倒字符串顺序
selectreplace('abcdefghicde','cde','xxxx')--返回呗替换了指定子串的字符串
selectspace(5),space(-2)
--系统函数
selecthost_name()as'host_name',host_id()as'host_id',user_name()as'user_name',user_id()as'user_id',db_name()as'db_name'
--变量的定义使用
--声明局部变量
declare@mycounterint
declare@last_namevarchar(30),@fnamevarchar(20),@statevarchar(2)--一下声明多个变量
--给变量赋值
usenorthwind
go
declare@firstnamevariablevarchar(20),
@regionvariablevarchar(30)
set@firstnamevariable='anne'--可以用set,也可以用select给变量赋值,微软推荐用set,但select在选择一个值直接赋值时很有用
set@regionvariable='wa'
selectlastname,firstname,title--用声明并赋值过的变量构建一个Select语句并查询
fromemployees
wherefirstname=@firstnamevariableorregion=@regionvariable
go
--全局变量
select@@version--返回数据库版本
select@@error--返回最后的一次脚本错误
select@@identity--返回最后的一个自动增长列的id
--while,break,continue的使用
--首先计算所有数的平均价格,如果低于的话进入循环让所有的price翻倍,
--里面又有个if来判断如果最大的单价还大于的话,退出循环,否则继续循环,知道最大单价大于就break出循环,呵呵,
--我分析的应该对吧.
usepubs
go
while(selectavg(price)fromtitles)<$30
begin
updatetitles
setprice=price*2
selectmax(price)fromtitles
if(selectmax(price)fromtitles)>$50
break
else
continue
end
print'too much for the marker to bear'
P2='A'
--事务编程经典例子
--begin transaction是开始事务,commit transaction是提交事务,rollback transaction是回滚事务
--这个例子是先插入一条记录,如果出现错误的话就回滚事务,也就是取消,并直接return(返回),如果没错的话就commit提交这个事务了哦
--上面的那个return返回可以返回一个整数值,如果这个值是的话就是执行的时候没出错,如果出错了就是一个负数,
--这个return也可以用在存储过程中,可用用exec @return_status= pro_name来获取这个值
usepubs
go
begintranmytran
insertintostores(stor_id,stor_name)
values('333','my books')
go
insertintodiscounts(discounttype,stor_id,discount)
values('清仓甩卖','9999',50.00)
if@@error<>0
begin
rollbacktranmytran
print'插入打折记录出错'
return
end
committranmytran
--事务处理的保存点示例
--做了事务保存点后可以rollback(回滚)到指定的保存点,不至于所有的操作都不能用
usepubs
go
select*fromstores
begintransactiontestsavetran
insertintostores(stor_id,stor_name)
values('1234','W.Z.D Book')
savetransactionbefore_insert_data2
go
insertintostores(stor_id,stor_name)
values('5678','foreat Books')
go
rollbacktransactionbefore_insert_data2
select*fromstores
--存储存储过程
usepubs
ifexists(selectnamefromsysobjectswherename='proc_calculate_taxes'andtype='P')
dropprocedureproc_calculate_taxes
go
createprocedureproc_calculate_taxes(@p1smallint=42,@p2char(1),@p3varchar(8)='char')
as
select*
fromtitles
--执行过程
EXECUTEPROC_CALCULATE_TAXES @
SQL常用函数
一、字符转换函数
1、ASCII()
返回字符表达式最左端字符的ASCII码值。在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。
2、CHAR()
将ASCII码转换为字符。如果没有输入~255之间的ASCII码值，CHAR（）返回NULL。
3、LOWER()和UPPER()
LOWER()将字符串全部转为小写；UPPER()将字符串全部转为大写。
4、STR()
把数值型数据转换为字符型数据。
STR(<float_expression>[，length[，<decimal>]])
length指定返回的字符串的长度，decimal指定返回的小数位数。如果没有指定长度，缺省的length值为，decimal缺省值为。
当length或者decimal为负值时，返回NULL；
当length小于小数点左边（包括符号位）的位数时，返回length个*；
先服从length，再取decimal；
当返回的字符串位数小于length，左边补足空格。
二、去空格函数
1、LTRIM()把字符串头部的空格去掉。
2、RTRIM()把字符串尾部的空格去掉。
三、取子串函数
1、left()
LEFT (<character_expression>，<integer_expression>)
返回character_expression左起integer_expression个字符。
2、RIGHT()
RIGHT (<character_expression>，<integer_expression>)
返回character_expression右起integer_expression个字符。
3、SUBSTRING()
SUBSTRING (<expression>，<starting_ position>，length)
返回从字符串左边第starting_ position个字符起length个字符的部分。
四、字符串比较函数
1、CHARINDEX()
返回字符串中某个指定的子串出现的开始位置。
CHARINDEX (<’substring_expression’>，<expression>)
其中substring _expression是所要查找的字符表达式，expression可为字符串也可为列名表达式。如果没有发现子串，则返回值。
此函数不能用于TEXT和IMAGE数据类型。
2、PATINDEX()
返回字符串中某个指定的子串出现的开始位置。
PATINDEX (<’%substring _expression%’>，<column_ name>)其中子串表达式前后必须有百分号“%”否则返回值为。
与CHARINDEX函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于CHAR、VARCHAR和TEXT数据类型。
五、字符串操作函数
1、QUOTENAME()
返回被特定字符括起来的字符串。
QUOTENAME (<’character_expression’>[，quote_ character])其中quote_character标明括字符串所用的字符，缺省值为“[]”。
2、REPLICATE()
返回一个重复character_expression指定次数的字符串。
REPLICATE(character_expression integer_expression)如果integer_expression值为负值，则返回NULL。
3、REVERSE()
将指定的字符串的字符排列顺序颠倒。
REVERSE(<character_expression>)其中character_expression可以是字符串、常数或一个列的值。
4、REPLACE()
返回被替换了指定子串的字符串。
REPLACE(<string_expression1>，<string_expression2>，<string_expression3>)用string_expression3替换在string_expression1中的子串string_expression2。
4、SPACE()
返回一个有指定长度的空白字符串。
SPACE(<integer_expression>)如果integer_expression值为负值，则返回NULL。
5、STUFF()
用另一子串替换字符串指定位置、长度的子串。
STUFF(<character_expression1>，<start_ position>，<length>，<character_expression2>)
如果起始位置为负或长度值为负，或者起始位置大于character_expression1的长度，则返回NULL值。
如果length长度大于character_expression1中start_ position以右的长度，则character_expression1只保留首字符。
六、数据类型转换函数
1、CAST()
CAST(<expression>AS<data_type>[ length ])
2、CONVERT()
CONVERT(<data_type>[ length ]，<expression>[，style])
1）data_type为SQLServer系统定义的数据类型，用户自定义的数据类型不能在此使用。
2）length用于指定数据的长度，缺省值为。
3）把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。
4）TEXT类型到CHAR或VARCHAR类型转换最多为个字符，即CHAR或VARCHAR数据类型是最大长度。
5）IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为个字符。
6）把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。
7）BIT类型的转换把非零值转换为，并仍以BIT类型存储。
8）试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“+”，以标识发生了这种截断。
9）用CONVERT（）函数的style选项能以不同的格式显示日期和时间。style是将DATATIME和SMALLDATETIME数据转换为字符串时所选用的由SQLServer系统提供的转换样式编号，不同的样式编号有不同的输出格式。
七、日期函数
1、day(date_expression)
返回date_expression中的日期值
2、month(date_expression)
返回date_expression中的月份值
3、year(date_expression)
返回date_expression中的年份值
4、DATEADD()
DATEADD(<datepart>，<number>，<date>)
返回指定日期date加上指定的额外日期间隔number产生的新日期。
5、DATEDIFF()
DATEDIFF(<datepart>，<date1>，<date2>)
返回两个指定日期在datepart方面的不同之处，即date2超过date1的差距值，其结果值是一个带有正负号的整数值。
6、DATENAME()
DATENAME(<datepart>，<date>)
以字符串的形式返回日期的指定部分此部分。由datepart来指定。
7、DATEPART()
DATEPART(<datepart>，<date>)
以整数值的形式返回日期的指定部分。此部分由datepart来指定。
DATEPART(dd，date)等同于DAY(date)
DATEPART(mm，date)等同于MONTH(date)
DATEPART(yy，date)等同于YEAR(date)
8、GETDATE()
以DATETIME的缺省格式返回系统当前时间