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

今日目标：Mysql学习40

实验内容：

-- 41.查询和"李军"同性别并且同班的所有同学的s_name

SELECT s_name, s_sex FROM student WHERE s_sex = (SELECT s_sex FROM student WHERE s_name = '李军') AND s_class = (SELECT s_class FROM student WHERE s_name = '李军');

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

| s_name | s_sex |

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

| 曾华   | 男    |

| 李军   | 男    |

| 王尼玛 | 男    |

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

 

SELECT s_name, s_sex FROM student s1 WHERE s_sex = (SELECT s_sex FROM student s2 WHERE s_name = '李军' AND s1.s_class = s2.s_class);

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

| s_name | s_sex |

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

| 曾华   | 男    |

| 李军   | 男    |

| 王尼玛 | 男    |

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

 

 

-- 42. 查询所有选修'计算机导论'课程的'男'同学的成绩表

SELECT * FROM score WHERE c_no = (SELECT c_no FROM course WHERE c_name = '计算机导论' ) AND s_no IN(SELECT s_no FROM student WHERE s_sex = '男');

 

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

| s_no | c_no  | sc_degree |

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

| 101  | 3-105 |        90 |

| 102  | 3-105 |        91 |

| 104  | 3-105 |        89 |

| 109  | 3-105 |        76 |

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

 

进阶:显示出s_name,c_name 替代之前的 s_no and c_no

SELECT s_name,c_name,sc_degree FROM score,student,course WHERE score.c_no = (SELECT c_no FROM course WHERE c_name = '计算机导论' ) AND score.s_no IN(SELECT s_no FROM student WHERE s_sex = '男') AND score.s_no = student.s_no AND score.c_no = course.c_no ;

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

| s_name | c_name     | sc_degree |

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

| 曾华   | 计算机导论 |        90 |

| 匡明   | 计算机导论 |        91 |

| 李军   | 计算机导论 |        89 |

| 赵铁柱 | 计算机导论 |        76 |

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

 

 

-- 43. 假设使用了以下命令建立了一个grade表

CREATE TABLE grade(

    low INT(3),

    upp INT(3),

    grade CHAR(1)

);

INSERT INTO grade VALUES(90,100,'A');

INSERT INTO grade VALUES(80,89,'B');

INSERT INTO grade VALUES(70,79,'c');

INSERT INTO grade VALUES(60,69,'D');

INSERT INTO grade VALUES(0,59,'E');

-- 查询所有同学的s_no , c_no 和grade列

SELECT s_no, c_no , grade FROM score, grade WHERE sc_degree BETWEEN low and upp; 

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

| s_no | c_no  | grade |

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

| 101  | 3-105 | A     |

| 102  | 3-105 | A     |

| 103  | 3-105 | A     |

| 103  | 3-245 | B     |

| 103  | 6-166 | B     |

| 104  | 3-105 | B     |

| 105  | 3-105 | B     |

| 105  | 3-245 | c     |

| 105  | 6-166 | c     |

| 109  | 3-105 | c     |

| 109  | 3-245 | D     |

| 109  | 6-166 | B     |

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

 

进阶:显示学生名字和课程名称

SELECT  s.s_no, s.s_name, c.c_name ,c.c_no , grade FROM student s, course c ,score sc, grade WHERE sc_degree BETWEEN low and upp AND sc.s_no = s.s_no AND sc.c_no = c.c_no;

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

| s_no | s_name | c_name     | c_no  | grade |

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

| 101  | 曾华   | 计算机导论 | 3-105 | A     |

| 102  | 匡明   | 计算机导论 | 3-105 | A     |

| 103  | 王丽   | 计算机导论 | 3-105 | A     |

| 103  | 王丽   | 操作系统   | 3-245 | B     |

| 103  | 王丽   | 数字电路   | 6-166 | B     |

| 104  | 李军   | 计算机导论 | 3-105 | B     |

| 105  | 王芳   | 计算机导论 | 3-105 | B     |

| 105  | 王芳   | 操作系统   | 3-245 | c     |

| 105  | 王芳   | 数字电路   | 6-166 | c     |

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

| 109  | 赵铁柱 | 操作系统   | 3-245 | D     |

| 109  | 赵铁柱 | 数字电路   | 6-166 | B     |

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