-- 创建 "名贵男女士手表折扣价" 数据库

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,'楠楠笔记本','笔记本','男总','4999',default,default);