Mysql 复健

sinarcsinx2024-12-272024-12-30

数据

-- 创建学生表并插入数据
DROP TABLE student;
CREATE TABLE student (
  s_id INT NOT NULL PRIMARY KEY,
  s_name VARCHAR ( 10 ) NOT NULL,
  s_sex VARCHAR ( 5 ) NOT NULL,
s_age INT NOT NULL 
);
DELETE FROM student;
INSERT INTO student VALUES(1,'胡图图','男',6);
INSERT INTO student VALUES(2,'牛爷爷','男',55);
INSERT INTO student VALUES(3,'壮壮妈','女',33);
INSERT INTO student VALUES(4,'胡英俊','男',32);
INSERT INTO student VALUES(5,'壮壮','男',7);
INSERT INTO student VALUES(6,'张小丽','女',31);
INSERT INTO student VALUES(7,'小怪','男',2);
-- 创建教师表并插入数据
DROP TABLE teacher;
CREATE TABLE teacher (
t_id INT,
t_name VARCHAR ( 10 ));
DELETE FROM teacher;
INSERT INTO teacher VALUES(1,'图图妈');
INSERT INTO teacher VALUES(2,'神厨小福贵');
INSERT INTO teacher VALUES(3,'健康哥哥');
-- 创建成绩表并插入数据
DROP TABLE score;
CREATE TABLE score ( s_id INT, c_id INT, s_score INT );
DELETE FROM score;
INSERT INTO score VALUES(1,2,90);
INSERT INTO score VALUES(1,3,50);
INSERT INTO score VALUES(2,1,60);
INSERT INTO score VALUES(2,3,80);
INSERT INTO score VALUES(3,1,80);
INSERT INTO score VALUES(3,2,50);
INSERT INTO score VALUES(3,3,80);
INSERT INTO score VALUES(4,1,70);
INSERT INTO score VALUES(4,2,60);
INSERT INTO score VALUES(4,3,70);
INSERT INTO score VALUES(5,2,85);
INSERT INTO score VALUES(5,3,55);
INSERT INTO score VALUES(6,1,95);
INSERT INTO score VALUES(6,2,65);
INSERT INTO score VALUES(6,3,100);
-- 创建课程表并插入数据
DROP TABLE course;
CREATE TABLE course ( 
c_id INT PRIMARY KEY, 
c_name VARCHAR ( 10 ) NOT NULL, 
t_id INT NOT NULL );
DELETE FROM course;
INSERT INTO course VALUES(1,'厨艺',2);
INSERT INTO course VALUES(2,'体育',3);
INSERT INTO course VALUES(3,'生活',1);

题目

查询课程编号为1的课程比2的课程成绩高的所有学生的学号（子查询+连接）

select student.s_id
from student
left join (select s_id,s_score as c1 from score where c_id=1) as sc1 on student.s_id=sc1.s_id
left join (select s_id,s_score as c2 from score where c_id=2) as sc2 on student.s_id=sc2.s_id
where c1 > c2;

查询平均成绩大于60分的学生的学号和平均成绩

select s_id,AVG(s_score) as avg_sc
from score
group by s_id
having avg_sc>60;

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

select student.s_id,s_name,COUNT(c_id),SUM(case when s_score is null then 0 else s_score end)
from student
left join score on student.s_id=score.s_id
group by s_id;

查询姓“刘”的老师的个数

1
2
3

select COUNT(t_id)
from teacher
where t_name like '刘%';

查询没学过“图图妈”老师课的学生的学号、姓名

select s_id,s_name
from student
where s_id not in (select s_id
              from score
              where c_id in (select c_id
                            from course
                            where t_id=(select t_id
                                        from teacher
                                        where t_name='图图妈')));

查询学过“健康哥哥”老师所教的所有课的同学的学号、姓名

select s_id,s_name
from student
where s_id in (select s_id
              from score
              where c_id in (select c_id
                            from course
                            where t_id=(select t_id
                                        from teacher
                                        where t_name='健康哥哥')));

查询学过编号为1的课程并且也学过编号为2的课程的学生的学号、姓名

select student.s_id,s_name
from student
inner join (select s_id,s_score as c1 from score where c_id=1) as sc1 on student.s_id=sc1.s_id
inner join (select s_id,s_score as c2 from score where c_id=2) as sc2 on student.s_id=sc2.s_id;

查询课程编号为2的课程总成绩

1
2
3

select SUM(s_score)
from score
where c_id=2;

