MySQL初学必练50题

1.创建测试数据

# 创建本次作业的数据库
create database mysql_homework;

# 首先创建老师表

use mysql_homework;
create table teacher(
tid int primary key auto_increment,
tname varchar(30)
);

insert into teacher(tname) values
('张三'),
('李四'),
('王五')
;
# 首先创建学生表
 
create table student(
sid int primary key auto_increment,
sname varchar(30),
gender enum("男","女") not null default "男"
);
insert into student(sname) values
('乔丹'),
('艾弗森'),
('科比')
;

# 创建年级表
create table class_grade(
gid int primary key auto_increment,
gname varchar(30)
);
insert into class_grade(gname) values
('一年级'),
('二年级'),
('三年级')
;

# 创建班级表
create table class(
cid int primary key auto_increment,
caption varchar(20),
grade_id int,
foreign key(grade_id) references class_grade(gid)
on delete cascade
on update cascade 
)engine=innodb;

insert into class(caption,grade_id) values
('一年级一班',1),
('二年级一班',2),
('三年级二班',3)
;

# 创建课程表
create table course(
cid int primary key auto_increment,
cname varchar(20),
teacher_id int,
foreign key(teacher_id) references teacher(tid)
on delete cascade
on update cascade 
)engine=innodb;

insert into course(cname,teacher_id) values
('生物',1),
('体育',1),
('物理',2)
;

# 创建班级任职表
create table teach2cls(
tcid int not null unique auto_increment,
tid int not null,
cid int not null,
foreign key(tid) references teacher(tid)
on delete cascade
on update cascade, 
foreign key(cid) references class(cid)
on delete cascade
on update cascade,
primary key(tid,cid)
);

insert into teach2cls(tid,cid) values
(1,1),
(1,2),
(2,1),
(3,2)
;

# 创建成绩表
create table score(
sid int not null unique auto_increment,
student_id int not null,
course_id int not null,
score int not null,
foreign key(student_id) references student(sid)
on delete cascade
on update cascade, 
foreign key(course_id) references course(cid)
on delete cascade
on update cascade,
primary key(student_id,course_id)
);

insert into score(student_id,course_id,score) values
(1,1,60),
(1,2,59),
(2,2,99)
;

2.查询学生总人数

select count(*) as '学生总数' from student sid;

3.查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；

select sid as '学号',sname as '姓名' from student 
where sid in ( 
select student_id
from score
where score>60 and course_id in
(
select cid from course 
where cname in ('生物','物理')
)
group by student_id
having count(student_id)= 2
) ;

4.查询每个年级的班级数，取出班级数最多的前三个年级；

select class_grade.gname as'年级',count(caption) as '班级数' 
from class left join class_grade on grade_id = gid
group by grade_id 
order by count(caption) desc
limit 3;

5.查询平均成绩最高和最低的学生的id和姓名以及平均成绩；

select t3.student_id as "学号", t4.sname as "姓名", t3.avg_score as "平均成绩"
from (
    select * 
    from (
        select student_id, avg(score) as avg_score
        from score
        group by student_id
        order by avg(score) desc
        limit 1
        )as t1
    union
    select * 
    from (
        select student_id,avg(score)
        from score
        group by student_id
        order by avg(score)
        limit 1
        )as t2 
    )as t3 
inner join student as t4
on t3.student_id = t4.sid
;

6.查询每个年级的学生人数；

select gname as '年级', num as '总人数' from (select class.grade_id as cg_id,count(sid) as num from student 
left join class on class_id = cid
group by class.grade_id)as t2 
left join class_grade on t2.cg_id = class_grade.gid;

7.查询每位学生的学号，姓名，选课数，平均成绩；

select student.sid as '学号',student.sname as '姓名',count(score.course_id) as '选课数',avg(score.score) as '平均分' from score
left join student on
score.student_id = student.sid
group by student_id;

8.查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；

select sname as '姓名',cname as '课程名',score as '分数' 
from (select sname,course_id,score from score left join 
student
on score.student_id = student.sid
where student.sid = 2) as t3 left join course
on t3.course_id= course.cid
where t3.score in ((select max(score) from t3),
    (select min(score) from t3));

