Mysql数据库的查询操作（五）

41、查询选修了全部课程的学生信息

```sql
select sid,count(cid) from score group by sid having count(cid)=3;

-- 方法二
select student.sid, sname from student left join score on student.sid = score.sid
group by sid having count(cid) = (select count(*) from course);
```

42、查询没学过“王五”老师讲授的任一门课程的学生姓名

```sql

```

43、查询两门以上不及格课程的同学的学号及其平均成绩

```sql
-- 两门以上不及格的学生的学号  统计一下每个学生不及格的科目的数量 
select sid from score where score < 60 group by sid having count(cid) >= 2;

-- 每个学生的平均成绩  筛选出来 有两门以上不及格的学生的信息 
select sid, avg(score) as avg_s from score group by sid having sid in (select sid from score where score < 60 group by sid having count(cid) >= 2);
```

44、检索课程编号为“03”且分数小于60的学生学号，结果按按分数降序排列

```sql

```

45、删除学生编号为“02”的课程编号为“01”的成绩

```sql
delete from score where sid='02' and cid='01';
```

46、使用分段[100-85],[85-70],[70-60],小于60来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

```sql
select score.cid, cname,
sum(if(score > 85, 1, 0)) as `[100-85]`,
sum(if(score > 70 and score <= 85, 1, 0)) as `[85-70]`,
sum(if(score >= 60 and score <= 70, 1, 0)) as `[70 -60]`,
sum(if(score < 60, 1, 0)) as `[<60]`
from score join course
on score.cid = course.cid
group by cid;
```

47、按各科平均成绩从低到高和及格率的百分数从高到低排列，以如下形式显示：课程号,课程名,平均成绩,及格百分数

```sql
select score.cid 课程号, cname 课程名, avg(score) 平均成绩,
-- 求的是当前科目中及格的人数 select count(*) from score as s where score >= 60 and s.cid=score.cid
concat(((select count(*) from score as s where score >= 60 and s.cid=score.cid) / count(sid)) * 100, '%') 及格率
from score join course
on score.cid = course.cid
group by score.cid;
```

48、查询本周过生日的学生

```sql
select * from student where week(concat(year(curdate()), substr(sbirthday, 5)), 1) = week(curdate(), 1);

```

49、查询下周过生日的学生

```sql
select * from student where week(concat(year(curdate()), substr(sbirthday, 5)), 1) = week(curdate(), 1) + 1;

```

50、查询本月过生日的学生

```sql
select * from student where month(sbirthday) = month(curdate());

```

51、查询下月过生日的学生

```sql
select * from student where month(sbirthday) = month(curdate()) + 1;

```

Mysql数据库的查询操作（五）

发表评论