搜档网
当前位置:搜档网 › SQL 函数大全

SQL 函数大全

SQL 函数大全
SQL 函数大全

第6节函数

目的:理解、掌握、运用几个常用函数。

SQL Server提供了5类函数:聚合函数,日期时间函数,数学函数,字符串函数,系统函数等。

6.1聚合函数与聚合查询

聚合函数对一组值执行计算并返回单一的值。聚合函数忽略空值。聚合函数经常与SELECT语句的GROUP BY子句一同使用构成聚合查询。

avg()

例1 查询药品的平均售价

SELECT avg(单价)

FROM 药品

例2 查询现金科目的借方金额的平均值

SELECT avg(借方金额)

FROM 凭证库

WHERE 科目名称= '现金'

思考:从语义上分析,查询结果有几行?

max()和min():

max ( <表达式> ):返回表达式的最大值。

min ( <表达式> ):返回表达式的最小值。

例3 查询凭证库表中借方金额的最大值

SELECT max(借方金额)

FROM 凭证库

例4查询凭证库表中借方金额的最小值

SELECT min(借方金额)

FROM 凭证库

例5查询凭证库表中借方金额的最大值和最小值

SELECT max(借方金额), min(借方金额)

FROM 凭证库

求均值函数avg()的完整格式是:avg ( [ ALL | DISTINCT ] <表达式> )。该函数返回组中值的平均值,忽略空值。ALL对所有的值进行聚合函数运算。ALL 是默认设置。DISTINCT指定

AVG 操作只使用每个值的唯一实例,而不管该值出现了多少次。执行计算时消除空值很重要,因为如果包含空值列,某些计算(如平均值)会不准确。

例如,表T1有一个数值型字段Num,如下:

执行:

SELECT avg(Num)

FROM T1

结果:60

从结果很容易看出,avg忽略了空值。

例如,表T2如下:

执行:

SELECT avg(distinct Num)

FROM T2

结果:60

动手练习

查询2003年1月份现金凭证的借方金额的最大值、最小值和平均值。

sum()

例6 求借方金额的汇总

SELECT sum(借方金额) AS 借方金额汇总

FROM 凭证库

例7 求借方金额、贷方金额的汇总

SELECT sum(借方金额) AS 借方金额汇总, sum(贷方金额) AS 贷方金额汇总

FROM 凭证库

求和函数sum()的完整格式是:sum ( [ ALL | DISTINCT ] <表达式> ),该函数返回表达式中所有值的和。sum只能用于数字列。忽略空值。ALL对所有的值进行聚合函数运算。ALL 是默认设置。DISTINCT指定SUM 返回唯一值的和。

动手练习

查询2003年1月份现金凭证的借方金额总额和贷方金额总额的差值。

count():

例8 查询住院收费表中,10205部门有多少条收费记录。

SELECT count(*)

FROM 住院收费

WHERE 部门号='10205'

例9 查询住院收费表中有多少条收费记录

SELECT count(*)

FROM 住院收费

下面查询是否与上面查询等价?

SELECT count(医生号)

FROM 住院收费

计数函数count()的完整格式是:count ( { [ ALL | DISTINCT ] <表达式> } | * ) ),该函数返回组中行的数量。count(*) 返回组中行的数量,count(<表达式>) 对组中的每一行都计算<表达式>并返回<表达式>取值不为空值的行的数量。默认情况下,以上两种用法都包括重复行,而count(DISTINCT <表达式>) 对<表达式>的重复值只统计一次。ALL 是默认设置。

已知表T1和T2,表中均含有空值。请分析下面查询的结果:

1) SELECT count(*)

FROM T1

2) SELECT count(num)

FROM T1

3) SELECT count(distinct num)

FROM T2

注意:使用聚合函数的查询称为聚合查询,查询结果在没有使用GROUP BY 子句的情况下最多只有一行。而且,在没有使用GROUP BY子句的情况下,SELECT后面的每个表达式都要使用聚合函数。例如,以下SQL语句是错误的:

SELECT 科目编码, 科目名称, min(借方金额)

FROM 凭证库

在查询分析器中执行,将返回以下错误信息:

服务器: 消息8118,级别16,状态1,行1

列'凭证库.科目编码' 在选择列表中无效,因为该列未包含在聚合函数中,并且没有GROUP BY 子句。

动手练习

1、查询’10101’部门的医生数

2、查询凭证库表中出现了多少种会计科目

3、解释以下两个SQL语句的功能

a)SELECT count(借方金额)

FROM 凭证库

WHERE 科目名称=’现金’

b)SELECT sum(借方金额)

FROM 凭证库

WHERE 科目名称=’现金’

6.2 日期和时间函数

getdate() 得到当前日期(时间)

例1 查询2003年的凭证的源凭证号和凭证发生的月份

SELECT 源凭证号,datename(mm, 凭证日期) AS 月份

FROM 凭证库

WHERE datename(yyyy,凭证日期) = '2003'

例2 从getdate()返回的日期中提取月份名。

SELECT datename(mm, getdate()) AS 当前月

getdate()函数返回当前系统的日期和时间,格式是:datename(<日期分量>, <日期>)。该函数以字符串形式返回<日期分量>指定部分。日期名称

<日期分量>

datepart(<日期分量>, <日期>) 以整数形式返回<日期分量>指定部分。dateadd(<日期分量>, <数值>, <日期> )返回以<日期分量>指定方式表示的<日期>加上<数值>以后新的值。datediff (<日期分量>, <开始日期>, <结束日期>)以datepart指定方式表示的<结束日期>和<开始日期>之差。日期的一部分

