1 准备工作

• 业务场景

1 每个教师可以教多门课程
2 每个课程由一个老师负责
3 每门课程可以由多个学生选修
4 每个学生可以选修多门课程
5 学生选修课程要有成绩

create database db_day0202;
use db_day0202;
​
CREATE TABLE teacher (
  id int(11) NOT NULL primary key auto_increment,
  name varchar(20) not null unique
 );
CREATE TABLE student (
  id int(11) NOT NULL primary key auto_increment,
  name varchar(20) NOT NULL unique,
  city varchar(40) NOT NULL,
  age int 
) ;
CREATE TABLE course(
  id int(11) NOT NULL primary key auto_increment,
  name varchar(20) NOT NULL unique,
  teacher_id int(11) NOT NULL,
  FOREIGN KEY (teacher_id) REFERENCES teacher (id)
);
​
CREATE TABLE studentcourse (
   student_id int NOT NULL,
   course_id int NOT NULL,
   score double NOT NULL,
   FOREIGN KEY (student_id) REFERENCES student (id),
   FOREIGN KEY (course_id) REFERENCES course (id)
);
​
insert into teacher values(null,'关羽');
insert into teacher values(null,'张飞');
insert into teacher values(null,'赵云');
​
insert into student values(null,'小王','北京',20);
insert into student values(null,'小李','上海',18);
insert into student values(null,'小周','北京',22);
insert into student values(null,'小刘','北京',21);
insert into student values(null,'小张','上海',22);
insert into student values(null,'小赵','北京',17);
insert into student values(null,'小蒋','上海',23);
insert into student values(null,'小韩','北京',25);
insert into student values(null,'小魏','上海',18);
insert into student values(null,'小明','广州',20);
​
insert into course values(null,'语文',1);
insert into course values(null,'数学',1);
insert into course values(null,'生物',2);
insert into course values(null,'化学',2);
insert into course values(null,'物理',2);
insert into course values(null,'英语',3);
​
insert into studentcourse values(1,1,80);
insert into studentcourse values(1,2,90);
insert into studentcourse values(1,3,85);
insert into studentcourse values(1,4,78);
insert into studentcourse values(2,2,53);
insert into studentcourse values(2,3,77);
insert into studentcourse values(2,5,80);
insert into studentcourse values(3,1,71);
insert into studentcourse values(3,2,70);
insert into studentcourse values(3,4,80);
insert into studentcourse values(3,5,65);
insert into studentcourse values(3,6,75);
insert into studentcourse values(4,2,90);
insert into studentcourse values(4,3,80);
insert into studentcourse values(4,4,70);
insert into studentcourse values(4,6,95);
insert into studentcourse values(5,1,60);
insert into studentcourse values(5,2,70);
insert into studentcourse values(5,5,80);
insert into studentcourse values(5,6,69);
insert into studentcourse values(6,1,76);
insert into studentcourse values(6,2,88);
insert into studentcourse values(6,3,87);
insert into studentcourse values(7,4,80);
insert into studentcourse values(8,2,71);
insert into studentcourse values(8,3,58);
insert into studentcourse values(8,5,68);
insert into studentcourse values(9,2,88);
insert into studentcourse values(10,1,77);
insert into studentcourse values(10,2,76);
insert into studentcourse values(10,3,80);
insert into studentcourse values(10,4,85);
insert into studentcourse values(10,5,83);

2 练习

1、查询平均成绩大于70分的同学的学号和平均成绩

select student_id, floor(avg(score)), avg(score)
from studentcourse
group by student_id
having avg(score)>70

2、查询所有同学的学号、姓名、选课数、总成绩

# 2.1 在中间表 求每个同学的学号、选课数、总成绩
select student_id,count(1),sum(score)
from studentcourse
group by student_id
# 2.2 将2.1的结果做临时表 和 学生表关联查询
select s.id, s.name, temp.coun, temp.scoreSum
from student s, (select student_id,count(1) coun,sum(score) scoreSum
                from studentcourse
                group by student_id) temp
where s.id=temp.student_id

3、查询学过赵云老师所教课的同学的学号、姓名

# 3.1 在教师表 查询赵云老师的工号
select id from teacher
where name='赵云'
# 3.2 在课程表 根据赵云老师工号查询所教课程编号
select id
from course
where teacher_id=(select id from teacher
                  where name='赵云');
# 3.3 在中间表 查看根据课程编号查询学号
select student_id from studentcourse
where course_id in (select id
                    from course
                    where teacher_id=(select id from teacher
                                      where name='赵云'));
# 3.4 在学生表 根据学号查询学生的学号和姓名
select id, name
from student
where id in (select student_id from studentcourse
            where course_id in (select id
                                from course
                                where teacher_id=(select id from teacher
                                                  where name='赵云')));

4、查询没学过关羽老师课的同学的学号、姓名

select id, name
from student
where id not in (select student_id from studentcourse
                where course_id in (select id
                                    from course
                                    where teacher_id=(select id from teacher
                                                      where name='关羽')));

5、查询没有学三门课以上的同学的学号、姓名

#5.1 在中间表 查询每个学生学了几门课 且 小于或等于三门的

select student_id, count(1)
from studentcourse
group by student_id
having count(1)<=3;

# 5.2 在学生表 查询对应学生的学号和姓名

```sql
select id, name
from student
where id in (select student_id
            from studentcourse
            group by student_id
            having count(1)<=3);

6、查询各科成绩最高和最低的分

select course_id, max(score),min(score)
from studentcourse
group by course_id

7、查询学生信息和平均成绩

# 7.1 在中间表 查询每个学生平均成绩
select student_id, avg(score) avgScore
from studentcourse
group by student_id;
# 7.2 将7.1的结果作为临时表 和 学生表关联查询
select s.*, floor(temp.avgScore)
from student s, (select student_id, avg(score) avgScore
                from studentcourse
                group by student_id) temp
where s.id = temp.student_id;

8、查询上海和北京学生数量

select city, count(1)
from student
where city in ('北京', '上海')
group by city;

9、查询不及格的学生信息和课程信息

# 9.1 在中间表 查询不及格的学号, 课程编号, 分数
select *
from studentcourse
where score<60;
​
# 9.2 9.1的结果作为临时表 关联学生表和课程表
select s.*, c.*, temp.score
from student s, course c, (select *
                            from studentcourse
                            where score<60) as temp
where temp.student_id=s.id and temp.course_id=c.id

10、统计每门课程的学生选修人数（超过四人的进行统计）

# 10.1 在中间表 统计每门课的选修人数 且 超过4人
select course_id, count(1)
from studentcourse
group by course_id
having count(1)>4