数据库多表查询count

时间: 2023-12-07 11:05:34 浏览: 91

sql 多表查询

在数据库管理中，多表查询是一项关键技能，它允许我们从多个相关联的表中提取信息，从而构建出更复杂、更丰富的数据视图。基于给定的学生信息表(S)、课程信息表(C)和学生成绩表(SC)，我们将探讨一系列多表查询案例，旨在深入理解SQL中的JOIN操作和其他高级查询技巧。 ### 1. 查询所有选课学生的学号，姓名，选课名称及成绩此查询涉及S和SC两个表，通过`INNER JOIN`将学生表与成绩表连接起来，确保只返回那些在SC表中有记录的学生信息。 ```sql SELECT S.Sno, S.Sname, C.Cname, SC.Score FROM S INNER JOIN SC ON S.Sno = SC.Sno INNER JOIN C ON SC.Cno = C.Cno; ``` ### 2. 查询每门课程的课程号，任课教师及其选课人数这里需要计算每个课程被选修的次数，通常使用`GROUP BY`结合`COUNT()`函数实现。 ```sql SELECT C.Cno, C.Cteacher, COUNT(SC.Sno) AS '选课人数' FROM C LEFT JOIN SC ON C.Cno = SC.Cno GROUP BY C.Cno, C.Cteacher; ``` ### 3. 查询所有选修了VB课程的学生的姓名我们需要确定VB课程的课程号，然后通过JOIN操作找到所有选修该课程的学生。 ```sql SELECT S.Sname FROM S INNER JOIN SC ON S.Sno = SC.Sno INNER JOIN C ON SC.Cno = C.Cno WHERE C.Cname = 'VB'; ``` ### 4. 查询同时选修数字逻辑和编译原理的学生姓名及所在系这需要使用子查询来找出符合条件的学生学号，然后再查询他们的详细信息。 ```sql SELECT S.Sname, S.Dept FROM S WHERE EXISTS ( SELECT * FROM SC AS SC1 WHERE S.Sno = SC1.Sno AND SC1.Cno IN ( SELECT Cno FROM C WHERE Cname IN ('数字逻辑', '编译原理') ) ) GROUP BY S.Sname, S.Dept HAVING COUNT(DISTINCT SC.Cno) = 2; ``` ### 5. 查询所有学生的学号，姓名，选课名称和成绩（没有选课的同学信息为空）使用`LEFT JOIN`可以包括学生表中所有学生的信息，即使他们没有选课记录。 ```sql SELECT S.Sno, S.Sname, C.Cname, SC.Score FROM S LEFT JOIN SC ON S.Sno = SC.Sno LEFT JOIN C ON SC.Cno = C.Cno; ``` ### 6. 查询与李四同龄的其他学生的姓名，年龄，地址和电话首先找到李四的年龄，然后查询所有年龄相同的其他学生。 ```sql SELECT Sname, Sage, Address, Tel FROM S WHERE Sage = ( SELECT Sage FROM S WHERE Sname = '李四' ) AND Sname != '李四'; ``` ### 7. 查询成绩在70-90之间的学生姓名，课程名称及成绩利用`BETWEEN`操作符筛选成绩范围。 ```sql SELECT S.Sname, C.Cname, SC.Score FROM S INNER JOIN SC ON S.Sno = SC.Sno INNER JOIN C ON SC.Cno = C.Cno WHERE SC.Score BETWEEN 70 AND 90; ``` ### 8. 查询选修2门以上的学生姓名使用子查询先找出每位学生选修课程的数量，再筛选出符合条件的学生。 ```sql SELECT Sname FROM ( SELECT S.Sname, COUNT(SC.Cno) AS '课程数量' FROM S INNER JOIN SC ON S.Sno = SC.Sno GROUP BY S.Sname ) AS T WHERE T.课程数量 >= 2; ``` ### 9. 查询至少有3个同学选修的课程名与第2点类似，但筛选条件不同。 ```sql SELECT Cname FROM ( SELECT C.Cname, COUNT(SC.Sno) AS '选课人数' FROM C LEFT JOIN SC ON C.Cno = SC.Cno GROUP BY C.Cname ) AS T WHERE T.选课人数 >= 3; ``` ### 10. 查询C003科成绩比该科平均成绩高的学生的成绩表首先计算C003课程的平均成绩，然后找出成绩高于平均分的学生。 ```sql WITH AverageScore AS ( SELECT AVG(SC.Score) AS '平均成绩' FROM SC WHERE SC.Cno = 'C003' ) SELECT S.Sname, SC.Score FROM S INNER JOIN SC ON S.Sno = SC.Sno WHERE SC.Cno = 'C003' AND SC.Score > (SELECT * FROM AverageScore); ``` ### 11. 查询选修了06003号学生选修了的课程的学生学号和姓名利用子查询找出06003号学生选修的所有课程，再查询所有选修这些课程的学生。 ```sql SELECT S.Sno, S.Sname FROM S INNER JOIN SC ON S.Sno = SC.Sno WHERE SC.Cno IN ( SELECT SC.Cno FROM SC WHERE SC.Sno = '06003' ) GROUP BY S.Sno, S.Sname; ``` ### 12. 查询选修C003号课程且成绩高于C002号平均成绩的学生姓名及成绩计算C002的平均成绩，再查找符合条件的学生。 ```sql WITH C002Average AS ( SELECT AVG(SC.Score) AS 'C002平均成绩' FROM SC WHERE SC.Cno = 'C002' ) SELECT S.Sname, SC.Score FROM S INNER JOIN SC ON S.Sno = SC.Sno WHERE SC.Cno = 'C003' AND SC.Score > (SELECT * FROM C002Average); ``` ### 13. 查询所有未选修C010号课程的学生名单使用`LEFT JOIN`和`IS NULL`检查哪些学生没有C010的记录。 ```sql SELECT DISTINCT S.Sname FROM S LEFT JOIN SC ON S.Sno = SC.Sno WHERE SC.Cno IS NULL OR SC.Cno != 'C010'; ``` ### 14. 查询每个同学各门课程的平均成绩和最高成绩，并降序排列输出课程号，课程名称，平均成绩及最高成绩利用聚合函数`AVG()`和`MAX()`，结合`GROUP BY`进行分组统计。 ```sql SELECT C.Cno, C.Cname, AVG(SC.Score) AS '平均成绩', MAX(SC.Score) AS '最高成绩' FROM C INNER JOIN SC ON C.Cno = SC.Cno GROUP BY C.Cno, C.Cname ORDER BY '平均成绩' DESC, '最高成绩' DESC; ``` ### 15. 查询每门课程的最高成绩，最低成绩的学生姓名及分数需分别找出每门课程的最高和最低成绩，然后查询对应的学生信息。 ```sql WITH MaxMinScores AS ( SELECT SC.Cno, MAX(SC.Score) AS '最高成绩', MIN(SC.Score) AS '最低成绩' FROM SC GROUP BY SC.Cno ) SELECT C.Cname, M.Sname AS '最高成绩学生', L.Sname AS '最低成绩学生', MS.最高成绩, MS.最低成绩 FROM C LEFT JOIN SC AS M ON C.Cno = M.Cno AND M.Score = ( SELECT 最高成绩 FROM MaxMinScores WHERE MaxMinScores.Cno = C.Cno ) LEFT JOIN SC AS L ON C.Cno = L.Cno AND L.Score = ( SELECT 最低成绩 FROM MaxMinScores WHERE MaxMinScores.Cno = C.Cno ) LEFT JOIN MaxMinScores AS MS ON C.Cno = MS.Cno; ``` ### 16. 查询所有学生都选修了的课程号和课程名这是一个复杂的查询，需要确定所有学生都有的课程，可以通过检查每门课程是否都有学生的记录来实现。 ```sql SELECT C.Cno, C.Cname FROM C WHERE NOT EXISTS ( SELECT * FROM S WHERE NOT EXISTS ( SELECT * FROM SC WHERE SC.Sno = S.Sno AND SC.Cno = C.Cno ) ); ``` ### 17. 查询选修了课程的学生人数简单地使用`COUNT(DISTINCT)`函数。 ```sql SELECT COUNT(DISTINCT S.Sno) AS '选课学生人数' FROM S INNER JOIN SC ON S.Sno = SC.Sno; ``` ### 18. 查询计算机系学生总人数使用`WHERE`子句过滤计算机系的学生，然后计数。 ```sql SELECT COUNT(*) AS '计算机系学生总数' FROM S WHERE S.Dept = '计算机系'; ``` ### 19. 查询各个课程号以及相应的选课人数与第2点相同，但直接返回课程号和选课人数。 ```sql SELECT C.Cno, COUNT(SC.Sno) AS '选课人数' FROM C LEFT JOIN SC ON C.Cno = SC.Cno GROUP BY C.Cno; ``` ### 20. 查询选择学科超过1门的学生学号和姓名类似于第8点，但直接返回学号和姓名。 ```sql SELECT S.Sno, S.Sname FROM S INNER JOIN SC ON S.Sno = SC.Sno GROUP BY S.Sno, S.Sname HAVING COUNT(SC.Cno) > 1; ``` 以上查询覆盖了多表查询的多种场景，从简单的信息提取到复杂的统计分析，展示了SQL的强大功能。通过这些练习，我们可以更好地理解和掌握SQL的JOIN操作和聚合函数，提高处理复杂数据库查询的能力。

