MySQL筆記基于教材榆俺，筆記中用到的數(shù)據(jù)庫(kù)可以點(diǎn)擊該鏈接下載
常用的文本處理函數(shù)
SOUNDEX值相等意味著字符串的發(fā)音相同。
常用日期和時(shí)間處理函數(shù)
常用的數(shù)值處理函數(shù)
聚集函數(shù)
聚集函數(shù)處理一組數(shù)據(jù)价匠，匯總后返回單個(gè)值。
-
AVG()函數(shù)只處理作為參數(shù)的單個(gè)列呛每，且忽略NULL - 不指定列的
COUNT(*)對(duì)所有行計(jì)數(shù)踩窖，不管各行有什么值；而指定列COUNT(column)對(duì)特定行技術(shù)晨横，忽略NULL - MySQL5.0.3后的版本支持對(duì)數(shù)值處理函數(shù)加上
ALL或DISTINCT限定洋腮，默認(rèn)為ALL。如果限定為DISTINCT颓遏，則只處理不重復(fù)的值
使用SELECT的GROUP BY子句分組數(shù)據(jù)
GROUP BY子句允許對(duì)數(shù)據(jù)匯總前按照邏輯分組
下面的例子按照vend_id分組后對(duì)每組計(jì)算行數(shù)徐矩。
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的一些規(guī)定：
使用SELECT的HAVING子句過(guò)濾分組
對(duì)分組篩選前
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)
對(duì)分組篩選后
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運(yùn)行在分組前對(duì)行篩選滞时，而HAVING運(yùn)行在分組后對(duì)分組篩選叁幢。在寫法上，WHERE需要寫在GROUP BY前坪稽，而HAVING需要寫在GROUP BY后曼玩。
下面的例子篩選出了具有2個(gè)（含）以上、價(jià)格為10（含）以上的產(chǎn)品的供應(yīng)商
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時(shí)不要忘記使用ORDER BY對(duì)數(shù)據(jù)排序
以下是GROUP BY和ORDER BY的區(qū)別
用例子說(shuō)明它們的區(qū)別
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按總計(jì)訂單額排序
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系列筆記
