一起来学SQL（四）

SQL函数

SQL Aggregate 函数

SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。

• AVG() - 返回平均值
• COUNT() - 返回行数
• FIRST() - 返回第一个记录的值
• LAST() - 返回最后一个记录的值
• MAX() - 返回最大值
• MIN() - 返回最小值
• SUM() - 返回总和

SQL Scalar 函数

SQL Scalar 函数基于输入值，返回一个单一的值。

• UCASE() - 将某个字段转换为大写
• LCASE() - 将某个字段转换为小写
• MID() - 从某个文本字段提取字符
• LEN() - 返回某个文本字段的长度
• NOW() - 返回当前的系统日期和时间
• FORMAT() - 格式化某个字段的显示方式

---

SQL Aggregate 函数

SQL AVG() 函数

AVG() 函数返回数值列的平均值。

AVG() 语法
SELECT AVG(column_name) FROM table_name

e.g.选择价格高于平均价格的 “ProductName” 和 “Price” 记录：
SELECT ProductName, Price FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products);

---

SQL COUNT() 函数

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

COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：

SELECT COUNT(column_name) FROM table_name;

COUNT(*) 函数返回表中的记录数：

SELECT COUNT(*) FROM table_name;

COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：

SELECT COUNT(DISTINCT column_name) FROM table_name;

e.g.
SELECT COUNT(DISTINCT CustomerID) AS NumberOfCustomers FROM Orders;

---

SQL FIRST() 函数

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

SQL FIRST() 语法
SELECT FIRST(column_name) FROM table_name;

p.s. 只有 MS Access 支持 FIRST() 函数。

SQL Server、MySQL 和 Oracle 中的 SQL FIRST() 工作区

• SQL Server 语法
  SELECT TOP 1 column_name FROM table_name ORDER BY column_name ASC;
• MySQL 语法
  SELECT column_name FROM table_name ORDER BY column_name ASC LIMIT 1;
• Oracle 语法
  SELECT column_name FROM table_name ORDER BY column_name ASC
  WHERE ROWNUM <=1;

---

SQL LAST() 函数

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

SQL LAST() 语法
SELECT LAST(column_name) FROM table_name;

p.s. 同样，只有 MS Access 支持LAST() 函数

SQL Server、MySQL 和 Oracle 中的 SQL LAST() 工作区
只需将上例中的ASC改成DESC就可以了

---

SQL MAX()

MAX() 函数返回指定列的最大值。

SQL MAX() 语法
SELECT MAX(column_name) FROM table_name;

e.g.SQL 语句从 “Products” 表的 “Price” 列获取最大值：
SELECT MAX(Price) AS HighestPrice FROM Products;

---

SQL MIN()

MIN() 函数返回指定列的最小值。

SQL MIN() 语法
SELECT MIN(column_name) FROM table_name;

e.g. SQL 语句从 “Products” 表的 “Price” 列获取最小值：
SELECT MIN(Price) AS SmallestOrderPrice FROM Products;

---

SQL SUM()

SUM() 函数返回数值列的总数。

SQL SUM() 语法
SELECT SUM(column_name) FROM table_name;

e.g.SQL 语句查找 “OrderDetails” 表的 “Quantity” 字段的总数：
SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

与SQL Aggregate 函数配合的子句

SQL GROUP BY

Aggregate 函数常常需要添加 GROUP BY 语句。

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

SQL GROUP BY 语法
SELECT column_name, aggregate_function(column_name) FROM table_name
WHERE column_name operator value GROUP BY column_name;

举个栗子

查找每个送货员配送的订单数目。下面的 SQL 语句按送货员进行订单分类统计：

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;

---

SQL HAVING

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

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 语句查找注册超过 10 个订单的员工：

