mysql查询练习2篇--分组模糊查询、多表查询、子查询

1. 分组查询、模糊查询、范围查询
11.查询每门课的平均成绩
-avg()

mysql> select * from course;
显示该门课所有成绩
mysql> select degm score where cno='3-105';
平均成绩
mysql> select avg(degree) from score where cno='3-105';

分开显示所有课程平均成绩
mysql> select avg(degree) from score where cno='3-105';
mysql> select avg(degree) from score where cno='3-245';
mysql> select avg(degree) from score where cno='6-166';
mysql> select avg(degree) from score where cno='9-888';

显示所有课程平均成绩
mysql> select avg(degree) from score group by cno;
显示所有课程及对应平均成绩
mysql> select cno,avg(degree) from score group by cno;

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

mysql> select cno from score group by cno having count(cno)>=2 and cno like '3%';
+-------+
| cno   |
+-------+
| 3-105 |
| 3-245 |
+-------+
mysql> select cno,avg(degree) from score group by cno having count(cno)>=2 and cno like '3%';
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
+-------+-------------+
2 rows in set (0.00 sec)
mysql> select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like '3%';
+-------+-------------+----------+
| cno   | avg(degree) | count(*) |
+-------+-------------+----------+
| 3-105 |     85.3333 |        3 |
| 3-245 |     76.3333 |        3 |
+-------+-------------+----------+

13.查询分数大于70，小于90的sno列。（两种方式）

mysql> select sno,degree from score where degree>70 and degree<90;;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 |     86 |
| 103 |     85 |
| 105 |     88 |
| 105 |     75 |
| 105 |     79 |
| 109 |     76 |
| 109 |     81 |
+-----+--------+
mysql> select sno,degree from score where degree between 70 and 90;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 |     86 |
| 103 |     85 |
| 105 |     88 |
| 105 |     75 |
| 105 |     79 |
| 109 |     76 |
| 109 |     81 |
+-----+--------+

2.多表查询
通过共同的字段联系起来
14.查询所有学生的sname、cno和degree列。

mysql> select sno,cno,degree from score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+

mysql> select sno,sname from student;
+-----+-----------+
| sno | sname     |
+-----+-----------+
| 101 | 曾华      |
| 102 | 匡明      |
| 103 | 王丽      |
| 104 | 李军      |
| 105 | 王芳      |
| 106 | 陆军      |
| 107 | 王二      |
| 108 | 张全蛋    |
| 109 | 赵铁柱    |
+-----+-----------+
他们的sno一样，通过sno获取对应值。
mysql> select cno,sname,degree from student,score where student.sno=score.sno;
+-------+-----------+--------+
| cno   | sname     | degree |
+-------+-----------+--------+
| 3-105 | 王丽      |     92 |
| 3-245 | 王丽      |     86 |
| 6-166 | 王丽      |     85 |
| 3-105 | 王芳      |     88 |
| 3-245 | 王芳      |     75 |
| 6-166 | 王芳      |     79 |
| 3-105 | 赵铁柱    |     76 |
| 3-245 | 赵铁柱    |     68 |
| 6-166 | 赵铁柱    |     81 |
+-------+-----------+--------+
9 rows in set (0.00 sec)

15.查询所有学生的sno、cname、degree列。

mysql> select cno,cname from course;
+-------+-----------------+
| cno   | cname           |
+-------+-----------------+
| 3-105 | 计算机导论      |
| 3-245 | 操作系统        |
| 6-166 | 数字电路        |
| 9-888 | 高等数学        |
+-------+-----------------+

mysql> select cno,sno,degree from score;
+-------+-----+--------+
| cno   | sno | degree |
+-------+-----+--------+
| 3-105 | 103 |     92 |
| 3-245 | 103 |     86 |
| 6-166 | 103 |     85 |
| 3-105 | 105 |     88 |
| 3-245 | 105 |     75 |
| 6-166 | 105 |     79 |
| 3-105 | 109 |     76 |
| 3-245 | 109 |     68 |
| 6-166 | 109 |     81 |
+-------+-----+--------+
以上两个cno相同，联系起来。
mysql> select sno,cname,degree from course,score where course.cno=score.cno;
+-----+-----------------+--------+
| sno | cname           | degree |
+-----+-----------------+--------+
| 103 | 计算机导论      |     92 |
| 103 | 操作系统        |     86 |
| 103 | 数字电路        |     85 |
| 105 | 计算机导论      |     88 |
| 105 | 操作系统        |     75 |
| 105 | 数字电路        |     79 |
| 109 | 计算机导论      |     76 |
| 109 | 操作系统        |     68 |
| 109 | 数字电路        |     81 |
+-----+-----------------+--------+

16.查询所有学生的sname、cname、degree列。
sname–>student
cname–>course
degree–>score

