mysql查询题目

Posted 2022-09-1 Updated 2023-03- 25

By qiaoqiao

22~28 min read

sql查询题目

sql语句创建表

drop TABLE if EXISTS student;
CREATE TABLE student (
    id INT(10) PRIMARY key,
    name VARCHAR (10),
    age INT (10) NOT NULL,
    gander varchar(2)
);

drop TABLE if EXISTS course;
CREATE TABLE course (
  id INT (10)  PRIMARY key,
  name VARCHAR (10) ,
  t_id INT (10) 
) ;

drop TABLE if EXISTS teacher;
CREATE TABLE teacher(
  id INT (10)  PRIMARY key,
  name VARCHAR (10) 
);

drop TABLE if EXISTS scores;
CREATE TABLE scores(
  s_id INT ,
  score INT (10),
  c_id INT (10) ,
	PRIMARY key(s_id,c_id)
) ;

插入数据

insert into  student (id,name,age,gander)VALUES(1,'白杰',19,'男'),(2,'连宇栋',19,'男'),(3,'邸志伟',24,'男'),(4,'李兴',11,'男'),(5,'张琪',18,'男'),(6,'武三水',18,'女'),(7,'张志伟',16,'男'),(8,'康永亮',23,'男'),(9,'杨涛瑞',22,'女'),(10,'王杰',21,'男');

insert into  course (id,name,t_id)VALUES(1,'数学',1),(2,'语文',2),(3,'c++',3),(4,'java',4),(5,'php',null);

insert into  teacher (id,name)VALUES(1,'张楠'),(2,'李子豪'),(3,'薇薇姐'),(4,'猴哥'),(5,'八戒');

insert into  scores (s_id,score,c_id)VALUES(1,80,1);
insert into  scores (s_id,score,c_id)VALUES(1,56,2);
insert into  scores (s_id,score,c_id)VALUES(1,95,3);
insert into  scores (s_id,score,c_id)VALUES(1,30,4);
insert into  scores (s_id,score,c_id)VALUES(1,76,5);

insert into  scores (s_id,score,c_id)VALUES(2,35,1);
insert into  scores (s_id,score,c_id)VALUES(2,86,2);
insert into  scores (s_id,score,c_id)VALUES(2,45,3);
insert into  scores (s_id,score,c_id)VALUES(2,94,4);
insert into  scores (s_id,score,c_id)VALUES(2,79,5);

insert into  scores (s_id,score,c_id)VALUES(3,65,2);
insert into  scores (s_id,score,c_id)VALUES(3,85,3);
insert into  scores (s_id,score,c_id)VALUES(3,37,4);
insert into  scores (s_id,score,c_id)VALUES(3,79,5);

insert into  scores (s_id,score,c_id)VALUES(4,66,1);
insert into  scores (s_id,score,c_id)VALUES(4,39,2);
insert into  scores (s_id,score,c_id)VALUES(4,85,3);

insert into  scores (s_id,score,c_id)VALUES(5,66,2);
insert into  scores (s_id,score,c_id)VALUES(5,89,3);
insert into  scores (s_id,score,c_id)VALUES(5,74,4);


insert into  scores (s_id,score,c_id)VALUES(6,80,1);
insert into  scores (s_id,score,c_id)VALUES(6,56,2);
insert into  scores (s_id,score,c_id)VALUES(6,95,3);
insert into  scores (s_id,score,c_id)VALUES(6,30,4);
insert into  scores (s_id,score,c_id)VALUES(6,76,5);

insert into  scores (s_id,score,c_id)VALUES(7,35,1);
insert into  scores (s_id,score,c_id)VALUES(7,86,2);
insert into  scores (s_id,score,c_id)VALUES(7,45,3);
insert into  scores (s_id,score,c_id)VALUES(7,94,4);
insert into  scores (s_id,score,c_id)VALUES(7,79,5);

insert into  scores (s_id,score,c_id)VALUES(8,65,2);
insert into  scores (s_id,score,c_id)VALUES(8,85,3);
insert into  scores (s_id,score,c_id)VALUES(8,37,4);
insert into  scores (s_id,score,c_id)VALUES(8,79,5);

insert into  scores (s_id,score,c_id)VALUES(9,66,1);
insert into  scores (s_id,score,c_id)VALUES(9,39,2);
insert into  scores (s_id,score,c_id)VALUES(9,85,3);
insert into  scores (s_id,score,c_id)VALUES(9,79,5);

insert into  scores (s_id,score,c_id)VALUES(10,66,2);
insert into  scores (s_id,score,c_id)VALUES(10,89,3);
insert into  scores (s_id,score,c_id)VALUES(10,74,4);
insert into  scores (s_id,score,c_id)VALUES(10,79,5);

题目

1.查询‘01’号学生的姓名和各科成绩。难度：两颗星

select st.name,c.name '成绩',sc.score 
from student st 
join scores sc on st.id = sc.s_id 
join course c on sc.c_id = c.id
where st.id = '01';

2.查询各个学科的平均成绩，最高成绩。难度：两颗星

select name,avg(score),max(score) 
from scores 
join course on c_id = id 
group by c_id

3.查询每个同学的最高成绩及科目名称。难度：四颗星

4.查询所有姓张的同学的各科成绩。难度：两颗星

