当前位置:文档之家› SQL函数

SQL函数

SQL 函数
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(*)
1

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)
2

STDEV(column)
STDEVP(column) 返回某列的总和
SUM(column)
VAR(column)
VARP(column)
Scalar 函数
Scalar 函数的操作面向某个单一的值,并返回基于输入值的一个单一的值。
MS Access 中的 Scalar 函数 函数 描述 将某个域转换为大写 将某个域转换为小写 从某个文本域提取字符 返回某个文本域的长度 返回在某个文本域中指定字符的数值位置 返回某个被请求的文本域的左侧部分 返回某个被请求的文本域的右侧部分 对某个数值域进行指定小数位数的四舍五入 返回除法操作的余数 返回当前的系统日期 改变某个域的显示方式 用于执行日期计算
UCASE(c)
LCASE(c)
MID(c,start[,end])
LEN(c)
INSTR(c,char)
LEFT(c,number_of_char)
RIGHT(c,number_of_char)
ROUND(c,decimals)
MOD(x,y)
NOW()
FORMAT(c,format)
DATEDIFF(d,date1,date2)
3

SQL AVG 函数
定义和用法
AVG 函数返回数值列的平均值。NULL 值不包括在计算中。
SQL AVG() 语法 SELECT AVG(column_name) FROM table_name
SQL AVG() 实例
我们拥有下面这个 "Orders" 表:
O_Id
OrderDate
OrderPrice
Customer
1
2008/12/29
1000
Bush
2
2008/11/23
1600
Carter
3
2008/10/05
700
Bush
4
2008/09/28
300
Bush
5
2008/08/06
2000
Adams
6
2008/07/21
100
Carter
例子 1 现在,我们希望计算 "OrderPrice" 字段的平均值。 我们使用如下 SQL 语句:
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
结果集类似这样:
OrderAverage
950
例子 2
4

现在,我们希望找到 OrderPrice 值高于 OrderPrice 平均值的客户。 我们使用如下 SQL 语句:
SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
结果集类似这样:
Customer
Bush
Carter
Adams
SQL COUNT() 函数
COUNT() 函数返回匹配指定条件的行数。
SQL COUNT() 语法
SQL COUNT(column_name) 语法 COUNT(column_name) 函数返回指定列的值的数目(NULL 不计入):
SELECT COUNT(column_name) FROM table_name
SQL COUNT(*) 语法 COUNT(*) 函数返回表中的记录数:
SELECT COUNT(*) FROM table_name
SQL COUNT(DISTINCT column_name) 语法 COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目:
SELECT COUNT(DISTINCT column_name) FROM table_name
5

注释:COUNT(DISTINCT) 适用于 ORACLE 和 Microsoft SQL Server,但是无法用于 Microsoft Access。
SQL COUNT(column_name) 实例
我们拥有下列 "Orders" 表:
O_Id
OrderDate
OrderPrice
Customer
1
2008/12/29
1000
Bush
2
2008/11/23
1600
Carter
3
2008/10/05
700
Bush
4
2008/09/28
300
Bush
5
2008/08/06
2000
Adams
6
2008/07/21
100
Carter
现在,我们希望计算客户 "Carter" 的订单数。 我们使用如下 SQL 语句:
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer='Carter'
以上 SQL 语句的结果是 2,因为客户 Carter 共有 2 个订单:
CustomerNilsen
2 SQL COUNT(*) 实例 如果我们省略 WHERE 子句,比如这样:
SELECT COUNT(*) AS NumberOfOrders FROM Orders
结果集类似这样:
NumberOfOrders
6

6
这是表中的总行数。
SQL COUNT(DISTINCT column_name) 实例
现在,我们希望计算 "Orders" 表中不同客户的数目。 我们使用如下 SQL 语句:
SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders
结果集类似这样:
NumberOfCustomers
3 这是 "Orders" 表中不同客户(Bush, Carter 和 Adams)的数目。
SQL FIRST() 函数
FIRST() 函数
FIRST() 函数返回指定的字段中第一个记录的值。 提示:可使用 ORDER BY 语句对记录进行排序。
SQL FIRST() 语法 SELECT FIRST(column_name) FROM table_name
SQL FIRST() 实例
我们拥有下面这个 "Orders" 表:
O_Id
OrderDate
OrderPrice
Customer
1
2008/12/29
1000
Bush
2
2008/11/23
1600
Carter
3
2008/10/05
700
Bush
7

