MySQL自测练习题

1 创建学生表 表名是student 插入数据如下 第一行为表字段（列）

+----+------+---------+-------+-------+
| sid | sname | subject | score | class |
+----+------+---------+-------+-------+
|  1 | 张三 | chinese |  60.5 | 901  |
|  1 | 张三 | english |  40.5 | 901  |
|  1 | 张三 | math  |  90.5 | 901  |
|  2 | 王五 | chinese |    78 | 901  |
|  2 | 王五 | english |   80.5 | 901  |
|  3 | 李四 | chinese |    30 | 902  |
|  3 | 李四 | english |    90 | 902  |
|  4 | 萝卜 | chinese |    50 | 903  |
|  5 | 赵雷 | english |    98 | 904  |
|  5 | 赵雷 | chinese|    96 | 904  |
+----+------+---------+-------+-------+

要求：

1）、创建表和输入数据

create table student(
    sid int,
    sname varchar(30),
    subject varchar(30),
    score double,
    class bigint
);

insert into student (sid,sname,subject,score,class)
values(1,"张三","chinese",60.5,901),
      (1,"张三","english",40.5,901),
      (1,"张三","math",90.5,901),
      (2,"王五","chinese",78,901),
      (2,"王五","english",80.5,901),
      (3,"李四","chinese",30,902),
      (3,"李四","english",90,902),
      (4,"萝卜","chinese",50,903),
      (5,"赵雷","english",98,904),
      (5,"赵雷","chinese",96,904);

select * from student;

2）、删除class列

alter table student drop column class;

3）、查询平均成绩大于70分的所有学生的名字和平均成绩；

select sname,avg(score) as avgs
from student
group by sid
having  avgs>70;

4）、查询每科成绩都不低于60分的所有学生的名字和班级

select distinct sname,class
from student
where sid not in 
(select sid 
from student
where score<60);

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

select subject,max(score),min(score)
from student
group by subject;

6）、查询每门课程被选修的学生人数

select subject ,count(sid)
from student
group by subject;

7）、查询出只选修了一门课程的所有学生的学号和姓名

select sid,sname
from student
where sid =(
select sid
from student
group by sid
having  count(subject)=1);

8）、查询班级超过一人的所有学生信息

select *
from student s1
where s1.class in(
  select s2.class
  from (
    select distinct(s3.sid),s3.class //查询出所有班级的学生sid
  from student as s3
  ) as s2
  group by s2.class //这里要加班级分组
  having count(s2.class)>1
)

9）、已知张三、李四为男，王五、萝卜为女，插入SEX列（备注：1为男，0为女），并写入备注

alter table student
add sex varchar(10);

10）张三已转班904，请更新数据表

update student
set class="904"
where sid=1;

11）将成绩低于60分的数据剔除

delete from student
where score<60;

2.创建班级表 表名是class 插入数据如下 第一行为表字段（列）

+----+------+---------+-------+-------+
| cid | name | teacher | grade | 
+----+------+---------+-------+-------+
|  1 |  901  | 吴兰 |  优 | 
|  2 |  902  | 李竹 |  良 | 
|  3 |  903  | 孙风 |  优 | 
|  4 |  904  | 李云 |  良 | 
+----+------+---------+-------+-------+

1）、查询"李"姓老师的班级信息

create table class(
  cid int,
  cname varchar(30),
  teacher varchar(30),
  grade varchar(30)
);

insert into class(cid,cname,teacher,grade)
values (1,"901","吴兰","优"),
       (2,"902","李竹","良"),
       (3,"903","孙风","优"),
       (4,"904","李云","良");

select *
from class
where teacher like '李%';

2）查询等级为优的班级的所有学生信息

select * 
from student s
right join class c on  c.cname=s.class
where c.grade="优";

3）查询学生数最多的班级名称

//这里只查出了班级名称和对应的班级里面学生人数
select s1.class 
from (
  select s2.class as class ,count(s2.sid) as counts
      from (
        select distinct(s3.sid),s3.class
      from student as s3
      ) as s2
      group by s2.class
) as s1


//
select distinct co.class
from Courses co
where co.class=(
  select class from(
    select s2.class as class ,count(s2.sid) as counts
        from (
          select distinct(s3.sid),s3.class
        from student as s3
        ) as s2
        group by s2.class

  )a
  order by counts desc limit 1
)

4）查询赵雷所在班级的等级

select distinct grade
from class c
left join student s on s.class=c.cname
where s.sname="赵雷";

3.创建课程信息表 表名是Courses 插入数据如下 第一行为表字段（列）

+----+------+---------+-------+-------+
| cid |   name | teacher | class | 
+----+------+---------+-------+-------+
|  1 |  chinese | 吴兰 |  901 | 
|  1 |  chinese | 吴兰 |  902 | 
|  1 |  chinese | 吴兰 |  903 | 
|  2 |  english | 李竹 |  901 | 
|  2 |  english | 李竹 |  902 | 
|  3 |  math  | 孙风 |  901 | 
|  4 |  english | 李云 |  904 | 
+----+------+---------+-------+-------+

1）查询赵雷的english 老师的名称

create table Courses(
  cid int,
  name varchar(30),
  teacher varchar(30),
  class varchar(30)
);

insert into Courses (cid,name,teacher,class)
values (1,"chinese","吴兰","901"),
(1,"chinese","吴兰","902"),
(1,"chinese","吴兰","903"),
(2,"english","李竹","901"),
(2,"english","李竹","902"),
(3,"math", "孙风","901"),
(4,"english","李云","904");

select distinct co.teacher
from Courses co
join  class c on co.class=c.cname
join student s on s.class=c.cname
where s.sname="赵雷";

2）查询李竹老师所教学生的最高、最低、平均分

select co.teacher,max(s.score),min(s.score),avg(s.score)
from Courses co
join student s on co.class=s.class and co.name=s.subject
where co.teacher="李竹";

3）查询最高分数的学生所有信息（包括班级信息，授课老师信息）

select *
from student s
left join class  c on  s.class=c.cname
left join Courses co on s.subject=co.teacher
where score=(select max(score) from student );