MySQL学习(六)

1 注意
select cout(*) from 表名； 查询的就是绝对的行数，哪怕某一列所有字段全部为NULL，也计算在内。而select cout(列名) form 表名；查询的是该列不为null的所有行的行数。

用count(*）和count(1)那个更好？

对于myisam引擎的表，没有区别，这种引擎内部有一个计数器在维护着行数，对Innodb的表，用count(*)直接读行数，效率很低，因为Innodb的表真的要去数一遍。

2

mysql> select sum(shop_price) from-> goods-> where cat_id = 3;

mysql> select sum(goods_number) from goods-> where cat_id = 4;

计算每个栏目下的库存量之和
配合group语句使用

mysql> select cat_id, sum(goods_number) from goods-> group by cat_id;

典型错误

mysql> select goos_id,sum(goods_number) from goods;ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test_db.goods.goos_id'; this is incompatible with sql_mode=only_full_group_by

运算不对应，没办法取出goos_id(MySQL 8.0.13 这条语句不会执行)。

3
按照cat_id分组，计算每个栏目下的商品的平均价格

mysql> select cat_id,avg(shop_price) from goods-> group by cat_id;

这里cat_id已经按组分类，所有语义上没有问题。
严格讲，以group by a, b,c为列，则select的列，只能在a,b,c中选择。

4
查询本店价格比市场价省的钱，并且要求省钱200元以上取出来

mysql> select goos_id,goods_name,(shop_price-market_price) as discount from goods-> where-> (shop_price - market_price) > 200;

或

mysql> select goos_id,goods_name,(shop_price-market_price) as discount from goods-> having discount;

这是典型错误

mysql> select goos_id,goods_name,(shop_price-market_price) as discount from goods-> where-> discount > 200;ERROR 1054 (42S22): Unknown column 'discount' in 'where clause'

这种写法也可以(P20)

mysql> select goos_id,goods_name,(shop_price-market_price) as discount from goods-> where 1-> having discount > 200;

5
查询每个商品所积压的货款

mysql> select goos_id,goods_number * shop_price from goods;

6
查询每个商品的积压货款之和

mysql> select sum(goods_number * shop_price) from goods;

7
查询每个栏目下，积压的货款(需要用到分组)

mysql> select cat_id,sum(shop_price * goods_number) from goods-> group by cat_id;

8
查询积压货款超过2W元的栏目，以及该栏目积压的货款

mysql> select cat_id,sum(goods_number * shop_price) as k from goods-> group by cat_id-> having k > 20000;

对结果集操作，就需要用到having
错误做法

mysql> select cat_id, sum(goods_number * shop_price) from goods-> where-> (goods_number * shop_price) >20000-> group by cat_id;

9
查询本店价比市场价省的钱，且筛选出省钱200以上的商品，用where和having分别来实现

mysql> select goos_id,goods_name,(shop_price - market_price) as discount from goods-> having-> discount > 200;

或

mysql> select goos_id,goods_name,shop_price - market_price from goods-> where-> (shop_price - market_price) > 200;

10
where-having-group综合练习
创建一张表

mysql> create table result-> (-> name varchar(10),-> subject varchar(10),-> score tinyint unsigned-> )engine myisam charset utf8;

插入数据

mysql> insert into result-> values-> ('张三','数学',90),-> ('张三','语文',50),-> ('张三','地理',40),-> ('李四','语文',55),-> ('李四','政治',45),-> ('王五','政治',30);Query OK, 6 rows affected (0.40 sec)

要求：查询出2门及2门以上不及格者的平均成绩
如张三 （90+50+40）/3 = 60
李四 （55+45） / 2 = 50；
注意count(1)或者count(0)查找出来的都是当前表的列数
1 第一步就是查找所有人的平均分

mysql> select name,avg(score) from result-> group by name;

2 再想办法计算出每个人的挂科的情况

mysql> select name,subject,score,score < 60 as ungrade from result;

3
如上，挂科数就是ungrade的和

mysql> select name,avg(score),sum(score < 60 ) as ungread-> from result-> group by name;

4 已经出现结果集，再对结果集进行一次操作

mysql> select name,avg(score),sum(score < 60 ) as ungread-> from result-> group by name-> having ungread >= 2;

这一个思路是逆向思维，先查出所有人的平均，再筛选，如果正常的考虑，我们会这样做，先找出谁的挂科数>2,找到这些人，再求这些人的平均分。