Mysql练习题目【7月10日更新】-CSDN博客

本文链接：https://blog.csdn.net/weixin_43654975/article/details/140310407

七、Mysql练习题目

https://zhuanlan.zhihu.com/p/38354000

1. 创建表

创建学生表

mysql> create table if not exists student(
    -> student_id varchar(255) not null,
    -> student_name varchar(255) not null,
    -> birthday date not null,
    -> gender varchar(255) not null,
    -> primary key(student_id)
    -> )default charset utf8;

创建成绩表

mysql> create table score(
  	-> student_id varchar(255) not null,
  	-> course_id varchar(255) not null,
  	-> score float(3) not null,
  	-> primary key(student_id,course_id)
		-> )default charset utf8;

创建课程表

mysql> create table course(
    -> course_id varchar(255) not null,
    -> course_name varchar(255) not null,
    -> teacher_id varchar(255) not null,
    -> primary key(course_id)
    -> )default charset utf8;

创建教师表

mysql> create table teacher(
  	-> teacher_id varchar(255) not null, 
  	-> teacher_name varchar(255) null, 
  	-> primary key(teacher_id) 
		-> ) default charset utf8;

2. 插入数据

学生表插入数据

insert into student
values
('0001','猴子','1989-01-01','男'),
('0002','猴子','1990-12-21','女'),
('0003','马云','1991-12-21','男'),
('0004','王思聪','1990-05-20','男');

成绩表插入数据

insert into score
values
('0001','0001',80),
('0001','0002',90),
('0001','0003',99),
('0002','0002',60),
('0002','0003',80),
('0003','0001',80),
('0003','0002',80),
('0003','0003',80);

课程表插入数据

insert into course
values
('0001','语文','0002'),
('0002','数学','0001'),
('0003','英语','0003');

教师表插入数据

insert into teacher 
values
('0001','孟扎扎'),
('0002','马化腾'),
('0003',null),
('0004','');

3. 题目

1.查询姓“猴”的学生名单

select * from student where student_name like "猴%";

2.查询名字中最后一个字是猴的学生名单

select * from student where student_name like "%猴";

3.查询名字中带猴的学生名单

select * from student where student_name like "%猴%";

4.查询名字中第二个字是猴的学生名单

select * from student where student_name like "_猴%";

5.查询姓“孟”老师的个数

关键字：个数-count

select count(*) from teacher where teacher_name like "孟%";

6.查询课程编号为“0002”的总成绩

关键字：总成绩-sum

select sum(score) from score where course_id='0002';

7.查询选了课程的学生人数*

关键字：人数-count

select 学号，成绩表里学号有重复值需要去掉

select count(distinct student_id) as 学生人数 from score;

8.查询各科成绩最高和最低的分

关键字：各科-分组；最高分-max ；最低分-min

select course_id,min(score) as 最低分,max(score) as 最高分 from score group by course_id;

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

关键字：每门-分组；学生数-count

select course_id as 课程,count(student_id) as 学生个数 from score group by course_id;

10.查询男生、女生人数

关键字：男生、女生（相当于每，因为性别只有男、女）-分组；人数-count

select gender,count(*) from student group by gender;

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

关键字：平均成绩大于-having avg()>

select student_id,avg(score) from score group by student_id having avg(score)>60;

12.查询至少选修两门课程的学生学号

关键字：至少-count

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

13.查询同名同姓学生名单并统计同名人数*

关键字：人数-count

select student_name,count(student_id) from student group by student_name having count(student_id) >=2;

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

select * from score where score<60 order by course_id desc;

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

关键字：每门-group by；平均成绩-avg

select course_id,avg(score) as 平均成绩 from score group by course_id order by 平均成绩,course_id desc;

16.检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列

select student_id student from score where course_id='0004' and score<60 order by score desc;

17.统计每门课程的学生选修人数(超过2人的课程才统计)

关键字：每门-group by；人数-count

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

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

19.查询每个学生的总成绩并进行排名

关键字：每个-group by；总成绩-sum

select student_id,sum(score) as 总成绩 from score group by student_id order by 总成绩;

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

关键字：平均成绩大于-having avg()>60

select student_id,avg(score) as 平均成绩 from score group by student_id having avg(score)>60;

