回顾MySQL查询经典实例2

一时失言乱红尘 2022-10-26 04:57 222阅读 0赞

\#查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩  
\#分析，我们可以根据学生编号将成绩分组，对分组的score求平均值，最后在选取结果中利用AVG函数进行求平均值并判断是否大于等于60即可  
\#一般的联合搜索

select student.sid,sname,avgScore from student,(
        select sid, AVG(score) as avgScore from score 
        GROUP BY sid
        HAVING AVG(score)> 60
        )r
    where student.sid = r.sid;

![20210201220622812.png][]

\#左连接

select s.sid,avgSocre,sname from(
    select sid, AVG(score) as avgSocre from score  
    GROUP BY sid 
    HAVING AVG(score)> 60
    )r left join 
    (select student.sid, student.sname from
    student)s on s.sid = r.sid;

![20210201220638952.png][]

\#右连接

select student.sid, student.sname, r.avgScore from student right join(
          select sid, AVG(score) AS avgScore from score
          GROUP BY sid
          HAVING AVG(score)> 60
    )r on student.sid = r.sid;

![20210201220654836.png][]

\#查询在socre表存在成绩的学生信息

select DISTINCT student.*
    from student,score
    where student.sid=score.sid

![20210201220926259.png][]

\#查询所有同学的学生编号，学生姓名，选课总数，所有课程的成绩综合  
\#联合查询（不会显示没有选课的学生）

![20210201221501392.png][]

\#通过join查询可以显示没有选课的学生（没有选课的学生为NULL）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70][]

\#查有成绩的学生信息  
\#exists

select * from student 
    where exists (select score.sid from score where student.sid = score.sid);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 1][]

\#in

select * from student
    where student.sid in (select score.sid from score);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 2][]

[20210201220622812.png]: /images/20221024/a9d4c895e088410894d8815245e75181.png
[20210201220638952.png]: /images/20221024/889fde0f8147451e8c4bfa48110f049e.png
[20210201220654836.png]: /images/20221024/0a0b9ae6a4a744d1923c2c3e933df114.png
[20210201220926259.png]: /images/20221024/f94c05caf7a34e1ea1e5913523b32c8d.png
[20210201221501392.png]: /images/20221024/3c66dcd68e034338b763e1e1da1eb62c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70]: /images/20221024/41bc81ae88534667b7e2c6948284d923.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 1]: /images/20221024/b1424055d4b04a44bb805736344c28a3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 2]: /images/20221024/28b5e6eecffa4f1a869be5f745ddd63e.png