例3 查询2003年的凭证的源凭证号和凭证发生的月份

SELECT 源凭证号,datename(mm, 凭证日期) AS 月份

FROM 凭证库

WHERE datepart(yyyy,凭证日期) = 2003

例4

SELECT dateadd(dd,9,getdate()) AS '9天后'

例5 假定某人生日1971年2月20日,计算年龄。

SELECT datediff(yy, '1971-02-20', getdate()) AS 年龄

也可以使用year()和month()函数。分别等价于datepart(yyyy, <日期>) 和datepart(mm, <日期>)。

6.3 数学函数

abs(<数字表达式> )返回给定<数字表达式>的绝对值。

例6 显示了abs函数对三个不同数字的效果。

SELECT abs(-1.0), abs(0.0), abs(1.0)

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

例7 把123.4545四舍五入,小数点后保留2位。

SELECT round(123.4545, 2)

结果:123.4500

例8对123.45十位上的数四舍五入。

SELECT round(123.45, -2)

结果:100.00

round()函数的格式为:round ( <数值表达式>, <长度>)。当<长度>为正数时,<数字表达

式>四舍五入为<长度>所指定的小数位数。当<长度>为负数时,<数字表达式>则按<长度>所指定的在小数点的左边四舍五入。

floor(<数值表达式>)返回小于或等于所给<数值表达式>的最大整数。

例9:说明正数、负数和货币值在floor() 函数中的运用。

SELECT floor (123.45), floor (-123.45), floor ($123.45), floor (123)

结果为与<数值表达式>数据类型相同的计算值的整数部分。

123 -124 123.0000 123

ceiling (<数值表达式> )返回大于或等于所给<数值表达式>的最小整数。

例10:显示使用CEILING 函数的正数、负数和零值。

SELECT ceiling($123.45), ceiling($-123.45), ceiling($0.0)

结果:

124.00 -123.00 0.00

power (<数值表达式> , <次方> )返回给定<数值表达式>的<次方>的值。

例如11:求32。

SELECT power(3, 2)

课题练习:

汇总2003年1月份的贷方金额和借方金额,四舍五入到小数点后面2位。

6.4 字符串函数

字符串函数对字符串输入值执行操作,返回字符串或数字值。

ascii (<字符表达式> )返回字符表达式最左端字符的ASCII 代码值。

char (<整数表达式> )将ASCII 代码转换为字符的字符串函数。

例1:

SELECT ascii('A')

结果:65

SELECT ascii('Abc')

结果:65

SELECT char(65)

结果:A

substring( <表达式>, <开始位置> , <长度> )返回字符、binary、text 或image 表达式的一部分。<表达式>是字符串、二进制字符串、text、image、列或包含列的表达式。<开始位置>是一个整数,指定子串的开始位置。<长度>是一个整数,指定子串的长度(要返回的字符数或字节数)。含义:截取函数

例1 摘要第二个字是“费”

SELECT * FROM 凭证库WHERE substring(摘要,2,1) = '费'

例2:科目名称前2个字是“现金”

SELECT *

FROM 凭证库

WHERE substring(科目名称,1,2) = '现金'

str ( <浮点表达式> [ , <总长度> [ ,<小数点右边的位数> ] ] )由数字数据转换字符数据。<总长度>包括小数点、符号、数字或空格,默认值为10。<小数点右边的位数>是小数点右边的位数。含义:将整数转换为字符串

例3:将包含五个数字和一个小数点的表达式转换为有六个位置的字符串。数字的小数部分四舍五入为一个小数位。

SELECT str(123.45, 6, 1)

结果为:123.5

left ( <字符串> , <整数表达式> )返回从<字符串>左边开始指定个数的字符。左截选

例4:摘要第1个字是“费”

SELECT * FROM 凭证库WHERE left(摘要,1) = '费'

例5:查询101及其下级科目的行

SELECT * FROM 凭证库WHERE left(科目编码,3)='101'

例6:查出支出类,假设以’5’打头

SELECT * FROM 凭证库WHERE left(科目编码,1)='5'

right ( <字符串> , <整数表达式> )返回<字符串>中从右边开始<整数表达式>个数的字符。右截取

len ( <字符串> ):返回<字符串>的字符(而不是字节,每个汉字和英文字母均为一个字符)个数,其中不包含尾随空格。含义:求出字符段长度

例6:查出一级科目(一级科目为3位)

SELECT * FROM 凭证库WHERE Len(科目编码)=3

例7:查出二级科目(二级科目为5位)

SELECT * FROM 凭证库WHERE Len(科目编码)=5

例8:查出一级和二级科目

SELECT * FROM 凭证库WHERE Len(科目编码)<=5

ltrim ( <字符串> )返回删除起始空格后的字符串。含义:从左开始截取掉空格

例9:使用ltrim()删除字符串中的起始空格。

SELECT LTRIM(' A B')

结果' A B'

rtrim (<字符串> ):返回截断所有尾随空格后的字符串。

例10:使用rtrim()删除字符串中的尾随空格。

SELECT rtrim('AB ') + 'END'

结果'ABEND'

例11:查询摘要为“招待费”的行。不计“招待费”后面的空格。

SELECT *

FROM 凭证库

WHERE rtrim(摘要)='招待费'

思考:既把左边的空格去掉,又把右边的空格去掉,怎么办?

答:SELECT * FROM 凭证库WHERE ltrim(rtrim(摘要))='招待费'

课堂习题

下列查询正确吗?

