mysql的查询、子查询及连接查询分析(二)
以上不及格的学生
select name,sum(score<60) as gk from stu group by name having gk > 1;
#综合结果
select name,sum(score<60) as gk,avg(score) as pj from stu group by name having gk >1;
4、order by
（1） order by price //默认升序排列
（2）order by price desc //降序排列
（3）order by price asc //升序排列，与默认一样
（4）order by rand() //随机排列，效率不高
#按栏目号升序排列，每个栏目下的商品价格降序排列
select * from goods where cat_id !=2 order by cat_id,price desc;
5、limit
limit [offset,] N
offset 偏移量，可选，不写则相当于limit 0,N
N 取出条目
#取价格第4-6高的商品
select good_id,goods_name,goods_price from goods order by good_price desc limit 3,3; www.2cto.com
###查询每个栏目下最贵的商品
思路：
#先对每个栏目下的商品价格排序
select cat_id,goods_id,goods_name,shop_price from goods order by cat_id,shop_price desc;
#上面的查询结果中每个栏目的第一行的商品就是最贵的商品
#把上面的查询结果理解为一个临时表[存在于内存中]【子查询】
#再从临时表中选出每个栏目最贵的商品
select * from (select goods_id,goods_name,cat_id,shop_price
from goods order by cat_id,shop_price desc) as t group by cat_id;
from goods order by cat_id,shop_price desc) as t group by cat_id;
#这里使用group by cat_id是因为临时表中每个栏目的第一个商品
就是最贵的商品，而group by前面没有使用聚合函数，所以默认就取每个分组的第一行数据，
这里以cat_id分组
就是最贵的商品，而group by前面没有使用聚合函数，所以默认就取每个分组的第一行数据，
这里以cat_id分组
良好的理解模型：
1、where后面的表达式，把表达式放在每一行中，看是否成立
2、字段(列)，理解为变量，可以进行运算（算术运算和逻辑运算）
3、 取出结果可以理解成一张临时表
二、mysql子查询
1、where型子查询
（把内层查询结果当作外层查询的比较条件）
#不用order by 来查询最新的商品
select goods_id,goods_name from goods where goods_id =
(select max(goods_id) from goods);
(select max(goods_id) from goods);
#取出每个栏目下最新的产品(goods_id唯一)
select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);
2、from型子查询
(把内层的查询结果供外层再次查询)
#用子查询查出挂科两门及以上的同学的平均成绩
思路：
#先查出哪些同学挂科两门以上
select name,count(*) as gk from stu where score < 60
having gk >=2;
having gk >=2;
#以上查询结果，我们只要名字就可以了，所以再取一次名字
select name from (select name,count(*) as gk from stu
having gk >=2) as t;
having gk >=2) as t;
#找出这些同学了，那么再计算他们的平均分
select name,avg(score) from stu where name in
(select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;
(select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;
3、exists型子查询
（把外层查询结果拿到内层，看内层的查询是否成立）
#查询哪些栏目下有商品，栏目表category,商品表goods
select cat_id,cat_name from category where exists
(select * from goods where goods.cat_id = category.cat_id); www.2cto.com
(select * from goods where goods.cat_id = category.cat_id); www.2cto.com
三、union的用法
（把两次或多次的查询结果合并起来，要求查询的列数一致，
推荐查询的对应的列类型一致，可以查询多张表，多次查询语句时如果列名不一样，
则取第一次的列名！如果不同的语句中取出的行的每个列的值都一样，那么结果将自动会去重复，
如果不想去重复则要加all来声明，即union all）
推荐查询的对应的列类型一致，可以查询多张表，多次查询语句时如果列名不一样，
则取第一次的列名！如果不同的语句中取出的行的每个列的值都一样，那么结果将自动会去重复，
如果不想去重复则要加all来声明，即union all）
## 现有表a如下
id num
a 5
b 10
c 15
d 10
表b如下
id num
b 5
c 10
d 20
e 99
求两个表中id相同的和
select id,sum(num) from (select * from ta union select * from tb)
as tmp group by id;
as tmp group by id;
//以上查询结果在本例中的确能正确输出结果，但是，如果把tb中的b的值
改为10以查询结果的b的值就是10了，因为ta中的b也是10，所以union后会被过滤掉一个重复的结果，
这时就要用union all
改为10以查询结果的b的值就是10了，因为ta中的b也是10，所以union后会被过滤掉一个重复的结果，
这时就要用union all
select id,sum(num) from (select * from ta union all select * from tb)
as tmp group by id;
as tmp group by id;