MySQL 查询练习记录

2023-08-07 21:46:22

MySQL 查询练习记录

最近在复习mysql，在b站上找了一个感觉还不错的视频，把视频中查询练习相关的内容记录了下来，以便自己日后查阅和复习。

视频连接：https://www.bilibili.com/video/av39807944/?p=1

数据准备

创建数据表

学生表 student：

create table student(
	sno varchar(20) primary key,
	name varchar(10) not null,
	sex varchar(10) not null,
	birthday datetime,
	class varchar(20)
);

教师表 teacher：

create table teacher(
	tno varchar(20) primary key,
	name varchar(20) not null,
	sex varchar(10) not null,
	birthday datetime,
	prof varchar(20),
	depart varchar(20) not null
);

课程表 course：

create table course(
	cno varchar(20) primary key,
	name varchar(32) not null,
	tno varchar(20) not null,
	foreign key(tno) references teacher(tno)
);

成绩表 score：

create table score(
	sno varchar(20) not null,
	cno varchar(20) not null,
	score decimal,
	foreign key(sno) references student(sno),
	foreign key(cno) references course(cno),
	primary key(sno, cno)
);

添加数据

-- 学生表
insert into student values('108','曾华','男','1977-09-01','95033');
insert into student values('105','匡明','男','1975-10-02','95031');
insert into student values('107','王丽','女','1976-01-23','95033');
insert into student values('101','李军','男','1976-02-20','95033');
insert into student values('109','王芳','女','1975-02-10','95031');
insert into student values('103','陆君','男','1974-06-03','95031');
insert into student values('102','后裔','男','1976-02-20','95033');
insert into student values('104','赵云','男','1975-02-10','95031');
insert into student values('106','王昭君','女','1974-06-03','95031');
-- 教师表
insert into teacher values('804','李成','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王小义','女','1972-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-08-15','猪脚','电子工程系');
-- 课程表
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');
-- 成绩表
insert into score values('103','3-245','86');
insert into score values('105','3-245','92');
insert into score values('109','3-245','75');
insert into score values('103','3-105','58');
insert into score values('105','3-105','88');
insert into score values('109','3-105','76');
insert into score values('103','6-166','79');
insert into score values('105','6-166','81');
insert into score values('109','6-166','94');
insert into score values('102','3-105','90');
insert into score values('106','3-105','82');

查询练习题汇总

查询student表的所有记录

查询student表中的所有记录的name、sex和class列

查询教师所有的部门即不重复的depart列

查询score表中成绩在60到80之间的所有记录

查询score表中成绩为85，86或88的记录

查询student表中“95031”班或性别为“女”的同学记录

以class降序查询student表的所有记录

以cno升序、degree降序查询score表的所有记录

查询“95031”班的学生人数

查询score表中的最高分的学生学号和课程号（子查询或者排序）

查询每门课的平均成绩

查询score表中至少有2名学生选修的并以3开头的课程的平均分数

查询分数大于70，小于90的sno列

查询所有学生的 sname、cno 和 degree 列

查询所有学生的sno、cname和degree列

查询所有学生的sname、cname和degree列

查询“95031”班学生每门课的平均分

查询选修“3-105”课程的成绩高于“109”号同学“3-105”课程成绩的所有同学的记录

查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录

查询和学号为108、101的同学同年出生的所有学生的sno、sname和sbirthday列

查询“张旭”教师任课的学生成绩

查询选修某课程的同学人数多于5人的教师姓名

查询“95033”班和“95031”班全体学生的记录

查询存在有85分以上成绩的课程cno

查询出“计算机系”教师所教课程的成绩表

查询“计算机系”和“电子工程系”不同职称的教师的tname和prof

查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的同学的cno、sno、和degree，并按degree从高到低次序排序

查询选修编号为“3-105”课程且成绩高于选修编号为“3-245”的同学的cno、sno、和degree

查询所有教师和同学的name、sex和birthday

查询所有“女”教师和“女”同学的name、sex和birthday

查询成绩比该课程平均成绩低的同学的成绩表

查询所有任课教师的name和depart

查询至少有两名男生的班号

查询student表中不姓“王”的同学记录

查询student表中每个学生的姓名和年龄

查询student表中最大和最小的sbirthday日期值

