2020/12/02 刘一辰的JAVA随笔

今日目标：Mysql学习34

实验内容：

-- 30.查询所有'女'教师和'女'学生的name,sex,birthday

SELECT s_name AS name, s_sex AS sex, s_birthday AS birthday  FROM student WHERE s_sex = '女'

UNION

SELECT t_name AS name, t_sex AS sex, t_birthday AS birthday FROM teacher WHERE t_sex = '女';

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

| name | sex | birthday            |

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

| 王丽 | 女  | 1976-01-23 00:00:00 |

| 王芳 | 女  | 1975-02-10 00:00:00 |

| 王萍 | 女  | 1972-05-05 00:00:00 |

| 刘冰 | 女  | 1977-08-14 00:00:00 |

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

 

 

-- 31.查询成绩比该课程平均成绩低的同学的成绩表

注意:我的数据和视频中的数据有点不一样,所以查询结果有点区别

视频中score:

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

| s_no | c_no  | sc_degree |

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

| 101  | 3-105 |        90 |

| 102  | 3-105 |        91 |

| 103  | 3-105 |        92 |

| 103  | 3-245 |        86 |

| 103  | 6-166 |        85 |

| 104  | 3-105 |        89 |

| 105  | 3-105 |        88 |

| 105  | 3-245 |        75 |

| 105  | 6-166 |        79 |

| 109  | 3-105 |        76 |

| 109  | 3-245 |        68 |

| 109  | 6-166 |        68 |    我数据库中:    109  | 6-166 |        81

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

 

SELECT * FROM score AS sc1 WHERE sc1.sc_degree < (SELECT  AVG(sc_degree) FROM score AS sc2 WHERE sc1.c_no = sc2.c_no);

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

| s_no | c_no  | sc_degree |

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

| 105  | 3-245 |        75 |

| 105  | 6-166 |        79 |

| 109  | 3-105 |        76 |

| 109  | 3-245 |        68 |

| 109  | 6-166 |        81 |

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

 

进阶,显示出学生name,课程name以及分数

SELECT s.s_name ,sc1.c_no,c.c_name, sc1.sc_degree FROM score AS sc1,student AS s,course AS c WHERE sc1.sc_degree < (SELECT  AVG(sc_degree) FROM score AS sc2 WHERE sc1.c_no = sc2.c_no) AND sc1.s_no = s.s_no AND sc1.c_no = c.c_no ;

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

| s_name | c_no  | c_name     | sc_degree |

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

| 赵铁柱 | 3-105 | 计算机导论 |        76 |

| 王芳   | 3-245 | 操作系统   |        75 |

| 赵铁柱 | 3-245 | 操作系统   |        68 |

| 王芳   | 6-166 | 数字电路   |        79 |

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

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