mysql中嵌套查询分数大于70分的人数_MySQL 多表连接

系统管理员 2023-01-04 11:59 178阅读 0赞

![dfd8ae0952ee4aaac9d419c4855faa61.png][]

建表代码在这里

May：看完这篇文章保证你学会sql的增删改zhuanlan.zhihu.com

![21127e15be7b57b7d1f46bc9e8e2c460.png][]

多表连接讲解在这里

May：一看就懂MySQL繁琐的合并表zhuanlan.zhihu.com

![5018430c901bcb3d56b79f349f12f2eb.png][]

查询题目及讲解

\#1.将stu表和sc表做连接：

内连接

SELECT*FROM stu JOIN sc on stu.s_id=sc.s_id;

![5768769774f6874cfd0c4bc07b748ee2.png][]

左连接

SELECT*FROM stu LEFT JOIN sc on stu.s_id=sc.s_id;

![f4f252b6f330bf1df9a100a49f4a2c1b.png][]

右连接

SELECT*FROM stu RIGHT JOIN sc on stu.s_id=sc.s_id;

![c7e1f1c5c5c4ea977c6b4e34c0be228e.png][]

\#2.将四表做连接，删除不必要字段：

SELECT stu.*,sc.c_id,sc.score,co.c_name,te.t_name FROM
    stu join sc on stu.s_id=sc.s_id
    JOIN co on sc.c_id=co.c_id
    JOIN te on co.t_id=te.t_id;

![76f5fe6978c6a25054d5d463d1200561.png][]

\#3.将stu表和stu表做纵向链接：

SELECT * FROM stu a
    union all 
    SELECT * FROM stu b;

![9ac6074052a93024dd6190bf0f284e39.png][]

\#4.将stu表的s\_id、s\_name与co表的c\_id、c\_name做纵向链接：

SELECT s_id,s_name FROM stu 
    UNION all
    SELECT c_id,co.c_name FROM co ;

![95c923926114f2050fbaa5207b080931.png][]

\#5.查询课程名称为“数学”，且分数低于60的学生姓名和分数；

逻辑树分析方法将问题分解：

1、显示结果：学生姓名和分数，来自两个表，做表连接，因为没有要求所有学生或所有课程做内连接；

2、条件：查询课程名称为“数学”，且分数低于60

SELECT s_name,score
    FROM stu
    JOIN sc on stu.s_id=sc.s_id
    JOIN co on sc.c_id=co.c_id
    WHERE c_name='数学' and score<60;

![540f31cce8dcec559059e7754682cbc5.png][]

\#6.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

1、显示结果：学生的学号和姓名

2、因为有成绩和学生信息，又没有特别要求，做内连接

3、条件：课程编号为01且课程成绩在80分以上

SELECT s_name,stu.s_id
    FROM stu
    JOIN sc on stu.s_id=sc.s_id
    WHERE sc.c_id='01' and sc.score>=80;

![523ad795d99ec036cfeff35137e048cf.png][]

\#7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩；

1、显示结果：学生编号、学生姓名、选课总数、所有课程的总成绩

2、表连接：所有同学，所以stu 作左表左连接

SELECT stu.s_id,stu.s_name,count(c_id)选课总数,IFNULL(SUM(score),0)总成绩
    FROM stu
    LEFT JOIN sc on stu.s_id=sc.s_id
    GROUP BY stu.s_id;

![e5f86f666850b7ef40ff8cb37068da88.png][]

\#8.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩；

SELECT stu.s_id,stu.s_name,AVG(score)平均成绩
    FROM stu
    JOIN sc on stu.s_id=sc.s_id
    GROUP BY stu.s_id
    HAVING 平均成绩>=60;

![b8d6b38324190134ee6aaddbfd056b24.png][]

\#9.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩；

1、显示结果：学号，姓名及其平均成绩

2、连接：没有特别要求内连接

3、条件两门及以上不及格：说明要通过学号分组；小于60分的课程数量大于等于2

SELECT stu.s_id,stu.s_name,AVG(score)平均成绩
    FROM stu
    JOIN sc on stu.s_id=sc.s_id
    GROUP BY stu.s_id
    HAVING SUM(score<60)>=2;

![a636d5c095e7b9bd6e332a2e833bbe65.png][]

\#10.检索“01”课程分数小于60，按分数降序排列的学生信息；

1、显示结果：学生信息

2、连接：内连接

3、条件：“01”课程分数小于60

SELECT stu.*  
    FROM stu
    JOIN sc on stu.s_id=sc.s_id
    WHERE  c_id='01' and sc.score<60
    ORDER BY score DESC;

![cbe84b564df93d83e901c7dcd43cf909.png][]

