【SQL实验】高级查询（二）

** 完整代码在文章末尾【文中可能会有不准确或待完善之处，恳请各位读者不吝批评指正，共同促进学习交流】**

二、下载educ20201027.bak，打开SSMS（集成管理器），使用菜单操作：将素材的中的educ20201027.bak数据还原到数据库【本文跳过这个内容】

【如果做过之前的实验的话（专栏中有），就没有必要去还原了，直接用前面建的EDUC，数据是一样的】如果没有数据库，需要去还原一下

问题：

1. 查询成绩大于90分的学生的学号和姓名。（使用连接和子查询两种方法）

连接查询：

SELECT student.sno, student.sname
FROM student
JOIN sc ON student.sno = sc.sno
WHERE sc.grade > 90;

子查询：

SELECT sno, sname
FROM student
WHERE sno IN (
    SELECT sno
    FROM sc
    WHERE grade > 90
);

SQL 子查询是嵌套在其他查询中的查询。通常子查询用于在主查询中对表数据进行进一步的条件筛选

• 连接查询：直接连接 student 和 sc 表，过滤出符合条件的数据。结果包含 student 表中所有符合条件的记录。
• 子查询：先在 sc 表中查找符合条件的学号，再在 student 表中基于学号进行筛选，达到相同的结果。

运行结果不同的原因

1. 连接查询的结果可能包含重复记录：如果在 sc 表中，一个学生有多条成绩记录，且每条记录都满足 grade > 90 的条件，连接查询会返回多条结果。例如，Y71814001（陈靖）有多条成绩都大于 90，就会出现重复
2. 子查询的结果通常是去重的：子查询会首先筛选出符合条件的学号，且在主查询中只查找这些学号。即使 sc 表中有多个相同的学号，只要它们在子查询中存在，主查询结果中也只会出现一次

如果希望在连接查询中去重，可以使用

DISTINCT

：

SELECT DISTINCT student.sno, student.sname
FROM student
JOIN sc ON student.sno = sc.sno
WHERE sc.grade > 90;

1.  查询所有学生的学号、姓名，所选课程的课程号、课程名和成绩信息
   

SELECT student.sno, student.sname, course.cno, course.cname, sc.grade
FROM student
JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno;

1.  查询每一门课的间接先修课（先修课的先修课），显示信息包括课程号、间接先修课课程号。
   

SELECT c1.cno AS 课程号, c3.cno AS 间接先修课课程号
FROM course c1
JOIN course c2 ON c1.cpno = c2.cno
JOIN course c3 ON c2.cpno = c3.cno;

通过多次连接同一张课程表，寻找课程的间接先修课程

FROM course c1

：从课程表

course

中选取数据，并将其赋予别名

c1

1.  查询“Y71814001”号同学选修，但“Y71814003”号同学未选的课程的课程号及课程名
   

SELECT course.cno, course.cname
FROM course
JOIN sc AS sc1 ON course.cno = sc1.cno
LEFT JOIN sc AS sc2 ON course.cno = sc2.cno AND sc2.sno = 'Y71814003'
WHERE sc1.sno = 'Y71814001' AND sc2.sno IS NULL;

sc1

表示学生

'Y71814001'

选修的课程

LEFT JOIN

没有匹配的记录时会返回

NULL

，可以确保只选择

'Y71814001'

选了但

'Y71814003'

没选的课程。

1.  查询程熙的成绩
   

SELECT course.cno, course.cname, sc.grade
FROM student
JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno
WHERE student.sname = '程熙';

1.  查询没有参加任何一门考试的学生姓名
   

SELECT sname
FROM student
WHERE sno NOT IN (
    SELECT sno
    FROM sc
);

1. 找出选修‘02’号课程并且分数大于该课程平均分的学生信息(包括学号、成绩)。
   

SELECT sc.sno, sc.grade
FROM sc
WHERE sc.cno = '02'
AND sc.grade > (
    SELECT AVG(grade)
    FROM sc
    WHERE cno = '02'
);

1. 计算选修了‘02’号课程的每个学生的学号和平均成绩。
   

SELECT sno, AVG(grade) AS 平均成绩
FROM sc
WHERE sno IN (
    SELECT sno
    FROM sc
    WHERE cno = '02'
)
GROUP BY sno;

错误写法：先筛选后分组，得到的是每个学生的2号课程成绩，不是平均成绩

SELECT sno, AVG(grade) AS 平均成绩
FROM sc
WHERE cno = '02'
GROUP BY sno;

**

AVG

** ：聚合函数，返回选定记录的平均值

1.  查询程熙高于其所有选修课程平均分的那些课程的课程名和成绩。（例如：该同学选修了离散数学，分数为90，而他选修的所有课程平均分为85，则显示）
   

