嗨客网搜索
数据库教程
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 没有全外联结。

嗨客网顶部