实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生） .docx实验四 SQL进行复杂查询（学生）
### 实验四 SQL进行复杂查询知识点详解
#### 实验目的
- **掌握SELECT语句的基本语法和查询条件表示方法**：SELECT语句是SQL中最基本也是最重要的查询命令，用于从数据库中检索数据。掌握其基本语法是进行任何类型查询的基础。
- **掌握查询条件表达式和使用方法**：查询条件表达式用于指定查询的具体条件，例如WHERE子句中的条件表达式，这些表达式可以是简单的等于比较，也可以是复杂的逻辑组合。
- **掌握GROUP BY子句的作用和使用方法**：GROUP BY子句用于将数据根据一个或多个列分组，通常与聚合函数（如COUNT、SUM等）结合使用来获取分组后的统计信息。
- **掌握HAVING子句的作用和使用方法**：HAVING子句用于过滤GROUP BY子句产生的结果集，它可以对分组后的结果进行筛选。
- **掌握ORDER BY子句的作用和使用方法**：ORDER BY子句用于对查询结果进行排序，可以按照一个或多个字段进行升序（ASC）或降序（DESC）排序。
#### 实验内容
- **了解数据库查询**：理解数据库查询的基本概念，包括如何构造有效的查询语句来检索所需的数据。
- **了解数据库查询的实现方式**：熟悉使用SQL语句进行数据检索的方法，包括简单的SELECT语句以及更复杂的多表连接、子查询等技术。
- **完成实验报告**：根据实验要求，撰写详细的实验过程记录和分析，总结所学到的知识点。
#### 实验步骤详解
1. **列出所有不姓刘的所有学生**：
- 使用`NOT LIKE`关键字排除姓“刘”的学生。
```sql
SELECT * FROM student WHERE sname NOT LIKE '刘%';
```
2. **列出姓“张”且全名为 3 个汉字的学生**：
- 使用`LIKE`关键字匹配全名为3个汉字的“张”姓学生。
```sql
SELECT * FROM student WHERE sname LIKE '张__';
```
3. **显示在 1993 年以后出生的学生的基本信息**：
- 计算当前年份减去学生的年龄，得到出生年份，再与1993进行比较。
```sql
SELECT * FROM student WHERE sage > (YEAR(GETDATE()) - 1993);
```
4. **查询出课程名含有“数据”字串的所有课程基本信息**：
- 使用`LIKE`关键字进行模糊匹配。
```sql
SELECT * FROM course WHERE cname LIKE '%数据%';
```
5. **列出选修了‘1’课程的学生，按成绩的降序排列**：
- 多表连接查询，使用`ORDER BY`子句进行排序。
```sql
SELECT student.sno, grade
FROM student JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno
WHERE sc.cno = '1'
ORDER BY grade DESC;
```
6. **列出同时选修“1”号课程和“2”号课程的所有学生的学号**：
- 使用子查询结合`IN`关键字找出符合条件的学生。
```sql
SELECT sno
FROM sc
WHERE cno = '1' AND sno IN (SELECT sno FROM sc WHERE cno = '2');
```
7. **列出课程表中全部信息，按先修课的升序排列**：
- 使用`ORDER BY`子句按先修课升序排序。
```sql
SELECT * FROM course ORDER BY cpno;
```
8. **列出年龄超过平均值的所有学生名单，按年龄的降序显示**：
- 使用子查询计算平均年龄，并使用`ORDER BY`子句进行排序。
```sql
SELECT sno, sname
FROM student
WHERE sage > (SELECT AVG(sage) FROM student)
ORDER BY sage DESC;
```
9. **按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系**：
- 计算出生年份，使用`AS`关键字定义列别名，并按计算结果排序。
```sql
SELECT sno AS 学号, sname AS 姓名, ssex AS 性别, (YEAR(GETDATE()) - sage) AS 出生年份, sdept AS 院系
FROM student
ORDER BY 出生年份 ASC;
```
10. **按照课程号、成绩降序显示课程成绩在 70-80 之间的学生的学号、课程号及成绩**：
- 使用`BETWEEN`关键字进行范围筛选，再用`ORDER BY`子句排序。
```sql
SELECT sno, cno, grade
FROM sc
WHERE grade BETWEEN 70 AND 80
ORDER BY cno DESC, grade DESC;
```
11. **显示学生信息表中的学生总人数及平均年龄**：
- 使用`COUNT()`和`AVG()`聚合函数计算总人数和平均年龄。
```sql
SELECT COUNT(sno) AS 学生总人数, AVG(sage) AS 平均年龄
FROM student;
```
12. **显示选修的课程数大于 3 的各个学生的选修课程数**：
- 使用`GROUP BY`和`HAVING`子句来过滤选修课程数大于3的学生。
```sql
SELECT sno, COUNT(cno)
FROM sc
GROUP BY sno
HAVING COUNT(cno) > 3;
```
13. **按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩**：
- 使用`GROUP BY`对课程号进行分组，并使用聚合函数计算统计数据。
```sql
SELECT COUNT(cno) AS 总人数, MAX(grade) AS 最高成绩, MIN(grade) AS 最低成绩, AVG(grade) AS 平均成绩
FROM sc
GROUP BY cno
ORDER BY cno DESC;
```
14. **显示各个院系男女生人数**：
- 使用`GROUP BY`和`CASE`语句进行分组和计数。
```sql
SELECT Sdept AS 院系名称, COUNT(CASE WHEN Ssex = '女' THEN 1 END) AS 女生人数, COUNT(CASE WHEN Ssex = '男' THEN 1 END) AS 男生人数
FROM student
GROUP BY Sdept, Ssex;
```
15. **按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息**：
- 使用`CASE`语句处理性别显示，按指定顺序排序。
```sql
SELECT CASE ssex WHEN '男' THEN '男生' WHEN '女' THEN '女生' ELSE '条件不明' END AS 性别, sno, sname, sage, sdept
FROM student
ORDER BY 性别, sno, sname, sage, sdept;
```
16. **显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系**：
- 使用字符串函数处理学号，并进行条件筛选。
```sql
SELECT sno, sname, ssex, sage, sdept
FROM student
WHERE SUBSTRING(sno, 8, 1) IN ('1', '2', '3', '4', '9') OR SUBSTRING(sno, 9, 1) IN ('1', '2', '3', '4', '9');
```
17. **按照院系降序显示所有学生的 “院系，学号、姓名、性别、年龄”等信息**：
- 使用`CASE`语句处理院系显示，并按院系降序排序。
```sql
SELECT CASE sdept WHEN 'CS' THEN '计算机系' WHEN 'IS' THEN '信息系' WHEN 'MA' THEN '数学系' WHEN 'EN' THEN '外语系' WHEN 'CM' THEN '中医系' WHEN 'WM' THEN '西医系' ELSE '其他' END AS 院系, sno, sname, ssex, sage
FROM student
ORDER BY 院系 DESC;
```
以上实验步骤覆盖了SQL中的多种查询技巧和技术，包括简单的数据检索、复杂的多表连接、聚合函数的使用、分组与过滤、排序、字符串处理等，有助于深入理解和掌握SQL查询语言的应用。
