【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;