MySQL题目

表结构:

–1.学生表 
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 
–2.课程表 
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 
–3.教师表 
Teacher(t_id,t_name) –教师编号,教师姓名 
–4.成绩表 
Score(s_id,c_id,s_score) –学生编号,课程编号,分数

1. 查询”01”课程比”02”课程成绩高的学生 的信息及课程分数

   从成绩表中获取学生 id 以及成绩
   通过id获取到学生的信息
   select student.*,a.s_score as 01_score,b.s_score as 02_score 
   from score a
   inner join student
   on a.s_id=student.s_id
   and a.c_id='01'
   left join score b
   on student.s_id=b.s_id
   and b.c_id='02'
   where a.s_score>b.s_score;

1. 查询”01”课程比”02”课程成绩低的学生的信息及课程分数

   select  student.*,a.s_score as 01_score,b.s_score as 02_score 
   from student
   join score a
   on a.s_id=student.s_id and a.c_id='01'
   left join score b
   on b.s_id=student.s_id and b.c_id='02'
   where a.s_score<b.s_score;

1. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

   --分组 having
   select student.s_id,student.s_name,avg(score.s_score) as avg_score
   from student
   inner join score 
   on score.s_id=student.s_id
   group by score.s_id
   having avg(score.s_score) >= 60;

   
   可以使用 四舍五入 函数 round（）

2. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩— (包括有成绩的和无成绩的)

   select student.s_id,student.s_name,round(avg(score.s_score),2) as avg_score
   from student
   inner join score 
   on score.s_id=student.s_id
   group by score.s_id
   having avg_score < 60
   union all
   select student.s_id,student.s_name,0 as avg_score
   from student
   where student.s_id 
   not in (select distinct s_id from score);

1. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

   select student.s_id,student.s_name,sum(score.s_score),count(score.c_id)
   from student
   left join score
   on student.s_id = score.s_id
   group by student.s_id;

1. 查询”李”姓老师的数量

   select count(t_name)
   from teacher
   where t_name like '李%';

1. 查询学过”张三”老师授课的同学的信息

   select student.* 
   from student
   inner join score
   on student.s_id = score.s_id
   where score.c_id in
   (select course.c_id
    from course
    where course.t_id = 
        (select teacher.t_id
        from teacher
        where teacher.t_name = '张三'
       )
   );

1. 查询没学过”张三”老师授课的同学的信息 ```sql select student.* from student where student.s_id not in (select score.s_id from score where score.c_id in

    (select course.c_id
    from course
    where course.t_id = 
        (select teacher.t_id
        from teacher
        where teacher.t_name = '张三'
       )
   )

   );

select student.* from student where student.s_id not in( select student.s_id from student inner join score on student.s_id = score.s_id where score.c_id in (select course.c_id from course where course.t_id = ( select teacher.t_id from teacher where teacher.t_name = ‘张三’ ) ) );

9.  查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 
```sql
select student.*
from student
inner join score
on student.s_id=score.s_id
inner join score s2
on student.s_id=s2.s_id
where score.c_id='02' and s2.c_id ='01';

1. 查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息

select student.*
from student
where student.s_id 
    in (
        select score.s_id
        from score
        where score.c_id='01'
    )
    and student.s_id
    not in(
        select score.s_id
        from score
        where score.c_id='02'
    );

1. 查询没有学全所有课程的同学的信息

   -- 利用内交,存在不匹配则不返回
   select student.* 
   from student
   where student.s_id 
   not in (
      select a.s_id
      from score a
      inner join score b 
      on a.s_id = b.s_id and b.c_id = '02'
      inner join score c
      on a.s_id = c.s_id and c.c_id = '03'
   );

1. 查询至少有一门课与学号为”01”的同学所学相同的同学的信息

   select student.*
   from student
   where student.s_id 
   in(
      select distinct a.s_id
      from score a
      where a.c_id 
      in(
          select b.c_id
          from score b
          where b.s_id = '01'
      )
   );

1. 查询和”01”号的同学学习的课程完全相同的其他同学的信息

   select student.* 
   from student
   where student.s_id 
   in(
      select distinct s_id
      from score 
      where s_id != '01' and c_id in (
          select c_id
          from score 
          where s_id = '01'
      )
      group by s_id
      having count(1) = (
          select count(1) from score where s_id = '01'
      )
   );

1. 查询没学过”张三”老师讲授的任一门课程的学生姓名

   select student.s_name
   from student
   where student.s_id not in (
   select distinct s_id
   from score
   where c_id in (
      select c_id
      from course
      where t_id = (
          select t_id
          from teacher
          where t_name = '张三'
      )
   )
   );