SELECT substring 凭证号, 1,2 FROM 凭证库

答:不正确,应该为SELECT substring(凭证号,1,2)FROM 凭证库

6.5 系统函数

数据类型转换函数有两个,他们提供了相同的功能:

CAST(<表达式> AS <数据类型>)把<表达式> 转换为<数据类型>。

CONVERT (<数据类型> [(长度)], <表达式>)把<表达式> 转换为<数据类型>。

例1:使用LIKE 查询借方金额中含有小数点的行。

因为LIKE只能适用于字符串

SELECT 科目名称, 借方金额

FROM 凭证库

WHERE CAST(借方金额AS char(20)) LIKE '%.%' 或cast(借方金额as varchar)

下面语句与上面语句等价:

SELECT 科目名称, 借方金额

FROM 凭证库

WHERE CONVERT (char(20), 借方金额) LIKE '%.%'

6.6 ISNULL

ISNULL ( <被替换的表达式> , <替换表达式> )如果<被替换的表达式>为NULL,则使用指定的值替换NULL;如果<被替换的表达式>不为NULL,那么返回该表达式的值。

例1:下面的示例查找会计科目表,如果某科目的上级科目编码为NULL,则显示“无”。

SELECT 科目编码,科目名称,ISNULL(上级科目编码,'无')

FROM 会计科目表

如果数据中可能包含空值,但不想在查询结果中出现空值,创建查询将空值转换成其它值是一个办法。

本节小结

通常并不需要记住这么多函数,但应该掌握这些函数的分类或者典型函数的用法。当需要时可以通过查找联机丛书寻找了解所需函数的确切用法。下面是计算机审计常用SQL Server 2000函数一览:

一、聚合函数

聚合函数的运算对象是集合,返回单个值。除了count()以外,聚合函数忽略空值。下表中ALL指对指定集合所有的值进行聚合函数运算,默认是ALL。DISTINCT只使用每个值的唯一实例,而不管该值出现了多少次。

二、日期时间函数

三、数学函数

算术函数(例如ABS、CEILING、DEGREES、FLOOR、POWER、RADIANS 和SIGN)返回与输入值相同数据类型的值。三角函数和其它函数(包括EXP、LOG、LOG10、SQUARE 和SQRT)将输入值投影到float 并返回float 值。

四、字符串函数

五、系统函数

动手练习

1、在计算机审计中需要掌握这么多函数吗?

2、从住院收费表中查询住院流水号,住院日期和医生号。要求,对于医生号为空值的记录,医生号字段显示“无”。

3、从凭证库中查询科目名称、摘要、借方金额、贷方金额,要求借方金额和贷方金额只显示一位小数。

sql_函数大全_比较常用的一些函数整理

