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

21、查询每门课程被选修的学生数

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

22、查询出只选修了两门课程的全部学生的学号和姓名

```sql
select student.sid,student.sname,count(cid) from student join score on student.sid =score.sid group by student.sid having count(cid)=2;

```

23、查询男生、女生人数

```sql
select student.ssex,count(ssex) from student group by ssex;
```

24、查询名字中含有“风”字的学生信息

```sql
方法一：
select * from student where sname like '%风%' ;
方法二：
select * from student where instr(sname, '风');
```

25、查询同名同性学生名单并统计同名人数

```sql
select sname, ssex, count(*) from student group by sname, ssex having count(*) >= 2;
```

26、1990年出生的学生名单

```sql
select * from student where year(sbirthday)=1990;
```

27、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

```sql
select student.sid, sname,avg(score) 平均成绩 from student join score on student.sid= score.sid group by student.sid having avg(score)>85;

```

28、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

```sql
select cid, avg(score) from score group by cid order by avg(score), cid desc;

```

29、查询课程名称为“数学”且分数低于60的学生姓名和分数

```sql
select * from student join score join course on student.sid= score.sid and score.cid=course.cid where course.cname='数学'and score<60;

--简单方法
select sname, score from score join course join student on score.sid = student.sid and course.cid = score.cid where cname = '数学' and score < 60;
```

30、查询所有学生的选课情况

```sql
select *,group_concat(course.cname) from student join score join course on student.sid=score.sid and score.cid=course.cid group by student.sid;
--简单方法
select student.sid, sname, ifnull(group_concat(cname), '无') 选课情况 from
student left join score on score.sid = student.sid left join course on score.cid = course.cid group by sid;
```

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

发表评论