4
2008/09/28
300
Bush
5
2008/08/06
2000
Adams
6
2008/07/21
100
Carter
现在,我们希望查找 "OrderPrice" 列的第一个值。 我们使用如下 SQL 语句:
SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders
结果集类似这样:
FirstOrderPrice
1000
SQL LAST() 函数
LAST() 函数
LAST() 函数返回指定的字段中最后一个记录的值。 提示:可使用 ORDER BY 语句对记录进行排序。
SQL LAST() 语法 SELECT LAST(column_name) FROM table_name
SQL LAST() 实例
我们拥有下面这个 "Orders" 表:
O_Id
OrderDate
OrderPrice
Customer
1
2008/12/29
1000
Bush
2
2008/11/23
1600
Carter
3
2008/10/05
700
Bush
4
2008/09/28
300
Bush
8

5
2008/08/06
2000
Adams
6
2008/07/21
100
Carter
现在,我们希望查找 "OrderPrice" 列的最后一个值。 我们使用如下 SQL 语句:
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders
结果集类似这样:
LastOrderPrice
100
SQL MAX() 函数
MAX() 函数
MAX 函数返回一列中的最大值。NULL 值不包括在计算中。
SQL MAX() 语法 SELECT MAX(column_name) FROM table_name
注释:MIN 和 MAX 也可用于文本列,以获得按字母顺序排列的最高或最低值。
SQL MAX() 实例
我们拥有下面这个 "Orders" 表:
O_Id
OrderDate
OrderPrice
Customer
1
2008/12/29
1000
Bush
2
2008/11/23
1600
Carter
3
2008/10/05
700
Bush
4
2008/09/28
300
Bush
5
2008/08/06
2000
Adams
9

6
2008/07/21
100
Carter
现在,我们希望查找 "OrderPrice" 列的最大值。 我们使用如下 SQL 语句:
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders
结果集类似这样:
LargestOrderPrice
2000
SQL MIN() 函数
MIN() 函数
MIN 函数返回一列中的最小值。NULL 值不包括在计算中。
SQL MIN() 语法 SELECT MIN(column_name) FROM table_name
注释:MIN 和 MAX 也可用于文本列,以获得按字母顺序排列的最高或最低值。
SQL MIN() 实例
我们拥有下面这个 "Orders" 表:
O_Id
OrderDate
OrderPrice
Customer
1
2008/12/29
1000
Bush
2
2008/11/23
1600
Carter
3
2008/10/05
700
Bush
4
2008/09/28
300
Bush
5
2008/08/06
2000
Adams
6
2008/07/21
100
Carter
10

现在,我们希望查找 "OrderPrice" 列的最小值。 我们使用如下 SQL 语句:
SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders
结果集类似这样:
SmallestOrderPrice
100
SQL SUM() 函数
SUM() 函数
SUM 函数返回数值列的总数(总额)。
SQL SUM() 语法 SELECT SUM(column_name) FROM table_name
SQL SUM() 实例
我们拥有下面这个 "Orders" 表:
O_Id
OrderDate
OrderPrice
Customer
1
2008/12/29
1000
Bush
2
2008/11/23
1600
Carter
3
2008/10/05
700
Bush
4
2008/09/28
300
Bush
5
2008/08/06
2000
Adams
6
2008/07/21
100
Carter
现在,我们希望查找 "OrderPrice" 字段的总数。 我们使用如下 SQL 语句:
11

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders
结果集类似这样:
OrderTotal
5700
SQL GROUP BY 语句
合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句。
GROUP BY 语句
GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。
SQL GROUP BY 语法 SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
SQL GROUP BY 实例
我们拥有下面这个 "Orders" 表:
O_Id
OrderDate
OrderPrice
Customer
1
2008/12/29
1000
Bush
2
2008/11/23
1600
Carter
3
2008/10/05
700
Bush
4
2008/09/28
300
Bush
5
2008/08/06
2000
Adams
6
2008/07/21
100
Carter
现在,我们希望查找每个客户的总金额(总订单)。
12

