sql总结

强化例子

表结构

mysql> desc goods;

Field	Type	Null	Key	Default	Extra
id	int unsigned	NO	PRI	NULL	auto_increment
name	varchar(150)	NO		NULL
cate_name	varchar(40)	NO		NULL
brand_name	varchar(40)	NO		NULL
price	decimal(10,3)	NO		0.000
is_show	bit(1)	NO		b’1’
is_saleoff	bit(1)	NO		b’0’

查询语句执行顺序：
FROM -> WHERE -> GROUP BY和聚合函数 -> SELECT -> ORDER BY -> LIMIT

聚合函数不能用在where子句，见例1：

例1：查询所有价格大于 平均价格 的商品，并且按 价格降序 排序 order desc

# 错误写法
select price, name from goods where price > avg(price) order by price desc;
# 报错
ERROR 1111 (HY000): Invalid use of group function

# 正确写法：用子查询
# step1 查询平局价格(avg_price)
select avg(price) as avg_price from goods;
# step2 使用子查询
select * from goods where price>(select avg(price) as avg_price from goods) order by price desc;

原因分析：
Aggregate functions cannot be directly used in the WHERE clause because the WHERE clause filters rows before any aggregation takes place. (见上查询语句执行顺序)Aggregate functions are applied to groups of rows, so they are typically used in the HAVING clause to filter groups of rows after the aggregation has been performed.

例2：查询每种类型中最贵的电脑信息(难)

# step1 查找每种类型 中 最贵的 max_price 价格
select max(price) as max_price,cate_name from goods group by cate_name;

# step2 关联查询 inner join 每种类型 中最贵的物品信息
select * from goods
inner join
(select max(price) as max_price,cate_name from goods group by cate_name) as max_price_goods
on goods.cate_name=max_price_goods.cate_name and goods.price=max_price_goods.max_price;

实战案例

由于只存在商品信息一张表 其中既有商品信息又有分类名称和品牌名称。当把某个商品信息删除后，本不应该删除的<独立于商品信息的分类和品牌>等信息结果也随着删除商品信息而删除，那么此时存在删除异常。

如图我们创建两张表 商品种类表 商品品牌表 存储对应的信息 就不会出现删除异常了

如图我们创建两张表 商品种类表 商品品牌表 存储对应的信息 就不会出现删除异常了
现在不仅解决了删除异常，而且对于“Apple被华为收购”这样的情形也更方便解决了，只要修改goods_brands表的name=Apple的记录为name=华为即可

# 创建"商品分类"表

# 第一步	创建表 (商品种类表 goods_cates )

create table if not exists goods_cates(
    id int unsigned primary key auto_increment,
    name varchar(40) not null
);

# 第二步	同步 商品分类表 数据 将商品的所有 (种类信息) 写入到 (商品种类表) 中


# -- 按照 分组 的方式查询 goods 表中的所有 种类(cate_name)
select cate_name from goods group by cate_name;

insert into goods_cates(name) (select cate_name from goods group by cate_name);

# 第三步 同步 商品表 数据 通过 goods_cates 数据表来更新 goods 表

# -- 因为要通过 goods_cates表 更新 goods 表 所以要把两个表连接起来
select * from goods inner join goods_cates on goods.cate_name=goods_cates.name;	
 
# -- 把 商品表 goods 中的 cate_name 全部替换成 商品分类表中的 商品id ( update ... set )
update (goods inner join goods_cates on goods.cate_name=goods_cates.name) set goods.cate_name=goods_cates.id;

# 第四部 修改表结构

# -- 查看表结构(注意 两个表中的 外键类型需要一致)
desc goods;

# -- 修改表结构 alter table 字段名字不同 change,把 cate_name 改成 cate_id int unsigned not null
alter table goods change cate_name cate_id int unsigned not null;

值得一提的是

'''
这里设置更行的的其实是goods表的cate_name字段，而非连接的(goods inner join goods_cates on goods.cate_name=goods_cates.name)表的goods.cate_name字段
'''
update (goods inner join goods_cates on goods.cate_name=goods_cates.name) set goods.cate_name=goods_cates.id;