MySQL必知必会第十一章-

使用数据处理函数

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

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

1.文本处理函数

    FROM vendors
    ORDER BY vend_name;  #Upper()将文本转换为大写

常见的的文本处理函数

函数	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个字串
Lower()	将串转换为小写
LTrim()	去掉串左边的空格
RTrim()	去掉串右边的空格
Right()	返回串右边的字符
Soundex()	返回串的SOUNDEX值
Upper()	将串转换为大写

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。

    WHERE cust_contact = 'Y. Lie'; #联系名实际上是Y. Lie，但是cust_contact中写成了‘Y. Lee’,那么现在无法查找到该联系名

    WHERE Soundex(cust_contact) = Soundex('Y. Lee'); #，WHERE子句使用Soundex()函数来转换cust_contact列值和搜索串为它们的SOUNDEX值。因为Y.Lee和Y.Lie发音相似，所以它们的SOUNDEX值匹配

2.日期和时间处理函数

常见的日期和时间处理函数

函数	说明
AddDate()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

MySQL使用的日期格式必须为格式yyyy-mm-dd。2005年9月1日，给出为2005-09-01。

    WHERE order_date = '2005-09-01';  #检索订单日期为2005-09-01的订单记录。

如果用当前日期和时间存储订单日期（存储的order_date 值为2005-09-01 11:30:05），则WHERE order_date = '2005-09-01'失败。即使给出具有该日期的一行，也不会把它检索出来，因为WHERE匹配失败。解决办法是指示MySQL仅将给出的日期与列中的日期部分进行比较，而不是将给出的日期与整个列值进行比较。为此，必须使用Date()函数。Date(order_date)指示MySQL仅提取列的日期部分：

    WHERE Date(order_date) = '2005-09-01'; 

当然，也存在一个Time()函数，在你只想要时间时应该使用它。

    WHERE Year(order_date) = 2005 AND Month(order_date) = 9; #检索2005年9月的所有订单

还有一种方法：

    WHERE order_date BETWEEN '2005-09-01' AND '2005-09-30';

3.数值处理函数

常用的数值处理函数：

函数	说明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

汇总数据

当需要对数据进行汇总而不是检索表中数据时，MySQL提供了专门的函数。这种例子有以下几种：
1> 确定表中行数（或者满足某个条件或包含某个特定值的行数）
2> 获得表中行组的和
3> 找出表列（或所有行或某些特定的行）的最大值、最小值和平均值
聚集函数（aggregate function），运行在行组上，计算和返回单个值的函数。

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

1.AVG()函数

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

    FROM products;  #返回表中所有产品的平均价格

    WHERE prod_id = 1003; #返回特定列的平均值

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

2.COUNT()函数

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。
COUNT()函数有两种使用方式：
1> 使用COUNT(*)对表中行的数目进行计数，包含NULL在内
2> 使用COUNT(column)对特定列中具有值的行进行计数，不包含NULL值

    FROM customers; #这里COUNT对所有行计数

    FROM customers; #这里COUTN对cust_email)中有值的行进行计数

3.MAX()函数

MAX()返回指定列中的最大值。MAX()要求指定列名

    FROM products; #返回products中最贵的物品的价格

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

4.MIN()函数

MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。与MAX()一样，MIN()要求指定列名。

    FROM products; #MIN()返回products表中最便宜物品的价格。

5.SUM()函数

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

    FROM orderitems
    WHERE order_num = 20005;
