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;

MySQL学习(六)

3

如上,挂科数就是ungrade的和

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

MySQL学习(六)

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

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

MySQL学习(六)

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

上一篇:1200: [HNOI2005]木梳 - BZOJ


下一篇:mysql 安装配置详解