MySQL 子查询、多表更新/删除、JOIN、无限级分类表

如果显示汉字为乱码，把utf8改成gbk格式：SET NAMES gbk;
DESC tbl_name; // 查看数据表结构

聚合函数：

AVG 平均值
MAX
MIN
COUNT
SUM

【子查询Subquery】查询指 结构化查询语言，所有SQL命令的统称。

指出现在其他SQL语句内的SELECT 子句，例：
SELECT * FROM t1 WHERE col1 = ( SELECT col2 FROM t2 );

SELECT * FROM t1 : 外层声明（查询）Outer Query ，可以是SELECT, INSERT UPDATE, SET, DO。
SELECT col2 FROM t2 ：子查询，嵌套在查询内部的查询，必须在()内，可以包含DISTINCT/GROUP BY/ORDER BY/LIMIT等函数。

子查询的返回值：标量、一行、一列或子查询。可以被其他sql语句使用。

【子查询分类】

1. 使用比较运算符： =,>, <, >=, <=, <> ,!= ,<=>.

SELECT AVG(goods_price) FROM tdb_goods; // 求平均值
SELECT ROUND(AVG(goods_price), 2) FROM tdb_goods; // 四舍五入保留2位
SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price >= 4505.70;
合并：
SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price), 2) FROM tdb_goods);

用ANY, SOME, ALL修饰的比较运算符
ANY SOME符合任何一个条件就行
ALL符合全部条件
=ALL 不存在

SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price > ANY ( SELECT goods_price FROM tdb_goods WHERE );

2. 使用[NOT] IN 的子查询

!= ALL 、<>ALL 与NOT IN 等效。
SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price != ALL ( SELECT goods_price FROM tdb_goods WHERE );

3. [NOT] EXISTS

如果子查询返回任何行，EXISTS将返回TRUE; 否则为FALSE。用的比较少。

【使用INSERT...SELECT插入记录】

INSERT SET 可以使用子查询：INSERT...SET A字段= ...

INSERT...SELECT...将查询结果写入数据表：
INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate; // tdb_goods_cates包含cate_id和cate_name两列

【多表更新】将商品名改为商品id，数字占用的字节数小于汉字。

连接：一张表通过INNER JOIN, OUTER JOIN连接两张表。
连接类型：内连接、左外连接、右外连接。

参照分类表更新商品表：
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name
->SET goods_cate = cate_id;

CREATE...SELECT实现多表更新：创建品牌表的同时查询并写入
CREATE tdb_goods_brand(
-> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
->brand_name VARCHAR(40) NOT NULL)
->SELECT brand_name FROM tdb_goods GROUP BY brand_name;

参照品牌表更新品牌id:
UPDATE tdb_goods AS g INNER JOIN tdb_goods_brand AS b ON g.brand_name = b.brand_name
-> SET g.brand_name = b.brand_id;

修改tdb_goods的列名和格式：
ALTER TABLE tdb_goods
->CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
->CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

SHOW COLUMNS FROM tdb_goods;
DESC tdb_goods;

这种形式的外键称为【适时外键】

【连接】

INNER JOIN | LEFT OUTER JOIN | RIGHT JOIN ON 连接条件

给数据表起别名：tbl_name AS alias_name, alias_name.col_name
没有别名：tbl_name.col_name

FROM子句中的子查询必须用别名。

存储时使用id，显示时显示具体名称。

【INNER JOIN】JOIN, CROSS JOIN同义

仅显示左表与右表符合条件的记录。
SELECT goods_id, goods_name, cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id; // 显示两表中cate_id相同的部分

【外连接】

SELECT goods_id, goods_name, cate_name FROM tdb_goods LEFT OUTER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id; //显示左表的全部记录以及右表符合连接条件的记录

SELECT goods_id, goods_name, cate_name FROM tdb_goods RIGHT OUTER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

5-11【多表连接】

品牌、商品分类同时显示，多表连接实现的显示结果。

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;

注意两个INNER字段结束 没有逗号。
注意两个INNER字段结束 没有逗号。
注意两个INNER字段结束 没有逗号。

表的连接，外键的逆向操作，将多张表联系在一起。

 

 

【无限级分类表】表内存储一个父类id

CREATE TABLE tdb_goods_types(
     type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
     type_name VARCHAR(20) NOT NULL,
     parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
  );

查找：自身连接（同一个数据表对自身进行连接）

-- 查找所有分类及其父类
（只能查找一级父类，MySQL无法递归查询，可通过程序实现）
假设left table 为子类：
  SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;
   
-- 查找所有分类及其子类

  SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id;

-- 查找所有分类及其子类的数目

  SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;

-- 为tdb_goods_types添加child_count字段

  ALTER TABLE tdb_goods_types ADD child_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0;

【多表删除】

删除重复，一张表模拟两张表实现。

-- 查找重复记录

  SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2; // 分组条件用HAVING

-- 删除重复记录

  DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (
->SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2
->) AS t2
->ON t1.goods_name = t2.goods_name
->WHERE t1.goods_id > t2.goods_id;