![](https://img2018.cnblogs.com/blog/1651714/201904/1651714-20190427160416788-1360113344.png)

SUM()也可以用来合计计算值。
```SELECT SUM(quantity*item_price) AS total_price
    FROM orderitems
    WHERE order_num = 20005;

利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。

5.DISTINCT聚集不同值

以上5个聚集函数都可以如下使用：
1> 对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认
行为）；
2> 只包含不同的值，指定DISTINCT参数。

    FROM products
    WHERE vend_id = 1003;

DISTINCT必须使用列名，不能用于计算或表达式。

6.组合聚集函数

实际上SELECT语句可根据需要包含多个聚集函数:

    MIN(prod_price) AS min_price,
    MAX(prod_price) AS max_price,
    AVG(prod_price) AS price_avg
    FROM products;   #注意这里的多个聚集函数之间必须用逗号隔开，否则会报错

分组数据

1.创建分组

分组是在SELECT语句的GROUP BY子句中建立的：

    FROM products
    GROUP BY vend_id; #。GROUP BY子句指示MySQL按vend_id排序并分组数据。

使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值：

    FROM products
    GROUP BY vend_id WITH ROLLUP;

2.过滤分组

MySQL为此目的提供了另外的子
句，那就是HAVING子句。HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。

    FROM orders
    GROUP BY cust_id 
    HAVING COUNT(*) >= 2; #HAVING子句，它过滤COUNT(*) >=2（两个以上的订单）的那些分组。

WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

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

    FROM products
    GROUP BY vend_id
    HAVING COUNT(*) >= 2;

3.分组和排序

GROUP BY和ORDER BY的区别：

ORDER BY	GROUP BY
排序产生的输出	分组行。但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

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

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

4.SELECT 子句顺序

SELECT语句中子句的顺序：

|子句|说明|是否必须使用|
|-----------|-----------|
|SELECT|要返回的列或表达式|是|
|FROM|从中检索数据的表|仅在从表选择数据时使用|
|WHERE|行级过滤|否|
|GROUP BY|分组说明|仅在按组计算聚集时使用|
|HAVING|组级过滤|否|
|ORDER BY|输出排序顺序|否|
|LIMIT|要检索的行数|否|

使用子查询

1.利用子查询进行过滤

假如需要列出订购物品TNT2的所有客户，应该怎样检索？
(1) 检索包含物品TNT2的所有订单的编号。
(2) 检索具有前一步骤列出的订单编号的所有客户的ID。
(3) 检索前一步骤返回的所有客户ID的客户信息。
上述每个步骤都可以单独作为一个查询来执行。

WHERE prod_id = 'TNT2'; #，对于prod_id为TNT2的所有订单物品，它检索其order_num列

WHERE order_num IN (20005,20007); #查询具有订单20005和20007的客户ID

对于上面的两个单独的查询，可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。

WHERE order_num IN (SELECT order_num FROM orderitems  WHERE prod_id = 'TNT2');

现在得到了订购物品TNT2的所有客户的ID。下一步是检索这些客户ID的客户信息。

WHERE  cust_id IN (10001,10004);

可以把其中的WHERE子句转换为子查询而不是硬编码这些客户id。

FROM customers 
WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));

把子查询分解为多行并且适当地进行缩进，能极大地简化子查询的使用。

2. 作为计算字段使用子查询

假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。
为了执行这个操作，遵循下面的步骤。
(1) 从customers表中检索客户列表。
(2) 对于检索出的每个客户，统计其在orders表中的订单数目。

    FROM orders
    GROUP BY cust_id;

可使用SELECT COUNT(*)对表中的行进行计数，并且通过提供一条WHERE子句来过滤某个特定的客户ID，可仅对该客户的订单进行计数。

FROM orders
WHERE cust_id = 10001; #对客户10001的订单进行计数

为了对每个客户执行COUNT()计算，应该将COUNT()作为一个子查询。

FROM customers
ORDER BY  cust_name;

相关子查询（correlated subquery） 涉及外部查询的子查询。任何时候只要列名可能有多义性，就必须使用这种语法（表名和列名由一个句点分隔）。

联接表

1.创建联结

联结的创建非常简单，规定要联结的所有表以及它们如何关联即可。

FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name;

2.内部联结

目前为止所用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。下面的SELECT语句返回与前面例子完全相同的数据：

FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

这里，两个表之间的关系是FROM子句的组成部分，以INNERJOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。

3.联结多个表

SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。

FROM orderitems,vendors, products
WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 20005; #此例子显示编号为20005的订单中的物品。

返回订购产品TNT2的客户列表：

FROM orderitems,orders,customers
WHERE customers.cust_id = orders.cust_id
AND