连接查询及视图的使用

实验5  连接查询及视图的使用

1. 实验环境：

操作系统：Windows 11 家庭中文版

数据库管理系统：SQL Server 2019

客户端：SQL Server Management Studio 19.3.4.0

数据库模式：MyDB

学生表：Student(Sno,Sname,Ssex,Sage,Sdept)

课程表：Course(Cno,Cname,Cpno,Ccredit)

选课表：SC(Sno,Cno,Grade)

数据库的值：如图2.4（教材52页）

1. 在学生表和学生选课表中，分别使用内连接、左连接、右连接、全连接查询学生的学号、姓名、性别、成绩。

实验代码：

内连接：

select

Student.Sno,

Student.Sname,

Student.Ssex,

SC.Grade

from Student,SC

where Student.Sno =SC.Sno

左连接：

select

Student.Sno,

Student.Sname,

Student.Ssex,

SC.Grade

from Student

LEFT JOIN SC on (Student.Sno =SC.Sno)

右连接：

select

Student.Sno,

Student.Sname,

Student.Ssex,

SC.Grade

from Student

RIGHT JOIN SC on (Student.Sno =SC.Sno)

全连接：

SELECT

    Student.Sno,

    Student.Sname,

    Student.Ssex,

    SC.Grade

FROM

    Student

FULL OUTER JOIN SC ON Student.Sno = SC.Sno

运行结果（依次为内连接、左连接、右连接、全连接）：

内连接：

左连接：

右连接：

全连接：

1. 统计各课程平均分、总分、最高分、最低分、参考人数，查询结果包括课程名称（CourseName）、课程总分（SumScore）、课程平均分（AvgScore）、课程最高分（MaxScore）、课程最低分（MinScore）、参考人数（CourseCount）字段（使用INNER JOIN实现）。

实验代码：

SELECT

    course.Cname,

    SUM(Grade) AS SumScore,

    AVG(Grade) AS AvgScore,

    MAX(Grade) AS MaxScore,

    MIN(Grade) AS MinScore,

    COUNT(Sno) AS CourseCount

FROM

    Course

INNER JOIN SC  ON course.Cno= sc.Cno

GROUP BY

    course.Cname

运行结果：

1. 将上题所写的SQL语句创建成视图（V_GetCourseScore），在视图中查询参考人数大于30小于50的所有课程信息

实验代码：

--首先创建一个视图

CREATE VIEW V_GetCourseScore AS

SELECT

    course.Cname,

    SUM(Grade) AS SumScore,

    AVG(Grade) AS AvgScore,

    MAX(Grade) AS MaxScore,

    MIN(Grade) AS MinScore,

    COUNT(Sno) AS CourseCount

FROM

    Course

INNER JOIN SC  ON course.Cno= sc.Cno

GROUP BY

course.Cname

--然后新建查询，输入如下代码

SELECT *

FROM V_GetCourseScore

WHERE CourseCount > 30 AND CourseCount < 50;

运行结果：

4、创建学生平均成绩视图（V_StudAvgScore），其中包括学生学号（StudNo）、姓名（StudName）、平均分（AvgScore）、总分（SumScore）、最高分（MaxScore）、最低分（MinScore）、课程门数（CountCourse）字段。

实验代码：

--创建一个视图

CREATE VIEW V_StudAvgScore AS

SELECT

    s.Sno AS StudNo,

    s.Sname AS StudName,

    AVG(sc.Grade) AS AvgScore,

    SUM(sc.Grade) AS SumScore,

    MAX(sc.Grade) AS MaxScore,

    MIN(sc.Grade) AS MinScore,

    COUNT(sc.Cno) AS CountCourse

FROM

    Student s

INNER JOIN SC sc ON s.Sno = sc.Sno

GROUP BY

    s.Sno, s.Sname;

运行结果：

5、利用第4题的结果，查询平均分在80~85和60~70的数据记录，其中包括学号、姓名、所在系、平均分、课程门数字段。

实验代码：

SELECT

    StudNo,

    StudName,

    student.Sdept ,

    AvgScore,

    CountCourse

FROM

    V_StudAvgScore,Student

WHERE

    V_StudAvgScore.StudNo=Student.Sno

and

    AvgScore BETWEEN 80 AND 85

    OR

    AvgScore BETWEEN 60 AND 70;

运行结果：

6、利用第4题的结果，查询平均分大于80的学生基本信息，其中包括学号、姓名、性别、年龄、所在系、平均分字段。

实验代码：

SELECT

    Student.Sno,

    Student.Sname,

    student.Ssex,

    student.Sage,

    student.Sdept,

    V_StudAvgScore.AvgScore

FROM

    V_StudAvgScore,Student

WHERE

    V_StudAvgScore.StudNo=Student.Sno

and

    AvgScore >80

   

运行结果：

1. 实验总结：

在做题目的时候，一直在想如何选择视图里面原来没有的属性列，后来想起来，视图可以和表一起做连接查询，这样就使得问题得到了解决

刚开始做第一题时分不清内连接和全连接，不会写全连接，后来翻阅教材询问同学之后，找到了方法