第11章 连接查询和分组查询

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.Subjectld=Result.Subjectld
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.Subjectld=Result.Subjectld
GROUP BY Subject.SubjectName HAVING AVG(StudentResult)>=70
ORDER BY AVG(StudentResult)
--链接查询
--1.内连接
SELECT S.StudentName,G.GradeNema 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.Subjectld=SU.Subjectld)
INNER JOIN Student AS S ON (S.StudentNo=R.StudentNo)
--2.外连接
--2.1左外链接
SELECT S.StudentName,R.StudentResult, R.Subjectld
FROM Result AS R LEFT JOIN Student AS S 
ON (S.StudentNo=R.StudentNo)
SELECT S.StudentName,G.GradeNema 
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.Subjectld=S.Subjectld)
--2.2右外连接
SELECT S.StudentName,R.StudentResult,R.Subjectld 
FROM Result AS R RIGHT JOIN Student AS S
ON (S.StudentNo=R.StudentNo)
SELECT S.StudentName,G.GradeNema 
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.Subjectld=S.Subjectld)