计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式。我们需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化。

可在SQL语句内完成的许多转换和格式化工作都可以直接在客户机应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户机中完成要快得多，因为DBMS是设计来快速有效地完成这种处理的。

拼接字段--concat

mysql> select concat(vend_name,'(',vend_country,')')
    -> from vendors
    -> order by vend_name;
+----------------------------------------+
| concat(vend_name,'(',vend_country,')') |
+----------------------------------------+
| ACME(USA)                              |
| Anvils R Us(USA)                       |
| Furball Inc.(USA)                      |
| Jet Set(England)                       |
| Jouets Et Ours(France)                 |
| LT Supplies(USA)                       |
+----------------------------------------+
6 rows in set (0.00 sec)

使用别名

SQL支持列别名。别名（alias）是一个字段或值的替换名。别名用AS关键字赋予。

mysql> select concat(vend_name,'(',vend_country,')') as vend_title from vendors order by vend_name;
+------------------------+
| vend_title             |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+
6 rows in set (0.00 sec)

计算

汇总物品的价格（单价乘以订购数量）：

mysql> select prod_id ,quantity,item_price, quantity*item_price as expanded_price from orderitems where order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+
4 rows in set (0.00 sec)

函数

文本处理函数

函数	说明
left()	返回串左边的字符
length()	返回串的长度
locate()	找出串的一个子串
lower()	将串转换为小写
ltrim()	去掉串左边的空格
right()	返回串右边的字符
rtrim()	去掉串右边的空格
soundex()	返回串的SOUNDEX值
subString()	返回子串的字符
upper()	将串转换为大写

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

customers表中有一个顾客Coyote Inc.，其联系名为Y.Lee。但如果这是输入错误，此联系名实际应该是Y.Lie

mysql> select cust_name,cust_contact from customers where cust_contact = 'Y.Lie';
Empty set (0.00 sec)
mysql> select cust_name,cust_contact from customers where Soundex(cust_contact) = Soundex('Y.Lie');
+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+
1 row in set (0.00 sec)

WHERE子句使用Soundex()函数来转换cust_ contact列值和搜索串为它们的SOUNDEX值。因为Y.Lee和Y.Lie发音相似，所以它们的SOUNDEX值匹配，因此WHERE子句正确地过滤出了所需的数据。

日期时间处理函数

首先需要注意的是MySQL使用的日期格式。无论你什么时候指定一个日期，不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd。

⚠️对于具有时间值00:00:00的日期，比较的时候应使用Date（）函数提取日期部分比较。

比如，存储的 order_date 值为2005-09-01 11:30:05，则WHERE order_date = '2005-09-01'失败。即使给出具有该日期的一行，也不会把它检索出来，因为WHERE匹配失败。

mysql> select cust_id,order_num,order_date from orders where order_date = '2022-12-26';
Empty set (0.00 sec)
mysql> select cust_id,order_num,order_date from orders where Date(order_date) = '2022-12-26';
+---------+-----------+---------------------+
| cust_id | order_num | order_date          |
+---------+-----------+---------------------+
|   10001 |     20010 | 2022-12-26 16:50:54 |
+---------+-----------+---------------------+
1 row in set (0.00 sec)

🐭🐭看日期也知道，这条数据是我自己插入的。嗯。。。

数值处理函数

聚集函数

avg(),max(),min(),sum()函数忽略列值为NULL的行。
如果指定列名，则指定列的值为空的行被COUNT()函数忽略，但如果COUNT()函数中用的是星号（*），则不忽略。

mysql> select count(*) from customers;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)
mysql> select count(cust_email) from customers;
+-------------------+
| count(cust_email) |
+-------------------+
|                 3 |
+-------------------+
1 row in set (0.00 sec)

聚集函数+distinct

对于聚集函数来说,all是默认参数(全部数据都会参与计算,包含重复值)
可使用distint去重

mysql> select avg(prod_price) from products where vend_id =1003;
+-----------------+
| avg(prod_price) |
+-----------------+
|       13.212857 |
+-----------------+
1 row in set (0.00 sec)
#使用distinct后平均值变高了,去除了重复的较低价格
mysql> select avg(distinct prod_price) from products where vend_id =1003;
+--------------------------+
| avg(distinct prod_price) |
+--------------------------+
|                15.998000 |
+--------------------------+

