SQL世界之函数+语句（九，十）

函数	描述
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)

2.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()	返回当前的系统日期

二、SQL AVG 函数

1.AVG 函数

AVG 函数返回数值列的平均值。NULL 值不包括在计算中。

2.SQL AVG() 语法

SELECT AVG(column_name) FROM table_name

3.SQL AVG() 实例

"Orders" 表：

O_Id	OrderDate	OrderPrice	Customer
001	2018/2/29	1000	Buh
002	2018/11/3	1600	Car
003	2018/10/5	700	Buh
004	2018/09/8	300	Buh
005	2018/09/06	2000	Aam
006	2018/07/1	100	Car

计算 "OrderPrice" 字段的平均值，使用如下 SQL 语句：

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

结果集：

OrderAverage

950

找到 OrderPrice 值高于 OrderPrice 平均值的客户，使用如下 SQL 语句：

SELECT Customer FROM Orders

WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

结果集：

Customer

Buh

Car

Aam

三、SQL COUNT() 函数

1.COUNT() 函数

COUNT() 函数返回匹配指定条件的行数。

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

注：COUNT(DISTINCT) 适用于 ORACLE 和 Microsoft SQL Server，但是无法用于 Microsoft Access。

3.SQL COUNT(column_name) 实例

"Orders" 表：

O_Id	OrderDate	OrderPrice	Customer
001	2018/2/29	1000	Buh
002	2018/11/3	1600	Car
003	2018/10/5	700	Buh
004	2018/09/8	300	Buh
005	2018/09/06	2000	Aam
006	2018/07/1	100	Car

计算客户 "Car" 的订单数，使用如下 SQL 语句：

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders

WHERE Customer='Cart'

以上 SQL 语句的结果是 2，因为客户 Car 共有 2 个订单：

CustomerNilsen

4.SQL COUNT(*) 实例

省略 WHERE 子句：

SELECT COUNT(*) AS NumberOfOrders FROM Orders

结果集：

NumberOfOrders

这是表中的总行数。

5.SQL COUNT(DISTINCT column_name) 实例

计算 "Orders" 表中不同客户的数目，使用如下 SQL 语句：

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

结果集：

NumberOfCustomers

这是 "Orders" 表中不同客户（Bush, Carter 和 Adams）的数目。

四、SQL FIRST() 函数

1.FIRST() 函数

FIRST() 函数返回指定的字段中第一个记录的值。

注：可使用 ORDER BY 语句对记录进行排序。

2.SQL FIRST() 语法

SELECT FIRST(column_name) FROM table_name

3.SQL FIRST() 实例

"Orders" 表：

O_Id	OrderDate	OrderPrice	Customer
001	2018/2/29	1000	Buh
002	2018/11/3	1600	Car
003	2018/10/5	700	Buh
004	2018/09/8	300	Buh
005	2018/09/06	2000	Aam
006	2018/07/1	100	Car

查找 "OrderPrice" 列的第一个值，使用如下 SQL 语句：

SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders

结果集：

FirstOrderPrice

1000

五、SQL LAST() 函数

1.LAST() 函数

LAST() 函数返回指定的字段中最后一个记录的值。

注：可使用 ORDER BY 语句对记录进行排序。

2.SQL LAST() 语法

SELECT LAST(column_name) FROM table_name

3.SQL LAST() 实例

"Orders" 表：

O_Id	OrderDate	OrderPrice	Customer
001	2018/2/29	1000	Buh
002	2018/11/3	1600	Car
003	2018/10/5	700	Buh
004	2018/09/8	300	Buh
005	2018/09/06	2000	Aam
006	2018/07/1	100	Car

查找 "OrderPrice" 列的最后一个值，使用如下 SQL 语句：

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

结果集：

LastOrderPrice

100

六、SQL MAX() 函数

1.MAX() 函数

MAX 函数返回一列中的最大值。NULL 值不包括在计算中。

2.SQL MAX() 语法

SELECT MAX(column_name) FROM table_name

注：MIN 和 MAX 也可用于文本列，以获得按字母顺序排列的最高或最低值。

3.SQL MAX() 实例

"Orders" 表：

O_Id	OrderDate	OrderPrice	Customer
001	2018/2/29	1000	Buh
002	2018/11/3	1600	Car
003	2018/10/5	700	Buh
004	2018/09/8	300	Buh
005	2018/09/06	2000	Aam
006	2018/07/1	100	Car

查找 "OrderPrice" 列的最大值，使用如下 SQL 语句：

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

结果集：

LargestOrderPrice

2000

七、SQL MIN() 函数

1.MIN() 函数

