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

今日目标：Mysql学习37

实验内容：

-- 37.以班级号和年龄从大到小的顺序查询student表中的全部记录

SELECt * FROM student ORDER BY s_class DESC, s_birthday; 

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

| s_no | s_name | s_sex | s_birthday          | s_class |

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

| 110  | 张飞   | 男    | 1974-06-03 00:00:00 | 95038   |

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

| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |

| 107  | 王尼玛 | 男    | 1976-02-20 00:00:00 | 95033   |

| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |

| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |

| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |

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

| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |

| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |

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

 

 

-- 38.查询"男"教师 及其所上的课

SELECT * FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_sex = '男');

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

| c_no  | c_name   | t_no |

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

| 3-245 | 操作系统 | 804  |

| 6-166 | 数字电路 | 856  |

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

 

进阶:查出教师名称,教师性别, 课程名字

SELECT t.t_name,t.t_sex ,c.c_name FROM teacher t ,course c WHERE t_sex = '男' AND t.t_no = c.t_no;

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

| t_name | t_sex | c_name   |

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

| 李诚   | 男    | 操作系统 |

| 张旭   | 男    | 数字电路 |

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

-- 37.以班级号和年龄从大到小的顺序查询student表中的全部记录

SELECt * FROM student ORDER BY s_class DESC, s_birthday; 

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

| s_no | s_name | s_sex | s_birthday          | s_class |

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

| 110  | 张飞   | 男    | 1974-06-03 00:00:00 | 95038   |

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

| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |

| 107  | 王尼玛 | 男    | 1976-02-20 00:00:00 | 95033   |

| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |

| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |

| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |

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

| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |

| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |

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

 

 

-- 38.查询"男"教师 及其所上的课

SELECT * FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_sex = '男');

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

| c_no  | c_name   | t_no |

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

| 3-245 | 操作系统 | 804  |

| 6-166 | 数字电路 | 856  |

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

 

进阶:查出教师名称,教师性别, 课程名字

SELECT t.t_name,t.t_sex ,c.c_name FROM teacher t ,course c WHERE t_sex = '男' AND t.t_no = c.t_no;

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

| t_name | t_sex | c_name   |

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

| 李诚   | 男    | 操作系统 |

| 张旭   | 男    | 数字电路 |

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