sql 函数大全比较常用的一些函数整理 select语句中只能使用sql函数对字段进行操作(链接sql server), select 字段1 from 表1 where 字段1.IndexOf("云")=1; 这条语句不对的原因是indexof()函数不是sql函数,改成sql对应的函数就可以了。left()是sql函数。 select 字段1 from 表1 where charindex('云',字段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() 把字符串头部的空格去掉。

SQL SERVER2008函数大全

SQL Server2008函数大全(完整版) SQL2008表达式:是常量、变量、列或函数等与运算符的任意组合。 一、字符串函数 函数名称参数示例说明 ascii(字符串表达式) select ascii('abc')返回97 说明:返回字符串中最左侧的字符的ASCII码。 char(整数表达式) select char(100)返回d 说明:把ASCII码转换为字符。 介于0和255之间的整数。如果该整数表达式不在此范围内,将返回 NULL值。 charindex(字符串表达式1,字符串表达式2[,整数表达式])instr select charindex('ab','BCabTabD')返回3 select charindex('ab','BCabTabD',4)返回6 说明:在字符串2中查找字符串1,如果存在返回第一个匹配的位置,如果不存在返回0。如果字符串1和字符串2中有一个是null则返回null。可以指定在字符串2中查找的起始位置。 patindex(字符串表达式1,字符串表达式2) select patindex('%ab%','123ab456')返回4 select patindex('ab%','123ab456')返回0 select patindex('___ab%','123ab456')返回1 select patindex('___ab_','123ab456')返回0 说明:在字符串表达式1中可以使用通配符,此字符串的第一个字符和最后一个字符通常是%。%表示任意多个字符,_表示任意字符返回字符串表达式2中字符串表达式1所指定模式第一次出现的起始位置。没有找到返回0。 difference(字符串表达式1,字符串表达式2) select difference('Green','Greene')返回4 返回一个0到4的整数值,指示两个字符表达式的之间的相似程度。 0表示几乎不同或完全不同, 4表示几乎相同或完全相同。 说明:注意相似并不代表相等 left(字符串表达式,整数表达式) select left('abcdefg',2)返回ab 说明:返回字符串中从左边开始指定个数的字符。

整理的SQL常用函数

create table test (id int, value varchar(10)) insertinto test values('1','aa') insertinto test values('1','bb') insertinto test values('2','aaa') insertinto test values('2','bbb') insertinto test values('2','ccc') insertinto test values('3','aa') insertinto test values('4','bb') select*from test select id, [values] =stuff((select','+ [values] from test t where id = test.id forxmlpath('')), 1 , 1 ,'') from test groupby id stuff(param1,startIndex,length, param2) 说明:将param1中自startIndex(SQL中都是从1开始,而非0)起,删除length个字符,然后用param2替换删掉的字符。*/

COUNT()函数用于返回一个列内所有非空值的个数,这是一个整型值。 由于COUNT(*)函数会忽略NULL值,所以这个查询的结果是2。 三、SUM()函数 SUM()函数是最常用的聚合函数之一,它的功能很容易理解:和AVG()函数一样,它用于数值数据类型,返回一个列范围内所有非空值的总和。 四、CAST()函数 CAST()函数的参数是一个表达式,它包括用AS关键字分隔的源值和目标数据类型。 以下例子用于将文本字符串'123'转换为整型: SELECT CAST('123' AS int) 返回值是整型值123。 如果试图将一个代表小数的字符串转换为整型值,又会出现什么情况呢? SELECT CAST('123.4' AS int) CAST()函数和CONVERT()函数都不能执行四舍五入或截断操作。由于123.4不能用int数据类型来表示,所以对这个函数调用将产生一个错误。 Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value '123.4' to a column of data type int. 在将varchar值'123.4' 转换成数据类型int时失败。 要返回一个合法的数值,就必须使用能处理这个值的数据类型。对于这个例子,存在多个可用的数据类型。如果通过CAST()函数将这个值转换为decimal类型,需要首先定义decimal 值的精度与小数位数。在本例中,精度与小数位数分别为9与2。精度是总的数字位数,包括小数点左边和右边位数的总和。而小数位数是小数点右边的位数。这表示本例能够支持的最大的整数值是9999999,而最小的小数是0.01。 SELECT CAST('123.4' AS decimal(9,2)) decimal数据类型在结果网格中将显示有效小数位:123.40 精度和小数位数的默认值分别是18与0。如果在decimal类型中不提供这两个值,SQL Server 将截断数字的小数部分,而不会产生错误。 SELECT CAST('123.4' AS decimal) 结果是一个整数值:123 五、CONVERT()函数 对于简单类型转换,CONVERT()函数和CAST()函数的功能相同,只是语法不同。 CAST()函数一般更容易使用,其功能也更简单。 CONVERT()函数的优点是可以格式化日期和数值,它需要两个参数:第1个是目标数据类型,第2个是源数据。 CONVERT()函数还具有一些改进的功能,它可以返回经过格式化的字符串值,且可以把日期值格式化成很多形式。有28种预定义的符合各种国际和特殊要求的日期与时间输出格式。 六、STR()函数 这是一个将数字转换为字符串的快捷函数。这个函数有3个参数:数值、总长度和小数位数。如果数字的整数位数和小数位数(要加上小数点占用的一个字符)的总和小于总长度,对结果中左边的字符将用空格填充。在下面第1个例子中,包括小数点在内一共是5个字符。结果

经典SQL语句大全

一、基础 1、说明:创建数据库 CREATE DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1. dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明:创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表: A:create table tab_new like tab_old (使用旧表创建新表) B:create table tab_new as select col1,col2… from tab_old definition only 5、说明:删除新表 drop table tabname 6、说明:增加一个列 Alter table tabname add column col type 注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 7、说明:添加主键:Alter table tabname add primary key(col) 说明:删除主键: Alter table tabname drop primary key(col) 8、说明:创建索引:create [unique] index idxname on tabname(col….) 删除索引:drop index idxname 注:索引是不可更改的,想更改必须删除重新建。 9、说明:创建视图:create view viewname as select statement 删除视图:drop view viewname 10、说明:几个简单的基本的sql语句 选择:select * from table1 where 范围 插入:insert into table1(field1,field2) values(value1,value2) 删除:delete from table1 where 范围 更新:update table1 set field1=value1 where 范围 查找:select * from table1 where field1 like ’%value1%’ ---like的语法很精妙,查资料! 排序:select * from table1 order by field1,field2 [desc] 总数:select count as totalcount from table1

SQL Server系统函数大全

sql server 系统函数大全 一、字符转换函数 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_expression integer_expression) 如果 integer_expression 值为负值,则返回NULL 。

sqlserver函数大全

在SQL Server在线图书或者在线帮助系统中,函数的可选参数用方括号表示。在下列的CONVERT()函数例子中,数据类型的length和style参数是可选的: CONVERT (data-type [(length)], expression[,style]) 可将它简化为如下形式,因为现在不讨论如何使用数据类型: CONVERT(date_type, expression[,style]) 根据上面的定义,CONVERT()函数可接受2个或3个参数。因此,下列两个例子都是正确的: SELECT CONVERT(Varchar(20),GETDATE()) SELECT CONVERT(Varchar(20),GETDATE(), 101) 这个函数的第一个参数是数据类型Varchar(20),第2个参数是另一个函数GETDATE()。GETDATE()函数用datetime数据类型将返回当前的系统日期和时间。第2条语句中的第3个参数决定了日期的样式。这个例子中的101指以mm/dd/yyyy格式返回日期。本章后面将详细介绍GETDATE()函数。即使函数不带参数或者不需要参数,调用这个函数时也需要写上一对括号,例如GETDATE()函数。注意在书中使用函数名引用函数时,一定要包含括号,因为这是一种标准形式。 确定性函数 由于数据库引擎的内部工作机制,SQL Server必须根据所谓的确定性,将函数分成两个不同的组。这不是一种新时代的信仰,只和能否根据其输入参数或执行对函数输出结果进行预测有关。如果函数的输出只与输入参数的值相关,而与其他外部因素无关,这个函数就是确定性函数。如果函数的输出基于环境条件,或者产生随机或者依赖结果的算法,这个函数就是非确定性的。例如,GETDATE()函数是非确定性函数,因为它不会两次返回相同的值。为什么要把看起来简单的事弄得如此复杂呢?主要原因是非确定性函数与全局变量不能在一些数据库编程对象中使用(如用户自定义函数)。部分原因是SQL Server缓存与预编译可执行对象的方式。例如,即席查询可以使用任何函数,不过如果打算构建先进的、可重用的编程对象,理解这种区别很重要。 以下这些函数是确定性的: ●?AVG()(所有的聚合函数都是确定性的) ●?CAST() ●?CONVERT() ●?DATEADD() ●?DATEDIFF() ●?ASCII() ●?CHAR() ●?SUBSTRING() 以下这些函数与变量是非确定性的: ●?GETDATE()

