赞
赏
前面的章节中,我们了解了表的 内连接查询(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;
运行结果如下
自然联结
只要对表进行联结查询,多张表里面的列至少有一个列不止出现在一个表里。我们了解了内联结。它会返回所有的数据,相同的列会出现多次(各个表里面相关联的列)。
自然联结石排除多次出现,将每个列只返回一次。自然联结中用户通过编写 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;
运行结果如下
我们通过列子中可以看到,上面的 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;
运行结果如下
右外联结(RIGHT OUTER join)
![]()