9.查询姓“李”的老师的个数和所带班级数；

select count(teacher.tid) as '李老师人数', GROUP_CONCAT(teach2cls.cid) as '班级数' from teacher
right join teach2cls
on teacher.tid = teach2cls.tid
where teacher.tname like '李%'
group by teacher.tid;

10.查询班级数小于5的年级id和年级名；

select gid ,class_grade.gname as'年级' 
from class left join class_grade on grade_id = gid
group by grade_id 
having count(caption) <5;

11.查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果如下；

select 
class.cid as '班级id',
class.caption as '班级名称',
class_grade.gname as '年级',
case 
    when class_grade.gid between 1 and 2 then '低'
    when class_grade.gid between 3 and 4 then '中'
    when class_grade.gid between 5 and 6 then '高' else 0 
    end as '年级级别'
from class 
left join class_grade
on class.grade_id = class_grade.gid;

12.查询学过“张三”老师2门课以上的同学的学号、姓名；

select sid,sname from student
where sid in 
(
    select score.student_id from score 
    left join course
    on score.course_id = course.cid
    where course.teacher_id in 
    (
        select tid from teacher
        where tname = '张三'
    )
    group by student_id
    having count(course.cid) >2
);

13.查询教授课程超过2门的老师的id和姓名；

select teacher.tid, teacher.tname as '姓名' 
from course
left join teacher
on course.teacher_id = teacher.tid 
group by teacher_id
having count(cid) > 2;

14.查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；

select student.sid as '学号', student.sname as '姓名' from score
left join student
on score.student_id = student.sid
where course_id in (1,2)
group by student_id
having count(course_id) =2;

15.查询没有带过高年级的老师id和姓名；

select teach2cls.tid,teacher.tname from teach2cls
left join teacher
on teach2cls.tid = teacher.tid
where cid not in 
    (
    select cid from class
    where grade_id > 4
    )
group by tid;

16.查询学过“张三”老师所教的所有课的同学的学号、姓名；

select score.student_id, student.sname from score
left join student
on score.student_id = student.sid
where score.course_id in
(
    select cid from course
    where teacher_id = 
    (
        select tid from teacher
        where tname = '张三'
    )
)
group by student_id;

17.查询带过超过2个班级的老师的id和姓名；

select teach2cls.tid,teacher.tname from teach2cls
left join teacher
on teach2cls.tid = teacher.tid
group by tid
having count(cid)> 2;

18.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

select student.sid,student.sname from student
where student.sid in 
(
    select t1.student_id from 
    (
        select student_id,score from score
        where course_id = 1
        group by student_id
    )as t1,
    (
        select student_id,score from score
        where course_id = 2
        group by student_id
    )as t2
    where t1.student_id = t2.student_id 
        and t2.score<t1.score
);

19.查询所带班级数最多的老师id和姓名；

select teacher.tid, teacher.tname from teacher
where teacher.tid = 
(
    select teach2cls.tid from teach2cls
    group by teach2cls.tid
    order by count(cid) desc
    limit 1 
);

20.查询有课程成绩小于60分的同学的学号、姓名；

select student.sid,student.sname from student
where student.sid in 
(
    select distinct score.student_id from score
    where score.score < 60
);

21.查询没有学全所有课的同学的学号、姓名；

select concat('学号:',student.sid,'>>>姓名：',student.sname) as '没有学全所有课的同学'from student
where student.sid in 
(
    select distinct score.student_id from score
    group by student_id
    having count(course_id) < 
    (
        select count(cid) from course
    )
);

22.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；

select concat('学号:',student.sid,'  姓名：',student.sname) 
as '至少有一门课与学号为“1”的同学所学相同的同学'
from student
where student.sid in 
(
    select distinct score.student_id from score
    where score.course_id in
    (
        select course_id from score
        where student_id = 1
    )
);

23.查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；

select concat('学号:',student.sid,'  姓名：',student.sname) 
as '至少学过学号为“1”同学所选课程中任意一门课的其他同学'
from student
where student.sid in 
(
    select distinct score.student_id from score
    where score.course_id in
    (
        select course_id from score
        where student_id = 1
    )
    having student_id != 1
);

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

