Mysql进阶技巧1-MySQL的多表关联与自连接

自连接

测试数据准备

CREATE TABLE `t2` (`id` int(11) NOT NULL,
  `gid` char(1) DEFAULT NULL,
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into t2 values (1,'A',31,6), (2,'B',25,83), (3,'C',76,21), (4,'D',63,56), (5,'E',3,17), (6,'A',29,97), (7,'B',88,63), (8,'C',16,22), (9,'D',25,43), (10,'E',45,28), (11,'A',2,78), (12,'B',30,79), (13,'C',96,73), (14,'D',37,40), (15,'E',14,86), (16,'A',32,67), (17,'B',84,38), (18,'C',27,9), (19,'D',31,21), (20,'E',80,63), (21,'A',89,9), (22,'B',15,22), (23,'C',46,84), (24,'D',54,79), (25,'E',85,64), (26,'A',87,13), (27,'B',40,45), (28,'C',34,90), (29,'D',63,8), (30,'E',66,40), (31,'A',83,49), (32,'B',4,90), (33,'C',81,7), (34,'D',11,12), (35,'E',85,10), (36,'A',39,75), (37,'B',22,39), (38,'C',76,67), (39,'D',20,11), (40,'E',81,36);

通过自连接查询每组 col2 最大的值;

-- 方法 1:
select * from t2 as a where not exists (select 1 from t2 where gid=a.gid and col2>a.col2); 
-- 1. select 1 from t2 where gid=a.gid and col2>a.col2 : select 就进入了隐式迭代, 同组中比当前 col2 大的就输出 1;
-- 2. 然后 not exists 来判断是否存在比当前 col2 大的, 如果不存在就返回 true; 返回 true 就输出当前 col2 这一列;
-- 3. 这里的 exists 与 not exists 是判断语句, 返回的是 true or false;

-- 方法 2:
select * from (select * from t2 order by gid,col2 desc) as t group by gid;
-- t2 按照 gid 和 col2 来降序排列, 然后 group 分组, 分组就取的是 frist row, 而 frist row 就是最大的值;
-- 乍看之下貌似不用自连接也可以搞定, 但是 group by 分组是不能放在 order by 之后的, 否则就会报错;

通过自连接查询每组 col2 最大的三个值;

select * from t2 as a where 3 > (select count(*) from t2 where gid=a.gid and col2>a.col2) order by a.gid,a.col2 desc; 
-- 比当前 col2 大的值如果小于三条就输出 (注意必须是小于三条, 如果等于三条就代表已经有了三条), 然后输出后排序;

上面两条自连接 sql 都比较难理解, 但只要换个角度, 其实理解起来也很容易, 首先在 mysql 中要把 select 翻译为输出, 并且要满足 where 以后才输出; 输出以后再分组, 分组以后才轮到排序, 排序之后才轮到取几个

JOIN

[inner] join,left join,right join;

通过 join,mysql 可以做到集合中的求交集, 并集, 差集等需求, 但比起类似 redis 等集合来说, 效率差了不止一个级别了;

数据准备

-- 创建数据表
CREATE TABLE IF NOT EXISTS tdb_goods(
  goods_id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  goods_name  VARCHAR(150) NOT NULL,
  goods_cate  VARCHAR(40)  NOT NULL,
  brand_name  VARCHAR(40)  NOT NULL,
  goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
  is_show     BOOLEAN NOT NULL DEFAULT 1,
  is_saleoff  BOOLEAN NOT NULL DEFAULT 0
)ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COMMENT='测试商品表';

-- 写入记录
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6 英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0 英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6 英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6 英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5 英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3 英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3 英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini MD531CH/A 7.9 英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad Air MD788CH/A 9.7 英寸平板电脑（16G WiFi 版）','平板电脑','苹果','3388',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini ME279CH/A 配备 Retina 显示屏 7.9 英寸平板电脑（16G WiFi 版）','平板电脑','苹果','2788',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20 英寸一体电脑','台式机','联想','3499',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5 英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP 台式电脑（i5-3450 四核 4G 500G 2G 独显 DVD 键鼠 Linux）','台式机','宏碁','3699',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PA 工作站','服务器 / 工作站','惠普','4288',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 II 服务器','服务器 / 工作站','戴尔','5388',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器 / 工作站','苹果','28888',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X3250 M4 机架式服务器 2583i14','服务器 / 工作站','IBM','6888',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('玄龙精英版 笔记本散热器','笔记本配件','九州风神','',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

语法

table1
{[INNER|CROSS]JOIN|{LEFT|RIGHT}[OUTER]JOIN}
table2
ON conditional_expr
...
{[INNER|CROSS]JOIN|{LEFT|RIGHT}[OUTER]JOIN}
tablen
ON conditional_expr

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接, 或等值连接,）：取得两个表中存在连接匹配关系的记录。
• LEFT JOIN（左连接）：取得左表（table1）完全记录，然后再去匹配 (table2), 如果匹配不到以 NULL 的形式返回 table2 的字段值。
• RIGHT JOIN（右连接）：与 LEFT JOIN 相反。

关联更新

-- ↓↓把单表更新的 table 换成了关联在一起的 table
UPDATE tdb_goods AS g
JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name
JOIN tdb_goods_cates AS c ON g.goods_cate = c.cate_name
SET g.brand_name = b.brand_id,
 g.goods_cate = c.cate_id;

-- ↓↓由于把原来的品牌名和分类名更换为了 id, 所以相应的字段名称和类型要有所改变
ALTER TABLE tdb_goods 
CHANGE brand_name brand_id TINYINT NOT NULL DEFAULT 0,
CHANGE goods_cate cate_id TINYINT NOT NULL DEFAULT 0;

DESC tdb_goods;

关联查询

-- 内连接
SELECT * FROM tdb_goods AS g 
JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;

-- 左连接
SELECT * 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;

关联删除

-- 查找出重复记录;
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;

-- 进行删除
DELETE t1 FROM tdb_goods AS t1 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;
-- 注意, 单表的删除在 delete 后面不用加上表名, 但多表一定要加, 否则会报语法错误;
-- 如何理解这条 sql 语句? 首先把 t1 和 t2 关联一起; 然后把 t1 全删除; 也就是说把符合另外一张表关联条件的本表给删掉;

Cross join [笛卡尔]

交叉连接，得到的结果是两个表的乘积

select * from tdb_goods,tdb_goods_brands; -- tdb_goods 表 23 条数据,tdb_goods_brands 有 10 条数据, 笛卡尔以后就出现 23*10 条数据;

select * from tdb_goods as g join tdb_goods_brands as b;  -- join 在没有 on 条件的时候也是笛卡尔乘积;

Full join [union]

mysql 里面没有 Full join, 只有 union; 如果要使用 union 的话, 被 union 的表的结构要一样才能并在一起;

-- 先查出 1101 的评论, 再查出 1101 所关注的人的评论;
SELECT * FROM `comment` WHERE user_id = 1101 UNION SELECT * FROM `comment` WHERE user_id IN (select follow_user_id from follow where user_id = 1101);

关于 join 的外键约束

以往做 PHP+MYSQL 的 web 应用时, 从未用过外键, 但即使如此, 外键还是很有必要的, 它能是多表之间的关联更严格, 能够达到一致性的需求;
如果不用外键约束的话, 在多表关联的应用场景中, 我们插入一条数据, 该条数据只要满足语法规范既可插入, 但如果使用了外键, 该语句还要同时满足当前外键在关联的表中是否存在;

使用外键要满足以下条件:

1. 两张表必须都是 InnoDB 表，并且它们没有临时表。
2. 外键列和参照列必须具有相似的数据类型. 其中数字的长度及是否有符号位必须相同; 而字符的长度则可以不同
3. 外键列和参照列必须创建索引. 如果外键列不存在索引的话,mysql 将自动创建索引

语法

  [CONSTRAINT [symbol]] FOREIGN KEY
  [index_name] (index_col_name, ...)
  REFERENCES tbl_name (index_col_name,...)
  [ON DELETE reference_option]
  [ON UPDATE reference_option]
  reference_option:
      RESTRICT | CASCADE | SET NULL | NO ACTION

如果子表试图创建一个在父表中不存在的外键值，InnoDB 会拒绝任何 INSERT 或 UPDATE 操作。如果父表试图 UPDATE 或者 DELETE 任何子表中存在或匹配的外键值，最终动作取决于外键约束定义中的 ON UPDATE 和 ON DELETE 选项。InnoDB 支持 5 种不同的动作，如果没有指定 ON DELETE 或者 ON UPDATE，默认的动作为 RESTRICT:

1. CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新子表中匹配的行。ON DELETE CANSCADE 和 ON UPDATE CANSCADE 都被 InnoDB 所支持。
2. SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为 NOT NULL 时才有效。ON DELETE SET NULL 和 ON UPDATE SET NULL 都被 InnoDB 所支持。
3. NO ACTION: InnoDB 拒绝删除或者更新父表。
4. RESTRICT: 拒绝删除或者更新父表。指定 RESTRICT（或者 NO ACTION）和忽略 ON DELETE 或者 ON UPDATE 选项的效果是一样的。
5. SET DEFAULT: InnoDB 目前不支持。

上文中的父表是指被参照的表;

外键约束使用最多的情况无外乎：

1. 父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败；(在外键定义中，我们使用 ON UPDATE CASCADE ON DELETE RESTRICT)
2. 父表更新时子表也更新，父表删除时子表匹配的项也删除。(使用 ON UPDATE CASCADE ON DELETE CASCADE)

InnoDB 允许你使用 ALTER TABLE 在一个已经存在的表上增加一个新的外键:

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

也支持

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

Example

CREATE TABLE `test1` (`goods_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `goods_name` varchar(150) NOT NULL,
  `brand_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `goods_price` decimal(15,3) unsigned NOT NULL DEFAULT '0.000',
  PRIMARY KEY (`goods_id`),
  KEY `brand_id` (`brand_id`),
  FOREIGN KEY (`brand_id`) REFERENCES `test2` (`brand_id`)
-- test1 的 brand_id 必须和 test2 一样, 包括类型, 长度, 是否有符号, 才能创建外键;
-- 创建完之后 FOREIGN KEY... 这 sql 就会自动变为:CONSTRAINT `test1_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `test2` (`brand_id`)
-- 如果不加任何 ON DELETE reference_option 语句的话, 默认就是 NO ACTION, 也就是说, 父表 test2 更新和删除, 只准增加;
-- 如果加上 ON DELETE CANSCADE 和 ON UPDATE CANSCADE 的话,test2 删除和更新都会影响到 test1;
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='索引测试 test1';

CREATE TABLE `test2` (`brand_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `brand_name` varchar(40) NOT NULL,
  PRIMARY KEY (`brand_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='索引测试 test2';

mysql> insert into test1(goods_name,brand_id) values('i phone 1',6);
1452 - Cannot add or update a child row: a foreign key constraint fails (`test`.`test1`, CONSTRAINT `test1_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `test2` (`brand_id`))
-- 没有参照不能插入

mysql> delete from test2 where brand_id=1;
1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`test1`, CONSTRAINT `test1_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `test2` (`brand_id`))
-- NO ACTION, 凡是 test1 已经关联上的 test2 的 row 都不能被删除; 保持数据的一致性; 没关联上可以被删除, 虽然是 no action;

自连接 +join 实现一级分类

表的设计是无限极的设计方式, 但 mysql 搞不了递归, 只能实现一级;

数据准备

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
 )ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='无限极分类表';; 

 INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);

实现分类

SELECT
  t1.type_id,
  t1.type_name AS parent_name,
  t2.type_name,
  t2.parent_id
FROM
  `tdb_goods_types` AS t1
JOIN tdb_goods_types AS t2 ON t1.type_id = t2.parent_id;