mysql的select函数_【MySQL笔记】3 MySQL函数与SELECT的更多功能

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系列笔记