MySQL 子查询(嵌套查询)介绍

学习笔记 马富天 2018-12-14 11:52:57 7 0

【摘要】子查询也可以叫做嵌套查询,即在一个 SQL 查询中包含了其它子 SQL 查询的结果,应用场景还是很常见的,本文会给出一个具体的实例仅供大家和自己学习参考使用。

相信大家在脚本语言的使用上都快忘记了 SQL 子查询的功能了,通常都是使用多条 SQL 来完成查询某一数据,既然我们是做技术的,就应该多运用一些高级的查询方法。

子查询一般会出现在 WHERE 和 FROM 子句、以及 SELECT、HAVING、EXISTS 子句等等中,如:

  1. SELECT * FROM `student` WHERE id IN(SELECT id FROM `student`);
  2. SELECT * FROM (SELECT * FROM `student`) AS tmp;
  3. SELECT (SELECT id FROM `student` AS s2 WHERE s1.id = s2.id) AS id  FROM `student` AS s1;
  4. SELECT * FROM `student` GROUP BY class_id HAVING class_id IN (SELECT id FROM `class`);
  5. SELECT * FROM `student` WHERE EXISTS(SELECT 1);

通过以上总结出来的几种子查询方式,可以在此基础上进行扩展。

以下创建一些表用来做测试数据,一共三张表,学生表 student,班主任 teacher,班级表 class:

请输入图片名称

1、查询各位班主任(含教师姓名)带领的班级(含班级名称)每位学生的平均成绩:

  1. SELECT
  2. 	t.id,
  3. 	t.`name` AS "班主任",
  4. 	(SELECT c.class_name FROM `class` AS c WHERE c.id = t.class_id ) AS "班级名称",
  5. 	(SELECT AVG(score) FROM `student` AS s WHERE s.class_id = t.class_id) AS "班级平均成绩"
  6. FROM 
  7. 	`teacher` AS t;

2、查询各班学生成绩在 90 分及以上的人数(班主任名称 + 班级名称 + 90分及以上的人数):

  1. SELECT 
  2. 	t.id,
  3. 	t.`name` AS "班主任",
  4. 	(SELECT c.class_name FROM `class` AS c WHERE c.id = t.class_id) AS "班级名称",
  5. 	(SELECT count(*) FROM `student` AS s WHERE s.class_id = t.class_id AND s.score >= 90) AS "90分及以上的学生人数"
  6. FROM
  7. 	`teacher` AS t;

3、查询学生成绩大于平均成绩的学生姓名

先把平均成绩查询出来,然后查找成绩大于平均成绩的学生

  1. SELECT
  2. 	s.id,
  3. 	s.`name` AS "学生姓名"
  4. FROM
  5. 	`student` AS s
  6. WHERE
  7. 	s.score > (SELECT AVG(score) FROM `student`);

4、查询每个班级中成绩最高的学生姓名以及对应的成绩

  1. SELECT 
  2. 	c.id,
  3. 	c.class_name AS "班级名称",
  4. 	(SELECT MAX(s.score) FROM `student` AS s WHERE s.class_id = c.id) AS "最高分"
  5. FROM
  6. 	`class` AS c;

4-1、查询每个班级中成绩最高的学生姓名以及对应的成绩,并按成绩升序排序

这里将子查询结果作为一张临时表。

  1. SELECT
  2. 	(SELECT c.class_name FROM `class` AS c WHERE c.id = class_id) AS "班级名称",
  3. 	tmp.score AS "最高分",
  4. 	tmp.name AS "学生姓名"
  5. FROM
  6. 	(SELECT * FROM `student` ORDER BY score DESC) as tmp
  7. GROUP BY
  8. 	tmp.class_id
  9. ORDER BY
  10. 	tmp.score ASC;

(备注:GROUP BY 按类别聚合输出,每个类别只输出一条记录,而 having 就是限制每个类别的条件,比如 class_id 等于或者小于啥啥啥的)

5、查询 "物联网工程","计算机科学与技术" 这两个班级的学生人数(前提是不知道这两个班级的班级 id )

  1. SELECT
  2. 	c.id,
  3. 	c.class_name AS "班级名称",
  4. 	(SELECT COUNT(*) FROM `student` AS s WHERE s.class_id = c.id) AS "班级人数"
  5. FROM
  6. 	`class` AS c
  7. WHERE
  8. 	c.class_name in("计算机科学与技术","物联网工程");

