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`

 

转载于:https://www.cnblogs.com/yunfeioliver/p/9416092.html