MySQL 03
子查询
子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询
子查询在WHERE语句中的一般用法:
SELECT … FROM 表1 WHERE 字段1 比较运算符(子查询)
注意:将子查询和比较运算符联合使用,必须保证子查询返回的值不能多于一个
***引用前两节创建的student,result,subject,grade表***
举个例子:查询参加最近一次Logic Java考试成绩的学生的最高分和最低分
SELECT MAX(studentResult) AS '最高分',MIN(studentResult) AS '最低分'
FROM result
WHERE subjectNo=(SELECT subjectNo FROM subject_1
WHERE subjectName='Logic Java')
AND examDate=( SELECT MAX(examDate) FROM result
WHERE subjectNo=(SELECT subjectNo FROM subject_1
WHERE subjectName='Logic Java'));
一个子查询了学号等于选择了Logic Lava 的def学号,第二子查询了examDate 等于examDate的最大值,即为最近一次的考试时间,学号等于(又一个子查询)学号为选择了Logic Lava的学号。
IN子查询
常用IN替换等于(=)的子查询
IN后面的子查询可以返回多条记录
例:查询参加“Logic Java”课程最近一次考试的在读学生名单
SELECT * FROM student
WHERE gradeID=(
SELECT gradeID FROM subject_1 WHERE subjectName='Logic Java'
)AND
studentNo IN (
SELECT studentNo FROM result WHERE examDate=(
SELECT MAX(examDate) FROM subject_1 WHERE subjectName='Logic Java')
AND subjectNo =(SELECT subjectNo FROM subject_1 WHERE subjectName='Logic Java')
)
在子查询studentNo学生学号是,我们使用了 IN子查询。因为查询结果返回了选择Logic Java课程学生的学号并且还有最近一次参加Logic Java考试的学生学号的交集,是一组数据,这时如果使用=号,会报错,而IN子查询则会逐一匹配数据。
EXISTS子查询
语法:SELECT ...... FROM 表名 WHERE EXISTS (子查询);
子查询有返回结果: EXISTS子查询结果为TRUE
子查询无返回结果: EXISTS子查询结果为FALSE,外层查询不执行
例:检查“Logic Java”课程最近一次考试成绩如果有 80分以上的成绩,显示分数排在前5名的学员学号和分数
SELECT studentNo,`studentResult` FROM result
WHERE EXISTS(
SELECT * FROM result
WHERE `studentResult` > 80
AND subjectNo=(SELECT subjectNo FROM subject_1 WHERE subjectName='Logic Java')
AND examDate=(SELECT MAX(`examDate`) FROM `result`
WHERE `subjectNo`=(SELECT `subjectNo` FROM `subject_1`
WHERE `subjectName`='Logic Java' ) )
)
AND subjectNo=(SELECT subjectNo FROM subject_1 WHERE subjectName='Logic Java')
AND examDate=(SELECT MAX(`examDate`) FROM `result`
WHERE `subjectNo`=(SELECT `subjectNo` FROM `subject_1`
WHERE `subjectName`='Logic Java' ) )
ORDER BY `studentResult` DESC
LIMIT 5
首先我们使用exists子查询判断了‘Logic Lava’最近一次考试中是否有80分以上的成绩,如果有,则执行外侧代码,如果没有,则返回null。接着我们挑选出学号满足选择了Logic Java 的学生并且参加了最近一次考试。然后order by 成绩,并降序排序
最后使用limit 只返回了前5条数据。
例2:检查“Logic Java”课程最近一次考试成绩,如果全部未通过考试(60分及格),认为本次考试偏难,计算的该次考试平均分加5分
SELECT AVG(studentResult)+5 FROM result
WHERE subjectNo=(SELECT subjectNo FROM subject_1 WHERE subjectName='Logic Java')
AND examDate=(SELECT MAX(examDate)FROM result WHERE subjectNo=(
SELECT subjectNo FROM subject_1 WHERE subjectName='Logic Java'))
AND NOT EXISTS (
SELECT * FROM result WHERE
subjectNo=(SELECT subjectNo FROM subject_1 WHERE subjectName='Logic Java')
AND examDate=(SELECT MAX(examDate)FROM result WHERE subjectNo=(
SELECT subjectNo FROM subject_1 WHERE subjectName='Logic Java'))
AND studentResult>60
)
首先我们看NOT RXISTS 子查询中,里面查询了result列表中Logci Lava的课程编号,还有最近一次Logci Lava考试的成绩,并且是否有大于60的成绩,如果有大于60的成绩,则not exists 为false,外侧代码不执行,返回null。如果没有大于60的成绩,外侧代码执行,返回平均分+5。
分组查询 group by
SELECT subjectNo,AVG(`studentResult`) AS `平均成绩` FROM result
GROUP BY subjectNo
这段代码查询了result表中的科目编号,已经平均成绩,并按照科目编号分组。查询结果如下:
查询student表中以出生年份分组的人数:
SELECT YEAR(`bornDate`),COUNT(*) FROM student
GROUP BY YEAR(`bornDate`)
分组筛选:having
我们继续查询result表中根据科目分组的平均成绩,并只返回平均成绩大于60的数据:
SELECT subjectNo,AVG(`studentResult`) AS `平均成绩` FROM result
GROUP BY subjectNo
HAVING AVG(`studentResult`)>60
ORDER BY studentResult DESC;
返回结果:
对比可发现,科目序号为3,平均成绩没有大于60的数据被筛选出去了。
WHERE子句
用来筛选 FROM 子句中指定的操作所产生的行
GROUP BY子句
用来分组 WHERE 子句的输出
HAVING子句
用来从分组的结果中筛选行
首先where判断输出怎样的数据,然后使用group by进行分组,最后having进行筛选。
多表联合查询
内联:inner join
SELECT studentName,s.studentNo,gradeName,subjectName,studentResult
FROM student AS s
INNER JOIN result AS r ON r.studentNo=s.`studentNo`
INNER JOIN subject_1 AS sub ON r.subjectNo=sub.`subjectNo`
INNER JOIN grade AS g ON g.`gradeID`=s.`gradeID`
如上所示,我们查询了学生姓名,学生编号,年级姓名,科目姓名和学生成绩,共来自student,result,subject_1和grade这四个表,分别通过studentNo将student与result表相连,subjectNo将subject_1与result表相连,gradeID将student与grade表相连。
注意如果查询的数据为多表共有,则需标明属于哪个表。
查询结果如下:
我们也可以使用直连的方式解决上述问题
直连:
SELECT studentName,sub.`subjectNo`,gradeName,subjectName,studentResult
FROM student AS s ,result AS r,subject_1 AS sub ,grade AS g
WHERE r.studentNo=s.`studentNo`
AND r.subjectNo=sub.`subjectNo`
AND g.`gradeID`=s.`gradeID`
直接则直接from多个表,通过where条件将其连接。
外联(分为左外联与右外联)
左外联:left join
语法:select ... from 主表 left join 从表 on 连接键
注意:主表(左表)student中数据逐条匹配表result中的数据匹配,返回到结果集;若无匹配,NULL值返回到结果集
例:主表student,从表result,返回学生姓名,学号,考试成绩
SELECT studentName,s.studentNo,studentResult
FROM student AS s
LEFT JOIN result AS r ON r.studentNo=s.`studentNo`
返回:
可以发现,后6条数据在result中没有考试成绩的数据,会以null的形式输出。
右外联:right join
语法:select ... from 从表 right join 主表 on 连接键
SELECT studentName,s.studentNo,studentResult
FROM result AS r
RIGHT JOIN student AS s ON r.studentNo=s.`studentNo`
这段代码的作用于上一个左外联结果一样,可以发现,
左外联的主表在from后,而
右外联的主表在right join后。