21.查询所有课程成绩小于60分学生的学号、姓名

关键字：所有成绩小于60-where score < 60

因为姓名在student表中，成绩在score表中，所以要用到子查询。

注意：如果(值1,值2,……)存在重复值时，in (值1,值2,……) 会从(值1,值2,……)中的重复值中选择一个。即in会过滤掉重复数据

select student_id,student_name from student where student_id in (select student_id from score where score<60);

22.查询没有学全所有课的学生的学号、姓名*

select student_id,student_name from student where student_id in (select student_id from score group by student_id having count(course_id) < (select count(*) from course));

23.查询出只选修了两门课程的全部学生的学号和姓名

select student_id,student_name from student where student_id in (select student_id from score group by student_id having count(course_id)=2);

24.查询各科成绩前两名的记录*

关键字：各科-group by

https://mp.weixin.qq.com/s/MuxjlFV0gi1GydOrYfiSeQ

25.查找1990年出生的学生名单*

select * from student where year(birthday) = 1990;

或

select * from student where birthday like '1990-%';

26.查询各学生的年龄（精确到年）*

select student_id, year(current_time)-year(birthday)+1 from student;

27.查询各学生的年龄（精确到月份）*

select student_id,timestampdiff(month ,birthday ,now())/12 from student;

28.查询本月过生日的学生*

select * from student where month(birthday)=month(current_date);

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

要显示的列：学号，姓名，选课数，总成绩，分布在两个表中，所以应该用连表查询，将两个表连接起来

关键字：所有学生-左连接或右连接：左连接的话join左边的表应该为student，右连接的话join右边应该为student表。

注意：分组时，如果要用外连接的条件分组，则选择的那个表中的列中的数据应该是唯一的，及s.student_id

select s.student_id as 学号,s.student_name as 姓名,count(c.course_id) as 选课数,sum(c.score) as 总成绩 from student as s left join score as c on s.student_id=c.student_id group by s.student_id;

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

select t1.student_id as '学号',t1.student_name as '姓名',avg(score) as '平均成绩' from student as t1 left join score as t2 on t1.student_id = t2.student_id group by  t1.student_id having avg(t2.score)>85;

31.查询学生的选课情况：学号，姓名，课程号，课程名称*

select t1.student_id,t1.student_name,t3.course_id,t3.course_name from student as t1 left join score as t2 on t1.student_id=t2.student_id left join course as t3 on t2.course_id=t3.course_id;

32.查询出每门课程的及格人数和不及格人数*

关键字：及格和不及格-分类：case语句

select course_id, sum(case when score>=60 then 1 else 0 end) as 及格人数, sum(case when score<60 then 1 else 0 end) as 不及格人数 from score group by course_id;

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

select a.课程号,b.课程名称,
sum(case when 成绩 between 85 and 100 
	 then 1 else 0 end) as '[100-85]',
sum(case when 成绩 >=70 and 成绩<85 
	 then 1 else 0 end) as '[85-70]',
sum(case when 成绩>=60 and 成绩<70  
	 then 1 else 0 end) as '[70-60]',
sum(case when 成绩<60 then 1 else 0 end) as '[<60]'
from score as a right join course as b 
on a.课程号=b.课程号
group by a.课程号,b.课程名称;

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

select student_id,student_name from student where student_id in (select student_id from score where course_id='0003' and score>80);

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

select t1.*,t2.score from student as t1 left join score as t2 on t1.student_id=t2.student_id where t2.course_id = 0001 and t2.score>60 order by t2.score desc;

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

注意：select只能是聚合函数或group by后面的字段，否则会报SELECT list is not in GROUP BY clause and contains nonaggregated column的错误。原因是sql_mode模式的限制。可以修改这个模式，修改方法：https://blog.csdn.net/weixin_42085125/article/details/115335503

因为我们要查询t2.course_id，所以要在group by后面加上t2.course_id

select t1.teacher_name,t2.course_id,avg(t3.score) from teacher as t1 inner join course as t2 on t1.teacher_id=t2.teacher_id inner join score as t3 on t2.course_id=t3.course_id group by t1.teacher_id,t2.course_id order by avg(score) desc;