MySQL的45道练习题

MySQL的45道练习题

#查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
mysql> select sno,cno from score where degree = (select max(degree) from score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)

mysql> select sno,cno from score order by degree desc limit 0,1;
+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)
#查询每门课的平均成绩。
mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     81.5000 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+
3 rows in set (0.02 sec)

#查询score表中至少有5名学生选修的并以3开头的课程的平均分数。
mysql> select avg(degree) from score where cno like '3%' and cno in (select cno from score group by cno having count(*) >= 5);
+-------------+
| avg(degree) |
+-------------+
|     81.5000 |
+-------------+
1 row in set (0.01 sec)

# 查询每门课的平均成绩。
mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     81.5000 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+
3 rows in set (0.02 sec)

mysql> select avg(degree) from score where cno like '3%' and cno in (select cno from score group by cno having count(*) >= 5);
+-------------+
| avg(degree) |
+-------------+
|     81.5000 |
+-------------+
1 row in set (0.01 sec)

#查询所有学生的Sname、Cname和Degree列。
mysql> select sname,cname,degree from student join score on student.sno = score.sno join course on course.cno = score.cno;
+--------+-----------------+--------+
| sname  | cname           | degree |
+--------+-----------------+--------+
| 陆君   | 操作系统        |     86 |
| 匡明   | 操作系统        |     75 |
| 王芳   | 操作系统        |     68 |
| 陆君   | 计算机导论      |     92 |
| 匡明   | 计算机导论      |     88 |
| 王芳   | 计算机导论      |     76 |
| 陆君   | 计算机导论      |     64 |
| 匡明   | 计算机导论      |     91 |
| 王芳   | 计算机导论      |     78 |
| 陆君   | 数字电路        |     85 |
| 匡明   | 数字电路        |     79 |
| 王芳   | 数字电路        |     81 |
+--------+-----------------+--------+
12 rows in set (0.01 sec)

# 查询“95031”班学生的平均分
mysql> select avg(degree) as 'class = 95031' from score where sno in (select sno from student where class = '95031');
+---------------+
| class = 95031 |
+---------------+
|       80.2500 |
+---------------+
1 row in set (0.00 sec)

#查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
mysql> select * from score where cno = '3-105' and degree > (select max(degree) from score where sno = '109' and cno = '3-105');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
| 105 | 3-105 |     91 |
+-----+-------+--------+
3 rows in set (0.02 sec)

#选了多门课程并且是这个课程下不是最高分的
mysql> select * from score a where sno in (select sno from score group by sno having count(*) > 1) and degree < (select max(degree) from score b where b.cno = a.cno);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
| 103 | 3-105 |     64 |
| 105 | 3-105 |     91 |
| 109 | 3-105 |     78 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
9 rows in set (0.02 sec)

mysql> use 45exercise;
Database changed

#查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
mysql> select sno,sname,sbirthday from student where year(sbirthday) = (select year(sbirthday) from student where sno = '108');
+-----+--------+---------------------+
| sno | sname  | sbirthday           |
+-----+--------+---------------------+
| 108 | 曾华   | 1977-09-01 00:00:00 |
+-----+--------+---------------------+
1 row in set (0.00 sec)

#查询“张旭“教师任课的学生成绩。
mysql> select sno,degree from score,course where score.cno = course.cno and course.tno = (select tno from teacher where tname = '张旭');
+-----+--------+
| sno | degree |
+-----+--------+
| 103 |     85 |
| 105 |     79 |
| 109 |     81 |
+-----+--------+
3 rows in set (0.01 sec)

#查询选修某课程的同学人数多于5人的教师姓名。
mysql> select tname from teacher,course where teacher.tno = course.tno and course.cno = (select cno from score group by cno having count(*) > 5 );
+--------+
| tname  |
+--------+
| 王萍   |
+--------+
1 row in set (0.00 sec)

#查询95033班和95031班全体学生的记录
mysql> select * from student where class in ('95033','95031');
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
6 rows in set (0.00 sec)

#查询存在有85分以上成绩的课程Cno.
mysql> select cno from score where degree > 85;
+-------+
| cno   |
+-------+
| 3-245 |
| 3-105 |
| 3-105 |
| 3-105 |
+-------+
4 rows in set (0.00 sec)

#查询出“计算机系“教师所教课程的成绩表。
mysql> select * from course where cno in (select cno from course where tno in (select tno from teacher where depart='计算机系' ));
+-------+-----------------+-----+
| cno   | cname           | tno |
+-------+-----------------+-----+
| 3-245 | 操作系统        | 804 |
| 3-105 | 计算机导论      | 825 |
+-------+-----------------+-----+
2 rows in set (0.00 sec)

#查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
mysql> select tname, prof from teacher where depart = '计算机系' and prof in (select prof from teacher where depart = '电子工程系') union select tname,prof from teacher where depart = '电子工程系' and prof not in (select prof from teacher where depart = '计算机系');
+--------+--------+
| tname  | prof   |
+--------+--------+
| 王萍   | 助教   |
| 张旭   | 讲师   |
+--------+--------+
2 rows in set (0.02 sec)

#查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
mysql> select cno,sno,degree from score where cno = '3-105' and degree > any(select degree from score where cno = '3-245') order by degree desc;
+-------+-----+--------+
| cno   | sno | degree |
+-------+-----+--------+
| 3-105 | 103 |     92 |
| 3-105 | 105 |     91 |
| 3-105 | 105 |     88 |
| 3-105 | 109 |     78 |
| 3-105 | 109 |     76 |
+-------+-----+--------+
5 rows in set (0.00 sec)

