子查询:
子查询是指出现在其他sql语句内的select子句。
select * from t1 where col1=(select col2 from t2);
select * from t1 称外层查询
select col2 from t2 称为子查询
子查询指嵌套在查询内部,且必须始终出现在圆括号内。
子查询可以包含多个关键字或条件:distinct、group by、order by 、limit等。
子查询可以返回标量、一行、一列、或子查询。
使用比较运算符的子查询:
AVG:是聚合函数
select avg(goods_priece) from tdb_goods;
select round(avg(goods_priece) ,2) from tdb_goods;
select goods_id,gods_name,goods_price from tdb_goods where goods_price>=5636.36;
合并:
select goods_id,gods_name,goods_price from tdb_goods where goods_price>=(select round(avg(goods_priece) ,2) from tdb_goods);
select goods_price from tdb_goods where goods_cate='超级本';
用any、some、all修饰的比较运算符:
any、some:符合其中的一个就行
all:符合里面的全部
由not in /exists引发的子查询:
=any 与 in等效
!=all 与 not in等效
如果子查询返回任何行,exists将返回true,否则为false。(用得较少)
tdb_goods_cates 分类表
select goods_cate from tdb_goods group by goods_cate;//商品表分了7类
insert...select 将查询结果写入数据表
insert tdb_goods_cates(cate_name) select good_cate from tdb_goods group by goods_cate;
多表的更新(重点):
inner join 内连接
left join 左连接
right join 右连接
update tdb_goods inner join tdb_goods_cates on goods_cate =cate_name set goods_cate=cate_id;
创建数据表同时将查询结果写入到数据表
select brand_name from tdb_goods group by brand_name;
select brand_name from tdb_goods group by brand_name;
update tdb_goods g inner join tdb_goods_brands b on g.brand_name =b.brand_name set brand_name =brand_id set g.brand_name =b.brand_id;
alter table tdb_goods change goods_cate cate_id smallint unsigned not null,
change brand_name brand_id smallint unsigned not null;
连接的语法结构:
内连接(在mysql中,inner join、cross join、join是等价的):
left 【outer】join:左外连接
right 【outer】join:右外连接
使用on关键字来设定连接条件,也可以使用where来代替
通常使用on关键字来设定连接条件,使用where关键字来进行结果集记录的过滤。
内连接:(显示2张表公共的部分)--用得比较多一些
select goods_id,goods_name,cate_name from tdb_goods inner join tdb_goods_cates on tdb_goods.cate_id=tdb_goods_cates.cate_id;
外连接:
多表连接(重点):
select goods_id,goods_name,cate_name,brand_name,goods_price from tdb_goods g
inner join tdb_goods_cates c on g.cate_id =c.cate_id
inner join tdb_goods_brands b on g.brand_id=b.brand_id;