Rumah >pangkalan data >tutorial mysql >mysql 统计函数和group by
五个统计函数(单独使用,意义不大,经常和分组group by组合使用)
max 最大 select max(shop_price) from goods;
min 最小 select min(shop_price) from goods;
sum 求和 select sum(shop_price) from goods;
avg 求平均值 select avg(shop_price) from goods;
count 所有值得行数有多少行
count(*)绝对行数null也计算在内
除此之外count(列名),计算这一列非null的行数
count使用
mysql> select * from test8; +------+------+ | id | name | +------+------+ | 1 | lisi | | 2 | NULL | +------+------+ mysql> select count(*) from test8; +----------+ | count(*) | +----------+ | 2 | +----------+ mysql> select count(name) from test8; +-------------+ | count(name) | +-------------+ | 1 | +-------------+
查询类型为4的库存
select sum(goods_number) from goods where cat_id=4;
group by
统计一下每个类型分组下的库存
mysql> select cat_id,sum(goods_number) from goods group by cat_id; +--------+-------------------+ | cat_id | sum(goods_number) | +--------+-------------------+ | 2 | 0 | | 3 | 203 | | 4 | 4 | | 5 | 8 | | 8 | 61 | | 11 | 23 | | 13 | 4 | | 14 | 9 | | 15 | 2 | +--------+-------------------+
不是标准的sql语句,逻辑上解释不通(每个类别cat_id里有很多goods_name)
不推荐 select goods_name ,sum(goods_number) from goods group by cat_id;
解释:在select a/b中必须在group by a/b/c语意上才没有问题
技巧:查询语句理解上从后面的条件过滤开始,先理解过滤条件,再看前面的执行
以上就是mysql 统计函数和group by的内容,更多相关内容请关注PHP中文网(www.php.cn)!