使用比较运算符的子查询
普通查询
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
无限级分类表设计