MySQL查询中的条件控制(where,group by,having,order by,limit,from,exists)

时间:2022-09-22 00:23:37

设有成绩表stu如下
姓名     科目      分数
张三     数学      90
张三     语文      50
张三     地理      40
李四     语文      45
李四     政治      45
王五     政治      30
要求:查询两门及两门以上不及格同学的平均分
select name,avg(score) from stu group by name;
#看每个人挂科情况
select name,score<60 from stu;
#计算每个人的挂科科目
select name,sum(score<60)from stu group by name;
select name,sum(score<60) as gk,avg(score) as pj from stu group by name having gk>=2;
************************order by**************************
select goods_id,cat_id,goods_name,shop_price from goods where cat_id=3
#排序可以依据字段来排序
#升序和降序(默认是升序)
#降序desc
select goods_id,cat_id,goods_name,shop_price from goods where cat_id=3 order by shop_price desc;
#显式声明升序asc
select goods_id,cat_id,goods_name,shop_price from goods where cat_id=3 order by cat_id asc;
#按栏目编号排序
select goods_id,cat_id,goods_name,shop_price from goods where cat_id<>3 order by cat_id;
#按栏目编号,网店价格排序
select goods_id,cat_id,goods_name,shop_price from goods where cat_id<>3 order by cat_id,shop_price desc;
#也就是说,当只用一个条件不能达到排序的目的的时候,就用多个条件进行排序。
#order by可以按多字段排序,order by 列1[asc/desc],列2[desc/asc]
#按发布时间由早到晚排序
#add_time时间戳,升序,asc
#取价格最高的三个商品
select goods_id,goods_name,shop_price from goods order by shop_price desc;
*****************************Limit*****************************
Limit在语句的最后,起到限制条目的作用
limit[offset,][N]  offset:偏移量     N:取出条目
offset如果不写,则相当于limit 0,N表示跳过0行。
select goods_id,goods_name,shop_price from goods order by shop_price desc limit 3,3;
#查询每个栏目下最贵的商品
select goods_id,goods_name,shop_price from goods group by cat_id order by cat_id asc,shop_price desc;
#建一张临时表
create table g2 like goods;
insert into g2 select * from goods order by cat_id asc,shop_price desc;
select goods_id,goods_name,cat_id,shop_price from g2;
#清空g2
truncate g2
#不必构造表
select * from(select goods_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc) as temp group by cat_id;
select * from(select goods_id,goods_name,cat_id,shop_price from goods order by cat_id asc,shop_price desc) as temp group by cat_id;
列,当成变量来理解,查询结果当成表来理解。
良好的理解模式
where表达式,把表达式放在行中,看表达式是否为真
列:理解成变量,可以运算。
*****************************子查询********************************
where子查询
from子查询
exists子查询(以exists为主)
*****************where条件查询**********************
模糊查询:
想查找'诺基亚'开头的所有商品
like->模糊查询的关键字
'%'->通配任意字符
'_'->通配单个字符
*********************group分组***********************
5个聚合函数(sum,max,avg,min,count)与group的综合运用
max()聚合函数
select max(shop_price)from goods;
#shop_price是一个列的名字
先把所有的商品分成几个类,然后查询每个栏目下的最大值
select cat_id,max(shop_price)from goods group by cat_id;
select max(goods_id) from goods;
#查询最旧(最小)的商品编号
select min(good_id)from goods;
#求商品数总和
select sum(goods_number)from goods;
#本店商品的平均价格
select avg(shop_price)from goods;
#查询本网店共多少商品,也就是总共多少行
select count(*)from goods;
count()在分页查询的时候也可以用的到,共有多少条数据,平均每页显示20条,总条数除以20就是共有多少页
#按栏目查询最贵的商品
select cat_id,max(shop_price)from goods group by cat_id;
#按栏目查询价格最低的商品
select cat_id,min(shop_price)from goods group by cat_id;
#分栏目查询每个栏目的商品平均价格
select cat_id,avg(shop_price)from goods group by cat_id;
#每个栏目下的商品种类
select cat_id,count(*)from goods group by cat_id;
#要把列名当成变量名来看
#查询出本店每个商品比市场价格低多少
select good_id,good_name,market_price-shop_price from goods;
#要把列名当成变量名来看
#查询每个栏目下积压的货款
select cat_id,sum(shop_price*goods_number)from goods group by cat_id;
#可以给列或计算结果取别名,用as
select cat_id,sum(shop_price*goods_number) as hk from good group by cat_id;
******************************having子句*****************************
#查询出本店价格比市场价格低多少钱,并且把低于200元以上的商品查出来
select goods_id,goods_name,market_price-shop_price as sheng from goods;
select goods_id,goods_name,market_price-shop_price as sheng from goods where market_price-shop_price>200;
#having
select goods_id,goods_name,market_price-shop_price as sheng from goods having sheng>200;
#一个查询中既有where又有having
select goods_id,cat_id,market_price-shp_price as sheng from where cat_id=3 having sheng>200;
#查询积压货款超过2W的栏目,以及该栏目积压的货款
select cat_id sum(shop_price*goods_number) as hk from goods by cat_id;
#查询积压货款超过2W的栏目
#再用having
select cat_id,sum(shop_price*goods_number) as hk from goods group by cat_id having hk>200;