mysql两数据库交互_MySQL与数据库的交互2

----------求所有电脑产品的平均价格，并且保留两位小数

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;

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