连接查询和分组查询

最新推荐文章于 2025-02-05 12:28:36 发布

转载最新推荐文章于 2025-02-05 12:28:36 发布 · 55 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/danxun/p/10148307.html

文章标签：

#数据库

本文深入探讨了SQL语言在数据库查询中的应用，包括基础的计数、分组、连接查询，以及进阶的条件筛选、平均分计算和成绩分析。通过具体案例，展示了如何灵活运用SQL解决实际问题。

--切换数据库
use MySchool

--查询对应年级下的学生人数
SELECT COUNT(1) AS '人数' FROM Student WHERE GradeId=1
SELECT COUNT(1) AS '人数' FROM Student WHERE GradeId=2
SELECT COUNT(1) AS '人数' FROM Student WHERE GradeId=3

--分组查询地区对应的人数
--分组查询原理：根据特定列进行分组，找列的相同值
SELECT COUNT(1) AS '人数',Address AS '地区' FROM Student WHERE Address LIKE '北京%' GROUP BY Address

--查询科目的平均分
--WHERE -->GROUP BY -->ORDER BY
SELECT AVG(StudentResult) AS '平均分',Subject.SubjectName FROM Subject,Result WHERE Subject.SubjectId=Result.SubjectId
GROUP BY Subject.SubjectName
ORDER BY AVG(StudentResult) ASC

--查询每学期的男女同学人数
--根据多列进行分组
SELECT COUNT(1) AS '人数',GradeId,Sex FROM Student GROUP BY GradeId,Sex ORDER BY COUNT(1)

--查询科目的平均分大于70
--HAVING和WHERE有什么不同
--where是针对表中原有的记录进行条件筛选
--group by是针对where条件筛选后的记录进行分组
--having是针对group by分组后的记录进行条件筛选

SELECT AVG(StudentResult) AS '平均分',Subject.SubjectName FROM Subject,Result WHERE Subject.SubjectId=Result.SubjectId
GROUP BY Subject.SubjectName HAVING AVG(StudentResult)>=70
ORDER BY AVG(StudentResult)

--连接查询
--1.内连接
SELECT S.StudentName,G.GradeName FROM Student AS S INNER JOIN Grade AS G ON S.GradeId=G.GradeId
--查询学生姓名对应科目和考试分数
--当使用内连接进行多表联查时，直接空格继续JINNER JOIN
SELECT S.StudentName,SU.SubjectName,R.StudentResult FROM Result AS R INNER JOIN Subject AS SU ON (R.SubjectId=SU.SubjectId)
INNER JOIN Student AS S ON (S.StudentNo=R.StudentNo)
--查询学生成绩大于等于65分的记录
--使用连接查询为了指定表与表之间的关系，如果想添加条件那么在指定完关系后加WHERE
SELECT S.StudentName,SU.SubjectName,R.StudentResult FROM Result AS R INNER JOIN Subject AS SU ON (R.SubjectId=SU.SubjectId)
INNER JOIN Student AS S ON (S.StudentNo=R.StudentNo) WHERE R.StudentResult>=65

--2.外连接
--2.1左外链接
SELECT S.StudentName,R.StudentResult,R.SubjectId FROM Result AS R LEFT JOIN Student AS S ON (S.StudentNo=R.StudentNo)
SELECT S.StudentName,G.GradeName FROM Grade AS G LEFT JOIN Student AS S ON(S.GradeId=G.GradeId)
SELECT R.*,S.SubjectName FROM Subject AS S LEFT JOIN Result AS R ON(R.SubjectId=S.SubjectId)
--2.2右外连接
SELECT S.StudentName,R.StudentResult,R.SubjectId FROM Result AS R RIGHT JOIN Student AS S ON (S.StudentNo=R.StudentNo)
SELECT S.StudentName,G.GradeName FROM Student AS S RIGHT JOIN Grade AS G ON(S.GradeId=G.GradeId)
SELECT R.*,S.SubjectName FROM Result AS R RIGHT JOIN Subject AS S ON(R.SubjectId=S.SubjectId)
--内连接和外连接的区别：内连接表与表之间的次序没有分别，指定表与表之间的关系，外链接表顺序有区别，会投影出NULL记录

转载于:https://www.cnblogs.com/danxun/p/10148307.html