SELECT Employees.LastName, COUNT(Orders.OrderID)
AS NumberOfOrders FROM (Orders INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

SQL Scalar 函数

SQL 字母大小写转换函数UPPER(s)、UCASE(s)、LOWER(s)和LCASE(s)

1、LOWER(s)函数和LCASE(s)函数
LOWER(s)或者LCASE(s)函数可以将字符串s中的字母字符全部转换成小写字母。

2、UPPER(s)函数和UCASE(s)函数
UPPER(s)或UCASE(s)函数可以将字符串s中的字母字符全部转换成大写字母。

UPPER(s)函数和UCASE(s)函数

upper()函数字母大小写转换函数，将字母转成大写 - 返回字符串str，根据当前字符集映射的所有字符更改为大写。

UPPER(str)
返回字符串str，根据当前字符集映射的所有字符更改为大写。
SELECT UPPER(‘Allah-hus-w3cschool’);

UCASE() 函数
把字段的值转换为大写。
SELECT UCASE(column_name) FROM table_name;

---

UPPER(s)函数和UCASE(s)函数

用法同上

---

MID() 函数

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

SQL MID() 语法
SELECT MID(column_name,start[,length]) FROM table_name;

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

e.g. SQL 语句从 “Customers” 表的 “City” 列中提取前 4 个字符：
SELECT MID(City,1,4) AS ShortCity FROM Customers;

LEN() 函数

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

SQL LEN() 语法
SELECT LEN(column_name) FROM table_name;

e.g. SQL 语句从 “Customers” 表中选取 “CustomerName” 和 “Address” 列中值的长度：
SELECT CustomerName,LEN(Address) as LengthOfAddress FROM Customers;

---

ROUND() 函数

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

SQL ROUND() 语法
SELECT ROUND(column_name,decimals) FROM table_name;

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

e.g. SQL 语句从 “Products” 表中选取产品名称和价格舍入为最接近的整数：。提取前 4 个字符：
SELECT ProductName, ROUND(Price,0) AS RoundedPrice FROM Products;

---

NOW() 函数

NOW() 函数返回当前系统的日期和时间。

SQL NOW() 语法
SELECT NOW() FROM table_name;

e.g. SQL 语句从 “Products” 表中选取产品名称以及当天的价格：
SELECT ProductName, Price, Now() AS PerDate FROM Products;

---

FORMAT() 函数

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

SQL FORMAT() 语法
SELECT FORMAT(column_name,format) FROM table_name;

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

e.g. SQL 语句从 “Products” 表中选取产品名称以及当天（格式化为 YYYY-MM-DD）的价格：
SELECT ProductName, Price, FORMAT(Now(),’YYYY-MM-DD’) AS PerDate FROM Products;

其他

• SQRT 函数
  SQRT 函数用于计算得出任何数值的平方根
  SELECT name, SQRT(daily_typing_pages) FROM employee_tbl;

• RAND 函数
  用于产生 0 至 1 之间的随机数

• CONCAT 函数
  CONCAT 函数用于将两个字符串连接为一个字符串

• REPLACE()函数
  字符串替换函数
  把数据库表article中的所有title字段里的w3cschool字符串替换成hello。
  update article set title=replace(title,’w3cschool’,’hello’);

replace函数定义

replace(original-string，search-string，replace-string)

参数
original-string： 被搜索的字符串。可为任意长度。
search-string： 要搜索并被 replace-string 替换的字符串。该字符串的长度不应超过 255 个字节。如果 search-string 是空字符串，则按原样返回原始字符串。
replace-string： 该字符串用于替换 search-string。可为任意长度。如果 replacement-string 是空字符串，则删除出现的所有 search-string。

• TRIM()函数
  SQL 中的 TRIM 函数是用来移除掉一个字串中的字头或字尾。最常见的用途是移除字首或字尾的空白。
  这个函数在不同的资料库中有不同的名称：
  MySQL: TRIM( ), RTRIM( ), LTRIM( )
  Oracle: RTRIM( ), LTRIM( )
  SQL Server: RTRIM( ), LTRIM( )