MIN 函数返回一列中的最小值。NULL 值不包括在计算中。

2.SQL MIN() 语法

SELECT MIN(column_name) FROM table_name

3.SQL MIN() 实例

"Orders" 表：

O_Id	OrderDate	OrderPrice	Customer
001	2018/2/29	1000	Buh
002	2018/11/3	1600	Car
003	2018/10/5	700	Buh
004	2018/09/8	300	Buh
005	2018/09/06	2000	Aam
006	2018/07/1	100	Car

查找 "OrderPrice" 列的最小值，使用如下 SQL 语句：

SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders

结果集：

SmallestOrderPrice

100

八、SQL SUM() 函数

1.SUM() 函数

SUM 函数返回数值列的总数（总额）。

2.SQL SUM() 语法

SELECT SUM(column_name) FROM table_name

3.SQL SUM() 实例

"Orders" 表：

O_Id	OrderDate	OrderPrice	Customer
001	2018/2/29	1000	Buh
002	2018/11/3	1600	Car
003	2018/10/5	700	Buh
004	2018/09/8	300	Buh
005	2018/09/06	2000	Aam
006	2018/07/1	100	Car

查找 "OrderPrice" 字段的总数，使用如下 SQL 语句：

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

结果集：

OrderTotal

5700

九、SQL GROUP BY 语句

1.GROUP BY 语句

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

2.SQL GROUP BY 语法

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

3.SQL GROUP BY 实例

"Orders" 表：

O_Id	OrderDate	OrderPrice	Customer
001	2018/2/29	1000	Buh
002	2018/11/3	1600	Car
003	2018/10/5	700	Buh
004	2018/09/8	300	Buh
005	2018/09/06	2000	Aam
006	2018/07/1	100	Car

查找每个客户的总金额（总订单），使用 GROUP BY 语句对客户进行组合：

SELECT Customer,SUM(OrderPrice) FROM Orders

GROUP BY Customer

结果集：

Customer	SUM(OrderPrice)
Buh	2000
Car	1700
Aam	20

如果省略GROUP BY：

SELECT Customer,SUM(OrderPrice) FROM Orders

结果集：

Customer	SUM(OrderPrice)
Buh	5700
Car	5700
Buh	5700
Buh	5700
Aam	5700
Car	5700

上面的结果集不是我们需要的。

上面的 SELECT 语句指定了两列（Customer 和 SUM(OrderPrice)）。"SUM(OrderPrice)" 返回一个单独的值（"OrderPrice" 列的总计），而 "Customer" 返回 6 个值（每个值对应 "Orders" 表中的每一行）。因此，得不到正确的结果。不过通过使用GROUP BY 语句解决了这个问题。

4.GROUP BY多个列

对一个以上的列应用 GROUP BY 语句：

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders

GROUP BY Customer,OrderDate

十、SQL HAVING 语句

1.HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

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

3.SQL HAVING 实例

"Orders" 表：

O_Id	OrderDate	OrderPrice	Customer
001	2018/2/29	1000	Buh
002	2018/11/3	1600	Car
003	2018/10/5	700	Buh
004	2018/09/8	300	Buh
005	2018/09/06	2000	Aam
006	2018/07/1	100	Car

查找订单总金额少于 2000 的客户，使用如下 SQL 语句：

SELECT Customer,SUM(OrderPrice) FROM Orders

GROUP BY Customer

HAVING SUM(OrderPrice)<2000

结果集：

Customer	SUM(OrderPrice)
Car	1700

查找客户 "Buh" 或 "Aam" 拥有超过 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)
Buh	2000
Aam	2000

十一、SQL UCASE() 函数

1.UCASE() 函数

UCASE 函数把字段的值转换为大写。

2.SQL UCASE() 语法

SELECT UCASE(column_name) FROM table_name

3.SQL UCASE() 实例

"Persons" 表：

Id_P	LastName	FirstName	Address	City
001	Aam	Joh	Oxd Street	London
002	Buh	Grge	Fifth Aue	New York
003	Car	Tmas	Can Street	Beijing

选取 "LastName" 和 "FirstName" 列的内容，然后把 "LastName" 列转换为大写，使用如下 SQL 语句：

SELECT UCASE(LastName) as LastName,FirstName FROM Persons

结果集：

LastName	FirstName
AAM	Joh
BUH	Grge
CAR	Tmas

十二、SQL LCASE() 函数

1.LCASE() 函数

LCASE 函数把字段的值转换为小写。

2.SQL LCASE() 语法

SELECT LCASE(column_name) FROM table_name

3.SQL LCASE() 实例

"Persons" 表：