\#11.查询学过“张三”老师授课的同学的信息；

1、显示结果：同学信息

2、连接：有老师名字，有同学信息，所以要stu、sc、co做内连接

3、条件：学过“张三”老师授课

SELECT stu.*  
    FROM stu
    JOIN sc on stu.s_id=sc.s_id
    JOIN co on sc.c_id=co.c_id
    JOIN te on co.t_id=te.t_id
    WHERE te.t_name='张三';

![7b1389101643711033e9faeb3d3b489a.png][]

\#12.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩；

1、显示结果：所有学生的所有课程的成绩以及平均成绩

2、连接：所有学生 左连接

3、条件按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩；

根本含义：所有学生的成绩和平均成绩一起显示；

所有学生成绩好显示，但是平均成绩是汇总字段；

要一起显示的话，

方法1：通过行换列，sum（if（））实现，SUM((c*id='01')\*score)* 等同于 SUM(if(c\_id=01, 1,0)\*score)；

方法二：通过子查询查到每个学号的平均成绩，再进行内连接显示；

方法一：

SELECT stu.s_name,sc.c_id,
    SUM((c_id='01')*score)1号课程成绩,
    SUM((c_id='02')*score)2号课程成绩,
    SUM((c_id='03')*score)3号课程成绩,
    AVG(score)平均成绩
    FROM stu
    left JOIN sc on stu.s_id=sc.s_id
    GROUP BY stu.s_id 
    ORDER BY 平均成绩;

![fbee09ee5e32296d5f2dcf19f6fa9493.png][]

方法二：

SELECT sc.*,平均成绩
    FROM sc 
    JOIN
    (SELECT s_id,avg(score)平均成绩 FROM sc GROUP BY s_id)a
    on sc.s_id=a.s_id;

![52418fbcf07ba39630c9edaca218d2a0.png][]

\#13.查询不同老师所教不同课程平均分从高到低显示；

不同老师所教不同课程：凡是看到不同和每，基本上都是需要用group by 分组。

SELECT te.t_name,co.c_name,AVG(score)平均成绩
    FROM stu
    JOIN sc on stu.s_id=sc.s_id
    JOIN co on sc.c_id=co.c_id
    JOIN te on co.t_id=te.t_id
    GROUP BY te.t_name,co.c_id
    ORDER BY 平均成绩 DESC;

![f8507e32f6f08d46d44ec0581dd78543.png][]

\#14.查询出只有两门课程的全部学生的学号和姓名；

1、显示结果：学号和姓名

2、连接：全部学生，左连接

3、只学了两门课程，通过学号分组，课程数为2的学生

SELECT stu.s_id,stu.s_name
    FROM stu
    LEFT JOIN sc on stu.s_id=sc.s_id
    GROUP BY s_id
    HAVING count(c_id)=2;

![58f73009693f349d8a0445e0f5913b46.png][]

\#15.查询任意一门课程成绩在70分以上的姓名、课程名称和分数；

1、显示结果：姓名、课程名称和分数

2、条件：任意一门课程成绩在70分以上，所有课程成绩都大于70分；

方法一：也就是大于70分的课程数等于学生选的所有课程数量。

方法二：最小成绩大于70分，所有课程成绩就都大于70分

SELECT stu.s_name,co.c_name,AVG(sc.score)平均成绩
    FROM stu
    JOIN sc on stu.s_id=sc.s_id
    JOIN co on sc.c_id=co.c_id
    GROUP BY stu.s_id
    HAVING SUM(score>70)=COUNT(sc.c_id);

![f68211c831c9ab031c50117b4d6d9b26.png][]