6、查询各位班主任(含教师姓名)带领的班级(含班级名称)每位学生的平均成绩,并按照平均成绩从高到低排序:

  1. SELECT
  2. 	(SELECT class_name FROM `class` AS c WHERE c.id = tmp.class_id) AS "班级名称",
  3. 	(SELECT t.name FROM `teacher` AS t WHERE t.class_id = tmp.class_id) AS "班主任",
  4. 	tmp.avg_score AS "评价成绩"
  5. FROM
  6. 	(SELECT
  7. 		s.class_id,
  8. 		avg(score) AS avg_score
  9. 	FROM
  10. 		`student` AS s
  11. 	GROUP BY
  12. 		s.class_id
  13. 	ORDER BY
  14. 		avg_score DESC) AS tmp;

以上的例子,都是自己设想出来的,其实只要掌握了子查询的方法,就能够写出很多复制的 SQL 来实现查询功能。总体来说,同一个查询功能实现的方法可以多种,并不是唯一的,然后重点在于采用出效率最高的一种方式。

例子中用到的所有基本信息:

  1. CREATE TABLE `student` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(255) DEFAULT NULL,
  4.   `class_id` int(11) DEFAULT NULL,
  5.   `score` int(11) DEFAULT NULL,
  6.   PRIMARY KEY (`id`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  8. INSERT INTO `student` VALUES ('1', '刘一', '1', '90');
  9. INSERT INTO `student` VALUES ('2', '陈二', '1', '88');
  10. INSERT INTO `student` VALUES ('3', '张三', '2', '92');
  11. INSERT INTO `student` VALUES ('4', '李四', '1', '96');
  12. INSERT INTO `student` VALUES ('5', '王五', '2', '94');
  13. INSERT INTO `student` VALUES ('6', '赵六', '2', '96');
  14. INSERT INTO `student` VALUES ('7', '孙七', '1', '90');
  15. INSERT INTO `student` VALUES ('8', '周八', '2', '87');
  16. INSERT INTO `student` VALUES ('9', '吴九', '3', '89');
  17. INSERT INTO `student` VALUES ('10', '郑十', '3', '93');
  18. CREATE TABLE `teacher` (
  19.   `id` int(11) NOT NULL AUTO_INCREMENT,
  20.   `name` varchar(255) DEFAULT NULL,
  21.   `class_id` int(11) DEFAULT NULL,
  22.   PRIMARY KEY (`id`)
  23. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  24. INSERT INTO `teacher` VALUES ('1', '刘德华', '1');
  25. INSERT INTO `teacher` VALUES ('2', '黎明', '2');
  26. INSERT INTO `teacher` VALUES ('3', '梁朝伟', '3');
  27. CREATE TABLE `class` (
  28.   `id` int(11) NOT NULL AUTO_INCREMENT,
  29.   `class_name` varchar(255) DEFAULT NULL,
  30.   PRIMARY KEY (`id`)
  31. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  32. INSERT INTO `class` VALUES ('1', '计算机科学与技术');
  33. INSERT INTO `class` VALUES ('2', '软件工程');
  34. INSERT INTO `class` VALUES ('3', '物联网工程');

小提示:MySQL 中五种常用的聚合函数:max(字段名) 取最大值、min(字段名)取最小值、sum(字段名) 求和、avg(字段名) 求平均值、count(字段名) 统计记录总数,并通常与 GROUP BY、Having 用在一起,按类别统计,分组就是将数据分为多个小组,以小组为单位进行查询。如下:

select 类别, sum(字段) as 数量之和 from A

group by 类别

having sum(字段) > 18

版权归 马富天PHP博客 所有

本文标题:《MySQL 子查询(嵌套查询)介绍》

本文链接地址:http://www.mafutian.net/401.html

转载请务必注明出处,小生将不胜感激,谢谢! 喜欢本文或觉得本文对您有帮助,请分享给您的朋友 ^_^

0

0

上一篇《 QQ、微信内置浏览器的 UA(useragent) 》 下一篇《 ASCII、ANSI、GB2312、GBK、UNICODE、UTF-8 五种编码的简介 》

暂无评论

评论审核未开启
表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情
验证码

TOP10

  • 浏览最多
  • 评论最多