这是一篇自学mysql的小案例,下面是部分数据信息:goods表
1、查询cate_name为‘超级本’的商品名称、价格
select
`name`,
price
from
goods
where cate_name like ‘超级本’;
— 2、查询商品的种类
select
cate_name
from
goods
group by
cate_name;
— 3、查询所有电脑产品的平均价格,并且保留两位小数
select
round(avg(price), 2) as avg_price
from
goods;
— 4、查询每种商品的平均价格
select
cate_name,
avg(price) as avg_price
from
goods
group by
cate_name;
— 5、查询每种商品中的最高价格、最低价格,平均价、数量
select
cate_name,
max(price) as ma_price,
min(price) as mi_price,
avg(price) as avg_price,
count(*)
from
goods
group by
cate_name;
— 6、查询所有价格大于平均价格的商品、并且按价格降序排列
select
id,
name,
cate_name,
brand_name,
price
from
goods
where
price > (
select
round(avg(price), 2)
from
goods
)
order by
price desc;
— 7、查询每种类型中最贵的电脑信息
select
*
from
goods
inner join (
select
cate_name,
max(price) as max_price,
min(price) as min_price,
count(*)
from
goods
group by
cate_name
) as goods_new_info on goods.cate_name = goods_new_info.cate_name
and goods.price = goods_new_info.max_price;
需要了解更多数据库技术:SQL查询小案例,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/823280.html