SELECT stu.s_name,co.c_name,AVG(sc.score)平均成绩
    FROM stu
    JOIN sc on stu.s_id=sc.s_id
    JOIN co on sc.c_id=co.c_id
    GROUP BY stu.s_id
    HAVING MIN(score）>70;

![f68211c831c9ab031c50117b4d6d9b26.png][]

\#16.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率（score>=60），中等率（60<score<80），优良率(80<=score<90)，优秀率(score>=90)；

SELECT a.c_id,b.c_name,MAX(score)最高分,MIN(score)最低分,AVG(score)平均分,
    COUNT(DISTINCT case when score>=60 then s_id else null end)/count(*)及格率,
    COUNT(DISTINCT case when score>60  and score<80 then s_id else null end)/count(*)中等率,
    COUNT(DISTINCT case when score>=80 and score<90 then s_id else null end)/count(*)优良率,
    COUNT(DISTINCT case when score>=90 then s_id else null end)/count(*)优秀率
    FROM sc a
    JOIN 
    co b
    on a.c_id=b.c_id
    GROUP BY a.c_id;

![a816c82d34ee91b3a6ce9ebcda192bac.png][]

\-- 查询题目

\#1.将stu表和sc表做连接：

\#2.将四表做连接，删除不必要字段：

\#3.将stu表和stu表做纵向链接：

\#4.将stu表的s\_id、s\_name与co表的c\_id、c\_name做纵向链接：

\#5.查询课程名称为“数学”，且分数低于60的学生姓名和分数；

\#6.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

\#7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩；

\#8.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩；

\#9.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩；

\#10.检索“01”课程分数小于60，按分数降序排列的学生信息；

\#11.查询学过“张三”老师授课的同学的信息；

\#12.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩；

\#13.查询不同老师所教不同课程平均分从高到低显示；

\#14.查询出只有两门课程的全部学生的学号和姓名；

\#15.查询任意一门课程成绩在70分以上的姓名、课程名称和分数；

[dfd8ae0952ee4aaac9d419c4855faa61.png]: /images/20221119/683206bee1b14c24b41a4212743964d6.png
[21127e15be7b57b7d1f46bc9e8e2c460.png]: /images/20221119/c54b36d1177f437a80fcab42469fc78b.png
[5018430c901bcb3d56b79f349f12f2eb.png]: https://img-blog.csdnimg.cn/img_convert/5018430c901bcb3d56b79f349f12f2eb.png
[5768769774f6874cfd0c4bc07b748ee2.png]: https://img-blog.csdnimg.cn/img_convert/5768769774f6874cfd0c4bc07b748ee2.png
[f4f252b6f330bf1df9a100a49f4a2c1b.png]: https://img-blog.csdnimg.cn/img_convert/f4f252b6f330bf1df9a100a49f4a2c1b.png
[c7e1f1c5c5c4ea977c6b4e34c0be228e.png]: https://img-blog.csdnimg.cn/img_convert/c7e1f1c5c5c4ea977c6b4e34c0be228e.png
[76f5fe6978c6a25054d5d463d1200561.png]: https://img-blog.csdnimg.cn/img_convert/76f5fe6978c6a25054d5d463d1200561.png
[9ac6074052a93024dd6190bf0f284e39.png]: https://img-blog.csdnimg.cn/img_convert/9ac6074052a93024dd6190bf0f284e39.png
[95c923926114f2050fbaa5207b080931.png]: https://img-blog.csdnimg.cn/img_convert/95c923926114f2050fbaa5207b080931.png
[540f31cce8dcec559059e7754682cbc5.png]: https://img-blog.csdnimg.cn/img_convert/540f31cce8dcec559059e7754682cbc5.png
[523ad795d99ec036cfeff35137e048cf.png]: https://img-blog.csdnimg.cn/img_convert/523ad795d99ec036cfeff35137e048cf.png
[e5f86f666850b7ef40ff8cb37068da88.png]: https://img-blog.csdnimg.cn/img_convert/e5f86f666850b7ef40ff8cb37068da88.png
[b8d6b38324190134ee6aaddbfd056b24.png]: https://img-blog.csdnimg.cn/img_convert/b8d6b38324190134ee6aaddbfd056b24.png
[a636d5c095e7b9bd6e332a2e833bbe65.png]: https://img-blog.csdnimg.cn/img_convert/a636d5c095e7b9bd6e332a2e833bbe65.png
[cbe84b564df93d83e901c7dcd43cf909.png]: https://img-blog.csdnimg.cn/img_convert/cbe84b564df93d83e901c7dcd43cf909.png
[7b1389101643711033e9faeb3d3b489a.png]: https://img-blog.csdnimg.cn/img_convert/7b1389101643711033e9faeb3d3b489a.png
[fbee09ee5e32296d5f2dcf19f6fa9493.png]: https://img-blog.csdnimg.cn/img_convert/fbee09ee5e32296d5f2dcf19f6fa9493.png
[52418fbcf07ba39630c9edaca218d2a0.png]: https://img-blog.csdnimg.cn/img_convert/52418fbcf07ba39630c9edaca218d2a0.png
[f8507e32f6f08d46d44ec0581dd78543.png]: https://img-blog.csdnimg.cn/img_convert/f8507e32f6f08d46d44ec0581dd78543.png
[58f73009693f349d8a0445e0f5913b46.png]: https://img-blog.csdnimg.cn/img_convert/58f73009693f349d8a0445e0f5913b46.png
[f68211c831c9ab031c50117b4d6d9b26.png]: https://img-blog.csdnimg.cn/img_convert/f68211c831c9ab031c50117b4d6d9b26.png
[a816c82d34ee91b3a6ce9ebcda192bac.png]: https://img-blog.csdnimg.cn/img_convert/a816c82d34ee91b3a6ce9ebcda192bac.png