#查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
mysql> select cno,sno,degree from score where cno = '3-105' and degree > all(select degree from score where cno = '3-245') order by degree desc;
+-------+-----+--------+
| cno   | sno | degree |
+-------+-----+--------+
| 3-105 | 103 |     92 |
| 3-105 | 105 |     91 |
| 3-105 | 105 |     88 |
+-------+-----+--------+
3 rows in set (0.00 sec)

#查询所有教师和同学的name、sex和birthday.
mysql> select tname,tsex,tbirthday from teacher union select sname,ssex,sbirthday from student;
+--------+------+---------------------+
| tname  | tsex | tbirthday           |
+--------+------+---------------------+
| 李诚   | 男   | 1958-12-02          |
| 王萍   | 女   | 1972-05-05          |
| 刘冰   | 女   | 1977-08-14          |
| 张旭   | 男   | 1969-03-12          |
| 李军   | 男   | 1976-02-20 00:00:00 |
| 陆君   | 男   | 1974-06-03 00:00:00 |
| 匡明   | 男   | 1975-10-02 00:00:00 |
| 王丽   | 女   | 1976-01-23 00:00:00 |
| 曾华   | 男   | 1977-09-01 00:00:00 |
| 王芳   | 女   | 1975-02-10 00:00:00 |
+--------+------+---------------------+
10 rows in set (0.00 sec)

#查询所有“女”教师和“女”同学的name、sex和birthday.
mysql> select tname,tsex,tbirthday from teacher where tsex = '女' union select sname,ssex,sbirthday from student where ssex = '女';、
+--------+------+---------------------+
| tname  | tsex | tbirthday           |
+--------+------+---------------------+
| 王萍   | 女   | 1972-05-05          |
| 刘冰   | 女   | 1977-08-14          |
| 王丽   | 女   | 1976-01-23 00:00:00 |
| 王芳   | 女   | 1975-02-10 00:00:00 |
+--------+------+---------------------+
4 rows in set (0.00 sec)

#查询成绩比该课程平均成绩低的同学的成绩表。
mysql> select * from score a where degree < (select avg(degree) from score b where b.cno = a.cno);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 109 | 3-105 |     76 |
| 103 | 3-105 |     64 |
| 109 | 3-105 |     78 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
7 rows in set (0.00 sec)

#查询所有任课教师的Tname和Depart.
mysql> select tname,depart from teacher where tno in (select tno from course);
+--------+-----------------+
| tname  | depart          |
+--------+-----------------+
| 李诚   | 计算机系        |
| 王萍   | 计算机系        |
| 刘冰   | 电子工程系      |
| 张旭   | 电子工程系      |
+--------+-----------------+
4 rows in set (0.01 sec)

#查询所有未讲课的教师的Tname和Depart.
mysql> select tname,depart from teacher where tno not in (select tno from course where cno in (select cno from score));
+--------+-----------------+
| tname  | depart          |
+--------+-----------------+
| 刘冰   | 电子工程系      |
+--------+-----------------+
1 row in set (0.00 sec)

#查询至少有2名男生的班号。
mysql> select class from student where ssex = '男' group by class having count(*) > 1;
+-------+
| class |
+-------+
| 95031 |
| 95033 |
+-------+
2 rows in set (0.01 sec)

#查询Student表中不姓“王”的同学记录。
mysql> select * from student where sname not like '王%%';
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 108 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
+-----+--------+------+---------------------+-------+
4 rows in set (0.00 sec)

#查询Student表中每个学生的姓名和年龄。
mysql> select sname,year(now()) - year(sbirthday) from student;
+--------+-------------------------------+
| sname  | year(now()) - year(sbirthday) |
+--------+-------------------------------+
| 李军   |                            41 |
| 陆君   |                            43 |
| 匡明   |                            42 |
| 王丽   |                            41 |
| 曾华   |                            40 |
| 王芳   |                            42 |
+--------+-------------------------------+
6 rows in set (0.00 sec)

#查询Student表中最大和最小的Sbirthday日期值
mysql> select Max(sbirthday),Min(sbirthday) from student;
+---------------------+---------------------+
| Max(sbirthday)      | Min(sbirthday)      |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

#以班号和年龄从大到小的顺序查询Student表中的全部记录。
mysql> select * from student order by class desc,sbirthday;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 108 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
6 rows in set (0.00 sec)

#查询“男”教师及其所上的课程。
mysql> select tname,cname from course,teacher where course.tno = teacher.tno and teacher.tsex = '男';
+--------+--------------+
| tname  | cname        |
+--------+--------------+
| 李诚   | 操作系统     |
| 张旭   | 数字电路     |
+--------+--------------+
2 rows in set (0.02 sec)

#查询最高分同学的Sno、Cno和Degree列。
mysql> select sno,cno,degree from score order by degree desc limit 0,1;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
+-----+-------+--------+
1 row in set (0.01 sec)

#查询和“李军”同性别的所有同学的Sname
mysql> select sname from student where ssex = (select ssex from student where sname = '李军');
+--------+
| sname  |
+--------+
| 李军   |
| 陆君   |
| 匡明   |
| 曾华   |
+--------+
4 rows in set (0.00 sec)

#查询和“李军”同性别并同班的同学Sname.
mysql> select sname from student where ssex = (select ssex from student where sname = '李军') and class = (select class from student where sname = '李军');
+--------+
| sname  |
+--------+
| 李军   |
| 曾华   |
+--------+
2 rows in set (0.01 sec)

#查询所有选修“计算机导论”课程的“男”同学的成绩表。
mysql> select sno,cno,degree from score where cno = (select cno from course where cname = '计算机导论') and sno in (select sno from student where ssex = '男');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 103 | 3-105 |     64 |
| 105 | 3-105 |     88 |
| 105 | 3-105 |     91 |
+-----+-------+--------+
4 rows in set (0.02 sec)

reference:

• MySQL查询语句的45道练习题