select st.name,score,c.name 
from student st 
left join scores sc on st.id = sc.s_id 
left join course c on sc.c_id = c.id
where st.name like '张%'；

5.查询每个课程最高分的同学信息。难度：五颗星

6.查询名字中含有“张”和‘李’字的学生信息和各科成绩。难度：两颗星

select st.*,c.name '科目',sc.score 
from student st 
join scores sc on st.id = sc.s_id
join course c on sc.c_id = c.id
where st.name like '%张%' or '%李%';

7.查询平均成绩及格的同学的信息。难度：三颗星

select * 
from student 
where id in (
            select s_id 
            from scores
            group by s_id 
            having avg(score) > 60;
            )

8.将学生按照总分数进行排名。难度：三颗星

select st.name,sum(score) sum_score 
from student st 
join scores sc on st.id = sc.s_id
group by s_id
order by sum_score desc;

9.查询数学成绩的最高分、最低分、平均分。难度：两颗星

select max(score),min(score),avg(score) 
from scores
where c_id = (
            select id 
            from course
            where name = '数学'
            );

10.将各科目按照平均分排序。难度：两颗星

select c.name,avg(score) 
from scores sc
join course c on sc.c_id = c.id
group by sc.c_id
order by avg(score)

11.查询老师的信息和他所带科目的平均分。难度：三颗星

select t.*,c.name '课程',avg(score) 
from scores sc 
join course c on sc.c_id = c.id
join teacher t on c.t_id = t.id
group by c_id

12.查询被“张楠”和‘‘李子豪’教的课程的最高分和平均分。难度：三颗星

select t.name,c.name '课程',max(score),avg(score) 
from scores sc 
join course c on sc.c_id = c.id
join teacher t on c.t_id = t.id
group by c_id
having t.name in('张楠','李子豪');

13.查询每个同学的最好成绩的科目名称。难度：五颗星

14.查询所有学生的课程及分数。难度：一颗星

select st.name,c.name '成绩',score
from student st
left join scores sc on st.id = sc.s_id
left join course c on sc.c_id = c.id；

15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名。难度：两颗星

select st.id,st.name 
from student st
join scores sc on st.id = sc.s_id
join course c on sc.c_id = c.id
where c.id = 1 and score > 60;

16.查询平均成绩大于等于75的所有学生的学号、姓名和平均成绩。难度：三颗星

select st.id,st.name,avg(score) 
from student st
join scores sc on st.id = sc.s_id
group by st.name
having avg(score) >= 75;

17.查询有不及格课程的同学信息。难度：四颗星

select * 
from student 
where id in(
            select distinct(s_id) 
            from scores 
            where score < 60
            )

18.求每门课程的学生人数。难度：两颗星

select name,count(*) 
from scores sc
join course c on c_id = id
group by c_id;

19.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列。难度：两颗星

select name,avg(score) 
from scores
join course on c_id = id
group by c_id
order by avg(score) desc,id;

20.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。难度：三颗星

select st.id,st.name,avg(score) 
from student st 
join scores sc on st.id = sc.s_id 
group by s_id
having avg(score) >= 60;

21.查询有且仅有一门课程成绩在90分以上的学生信息；难度：三颗星

select st.* 
from student st
join scores sc on st.id = sc.s_id
where score > 90
group by NAME
having count(name) = 1;

22.查询出只有三门课程的全部学生的学号和姓名。难度：三颗星

select id,name 
from student 
where id in(
            select s_id 
            from scores 
            group by s_id
            having count(c_id) = 3
            );

23.查询有不及格课程的课程信息。难度：三颗星

select * 
from course 
where id in(
            select distinct(c_id) 
            from scores 
            where score < 60
            );

24.检索至少选修5门课程的学生学号。难度：三颗星

select id 
from student 
where id  in(
            select s_id 
            from scores 
            group by s_id 
            having count(c_id) >=5
            );

25.查询没有学全所有课程的同学的信息。难度：四颗星

select * 
from student 
where id in(
			select s_id 
            from scores 
            group by s_id 
            having count(c_id) <(
                                select count(*) 
                                from course
                                ));

26.查询学全所有课程的同学的信息。难度：四颗星

select * 
from student 
where id in(
            select s_id 
            from scores 
            group by s_id 
            having count(c_id) =(
                                select count(*) 
                                from course
                                ));

27.查询各学生都选了多少门课。难度：两颗星

select st.name,count(c_id) 
from student st
join scores sc on st.id = sc.s_id
group by s_id;

28.查询课程名称为”java”，且分数低于60的学生姓名和分数。难度：三颗星

select st.name,score 
from student st
join scores sc on st.id = sc.s_id
join course c on sc.c_id = c.id 
where c.name = 'java' and score < 60;

29.查询学过”张楠”老师授课的同学的信息。难度：四颗星

select * 
from student 
where id in (
            select s_id 
            from scores 
            where c_id =(
                        select id 
                        from course c 
                        where t_id =(
                                    select id 
                                    from teacher 
                                    where name = '张楠'
                                     )));

30.查询没学过“张楠”老师授课的同学的信息。难度：五颗星

select name 
from student 
where name not in(
                select st.name 
                from student st
                join scores sc on st.id = sc.s_id 
                join course c on sc.c_id = c.id 
                join teacher t on c.t_id = t.id
                where t.name = '张楠'
                );

其他

mysql

License: CC BY 4.0