Id_P	LastName	FirstName	Address	City
001	Aam	Joh	Oxd Street	London
002	Buh	Grge	Fifth Aue	New York
003	Car	Tmas	Can Street	Beijing

选取 "LastName" 和 "FirstName" 列的内容，然后把 "LastName" 列转换为小写，使用如下 SQL 语句：

SELECT LCASE(LastName) as LastName,FirstName FROM Persons

结果集：

LastName	FirstName
aam	Joh
buh	Grge
car	Tmas

十三、SQL MID() 函数

1.MID() 函数

MID 函数用于从文本字段中提取字符。

2.SQL MID() 语法

SELECT MID(column_name,start[,length]) FROM table_name

参数	描述
column_name	必需。要提取字符的字段。
start	必需。规定开始位置（起始值是 1）。
length	可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。

3.SQL MID() 实例

"Persons" 表：

Id_P	LastName	FirstName	Address	City
001	Aam	Joh	Oxd Street	London
002	Buh	Grge	Fifth Aue	New York
003	Car	Tmas	Can Street	Beijing

从 "City" 列中提取前 3 个字符，使用如下 SQL 语句：

SELECT MID(City,1,3) as SmallCity FROM Persons

结果集：

SmallCity

Lon

New

Bei

十四、SQL LEN() 函数

1.LEN() 函数

LEN 函数返回文本字段中值的长度。

2.SQL LEN() 语法

SELECT LEN(column_name) FROM table_name

3.SQL LEN() 实例

"Persons" 表：

Id_P	LastName	FirstName	Address	City
001	Aam	Joh	Oxd Street	London
002	Buh	Grge	Fifth Aue	New York
003	Car	Tmas	Can Street	Beijing

取得 "City" 列中值的长度，使用如下 SQL 语句：

SELECT LEN(City) as LengthOfCity FROM Persons

结果集：

LengthOfCity

十五、SQL ROUND() 函数

1.ROUND() 函数

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

2.SQL ROUND() 语法

SELECT ROUND(column_name,decimals) FROM table_name

参数	描述
column_name	必需。要舍入的字段。
decimals	必需。规定要返回的小数位数。

3.SQL ROUND() 实例

"Products" 表：

Prod_Id	ProductName	Unit	UnitPrice
001	gold	1000 g	32.35
002	silver	1000 g	11.56
003	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() 函数

1.NOW() 函数

NOW 函数返回当前的日期和时间。

注：在使用 Sql Server 数据库，请使用 getdate() 函数来获得当前的日期时间。

2.SQL NOW() 语法

SELECT NOW() FROM table_name

3.SQL NOW() 实例

"Products" 表：

Prod_Id	ProductName	Unit	UnitPrice
001	gold	1000 g	32.35
002	silver	1000 g	11.56
003	copper	1000 g	6.85

显示当天的日期所对应的名称和价格，使用如下 SQL 语句：

SELECT ProductName, UnitPrice, Now() as PerDate FROM Products

结果集：

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

十七、SQL FORMAT() 函数

1.FORMAT() 函数

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

2.SQL FORMAT() 语法

SELECT FORMAT(column_name,format) FROM table_name

参数	描述
column_name	必需。要格式化的字段。
format	必需。规定格式。

3.SQL FORMAT() 实例

"Products" 表：

Prod_Id	ProductName	Unit	UnitPrice
001	gold	1000 g	32.35
002	silver	1000 g	11.56
003	copper	1000 g	6.85

显示每天日期所对应的名称和价格（日期的显示格式是 "YYYY-MM-DD"），使用如下 SQL 语句：

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate

FROM Products

结果集：

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

十八、SQL NULL 函数

注：SQL ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数

"Products" 表：

P_Id	ProductName	UnitPrice	UnitsInStock	UnitsOnOrder
001	computer	699	25	15
002	printer	365	36
003	telephone	280	159	57

假如 "UnitsOnOrder" 是可选的，而且可以包含 NULL 值，使用如下 SELECT 语句：

SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)

FROM Products

在上面的例子中，如果有 "UnitsOnOrder" 值是 NULL，那么结果是 NULL。

微软的 ISNULL() 函数用于规定如何处理 NULL 值。NVL(), IFNULL() 和 COALESCE() 函数也可以达到相同的结果。

在这里，NULL 值为 0。下面，如果 "UnitsOnOrder" 是 NULL，则不利于计算，因此如果值是 NULL 则 ISNULL() 返回 0。

①SQL Server / MS Access

SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))

FROM Products

②Oracle

Oracle 没有 ISNULL() 函数。使用 NVL() 函数达到相同的结果：

SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))

