### SQL 数据查询与视图知识点解析
#### 一、实验背景与目的
本实验报告针对的是计算机科学与技术系的数据库系统及应用课程中的一个重要环节——SQL数据查询和视图的使用。通过本次实验，旨在使学生达到以下目标：
1. **熟练掌握SQL SELECT语句**：能够运用查询语句完成复杂的查询任务。
2. **理解视图的概念**：了解视图的基本原理及其在实际应用中的价值。
3. **掌握视图的使用方法**：包括如何创建、修改和删除视图。
4. **理解视图和基本表的异同**：明确视图与基本表的区别和联系。
#### 二、实验内容解析
##### 1. 一般简单查询
- **不带条件的查询指定字段**：例如，查询教师表中的所有教师姓名。可以通过 `SELECT 姓名 FROM 教师;` 来实现。
- **考虑去掉和不去掉重复值的情况**：使用 `DISTINCT` 关键字可以去除重复值。例如，查询所有不同教师的姓名：`SELECT DISTINCT 姓名 FROM 教师;`。
- **查询某个表中的所有记录**：使用 `SELECT * FROM 表名;` 可以获取表中的所有记录。
- **使用单个条件的简单查询**：例如，查询所有院系为“计算机”的教师：`SELECT * FROM 教师 WHERE 院系 = '计算机';`。
- **使用多个条件（AND关系）的查询**：例如，查询所有院系为“计算机”且性别为“女”的教师：`SELECT * FROM 教师 WHERE 院系 = '计算机' AND 性别 = '女';`。
- **使用多个条件（OR关系）的查询**：例如，查询所有院系为“计算机”或性别为“女”的教师：`SELECT * FROM 教师 WHERE 院系 = '计算机' OR 性别 = '女';`。
- **使用多个条件（混合AND和OR关系）的查询**：例如，查询所有院系为“计算机”且性别为“女”或院系为“数学”的教师：`SELECT * FROM 教师 WHERE (院系 = '计算机' AND 性别 = '女') OR 院系 = '数学';`。
- **使用带NOT运算的查询**：例如，查询所有院系不是“计算机”的教师：`SELECT * FROM 教师 WHERE NOT 院系 = '计算机';`。
- **使用BETWEEN...AND...的查询**：例如，查询教师编号在“3”到“6”之间的教师：`SELECT * FROM 教师 WHERE 教师编号 BETWEEN '3' AND '6';`。
- **使用NOT...BETWEEN...AND...的查询**：例如，查询教师编号不在“3”到“6”之间的教师：`SELECT * FROM 教师 WHERE 教师编号 NOT BETWEEN '3' AND '6';`。
- **使用LIKE运算符的字符串匹配查询**：例如，查询所有教师姓名中包含“张”的教师：`SELECT * FROM 教师 WHERE 姓名 LIKE '%张%';`。
- **使用LIKE运算符的模板匹配查询**：例如，查询所有教师姓名以“张”开头的教师：`SELECT * FROM 教师 WHERE 姓名 LIKE '张%';`。
- **查询空值和非空值的查询**：例如，查询所有教师编号为空的教师：`SELECT * FROM 教师 WHERE 教师编号 IS NULL;`。
- **结果要求排序的查询**：例如，按照教师编号升序排列：`SELECT * FROM 教师 ORDER BY 教师编号 ASC;`。
- **查询结果按多列排序**：例如，先按照教师编号升序排列，然后按照姓名降序排列：`SELECT * FROM 教师 ORDER BY 教师编号 ASC, 姓名 DESC;`。
- **使用TOP显示前若干记录的查询**：例如，显示前10条记录：`SELECT TOP 10 * FROM 教师;`。
##### 2. 连接查询
- **两个关系的连接查询**：例如，连接教师表和课程表，查询所有教师及其负责的课程：`SELECT 教师.姓名, 课程.课程名称 FROM 教师 INNER JOIN 课程 ON 教师.教师编号 = 课程.责任教师编号;`。
- **带其他查询条件的两个关系的连接查询**：例如，连接教师表和课程表，查询所有计算机系的教师及其负责的课程：`SELECT 教师.姓名, 课程.课程名称 FROM 教师 INNER JOIN 课程 ON 教师.教师编号 = 课程.责任教师编号 WHERE 教师.院系 = '计算机';`。
- **多个关系（三个以上）的连接查询**：例如，连接教师表、课程表和学生选课表，查询所有学生及其所选课程的教师信息：`SELECT 学生.姓名, 教师.姓名, 课程.课程名称 FROM 学生 INNER JOIN 选课 ON 学生.学号 = 选课.学号 INNER JOIN 课程 ON 选课.课程编号 = 课程.课程编号 INNER JOIN 教师 ON 课程.责任教师编号 = 教师.教师编号;`。
- **两个关系的广义笛卡尔积运算结果**：例如，显示教师表和课程表的所有可能组合：`SELECT 教师.姓名, 课程.课程名称 FROM 教师, 课程;`。
- **根据两个关系的广义笛卡尔积运算结果得到两个关系进行自然连接的结果**：自然连接通常是指通过公共列自动连接两个表。例如，如果教师表和课程表都有“教师编号”列，则可以使用 `SELECT 教师.姓名, 课程.课程名称 FROM 教师 NATURAL JOIN 课程;`。
##### 3. 嵌套查询
- **使用IN运算的简单嵌套查询**：例如，查询所有课程编号在特定列表中的课程：`SELECT * FROM 课程 WHERE 课程编号 IN (SELECT 课程编号 FROM 选课);`。
- **使用NOT IN运算的简单嵌套查询**：例如，查询所有课程编号不在特定列表中的课程：`SELECT * FROM 课程 WHERE 课程编号 NOT IN (SELECT 课程编号 FROM 选课);`。
- **使用关系运算（如等于）的简单嵌套查询**：例如，查询所有教师编号等于某课程的责任教师编号的教师信息：`SELECT * FROM 教师 WHERE 教师编号 = (SELECT 责任教师编号 FROM 课程 WHERE 课程编号 = '1001');`。
- **使用ANY或SOME的简单嵌套查询**：例如，查询所有教师编号小于某课程的责任教师编号的教师信息：`SELECT * FROM 教师 WHERE 教师编号 < ANY (SELECT 责任教师编号 FROM 课程);`。
- **使用ALL的简单嵌套查询**：例如，查询所有教师编号大于所有课程的责任教师编号的教师信息：`SELECT * FROM 教师 WHERE 教师编号 > ALL (SELECT 责任教师编号 FROM 课程);`。
- **查询院系名称含“计算机”、职称为教授、所负责教程为必修课的教师姓名、职称、课程名称和课程学时等信息**：使用嵌套查询和连接查询分别完成，并对比效率。例如，使用连接查询：`SELECT 教师.姓名, 教师.职称, 课程.课程名称, 课程.学时 FROM 教师 INNER JOIN 课程 ON 教师.教师编号 = 课程.责任教师编号 WHERE 教师.院系 LIKE '%计算机%' AND 教师.职称 = '教授' AND 课程.课程性质 = '必修课';`。
- **设计两个内外层互相关的嵌套查询**：例如，查询所有教师负责的课程中有选课人数超过10人的课程的教师信息：`SELECT 教师.姓名 FROM 教师 WHERE EXISTS (SELECT * FROM 课程 INNER JOIN 选课 ON 课程.课程编号 = 选课.课程编号 WHERE 课程.责任教师编号 = 教师.教师编号 GROUP BY 课程.课程编号 HAVING COUNT(*) > 10);`。
- **使用EXISTS的嵌套查询**：例如，查询所有有学生选课的课程信息：`SELECT * FROM 课程 WHERE EXISTS (SELECT * FROM 选课 WHERE 课程.课程编号 = 选课.课程编号);`。
- **使用NOT EXISTS的嵌套查询**：例如，查询所有没有学生选课的课程信息：`SELECT * FROM 课程 WHERE NOT EXISTS (SELECT * FROM 选课 WHERE 课程.课程编号 = 选课.课程编号);`。
##### 4. 汇总和分组查询
- **使用COUNT统计数目的查询**：例如，统计教师表中的教师总数：`SELECT COUNT(*) FROM 教师;`。
- **使用SUM计算合计的查询**：例如，计算所有课程的学时总和：`SELECT SUM(学时) FROM 课程;`。
- **一次完成求和、计数、计算平均值的查询**：例如，计算所有课程的学时总和、课程总数和平均学时：`SELECT SUM(学时), COUNT(*), AVG(学时) FROM 课程;`。
- **查询所有课程的成绩都大于60分的学生的平均分最高的学生信息**：例如，`SELECT 学生.姓名, AVG(选课.成绩) AS 平均分 FROM 学生 INNER JOIN 选课 ON 学生.学号 = 选课.学号 GROUP BY 学生.学号, 学生.姓名 HAVING MIN(选课.成绩) > 60 ORDER BY 平均分 DESC LIMIT 1;`。
- **查询数据库课程的成绩大于70分的、所有课程平均分最高的学生信息**：例如，`SELECT 学生.姓名, AVG(选课.成绩) AS 平均分 FROM 学生 INNER JOIN 选课 ON 学生.学号 = 选课.学号 INNER JOIN 课程 ON 选课.课程编号 = 课程.课程编号 WHERE 课程.课程名称 = '数据库' AND 选课.成绩 > 70 GROUP BY 学生.学号, 学生.姓名 ORDER BY 平均分 DESC LIMIT 1;`。
- **查询每个学生的平均成绩**：例如，`SELECT 学生.姓名, AVG(选课.成绩) AS 平均分 FROM 学生 INNER JOIN 选课 ON 学生.学号 = 选课.学号 GROUP BY 学生.学号, 学生.姓名;`。
- **查询每个学生的所有成绩的最高成绩、最低成绩、平均成绩和所考课程的门数**：例如，`SELECT 学生.姓名, MAX(选课.成绩) AS 最高分, MIN(选课.成绩) AS 最低分, AVG(选课.成绩) AS 平均分, COUNT(*) AS 课程门数 FROM 学生 INNER JOIN 选课 ON 学生.学号 = 选课.学号 GROUP BY 学生.学号, 学生.姓名;`。
- **查询至少有10门必修课程考试成绩的每个学生的平均成绩**：例如，`SELECT 学生.姓名, AVG(选课.成绩) AS 平均分 FROM 学生 INNER JOIN 选课 ON 学生.学号 = 选课.学号 INNER JOIN 课程 ON 选课.课程编号 = 课程.课程编号 WHERE 课程.课程性质 = '必修课' GROUP BY 学生.学号, 学生.姓名 HAVING COUNT(*) >= 10;`。
- **设计2个使用COMPUTE...BY和COMPUTE的查询**：例如，`SELECT 学生.姓名, 选课.成绩, COMPUTE(AVG(选课.成绩)) OVER (PARTITION BY 学生.学号) AS 平均分 FROM 学生 INNER JOIN 选课 ON 学生.学号 = 选课.学号;` 和 `SELECT 学生.姓名, 选课.成绩, COMPUTE(AVG(选课.成绩)) AS 平均分 FROM 学生 INNER JOIN 选课 ON 学生.学号 = 选课.学号;`。
- **设计1个使用COMPUTE的查询**：例如，`SELECT 学生.姓名, 选课.成绩, COMPUTE(AVG(选课.成绩)) AS 平均分 FROM 学生 INNER JOIN 选课 ON 学生.学号 = 选课.学号;`。
- **设计1个使用COMPUTE...BY的查询**：例如，`SELECT 学生.姓名, 选课.成绩, COMPUTE(AVG(选课.成绩)) OVER (PARTITION BY 学生.学号) AS 平均分 FROM 学生 INNER JOIN 选课 ON 学生.学号 = 选课.学号;`。
##### 视图
- **基于单个表按投影操作定义视图**：例如，创建一个只包含教师姓名和职称的视图：`CREATE VIEW 教师姓名职称 AS SELECT 姓名, 职称 FROM 教师;`。
- **基于单个表按选择操作定义视图**：例如，创建一个只包含计算机系教师信息的视图：`CREATE VIEW 计算机系教师 AS SELECT * FROM 教师 WHERE 院系 = '计算机';`。
- **基于单个表按选择和投影操作定义视图**：例如，创建一个只包含计算机系教师的姓名和职称的视图：`CREATE VIEW 计算机系教师姓名职称 AS SELECT 姓名, 职称 FROM 教师 WHERE 院系 = '计算机';`。
- **基于多个表根据连接操作定义视图**：例如，创建一个包含教师姓名、职称、所负责课程的课程名称和课程性质的视图：`CREATE VIEW 教师课程信息 AS SELECT 教师.姓名, 教师.职称, 课程.课程名称, 课程.课程性质 FROM 教师 INNER JOIN 课程 ON 教师.教师编号 = 课程.责任教师编号;`。
- **基于多个表根据嵌套操作定义视图**：例如，创建一个包含教师姓名、职称和他们负责的所有课程名称的视图：`CREATE VIEW 教师负责课程 AS SELECT 教师.姓名, 教师.职称, (SELECT GROUP_CONCAT(课程.课程名称) FROM 课程 WHERE 课程.责任教师编号 = 教师.教师编号) AS 负责课程 FROM 教师;`。
- **定义含有虚字段的视图**：例如，创建一个视图，其中包含教师的姓名、职称和他们负责的课程数量：`CREATE VIEW 教师课程数量 AS SELECT 教师.姓名, 教师.职称, COUNT(课程.课程编号) AS 课程数量 FROM 教师 LEFT JOIN 课程 ON 教师.教师编号 = 课程.责任教师编号 GROUP BY 教师.教师编号, 教师.姓名, 教师.职称;`。
#### 三、实验过程
实验过程中涉及的具体查询语句已经在实验内容部分进行了详细的解析。此外，还应该包括以下几点：
- **视图上的查询操作**：可以在定义好的视图上进行查询操作，例如，在“教师负责课程”视图上查询所有教师及其负责的课程。
- **视图上的插入、更新和删除操作**：在某些情况下，可以在视图上执行这些操作。需要注意的是，并非所有的视图都可以进行这些操作。例如，在基于多个表定义的视图上，可能无法直接更新或删除数据，因为这可能会导致数据不一致的问题。
- **分析和讨论**：在实验报告中要对实验过程中遇到的各种情况进行具体的分析和讨论。例如，对于嵌套查询和连接查询，可以从查询效率的角度进行比较；对于视图的操作，可以讨论哪些操作是可以成功完成的，哪些是不可以完成的，并分析其原因。
本实验报告涵盖了SQL数据查询和视图使用的各个方面，不仅提供了理论基础，还通过具体的实验内容和过程展示了这些知识点的实际应用。通过完成这些任务，学生能够更深入地理解和掌握SQL语言的基础知识和高级特性。
