数据库的嵌套查询和统计查询_数据库的嵌套、组合、统计查询

1．嵌套查询

①求选修了数据结构的学生学号和成绩。
select Sno as 学号,grade as 成绩 from SC where Cno in(select Cno from C where Cname = ‘数据结构’);

②求007课程的成绩高于于文轩的学生学号和成绩。
select Sno as 学号,grade as 成绩 from SC where Cno = ‘007’ and grade > any(select grade from SC where Sno in (select Sno from S where Sname = ‘于文轩’));
③求其他系中比软件工程系某一学生年龄小的学生姓名和年龄。
select Sname as 姓名,Year(CURDATE())-Year(Sbirth) as 年龄 from S where Sbirth>any(select Sbirth from S where Sdept = ‘软件工程系’) and Sdept != ‘软件工程系’;
④求其他系中比软件工程系所有学生年龄都小的学生姓名和年龄。
select Sname as 姓名,Year(CURDATE()-Year(Sbirth)) as 年龄 from S where Sbirth > all(select Sbirth from S where Sdept = ‘软件工程系’) and Sdept != ‘软件工程系’;
⑤求选修了002课程的学生姓名。
select Sname as 学生姓名 from S where Sno in (select Sno from SC where Cno = ‘002’);
⑥求没有选修了002课程的学生姓名。
select Sname as 学生姓名 from S where Sno not in(select Sno from SC where Cno = ‘002’);
⑦查询选修了全部课程的学生的姓名。
select Sname as 姓名 from S where not exists(select * from C where not exists(select * from SC where SC.Sno = S.Sno and SC.Cno = C.Cno));
⑧求至少选修了学号为20418002的学生所选修的全部课程的学生学号和姓名。
select distinct Sno from SC A where not exists(select * from SC B where B.Sno = ‘20418002’ and not exists(select * from SC C where C.Cno = B.Cno and C.Sno = A.Sno));

2．分组、统计查询

①查询学生总人数。
select count(Sno) as 总人数 from S;
②查询选修了课程的学生人数。
select count(distinct Sno) as 选修了课程的学生学号 from SC;

③计算001课程的学生平均成绩。
select avg(grade) as 001号课程平均成绩 from SC where Cno = ‘001’;

④查询选修001课程的学生的最高分数。
select max(grade) as 001号课程的最高成绩 from SC where Cno = ‘001’;

⑤求学号为20418002学生的总分和平均分。
select sum(grade) as 总分,avg(grade) as 平均分 from SC where Sno = ‘20418002’;

⑥求各个课程号及相应的选课人数。
select Cno as 课程号,count(Sno) as 选课人数 from SC group by Cno;

⑦查询选修了3门以上课程的学生学号。
select Sno as 学号 from SC group by Sno having count(Cno)>=3;

⑧查询选修了3门以上且各门课程均为及格的学生的学号及其总成绩，查询结果按总成绩降序列出。
select Sno as 学号,sum(grade) as 总分 from SC where grade>=60 group by Sno having count(Cno)>=3 order by sum(grade) desc;

3．集合查询

①查询软件软件工程系的学生及年龄不大于19岁的学生。
select * from S where Sdept = ‘软件工程系’ intersect select * from S where Year(curdate()) - Year(Sbirth)<=19;

②查询选修了课程001或者选修了002的学生。
select S.Sno as 学号,Sname as 姓名 from SC,S where Cno = ‘001’ and S.Sno = SC.Sno union select S.Sno as 学号,Sname as 姓名 from SC,S where Cno = ‘002’ and S.Sno = SC.Sno;

③查询学号为002和学号为005的学生的学号和总分。
select Sno as 学号,sum(grade) as 总分 from SC where Sno = ‘20418002’ group by Sno union select Sno as 学号,sum(grade) as 总分 from SC where Sno = ‘20418001’ group by Sno;

④查询网络工程系与年龄不大于19岁的学生的交集。
select * from S where Sdept = ‘网络工程系’ intersect select * from S where Year(curdate())-Year(Sbirth)<=19;

⑤查询计算机科学系的学生与年龄不大于19岁的学生的差集。
select * from S where Sdep = ‘计算机科学系’ except select * from S where Year(curdate())-Year(Sbirth)<=19;

---

操作技巧与注意事项：

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

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

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

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

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

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

ANY 大于子查询结果中的某个值
ALL 大于子查询结果中的所有值
< ANY 小于子查询结果中的某个值
< ALL 小于子查询结果中的所有值
= ANY 大于等于子查询结果中的某个值
= ALL 大于等于子查询结果中的所有值
!=（或<>）ALL 不等于子查询结果中的任何一个值
<= ANY 小于等于子查询结果中的某个值
<= ALL 小于等于子查询结果中的所有值
= ANY 等于子查询结果中的某个值
=ALL 等于子查询结果中的所有值（没有实际意义）
!=（或<>）ANY 不等于子查询结果中的某个值

7．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、组合查询语句是否可以用其他语句代替，有什么不同？

组合查询语句可以使用其他语句代替，但是使用不同的语句可能会导致不同的结果，具体取决于查询的需求和数据结构。

以下是一些常见的SQL语句和它们在组合查询中的替代方案：

INNER JOIN: 可以使用WHERE子句进行连接，但是需要注意的是，WHERE子句可能会返回重复的结果。

LEFT JOIN: 可以使用RIGHT JOIN代替，但是需要将表的顺序进行调整。可以将左表变为右表，右表变为左表，然后使用RIGHT JOIN。

UNION: 可以使用UNION ALL或者INNER JOIN代替。使用UNION ALL会返回重复的结果，而INNER JOIN则不会。

需要注意的是，不同的语句可能会对性能产生不同的影响。在进行查询时，需要考虑数据量、表结构和查询需求，选择最优的查询方式。

---

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

在SQL语句中，使用GROUP BY子句后，统计函数的运行结果会按照分组列的值进行分组，并且针对每个分组计算统计函数的值。具体来说，使用GROUP BY子句后，统计函数会对每个分组返回一个单独的结果，而不是对整个表的结果进行计算。

举个例子，假设有一个名为"orders"的表，其中包含"order_id"、“customer_id”、"order_date"和"total_amount"四个字段。现在要统计每个客户的总订单金额，可以使用以下SQL语句：

SELECT customer_id, SUM(total_amount)
FROM orders
GROUP BY customer_id;

在这个SQL语句中，使用了GROUP BY子句对"customer_id"进行分组，然后对每个分组计算"total_amount"的总和。运行结果将返回每个客户的"customer_id"和"total_amount"的总和。

使用GROUP BY子句后，统计函数的结果会受到分组列的影响，因此不同的分组列会得到不同的结果。同时，如果查询中没有使用GROUP BY子句，则统计函数会对整个表的结果进行计算，不会按照任何分组进行分组计算。