SQL日期函数大全

通常,你需要获得当前日期和计算一些其他的日期,例如,你的程序可能需要判断一个月的第一天或者最后一天。你们大部分人大概都知道怎样把日期进行分割(年、月、日等),然后仅仅用分割出来的年、月、日等放在几个函数中计算出自己所需要的日期!在这篇文章里,我将告诉你如何使用DATEADD和DATEDIFF函数来计算出在你的程序中可能你要用到的一些不同日期。 在使用本文中的例子之前,你必须注意以下的问题。大部分可能不是所有例子在不同的机器上执行的结果可能不一样,这完全由哪一天是一个星期的第一天这个设置决定。第一天(DATEFIRST)设定决定了你的系统使用哪一天作为一周的第一天。所有以下的例子都是以星期天作为一周的第一天来建立,也就是第一天设置为7。假如你的第一天设置不一样,你可能需要调整这些例子,使它和不同的第一天设置相符合。你可以通过@@DATEFIRST函数来检查第一天设置。 为了理解这些例子,我们先复习一下DATEDIFF和DATEADD函数。DATEDIFF函数计算两个日期之间的小时、天、周、月、年等时间间隔总数。DATEADD函数计算一个日期通过给时间间隔加减来获得一个新的日期。要了解更多的DATEDIFF和DATEADD函数以及时间间隔可以阅读微软联机帮助。 使用DATEDIFF和DATEADD函数来计算日期,和本来从当前日期转换到你需要的日期的考虑方法有点不同。你必须从时间间隔这个方面来考虑。比如,从当前日期到你要得到的日期之间有多少时间间隔,或者,从今天到某一天(比如1900-1-1)之间有多少时间间隔,等等。理解怎样着眼于时间间隔有助于你轻松的理解我的不同的日期计算例子。 一个月的第一天 第一个例子,我将告诉你如何从当前日期去这个月的最后一天。请注意:这个例子以及这篇文章中的其他例子都将只使用DATEDIFF和DATEADD函数来计算我们想要的日期。每一个例子都将通过计算但前的时间间隔,然后进行加减来得到想要计算的日期。 这是计算一个月第一天的SQL 脚本: SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) 我们把这个语句分开来看看它是如何工作的。最核心的函数是getdate(),大部分人都知道这个是返回当前的日期和时间的函数。下一个执行的函数DATEDIFF(mm,0,getdate())是计算当前日期和“1900-01-01 00:00:00.000”这个日期之间的月数。记住:时期和时间变量和毫秒一样是从“1900-01-01 00:00:00.000”开始计算的。这就是为什么你可以在DATEDIFF函数中指定第一个时间表达式为“0”。下一个函数是DATEADD,增加当前日期到“1900-01-01”的月数。通过增加预定义的日期“1900-01-01”和当前日期的月数,我们可以获得这个月的第一天。另外,计算出来的日期的时间部分将会是“00:00:00.000”。 这个计算的技巧是先计算当前日期到“1900-01-01”的时间间隔数,然后把它加到“1900-01-01”上来获得特殊的日期,这个技巧可以用来计算很多不同的日期。下一个例子也是用这个技巧从当前日期来产生不同的日期。 本周的星期一 这里我是用周(wk)的时间间隔来计算哪一天是本周的星期一。 SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

Sql server 函数大全

Sql 2008 函数大全 SQL2008 表达式:就是常量、变量、列或函数等与运算符的任意组合。 1、字符串函数 函数名称参数示例说明 ascii(字符串表达式) select ascii('abc') 返回 97 返回字符串中最左侧的字符的ASCII 码。 char(整数表达式) select char(100) 返回 d 把ASCII 码转换为字符。 介于0 与 255 之间的整数。如果该整数表达式不在此范围内,将返回NULL 值。charindex(字符串表达式 1, 字符串表达式2[,整数表达式]) select charindex('ab','BCabTabD') 返回 3 select charindex('ab','BCabTabD', 4) 返回 6 在字符串2 中查找字符串 1,如果存在返回第一个匹配的位置,如果不存在返回0。如果字符串 1 与字符串 2 中有一个就是null 则返回 null。 可以指定在字符串2 中查找的起始位置。 difference(字符串表达式 1,字符串表达式 2) select difference('Green','Greene') 返回 4 返回一个0 到 4 的整数值,指示两个字符表达式的之间的相似程度。 0 表示几乎不同或完全不同, 4 表示几乎相同或完全相同。 注意相似并不代表相等 left(字符串表达式,整数表达式) select left('abcdefg',2) 返回 ab 返回字符串中从左边开始指定个数的字符。 //貌似下标从1开始? right(字符串表达式,整数表达式) select right('abcdefg',2) 返回 fg 返回字符串中从右边开始指定个数的字符。 len(字符串表达式) select len('abcdefg')返回 7 select len('abcdefg') 返回 7 返回指定字符串表达式的字符数,其中不包含尾随空格。 Oracle与mysql 里面就是 select length(‘aaa’); lower(字符串表达式) select lower('ABCDEF')返回 abcdef 返回大写字符数据转换为小写的字符表达式。 upper(字符串表达式) select upper('abcdef')返回 ABCDEF 返回小写字符数据转换为大写的字符表达式。

