SQL 连接查询、子查询、union
图例
1、连接查询
查询过程中两张表没有主次关系就是内连接，如果两张表有主次关系就是外连接
1.1 内连接
* 表 1 inner join 表 2 on 关联条件 * 做连接查询的时候一定要写上关联条件 * inner 可以省略 -- -----------内连接 （不区分主从表，匹配的全部数据）只保留两张表中完全匹配的结果集 -- -- 等值链接 -- 求：显示学生名和班级名 SELECT u.`name`, c.classname FROM `user` u JOIN class c ON u.classid = c.id -- -- 非等值链接 -- 求：每个学生的成绩评级，显示学生名、成绩、成绩评级 SELECT u.`name`, u.grade, ss.leve_name FROM `user` u JOIN score_level ss ON u.grade BETWEEN ss.lower AND ss.height -- -- 自链接 -- 求：查询学生的管理者，显示学生名和管理者名 （通过表a查询学生，再通过表b查询学生管理者） SELECT a.NAME '学生', b.`name` '管理者' FROM `user` a JOIN `user` b ON a.leader_id = b.id
1.2 外连接
*左外连接
* 表 1 left outer join 表 2 on 关联条件
* 做连接查询的时候一定要写上关联条件
* outer 可以省略
*右外连接
* 表 1 right outer join 表 2 on 关联条件
* 做连接查询的时候一定要写上关联条件
* outer 可以省略
*左外连接（左连接）和右外连接（右连接）的区别：
*左连接以左面的表为准和右边的表比较，和左表相等的不相等都会显示出来，右表符合条件
--
-- -----------外连接 （区分主从表 ）
--
-- 右外连接（右连接） ：展示join关键字右表的所有字段 -> RIGHT JOIN ... ON ...
SELECT u.`name`,c.classname FROM `user` u RIGHT JOIN class c ON u.classid=c.id
--
-- 左外连接（左连接） ：展示join关键字左表的所有字段 -> LEFT JOIN ... ON ...
SELECT u.`name`,c.classname FROM `user` u LEFT JOIN class c ON u.classid=c.id
1.3 多表连接
多张表用到的连接，包含内连接和外连接
-- -- 多张表连接 -- -- 模板：SELECT ... FROM a JOIN b ON a.=b. JOIN c ON a.=c. JOIN d ON a.=d. -- -- 求：查询每一位学生并 显示学生名、成绩、成绩评级、班级 SELECT c.classname, u.`name`, u.grade, ss.leve_name FROM `user` u JOIN score_level ss ON u.grade BETWEEN ss.lower AND ss.height JOIN class c ON u.classid=c.id -- 求：查询每一位学生并 显示学生名、成绩、成绩评级、班级、管理者 SELECT c.classname, u.`name`, u.grade, ss.leve_name, m.`name` '管理者' FROM `user` u JOIN score_level ss ON u.grade BETWEEN ss.lower AND ss.height JOIN class c ON u.classid = c.id LEFT JOIN `user` m ON u.leader_
2、子查询
子查询就是嵌套的 select 语句，可以理解为子查询是一张表
-- 结构
-- SELECT
-- ( 子查询 )
-- FROM
-- （子查询）
-- WHERE
-- （子查询）
-- ....
-- WHERE （子查询） -- 求：学生表中比最低分数高的所有学生 SELECT *FROM `user` u WHERE u.grade>(SELECT MIN(uu.grade) FROM `user` uu) -- 连接查询 SELECT *FROM `user` u JOIN (SELECT MIN(uu.grade) minG FROM `user` uu) j ON u.grade>j.minG -- FROM （子查询）（FROM 后面看成是一张临时表） -- 求：每个年级的平均分数 和 平均分数等级 并显示平均分数、平均分数等级、班级名称 SELECT ss.avgg, sl.leve_name, c.classname FROM ( SELECT classid, AVG( grade ) avgg FROM `user` GROUP BY classid ) ss JOIN score_level sl ON avgg BETWEEN sl.lower AND sl.height JOIN class c ON ss.classid = c.id -- SELECT( 子查询 )：子查询查询出来的数据必须是一条记录 SELECT u.`name`,(SELECT c.classname FROM class c WHERE c.id = u.classid) cName FROM `user` u
3、union
合并结果集的时候，需要查询字段对应个数相同。在 Oracle 中更严格，不但要求个数相同，而且还要求类型对应相同。
--union是产生的两个记录集(字段要一样的)并在一起，成为一个新的记录集 。 SELECT `name`,classid FROM `user` WHERE classid = 1 UNION SELECT `name`,classid FROM `user` WHERE classid = 2
4、总结
join 是两张表做交连后里面条件相同的部分记录产生一个记录集，
union是产生的两个记录集(字段要一样的)并在一起，成为一个新的记录集 。
union自动屏蔽重复的数据
5、sql及表
-- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `id` int(11) NOT NULL AUTO_INCREMENT, `classname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班级名称', `subjectid` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '科目id', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- INSERT INTO `class` VALUES (1, '一年级', '1'); INSERT INTO `class` VALUES (2, '二年级', '2'); INSERT INTO `class` VALUES (3, '三年级', '3'); INSERT INTO `class` VALUES (4, '四年级', '4'); INSERT INTO `class` VALUES (5, '五年级', '5'); DROP TABLE IF EXISTS `score_level`; CREATE TABLE `score_level` ( `id` int(4) NOT NULL AUTO_INCREMENT, `lower` int(3) NULL DEFAULT NULL COMMENT '最低分',, `height` int(3) NULL DEFAULT NULL COMMENT '最高分', `leve_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '等级名称', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- INSERT INTO `score_level` VALUES (1, 0, 30, '很差'); INSERT INTO `score_level` VALUES (2, 30, 60, '差'); INSERT INTO `score_level` VALUES (3, 60, 80, '良'); INSERT INTO `score_level` VALUES (4, 80, 95, '优秀'); INSERT INTO `score_level` VALUES (5, 95, 100, '很优秀'); -- ---------------------------- DROP TABLE IF EXISTS `subject`; CREATE TABLE `subject` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '科目id', `subjectname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '科目名称', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- INSERT INTO `subject` VALUES (1, '语文'); INSERT INTO `subject` VALUES (2, '数学'); INSERT INTO `subject` VALUES (3, '英语'); INSERT INTO `subject` VALUES (4, '化学'); INSERT INTO `subject` VALUES (5, '物理'); -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `teacher_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '教师名字', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES (1, '大老师'); INSERT INTO `teacher` VALUES (2, '肖老师'); -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名字', `age` int(3) NULL DEFAULT NULL COMMENT '年龄', `grade` double(5, 2) NULL DEFAULT NULL COMMENT '分数', `classid` int(11) NULL DEFAULT NULL COMMENT '班级id', `create_time` date NULL DEFAULT NULL COMMENT '创建时间', `teacher_id` int(11) NULL DEFAULT NULL COMMENT '关联教师表的教师id', `leader_id` int(11) NULL DEFAULT NULL COMMENT '学生中的管理者', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- ---------------------------- INSERT INTO `user` VALUES (1, '张三', 35, 24.00, 3, '2021-10-13', 1, NULL); INSERT INTO `user` VALUES (3, '赵六', 23, 23.82, 2, '2021-10-07', 2, 1); INSERT INTO `user` VALUES (5, '李小', 30, 23.16, 3, '2021-10-02', 1, 1); INSERT INTO `user` VALUES (6, '赵四', 45, 34.00, 2, '2021-10-01', 2, 1); INSERT INTO `user` VALUES (7, '大红', 12, 60.98, 1, '2021-10-21', 1, 1); INSERT INTO `user` VALUES (8, '赵八', 23, 99.90, 2, '2021-09-28', 1, 1); INSERT INTO `user` VALUES (9, 'DAXIE', 35, 34.00, 2, '2021-09-27', 2, 1); INSERT INTO `user` VALUES (10, 'xiaoxie', 67, 89.00, 3, '2021-09-27', 1, 3); INSERT INTO `user` VALUES (11, '小红', NULL, NULL, NULL, NULL, 2, 3); INSERT INTO `user` VALUES (12, '小吕', NULL, NULL, NULL, '2021-10-01', 1, 3); -- ---------------------------- DROP TABLE IF EXISTS `uses_subject`; CREATE TABLE `uses_subject` ( `id` int(3) NOT NULL AUTO_INCREMENT, `user_id` int(3) NULL DEFAULT NULL COMMENT '学生id', `subject_id` int(3) NULL DEFAULT NULL COMMENT '课程id', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- INSERT INTO `uses_subject` VALUES (1, 1, 2); INSERT INTO `uses_subject` VALUES (2, 2, 3); INSERT INTO `uses_subject` VALUES (3, 1, 3); INSERT INTO `uses_subject` VALUES (4, 1, 1); INSERT INTO `uses_subject` VALUES (5, 3, 2); INSERT INTO `uses_subject` VALUES (6, 3, 1);
