mysql高级查询
Exists子查询
Exists的特点
1.在执行create或drop语句前，可以使用exists语句来判断数据库对象是否存在，返回值是true或false
比如
drop table if exists student; 意思是如果存在表student则删除！否则不删除！
Create table if not exists student; 意思是如果不存在表student则创建，否则不创建！
2.exists还可以作为where条件的子查询
Select ..... from 表名 where exists （子查询）;
意思是：
如果子查询有结果，则返回值为true，继续执行外层的查询语句;
如果子查询没有结果，则返回值是false,外层的查询语句不会执行。
-- 检查“高等数学-1” 课程最近一次考试成绩
-- 如果有 80分以上的成绩，显示分数排在前5名的学员学号和分数
-- 不使用exists
-- 01.查询“高等数学-1” 课程 对应的编号
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
-- 02.查询最近的考试成绩
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
-- 03. 在02的基础上 加条件 成绩大于80
SELECT * FROM result
WHERE ExamDate=
(SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'))
AND StudentResult>80
-- 04.优化
SELECT studentNo,StudentResult FROM result
WHERE ExamDate=
(SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'))
AND StudentResult>80
ORDER BY StudentResult DESC
LIMIT 0,5
-- 使用exists
-- 检查“高等数学-1” 课程最近一次考试成绩
-- 如果有 80分以上的成绩，显示分数排在前5名的学员学号和分数
-- 01.查询“高等数学-1” 课程 对应的编号
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
-- 02.查询最近的考试成绩
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
-- 03.查询学号和成绩
SELECT StudentNo,StudentResult FROM result
WHERE EXISTS
(
SELECT * FROM result
WHERE subjectNo=(
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
)
AND ExamDate=(
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
)
AND StudentResult>80
)
AND subjectNo=(
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
)
AND ExamDate=(
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
)
ORDER BY StudentResult DESC
LIMIT 0,5
Not Exists子查询
-- 检查“高等数学-1”课程最近一次考试成绩
-- 如果全部未通过考试（60分及格），认为本次考试偏难，计算的该次考试平均分加5分
-- 01.查询“高等数学-1” 课程 对应的编号
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
-- 02.查询最近的考试成绩
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
-- 03.查询成绩大于60的 反着来
SELECT StudentResult FROM result
WHERE StudentResult>60
AND SubjectNo=(
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
)
AND ExamDate=(
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
)
-- 04. 如果全部未通过考试，考试平均分加5分
SELECT AVG(StudentResult)+5 FROM result
WHERE NOT EXISTS
(
SELECT StudentResult FROM result
WHERE StudentResult>60
AND SubjectNo=(
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
)
AND ExamDate=(
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
)
)
AND SubjectNo=(
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
)
AND ExamDate=(
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
)
-- 如果有 年级名称是大二 的学生，就 查询出 年级名称是大一的 所有学生信息
-- 01.先查询出 对应的年级编号
SELECT GradeId FROM grade WHERE GradeName='大一'
SELECT GradeId FROM grade WHERE GradeName='大二'
-- 02.在学生表中是否存在 年级名称是大二 的学生
SELECT * FROM student WHERE gradeID=(
SELECT GradeId FROM grade WHERE GradeName='大二'
)
-- 03.如果有查询出 年级名称是大一的 所有学生信息
SELECT * FROM student
WHERE EXISTS
(
SELECT * FROM student WHERE gradeID=(
SELECT GradeId FROM grade WHERE GradeName='大二'
)
)
AND GradeId=(
SELECT GradeId FROM grade WHERE GradeName='大一'
)
Exists与IN , Not Exists与Not IN 的区别
01. IN和Not IN 做的是一个区间的判断，查询数据是否在区间内
Exists和Not Exists都是根据查询语句返回true或者false!
02. 例子：
select a.* from A a
where a.id in(select id from B)
如果A表中有1000条数据
B表中有1000条数据
分析步骤：
01. select id from B 会查询出B表中的所有id，然后缓存起来，共1000条（因为使用in 会先执行子查询）
02.然后分别拿A表中的每一个id和B表中的1000个id进行比较，也就是比较了 1000*1000次
03.这样效率是非常慢的
04.如果B表中只有100或者10条数据（只是举例说明数据量小），那么就会比较1000*10
这样相对来说效率会高点！
结论：
子查询中涉及的表（B）数据量小于 主查询中涉及的表（A）数据量 时，使用In来查询！可以提高效率
In 查询做的是 外表和内表的hash连接
hash连接就是 以 外层查询的表作为hash table ，内层查询的表在hash table中查询数据！
很显然，如果内层查询的数量大 ，查询效率就慢，查询数据量小，效率就高！
03. 例子：
select a.* from A a
where exists (select id from B b where a.id=b.id)
如果A表中有1000条数据
B表中有1000条数据
分析步骤：
01. 使用了exists（会以外层查询为驱动）上面的sql语句只会执行1000次（因为A表中有多少条数据，就会执行几次）
02. exists查询不需要数据的结果集，只需要返回true或者false
结论：
子查询中涉及的表（B）数据量大于 主查询中涉及的表（A）数据量时，使用exists来查询！可以提高效率
Exists查询做的是loop循环
04. 如果子查询中涉及的表（B）数据量 和 主查询中涉及的表（A）数据量 差不多时，建议使用IN来查询！
因为In查询是在内存中的查询，exists需要查询数据库，所以内存中的查询肯定比查询数据库性能高！
05.not exists 在任何时候都比not in 效率高！
因为not in 那么内外表都进行全表扫描，没有用到索引！而not exists的子查询仍然可以用到索引！
any,some,all的使用
SELECT * FROM student
WHERE studentname
IN(SELECT studentName FROM student)
-- 使用any（只要是在区间就行） 替换in
SELECT * FROM student
WHERE studentname=ANY(SELECT studentName FROM student)
-- all 满足子查询中编号最大的
SELECT * FROM student WHERE
studentNo>ALL
(SELECT studentNo FROM student WHERE studentNo IN(1014,1001,1002))
-- any 满足子查询中编号最小的
SELECT * FROM student WHERE
studentNo>ANY
(SELECT studentNo FROM student WHERE studentNo IN(1014,1001,1002))
-- 和 any 效果一致
SELECT * FROM student WHERE
studentNo>SOME
分组查询
-- 分组 group by
-- 01. 查询 每门课程的名称 以及平均分
-- 并且按照平均分降序排列
SELECT subjectName,AVG(StudentResult)
FROM `result` r,`subject` s
WHERE
r.`SubjectNo`=s.`SubjectNo`
GROUP BY subjectName
ORDER BY AVG(StudentResult) DESC
-- 02.在上述案例中增加 条件 having
-- 平均分>73的 显示
SELECT subjectName,AVG(StudentResult)
FROM `result` r,`subject` s
WHERE
r.`SubjectNo`=s.`SubjectNo`
GROUP BY subjectName
HAVING AVG(StudentResult)>73
ORDER BY AVG(StudentResult) DESC
-- 03.统计每个年级的男女人数 多列进行分组
SELECT gradeid '年级编号',COUNT(sex) '性别人数',sex '性别'
FROM student
WHERE sex IS NOT NULL AND gradeid IS NOT NULL
GROUP BY gradeid,sex
-- 04. 找出每个课程成绩的前三名
SELECT * FROM result r1
WHERE
(
SELECT COUNT(1) FROM result r2
WHERE r1.subjectNo=r2.`SubjectNo`
AND r1.studentresult<r2.studentresult
)<3
ORDER BY subjectNo,studentResult DESC
多表连接查询
比如之前写的小例子，查询学生的成绩，我们获取的是学生编号和成绩！
但是如果获取了学生姓名和成绩岂不是更好？ 但是学生姓名和成绩不在一张表中！
这时候就需要我们的连接查询！
常用的连接查询方式：
01. 内连接
02. 外连接
内连接
内连接是典型的最常用的连接查询！ 特点就是两个表中存在主外健关系时，通常使用！
查询两张表中共同的数据！
内连接的实现方式有两种：
01. 在where条件中指定连接条件
比如 查询学生姓名以及对应的年级名称
Select studentName,gradeName
from student,grade
Where student.gradeId=grade.gradeId
02.在form 子句中增加 inner join 表 on 关系
比如查询学生姓名，科目名称以及考试成绩
SELECT studentName,subjectName,studentresult
FROM student s
INNER JOIN result r ON s.studentNo=r.studentNo
INNER JOIN `subject` su ON su.subjectNo=r.subjectNo
注意点：
001.inner可以省略
002.inner join 用来连接两个表
003.on用来设置条件
004. s r su是用的别名
外连接
外连接查询是至少返回一个表中的所有记录，根据匹配条件有选择地返回另一张表的数据！
外连接有主表和从表的概念！
以主表为准匹配从表的数据，符合连接条件的数据直接返回到结果集中，不符合的数据将被赋予null之后再返回到结果集中！
外连接查询又分为：
01.左外连接 Left outer join
以左表为主表，从表（右边的表）中如果没有匹配的数据返回null
例子1： 查询学生的姓名，考试科目以及成绩！
SELECT studentName,subjectNo,studentResult FROM student s
LEFT JOIN result r ON r.`studentNo`=s.`studentNo`
例子2：查询所有科目对应的学生成绩
SELECT subjectName,s.subjectNo,studentResult FROM
`subject` s LEFT JOIN result r
ON s.`SubjectNo`=r.`SubjectNo`
02.右外连接 right outer join
以右表为主表，从表（左边的表）中如果没有匹配的数据返回null
例子：查询年级名称和学生名称 两个结果是否一致？
SELECT gradeName,studentName FROM grade
RIGHT JOIN student ON grade.`GradeID`=student.`GradeId`
SELECT gradeName,studentName FROM grade
INNER JOIN student ON grade.`GradeID`=student.`GradeId`
