2020/11/20 刘一辰的JAVA随笔

今日目标：Mysql学习24

实验内容：

-- 19.查询成绩高于学号为'109',课程号为'3-105'的成绩的所有记录

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 |

+------+-------+-----------+

 

不过视频中仅仅查出来了score记录,但是并没有学生的信息,按照上面的来修改:

SELECT * FROM student AS s, score AS sc WHERE sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105') AND s.s_no = sc.s_no;

 

还可以再进一步:

SELECT s.s_no AS'学生学号', s.s_name AS'学生姓名', s_sex AS'性别', s_class AS'班级', c.c_no AS'课程编号', c.c_name AS'课程名称' ,sc.sc_degree AS'分数' FROM student AS s, score AS sc ,course AS c WHERE sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105') AND s.s_no = sc.s_no AND sc.c_no = c.c_no;

+----------+----------+------+-------+----------+------------+------+

| 学生学号 | 学生姓名 | 性别 | 班级  | 课程编号 | 课程名称   | 分数 |

+----------+----------+------+-------+----------+------------+------+

| 103      | 王丽     | 女   | 95033 | 3-105    | 计算机导论 |   92 |

| 103      | 王丽     | 女   | 95033 | 3-245    | 操作系统   |   86 |

| 103      | 王丽     | 女   | 95033 | 6-166    | 数字电路   |   85 |

| 105      | 王芳     | 女   | 95031 | 3-105    | 计算机导论 |   88 |

| 105      | 王芳     | 女   | 95031 | 6-166    | 数字电路   |   79 |

| 109      | 赵铁柱   | 男   | 95031 | 6-166    | 数字电路   |   81 |

+----------+----------+------+-------+----------+------------+------+