数据库实验5-分组查询

2021011203

1、统计学生选修表，统计每门课程的选修人数。

select cno, count(sno) as 学生数量
from scfcy
group by cno

2、统计学生选修表，统计每个同学的总成绩。

SELECT sno ,sum(grade)as 总成绩
FROM scfcy
GROUP by sno

3、统计学生表，统计每个系中每种性别的学生人数，并按照系升序排序。

SELECT sdept as 所在系,ssex as 性别, count(sno)as 人数
FROM studentfcy
GROUP by sdept,ssex
ORDER by sdept

4、统计学生选修表，统计每门课程的平均成绩,并按照成绩降序排序。

时长？？？？？？？？？？

SELECT cno,AVG(grade) as 平均成绩
FROM scfcy
GROUP by cno
ORDER by AVG(grade) DESC

 查询时间过长的原因

• 扫描次数多少
• 扫描硬盘

优化方法 

• 添加索引

索引用来，查询和排序 

最左前缀法则：建立符合索引，索引顺序按照建立时的顺序，从左到右（和b+树结构有关） 

 例子：

建立索引:a->b->c

a->c        c无效

b->c      b,c都无效

索引顺序链接的每部分都像桥的一部分，有效部分为：从桥头能走到的位置。

会导致索引失败的用法(在查询语句中)：

计算：is null, or , +...

函数 ：sum()

类型转换：int id ， id="1"

索引字段放在范围的右边：id（b）>121（回表操作过多，innodc会走全表查询，不走索引）

                                            a正常索引时，c失效

 select*：使用不到覆盖索引（select查询字段和where中使用的索引字段一致），不要使用

like模糊搜索：（不回表，查找的时候没有使用 ）

        like'%......%'：当前面是覆盖索引时，索引才有效

        like'.....%'：√  执行速度快

可以使用instr函数，效果相同，会用到索引

 using filesort:文件内排序，未按照索引本身去排序，在内存开辟了一份空间，数据复制放进去，在该空间内排序，消耗性能 

MySQL索引优化系列（1）—索引介绍

优化后：

ALTER TABLE scfcy ADD INDEX index_cno_grade(cno,grade);
SELECT cno,AVG(grade) as 平均成绩
FROM scfcy
GROUP by cno
ORDER by AVG(grade) DESC

5、统计学生选修表，显示有两门及以上课程不及格的学生的学号。

Select sno as 不及格学生学号,count(grade) as 不及格科目数 
from scfcy
where grade<60
group by sno
having count(grade)>1

---

#查询每个人的选课数（有成绩的？）
select sno,count(grade) 
from scfcy
group by sno

 

SELECT sno as 不及格科目超过1的学生学号
FROM scfcy
GROUP by sno
ORDER by count(grade<60)>=2 

#得到的是不按顺序的，所有科目数

---

 6、统计学生表，统计每个系的最大年龄是多少。

Select sdept as 所在系, max(sage) as 最大年龄 
from studentfcy 
group by sdept

一些提醒

在数据库数据量超大时，不要使用  * ，数据量太多，加上limit 限制。浪费带宽，减少覆盖索引的使用。

不要使用delete语句：表之间的联系会使逻辑混乱