Exists 和not in 的使用

最新推荐文章于 2025-06-28 21:29:29 发布

原创最新推荐文章于 2025-06-28 21:29:29 发布 · 351 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

12 篇文章

订阅专栏

本文深入探讨了SQL查询中的高级技巧，特别是如何利用LEFT JOIN和GROUP BY结合HAVING子句来筛选平均成绩低于60分的学生信息。通过具体实例，展示了如何处理有成绩和无成绩的学生记录，同时对比了EXISTS和NOT EXISTS在查询中的应用，帮助读者掌握复杂SQL查询的构造方法。

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `s_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `s_birth` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `s_sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01', '女');
INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20', '女');

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `c_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `s_score` int(3) NULL DEFAULT NULL,
  PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('01', '01', 80);
INSERT INTO `score` VALUES ('01', '02', 90);
INSERT INTO `score` VALUES ('01', '03', 99);
INSERT INTO `score` VALUES ('02', '01', 70);
INSERT INTO `score` VALUES ('02', '02', 60);
INSERT INTO `score` VALUES ('02', '03', 80);
INSERT INTO `score` VALUES ('03', '01', 80);
INSERT INTO `score` VALUES ('03', '02', 80);
INSERT INTO `score` VALUES ('03', '03', 80);
INSERT INTO `score` VALUES ('04', '01', 50);
INSERT INTO `score` VALUES ('04', '02', 30);
INSERT INTO `score` VALUES ('04', '03', 20);
INSERT INTO `score` VALUES ('05', '01', 76);
INSERT INTO `score` VALUES ('05', '02', 87);
INSERT INTO `score` VALUES ('06', '01', 31);
INSERT INTO `score` VALUES ('06', '03', 34);
INSERT INTO `score` VALUES ('07', '02', 89);
INSERT INTO `score` VALUES ('07', '03', 98);

SET FOREIGN_KEY_CHECKS = 1;

-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩**
**-- (包括有成绩的和无成绩的)**
select st.s_id ,st.s_name ,Round(avg(s.s_score),2) as score from student st
left join score s on s.s_id = st.s_id group by s.s_id having Round(avg(s.s_score),2) < 60
union
SELECT st.s_id ,st.s_name ,0 as score from student st where s_id not in ( select s_id from score )

select st.s_id ,st.s_name ,Round(avg(s.s_score),2) as score from student st
left join score s on s.s_id = st.s_id group by s.s_id having Round(avg(s.s_score),2) < 60
union
SELECT st.s_id ,st.s_name ,0 as score from student st where not exists ( select s.s_id from score s where s.s_id = st.s_id )

exists 使用的主要注意： -- 如果exists后面的语句查询出来有值则为真，会将SELECT * from student st 查询结果全部展示
如果exists后面语句有 student 表中的条件，则会使用过滤条件。

-- //**** not exists后面一般要加查询表如student 中的条件，才能筛选出来,如果没有student表中的条件，那么结果会如下语句查询不出来
-- //没有SELECT * from student st 结果一个符合 not exists 后面 (SELECT s_id from score s)的值因此查不出来值
SELECT * from student st where not exists (SELECT s_id from score s)
-- // SELECT * from student st 结果符合 not exists 后面 (SELECT s_id from score s where s.s_id = st.s_id ）的值，因此有值
SELECT * from student st where exists (SELECT s_id from score s where s.s_id = st.s_id)

-- 如果exists后面的语句查询出来有值则为真，会将SELECT * from student st 查询结果全部展示
SELECT * from student st where exists (SELECT 1 from score s where s.s_id =2 )
-- 如果exists后面语句有 student 表中的条件，则会使用过滤条件。
SELECT * from student st where exists (SELECT 1 from score s where s.s_id =2 and st.s_id = 3)

SELECT * from student st where not exists (SELECT 1 from score s where s.s_id =2 and st.s_id = 3)

SELECT * from student st where not exists (SELECT 1 from score s where s.s_id =st.s_id AND st.s_name = '赵雷')