1rowinset (0.00 sec)

DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)
DISTINCT必须使用列名，不能用于计算或表达式

分组

创建分组

mysql> select vend_id,count(*) from products group by vend_id;
+---------+----------+
| vend_id | count(*) |
+---------+----------+
|    1001 |        3 |
|    1002 |        2 |
|    1003 |        7 |
|    1005 |        2 |
+---------+----------+
4 rows in set (0.01 sec)

GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。

PS：

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

WITH ROLLUP：在group分组字段的基础上再进行统计数据。

mysql> select vend_id,count(*) from products group by vend_id with rollup;
+---------+----------+
| vend_id | count(*) |
+---------+----------+
|    1001 |        3 |
|    1002 |        2 |
|    1003 |        7 |
|    1005 |        2 |
|    NULL |       14 |
+---------+----------+
5 rows in set (0.01 sec)

统计count（*），最后一行的值 14=3+2+7+2

Mysql中的WITH ROLLUP用法 - 周伯通之草堂 - 博客园 (cnblogs.com)

过滤分组

MySQL提供了另外的子句，那就是HAVING子句。HAVING非常类似于WHERE。

mysql> select cust_id ,count(*) orders from orders group by cust_id having count(*)>2;
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      3 |
+---------+--------+
1 row in set (0.00 sec)

having和where区别

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

列出具有2个（含）以上、价格为10（含）以上的产品的供应商：

mysql> select vend_id,count(*) from products where prod_price>=10 group by vend_id having count(*) >=2;
+---------+----------+
| vend_id | count(*) |
+---------+----------+
|    1003 |        4 |
|    1005 |        2 |
+---------+----------+
2 rows in set (0.00 sec)

分组和排序

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

mysql> select order_num,sum(quantity*item_price) as ordertotal from orderite
ms group by order_num having ordertotal >=50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20005 |     149.87 |
|     20006 |      55.00 |
|     20007 |    1000.00 |
|     20008 |     125.00 |
+-----------+------------+
4 rows in set (0.00 sec)
mysql> select order_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having ordertotal >=50 order by ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+
4 rows in set (0.00 sec)

select语句顺序

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

子查询

子查询过滤

假如需要列出订购物品TNT2的所有客户：

检索包含物品TNT2的所有订单的编号。
select order_num from orderitems where prod_id = 'tnt2'
检索具有前一步骤列出的订单编号的所有客户的ID。
select cust_id from orders where order_num in 。。。
检索前一步骤返回的所有客户ID的客户信息。
select cust_name ,cust_contact from customers where cust_id in。。。。。

mysql> select cust_name ,cust_contact from customers where cust_id in 
  (select cust_id from orders where order_num in 
      (select order_num from orderitems where prod_id = 'tnt2'));
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.01 sec)

列必须匹配

在WHERE子句中使用子查询（如这里所示），应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。

作为计算字段

假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。

从customers表中检索客户列表。
select....from customers order by cust_name
对于检索出的每个客户，统计其在orders表中的订单数目。
select count(*) from orders where orders.cust_id =customers.cust_id

mysql> select cust_name,cust_state,
  (select count(*) 
      from orders 
      where orders.cust_id =customers.cust_id) as orders 
  from customers 
  order by cust_name;
+----------------+------------+--------+
| cust_name      | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      3 |
| E Fudd         | IL         |      1 |
| Mouse House    | OH         |      0 |
| Wascals        | IN         |      1 |
| Yosemite Place | AZ         |      1 |
+----------------+------------+--------+
5 rows in set (0.00 sec)

这条 SELECT 语句对 customers 表中每个客户返回 3 列：cust_name、cust_state和orders。orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在此例子中，该子查询执行了5次，因为检索出了5个客户。

where orders.cust_id =customers.cust_id

orders.cust_id是内部字段，customers.cust_id是外部字段，由外部传入。不加限定名会无法区分

盘点一下Mysql中的一些小知识（三）