20.子查询,连接

1.使用比较运算符的子查询

select ROUND(AVG(goods_price),2) from tdb_goods;//求平均值，并且四舍五入保留2位小数

  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 

表的参照关系：

-- 通过CREATE...SELECT来创建数据表并且同时写入记录  -- SELECT brand_name FROM tdb_goods GROUP BY brand_name;  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;

-- 通过tdb_goods_brands数据表来更新tdb_goods数据表(错误)  UPDATE tdb_goods  INNER JOIN tdb_goods_brands ON brand_name = brand_name  SET brand_name = brand_id;  -- Column 'brand_name' in field list is ambigous  -- 正确  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 as g inner join tdb_goods_cates as c on g.cate_id = c.cate_id

左外连接:显示左表全部记录以及右表符合连接条件的记录

select goods_id,goods_name,cate_name from tdb_goods as g left join tdb_goods_cates as c on g.cate_id = c.cate_id

右外连接：显示右表全部记录，以及左表符合条件的记录

select goods_id,goods_name,cate_name from tdb_goods as g right join tdb_goods_cates as c on g.cate_id = c.cate_id

---

三表连接查询：

-- 查询所有商品的详细信息(通过内连接实现)   SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g   INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id   INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;-- 查询所有商品的详细信息(通过左外连接实现)   SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g   LEFT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id   LEFT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;-- 查询所有商品的详细信息(通过右外连接实现)   SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g   RIGHT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id   RIGHT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

---