mysql> select sname,cname,degree from course,score,student where course.cno=score.cno and student.sno=score.sno;
+-----------+-----------------+--------+
| sname     | cname           | degree |
+-----------+-----------------+--------+
| 王丽      | 计算机导论      |     92 |
| 王丽      | 操作系统        |     86 |
| 王丽      | 数字电路        |     85 |
| 王芳      | 计算机导论      |     88 |
| 王芳      | 操作系统        |     75 |
| 王芳      | 数字电路        |     79 |
| 赵铁柱    | 计算机导论      |     76 |
| 赵铁柱    | 操作系统        |     68 |
| 赵铁柱    | 数字电路        |     81 |
+-----------+-----------------+--------+
mysql> select sname,cname,degree,student.sno,course.cno from course,score,student where course.cno=score.cno and student.sno=score.sno;
+-----------+-----------------+--------+-----+-------+
| sname     | cname           | degree | sno | cno   |
+-----------+-----------------+--------+-----+-------+
| 王丽      | 计算机导论      |     92 | 103 | 3-105 |
| 王丽      | 操作系统        |     86 | 103 | 3-245 |
| 王丽      | 数字电路        |     85 | 103 | 6-166 |
| 王芳      | 计算机导论      |     88 | 105 | 3-105 |
| 王芳      | 操作系统        |     75 | 105 | 3-245 |
| 王芳      | 数字电路        |     79 | 105 | 6-166 |
| 赵铁柱    | 计算机导论      |     76 | 109 | 3-105 |
| 赵铁柱    | 操作系统        |     68 | 109 | 3-245 |
| 赵铁柱    | 数字电路        |     81 | 109 | 6-166 |
+-----------+-----------------+--------+-----+-------+

mysql> select sname,cname,degree,student.sno as stu_sno,course.cno as cou_cno from course,score,student where course.cno=score.cno and student.sno=score.sno;
+-----------+-----------------+--------+---------+---------+
| sname     | cname           | degree | stu_sno | cou_cno |
+-----------+-----------------+--------+---------+---------+
| 王丽      | 计算机导论      |     92 | 103     | 3-105   |
| 王丽      | 操作系统        |     86 | 103     | 3-245   |
| 王丽      | 数字电路        |     85 | 103     | 6-166   |
| 王芳      | 计算机导论      |     88 | 105     | 3-105   |
| 王芳      | 操作系统        |     75 | 105     | 3-245   |
| 王芳      | 数字电路        |     79 | 105     | 6-166   |
| 赵铁柱    | 计算机导论      |     76 | 109     | 3-105   |
| 赵铁柱    | 操作系统        |     68 | 109     | 3-245   |
| 赵铁柱    | 数字电路        |     81 | 109     | 6-166   |
+-----------+-----------------+--------+---------+---------+

3.子查询
17.查询’95031’班学生每门课的平均分。

（1）获取这个班学生的学号
mysql> select * from student where class='95031';
（2）将sno选出来
mysql> select sno from student where class='95031'; 
（3）将sno等于这些的人的成绩拿出来
mysql> select * from score where sno in (select sno from student where class='95031');
（4）算平均成绩，获取课程号及对应平均成绩，按cno分组（ group by cno）
mysql> select cno,avg(degree) from score where sno in (select sno from student where class='95031') group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |
+-------+-------------+

18.查询选修‘3-105’课程的成绩高于‘109’号同学‘3-105’成绩的所有同学的记录。

（1）查到这位同学这门课成绩
mysql> select degree from score where sno='109' and cno='3-105';
（2）查出大于这个分数的
mysql> select * from score where cno='3-105'and  degree > (select degree 
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
+-----+-------+--------+
2 rows in set (0.00 sec)

19.查询成绩高于学号为‘109’、课程号为‘3-105’的成绩的所有记录。

mysql> select degree from score where sno='109' and cno='3-105';
+--------+
| degree |
+--------+
|     76 |
+--------+
1 row in set (0.00 sec)

mysql> select * from score where degree > (select degree from score where sno='109' and cno='3-105');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+

year函数和带in关键字的子查询
20.查询和学号为101、108的同学同年出生的所有学生的sno、sname
和sbirthday列。

（1)找出学号是101和108的信息
mysql> select * from student where sno in(101,108);
（2）取出他们对应的出生日期
mysql> select year(sbirthday )from student where sno in(101,108);
（3）挑选出生日期等于其中任意一个数值的学生
mysql> select * from student where year(sbirthday) in (select year(sbirthday )from student where sno in(101,108));
+-----+-----------+------+---------------------+-------+
| sno | sname     | ssex | sbirthday           | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华      | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明      | 男   | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳      | 女   | 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋    | 男   | 1975-02-10 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+

多层嵌套子查询
21.查询‘张旭’老师任课的学生的成绩

（1）找到张旭
mysql> select * from teacher where tname='张旭';
（2）找到张旭的工号
mysql> select tno from teacher where tname='张旭';
（3）长到张旭工号对应的课程
mysql> select * from course where tno=(select tno from teacher where tname='张旭');
（4）课程对应的课程号
mysql> select cno from course where tno=(select tno from teacher where tname='张旭');
（5）该课程号对应的学生的成绩
mysql> select * from score where cno=(select cno from course where tno=(select tno from teacher where tname='张旭'));
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 6-166 |     85 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
3 rows in set (0.00 sec)