MySQL数据库04（⾼级查询）
(注：数据均为⾃⼰随意调节添加)
⼀、EXISTS⼦查询
1.⽤SQL语句检测temp表是否已经创建
语法:
DROP TABLE IF EXISTS temp;
CREATE TABLE temp(
mysql高级教程视频...#省略建表语句
)
2.EXISTS⼦查询语法:
SELECT ......FROM 表名 WHERE EXISTS(⼦查询);
⼦查询有返回结果:EXISTS⼦查询结果为TRUE
⼦查询⽆返回结果:EXISTS⼦查询结果FALSE，外层查询不执⾏
例:
检查“Logic Java”课程最近⼀次考试成绩
如果有 80分以上的成绩，显⽰分数排在前5名的学员学号和分数
SELECT studentNo AS '学员学号',studentResult AS '成绩'
FROM result
WHERE EXISTS(SELECT * #⽤seists语句检查是否有80分以上的成绩
FROM result
WHERE studentResult>80#条件成绩⼤于80
AND subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')#条件成绩为80以上的必须是Logic Java课程
AND examDate=(SELECT MAX(examDate)FROM result WHERE `subjectNO`=(SELECT subjectNo FROM `subject` WHERE subje    ))#条件必须是最近⼀次考试
AND studentNo IN (
SELECT studentNo
FROM result
WHERE examDate=(SELECT MAX(examDate)
FROM result
WHERE `subjectNO`=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')
)
AND studentNo IN (SELECT studentNo
FROM result
WHERE studentResult>80
AND subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')))
ORDER BY studentResult DESC
LIMIT 5
执⾏命令得
3.NOT EXISTS⼦查询
检查“Logic Java”课程最近⼀次考试成绩
如果全部未通过考试（60分及格），认为本次考试偏难，计算的该次考试平均分加5分
SELECT AVG(studentResult)+5
FROM result
WHERE NOT EXISTS(
SELECT studentResult#查询所有考试成绩
FROM result
WHERE studentResult >=60#条件1 成绩要 >=60
AND  subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')# 条件2 科⽬必须是Logic Java
AND examDate=(SELECT MAX(examDate)FROM result WHERE `subjectNO`=(SELECT subjectNo FROM `subject` WHERE subjectName    ))
AND  subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')
AND  examDate=(SELECT MAX(examDate)FROM result WHERE `subjectNO`=(SELECT subjectNo FROM `subject` WHERE subjectName='Lo
4.⼦查询注意事项
<1>任何允许使⽤表达式的地⽅都可以使⽤⼦查询
<2>潜逃在⽗查询SEELECT语句的⼦查询可包括
SELECT⼦句
FROM⼦句
WHERE⼦句
GROUP BY⼦句
HAVING⼦句
<3>只出现在⼦查询中⽽没有出现在⽗查询中的列不能包含在输出列中
⼆、分组查询
1.掌握GROUP BY 字句实现分组查询
语法:
SELECT ...... FROM 表名
<
例:
查询每门课程的平均分，并且按照分数由⾼到低的顺序排列显⽰
SELECT subjectNo,AVG(studentResult) AS '平均分'
FROM result
GROUP BY subjectNo
ORDER BY 平均分 DESC
执⾏命令得
注:SELECT⾥表只中能包含:
<1>.被分组的列
<2>.为每分组返回⼀个值的表达式，如聚合函数
分组统计每个年级男、⼥⽣⼈数
SELECT `gradeID` AS 年级编号,sex AS 性别,COUNT(*) AS ⼈数FROM student
GROUP BY gradeID
执⾏命令得
3.分组筛选
例：获取课程平均分及格的课程编号
SELECT subjectNo,AVG(studentResult)AS 平均分
FROM result
GROUP BY subjectNo#分组
HAVING 平均分>=60#筛选
ORDER BY 平均分 DESC#顺序
执⾏命令得
按出⽣年份分组统计学⽣⼈数，将各组中⼈数达到2⼈的年份和⼈数显⽰出来
SELECT YEAR(bornDate)AS 年份, COUNT(studentNo) AS ⼈数
FROM student
GROUP BY 年份
HAVING ⼈数>2
4.HWERE与HAVING对⽐
WHERE字句:⽤来筛选FROM字句中指定的操作所产⽣的⾏
GROUP BY⼦句:⽤来分组 WHERE字句的输出
HAVING⼦句:⽤来分组的结果中筛选⾏
三、多表连接查询
内连接(INNER JOIN)
外连接
左外连接(L EFT JOIN)
右外连接(RIGHT JOIN)
1.内连接使⽤⽐较运算符根据每个表的通⽤列中的值匹配两个表中的⾏
语法:
<
INNER JOIN 表2
ON ...
直联语法:
<
FROM 表1，表2
WHERE ....
例：查询学⽣(不包括未考的)的考试成绩 显⽰学⽣姓名 科⽬ID  成绩
#内联
SELECT studentName,subjectNo,studentResult
FROM student AS s
INNER JOIN result AS r
ON r.studentNo=s.studentNo
#直联
SELECT studentName,subjectNo,studentResult
FROM student AS s ,result AS r
WHERE s.studentNo=r.studentNo
执⾏命令得
2.三表内连接(多表连接戏法相同)
查询学⽣(不包括未考的)的考试成绩 显⽰学⽣姓名 学⽣编号    成绩 科⽬名(s.studentName 表⽰获取student表⾥的列 防⽌获取不明确报错)
#三表内连接
SELECT s.studentName,s.studentNo,r.studentResult,sub.subjectName FROM student AS s
INNER JOIN result AS r
ON r.studentNo=s.studentNo
INNER JOIN `subject` AS sub
ON r.subjectNo=sub.subjectNo
#三表直连接
SELECT s.studentName,sub.subjectNo,r.studentResult
FROM student AS s ,result AS r,`subject` AS sub
WHERE s.studentNo=r.studentNo
AND sub.subjectNo=r.subjectNo
执⾏命令得
3.左外连接
主表(左表)student中数据逐条匹配表result中的数据
<1>匹配，返回到结果集
<2>⽆匹配.NULL值返回到结果集
例：查取学⽣(包括未考试的学⽣)学⽣姓名 课程编号 考试成绩SELECT studentName,subjectNo,studentResult
FROM student AS s
LEFT JOIN result AS r
ON r.studentNo=s.studentNo
执⾏命令得
4.右外连接
右外连接的原理与左外连接相同
右表逐条去匹配记录；否则NULL填充
执⾏命令得

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。