SQLServer用户自定义函数详细介绍

SQL Server用户自定义函数 用户自定义函数不能用于执行一系列改变数据库状态的操作,但它可以像系统函数一样在查询或存储过程等的程序段中使用,也可以像存储过程一样通过EXECUTE 命令来执行。在 SQL Server 中根据函数返回值形式的不同将用户自定义函数分为三种类型: (1) 标量函数 标量函数返回一个确定类型的标量值,其返回值类型为除 TEXT 、 NTEXT 、IMAGE 、 CURSOR 、 TIMESTAMP 和 TABLE 类型外的其它数据类型。函数体语句定义 在 BEGIN-END 语句内。在 RETURNS 子句中定义返回值的数据类型,并且函数的最后一条语句必须为 Return 语句。创建标量函数的格式: Create Function 函数名(参数) Returns 返回值数据类型 [With {Encryption|Schemabinding}] [AS] BEGIN SQL 语句 ( 必须有 Return 子句 ) END 举例: ******************************************************************* CREATE FUNCTION dbo.Max ( @a int, @b int ) RETURNS int AS BEGIN DECLARE @max int IF @a>@b SET @max=@a ELSE SET @max=@b Return @max END *******************************************************************调用标量函数可以在 T-SQL 语句中允许使用标量表达式的任何位置调用返 回标量值(与标量表达式的数据类型相同)的任何函数。必须使用至少由两部分组成名称的函数来调用标量值函数,即架构名 . 对象名,如 dbo.Max(12,34) 。 (2) 内联表值函数 内联表值型函数以表的形式返回一个返回值,即它返回的是一个表。内联表 值型函数没有由 BEGIN-END 语句括起来的函数体。其返回的表是由一个位于RETURN 子句中的 SELECT 命令从数据库中筛选出来。内联表值型函数功能相当 于一个参数化的视图。

SQL函数用法大全

SQL 是用于访问和处理数据库的标准的计算机语言。 什么是SQL? ?SQL 指结构化查询语言 ?SQL 使我们有能力访问数据库 ?SQL 是一种ANSI 的标准计算机语言 编者注:ANSI,美国国家标准化组织 SQL 能做什么? ?SQL 面向数据库执行查询 ?SQL 可从数据库取回数据 ?SQL 可在数据库中插入新的纪录 ?SQL 可更新数据库中的数据 ?SQL 可从数据库删除记录 ?SQL 可创建新数据库 ?SQL 可在数据库中创建新表 ?SQL 可在数据库中创建存储过程 ?SQL 可在数据库中创建视图 ?SQL 可以设置表、存储过程和视图的权限 SQL 是一种标准- 但是... SQL 是一门ANSI 的标准计算机语言,用来访问和操作数据库系统。SQL 语句用于取回和更新数据库中的数据。SQL 可与数据库程序协同工作,比如MS Access、DB2、Informix、MS SQL Server、Oracle、Sybase 以及其他数据库系统。 不幸地是,存在着很多不同版本的SQL 语言,但是为了与ANSI 标准相兼容,它们必须以相似的方式共同地来支持一些主要的关键词(比如SELECT、UPDATE、DELETE、INSERT、WHERE 等等)。 注释:除了SQL 标准之外,大部分SQL 数据库程序都拥有它们自己的私有扩展! 在您的网站中使用SQL 要创建发布数据库中数据的网站,您需要以下要素: ?RDBMS 数据库程序(比如MS Access, SQL Server, MySQL) ?服务器端脚本语言(比如PHP 或ASP) ?SQL ?HTML / CSS RDBMS

SQL数据库语句大全大全(完全整理版)

SQL语句大全--语句功能 --数据操作 SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 UPDATE --更新数据库表中的数据 -数据定义 CREATE TABLE --创建一个数据库表 DROP TABLE --从数据库中删除表 ALTER TABLE --修改数据库表结构 CREATE VIEW --创建一个视图 DROP VIEW --从数据库中删除视图 CREATE INDEX --为数据库表创建一个索引 DROP INDEX --从数据库中删除索引 CREATE PROCEDURE --创建一个存储过程 DROP PROCEDURE --从数据库中删除存储过程CREATE TRIGGER --创建一个触发器 DROP TRIGGER --从数据库中删除触发器 CREATE SCHEMA --向数据库添加一个新模式DROP SCHEMA --从数据库中删除一个模式CREATE DOMAIN --创建一个数据值域 ALTER DOMAIN --改变域定义 DROP DOMAIN --从数据库中删除一个域 --数据控制 GRANT --授予用户访问权限 DENY --拒绝用户访问 REVOKE --解除用户访问权限 --事务控制 COMMIT --结束当前事务 ROLLBACK --中止当前事务 SET TRANSACTION --定义当前事务数据访问特征 --程序化SQL DECLARE --为查询设定游标 EXPLAN --为查询描述数据访问计划 OPEN --检索查询结果打开一个游标

SQL常用函数,整理