我们想要使用 GROUP BY 语句对客户进行组合。 我们使用下列 SQL 语句:
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer
结果集类似这样:
Customer
SUM(OrderPrice)
Bush
2000
Carter
1700
Adams
2000
很棒吧,对不对? 让我们看一下如果省略 GROUP BY 会出现什么情况:
SELECT Customer,SUM(OrderPrice) FROM Orders
结果集类似这样:
Customer
SUM(OrderPrice)
Bush
5700
Carter
5700
Bush
5700
Bush
5700
Adams
5700
Carter
5700
上面的结果集不是我们需要的。
13

那么为什么不能使用上面这条 SELECT 语句呢?解释如下:上面的 SELECT 语句指定了两列 (Customer 和 SUM(OrderPrice))。"SUM(OrderPrice)" 返回一个单独的值("OrderPrice" 列的 总计),而 "Customer" 返回 6 个值(每个值对应 "Orders" 表中的每一行)。因此,我们得不到正 确的结果。不过,您已经看到了,GROUP BY 语句解决了这个问题。
GROUP BY 一个以上的列
我们也可以对一个以上的列应用 GROUP BY 语句,就像这样:
SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders GROUP BY Customer,OrderDate
SQL HAVING 子句
HAVING 子句
在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用。
SQL HAVING 语法 SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value
SQL HAVING 实例
我们拥有下面这个 "Orders" 表:
O_Id
OrderDate
OrderPrice
Customer
1
2008/12/29
1000
Bush
2
2008/11/23
1600
Carter
3
2008/10/05
700
Bush
4
2008/09/28
300
Bush
5
2008/08/06
2000
Adams
14

6
2008/07/21
100
Carter
现在,我们希望查找订单总金额少于 2000 的客户。 我们使用如下 SQL 语句:
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000
结果集类似:
Customer
SUM(OrderPrice)
Carter
1700
现在我们希望查找客户 "Bush" 或 "Adams" 拥有超过 1500 的订单总金额。 我们在 SQL 语句中增加了一个普通的 WHERE 子句:
SELECT Customer,SUM(OrderPrice) FROM Orders WHERE Customer='Bush' OR Customer='Adams' GROUP BY Customer HAVING SUM(OrderPrice)>1500
结果集:
Customer
SUM(OrderPrice)
Bush
2000
Adams
2000
SQL UCASE() 函数
UCASE() 函数
UCASE 函数把字段的值转换为大写。
SQL UCASE() 语法 15

SELECT UCASE(column_name) FROM table_name
SQL UCASE() 实例
我们拥有下面这个 "Persons" 表:
Id
LastName
FirstName
Address
City
1
Adams
John
Oxford Street
London
2
Bush
George
Fifth Avenue
New York
3
Carter
Thomas
Changan Street
Beijing
现在,我们希望选取 "LastName" 和 "FirstName" 列的内容,然后把 "LastName" 列转换为大写。 我们使用如下 SQL 语句:
SELECT UCASE(LastName) as LastName,FirstName FROM Persons
结果集类似这样:
LastName
FirstName
ADAMS
John
BUSH
George
CARTER
Thomas
SQL LCASE() 函数
LCASE() 函数
LCASE 函数把字段的值转换为小写。
SQL LCASE() 语法 SELECT LCASE(column_name) FROM table_name
SQL LCASE() 实例
我们拥有下面这个 "Persons" 表:
16

Id
LastName
FirstName
Address
City
1
Adams
John
Oxford Street
London
2
Bush
George
Fifth Avenue
New York
3
Carter
Thomas
Changan Street
Beijing
现在,我们希望选取 "LastName" 和 "FirstName" 列的内容,然后把 "LastName" 列转换为小写。 我们使用如下 SQL 语句:
SELECT LCASE(LastName) as LastName,FirstName FROM Persons
结果集类似这样:
LastName
FirstName
adams
John
bush
George
carter
Thomas
SQL MID() 函数
MID() 函数
MID 函数用于从文本字段中提取字符。
SQL MID() 语法 SELECT MID(column_name,start[,length]) FROM table_name
参数
描述 必需。要提取字符的字段。 必需。规定开始位置(起始值是 1)。 可选。要返回的字符数。如果省略,则 MID() 函数返回剩余文本。
column_name
start
length
SQL MID() 实例
17

