《数据库的嵌套查询和统计查询》

选择Study数据库，用SQL语句进行以下查询操作。

---

1．嵌套查询

①求选修了数据结构的学生学号和成绩。

SELECT Sno, grade FROM sc WHERE Cno = '007';

②求007课程的成绩高于于文轩的学生学号和成绩。

SELECT Sno, grade FROM sc
WHERE Cno = '007' AND 
grade >(SELECT grade FROM sc WHERE Sno = '20418001' AND Cno = '007');

③求其他系中比软件工程系某一学生年龄小的学生姓名和年龄。

SELECT Sname, TIMESTAMPDIFF(YEAR, Sbirth, CURDATE()) AS age FROM s
WHERE Sdept != '软件工程系' AND
TIMESTAMPDIFF(YEAR, Sbirth, CURDATE()) <
(SELECT MIN(TIMESTAMPDIFF(YEAR, Sbirth, CURDATE())) FROM s WHERE Sdept = '软件工程系');

TIMESTAMPDIFF(YEAR, Sbirth, CURDATE())是MySQL函数，用于计算两个时间（或时间戳）之间的年数差。
在这个代码中，它用于计算学生出生日期（Sbirth）和当前日期（CURDATE()）之间的年数差，以确定学生的年龄。

④求其他系中比软件工程系所有学生年龄都小的学生姓名和年龄。

SELECT Sname, TIMESTAMPDIFF(YEAR, Sbirth, CURDATE()) AS age FROM s
WHERE Sdept != '软件工程系' AND
TIMESTAMPDIFF(YEAR, Sbirth, CURDATE()) <
(SELECT MIN(TIMESTAMPDIFF(YEAR, Sbirth, CURDATE())) FROM s 
WHERE Sdept = '软件工程系');

⑤求选修了002课程的学生姓名。

SELECT s.Sname FROM s INNER JOIN sc ON s.Sno = sc.Sno WHERE sc.Cno = '002'; 

⑥求没有选修了002课程的学生姓名。

SELECT Sname FROM s WHERE Sno NOT IN (SELECT Sno FROM sc WHERE Cno = '002'); 

⑦查询选修了全部课程的学生的姓名。

SELECT Sname FROM s WHERE Sno IN (SELECT Sno FROM sc GROUP BY Sno HAVING COUNT(Cno) = (SELECT COUNT(*) FROM c)); 

⑧求至少选修了学号为20418002的学生所选修的全部课程的学生学号和姓名。

SELECT s.Sno, s.Sname FROM s INNER JOIN sc ON s.Sno = sc.Sno WHERE sc.Cno IN (SELECT sc.Cno FROM sc WHERE sc.Sno = '20418002'); 

---

2．分组、统计查询

①查询学生总人数。

SELECT COUNT(*) as total_students FROM s; 

②查询选修了课程的学生人数。

SELECT COUNT(DISTINCT Sno) as enrolled_students FROM sc; 

③计算001课程的学生平均成绩。

SELECT AVG(grade) as avg_grade FROM sc WHERE Cno = '001'; 

④查询选修001课程的学生的最高分数。

SELECT MAX(grade) as highest_grade FROM sc WHERE Cno = '001'; 

⑤求学号为20418002学生的总分和平均分。

SELECT SUM(grade) as total_score, AVG(grade) as average_score FROM sc WHERE Sno = '20418002'; 

⑥求各个课程号及相应的选课人数。

SELECT Cno, COUNT(Sno) as student_count FROM sc GROUP BY Cno; 

⑦查询选修了3门以上课程的学生学号。

SELECT Sno FROM sc GROUP BY Sno HAVING COUNT(Cno) >= 3; 

⑧查询选修了3门以上且各门课程均为及格的学生的学号及其总成绩，查询结果按总成绩降序列出。

SELECT Sno, SUM(grade) as total_score FROM sc GROUP BY Sno HAVING COUNT(Cno) >= 3 AND MIN(grade) >= 60 ORDER BY total_score DESC; 

---

3．集合查询

①查询软件软件工程系的学生及年龄不大于19岁的学生。

SELECT * FROM s WHERE Sdept = '软件工程系' OR TIMESTAMPDIFF(YEAR, Sbirth, CURDATE()) <= 19; 

②查询选修了课程001或者选修了002的学生。

SELECT DISTINCT Sno FROM sc WHERE Cno IN ('001', '002'); 

③查询学号为002和学号为005的学生的学号和总分。

SELECT Sno, SUM(grade) as total_score FROM sc WHERE Sno IN ('002', '005') GROUP BY Sno; 