一数学函数 1,求绝对值ABS() 2,求指数POWER(x,y); 3,求平方根SQRT() select FWeight, SQRT(FWeight) from t_person; 4 求随机数 A,mysql RAND()select rand(); B,sqlserver rand() ,rand(x); select rand(), select rand(9527)支持种子 5舍入到最大整数Ceiling() select FWeight, ceiling(FWeight), ceiling(FWeight * -1) from t_person; 6舍入到最小整数floor() 7,四舍五入round(m,d) m为四舍五入的数值,d为计算精度(特别地d还可以为负值)如36.63精度-1为40 Round(m)为m值0精度 8求正弦值sin(m); select sin(1) from t_person 求余弦值cos(m); 求返正弦值asin(m); 求反余弦acos(m); 9求正切值tan(m) 反正切atan(m) 求余切cotm() 10求圆周率PI(); 11.弧度转换为角度degrees(m);角度转换为弧度制radians() 12求符号sign();(符号函数) 13 求整除余数mod(m,d);m为除数d 为被除数(sqlserver中直接用%)14,求自然对数LOG(m); 求10为底得对数LOG10(); 二字符串函数 1 计算字符串长度length(m); select length(FName) from t_person Sqlserver中为len(); 2字符串转换为小写Lower(m) 字符串转换为大写UPPER(m) 3截去字符串左侧空格ltrim(m); 截去字符串右侧空白rtrim(m); 截去字符串两侧空白trim(m) 5取子字符串substring(string,start_position,length); 6计算子字符串的位置instr(string, substring);若不存在子字符串则返回0 7从左侧开始取字符串Left(string, length); 从右侧开始取字符串Right(string, length); 8字符串替换replace(string,string_tobe_replace, string_to_replace); 9得到字符的ASCII码ASCII(m)如果参数为字符串则返回第一个字符的Ascii 码

sql函数详尽说明大全

Sql函数说明 一旦成功地从表中检索出数据,就需要进一步操纵这些数据,以获得有用或有意义的结果。这些要求包括:执行计算与数学运算、转换数据、解析数值、组合值和聚合一个范围内的值等。 下表给出了T-SQL函数的类别和描述。 函数的组成 函数的目标是返回一个值。大多数函数都返回一个标量值(scalar value),标量值代表一个数据单元或一个简单值。实际上,函数可以返回任何数据类型,包括表、游标等可返回完整的多行结果集的类型。本章不准备讨论到这个深度,第12章将讲解如何创建和使用用户自定义函数,以返回更复杂的数据。 函数己经存在很长时间了,它的历史比SQL还要长。在几乎所有的编程语言中,函数调用的方式都是相同的: Result=Function() 在T-SQL中,一般用SELECT语句来返回值。如果需要从查询中返回一个值,就可以把SELECT当成输出运算符,而不用使用等号: SELECT Function() 一个论点

对于SQL函数而言,参数表示输入变量或者值的占位符。函数可以有任意个参数,有些参数是必须的,而有些参数是可选的。可选参数通常被置于以逗号隔开的参数表的末尾,以便于在函数调用中去除不需要的参数。 在SQL Server在线图书或者在线帮助系统中,函数的可选参数用方括号表示。在下列的CONVERT()函数例子中,数据类型的length和style参数是可选的: CONVERT (data-type [(length)], expression[,style]) 可将它简化为如下形式,因为现在不讨论如何使用数据类型: CONVERT(date_type, expression[,style]) 根据上面的定义,CONVERT()函数可接受2个或3个参数。因此,下列两个例子都是 这个函数的第一个参数是数据类型Varchar(20),第2个参数是另一个函数GETDATE()。GETDATE()函数用datetime数据类型将返回当前的系统日期和时间。第2条语句中的第3个参数决定了日期的样式。这个例子中的101指以mm/dd/yyyy格式返回日期。本章后面将详细介绍GETDATE()函数。即使函数不带参数或者不需要参数,调用这个函数时也需要写上一对括号,例如GETDATE()函数。注意在书中使用函数名引用函数时,一定要包含括号,因为这是一种标准形式。 确定性函数 由于数据库引擎的内部工作机制,SQL Server必须根据所谓的确定性,将函数分成两个不同的组。这不是一种新时代的信仰,只和能否根据其输入参数或执行对函数输出结果进行预测有关。如果函数的输出只与输入参数的值相关,而与其他外部因素无关,这个函数就是确定性函数。如果函数的输出基于环境条件,或者产生随机或者依赖结果的算法,这个函数就是非确定性的。例如,GETDATE()函数是非确定性函数,因为它不会两次返回相同的值。为什么要把看起来简单的事弄得如此复杂呢?主要原因是非确定性函数与全局变量不能在一些数据库编程对象中使用(如用户自定义函数)。部分原因是SQL Server缓存与预编译可执行对象的方式。例如,即席查询可以使用任何函数,不过如果打算构建先进的、可重用的编程对象,理解这种区别很重要。 以下这些函数是确定性的: ●?AVG()(所有的聚合函数都是确定性的) ●?CAST() ●?CONVERT() ●?DATEADD() ●?DATEDIFF() ●?ASCII() ●?CHAR() ●?SUBSTRING() 以下这些函数与变量是非确定性的: ●?GETDATE()

informix sql函数使用说明大全