我们拥有下面这个 "Persons" 表:
Id
LastName
FirstName
Address
City
1
Adams
John
Oxford Street
London
2
Bush
George
Fifth Avenue
New York
3
Carter
Thomas
Changan Street
Beijing
现在,我们希望从 "City" 列中提取前 3 个字符。 我们使用如下 SQL 语句:
SELECT MID(City,1,3) as SmallCity FROM Persons
结果集类似这样:
SmallCity
Lon
New
Bei
SQL LEN() 函数
LEN() 函数
LEN 函数返回文本字段中值的长度。
SQL LEN() 语法 SELECT LEN(column_name) FROM table_name
SQL LEN() 实例
我们拥有下面这个 "Persons" 表:
Id
LastName
FirstName
Address
City
1
Adams
John
Oxford Street
London
18

2
Bush
George
Fifth Avenue
New York
3
Carter
Thomas
Changan Street
Beijing
现在,我们希望取得 "City" 列中值的长度。 我们使用如下 SQL 语句:
SELECT LEN(City) as LengthOfAddress FROM Persons
结果集类似这样:
LengthOfCity
6
8
7
SQL ROUND() 函数
ROUND() 函数
ROUND 函数用于把数值字段舍入为指定的小数位数。
SQL ROUND() 语法 SELECT ROUND(column_name,decimals) FROM table_name
参数
描述 必需。要舍入的字段。 必需。规定要返回的小数位数。
column_name
decimals
SQL ROUND() 实例
我们拥有下面这个 "Products" 表:
Prod_Id
ProductName
Unit
UnitPrice
1
gold
1000 g
32.35
19

2
silver
1000 g
11.56
3
copper
1000 g
6.85
现在,我们希望把名称和价格舍入为最接近的整数。 我们使用如下 SQL 语句:
SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products
结果集类似这样:
ProductName
UnitPrice
gold
32
silver
12
copper
7
SQL NOW() 函数
NOW() 函数
NOW 函数返回当前的日期和时间。
SQL NOW() 语法 SELECT NOW() FROM table_name
SQL NOW() 实例
我们拥有下面这个 "Products" 表:
Prod_Id
ProductName
Unit
UnitPrice
1
gold
1000 g
32.35
2
silver
1000 g
11.56
3
copper
1000 g
6.85
现在,我们希望显示当天的日期所对应的名称和价格。
20

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个字符。结果

DB2_SQL常用函数

DB2函数大全 函数名函数解释函数举例 AVG() 返回一组数值的平均值. SELECTAVG(SALARY)FROMBSEMPMS; CORR(),CORRELATION() 返回一对数值的关系系数. SELECT CORRELATION(SALARY,BONUS)FROM BSEMPMS; COUNT() 返回一组行或值的个 数.SELECTCOUNT(*)FROMBSEMPMS; COVAR(),COVARIANCE() 返回一对数值的协方差. SELECTCOVAR(SALARY,BONUS)FROMBSEMPMS; MAX() 返回一组数值中的最大值. SELECTMAX(SALARY)FROMBSEMPMS; MIN() 返回一组数值中的最小值. SELECTMIN(SALARY)FROMBSEMPMS; STDDEV() 返回一组数值的标准偏差. SELECTSTDDEV(SALARY)FROMBSEMPMS; SUM() 返回一组数据的和. SELECTSUM(SALARY)FROMBSEMPMS; VAR(),VARIANCE() 返回一组数值的方差. SELECTVARIANCE(SALARY)FROMBSEMPMS; ABS(),ABSVAL() 返回参数的绝对值. SELECTABS(-3.4)FROMBSEMPMS; ACOS() 返回参数的反余弦值. SELECTACOS(0.9)FROMBSEMPMS; ASCII() 返回整数参数最左边的字符的ASCII码. SELECTASCII('R')FROMBSEMPMS; ASIN() 返回用弧度表示的角度的参数的反正弦函数. SELECTASIN(0.9)FROMBSEMPMS; ATAN() 返回参数的反正切值,该参数用弧度表示的角度的参数. SELECTATAN(0.9)FROMBSEMPMS; ATAN2() 返回用弧度表示的角度的X和Y坐标的反正切值. SELECTATAN2(0.5,0.9)FROMBSEMPMS;

SQL函数3种排名方法报告

