SQL 高级（10） 函数
SQL 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 |
现在，我们希望查找每个客户的总金额（总订单）。
我们想要使用 GROUP BY 语句对客户进行组合。
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 |
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 |
| 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 |
SQL UCASE() 函数
SQL UCASE() 语法
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" 列转换为大写。
SELECT UCASE(LastName) as LastName,FirstName FROM Persons
结果集类似这样：
| LastName |
FirstName |
| ADAMS |
John |
| BUSH |
George |
| CARTER |
Thomas |
SQL LCASE() 函数
SQL LCASE() 语法
SELECT LCASE(column_name) FROM table_name
SQL LCASE() 实例
我们拥有下面这个 "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 |
SQL MID() 函数
MID() 函数
MID 函数用于从文本字段中提取字符。
SQL MID() 语法
SELECT MID(column_name,start[,length]) FROM table_name
| 参数 |
描述 |
| column_name |
必需。要提取字符的字段。 |
| start |
必需。规定开始位置（起始值是 1）。 |
| length |
可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。 |
SQL MID() 实例
我们拥有下面这个 "Persons" 表：
| Id |
LastName |
FirstName |
Address |
City |
| 1 |
Adams |
John |
Oxford Street |
London |
| 2 |
Bush |
George |
Fift
|