FROM Products

③MySQL

MySQL 也拥有类似 ISNULL() 的函数。不过它的工作方式与微软的 ISNULL() 函数有点不同。

在 MySQL 中，可以使用 IFNULL() 函数：

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))

FROM Products

或者使用 COALESCE() 函数：

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))

FROM Products

十九、SQL Date 函数

1.SQL 日期

处理日期时，最难的任务是确保所插入的日期的格式，与数据库中日期列的格式相匹配。只要数据包含的只是日期部分，运行查询就不会出问题。但是，如果涉及时间，情况就有点复杂了。在讨论日期查询的复杂性之前，先来了解最重要的内建日期处理函数。

1.MySQL Date 函数

MySQL 中最重要的内建日期函数：

函数	描述	语法
NOW()	返回当前的日期和时间	NOW()
CURDATE()	返回当前的日期	CURDATE()
CURTIME()	返回当前的时间	CURTIME()
DATE()	提取日期或日期/时间表达式的日期部分	DATE(date)
EXTRACT()	返回日期/时间按的单独部分	EXTRACT(unit FROM date)
DATE_ADD()	给日期添加指定的时间间隔	DATE_ADD(date,INTERVAL expr type)
DATE_SUB()	从日期减去指定的时间间隔	DATE_SUB(date,INTERVAL expr type)
DATEDIFF()	返回两个日期之间的天数	DATEDIFF(date1,date2)
DATE_FORMAT()	用不同的格式显示日期/时间	DATE_FORMAT(date,format)

注：date参数是合法的日期表达式；expr参数是添加的时间间隔；format规定日期/时间的输出格式。

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值（00-31）
%e	月的天，数值（0-31）
%f	微秒
%H	小时（00-23）
%h	小时（01-12）
%I	小时（01-12）
%i	分钟，数值（00-59）
%j	年的天（001-366）
%k	小时（0-23）
%l	小时（1-12）
%M	月名
%m	月，数值（00-12）
%p	AM或PM
%r	时间，12-小时（hh:mm:ss AM或PM）
%S	秒（00-59）
%s	秒（00-59）
%T	时间，24-小时（hh:mm:ss)
%U	周（00-53）星期日是一周的第一天
%u	周（00-53）星期一是一周的第一天
%V	周（01-53）星期日是一周的第一天，与%X使用
%v	周（01-53）星期一是一周的第一天，与%x使用
%W	星期名
%w	周的天（0=星期日，6=星期六）
%X	年，其中的星期日是周的第一天，4位，与%V使用
%x	年，其中的星期是周的第一天，4位，与%v使用
%Y	年，4位
%y	年，2位

2.SQL Server Date 函数

SQL Server 中最重要的内建日期函数：

函数	描述	语法
GETDATE()	返回当前日期和时间	GETDATE()
DATEPART()	返回日期/时间的单独部分	DATEPART(datepart,date)
DATEADD()	在日期中添加或减去指定的时间间隔	DATEADD(datepart,number,date)
DATEDIFF()	返回两个日期之间的时间	DATEDIFF(datepart,startdate,enddate)
CONVERT()	用不同的格式显示日期/时间	CONVERT(data_type(length),expression,style)

注：date参数是合法的日期表达式；number是希望添加的间隔数（对于未来的时间，此数是正数，对于过去的时间，此数是负数）；data_type(length)规定目标数据类型（带有可选的长度）；expression规定需要转换的值；style规定日期/时间的输出格式。

2.SQL Date 数据类型

①MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

DATE - 格式 YYYY-MM-DD

DATETIME - 格式: YYYY-MM-DD HH:MM:SS

TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS

YEAR - 格式 YYYY 或 YY

②SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：

DATE - 格式 YYYY-MM-DD

DATETIME - 格式: YYYY-MM-DD HH:MM:SS

SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS

TIMESTAMP - 格式: 唯一的数字

阳阳大魔王

关注

44
点赞
踩
18

收藏

觉得还不错? 一键收藏
打赏
1
评论
SQL世界之函数+语句（九，十）

"SUM(OrderPrice)" 返回一个单独的值（"OrderPrice" 列的总计），而 "Customer" 返回 6 个值（每个值对应 "Orders" 表中的每一行）。因此，得不到正确的结果。number是希望添加的间隔数（对于未来的时间，此数是正数，对于过去的时间，此数是负数）；处理日期时，最难的任务是确保所插入的日期的格式，与数据库中日期列的格式相匹配。下面，如果 "UnitsOnOrder" 是 NULL，则不利于计算，因此如果值是 NULL 则 ISNULL() 返回 0。
复制链接

扫一扫