查询所有课程成绩小于60分的学生的学号、姓名，未选课程则成绩视为0，最大值小于60则均小于60，此外考虑无成绩学生

select s_id,s_name
from student
where (select MAX(s_score)
      from score
      group by s_id
      having score.s_id=student.s_id)<60
or
s_id not in (select distinct s_id
            from score);

查询至少有一门课与学号为1的学生所学课程相同的学生的学号和姓名

select distinct student.s_id,s_name
from student
left join score on score.s_id=student.s_id
where c_id in (select c_id
                from score
                where s_id=1)
and student.s_id!=1;

查询和1号同学所学课程完全相同的其他同学的学号

select s_id,s_name
from student
where s_id in (select s_id
              from score
              where s_id != 1
              group by s_id
              having COUNT(*)=(select COUNT(c_id)
                              from score
                              where s_id=1)
              and s_id not in (select distinct s_id
                              from score
                              where c_id not in (select c_id
                                                from score
                                                where s_id=1)));

查询没学过”神厨小福贵”老师讲授的任一门课程的学生姓名

select s_name
from student
where s_id not in (select s_id
                from score
                where c_id in (select c_id
                                from course
                                where t_id=(select t_id
                                            from teacher
                                            where t_name = '神厨小福贵')));

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（COUNT内不可加查询）

select student.s_id,s_name,AVG(s_score)
from student
left join score on student.s_id=score.s_id
where student.s_id in (select s_id
                from score
                where s_score<60
                group by s_id
                having COUNT(c_id)>=2)
group by s_id;

检索课程1分数小于60，按分数降序排列的学生信息

select student.*
from student,score
where student.s_id=score.s_id
and c_id=1
and s_score<60
order by s_score desc;

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select s_id,s_name,
(select s_score from score where s_id=student.s_id and c_id=1) as score1,
(select s_score from score where s_id=student.s_id and c_id=2) as score2,
(select s_score from score where s_id=student.s_id and c_id=3) as score3,
(select AVG(s_score) from score where s_id=student.s_id) as avg_score
from student
order by avg_score desc;

查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率（按课程分组，逻辑）

select sc.c_id '课程ID',c_name '课程名',
MAX(s_score) '最高分',
MIN(s_score) '最低分',
AVG(s_score) '平均分',
(select COUNT(*) from score where c_id=sc.c_id and s_score>=60)/COUNT(*) '及格率'
from score as sc,course
where sc.c_id=course.c_id
group by sc.c_id;

按各科成绩进行排序，并显示排名(难)（1）按照每个科目进行一次排名（2）对所有分数进行一个总的排名

select student.s_id,s_name,c_id,s_score,
row_number() over (partition by c_id order by s_score desc) as 'rank'
from student,score
where student.s_id=score.s_id
order by c_id;

查询学生的总成绩并进行排名

select s_id,s_name,total,row_number() over (order by total desc) as trank
from (select student.s_id,s_name,SUM(s_score) as total
     from student
     left join score on student.s_id=score.s_id
     group by student.s_id) as sub_quary
order by trank;

查询不同老师所教不同课程平均分从高到低显示

select t_name,c_name,avg_score
from teacher
left join course on teacher.t_id=course.t_id
left join (select c_id,AVG(s_score) as avg_score from score group by c_id) as avg_table on course.c_id=avg_table.c_id
order by avg_score desc;

查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

select *
from (select student.*,c_id,s_score,row_number() over (partition by c_id order by s_score desc) as crank
     from student
     right join score on student.s_id=score.s_id) as sub_quary
where crank in (2,3);

使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

select sc.c_id,c_name,COUNT(sc.c_id) as '总数',
(select COUNT(c_id) as '100-85' from score where c_id=sc.c_id and s_score between 85 and 100) as '100-85',
(select COUNT(c_id) as '100-85' from score where c_id=sc.c_id and s_score between 70 and 85) as '85-70',
(select COUNT(c_id) as '100-85' from score where c_id=sc.c_id and s_score between 60 and 70) as '70-60',
(select COUNT(c_id) as '100-85' from score where c_id=sc.c_id and s_score<60) as '<60'
from score as sc
left join course on sc.c_id=course.c_id
group by sc.c_id

查询学生平均成绩及其名次。考察点：row_number 的用法,记得里面可以指定是升序还是降序排列

