SQLServer-Day8

AVG 函数

AVG函数但会数值列的平均值，NULL

值不包括在计算中

SELECT AVG(column_name) FROM yable_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

例1：计算OrderPrice字段的平均值：

1	SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

结果：

OrderAverage
950

例2：找到OrderPrice值高于OrderPrice平均值的客户

1 2	SELECT Curstomer FROM Orders WHERE OrderPrice > (SELECT AVG(OrderPrice) FROM Orders)

结果：

Customer
Bush
Carter
Adams

COUNT函数

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

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：计算客户“Carter”的订单数。

1 2	SELECT COUNT(Customer) AS CustimerNilsen FROM Orders WHERE Customer = 'Carter'

以上SQL语句的返回结果是2，因为客户Carter共有2个订单。

CustomerNilsen
2

例2：

1	SELECT COUNT(*) AS NumberOfOrders FROM Orders

结果：

NumberOfOrders
6

这是表中的总行数。

例3：计算”Orders”表中不同客户的数目

1	SELECT COUNT(DISTINCT Customer) AS NumberOdCustomers FROM Orders

结果：

NumberOfCustomers
3

FIRST()函数

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

**Tips：**可使用ORDER BY 语句对记录进行排序

SELECT FIRST(column_name) FROM table_name

实例：Orders表同上，查找“OrderPrice”列的第一个值

1	SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders

结果类似这样：

FirstOrderPrice
1000

LAST()函数

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

**Tips：**可使用ORDER BY 语句对记录进行排序

SELECT LAST(column_name) FROM table_name

例：查找”OrderPrice“列的最后一个值

1	SELECT LAST(OrderPrices) AS LastOrderPrice FROM Orders

结果：

LastOrderPrice
100

MAX() 和 MIN() 函数

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

SELECT MAX(column_name) FROM table_name

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

Orders表：

例：我们要查找”OrderPrice“列中的最大值

1	SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

结果：

LargestOrderPrice
2000

1	SELECT MIN(OrderPrice) AS LargestOrderPrice FROM Orders

结果：

SmallestOrderPrice
100

SUM() 函数

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

SELECT SUM(column_name) FROM table_name

例：我们希望查找“OrderPrice”字段的总数

1	SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

结果：

OrderTotal
5700

GROUP BY语句

合计函数（比如SUM）尝尝需要添加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示例

现在，我们希望查找每个客户的总金额（总订单）

我们想要使用GROUP BY语句对客户进行组合。

1 2	SELECT Customer, SUM(OrderPrice) FROM Orders GROUP BY Customer

结果：

Customer	SUM(OrderPrice)
Bush	2000
Carter	1700
Adams	2000

GROUP BY一个以上的列

1 2	SELECT Customer, OrderDate, SUM(OrderPrice) FROM Orders GROUP BY Customer, OrderDate

HAVING子句

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

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE colum_name operator value 
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

例1：我们希望查找订单总金额少于2000的客户

1
2
3

SELECT Customer, SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice) < 2000

结果：

Customer	SUM(OrderPrice)
Carter	1700

例2：我们希望查找客户“Bush”或“Adams”拥有超过1500的订单总金额

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

UCASE()和LCASE()函数

UCASE函数把字段的值转换为大写，LCASE()函数把字段的值转换为小写

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”列转换为大写

1	SELECT UCASE(LastName) as LastName, FirstName FROM Persons

结果：

LastName	FirstName
ADAMS	John
BUSH	George
CARTER	Thomas

1	SELECT LCASE(LastName) as LastName, FirstName FROM Persons

结果：

LastName	FirstName
adams	John
bush	George
carter	Thomas

MID()函数

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

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

参数	描述
column_name	必需。要提取字符的字段
start	必需。规定开始位置（起始值是1）
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

例1：我们希望从“City”列中提出前3个字符。

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

SmallCity
Lon
New
Bei

LEN()函数

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

SELECT LEN(column_name) FROM table_name

Persons表同上：

1	SELECT LEN(City) AS LengthOfCity FROM Persons

结果：

LengthOfCity
6
8
7

ROUND()函数

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

例：把名称和价格舍入为最接近的整数

1	SELECT ProductName, ROUND(UnitPrice, 0) AS UnitPrice FROM Products

结果：

ProductName	UnitPrice
gold	32
silver	12

NOW()函数

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

**Tips：**如果您在使用SQL Server数据库，请使用getdate()函数来获得当前的日期时间。

SELECT NOW() FROM table_name

例：表格同上，要求显示当天的日期所对应的名称和价格

1	SELECT ProfuctName, 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

FORMAT()函数

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

SELECT FORMAT(column_name, format) FROM table_name

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

表同上。

例：需要实现每天日期所对应的名称和价格（日期显示的格式为“YYYY-MM-DD”）

1 2	SELECT ProductName, UnitPrice, FORMAT(NOW(), 'YYYY-MM-DD') AS PerDate FROM Product

结果：

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