排名函数是SQL Server2005新加的功能。在SQL Server2005中有如下四个排名函数: 1.row_number 2.rank 3.dense_rank 4.ntile 下面分别介绍一下这四个排名函数的功能及用法。在介绍之前假设有一个t_table表,表结构与表中的数据如图1所示: 图1 其中field1字段的类型是int,field2字段的类型是varchar 一、row_number row_number函数的用途是非常广泛,这个函数的功能是为查询出来的每一行记录生成一个序号。row_number函数的用法如下面的SQL语句所示: select row_number() over(order by field1) as row_number,* fromt_table 上面的SQL语句的查询结果如图2所示。 图2 其中row_number列是由row_number函数生成的序号列。在使用row_number 函数是要使用over子句选择对某一列进行排序,然后才能生成序号。

实际上,row_number函数生成序号的基本原理是先使用over子句中的排序语句对记录进行排序,然后按着这个顺序生成序号。over子句中的order by子句与SQL语句中的order by子句没有任何关系,这两处的order by 可以完全不同,如下面的SQL语句所示 select row_number() over(order by field2 desc) as row_number,*from t_table order by field1 desc 上面的SQL语句的查询结果如图3所示。 图3 我们可以使用row_number函数来实现查询表中指定范围的记录,一般将其应用到Web应用程序的分页功能上。下面的SQL语句可以查询t_table表中第2条和第3条记录: with t_rowtable as ( select row_number() over(order by field1) as row_number,*from t_table ) select * from t_rowtable where row_number>1 and row_number<4 order by field1 上面的SQL语句的查询结果如图4所示。

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 。

sql常用函数instr()和substr()

在Oracle中 可以使用instr函数对某个字符串进行判断,判断其是否含有指定的字符。 其语法为: instr(sourceString,destString,start,appearPosition). instr('源字符串' , '目标字符串' ,'开始位置','第几次出现') 其中sourceString代表源字符串; destString代表想聪源字符串中查找的子串; start代表查找的开始位置,该参数可选的,默认为1; appearPosition代表想从源字符中查找出第几次出现的destString,该参数也是可选的,默认为1; 如果start的值为负数,那么代表从右往左进行查找,但是位置数据仍然从左向右计算。 返回值为:查找到的字符串的位置。 对于instr函数,我们经常这样使用:从一个字符串中查找指定子串的位置。例如:SQL> select instr('yuechaotianyuechao','ao') position from dual; POSITION ---------- 6 从第7个字符开始搜索 SQL> select instr('yuechaotianyuechao','ao', 7) position from dual; POSITION ---------- 17 从第1个字符开始,搜索第2次出现子串的位置 SQL> select instr('yuechaotianyuechao','ao', 1, 2) position from dual; POSITION ---------- 17 注意:1。若‘起始位置’=0 时返回结果为0, 2。这里只有三个参数,意思是查找第一个要查找字符的位置(因为‘第几次出现’默认为1), 当‘起始位置’不大于要查找的第一个字符的位置时,返回的值都将是第一个字符的位置,如果‘起始位置’大于要查找的第一个字符的位置时,返回的值都将是第2个字符的位置,依此类推……(但是也是以第一个字符开始计数)

SQL函数

/*****************字符串函数***************/ --把传递给它的字符串的长度 select len('aaaaaaaaaaaaa')13位 --结果:13 --把传递给它的字符串转换成大写 select upper ('aaaaaaaaaaaaaaaaaa') --结果:AAAAAAAAAAAAAAAAAA --用来寻找一个指定的字符串在另一个字符串的启示位置 select charIndex ('Accp' , 'kjakjf Accp laaaaaaaaaaaa',1) --结果:7 --清除字符串左边的的空格 select ltrim(' aa') --结果:aa --清除字符串右边的的空格 select rtrim ('af ') --结果:af --替换一个字符串中的字符 select replace ('ABCdEFG.Aa' ,'a','A') --结果:ABCdEFG.AA select stuff ('AaaaAAAA',2,1,'我的障碍') --结果:A我的障碍aaAAAA 2代表位置,1代表替换的字符串 /*******日期函数******/ (----mm月-----dd天----- hh小时----ss秒-----ms毫秒)select getdate( )--取得当前时间饿系统日期 --结果:2008-12-22 10:30:22.140 select dateAdd (mm , 2 , '2010-1-10' ) --结果:返回2010-3-10 00:00:00.000 --将指定的数值添加到指定的日期的部分的日期 select datepart (day ,'01/15/2000') 返回15天 select datediff(ss , time1 , time2)—------获取时间差 如time1=1999-12-01 12:18:12.000 Time2=1999-12-01 12:19:45.000 返回93秒

