sql函数大全解读.docx
《sql函数大全解读.docx》由会员分享，可在线阅读，更多相关《sql函数大全解读.docx（13页珍藏版）》请在冰豆网上搜索。
sql函数大全解读
select语句中只能使用sql函数对字段进行操作（链接sqlserver），
select字段1from表1where字段1.IndexOf（"云"）=1;
这条语句不对的原因是indexof（）函数不是sql函数，改成sql对应的函数就可以了。
left（）是sql函数。
select字段1from表1wherecharindex（'云',字段1）=1;字符串函数对二进制数据、字符串和表达式执行不同的运算。
此类函数作用于CHAR、VARCHAR、BINARY、和VARBINARY数据类型以及可以隐式转换为CHAR或VARCHAR的数据类型。
可以在SELECT语句的SELECT和WHERE子句以及表达式中使用字符串函数。
常用的字符串函数有：
一、字符转换函数
1、ASCII（）
返回字符表达式最左端字符的ASCII码值。
在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。
2、CHAR（）
将ASCII码转换为字符。
如果没有输入0~255之间的ASCII码值，CHAR（）返回NULL。
3、LOWER（）和UPPER（）
LOWER（）将字符串全部转为小写;UPPER（）将字符串全部转为大写。
4、STR（）
把数值型数据转换为字符型数据。
STR（[，length[，]]）
length指定返回的字符串的长度，decimal指定返回的小数位数。
如果没有指定长度，缺省的length值为10，decimal缺省值为0。
当length或者decimal为负值时，返回NULL;
当length小于小数点左边（包括符号位）的位数时，返回length个*;
先服从length，再取decimal;
当返回的字符串位数小于length，左边补足空格。
二、去空格函数
1、LTRIM（）把字符串头部的空格去掉。
2、RTRIM（）把字符串尾部的空格去掉。
三、取子串函数
1、left（）
LEFT（，）
返回character_expression左起integer_expression个字符。
2、RIGHT（）
RIGHT（，）
返回character_expression右起integer_expression个字符。
3、SUBSTRING（）
SUBSTRING（，，length）
返回从字符串左边第starting_position个字符起length个字符的部分。
四、字符串比较函数
1、CHARINDEX（）
返回字符串中某个指定的子串出现的开始位置。
CHARINDEX（<’substring_expression’>，）
其中substring_expression是所要查找的字符表达式，expression可为字符串也可为列名表达式。
如果没有发现子串，则返回0值。
此函数不能用于TEXT和IMAGE数据类型。
2、PATINDEX（）
返回字符串中某个指定的子串出现的开始位置。
PATINDEX（<’%substring_expression%’>，）其中子串表达式前后必须有百分号“%”否则返回值为0。
与CHARINDEX函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于CHAR、VARCHAR和TEXT数据类型。
五、字符串操作函数
1、QUOTENAME（）
返回被特定字符括起来的字符串。
QUOTENAME（<’character_expression’>[，quote_character]）其中quote_character标明括字符串所用的字符，缺省值为“[]”。
2、REPLICATE（）
返回一个重复character_expression指定次数的字符串。
REPLICATE（character_expressioninteger_expression）如果integer_expression值为负值，则返回NULL。
3、REVERSE（）
将指定的字符串的字符排列顺序颠倒。
REVERSE（）其中character_expression可以是字符串、常数或一个列的值。
4、REPLACE（）
返回被替换了指定子串的字符串。
REPLACE（，，）用string_expression3替换在string_expression1中的子串string_expression2。
4、SPACE（）
返回一个有指定长度的空白字符串。
SPACE（）如果integer_expression值为负值，则返回NULL。
5、STUFF（）
用另一子串替换字符串指定位置、长度的子串。
STUFF（，，，）
如果起始位置为负或长度值为负，或者起始位置大于character_expression1的长度，则返回NULL值。
如果length长度大于character_expression1中start_position以右的长度，则character_expression1只保留首字符。
六、数据类型转换函数
1、CAST（）
CAST（AS[length]）
2、CONVERT（）
CONVERT（[length]，[，style]）
1）data_type为SQLServer系统定义的数据类型，用户自定义的数据类型不能在此使用。
2）length用于指定数据的长度，缺省值为30。
3）把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。
4）TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。
5）IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。
6）把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。
7）BIT类型的转换把非零值转换为1，并仍以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（，，）
返回指定日期date加上指定的额外日期间隔number产生的新日期。
5、DATEDIFF（）
DATEDIFF（，，）
返回两个指定日期在datepart方面的不同之处，即date2超过date1的差距值，其结果值是一个带有正负号的整数值。
6、DATENAME（）
DATENAME（，）
以字符串的形式返回日期的指定部分此部分。
由datepart来指定。
7、DATEPART（）
DATEPART（，）
以整数值的形式返回日期的指定部分。
此部分由datepart来指定。
DATEPART（dd，date）等同于DAY（date）
DATEPART（mm，date）等同于MONTH（date）
DATEPART（yy，date）等同于YEAR（date）
8、GETDATE（）
以DATETIME的缺省格式返回系统当前的日期和时间。
--聚合函数
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（'sqlserver',3）,right（'sqlserver',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的使用
--首先计算所有数的平均价格,如果低于30的话进入循环让所有的price翻倍,
--里面又有个if来判断如果最大的单价还大于50的话,退出循环,否则继续循环,知道最大单价大于50就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'toomuchforthemarkertobear'
--事务编程经典例子
--begintransaction是开始事务,committransaction是提交事务,rollbacktransaction是回滚事务
--这个例子是先插入一条记录,如果出现错误的话就回滚事务,也就是取消,并直接return（返回）,如果没错的话就commit提交这个事务了哦
--上面的那个return返回可以返回一个整数值,如果这个值是0的话就是执行的时候没出错,如果出错了就是一个负数,
--这个return也可以用在存储过程中,可用用exec@return_status=pro_name来获取这个值
usepubs
go
begintranmytran
insertintostores（stor_id,stor_name）
values（'333','mybooks'）
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.DBook'）
savetransactionbefore_insert_data2
go
insertintostores（stor_id,stor_name）
values（'5678','foreatBooks'）
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@P2='A'
SQLServer基本函数
SQLServer基本函数
1.字符串函数长度与分析用
1,datalength（Char_expr）返回字符串包含字符数,但不包含后面的空格
2,substring（expression,start,length）取子串，字符串的下标是从“1”，start为起始位置，length为字符串长度，实际应用中以len（expression）取得其长度
3,right（char_expr,int_expr）返回字符串右边第int_expr个字符，还用left于之相反
4,isnull（check_expression,replacement_value）如果check_expression為空，則返回replacement_value的值，不為空，就返回check_expression字符操作类
5,Sp_addtype自定義數據類型
例如：
EXECsp_addtypebirthday,datetime,'NULL'
6,setnocount{on|off}
使返回的结果中不包含有关受Transact-SQL语句影响的行数的信息。
如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。
SETNOCOUNT设置是在执行或运行时设置，而不是在分析时设置。
SETNOCOUNT为ON时，不返回计数（表示受Transact-SQL语句影响的行数）。
SETNOCOUNT为OFF时，返回计数
常识
在SQL查询中：
from后最多可以跟多少张表或视图：
256
在SQL语句中出现Orderby,查询时，先排序，后取
在SQL中，一个字段的最大容量是8000，而对于nvarchar（4000）,由于nvarchar是Unicode码。
SQLServer内置函数大全
1.1.字符串函数
2.
3.ascii（）,将字符转换为ASCII码,ASCII（'abc'）=97
4.
5.char（）,ASCII码转换为字符
6.
7.low（），upper（）
8.
9.str（a,b,c）转换数字为字符串。
a,是要转换的字符串。
b是转换以后的长度，c是小数位数。
str（123.456,8,2）=123.46
10.
11.ltrim（）,rtrim（）去空格
12.
13.left（n）,right（n）,substring（str,start,length）截取字符串
14.
15.c
升级会员 