多表查询count可以使用联结查询和聚合函数来实现。例如，查询某个学校的所有学生和他们的选课情况，可以使用以下SQL语句： ``` SELECT students.name, COUNT(courses.id) AS course_count FROM students LEFT JOIN course_selections ON students.id = course_selections.student_id LEFT JOIN courses ON course_selections.course_id = courses.id WHERE students.school = 'xxx' GROUP BY students.id ``` 其中，LEFT JOIN用于连接学生表、选课表和课程表，WHERE子句限制了学校名称，GROUP BY子句按照学生ID分组，COUNT函数统计每个学生选课的数量。

阅读全文

数据库多表查询count

相关推荐

\Access数据库的多表查询

SQL多表查询

数据库多表查询.docx

数据库多表查询及嵌套查询子查询语句示例

SQL数据库单表查询练习题

易语言-ACCESS数据库多表关联查询例程

数据库多表并查

数据库程序设计-多表查询

C#编程 数据库操作应用 FindCount(源码)(源码)

数据库中Count的使用

数据库de表

mysql数据库的多表设计和各种查询(连接查询\子查询\联合查询\报表查询)，以及数据库备份和恢复

如何查询数据库中表的字段信息

数据库简单查询课件 数据库简单查询课件

数据库查询

数据库SQL server查询

多表查询.sql

最新推荐

Windchill_数据库表说明.doc

Java实现获得MySQL数据库中所有表的记录总数可行方法

数据库实验一 基于Sakila的数据库操作

MYSQL中统计查询结果总行数的便捷方法省去count(*)

查询oracle表以及统计数据库的使用情况

Angular实现MarcHayek简历展示应用教程

管理建模和仿真的文件

深入剖析：内存溢出背后的原因、预防及应急策略（专家版）

Java中如何对年月日时分秒的日期字符串作如下处理：如何日期分钟介于两个相连的半点之间，就将分钟数调整为前半点

Crossbow Spot最新更新 - 获取Chrome扩展新闻

C#编程数据库操作应用 FindCount(源码)(源码)

数据库简单查询课件数据库简单查询课件

数据库实验一基于Sakila的数据库操作