SQL函数

1.函数的语法  

内建SQL函数的语法

SELECT function （列）FROM 表

函数的类型

在SQL中，基本的函数类型和种类有若干重，函数的基本类型有 Aggregate 函数 和Scalar函数

合计函数（Aggregate 函数）

aggregate函数的操作面向同一系列的值，并返回单一的值，

注释 如果在SELECT语句的项目列表中的众多其它表达式中使用SELECT语句，则这个SELECT语句必须使用 group by语句

以下复制于jb51.net

"Persons" table (在大部分的例子中使用过)

Name	Age
Adams, John	38
Bush, George	33
Carter, Thomas	28

MS Access 中的合计函数

函数	描述
AVG(column)	返回某列的平均值
COUNT(column)	返回某列的行数（不包括 NULL 值）
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
COUNT(column)	返回某列的行数（不包括NULL值）
COUNT(*)	返回被选行数
COUNT(DISTINCT column)	返回相异结果的数目
FIRST(column)	返回在指定的域中第一个记录的值（SQLServer2000 不支持）
LAST(column)	返回在指定的域中最后一个记录的值（SQLServer2000 不支持）
MAX(column)	返回某列的最高值
MIN(column)	返回某列的最低值
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)	用于执行日期计算

 SQL AVG函数

语法

SELECT  AVG（column_name）From  table_name

SQL AVG（）实例

下面这个Order表

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字段中得平均数

使用下面的语句

SELECT AVG（OrderPrice） As OrderAverage FROM Orders

结果集为

OrderAverage

950

例2

现在，我们希望找到OrderPrice值高于OrderPrice平均值的客户

我们 使用如下SQL语句

SELECT customer FROM Orders where OrderPrice >(SELECT FROM  AVG（OrderPrice）FROM Orders)

结果集为

Customer

Bush

Carter

Adams

3.SQL COUNT（）函数

语法

Count（column_name）函数返回指定列的值的数值（NULL值不计入）

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

注意 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的订单数

SELECT count（customer） As  customerNilsen FROM Orders  where  customer=‘Carter’

结果为2 因为 Carter工有两个订单

CustomerNilsen

2

SQL COUNT（*）实例

如果我们省略where子句

SELECT COUNT（*） As  NumberOfOrders  FROM  Orders

结果为

NumberOfOrders

6

这为表中的总行数

SQL COUNT（distinct column_name实例）

现在我们希望计算Orders表中不同客户的数目

SELECT COUNT（Distinct  customer） As  NumberOfCustomers FROM Orders

结果为NumberOfCustomers

           3

这是Orders表中不同客户的数目

4.SQL first（）函数

返回指定的字段中第一个记录的值

提示 可使用 Order by对记录进行排序

语法

SELECT first（column_name） FROM table_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

我们希望查找OrderPrice列的第一个值

SELECT first（OrderPrice） As FirstOrderPrice FROM  Orders

结果为

FirstOrderPrice

1000

5.SQL 

返回指定的字段中最后一个记录的值

提示 可使用 Order by对记录进行排序

语法

SELECT Last（column_name） FROM table_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

我们希望查找OrderPrice列的最后一个值

SELECT Last（OrderPrice） As LastOrderPrice FROM  Orders

结果为

FirstOrderPrice

100

6.SQL  MAX（）函数

返回一列中得最大的值，NULL值不包括在计算中

语法

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
6	2008/07/21	100	Carter

现在我们希望查找 OrderPrice的最大值

SELECT MAX （OrderPrice）As  LargestOrderPrice FROM Orders

结果类似为

LargestOrderPrice

2000

7.SQL  MIN（）函数

返回一列中得最小的值，NULL值不包括在计算中

语法

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

现在我们希望查找 OrderPrice的最大值

SELECT MIN （OrderPrice）As  smallestOrderPrice FROM Orders

结果类似为

smallestOrderPrice

300

8.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字段中得总数

SELECT  SUM（OrderPrice）As  OrderTotal FROM  Orders

结果为

OrderTotal

5700

9.SQL GROUP BY语句

用于结合合计函数，根据一个或者多个列对结果集进行分组

语法

SELECT column_name,aggreate_function（column_name） From table_name where column_name operator value ground  by 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

我们希望查找每个客户的总金额

则  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

上面的结果不是我们想要的

那么为什么不能使用上面这条 SELECT 语句呢？解释如下：上面的 SELECT 语句指定了两列（Customer 和 SUM(OrderPrice)）。"SUM(OrderPrice)" 返回一个单独的值（"OrderPrice" 列的总计），而 "Customer" 返回 6 个值（每个值对应 "Orders" 表中的每一行）。因此，我们得不到正确的结果。不过，您已经看到了，GROUP BY 语句解决了这个问题。

Group by一个以上实例

SELECT Customer，OrderDate，SUM（OrderPrice）From Orders group  by customer，OrderDate；

10.SQL HAVING 语句

在SQL中增加该语句的原因是，where关键字无法与合计函数一块使用

语法

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 

实例

我们拥有下面这个 "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

现在我们希望查找订单总金额少于2000的客户

则 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  

11. SQL UCASE（）函数

把字段的值转换为大写

语法

SELECT  UCASE（column_name）FROM table_name

实例

我们拥有下面这个 "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列转换为大写

SELECT UCASE（LastName） as LastName，firstName FROM Persons

结果为

LastName     FirstName

ADAMS         john

BUSH           George

CARTER      Thomas