select concat('学号:',student.sid,'  姓名：',student.sname) 
as '和“2”号同学学习的课程完全相同的其他同学'
from student
where student.sid in 
(
    select score.student_id from score,(
        select course_id from score
        where student_id = 2
    )as t1
    where score.course_id = t1.course_id and score.student_id !=2
    group by score.student_id
    having count(score.course_id) = 
    (
        select count(course_id) from score
        where student_id = 2
    )
);

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

delete from score
    where course_id in (
        select cid from course
        where teacher_id = (
            select tid from teacher
            where tname = '张三'
            )
        );

26.向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；

insert into score(student_id,course_id,score)
    select t1.sid,2,t2.avg from
    (select sid from student
    where sid not in
        (select student_id from score
        where course_id = 2)
    )as t1,
    (select avg(score) as avg from score
    group by course_id
    having course_id = 2
    )as t2;

27.按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

select *,(case when 数学 is null then 0 else 1 end)+
(case when 英语 is null then 0 else 1 end)+
(case when 语文 is null then 0 else 1 end)
as 有效成绩数,
(语文+数学+英语)/(
(case when 数学 is null then 0 else 1 end)+
(case when 英语 is null then 0 else 1 end)+
(case when 语文 is null then 0 else 1 end)
) as 有效平均分
from
(
    SELECT student_id AS 学生ID,
    (SELECT score FROM score LEFT JOIN course on score.course_id=course.cid 
        WHERE course.cname="语文" AND student_id=s1.student_id) AS 语文,
    (SELECT score FROM score LEFT JOIN course on score.course_id=course.cid 
        WHERE course.cname="数学" AND student_id=s1.student_id) AS 数学,
    (SELECT score FROM score LEFT JOIN course on score.course_id=course.cid 
        WHERE course.cname="英语" AND student_id=s1.student_id) AS 英语
    FROM score as s1 GROUP BY student_id
 )as t2;

28.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

select course_id,max(score) as max_score,min(score) as min_score
from score
group by course_id；

29.按各科平均成绩从低到高和及格率的百分数从高到低顺序；

select course_id,avg(score) as avg_score,
 sum(case when score.score>60 then 1 else 0 end)/count(1)*100 as percent
                from
                    score
                group by
                    course_id
                order by
                    avg(score) asc,
                    percent desc;

30.课程平均分从高到低显示（现实任课老师）；

select course.cname, avg(score) as'平均分' from course
right join score
on course.cid = score.course_id
group by score.course_id 
order by avg(score) desc;  

31.查询各科成绩前三名的记录(不考虑成绩并列情况)

SELECT t1.student_id,t1.course_id,t1.score
FROM score  as t1
WHERE
(SELECT COUNT(course_id)
FROM score
WHERE t1.course_id= course_id AND t1.score<score)<3

ORDER BY t1.course_id,score DESC；

32.查询每门课程被选修的学生数；

select course.cname as '课程名', t1.sum as '学生数' from course,
(select course_id,count(student_id) as sum from score
group by course_id
)as t1
where course.cid = t1.course_id;

33.查询选修了2门以上课程的全部学生的学号和姓名；

select student.sid,student.sname from student
where student.sid in (
select student_id from score
group by student_id
having count(course_id) >2
);

34.查询男生、女生的人数，按倒序排列；

select gender,count(sid) as sum from student
group by gender
order by sum desc;

35.查询姓“张”的学生名单；

select sname from student
where sname like "张%";

36.查询同名同姓学生名单，并统计同名人数；

select sname,count(sid)as'同名人数' from student
group by sname
having count(sid)>1;

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

select course.cname, avg(score) as'平均分' from course
right join score
on course.cid = score.course_id
group by score.course_id 
order by avg(score), course.cid desc; 

38.查询课程名称为“数学”，且分数低于60的学生姓名和分数；

select student.sname,score.score from score 
left join student
on score.student_id = student.sid
where score.course_id = (select cid from course where cname = '数学') 
    and score.score<60;

39.查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；

select student.sname,score.score from score 
left join student
on score.student_id = student.sid
where score.course_id = 3 
    and score.score>80;

