Mysql学习02

语言目前总结：

表级子查询

• 分组：group by
• 平均价格：

select round(avg(price）,2) from goods;

• 查询所有价格大于平均价格的商品，并案价格降序排序：

select * from goods where price >(select round(avg(price),2)from goods) order by price desc;

• 查询类型name 为‘超极本’的商品名称，价格：

select name,price from goods where cate_name ='超极本'
- 显示商品的种类(distinct是区分的不重复内容）：select distinct cate_name from goods;

• 显示商品种类方式2：

select cate_name from goods group by cate_name;

• 显示每种类型的商品的平均价格：

select cate_name, max(price) as 最贵, min(price) as 最便宜，avg(price) as 平均价, count(*) as 总数量 
avg(price) from goods 
group by cate_name;

• 查询每种类型中最贵的商品：

select *
from goods inner join
(select cate_name, max(price) as 最贵, min(price) as 最便宜，avg(price) as 平均价, count(*) as 总数量 
avg(price) from goods 
group by cate_name) as info on goodscate_name = info.cate_name and goods.price =info.最贵;

行级子查询

= => in or => any

select * from goods where (cate_name,price) = in (select cate_name ,max(price) from goods group by cate_name);

创建商品分类表

 create table if not exists goods_cates(id int unsigned primary key auto_increment, name varchar(40) not null);

• 查询商品类别

 insert into goods_cates(name) select distinct cate_name from goods;

• 将good表的brand_name转化 select distinct cate_name from goods;

update tabel select colum =xx;
update goods as g inner join goods_cates as c on g.cate_name = c.name set g.cate_name = c.id;

• 更新字段名称和数据类型

alter table goods change cate_name cate_id int unsigned not null;

待updat 10.30