12. SQL LCASE（）函数

把字段的值转换为大写

语法

SELECT  LCASE（column_name）FROM table_name

实例

我们拥有下面这个 "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列转换为大写

SELECT LCASE（LastName） as LastName，firstName FROM Persons

结果为

LastName     FirstName

adams          john

bush            George

carter           Thomas

13.SQL MID（）函数

用于从文本字段中提取字符

SELECT  MID（column_name,start[，length]）FROM table_name

参数                         描述

column_name       必须 要提取字符的字段

start                        必须 规定开始位置

length                    可选，要返回的字符数，如果省略，则MID（）函数返回剩余文本。

实例

我们拥有下面这个 "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个字符

SELECT  MID（City，1，3）as SmallCity  FROM Persons

结果集类似

SmallCity

Lon

New

Bei

14.Len()函数返回文本字段中值的长度。

语法

SELECT  Len(column_name)FROM table_name

实例

我们拥有下面这个 "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列中值的长度

SELECT LEN（City）as LengthOfCity FROM Persons

结果集

LengthOfCity

6

8

7

15.Round（）函数

用于把数值字段舍入为指定的小数位数

语法

SELECT  ROUND（column_name,decimals）FROM table_name

参数                         描述

column_name      必须，要舍入的字段

decimals              必须，规定要返回的小数位数

实例

我们拥有下面这个 "Products" 表：

Prod_Id	ProductName	Unit	UnitPrice
1	gold	1000 g	32.35
2	silver	1000 g	11.56
3	copper	1000 g	6.85

现在，我们希望把名称和价格舍入为最接近的整数。

SELECT  ProductName，Round（UnitPrice，0）as  UnitPrice  FROM Products

结果为

ProductName          UnitPrice

gold                           32

sliver                          12

copper                       7

16.SQL NOW()函数

返回当前的时间和日期

提示  如果使用SQL  Server 数据库，请使用gerdate()函数获得当前的日期和时间

语法

SELECT   NOW（）FROM table_name

实例

我们拥有下面这个 "Products" 表：

Prod_Id	ProductName	Unit	UnitPrice
1	gold	1000 g	32.35
2	silver	1000 g	11.56
3	copper	1000 g	6.85

现在我们希望显示当前的日期所对应的名称和价格

SELECT  ProductName，UnitPrice，Now（）as PerDate FROM Products

结果为

ProductName	UnitPrice	PerDate
gold	32.35	12/29/2008 11:36:05 AM
silver	11.56	12/29/2008 11:36:05 AM
copper	6.85	12/29/2008 11:36:05 AM

17.SQL  FORMAT（）函数

用于对字段的显示进行格式化。

语法

SELECT  FORMAT（column_name,format）FROM table_name

参数                             描述

column_name             必须，要格式化的字段

format                          必须，规定格式

实例

我们拥有下面这个 "Products" 表：

Prod_Id	ProductName	Unit	UnitPrice
1	gold	1000 g	32.35
2	silver	1000 g	11.56
3	copper	1000 g	6.85

现在 我们希望显示每天日期所对应的名称和价格（日期的显示格式为YYYY—MM-DD）

SELECT ProductName，UnitPrice，FORMAT（Now（），‘YYYY-MM-DD’）as PerDate FROM Products

ProductName	UnitPrice	PerDate
gold	32.35	12/29/2008
silver	11.56	12/29/2008
copper	6.85	12/29/2008

SQL快速参考

语句	语法
AND / OR	SELECT column_name(s) FROM table_name WHERE condition AND|OR condition
ALTER TABLE (add column)	ALTER TABLE table_name  ADD column_name datatype
ALTER TABLE (drop column)	ALTER TABLE table_name  DROP COLUMN column_name
AS (alias for column)	SELECT column_name AS column_alias FROM table_name
AS (alias for table)	SELECT column_name FROM table_name  AS table_alias
BETWEEN	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
CREATE DATABASE	CREATE DATABASE database_name
CREATE INDEX	CREATE INDEX index_name ON table_name (column_name)
CREATE TABLE	CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ....... )
CREATE UNIQUE INDEX	CREATE UNIQUE INDEX index_name ON table_name (column_name)
CREATE VIEW	CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
DELETE FROM	DELETE FROM table_name  (Note: Deletes the entire table!!) or DELETE FROM table_name WHERE condition
DROP DATABASE	DROP DATABASE database_name
DROP INDEX	DROP INDEX table_name.index_name
DROP TABLE	DROP TABLE table_name
GROUP BY	SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1
HAVING	SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 HAVING SUM(column_name2) condition value
IN	SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..)
INSERT INTO	INSERT INTO table_name VALUES (value1, value2,....) or INSERT INTO table_name (column_name1, column_name2,...) VALUES (value1, value2,....)
LIKE	SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
ORDER BY	SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC]
SELECT	SELECT column_name(s) FROM table_name
SELECT *	SELECT * FROM table_name
SELECT DISTINCT	SELECT DISTINCT column_name(s) FROM table_name
SELECT INTO (used to create backup copies of tables)	SELECT * INTO new_table_name FROM original_table_name or SELECT column_name(s) INTO new_table_name FROM original_table_name
TRUNCATE TABLE (deletes only the data inside the table)	TRUNCATE TABLE table_name
UPDATE	UPDATE table_name SET column_name=new_value [, column_name=new_value] WHERE column_name=some_value
WHERE	SELECT column_name(s) FROM table_name WHERE condition