MYSQL（四）

子查询：出现在其他SQL语句内的SELECT子句，潜逃在查询内部，切必须始终出现在圆括号内。

子查询可以返回标量，一行，一列或子查询。

求平均值，并将平均值四舍五入，保留两位小数：SELECT ROUND (AVG(goods_price),2) FROM tdb_goods;

获取商品价格大于平均价格的商品：SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>345.87;

合并：SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>(SELECT ROUND (AVG(goods_price),2) FROM tdb_goods);

创建商品分类表：CREATE TABLE IF NOT EXISTS tdb_goods_news(cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,cate_name VARCHAR(40) NOT NULL);

INSERT tdb_goods_news(cate_name) SELECT goods_cate FROM  tdb_goods GROUP BY goods_cate;

多表更新：

UPDATE tdb_goods INNER JOIN tdb_goods_news ON goods_cate = cate_name（两张表的连接条件） SET goods_cate = cate_id (更新的值);

创建数据表同时将查询结果写入到数据表：create...select

XREATE 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_news AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id;