④查询网络工程系与年龄不大于19岁的学生的交集。

SELECT * FROM s WHERE Sdept = '网络工程系' AND TIMESTAMPDIFF(YEAR, Sbirth, CURDATE()) <= 19; 

⑤查询计算机科学系的学生与年龄不大于19岁的学生的差集。

SELECT * FROM s WHERE Sdept = '计算机科学系' AND TIMESTAMPDIFF(YEAR, Sbirth, CURDATE()) > 19;

---

注意：

子句WHERE<条件>表示元组筛选条件，子句HAVING<条件>表示元组选择条件。

子句HAVING<条件>必须和GROUP BY<分组列名>子句配合使用。

组合查询的子句间不能有语句结束符。

使用UNION将多个查询结果合并起来时，系统会自动去掉重复元组。

参加UNION操作的各结果表的列数必须相同；对应项数据类型也必须相同。

Any和All与比较运算符配合使用：

> ANY	大于子查询结果中的某个值  

> ALL	大于子查询结果中的所有值

< ANY	小于子查询结果中的某个值  

< ALL	小于子查询结果中的所有值

>= ANY	大于等于子查询结果中的某个值 

>= ALL	大于等于子查询结果中的所有值

!=（或<>）ALL	不等于子查询结果中的任何一个值

<= ANY	小于等于子查询结果中的某个值 

<= ALL	小于等于子查询结果中的所有值

= ANY   等于子查询结果中的某个值  

=ALL	等于子查询结果中的所有值（没有实际意义）

!=（或<>）ANY	不等于子查询结果中的某个值

MySQL5.7.40版本以前的版本和SQL Server2008仅支持集合的并操作UNION，不支持集合的交操作INTERSECT和差MINUS操作，但可以使用其他方法实现。而MySQL 8.0.31以后版本提供了对集合操作交操作INTERSECT和差操作EXCEPT。

集合操作一般要求两个输入表必须拥有相同的列数且相应列的数据类型相同。MySQL支持两种形式的并操作：UNION DISTINCT和UNION ALL，将合并两个查询结果并应用DISTINCT过滤重复项，生成一个虚拟表。而UNION ALL不会排除掉重复的数据项。若两个输入表相应列的数据类型不同时，MySQL自动将进行隐式转换，结果列的名称由第一个输入决定。

并操作格式：
SELECT column,... FROM table1
[DISTINCT] UNION [ALL]
SELECT column,... FROM table2

交操作格式：
SELECT column,... FROM table1
INTERSECT
SELECT column,... FROM table2

差操作格式：
SELECT column,... FROM table1
EXCEPT
SELECT column,... FROM table2

---

思考：
组合查询语句是否可以用其他语句代替，有什么不同？

可以使用其他语句代替组合查询，但是不同的语句可以有不同的结果和性能。下面是一些代替组合查询的语句： 
 
1. 嵌套查询：使用一个查询作为另一个查询的条件，以实现类似于组合查询的功能。嵌套查询可以更加灵活，但在复杂的查询中可能会影响性能。 
 
2. UNION ALL：将多个SELECT语句的结果集合并为一个结果集。UNION ALL 可以更容易地将多个结果集组合在一起，但效率可能会比组合查询慢。 
 
3. JOIN：使用JOIN可将两个或多个表中的数据合并到一个结果集中。JOIN更适合关联多个表的数据，但对于相同的查询，性能可能会比组合查询慢。 
 
总之，组合查询是一种方便、灵活和高效的查询方式，但无论使用哪种替代方案，都需要考虑查询的性能和结果的正确性。

使用GROUP BY<分组列名>子句后，语句中的统计函数的运行结果有什么不同？

使用GROUP BY子句后，语句中的统计函数的运行结果将会按照分组列名进行分组，然后对每个组的数据进行统计计算，返回每个组的计算结果。不同分组列名的组之间的计算结果将会相互独立，互不干扰。 
 
例如，如果在SELECT语句中使用了SUM函数，语句将按照GROUP BY子句中指定的分组列名对数据进行分组，然后对每个组的数据进行SUM运算，最终返回每个组的SUM运算结果。 

举个例子，假设有一个sales表，其中包含商品名称(name)、商品类别(category)和销售额(sales)三个字段，可以使用以下语句：

SELECT category, SUM(sales) FROM sales GROUP BY category;

运行结果将会按照商品类别对销售额进行分组，计算每个类别的销售额总和，最终返回每个类别的销售额总和。