【MySQL笔记】3 MySQL函数与SELECT的更多功能
2018-02-22 本文已影响0人
shawn233
MySQL笔记基于教材，笔记中用到的数据库可以点击该链接下载
常用的文本处理函数
SOUNDEX值相等意味着字符串的发音相同。
常用日期和时间处理函数
常用的数值处理函数
聚集函数
聚集函数处理一组数据，汇总后返回单个值。
-
AVG()函数只处理作为参数的单个列，且忽略NULL - 不指定列的
COUNT(*)对所有行计数，不管各行有什么值；而指定列COUNT(column)对特定行技术，忽略NULL - MySQL5.0.3后的版本支持对数值处理函数加上
ALL或DISTINCT限定，默认为ALL。如果限定为DISTINCT，则只处理不重复的值
使用SELECT的GROUP BY子句分组数据
GROUP BY子句允许对数据汇总前按照逻辑分组
下面的例子按照vend_id分组后对每组计算行数。
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 |
+---------+-----------+
4 rows in set (0.01 sec)
以下是GROUP BY的一些规定：
使用SELECT的HAVING子句过滤分组
对分组筛选前
mysql> SELECT cust_id, COUNT(*) AS orders
-> FROM orders
-> GROUP BY cust_id;
+---------+--------+
| cust_id | orders |
+---------+--------+
| 10001 | 2 |
| 10003 | 1 |
| 10004 | 1 |
| 10005 | 1 |
+---------+--------+
4 rows in set (0.00 sec)
对分组筛选后
mysql> SELECT cust_id, COUNT(*) AS orders
-> FROM orders
-> GROUP BY cust_id
-> HAVING COUNT(*) >= 2;
+---------+--------+
| cust_id | orders |
+---------+--------+
| 10001 | 2 |
+---------+--------+
1 row in set (0.00 sec)
HAVING与WHERE在于WHERE运行在分组前对行筛选，而HAVING运行在分组后对分组筛选。在写法上，WHERE需要写在GROUP BY前，而HAVING需要写在GROUP BY后。
下面的例子筛选出了具有2个（含）以上、价格为10（含）以上的产品的供应商
mysql> SELECT vend_id, COUNT(*) AS num_prods
-> FROM products
-> WHERE prod_price >= 10
-> GROUP BY vend_id
-> HAVING COUNT(*) >= 2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1003 | 4 |
| 1005 | 2 |
+---------+-----------+
2 rows in set (0.01 sec)
在使用GROUP BY时不要忘记使用ORDER BY对数据排序
以下是GROUP BY和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.00 sec)
使用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 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子句顺序回顾
MySQL系列笔记
