MySQL练习

MySQL学习阶段练习：

练习时所有SQL指令都是在windows 交互环境下完成;

MySQL一些常用的指令可参考：https://www.cnblogs.com/zhoucs/articles/13453360.html

创建商品信息表，进行系列基础操作；

逐步拆分为多个表，进行外键关联等操作

--创建超市数据库
create database market charset=utf8;

--使用超市数据库
use market;

--创建商品goods数据表
create table goods(
    id int unsigned primary key not null auto_increment,
    name varchar(100) not null,
    cate_name varchar(100) not null,
    brand_name varchar(100) not null,
    price decimal(10,3) not null default 0,
    is_show bit not null default 1,
    is_saleoff bit not null default 0
    );

--插入数据
insert into goods values(0,'r510vc 15.6英寸笔记本'，'笔记本'，'华硕',3399,default,default);
insert into goods values(0,'iphoneX'，'手机'，'苹果',7999,default,default);
insert into goods values(0,'小米10'，'手机'，'小米',5699,default,default);


--查询数据
select * from goods;

--查询超极本的商品名称、价格
select name,price from goods where cate_name='超极本';

--显示商品种类
select cate_name as 商品种类 from goods group by cate_name;

--求所有电脑产品的平均价格，并保留两位小数
select round(sum(price)/count(*),2) as '平均价' from goods;
select round(avg(price),2) as '平均价' from goods;

--显示每种商品的平均价格
select cate_name as 商品种类,round(sum(price)/count(*),2) as '平均价' from goods group by cate_name;
select cate_name as 商品种类,round(avg(price),2) as '平均价' from goods group by cate_name;

--查询每种类型的商品中最贵、最便宜、平均价、数量
select cate_name as '商品种类',
max(price) as '最贵',
min(price) as '最便宜',
round(avg(price),2) as '平均价',
count(*) as '数量' 
from goods group by cate_name;

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


--查询每种类型中最贵的电脑信息
--内连接查询
select g1.cate_name,g2.name,g2.price from (select cate_name,max(price) as max_price from goods group by cate_name) as g1
    inner join
    goods as g2
    on g1.cate_name=g2.cate_name and g1.max_price=g2.price;

--左连接查询
select g1.cate_name,g2.name,g2.price from (select cate_name,max(price) as max_price from goods group by cate_name) as g1
    left join
    goods as g2
    on g1.cate_name=g2.cate_name and g1.max_price=g2.price;
    
--创建“商品分类”表
--创建商品分类
create table if not exists goods_cates(
    id int unsigned primary key auto_increment，
    name varchar(30) not null);

--查询商品信息表中所有商品类型
select cate_name from goods group by cate_name;

--将商品信息表中的商品类型插入到goods_cate表中
insert into goods_cates(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;

--创建“商品品牌”表
--通过creat...select来创建数据表并且同时写入记录
create table if not exists goods_brands(
    id int unsigned primary key auto_increment,
    name varchar(40) not null )select brand_name as name from goods group by brand_name;

--同步表数据，通过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;

--修改表结构
alter table goods
change cate_name cate_id int unsigned not null,
change brand_name brand_id int unsigned not null;

--外键
--分别在goods_cates和goods_brands表中插入数据
insert into goods_cates(name) values ('路由器'),('交换机'),('网卡');
insert into goods_brands(name) values ('海尔'),('美的'),('神舟');

--在goods数据表中写入任意记录
insert into goods(name,cate_id,brand_id,price) values('LaserJet Pro P1739 黑白激光打印机',7,2,4355);
--插入的记录cate_id或brand_id不一定在goods_cates和goods_brands表中，可以外键关联验证是否存在

--查询所有商品的详细信息（通过内连接）
select g.name,c.name as cate_name,b.name as brand_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.brand_id=b.id;

--查询所有商品的详细信息（通过左连接）
select g.name,c.name as cate_name,b.name as brand_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.brand_id=b.id;

--外键：为了防止无效信息插入，可以在插入前判断类型或者品牌名称是否存在
--外键约束：对数据的有效性进行验证
--关键字：foreign key ，只有innodb数据引擎 支持外键约束
--对于已经存在的数据表，如何更新外键约束
--添加外键约束时报错，可能是存在不符合约束条件的数据，需要先删除数据，再添加外键

--给brand_id添加外键约束
alter table goods add foreign key(brand_id) references goods_brands(id);

--给cate_id添加外键约束
alter table goods add foreign key(cate_id) references goods_cates(id);

--在创建表数据时同时设置外键约束
create table goods (
id int unsigned primary key auto_increment,
name varchar(40) not null,
cate_id int unsigned,
brand_id int unsigned, 
price decimal(5,2) not null,
is_show bit default 1,
is_saleoff bit default 0,
foreign key(cate_id) references goods_cates(id),
foreign key(brand_id) references goods_brands(id));

--取消外键（因为外键约束会降低表更新的效率）
show create table goods;查出外键名称 goods_ibfk_1
alter table goods drop foreign key goods_ibfk_1；