MySQL指令联系随笔

1.查询类型 cate_name 为超极本的商品名称，价格
select name,price from goods where cate_name="超极本";

2.显示商品种类
select cate_name from goods group by cate_name

3.求所有电脑的平均价，并保留两位小数
select round(avg(price),2) as avg_price from goods;

4.显示每种商品的平均价格
select cate_name,avg(price) from goods group by cate_name;

5.查询每种类型商品中最贵，最便宜，平均价，数量
select cate_name,max(price),min(price),avg(price)，count(*) from goods group by cate_name;

6.查询所有价格大于平均价的商品，并且按价格降序排序
select id,name price from goods where price>(select round(avg(price),2) from goods) order by price desc;

7.查询每种类型中最贵的电脑信息
select * from goods inner jojin (select cate_name,max(price) as m,min(price) as min,avg(price) as a,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.m;

2.1创建商品分类表
create table if not exists goods_cates(id int unsigned primary key auto_increment,
name varchar(30) not null);

2.2查询goods表中商品的种类
select cate_name from goods group by cate_name;

2.3 将分组结果写入goods_cates数据表
insert into goods_cate(name) select cate_name from goods group by cate_name;

3.1同步数据表信息
通过goods_cates数据表来更新goods表
update goods as g inner join goods_cater as c on g.cate_name=c.name set g.cate_name=c.id;

4.1创建商品“品牌”表
通过create ... select 来创建数据表并且同时写入记录。
create tabie goods_brands (id int primary key auto_increment,name varchar(30) not null) select
brand_name as name from goods group by brand_name;
5.1同步数据
通过goods_brands数据表来更新goods数据表
update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand_name=b.id;

6.1修改数据表结构
查看goods表结构发现cate_name 和brand_name类型为varchar,但是存储都是数字int类型
alter table goods change cate_name cate_id int unsigned not null,change brand_name brand_id int unsigned not null;

7.1 外键
分别在goods_cates和goods_brands表中插入记录
insert into goods_cates(name) values ("路由器"),("交换机"),("网卡");
insert into goods_brands(name) valuse("海尔"),("神舟");

在goods表中插入任意记录
insert into goods (name,cate_id,brand_id,price) values ("打印机",12,4,"1894");

通过内连接查询所有商品信息
select g.id,g.name,c.name,b.name,g.price from goods as g inner join goods_cates as c on
g.cate_id=c.id inner join goods_brands as b on g.brands_id=b.id;
通过左连接查询所有商品信息
select g.id,g.name,c.name,b.name,g.price from goods as g left join goods_cates as c on g.cate_id=c.id
left join goods_brands as b on g,brands_id=b.id;

8.1 如何防止无效信息插入，在插入前判断类型或品牌名称是否存在，可以使用外键解决
外键约束：对数据的有效性进行验证
关键字：foreigh 只有innodb数据库引擎支持外键约束
--对已存在的表添加外键约束
alter table goods add foreigh key (brand_id) references goods_brands(id);

alter table goods add foreigh key (cate_id) references goods_cates(id);

--创建时添加外键约束（两表列的类型必须一致）
create table goods (id int primary key auto_increment not null,
name varchar(30) default " " ,
cate_id int unsigned,
brand_id int unsigned,
foreigh key (cate_id) references goods_cates(id),
foreigh key (brand_id) references goods_brands(id))；

8.2取消外键约束
获取外键约束名称
--show create table goods;

根据名称删掉外键约束
alter table goods drop foreigh key 外键名称；

在实际开发中，很少会用到外键约束，会极大降低更新效率