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

无限级分类表设计