MySQL 查询练习记录

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

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

数据准备

创建数据表

学生表 student:

1
2
3
4
5
6
7
create table student(
sno varchar(20) primary key,
name varchar(10) not null,
sex varchar(10) not null,
birthday datetime,
class varchar(20)
);

教师表 teacher:

1
2
3
4
5
6
7
8
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:

1
2
3
4
5
6
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:

1
2
3
4
5
6
7
8
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)
);

添加数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 学生表
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');

查询练习题汇总

参考答案

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
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;
 

EOF

 
05-02 16:20
查看更多