Mysql高级查询语句

作者: Recorder_MZou | 来源:发表于2019-09-27 16:35 被阅读0次

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 的区别

image

1. IN和Not IN 做的是一个区间的判断，查询数据是否在区间内

    Exists 和 Not Exists 都是根据查询语句返回 true 或者 false !

2. 例子：


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中查询数据！

很显然，如果内层查询的数量大，查询效率就慢，查询数据量小，效率就高！

例子：

select  a.*
from  A  a
where exists (select id 
                from B b
               where a.id=b.id)

如果:
A表中有1000条数据
B表中有1000条数据

分析步骤：

  1. 使用了exists（会以外层查询为驱动）上面的sql语句只会执行1000次（因为A表中有多少条数据，就会执行几次）

  2. exists查询不需要数据的结果集，只需要返回true或者false

结论：

子查询中涉及的表（B）数据量大于主查询中涉及的表（A）数据量时，使用exists来查询！可以提高效率

Exists查询做的是loop循环

如果子查询中涉及的表（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

多表连接查询

比如之前写的小例子，查询学生的成绩，我们获取的是学生编号和成绩！

但是如果获取了学生姓名和成绩岂不是更好？但是学生姓名和成绩不在一张表中！

这时候就需要我们的连接查询！

  常用的连接查询方式：

内连接
外连接

内连接

内连接是典型的最常用的连接查询！特点就是两个表中存在主外健关系时，通常使用！查询两张表中共同的数据！内连接的实现方式有两种：

在where条件中指定连接条件,比如查询学生姓名以及对应的年级名称


Select  studentName,gradeName
From student,grade
Where student.gradeId=grade.gradeId

2.在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之后再返回到结果集中！外连接查询又分为：

1.左外连接 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`

2.右外连接 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`

大千世界，求同存异；相遇是缘，相识是份，相知便是“猿粪”(缘分)
From MZou

Mysql高级查询语句

Exists子查询

Not Exists子查询

Exists与IN , Not Exists与Not IN 的区别

any,

分组查询

多表连接查询

内连接

外连接

1.左外连接 Left outer join

2.右外连接 right outer join

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读