02 计算

2.1 创建计算字段

计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式,我们需要的是转化、计算或者格式化之后的数据。

拼接字段

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.01 sec)

别名

别名有时也称导出列(derived column)

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.01 sec)

2.2 使用数据处理函数

函数类型

大多数SQL实现支持一下几种函数

  • 处理文本

  • 处理日期

  • 处理数值

  • 返回DBMS使用的特殊信息(如用户信息、版本信息)

文本处理函数

下面是部分文本处理函数的说明

函数

说明

Left()

返回串左边的字符

Right()

返回串右边的字符

Locate()

找出一个串的子串

Lower()

转换为小写

Upper()

转换为大写

Soundex()

返回串的SOUNDEX值

Soundex可以看做是按发音的近似来匹配

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.33 sec)

日期处理函数

如果是比较日期,不考虑小时和分钟的话,一定要使用Date(),不然小时和分钟也会一起比较。mysql中的标准日期格式是yyyy-mm-dd。

mysql> SELECT cust_id, order_num
-> FROM orders
-> WHERE Date(order_date) = '2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
+---------+-----------+
1 row in set (0.00 sec)
mysql> SELECT cust_id, order_num
-> FROM orders
-> WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10003 | 20006 |
| 10004 | 20007 |
+---------+-----------+
3 rows in set (0.01 sec)

如果不清楚是否是闰年,或每个月多少天,也可以用Year()或Month()直接指定年和月

mysql> SELECT cust_id, order_num
-> FROM orders
-> WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10003 | 20006 |
| 10004 | 20007 |
+---------+-----------+

2.3 汇总数据

有些情况下我们需要汇总数据,而不是把数据实际检索出来。这时候就需要使用聚集函数(aggregate function)

AVG()函数

注意AVG会忽略值为NULL的行。

mysql> SELECT AVG(prod_price) AS avg_price
-> FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+

还可以加上WHERE条件

mysql> SELECT AVG(prod_price) AS avg_price
-> FROM products WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+
1 row in set (0.01 sec)

COUNT()函数

两种使用方式:

  • COUNT(*):不管是不是NULL,都会统计

  • COUNT(column):统计具体的列,会忽略NULL

mysql> SELECT COUNT(*) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
| 5 |
+----------+

下面统计单个列,忽视NULL。

mysql> SELECT cust_email FROM customers;
+---------------------+
| cust_email |
+---------------------+
| ylee@coyote.com |
| NULL |
| rabbit@wascally.com |
| sam@yosemite.com |
| NULL |
+---------------------+
mysql> SELECT COUNT(cust_email) AS num_cust
-> FROM customers;
+----------+
| num_cust |
+----------+
| 3 |
+----------+

MAX()函数

max和min一般用于数值或日期类型

mysql> SELECT MAX(prod_price) AS max_price
-> FROM products;
+-----------+
| max_price |
+-----------+
| 55.00 |
+-----------+

MIN()函数

mysql> SELECT MIN(prod_price) AS min_price
-> FROM products;
+-----------+
| min_price |
+-----------+
| 2.50 |
+-----------+

SUM()函数

sum可以直接计算指定列的和,也可以合计计算值

mysql> SELECT SUM(quantity) AS items_ordered
-> FROM orderitems
-> WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
| 19 |
+---------------+
mysql> SELECT SUM(item_price*quantity) AS total_price
-> FROM orderitems
-> WHERE order_num = 20005;
+-------------+
| total_price |
+-------------+
| 149.87 |
+-------------+

聚集不同的值

实际上上面五个函数都默认有一个ALL参数。我们还可以指定DISTINCT来聚集不同值。下面的语句值计算了不同值的价格的平均值。

mysql> SELECT AVG(DISTINCT prod_price) AS avg_price
-> FROM products
-> WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+

DISTINCT后面要加列名,因此不能用于COUNT(*)。列外min和max本来就只选出一个,用DISTINCT没有意义。

组合聚集函数

前面我们都是在单个使用聚集函数,当然我们也可以把它们组合起来使用

mysql> 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;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
| 14 | 2.50 | 55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+

2.4 数据分组

上一节中我们是把数据直接拿来聚集。分组则允许我们把数据进行分组,以便可以对每个分组分别进行聚集计算。

创建分组 GROUP BY

创建分组使用group by子句,下面的例子中,数据按vend_id分成了4组,每组分别有3,2,7,2个记录。

mysql> SELECT vend_id, COUNT(*) AS num_prods
-> FROM products
-> GROUP BY vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+-----------+

过滤分组 HAVING

  • WHERE过滤行,HAVING过滤分组。

  • WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。WHERE排除的行不包括在HAVING中。

  • HAVING支持所有WHERE操作符。

mysql> SELECT cust_id, COUNT(*) AS orders
-> FROM orders
-> GROUP BY cust_id;
+---------+--------+
| cust_id | orders |
+---------+--------+
| 10001 | 2 |
| 10003 | 1 |
| 10004 | 1 |
| 10005 | 1 |
+---------+--------+
mysql> SELECT cust_id, COUNT(*) AS orders
-> FROM orders
-> GROUP BY cust_id
-> HAVING COUNT(*) >= 2;
+---------+--------+
| cust_id | orders |
+---------+--------+
| 10001 | 2 |
+---------+--------+

分组后再排序 ORDER BY

mysql> SELECT order_num, SUM(quantity*item_price) AS ordertotal
-> FROM orderitems
-> GROUP BY order_num
-> HAVING SUM(quantity*item_price) >= 50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20005 | 149.87 |
| 20006 | 55.00 |
| 20007 | 1000.00 |
| 20008 | 125.00 |
+-----------+------------+
4 rows in set (0.01 sec)
mysql> SELECT order_num, SUM(quantity*item_price) AS ordertotal
-> FROM orderitems
-> GROUP BY order_num
-> HAVING SUM(quantity*item_price) >= 50
-> ORDER BY ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20006 | 55.00 |
| 20008 | 125.00 |
| 20005 | 149.87 |
| 20007 | 1000.00 |
+-----------+------------+

SELECT子句总结

子句

是否必须使用

SELECT

FROM

在从表中选择数据时使用

WHERE

GROUP BY

在按组计算聚集时使用

HAVING

ORDER BY

LIMIT