plsql常用函数

PLSQL常用函数 1)处理字符的函数 || CONCAT ---并置运算符。 格式∶CONCAT(STRING1, STRING2) 例:’ABC’|| ’DE’=’ABCDE’ CONCAT(‘ABC’,’DE’) =’ABCDE’ INSTR---搜索子串位置 格式∶INSTR(STRING , SET[ , 开始位置[ , 出现次数]]) 例∶ INSTR (‘this is a test’ , ‘i’ , 1,2)=6 LENGTH----计算串长 格式∶ LENGTH(string) LTRIM,RTRIM,TRIM -----左截断,右截断,左右截断。默认为删除空格。 格式∶ LTRIM(STRING[,‘SET’]) 例∶ LTRIM(‘***tes*t***’,’*’)=’tes*t***’ LOWER----将字符串转换为小写 格式∶LOWER(string) UPPER---将字符串转换为大写 格式∶UPPER(string) SUBSTR----提取子串。START为正数时从左开始、为负数时从右开始 格式∶ SUBSTR(STRING , START [ , COUNT]) 例∶ SUBSTR(‘WORDSTAR’ , 2 , 3)=’ ORD’ REPLACE---搜索指定字符串并替换 格式∶REPLACE(string , substring , replace_string) 例∶ REPLACE(‘this is a test’ , ‘this’ , ‘that an’)=’that an is a test’

2)处理数字的函数 ROUND---返回固定小数位数。 格式∶ROUND (value)value为数字 例∶ROUND (66.123,2)= 66.12 CELL---返回大于等于特定值的最小整数 格式∶CELL(value) 例∶ CELL(-10,9)= -10 3)处理日期 SYSDATE---系统时间。精确至秒 例:TO_CHAR(SYSDATE,'YYYY-MM-DD') 2011-05-11(返回日期) TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') 2011-05-11 11:05:15(返回日期+时间) 常用日期数据格式 Y或YY或YYY 年的最后一位,两位或三位Select to_char(sysdate,’YYY’) from dual; SYEAR或YEAR SYEAR使公元前的年份前加一负号Select to_char(sysdate,’SYEAR’) from dua l;-1112表示公元前111 2年 Q 季度,1~3月为第一季度Select to_char(sysdate,’Q’) from dual;2表示第二季度① MM 月份数Select to_char(sysdate,’MM’) from dual;12表示12月 RM 月份的罗马表示Select to_char(sysdate,’RM’) from dual;IV表示4月 Month 用9个字符长度表示的月份名Select to_char(sysdate,’Month’) from dual;May后跟6个空格表示5月 WW 当年第几周Select to_char(sysdate,’WW’) from dual;24表示2002年6月13日为第24周W 本月第几周Select to_char(sysdate,’W’) from dual;2002年10月1日为第1周 DDD 当年第几, 1月1日为001,2月1日为032 Select to_char(sysdate,’DDD’) from dual;36 3 2002年1 2月2 9日为第363天 DD 当月第几天Select to_char(sysdate,’DD’) from dual;04 10月4日为第4天 D 周内第几天Select to_char(sysdate,’D’) from dual;5 2002年3月14日为星期一 DY 周内第几天缩写Select to_char(sysdate,’DY’) from dual;SUN 2002年3月24日为星期天

SQL教程(函数编)

SQL 教程(函数篇)
课程表
SQL 基础
? ? ? ? ? ? ? ? ? ? ?
SQL 首页 SQL 简介 SQL 语法 SQL select SQL distinct SQL where SQL AND & OR SQL Order By SQL insert SQL update SQL delete SQL 高级
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
SQL Top SQL Like SQL 通配符 SQL In SQL Between SQL Aliases SQL Join SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Union SQL Select Into SQL Create DB SQL Create Table SQL Constraints SQL Not Null SQL Unique SQL Primary Key SQL Foreign Key SQL Check SQL Default SQL Create Index SQL Drop