select student.s_id,s_name,avg_score,row_number() over (order by avg_score desc) as sc_rank
from student,(select s_id,AVG(s_score) as avg_score
             from score
             group by s_id) as sub_quary
where student.s_id=sub_quary.s_id
order by sc_rank;

查询各科成绩前三名的记录（不考虑成绩并列情况）

select student.s_id,s_name,c_id,s_score,sc_rank
from student,(select s_id,c_id,s_score,row_number() over (partition by c_id order by s_score desc) as sc_rank
                from score
                order by c_id) as sub_quary
where student.s_id=sub_quary.s_id
and sc_rank<=3
order by c_id,sc_rank;

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

1
2
3

select c_id,COUNT(s_id) as stnum
from score
group by c_id;

查询出只有两门课程的全部学生的学号和姓名

select s_id,s_name
from student
where s_id in (select s_id
                from score
                group by s_id
                having COUNT(s_id)=2);

查询男生、女生人数

1
2
3

select s_sex,COUNT(s_id) as '人数'
from student
group by s_sex;

查询名字中含有”状”字的学生信息

1
2
3

select *
from student
where s_name like '%壮%';

查询1990年出生的学生名单

1
2
3

select * 
from student
where YEAR(s_birth)=1990;

查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select s_id,s_name,(select AVG(s_score)
                   from score
                   where student.s_id=s_id
                   group by s_id) as avg_score
from student
having avg_score>=85;

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

select c_id,AVG(s_score) as avg_score
from score
group by c_id
order by avg_score asc,c_id desc;

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

select s_name,s_score
from student
right join (select s_id,s_score
            from score
            where c_id in (select c_id
                            from course
                            where c_name='生活')
            and s_score<60) as sub_quary on student.s_id=sub_quary.s_id

查询所有学生的课程及分数情况

select student.s_id,s_name,score.c_id,course.c_name,s_score
from student
right join score on score.s_id=student.s_id
right join course on course.c_id=score.c_id;

查询任何一门课程成绩在70分以上的姓名、课程名称和分数

select student.s_id,s_name,c_name,s_score
from student
right join (select s_id,c_id,s_score
            from score
            where s_score>=70) as sub_quary on student.s_id=sub_quary.s_id
left join course on sub_quary.c_id=course.c_id;

查询不及格的课程并按课程号从大到小排列

select *
from score
where s_score<60
order by c_id desc;

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

select s_id,s_name
from student
where s_id in (select s_id
              from score
              where c_id=3
              and s_score>80);

求每门课程的学生人数

select score.c_id,c_name,COUNT(s_id)
from score
left join course on score.c_id=course.c_id
group by score.c_id;

查询选修“图图妈”老师所授课程的学生中成绩最高的学生姓名及其成绩

select s_name,s_score
from student
right join (select s_id,s_score
            from score
            where c_id in (select c_id
                            from course
                            where t_id=(select t_id
                                        from teacher
                                        where t_name='图图妈'))
            and s_score=(select MAX(s_score)
                        from score)) as sub_quary on student.s_id=sub_quary.s_id;

查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select sc.s_id,sc.c_id,sc.s_score
from score as sc
join score as sc1 on sc.s_score=sc1.s_score
                    and sc.s_id <> sc1.s_id
                    and sc.c_id <> sc1.c_id
order by sc.s_score;

自联结

查询每门功成绩最好的前两名

select *
from (select s_name,c_id,row_number() over (partition by c_id order by s_score desc) as sc_rank,s_score
        from student
        right join score on student.s_id=score.s_id) as sub_quary
where sc_rank<3;

统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c_id,COUNT(s_id) as nums
from score
group by c_id
having COUNT(s_id)>5
order by nums desc,c_id asc;

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

select s_id
from score
group by s_id
having COUNT(*)>1;

查询选修了全部课程的学生信息

select *
from student
where s_id in (select s_id
                from score
                group by s_id
                having COUNT(*)=(select COUNT(*)
                                from course));

查询各学生的年龄

1 2	select s_name,floor(datediff(now(),student.s_birth)/365) as age from student;

查询两门以上不及格课程的同学的学号及其平均成绩

select s_id,AVG(s_score) as avg_score
from score
where s_id in (select s_id
              from score
              where s_score<60
              group by s_id
              having COUNT(c_id)>2)
group by s_id;

查询本月过生日的学生

1
2
3

select *
from student
where month(now())=month(s_birth);

查询下一个月过生日的学生

1
2
3

select *
from student
where (month(now())+1)%12=month(s_birth);