SQL查询小案例

这是一篇自学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;