40.求选修了课程的学生人数

select course_id,count(student_id) as '学生人数' from score
group by course_id;

41.查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；

select student.sname,score.score
from score
left join student 
on score.student_id =student.sid
where course_id in 
    (
        select cid  from course 
        where teacher_id in 
        (
            select tid from teacher
            where tname  = "王五"
        )
    )
order by score.score desc
limit 1 
;

42.查询各个课程及相应的选修人数；

select course.cname, count(student_id) as'选修人数' from score 
left join course
on score.course_id = course.cid
group by course_id;

43.查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

select  score.student_id,score.course_id,score.score from score,(select score,student_id from score
group by score,student_id

having count(course_id)>1)as t1
where score.score =t1.score and score.student_id =t1.student_id;

44.查询每门课程成绩最好的前两名学生id和姓名；

select
    student.sid,
    student.sname,
    t2.course_id,
    t2.score,
    t2.first_score,
    t2.second_score
from
    student
inner join (                    
    select
        score.student_id,
        score.course_id,
        score.score,
        t1.first_score,
        t1.second_score
    from
        score
    inner join (
        select
            s1.sid,
            (select s2.score from score as s2 where s1.course_id = s2.course_id order by s2.score desc limit 0,1) as first_score,
            (select s3.score from score as s3 where s1.course_id = s3.course_id order by s3.score desc limit 1,1) as second_score
        from
            score as s1
    ) as t1 on score.sid = t1.sid
    where
        score.score in (           
            t1.first_score,
            t1.second_score
        )
) as t2 on student.sid = t2.student_id;

45.检索至少选修两门课程的学生学号；

select student_id from score
group by student_id
having count(course_id)>1;

46.查询没有学生选修的课程的课程号和课程名；

select cid,cname from course
where cid not in 
    (
    select distinct course_id from score
    );

47.查询没带过任何班级的老师id和姓名；

select tid,tname from teacher
where tid not in 
    (
    select distinct tid from teach2cls
    );

48.查询有两门以上课程超过80分的学生id及其平均成绩；

select student_id,avg(score)
as '平均成绩' from score
where score >80
group by student_id
having count(course_id)>1;

49.检索“3”课程分数小于60，按分数降序排列的同学学号；

select student_id from score
where course_id =3 and score <60
order by score desc;

50.删除编号为“2”的同学的“1”课程的成绩；

delete from score
    where student_id =2 and course_id=1;

51.查询同时选修了物理课和生物课的学生id和姓名；

select sid as '学号',sname as '姓名' from student 
where sid in ( 
select student_id
from score
where course_id in
(
select cid from course 
where cname in ('生物','物理')
)
group by student_id
having count(student_id)= 2
) ;

52.查询学员信息，封装成视图

create view vstudent               
as
select
	gname,caption,sid,sname,gender
from
	student u
inner join
	class c
on
	c.cid=u.class_id
inner join
	class_grade g
on
	g.gid=c.grade_id

53.查询每个老师不同带班数量，及其所带各班的总平均成绩

select
	tname,count(distinct l.tcid) classCount,avg(score) avgscore
from
	score s
right join
	student u
on
	s.student_id=u.sid
right join
	class c
on
	u.class_id=c.cid
inner join
	teach2cls l
on
	c.cid=l.cid
right join
	teacher t
on
	l.tid=t.tid
group by
	tname;

54.查询各班不同科目平均成绩的最大差值

select
	caption,max(avgscore)-min(avgscore)
from
(
select 
	caption,cname,avg(score) avgscore
from
	score s
inner join
	course r
on
	r.cid=s.course_id
right join
	student u
on
	s.student_id=u.sid
right join 
	class c
on
	c.cid=u.class_id
where
	cname is not null
group by
	caption,cname
having
	avgscore is not null
)t
group by
	caption;

55.查询每个年级，每个班男女生的人数

select 
	gname,caption,gender,count(sid)
from
	student u
right join
	class c
on
	u.class_id=c.cid
right join
	class_grade g
on
	g.gid=c.grade_id
group by
	gname,caption,gender

56.查总页码

select 
	ceil(count(1)/5) total
from
	student