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

11.查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

```sql
-- 找到01所学课程的编号
select cid from score where sid = '01';

-- 在成绩表找到 只要这个学生的 课程编号在01的里面 
select distinct student.sid, sname from score join student on score.sid = student.sid
where student.sid != '01' and cid in (select cid from score where sid = '01');
```

12.查询和“01”号同学所学课程完全相同的其他同学的学号

```sql
-- 获取01的学的课程
select group_concat(cid order by cid) from score where sid = '01' group by sid;
-- 获取其他同学所学课程 
select sid from score where sid != '01' group by sid having group_concat(cid order by cid) = (select group_concat(cid order by cid) from score where sid = '01' group by sid);
```

13.把“score”表中“张三”老师教的课的成绩都更改为此课程的平均成绩

```sql
-- 获取张三老师每门课程的平均成绩  
select score.cid, avg(score) as avg_s from score join course join teacher on score.cid = course.cid and teacher.tid = course.tid where tname = '张三' group by cid;
```

14.查询和“02”号的同学学习的课程完全相同的其他同学学号和姓名

```sql
select student.sid, sname from score join student on score.sid = student.sid
where score.sid != '02' group by sid having group_concat(cid order by cid) = (select group_concat(cid order by cid) from score where sid = '02' group by sid);

```

15.删除学习“张三”老师课的score表记录

```sql
-- 找到张三老师所授课程的cid 
select cid from course join teacher on teacher.tid = course.tid where tname='张三';
-- 删除score表 筛选条件是 cid 在张三老师的授课表中 
delete from score where cid in (select cid from course join teacher on teacher.tid = course.tid where tname='张三');
```

16.检索"01"课程分数小于60，按分数降序排列的学生信息

```sql
select * from score join student on score.sid = student.sid where cid='01' and score < 60 order by score desc;
```

17、查询各科成绩最高和最低的分：以如下的形式显示：课程ID，最高分，最低分(之前删除了张三老师的课程，所以不存在02课程)

```sql
select cid 课程ID, max(score) 最高分, min(score) 最低分 from score group by cid;
```

18、查询不同老师所教不同课程平均分从高到低显示

```sql
select * from
(select teacher.*,course.cid,course.cname,score.score,avg(score.score) p from teacher join course  on teacher.tid=course.tid join score on course.cid=score.cid group by teacher.tid)
as t order by t.p desc;

--简单方法：
select teacher.tid, tname, course.cid, cname, avg(score) as 平均分 from teacher join course join score on teacher.tid = course.tid and course.cid = score.cid group by tid, cid order by 平均分 desc;
```

19、查询学生平均成绩及其名次

```sql
select *,row_number()over(order by t.p desc)from 
(select student.*,score.score,avg(score) p from student join score on student.sid=score.sid group by student.sid)as t;
--简单方法
select *, rank() over(order by avg_s desc) as ranking from(select sid, avg(score) as avg_s from score group by sid) as t;
```

20、查询各科成绩前三名的记录

```sql
select * from
(select *,row_number()over(partition by cid order by score desc) p from score) as t
where t.p<4;
--简单方法：
select * from (select *, rank() over(partition by cid order by score desc) as ranking from score) as t where ranking < 4;
```

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

发表评论