-- 创建 "名贵男女士手表折扣价" 数据库
create database Aashford charset=utf8;
-- 使用 "名贵男女士手表折扣价" 数据库
use Aashford;
-- 删除表
drop table goods;
-- 查询类型cate_name为 '超级本' 的商品
select * from goods where cate_name = '超级本';
-- 查询类型cate_name为 '超级本' 的商品名称、价格
select name,price from goods where cate_name = '超级本';
-- 起别名
select name as 商品名称,price as 商品价格 from goods where cate_name = '超级本';
-- 显示商品的种类
select cate_name from goods;
select distinct cate_name from goods; -- 去重
select cate_name from goods group by cate_name; -- 分组,效率低一些
-- 查每个种类的商品名称
select cate_name,group_concat(name) from goods group by cate_name;
-- group by取到的比distinct取到的功能强
-- 求所有电脑产品的平均价格,并且保留两位小数
select round(avg(price),2) as avg_price from goods;
-- 显示每种商品的平均价格
select cate_name,avg(price) from goods group by cate_name;
-- 查询每种类型的商品中 最贵、最便宜、平均价、数量
select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name;
-- 查询所有价格大于平均价格的商品,并且按价格降序排序
-- 所有商品的平均价
select avg(price) from goods;
-- 所有价格大于平均价格的商品
select * from goods where price >(seelct avg(price) from goods);
-- 查询所有价格大于平均价格的商品,并且按价格降序排序
select id,name,price from goods
where price > (select round(avg(price),2) as avg_price from goods)
order by price desc;
-- 查询每种类型中最贵的电脑信息
select * from goods;
select cate_name,max(price) from goods group by cate_name
-- 相当于把上面两句连接起来查询
select * from goods
inner join
(
select
cate_name,
max(price) as max_price,
min(price) as min_price,
avg(price) as avg_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;
select g_new.cate_name,g.name,g.price
from (select cate_name,max(price) as max_price from goods group by cate_name) as g_new -- 括号里是左表,以左表为主
left join goods as g
on g_new.cate_name=g.cate_name
and g_new.max_price=g.price order by g_new.cate_name;
insert into goods values(0,default,default);