mysql必知必会第12~14章笔记

#第12章: 汇总数据

聚集函数

mysql提供一些聚集函数用来汇总数据,比如返回表中一个有多少行,表中某列的的平均值,最大值等.
mysql聚集函数:

函数	说明
MAX(列名)	返回该列的最大值,如果是字符串返回按字典排序最后的那个,忽略NULL值
MIN(列名)	返回该列的最小值,如果是字符串返回按字典顺序第一个,忽略NULL值
SUM(列名)	返回该列的总和,如果是字符串类型,语法可以通过但返回0,忽略NULL值
AVG(列名)	返回该列的平均值,如果是字符串类型,语法可以通过但返回0,忽略NULL值
COUNT(*)	返回表的总行数,不忽略NULL值
COUNT(列名)	返回表的总行数,如果该列为NULL,则忽略

例:返回orderitems表中的总行数,order_price的平均值,最大值,最小值,平均值和总和

SELECT COUNT(*) AS row_num ,
AVG(item_price) AS item_price_avg,
MAX(item_price) AS item_price_max,
MIN(item_price) AS item_price_max,
SUM(item_price) AS item_price_sum
FROM orderitems;

结果:

+---------+----------------+----------------+----------------+----------------+
| row_num | item_price_avg | item_price_max | item_price_max | item_price_sum |
+---------+----------------+----------------+----------------+----------------+
|      11 |      12.904545 |          55.00 |           2.50 |         141.95 |
+---------+----------------+----------------+----------------+----------------+

DISTINCT在聚集函数中的使用

mysql5及以后版本支持在聚集函数中加入DISTINCT关键字来对聚集函数中的列去重
products表中vend_id=1003的数据:

+---------+---------+----------------+------------+-------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                       |
+---------+---------+----------------+------------+-------------------------------------------------+
| FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)            |
| TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                          |
| SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                        |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)           |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                     |
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                      |
+---------+---------+----------------+------------+-------------------------------------------------+

可以看到,prod_price中的数据有重复的,下面的查询语句中将把表中prod_price重复的行只算一次,也就是去重再求平均值

SELECT AVG(DISTINCT prod_price) AS avg_price 
FROM products 
WHERE vend_id=1003;

结果:

+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+

注意:在聚集函数中,DISTINCT不能用于COUNT(*)聚集函数,否则会报错,COUNT(列名) 函数可以.

第13章:分组数据

创建分组

分组由GROUP BY 子句来完成看一个例子:

SELECT vend_id, COUNT(*) AS num_prods
FROM products GROUP BY vend_id;

上面的语句将products表中的数据按vend_id 列进行分组,返回vend_id的值和总数

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

vend_id=1001的行有3行
vend_id=1002的行有2行 ,以此类推...

GROUP BY子句的注意事项

GROUP BY 子句可以包含任意数目的列,这说明GROUP BY 子句可以对分组进行嵌套,为数据分组提供更细致的控制
如果GROUP BY 子句中嵌套了分组,数据将在最后规定的分组进行汇总.换句话说,在建立分组是,指定的列都一起计算(不能从各别的列取回数据)
GROUP BY 子句后面列出的每个列必须是检索列或有效的表达式(但不能是聚集函数),如果在SELECT 中使用表达式,如果还想在GROUP BY 子句中使用,应该写相同的表达式,不能使用别名
除聚集计算外,SELECT语句中的每个类必须在GRUOP BY 子句中给出
如果分组列中有NULL值,将作为NULL返回,如果列中有多行NULL值,NULL将归为一组返回
如果WHERE ,GROUP BY ,ORDER BY 子句共同出现,GROUP BY子句要在WHERE 之后,ORDER BY之前

WITH ROLLUP关键字

使用WITH ROLLUP关键字可以得到分组的汇总数据
不用WITH ROLLUP

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 |
+---------+-----------+

使用WITH ROLLUP

SELECT vend_id, COUNT(*) AS num_prods
  FROM products
  GROUP BY vend_id WITH ROLLUP;

结果

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
|    NULL |        14 |
+---------+-----------+

3+2+7+2=14

HAVING 过滤分组

用HAVING放在GROUP BY 的后面来过滤分组

SELECT vend_id , COUNT(*) AS  `count` 
FROM products
GROUP BY vend_id HAVING `count` >2;

上面语句的意思是:将products表按vend_id分组后将count(*)>2的行检索出来
结果:

+---------+-------+
| vend_id | count |
+---------+-------+
|    1001 |     3 |
|    1003 |     7 |
+---------+-------+

可以看到count的值都是大于2的
当然WHERE 和 HAVING 可以同时存在,可以认为WHERE在分组前进行过滤,HAVING在分组后进行过滤

SELECT vend_id ,COUNT(*) AS `count` 
FROM products 
WHERE prod_price>5.0 
GROUP BY vend_id HAVING `count`>2;

上面语句的意思是:在products表中把prod_price>5.0的行按vend_id进行分组,在将分组中count>2的行检索出来

+---------+-------+
| vend_id | count |
+---------+-------+
|    1001 |     3 |
|    1003 |     4 |
+---------+-------+

#第14章:子查询 mysql4.1引入了子查询,如果想使用子查询,mysql版本要在myslq4.1及更高

利用子查询进行过滤

SELECT * FROM products  WHERE prod_price >
								(SELECT AVG(prod_price) FROM products);

上面语句的意思是:在products表中将prod_price大于prod_price平均值的行检索出来

SELECT * FROM products WHERE prod_price IN
								(SELECT MAX(prod_price) FROM products GROUP BY vend_id);

上面语句的意思是:将products表按vend_id分组后,每个组prod_price最大的行的所有信息检索出来

作为计算字段使用子查询

SELECT cust_name,cust_state , (SELECT COUNT(*) FROM orders 
												WHERE orders.cust_id=customers.cust_id) AS orders
							   FROM 
									customers ORDER BY cust_name;

上面语句的意思是:将在orders表查询符合orders表cust_id等于customers表的cust_id的总行数作为一个字段,同 cust_name,cust_state 一并返回并按cust_name 升序排列