SQL语句强换演练
1、SQL语句的强化
查询类型为cate_name为”xxx“的商品名称及价格
select name,price from goods whrere cate_name = 'xxx'
2、显示商品的种类
select cate_name from goods group by cate_name
3、求商品的平均价格,并且保留两位小数
select round(avg(prrice),2) as avg_price frrom goods
4、显示商品中每种,类型中最贵,最便宜,平均价,数量
select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name
5、查询所有价格大于平均价格的商品,并且按照价格降序排列(desc为降序排列,默认升序)
select id,name,price from goods where price >(select round(avg(price),2) as avg_pricee from goods) order by desc;
同步数据,同步表数据,添加外键的前提都是已经创建完成被操作的表格(下边已经认为表goods,goods_brands,brands_name,goods_cates已经创建好)
同步数据
update goods as g inner join goods_brands as b on `在这里插入代码片`g.brand_name=b.name set g.brands_name=b.id
同步表数据
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,
change brand_name brand_id int unsigned not null;
添加外键
alter table goods add foreign key (brand_id) references goods_brands(id);
注意: goods 中的 cate_id 的类型一定要和 goods_cates 表中的 id 类型一致
取消外键
显示表格中的外键
show create table goods;
删除表格中添加的外键
alter table goods drop foreign key 外键名称;