一、准备数据
1、创建数据表
-- 创建 "创维" 数据库
create database cw charset=utf8;
-- 使用 "创维" 数据库
use cw;
2、插入数据
如果插入数据错误，想重新插入
二、SQL演练
1. SQL语句的强化
-- 查询所有类型cate_name为 '超级本' 的商品
select * from goods where cate_name='超级本';
-- 查询类型cate_name为 '超极本' 的商品名称、价格
select name as 商品名称,price as 商品价格 from goods where cate_name ='超级本';
-- 显示商品的种类
select distinct cate_name from goods;-- 去重 distinct：去重
select cate_name from goods group by cate_name;-- 分组（效率稍低、数据复杂用）
-- 查每个种类的商品名称（得用分组）
select cate_name,group_concat(name) from goods group by cate_name;
-- 所有商品的平均价
select avg(price)from goods;
-- 求所有电脑产品的平均价格,并且保留两位小数
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 * from goods where price>(select avg(price) from goods);
-- 查询所有价格大于平均价格的商品，并且按价格降序排序
select * from goods where price>(select avg(price) from goods) order by price desc;-- order by—— 排序 desc —— 降序
-- 查询每种类型中最贵的电脑信息
select cate_name,max(price) from goods group by cate_name;-- 每种类别的最高价
select * from goods;
-- 相当于把上面两句连接查询
-- 方法一：inner join
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;
-- 方法二：left join（既用到了连接查询，又用到了子查询 ）
select g_new.cate_name,g.name,g.price -- g_new.cate_name,g.name,g.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);
2. 创建 "商品分类"表
-- unsigned:无符号（非负数）
-- 创建商品分类表
create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null-- 类别名称 40字长度 不允许为空
);
-- 查询goods表中商品的种类
select cate_name from goods group by cate_name;
-- 将分组结果写入到goods_cates数据表
insert into goods_cates (name) select cate_name from goods group by cate_name;-- select前不能加values
3. 同步表数据
-- 通过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;
-- 更新goods表起别名g —— inner join goods_cates关联类别表 —— 给类别表起名c —— on后面相当条件 —— g.cate_name=c.name 关联条件商品表的类别名称=类别表名称 —— set g.cate_name=c.id设置商品表的类别名称=……
4.修改表结构
-- 查看 goods 的数据表结构,会发现 cate_name 和 brand_name对应的类型为 varchar 但是存储的都是数字
desc goods;
-- 通过alter table语句修改表结构（既改名字又改类型）change：重命名
alter table goods change cate_name cate_id int unsigned not null;-- 1 类别的名称cate_name重命名为cate_id
alter table goods change brand_name brand_id int unsigned not null;-- 2 类别的名称brand_name重命名为brand_id
5.外键
-- 分别在goods_cates和goods_brands类别表中插入记录
insert into goods_cates(name) values ('路由器'),('交换机'),('网卡');
insert into goods_brands(name) values ('路由器'),('交换机'),('网卡');
-- 查看 goods 的数据表结构,会发现 cate_name 和 brand_name对应的类型为 varchar 但是存储的都是数字
desc goods;
-- 通过alter table语句修改表结构（既改名字又改类型）change：重命名
alter table goods change cate_name cate_id int unsigned not null; -- 1 类别的名称cate_name重命名为cate_id
alter table goods change brand_name brand_id int unsigned not null;-- 2 类别的名称brand_name重命名为brand_id
-- 如何防止无效信息的插入,就是可以在插入前判断类型或者品牌名称是否存在呢? 可以使用之前讲过的外键来解决
-- 外键约束:对数据的有效性进行验证
-- 关键字: foreign key,只有 innodb数据库引擎 支持外键约束
-- 关联外键
-- 给alter table goods添加一个foreign key（外键）给cate_id设 —— references：关联 和goods_cates类别表的id关联
alter table goods add foreign key (cate_id) references goods_cates(id); -- 1
alter table goods add foreign key (brand_id) references goods_brands(id); -- 2
-- 删除id=23的
delete from goods where id=23;
-- 在 goods 数据表中插入商品
insert into goods (name,cate_id,brand_id,price)
values('LaserJet Pro P1606dn 黑白激光打印机', 12, 4,'1849');
-- 在实际开发中，很少使用外键约束，只因会极大的降低表更新的效率，十分影响sql的性能
-- CPU > 内存 > 固态硬盘 > 机械硬盘
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table goods;
-- 取消外键
alter table goods drop foreign key goods_ibfk_1;
alter table goods drop foreign key goods_ibfk_2;
