【学习笔记】子查询和分组过滤

子查询和分组过滤

 

子查询

子查询就是在where语句中，再嵌套一个查询语句

下面我们以查询学生考试信息为例，列举三种查询方式

查询C语言-1的分数大于20分的学生信息（学号、姓名、考试成绩），分数降序排序

1. 联表查询

   SELECT s.studentno,studentname,studentresult
   FROM student s
   INNER JOIN result r
   ON s.studentno = r.studentno
   INNER JOIN `subject` sub
   ON sub.subjectno = r.subjectno
   WHERE subjectname = 'C语言-1' AND studentresult>=20
   ORDER BY studentresult DESC

   这是通过联表查询的方式，连接了三个表

   

 

2. 子查询

   SELECT s.studentno,studentname,studentresult
   FROM student s
   INNER JOIN result r
   ON s.studentno = r.studentno
   WHERE subjectno = (
        SELECT subjectno FROM `subject` WHERE subjectname='C语言-1'
   ) AND studentresult>=20
   ORDER BY studentresult DESC

   在查询subjectno时，没有连接表，而是在where语句中又写了一个查询语句

 

3. 嵌套查询

   SELECT studentno,studentname 
   FROM student  
   WHERE studentno IN (
       SELECT studentno FROM result WHERE subjectno =(
               SELECT subjectno FROM `subject` WHERE subjectname = 'C语言-1'
           ) AND studentresult>=20
   )

   这段sql把studentno也嵌套进去了，但是这样就查询不出studentresult，因为学生表中没有这一列

 

 

子查询的效率更高一些，因为它只查询没有联表

 

分组过滤

涉及到两个关键词：GROUP BY 和 HAVING

我们通过查询不同课程的平均分、最高分、最低分 来了解

SELECT subjectname ,AVG(studentresult) AS 平均分, MAX(studentresult) AS 最高分, MIN(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

这时我们发现只查出一条信息，因为最大值，最小值只有一个

但是我们想要的结果是每一个课程的平均分、最高分、最低分 ，所以我们要通过studentid来分组

SELECT subjectname ,AVG(studentresult) AS 平均分, MAX(studentresult) AS 最高分, MIN(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
GROUP BY subjectno

这时我们得到了每个课程的平均分、最高分、最低分，如果我们再加一个条件，即平均分大于80分

我们首先会想到使用where来控制：where 平均分>80,

SELECT subjectname ,AVG(studentresult) AS 平均分, MAX(studentresult) AS 最高分, MIN(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE 平均分 > 80
GROUP BY subjectno

发现报错了，所以不能使用where

我们要使用HAVING，因为HAVING是过滤分组后要满足的次要条件

分组之后，就要使用HAVING 来控制条件

SELECT subjectname ,AVG(studentresult) AS 平均分, MAX(studentresult) AS 最高分, MIN(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
GROUP BY subjectno
HAVING 平均分 > 80