以班号和年龄从大到小的顺序查询student表中的全部记录

查询男教师及其所上的课程

查询最高分同学的sno、cno和degree列

查询所有和李军同性别的同学的名字

查询和“李军”同性别并同班的同学的名字

查询所有选修“计算机导论”课程的“男”同学的成绩表

假设使用如下命令创建了一个grade表：
1
2
3
4
5
6
7
8
9
10
11
> create table grade(
> 	low int(3),
> 	up int(3),
> 	grade char(1)
> );
> insert into grade values(90,100,"A");
> insert into grade values(80,89,"B");
> insert into grade values(70,79,"C");
> insert into grade values(60,69,"D");
> insert into grade values(50,59,"E");
>

现查询所有同学的sno、cno和grade列。

参考答案

1. select * from student;
2. select name,sex,class from student;
3. select distinct depart from teacher;
4. select * from score where degree between 60 and 80;
或 select * from score where degree > 60 and degree < 80;
5. select * from score where degree in (85,86,88);
6. select * from student where class="95031" or sex="女";
7. select * from student order by class desc;
8. select * from score order by cno asc,degree desc;
9. select count(*) from student where class='95031';
10. select sno, cno from score order by degree desc limit 1;
或 select sno,cno from score where degree=(select max(degree) from score);
11. select cno,avg(degree) from score group by cno;
12. select cno,avg(degree) from score group by cno having count(cno)>=2 and cno like '3%';
13. select sno,degree from score where degree > 70 and degree < 90;
14. select name,cno,degree from student,score where student.sno=score.sno;
15. select sno,name,degree from course,score where course.cno=score.cno;
16. select student.name as sname,course.name as cname,score.degree from student,course,score where student.sno=score.sno and course.cno=score.cno;
17. select cno,avg(degree) from score where sno in (select sno from student where class='95031') group by cno;
18. select * from score where degree > (select degree from score where sno='109' and cno='3-105') and cno='3-105';
19. select * from score where degree > (select degree from score where sno='109' and cno='3-105');
20. select sno,name,birthday from student where year(birthday) in (select year(birthday) from student where sno in (101,108));
21. select * from score where cno=(select cno from course where tno=(select tno from teacher where name='张旭'));
22. select name from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(cno)>=5));
23. select * from student where class in ("95033","95031");
24. select cno,degree from score where degree > 85;
25. select * from score where cno in (select cno from course where tno in (select tno from teacher where depart="计算机系"));
26. select name,prof from teacher where depart='电子工程系' and prof not in (select prof from teacher where depart='计算机系')
union
select name,prof from teacher where depart='计算机系' and prof not in (select prof from teacher where depart='电子工程系');
27. select * from score
where cno='3-105'
and degree>any(select degree from score where cno='3-245')
order by degree desc;
28. select * from score
where cno='3-105'
and degree>all(select degree from score where cno='3-245');
29. select name,sex,birthday from teacher
union
select name,sex,birthday from student;
30. select name,sex,birthday from teacher where sex='女'
union
select name,sex,birthday from student where sex='女';
31. select * from score a where degree < (select avg(degree) from score b where a.cno=b.cno);
32. select teacher.name as tname,course.name as cname,teacher.depart from teacher,course where teacher.tno=course.tno;
33. select class from student where sex='男' group by class having count(class)>2;
34. select * from student where name not like "王%";
35. select name,year(now())-year(birthday) as '年龄' from student;
36. select max(birthday) as max_bd,min(birthday) as min_bd from student;
37. select * from student order by class desc,birthday;
38. select * from course where tno in (select tno from teacher where sex='男');
39. select * from score where degree=(select max(degree) from score);
40. select name from student where sex=(select sex from student where name='李军');
41. select name from student
where sex=(select sex from student where name='李军')
and class=(select class from student where name='李军');
42. select * from score
where cno=(select cno from course where name='计算机导论')
and sno in (select sno from student where sex='男');
43. select sno,cno,degree,grade from score,grade where degree between low and up;

码农公寓

最近在复习mysql，在b站上找了一个感觉还不错的视频，把视频中查询练习相关的内容记录了下来，以便自己日后查阅和复习。

数据准备

查询练习题汇总

参考答案

相关文章