接着上一篇：
《编程一五八俱乐部SQL学习笔记-4-查询练习1-10》
11、查询每门课的平均成绩
avg函数算平均值，group by表示分组
mysql> select c_no,avg(sc_degree) from score group by c_no; //先按照c_no进行分组，再进行计算平均值
+-------+----------------+
| c_no | avg(sc_degree) |
+-------+----------------+
| 3-105 | 85.3333 | //各个班的各自的平均成绩
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+----------------+
某一班的平均成绩select c_no,avg(sc_degree) from score where c_no='3-105';
12、查询score表中至少有2名学生选修的，并且以3开头的课程的平均分
groupby后面加条件，不用where用having
//select c_no from score group by cno //到这里输出只有三行，只是分好组
mysql> select c_no,avg(sc_degree),count(*) from score
-> group by c_no //先是分组
-> having count(c_no)>=2 and c_no like '3%'; //分组后面带条件，不用where而是having
+-------+----------------+----------+
| c_no | avg(sc_degree) | count(*) |
+-------+----------------+----------+
| 3-105 | 85.3333 | 3 |
| 3-245 | 76.3333 | 3 |
+-------+----------------+----------+
13、查询分数大于70小于90的s_no列
mysql> select s_no,sc_degree from score
-> where sc_degree between 70 and 90; //其实这么写70和90包含在里面
//mysql> select s_no,sc_degree from score
//-> where sc_degree>70 and sc_degree<90; //这么写也可以
+------+-----------+
| s_no | sc_degree |
+------+-----------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 109 | 76 |
| 109 | 81 |
+------+-----------+
//显示对应的人
mysql> select s_name,c_name,sc_degree from score,student,course
-> where score.s_no = student.s_no AND score.c_no = course.c_no //关联表的连接，多表联合查询
-> AND sc_degree between 71 and 89;
+-----------+-----------------+-----------+
| s_name | c_name | sc_degree |
+-----------+-----------------+-----------+
| 王丽 | 操作系统 | 86 |
| 王丽 | 数字电路 | 85 |
| 王芳 | 计算机导论 | 88 |
| 王芳 | 操作系统 | 75 |
| 王芳 | 数字电路 | 79 |
| 赵铁柱 | 计算机导论 | 76 |
| 赵铁柱 | 数字电路 | 81 |
+-----------+-----------------+-----------+
14、查询所有学生的s_name,c_no,sc_degree列
多表查询
mysql> select s_name,c_no,sc_degree from student,score
-> where student.s_no = score.s_no;
+-----------+-------+-----------+
| s_name | c_no | sc_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 |
+-----------+-------+-----------+
15、查询所有学生的s_no，c_name，sc_degree列
多表查询
mysql> select s_no,c_name,sc_degree from course,score //多表关联，c_name字段来自于course表
-> where course.c_no = score.c_no;
+------+-----------------+-----------+
| s_no | c_name | sc_degree |
+------+-----------------+-----------+
| 103 | 计算机导论 | 92 |
| 105 | 计算机导论 | 88 |
| 109 | 计算机导论 | 76 |
| 103 | 操作系统 | 86 |
| 105 | 操作系统 | 75 |
| 109 | 操作系统 | 68 |
| 103 | 数字电路 | 85 |
| 105 | 数字电路 | 79 |
| 109 | 数字电路 | 81 |
+------+-----------------+-----------+
16、查询所有学生s_name，c_name，sc_degree列
三表关联查询
mysql> SELECT s_name, c_name, sc_degree FROM student, course, score
-> WHERE student.s_no = score.s_no AND score.c_no = course.c_no;
+-----------+-----------------+-----------+
| s_name | c_name | sc_degree |
+-----------+-----------------+-----------+
| 王丽 | 计算机导论 | 92 |
| 王丽 | 操作系统 | 86 |
| 王丽 | 数字电路 | 85 |
| 王芳 | 计算机导论 | 88 |
| 王芳 | 操作系统 | 75 |
| 王芳 | 数字电路 | 79 |
| 赵铁柱 | 计算机导论 | 76 |
| 赵铁柱 | 操作系统 | 68 |
| 赵铁柱 | 数字电路 | 81 |
+-----------+-----------------+-----------+
17、查询95031班学生每门课的平均分
子查询 + 分组求平均分
mysql> select c_no,avg(sc_degree)
-> from score
-> where s_no in (select s_no from student where s_class = '95031')
-> group by c_no;
+-------+----------------+
| c_no | avg(sc_degree) |
+-------+----------------+
| 3-105 | 82.0000 |
| 3-245 | 71.5000 |
| 6-166 | 80.0000 |
+-------+----------------+
//将95031班的学生选出来，按照每个学生的课程分组，展示平均值
18、查询选修3-105课程的成绩高于109号学生3-105成绩 的所有同学记录
即查询所有选修3-105的学生中，分数比学号为109号学生还要高的学生信息
mysql> select * from student as s,score as sc //新列名称as
-> where sc.c_no='3-105'
-> and sc.sc_degree > (select sc_degree from score where s_no = '109' and c_no = '3-105')
-> and s.s_no = sc.s_no; //连接学生信息表和成绩表
+------+--------+-------+---------------------+---------+------+-------+-----------+
| s_no | s_name | s_sex | s_birthday | s_class | s_no | c_no | sc_degree |
+------+--------+-------+---------------------+---------+------+-------+-----------+
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 103 | 3-105 | 92 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 105 | 3-105 | 88 |
+------+--------+-------+---------------------+---------+------+-------+-----------+
19、查询成绩高于学号为109，课程号为3-105的成绩的所有记录
这里先找到这个109号的3-105同学的成绩，再检索出比之大的即可。
mysql> select * from score
-> where sc_degree > (select sc_degree from score where s_no='109' and c_no='3-105');
+------+-------+-----------+
| s_no | c_no | sc_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 |
+------+-------+-----------+
20、查询学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday
year()函数提取年份
in和or功能类似，但是in可以包含其他select语句
mysql> select * from student
-> where year(s_birthday) in (select year(s_birthday) from student where s_no in('108','101'));
+------+-----------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday | s_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 |
不积跬步无以至千里，不积小流无以成江海。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