? ? ? ? ? ? ? ?
SQL Alter SQL Increment SQL View SQL Date SQL Nulls SQL isnull() SQL 数据类型 SQL 服务器 SQL 函数
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
SQL functions SQL avg() SQL count() SQL first() SQL last() SQL max() SQL min() SQL sum() SQL Group By SQL Having SQL ucase() SQL lcase() SQL mid() SQL len() SQL round() SQL now() SQL format()
SQL 总结
? ?
SQL 快速索引 SQL 总结 实例/测验 实例 测验
?
SQL 测验 建站手册
? ? ? ? ? ? ?
网站构建 万维网联盟 (W3C) 浏览器信息 网站品质 语义网 职业规划 网站主机

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)

数据库常用函数

数据库常用函数

一、基础 1、说明:创建数据库 CREATE DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份和还原 备份:exp dsscount/sa@dsscount owner=dsscount file=C:\dsscount_data_backup\dsscount.dmp log=C:\dsscount_data_backup\outputa.log 还原:imp dsscount/sa@dsscount file=C:\dsscount_data_backup\dsscount.dmp full=y ignore=y log=C:\dsscount_data_backup\dsscount.log statistics=none 4、说明:创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) CREATE TABLE ceshi(id INT not null identity(1,1) PRIMARY KEY,NAME VARCHAR(50),age INT) id为主键,不为空,自增长 根据已有的表创建新表: 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 范围

sql自定义函数

SQL Server 2005 自定义函数语汇小结 由于工作的需要,了解下SQL Server 2005 函数的写法,现在总结一下: 对于SQL Server 2005 数据库而言,函数与存储过程在语法方面是有很大的相同点, 最大的不同就是函数有返回值,直接使用returns ,而存储过程则使用output来声明输出变量 一、下面先说明下,如何创建函数 1、创建没有返回值与没有参数的函数 CREATE FUNCTION my_function() AS BEGIN DECLARE @variable varchar(255) --声明字符型变量 DECLARE @variable int --声明整形型变量 ...(do something) SET @variable = '12345' --对变量variable赋值 END 2、创建没有返回值有参数的函数 CREATE FUNCTION my_function(@user_Name varchar(128),@password int(6)) AS BEGIN DECLARE @variable_1 varchar(255) --声明字符型变量 ...(do something) SET @variable_1 = @user_Name + convert(varchar(255),@password) --将变量@user_Name与@password连接赋给@variable_1,其中convert()函数是将int型转为varchar型 END 3、创建有返回值与有参数的函数 CREATE FUNCTION my_function(@user_Name varchar(128),@password int(6)) returns varchar(255)--设置返回值,记住是returns 而不是return AS BEGIN DECLARE @result varchar(5) DECLARE @fagle varchar(5) SET @result = select https://www.doczj.com/doc/5310552683.html,er_Name from USERS as users where https://www.doczj.com/doc/5310552683.html,er_Name = @user_Name and users.password = @password IF @result = '' BEGIN SET @fagle = 'NO' END ELSE BEGIN SET @falge = 'YES' END return @result --返回结果 END 二、删除一个函数语法

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 返回小写字符数据转换为大写的字符表达式。

SQL常用语句及函数方法

1、通常用到的字符串转日期格式 Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06 Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16 Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06 Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06 Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06 Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06 Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06 Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46 Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06 Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16 Select CONVERT(varchar(100), GETDATE(), 12): 060516 Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937 Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967 Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47 Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157 Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16 Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47

mssql常用函数

MS SQL 常用函数 一、字符转换函数 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 是所要查找的字符表达式,expression 可为字符串也可为列名表达式。如果没有发现子串,则返回0 值。 此函数不能用于TEXT 和IMAGE 数据类型。 2、PATINDEX() 返回字符串中某个指定的子串出现的开始位置。 PATINDEX ()其中子串表达式前后必须有百分号“%”否则返回值为0。 与CHARINDEX 函数不同的是,PATINDEX函数的子串中可以使用通配符,且此函数可用于 CHAR、VARCHAR 和TEXT 数据类型。 五、字符串操作函数 1、QUOTENAME() 返回被特定字符括起来的字符串。 QUOTENAME ([,quote_ character]) 其中quote_ character 标明括字符串所用的字符,缺省值为“[]”。 2、REPLICATE()

相关主题
相关文档 最新文档