----------求所有电脑产品的平均价格，并且保留两位小数
select round (avg(price),2) 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 > (select avg(price) from goods)
----- 查询所有价格大于平均价格的商品，并且按价格降序排序
select * from goods where price > (select avg(price) from goods) order by price desc;
------查询每种类型中最贵的电脑信息
select cate_name,max(price) from goods group by cate_name;
select * from goods;
-----查询每种类型中最贵的电脑信息
select cate_name,max(price) from goods group by cate_name;
select * from goods;
------相当于把上面的两句连接查询
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,'charry笔记本','笔记本','老王','4999',default,default);
-------创建 ‘商品分类’ 表--------
---创建商品分类表
create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null
);
--- 查询goods表中商品的种类
select cate_name from goods group by cate_name;
----将分组结果写入到goods_cates数据表
insert into goods_cate(name) select cate_name from goods group by cate_name;
---同步数据表
通过goods_cates数据表来更新goods表
update goods as g inner join goods_cates as c on g.cate_name = c.name set g.cate_name = c.id;
------插入外键
insert into goods_cates(name) values ('路由器') ,('交换机'),('网卡');
insert into goods (name,cate_name,brand_name,price)
value('打印机',12,3,'1234');
alter table goods
change cate_name cate_id int unsigned not null;
alter table goods add foreign key (cate_id)
references goods_cates(id);
delete from goods where id = 23;
insert into goods (name,cate_id,brand_name,price)
vaules('打印机',12,3,'1234');
