MySQL题目总结

# 建库
create table course
(
    id         varchar(255) not null
        primary key,
    name       varchar(255) not null,
    teacher_id varchar(255) not null
);


create table score
(
    user_id   varchar(255) not null,
    course_id varchar(255) not null,
    score     float(3, 0)  not null
);


create table student
(
    id     varchar(255) not null
        primary key,
    name   varchar(255) not null,
    birth  date         not null,
    gender varchar(255) not null
);


create table teacher
(
    id   varchar(255) not null
        primary key,
    name varchar(255) null
);


# 插入数据
insert into student values ('0001', '猴子', '1989-01-01', '男');
insert into student values ('0002', '猴子', '1990-12-21', '女');
insert into student values ('0003', '马云', '1991-12-21', '男');
insert into student values ('0004', '王思聪', '1990-05-20', '男');
insert into score values ('0001', '0001', 80);
insert into score values ('0001', '0002', 90);
insert into score values ('0001', '0003', 99);
insert into score values ('0002', '0002', 60);
insert into score values ('0002', '0003', 80);
insert into score values ('0003', '0001', 80);
insert into score values ('0003', '0002', 80);
insert into score values ('0003', '0003', 80);
insert into course values('0001' , '语文' , '0002');
insert into course values('0002' , '数学' , '0001');
insert into course values('0003' , '英语' , '0003');
insert into teacher values('0001' , '孟扎扎');
insert into teacher values('0002' , '马化腾');
insert into teacher values('0003' , null);
insert into teacher values('0004' , '');


# 题目
-- 一般查询

# 查询姓 猴 的学生名单
select * from student where name like '猴%';

# 查询姓名最后一个是 猴 的学生
select * from student where name like '%猴';

# 查询姓名中带 猴 的学生名单
select * from student where name like '%猴%';

# 查询姓 孟 的老师个数
select count(*) from teacher where name like '孟%';

# 查询课程编号为 0002 的总成绩
select sum(score) from score where course_id = '0002';

# 查询选了课程的学生人数
select count(distinct user_id) from score;

# 查询各科成绩最高和最低的分数
select course_id,max(score) as max_score,min(score) as min_score from score group by course_id;

# 查询每门课被选的学生数
select count(distinct user_id) from score group by course_id;

# 查询男生，女生人数
select gender,count(gender) from student group by gender;

# 查询平均成绩大于 60 分的学生学号和平均成绩
select user_id,avg(score) from score group by user_id having avg(score) > 60;

# 查询至少选了两门课程的学生学号
select user_id from score group by user_id having count(distinct course_id) >= 2;

# 查询同名同姓学生名单并统计同名人数
select name,count(name) from student group by name;

# 查询不及格的课程并按课程号从大到小排列
select * from score where score < 60 order by course_id desc;

# 查询每门课的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select course_id,avg(score) from score group by course_id order by avg(score) asc,course_id desc ;

# 检索课程编号为 0004 且分数小于 60 的学生学号，结果按分数降序排列
select user_id from score where course_id = '0004' and score < 60 order by score desc;

# 统计每门课的学生选修人数（超过两人的课程才统计），要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select course_id,count(user_id) from score group by course_id having count(user_id) >= 2 order by count(user_id) desc,course_id asc ;

# 查询两门以上不及格课程的学生的学号以及平均成绩
select course_id,avg(score) from score where score < 60 group by course_id having count(score) >= 2;

# 查询学生的总成绩并排名
select user_id,sum(score) from score group by user_id order by sum(score) desc ;

# 查询平均成绩大于 60 分的学生的学号和平均成绩
select user_id,avg(score) from score group by user_id having avg(score) > 60;


-- 复杂查询

# 查询所有课程中成绩小于 60 分学生的学号、姓名
select student.id,student.name from student where id in (
    select user_id from score where score < 60
    );

# 查询没有学全所有课的学生的学号、姓名　
select student.id,student.name from student where id in (
    select user_id from score group by user_id having count(distinct course_id) < (select count(distinct course_id) from score)
    );

# 查询出只选修了两门课程的全部学生的学号和姓名　
select student.id,student.name from student where id in (
    select user_id from score group by user_id having count(distinct course_id) = 2
    );

# 1990年出生的学生名单
select * from student where birth like '1990%';

# 查询各科成绩前两名的记录　　
(select * from score where course_id = '0001' order by score desc limit 2)
union all
(select * from score where course_id = '0002' order by score desc limit 2)
union all
(select * from score where course_id = '0003' order by score desc limit 2);

# 查询本月过生日的学生
SELECT * FROM student WHERE MONTH(birth) = month(now());


-- 联表查询

# 查询所有学生的学号、姓名、选课数、总成绩　
select id,count(course_id),ifnull(sum(score),0) from student left join score on student.id = score.user_id group by user_id,id;

# 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select user_id,name,avg(score) from score left join student on user_id = id group by user_id,id having avg(score) > 85;

# 查询学生的选课情况：学号，姓名，课程号，课程名称
select student.id,student.name,course_id,c.name from student inner join score on student.id = score.user_id inner join course c on score.course_id = c.id;

# 查询出每门课程的及格人数和不及格人数
select course_id,sum(case when score < 80 then 1 else 0 end) as '<60',sum(case when score >= 80 then 1 else 0 end) as '>=60' from score group by course_id;

# 使用分段[100-85]，分别统计：各分数段人数，课程号和课程名称
select course_id,sum(case when score >= 85 and score < 100 then 1 else 0 end) as '[100-85]' from score s left join course c on s.course_id = c.id group by s.course_id,c.id;

# 查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
select * from score sc inner join student st on sc.user_id = st.id and sc.course_id = '0003' and sc.score > 80;