Mysql之子查询和连接

使用比较运算符的子查询

普通查询
select avg(goods_price) from tdb_goods;//返回平均值
select round(avg(goods_price),2) from tdb_goods;//返回平均值的两位小数
select goods_id from tdb_goods where goods_price >= 5636.36;

子查询
select goods_id from tdb_goods where goods_price >= (select round(avg(goods_price),2) from tdb_goods);

ANY SOME ALL

   SELECT goods_id,goods_name,goods_price FROM tdb_goods 

   WHERE goods_price >= ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')

   ORDER BY goods_price DESC;

INSERT …SELECT 插入记录

select goods_cate from tdb_goods group by goods_cate;
insert tdb_goods_cates(cate_name) select goods_cate from tdb_goods group by goods_cate;

多表更新

update tdb_goods inner join tdb_goods_cates on goods_cate = cate_name set goods_cate = cate_id;

一步创建
  CREATE TABLE tdb_goods_brands (

    brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

    brand_name VARCHAR(40) NOT NULL

  ) SELECT brand_name FROM tdb_goods GROUP BY brand_name;

UPDATE tdb_goods AS  g  INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name
  SET g.brand_name = b.brand_id;

连接

内连接：显示左表及右表符合连接条件的记录
select goods_id,goods_name,cate_name from tdb_goods inner join tdb_goods_cates on tdb_goods.goods_cate = tdb_goods_cates.cate_id;

外连接
select goods_id,goods_name,cate_name from tdb_goods right join tdb_goods_cates on tdb_goods.goods_cate = tdb_goods_cates.cate_id;
left:左表的全部
right:右表的全部

多表连接

   SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods AS g

   INNER JOIN tdb_goods_cates AS c ON g.goods_cate = c.cate_id

   INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_id\G

无限级分类表设计