informix sql函数使用说明大全 一、内部函数 1、内部合计函数 1)COUNT(*)返回行数 2)COUNT(DISTINCT COLNAME)返回指定列中唯一值的个数 3)SUM(COLNAME/EXPRESSION)返回指定列或表达式的数值和; 4)SUM(DISTINCT COLNAME)返回指定列中唯一值的和 5)AVG(COLNAME/EXPRESSION)返回指定列或表达式中的数值平均值6)AVG(DISTINCT COLNAME)返回指定列中唯一值的平均值 7)MIN(COLNAME/EXPRESSION)返回指定列或表达式中的数值最小值8)MAX(COLNAME/EXPRESSION)返回指定列或表达式中的数值最大值2、日期与时间函数 1)DAY(DATE/DATETIME EXPRESSION)返回指定表达式中的当月几号2)MONTH(DATE/DATETIME EXPRESSION)返回指定表达式中的月份 3)YEAR(DATE/DATETIME EXPRESSION)返回指定表达式中的年份 4)WEEKDAY(DATE/DATETIME EXPRESSION)返回指定表达式中的当周星期几 5)DATE(NOT DATE EXPRESSION)返回指定表达式代表的日期值 6)TODAY 返回当前日期的日期值 7)CURRENT[FIRST TO LAST] 返回当前日期的日期时间值 8)COLNAME/EXPRESSION UNITS PRECISION 返回指定精度的指定单位数9)MDY(MONTH,DAY,YEAR)返回标识指定年、月、日的日期值 10)DATETIME(DATE/DATETIME EXPRESSION)FIRST TO LAST 返回表达式代表的日期时间值 11)INTERVAL(DATE/DATETIME EXPRESSION)FIRST TO LAST 返回表达式代表的时间间隔值 12)EXTEND(DATE/DATETIME EXPRESSION,[FIRST TO LAST])返回经过调整的日期或日期时间值 To_char函数将datetime和date值转化为字符值。 To_date函数将字符值转化为datetime类型的值。例to_date(“1978-10-07 10:00” ,”%Y-%m-%d %H:%M) 例子1、和UNITS合用,指定日期或时间单位 (year,month,day,hour,minute,seond,fraction): let tmp_date = today + 3 UNITS day 例子2、let tmp_date = MDY(10,30,2002) -- 2002-10-30 例子3、let tmp_date = today + interval(7) day to day --当前时间加上7天; 注:该功能与1相似;

SQL中经典函数使用实例大全

select substring(CODE_KIND,2,3)kind from code //2处为起始位置,3为获取字符的长度。 select left(CODE_KIND,3)from CODE //返回从左边数起的3个字符,right()函数反之。 select left('1233',2) select upper(code_name)name from code //将字符串中每个小写字母转换为大写字母,lower 转化成小写。 select REVERSE(code_name)code_name from code //将查询结果反过来。 SELECT row_number()over (order by code_kind)as kind,*from code

with codeT as ( SELECT row_number()over (order by code_kind desc)as kind,*from code ) select*from codeT where kind>1 and kind<4 SELECT RANK()over (order by code_kind)as kind,*from code //rand() 查询出来的数字不是连续的。DENSE_RANK()查出来的反之。 SELECT DENSE_RANK()over (order by code_kind)as kind,*from code

學習SQL中事務,以及upper(),right(),RANK(),DENSE_RANK(),replace()等等一些操作sql中字符串的常用函數。可快速查找出sql中符合自己所想要的數據。 可以。比如说现在有两个排序字段,如果根据第1个字段排不出来(有几条记录的这个字段相同)的话,再根据第2个字段排的。 请看下面的例子: 可多字段排序,当多字段排序时,首先排序第一个字段,当第一个字段值相同时,才按第二个字段排序,如果第二个字段值相同,才按第三个排序... 比如说现在有两个排序字段,如果根据第1个字段排不出来(有几条记录的这个字段相同)的话,再根据第2个字段排的 排序前(注意红色标记部分的数据): SQL语句: select * from Test where uname like 'Test%' order by score desc, uname asc; 排序后(注意红色标记部分的数据):

SQL语句学习3--函数语句

SQL 拥有很多可用于计数和计算的内建函数。
函数的语法
内建 SQL 函数的语法是:
SELECT function(列) FROM 表
函数的类型
在 SQL 中,基本的函数类型和种类有若干种。函数的基本类型是:
? Aggregate 函数 ? Scalar 函数
合计函数(Aggregate functions)
Aggregate 函数的操作面向一系列的值,并返回一个单一的值。 注释:如果在 SELECT 语句的项目列表中的众多其它表达式中使用 SELECT 语句,则这个 SELECT 必 须使用 GROUP BY 语句!
"Persons" table (在大部分的例子中使用过) Name Age
Adams, John
38
Bush, George
33
Carter, Thomas
28
MS Access 中的合计函数 函数 描述 返回某列的平均值 返回某列的行数(不包括 NULL 值) 返回被选行数 返回在指定的域中第一个记录的值
AVG(column)
COUNT(column)
COUNT(*)
FIRST(column)

LAST(column)
返回在指定的域中最后一个记录的值 返回某列的最高值 返回某列的最低值
MAX(column)
MIN(column)
STDEV(column)
STDEVP(column) 返回某列的总和
SUM(column)
VAR(column)
VARP(column)
在 SQL Server 中的合计函数 函数 描述 返回某列的行数
AVG(column)
BINARY_CHECKSUM
CHECKSUM
CHECKSUM_AGG 返回某列的行数(不包括 NULL 值) 返回被选行数 返回相异结果的数目 返回在指定的域中第一个记录的值(SQLServer2000 不支持) 返回在指定的域中最后一个记录的值(SQLServer2000 不支持) 返回某列的最高值 返回某列的最低值
COUNT(column)
COUNT(*)
COUNT(DISTINCT column)
FIRST(column)
LAST(column)
MAX(column)
MIN(column)
STDEV(column)

相关主题