Mysql高级联结

Mysql高级联结教程

前面的章节中，我们了解了表的 内连接查询（inner join），左关联查询（left join），右关联查询（right join）。这章节我们来了解一下表的自联结（self-join），自然联结（natural join）和外联结（outer join）。

在前面的文章中，我们了解到列可以设置别名，表名也可以设置别名，它和列设置别名语法一样，也可以通过 AS 来对表设置别名。通过对表设置别名不仅可以缩短 SQL 语句也可以允许对表查询到时候多次操作相同的表。

自联结

自联结表示将一张表当作两个表来操作，它们通过别名来控制。

自联结使用的 sql 语句如下

-- 工人表
create table emp(
`id` int(11) NOT NULL AUTO_INCREMENT,
`ename`  varchar(255) DEFAULT NULL COMMENT '员工名称', 
`sex` varchar(4) DEFAULT NULL COMMENT '性别',
`dept_id` int(11) DEFAULT NULL COMMENT '部门ID',
`leader_id` int(11) DEFAULT NULL COMMENT '领导ID',
PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
INSERT INTO emp(`id`,`ename`,`sex`,`dept_id`,`leader_id`) VALUES 
(1,'小张','男',2,NULL),
(2,'小赵','男',2,1),
(3,'小钱','男',2,2),
(4,'小孙','男',2,1),
(5,'小李','男',2,1),
(6,'小周','男',2,2),
(7,'小吴','男',2,2);

案例

我们通过自联结，将该员工的名字和他领导的名字都查询出来。

-- 嗨客网(www.haicoder.net)
SELECT e1.id,e1.ename AS '员工名称',e2.ename AS '领导名称' FROM emp AS e1, emp AS e2 
WHERE e1.leader_id = e2.id;

运行结果如下

11 selfjoin.png

自然联结

只要对表进行联结查询，多张表里面的列至少有一个列不止出现在一个表里。我们了解了内联结。它会返回所有的数据，相同的列会出现多次（各个表里面相关联的列）。

自然联结石排除多次出现，将每个列只返回一次。自然联结中用户通过编写 sql 语句来将相同的列给排出掉。

自然联结我们使用的表数据如下

-- 学生表
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` int(11) DEFAULT NULL,
  `stu_name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `class_id` int(11) NOT NULL COMMENT '班级ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
INSERT INTO `student` VALUES (1, 1001, 'xiaoming', 17, 1);
INSERT INTO `student` VALUES (2, 1002, 'honghong', 16, 2);
INSERT INTO `student` VALUES (3, 1003, 'xiaojun ', 18, 3);
INSERT INTO `student` VALUES (4, 1004, '小亮', 19, 1);
INSERT INTO `student` VALUES (5, 1005, 'LIANGLIANG', 19, 4);
INSERT INTO `student` VALUES (6, 1006, ' 小 杰 ', 16, 4);
INSERT INTO `student` VALUES (7, 1007, '小亮', NULL, 1);
INSERT INTO `student` VALUES (8, 1008, '无年龄', NULL, 2);
INSERT INTO `student` VALUES (9, 1009, '欧阳亮亮', 19, 5);

-- 班级表
CREATE TABLE `class`(
`id` int(11) NOT NULL AUTO_INCREMENT,
 `class_name` varchar(100) NOT NULL DEFAULT '' COMMENT '班级名称',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
INSERT INTO class VALUES(1,'一年级一班');
INSERT INTO `class` VALUES(2,'一年级二班');
INSERT INTO `class` VALUES(3,'二年级一班');
INSERT INTO `class` VALUES(4,'二年级二班');
INSERT INTO `class` VALUES(6,'二年级三班');

案例

-- 嗨客网(www.haicoder.net)
SELECT student.*,class.class_name FROM student,class
WHERE student.class_id = class.id;

运行结果如下

12 natural_join.png

我们通过列子中可以看到，上面的 sql 是通过内联结将学生表和班级表两个表里面的数据关联起来，对学生表，我们使用的通配符 * 将学生表里面的所有数据查询出来，其他表里面的列都明确列出，所以没有重复的列被列举出来。一般情况下，我们使用的内联结都是自然联结。

外联结

前面的联结查询中，我们了解到了左连接和右连接，其实，左外联结和右外联结的效果和左联结右联结是一样的。

外联结的操作和我们前面了解的联结查询相似，在 Mysql 中有左外联结和右外联结。它不存在全外联结。

外联结我们使用下表语句

-- 学生表
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` int(11) DEFAULT NULL,
  `stu_name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `class_id` int(11) NOT NULL COMMENT '班级ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
INSERT INTO `student` VALUES (1, 1001, 'xiaoming', 17, 1);
INSERT INTO `student` VALUES (2, 1002, 'honghong', 16, 2);
INSERT INTO `student` VALUES (3, 1003, 'xiaojun ', 18, 3);
INSERT INTO `student` VALUES (4, 1004, '小亮', 19, 1);
INSERT INTO `student` VALUES (5, 1005, 'LIANGLIANG', 19, 4);
INSERT INTO `student` VALUES (6, 1006, ' 小 杰 ', 16, 4);
INSERT INTO `student` VALUES (7, 1007, '小亮', NULL, 1);
INSERT INTO `student` VALUES (8, 1008, '无年龄', NULL, 2);
INSERT INTO `student` VALUES (9, 1009, '欧阳亮亮', 19, 5);

-- 班级表
CREATE TABLE `class`(
`id` int(11) NOT NULL AUTO_INCREMENT,
 `class_name` varchar(100) NOT NULL DEFAULT '' COMMENT '班级名称',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
INSERT INTO class VALUES(1,'一年级一班');
INSERT INTO `class` VALUES(2,'一年级二班');
INSERT INTO `class` VALUES(3,'二年级一班');
INSERT INTO `class` VALUES(4,'二年级二班');
INSERT INTO `class` VALUES(6,'二年级三班');

案例

左外联结（left outer join）

-- 嗨客网(www.haicoder.net)
select * from  student  as p LEFT  OUTER join  class as  t  on p.class_id=t.id;

运行结果如下

13 left_outer_join.png

右外联结（RIGHT OUTER join）

-- 嗨客网(www.haicoder.net)
select * from  student  as p RIGHT  OUTER join  class as  t  on p.class_id=t.id;

运行结果如下

14 right_outer_join.png

Mysql高级联结总结

本章节我们了解到了自联结，自然联结和外联结。自联结就是给表设置别名，表与表关联。自然联结表示通过 sql 语句将多张表里面重复的列的数据排除掉，让两张表里面查询到的数据相当于一个表里面的数据。

外联结和左关联有关联数据一样，注意，mysql 没有全外联结。