MYSQL必知必会（六）使用数据处理函数

使用数据处理函数

函数

函数没有SQL的可移植性强，能运行在多个系统上的代码称为可移植的（portable），相对来说，多数SQL语句是可移植的，在SQL实现之间有差异时，这些差异通常不那么难处理。而函数的可移植性却不强。

大多数SQL实现支持以下类型的函数：

1. 用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数
2. 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数
3. 用于处理日期和时间值并从这些值中提取特定成分（例如，返回两个日期之差，检查日期有效性等）的日期和时间函数
4. 返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数

文本处理函数

SELECT vend_name,Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name

Upper()将文本转换为大写
常用的文本处理函数

SOUNDEX：将任何文本串转换为描述其语音表示的字母数字模式的算法，对串进行发音比较而不是字母比较

SELECT cust_name,cust_contact
FROM customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Y.Lie')

日期和时间处理函数

常用的日期和时间处理函数：

SELECT cust_id,order_num
FROM orders
WHERE order_date = '2005-09-01';

但有时表中给出的日期会精确到秒，故可以用Date函数

SELECT cust_id,order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';

注：如果要的是日期，请使用Date()

BETWEEN操作符用来把2005-09-01和2005-09-30定义为一个要匹配的日期范围

SELECT cust_id,order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

Year()是一个从日期（或日期时间）中返回年份的函数。类似，Month()从日期中返回月份。

SELECT cust_id,order_num
FROM orders
WHERE YEAR(order_date) = 2005 AND Month(order_date) = 9;##检索出order_date为2005年9月的所有行

数值处理函数

汇总数据

检索例子：

1. 确定表中行数（或者满足某个条件或包含某个特定值的行数）
2. 获得表中行组的和
3. 找出表列（或所有行或某些特定的行）的最大值、最小值和平均值
   **聚集函数：**运行在行组上，计算和返回单个值的函数
   

AVG()函数

AVG()通过对表中行数计数并计算特定列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

SELECT AVG(prod_price) AS avg_price
FROM products

注：

1. 只用于单个列：AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。
2. NULL值：AVG()函数忽略列值为NULL的行

COUNT()函数

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目
COUNT()函数有两种使用方式：

1. 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值
2. 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值

SELECT COUNT(*) AS num_cust
FROM customers

只对具有电子邮件地址的客户计数

SELECT COUNT(cust_email) AS num_cust
FROM customers

MAX()函数

SELECT MAX(prod_price) AS max_price
FROM products

注：

1. 对非数值数据使用MAX()：虽然MAX()一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行
2. NULL值：MAX()函数忽略列值为NULL的行

MiN()函数

SELECT MIN(prod_price) AS max_price
FROM products

注：

1. 对非数值数据使用MIN()：MIN()函数与MAX()函数类似，MySQL允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。在用于文本数据时，如果数据按相应的列排序，则MIN()返回最前面的行
2. MIN()函数忽略列值为NULL的行

SUM()函数

SUM()用来返回指定列值的和（总计）

SELECT SUM(item_price * quantity) AS total_price
FROM orderitems
WHERE order_num = 20005

注：
1.在多个列上进行计算：如本例所示，利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算
2.NULL值：SUM()函数忽略列值为NULL的行

聚集不同值

以上5个聚集函数都可以如下使用：

1. 对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）
2. 只包含不同的值，指定DISTINCT参数

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id= 1003;

注：

1. 如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式
2. 将DISTINCT用于MIN()和MAX() 虽然DISTINCT从技术上可用于MIN()和MAX()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否包含不同值都是相同的

组合聚集函数

SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM products;

分组数据

数据分组

返回供应商1003提供的产品数目

SELECT COUNT(*) AS num_prods
FROM products;
WHERE vend_id = 1003

那么如果返回每个供应商的产品数目怎么办？或者只提供单项产品的供应商所提供的的产品，或提供10个以上产品的供应商怎么办？引入创建分组

创建分组

SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

在具体使用GROUP BY子句前，需要知道一些重要的规定：

1. GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制
2. 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）
3. GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名
4. 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出
5. 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组
6. GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前

SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id  WITH ROLLUP; 

注：可以显示出null的个数，如不加WITH ROLLUP，则看不到null的个数

过滤分组

where过滤行而having过滤分组，所有类型的where都可以利用having来代替，WHERE在数据
分组前进行过滤，HAVING在数据分组后进行过滤

SELECT cust_id,COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2

SELECT vend_id,COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*) >= 2;

分组和排序

虽然GROUP BY和ORDER BY经常完成相同的工作，但它们是非常不同的

注：一般在使用GROUP BY子句时，应该也给
出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据

SELECT order_num,SUM(quantity * item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(order_num * quantity) >=50;

![在这里插入图片描述](https://img-blog.csdnimg.cn/20200504144030167.PNG#pic_center

SELECT order_num,SUM(quantity * item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(order_num * quantity) >=50
order by ordertotal;

SELECT子句顺序