SELECT course.cname, sc.grade
FROM student
JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno
WHERE student.sname = '程熙'
AND sc.grade > (
    SELECT AVG(grade)
    FROM sc
    --WHERE sno = student.sno
);

1. 查询每个学生的成绩在80分以上的各有多少门，显示姓名和门数。
   

SELECT student.sname, COUNT(*) AS 成绩在80分以上门数
FROM student
JOIN sc ON student.sno = sc.sno
WHERE sc.grade > 80
GROUP BY student.sname;

在上面代码中定义别名时报错

在 SQL 中定义别名时，有一些限制和要求需要遵守，具体如下：

1. 命名规则

• 字符集：别名可以使用字母、数字和下划线（_），但不能以数字开头。
• 长度限制：不同的数据库管理系统（DBMS）对别名的最大长度有不同的限制

1. 关键字和保留字

• 避免使用关键字：避免将别名命名为 SQL 的关键字（如 SELECT、FROM、WHERE 等），以免引起语法错误或混淆。

1. 唯一性

• 在同一查询中必须唯一：在同一 SQL 查询中，别名必须是唯一的，以避免歧义。

1. 使用场景

• 仅在查询中有效：别名仅在特定的查询中有效，并不会在数据库的其他地方（如表、列）保留。别名的作用是使查询结果更加易读或便于进一步处理。

1. 引用方式

• 使用空格时的引用：如果别名包含空格或特殊字符，需要用方括号、反引号或双引号将其括起来。

其他方式：

SELECT student.sname, A.num AS "80分以上的门数"  
FROM student  
JOIN (  
    SELECT sno, COUNT(*) AS num  
    FROM sc  
    WHERE sc.grade > 80  
    GROUP BY sc.sno  
) AS A  
ON student.sno = A.sno;

SELECT student.sno, student.sname, COUNT(*)  
FROM student,sc 
where student.sno = sc.sno and grade> 80
group by student.snastudent.sname

完整代码：

--二
-- 1. 查询成绩大于90分的学生的学号和姓名（使用连接和子查询两种方法）
-- 使用连接
--SELECT student.sno, student.sname
SELECT DISTINCT student.sno, student.sname
FROM student
JOIN sc ON student.sno = sc.sno
WHERE sc.grade > 90;

-- 使用子查询
SELECT sno, sname
FROM student
WHERE sno IN (
    SELECT sno
    FROM sc
    WHERE grade > 90
);

-- 2. 查询所有学生的学号、姓名，所选课程的课程号、课程名和成绩信息
SELECT student.sno, student.sname, course.cno, course.cname, sc.grade
FROM student
JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno;

-- 3. 查询每一门课的间接先修课（先修课的先修课），显示信息包括课程号、间接先修课课程号
SELECT c1.cno AS 课程号, c3.cno AS 间接先修课课程号
FROM course c1
JOIN course c2 ON c1.cpno = c2.cno
JOIN course c3 ON c2.cpno = c3.cno;

-- 4. 查询“Y71814001”号同学选修，但“Y71814003”号同学未选的课程的课程号及课程名
SELECT course.cno, course.cname
FROM course
JOIN sc AS sc1 ON course.cno = sc1.cno
LEFT JOIN sc AS sc2 ON course.cno = sc2.cno AND sc2.sno = 'Y71814003'
WHERE sc1.sno = 'Y71814001' AND sc2.sno IS NULL;

-- 5. 查询程熙的成绩
SELECT course.cno, course.cname, sc.grade
FROM student
JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno
WHERE student.sname = '程熙';

-- 6. 查询没有参加任何一门考试的学生姓名
SELECT sname
FROM student
WHERE sno NOT IN (
    SELECT sno
    FROM sc
);

-- 7. 找出选修‘02’号课程并且分数大于该课程平均分的学生信息(包括学号、成绩)
SELECT sc.sno, sc.grade
FROM sc
WHERE sc.cno = '02'
AND sc.grade > (
    SELECT AVG(grade)
    FROM sc
    WHERE cno = '02'
);

-- 8. 计算选修了‘02’号课程的每个学生的学号和平均成绩
SELECT sno, AVG(grade) AS 平均成绩
FROM sc
WHERE sno IN (
    SELECT sno
    FROM sc
    WHERE cno = '02'
)
GROUP BY sno;

-- 9. 查询程熙高于其所有选修课程平均分的那些课程的课程名和成绩
SELECT course.cname, sc.grade
FROM student
JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno
WHERE student.sname = '程熙'
AND sc.grade > (
    SELECT AVG(grade)
    FROM sc
    WHERE sno = student.sno
);

-- 10. 查询每个学生的成绩在80分以上的各有多少门，显示姓名和门数
SELECT student.sname, COUNT(*) AS 成绩在80分以上门数
FROM student
JOIN sc ON student.sno = sc.sno
WHERE sc.grade > 80
GROUP BY student.sname;