MySql简单入门(三)

接着上篇继续回顾

9>SQL中的聚合函数

聚合函数(聚集函数)：多个数据进行运算，运算出一个结果。例如，求记录数，求和，平均值，最大值，最小值。

作用：做统计使用的。 例如：可以统计班级的平均分。

SQL语言中定义了部分的函数，可以帮助我们完成对查询结果的计算操作：

count(数目) : 统计个数（行数）,统计有多少行，或者有多少条数据。

sum(和)函数：求和。

avg(均值)函数：求平均值。

max(最大值)：求最大值。

min(最小值)：求最小值。

1.count函数--统计记录数(统计行数)

语法：select count(*)或者count(具体列名) from 表名;

注意：

1.count(具体列名)：在根据指定的列统计的时候，如果这一列中有null的行，该行 不会被统计在其中。按照列去统计有多少行数据。

2、select count(列名) from 表名:按具体列来进行统计行数。

      select count(*) from 表名: 统计表中的行数。

需求：

一、统计一个班级共有多少学生？

select count(*) from student;

二、统计成绩大于80的学生有多少个？

select count(score) from student where score>80;

 

2.sum函数(和)

语法：select sum(列名) ,sum(列名) ,......from 表名;

注意事项：

1、如果使用sum 多列进行求和的时候，如果某一列中含有null，这一列所在的行中的其他数据不会被加到总和中。

需求：

1、统计一个班级成绩和。

select sum(score) from student;

2、分别统计年龄和, 成绩和。

select sum(score),sum(age) from student;

3.统计年龄与成绩的总和值。

方式一：

方式二：

表中的所有数据：

我们发现按照方法二的做法，结果是有问题的。结果少了84。

产生问题的原因：

上述写法会先将每一行的年龄值和分数值进行相加，然后再把每一行的年龄值和分数值相加后的值进行求和。

 ·这样写会出现一个问题，因为在mysql中null值和任何值相加为null,导致在进行柳岩的年龄和分数相加的时候，柳岩的年龄和分数和值就变为了null。而最后sum求和的时候，就把柳岩的年龄和分数和值null给排除，因此最后的和值会缺少柳岩的年龄和分数和值84。

解决方案：在sql语句中我们可以使用数据库提供的函数ifnull(列名, 默认值)来解决上述问题。

ifnull(列名, 默认值)函数表示判断该列名是否为null,如果为null，返回默认值，如果不为null，返回实际的值。

例子：

age 的值是null

         ifnull(age,0) ====age列的值是null，返回值是 0

age 的值是3

    ifnull(age,0) ===== age列的值不是null，返回实际值是3

关于上述结果仍然有一个小的问题，就是小数点后面的内容比较多，如果我们希望保留具体的几位小数，我们可以使用

数据库中的函数：truncate(列名, 截取的小数位) 表示截取的意思，保留几位小数。

 

3.avg函数(平均值)

语法： select avg(列名) from 表名;

注意：求某列的平均值，avg里面的null不作为统计。

需求：求一个班级平均年龄。

 

4.max，min函数(最大，最小)

max(列) /min(列)  统计该列的最大值或者最小值。

select max(列名),min(列名) from 表名;

注意：null 排除在外。

需求：求班级最高分和最低分

 5.group by分组函数（★★★★★）

官方定义：

分组： 按照某一列或者某几列。把相同的数据，进行合并输出。

完整写法：

select … from … group by 列名，列名

按照某一列进行分组：

目的：仍然是统计使用。

分组生活举例：假设去超市买东西，我买了肥皂、洗衣粉、洗衣液、苹果、香蕉、葡萄等。我们在计算总价格之前先对所买的商品进行分类计算价格，然后在相加计算总价格。比如我们先将肥皂、洗衣粉、洗衣液分为一组计算出价格，然后再将苹果、香蕉、葡萄分为一组计算出价格，最后将两组的价格相加就是商品的总价格。

说明：分组其实就是按列进行分类，然后可以对分类完的数据使用聚合函数进行运算。

 

上述数据表示纸巾和洗衣粉属于不同品牌，但是价格是一样的

注意：

1. 聚合函数：分组之后进行计算；
2. 通常 select后面的内容是被分组的列，以及聚合函数；

需求1：查询购买的每种商品的总价

结果：

 

说明：

通过查询的结果和原来的数据比较发现，虽然添加id之后可以查询出结果，但是对于纸巾商品来说有问题，查询结果显示id是1，而纸巾的结果的id除了1还有2，同是纸巾，牌子还不一样，所以查询结果有问题。

 

修改上述数据库表中的数据：

将纸巾id为2的price价格修改为20。

说明：先按照商品名字进行分组，然后再按照商品的价格进行分组。

需求2：查询每一种商品的总价大于30的商品，并显示总价。

select product from orders where sum(price)>30 group by product;

可是执行上述sql语句时，会报如下错误：

是因为上述的sql语句的使用有错误。

主要原因：在sql语句中的where 后面不允许添加聚合函数，添加就会报上述错误。

那么既然这里不能使用where来解决问题，但是我们还依然要进行过滤，所以在sql语句中，如果分组之后，还需要一些条件。

可以使用having条件，表示分组之后的条件，在having后面可以书写聚合函数。

关于 having 的用法解释：

having和group by 一起使用，having和where的用法一模一样，where怎么使用having就怎么使用，where不能使用的，having也可以使用，比如说where后面不可以使用聚合函数，但是在having后面是可以使用聚合函数的。

更改后：

注意：红框的标注，having要写在group by后面

答案是可以的，但是会有一些要求。

解释：上面的having可以用的前提是我已经筛选出了price字段，在这种情况下和where的效果是等效的，但是如果我没有select price 就会报错！！

(注意：这里书写 * 就表示筛选出来的包括price 字段。)

因为having是从筛选出来的字段再筛选，而where是从数据表中的字段直接进行的筛选的。

进一步解释上述说法：

使用where就可以查询出结果。

因为前面的select product,id ，并没有筛选出price。所以会报错。

总结：开发中建议上述情况下使用where，避免没有必要的错误。

3、where 后面的条件可以写在having 中，但是 having中的条件不一定能写在where中。

having 可以书写聚合函数 (聚合函数出现的位置： having 之后)

例如having中的 聚合函数（count，sum，avg，max，min），是不可以出现where条件中。

4、where 是在分组之前进行过滤的。having 是在分组之后进行过滤的。

 

select ... from ... where 条件1 ... gropu by ... having 条件2

执行顺序：

条件1 会先执行过滤

条件2 先进行分组后进行过滤

开发中什么情况下使用分组？小技巧。

当在需求中遇到每种，每个等字眼的时候就使用分组。

注意：

1.group by 比order by先执行，order by不会对group by 内部进行排序，如果group by后只有一条记录，那么order by 将无效。

2.如果要查出group by中最大的或最小的某一字段，那么就要使用 max或min函数。

select sum(price) ,max(update_time) ,count(*)  from  product_1 where productid =1 group by product order by update_time desc