mysql> -- 1. 先试着查绚选修了 3-105 课程的 109 号同学的分数 mysql> SELECT grade FROM student, score WHERE student.s_no=score.s_no AND c_no='3-105' AND score.s_no='109';
mysql> -- 2. 再查询成绩高于 109 号同学的所有同学记录 mysql> SELECT * FROM student, score WHERE student.s_no=score.s_no AND c_no='3-105' AND grade > (SELECT grade FROM student, score WHERE student.s_no=score.s_no AND c_no='3-105' AND score.s_no='109'); +------+--------+----------+---------------------+-------+------+-------+-------+ | s_no | s_name | s_gender | s_birthday | class | s_no | c_no | grade | +------+--------+----------+---------------------+-------+------+-------+-------+ | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 103 | 3-105 | 92 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 105 | 3-105 | 88 | +------+--------+----------+---------------------+-------+------+-------+-------+ 2 rows in set (0.00 sec)
mysql> SELECT s_no, score.c_no, grade FROM teacher, course, score WHERE teacher.t_no=course.t_no AND course.c_no=score.c_no AND t_name='张旭'; +------+-------+-------+ | s_no | c_no | grade | +------+-------+-------+ | 103 | 6-166 | 85 | | 105 | 6-166 | 79 | | 109 | 6-166 | 81 | +------+-------+-------+ 3 rows in set (0.00 sec)
mysql> -- 也可以使用多层嵌套子查询的方式 mysql> -- 1. 先找到 '张旭' 老师的教师号 mysql> SELECT t_no FROM teacher WHERE t_name='张旭';
mysql> -- 2. 再找到 '张旭' 老师所教授的课程号 mysql> SELECT c_no FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_name='张旭');
mysql> -- 3. 再从 score 表中找到对应课程号的成绩 mysql> SELECT s_no, c_no, grade FROM score WHERE c_no IN (SELECT c_no FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_name='张旭')); mysql> -- 结果同上
mysql> -- 2. 再通过课程表,利用课程编号查询出对应的教师编号 mysql> SELECT t_no FROM course WHERE c_no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 2); mysql> -- 3. 再通过教师表,利用教师编号查询出对应的教师名称 mysql> SELECT t_name FROM teacher WHERE t_no IN (SELECT t_no FROM course WHERE c_no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 2)); +--------+ | t_name | +--------+ | 李诚 | | 王萍 | | 张旭 | +--------+ 3 rows in set (0.00 sec)
mysql> -- 也可以使用多层嵌套子查询的方式 mysql> -- 1. 通过教师表,先找出 '计算机系' 老师的教师编号 mysql> SELECT t_no FROM teacher WHERE department='计算机系';
mysql> -- 2. 通过课程表,再找出对应老师所教授课程的课程编号 mysql> SELECT c_no FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE department='计算机系');
mysql> -- 2. 通过成绩表,找出对应课程的成绩 mysql> SELECT * FROM score WHERE c_no IN (SELECT c_no FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE department='计算机系')); mysql> -- 结果同上
26. 查询 ‘计算机系’ 与 ‘电子工程系’ 中不同职称的教师的所有记录 (职称相同的不输出)
换句话说,题目大意就是,找出两个系中职称互补的教师,职称相交的不要 电子工程系中已有的职称,计算机系中不能有人有,反过来计算机系中有的职称,电子工程系不能有人有 两个系的职称是互补的,互补的输出需要将查询结果进行联合,使用 UNION 关键字联合两个查询的结果
1 2 3 4 5 6 7 8 9 10
mysql> SELECT * FROM teacher WHERE department='计算机系' AND title NOT IN (SELECT title FROM teacher WHERE department='电子工程系') -> UNION -> SELECT * FROM teacher WHERE department='电子工程系' AND title NOT IN (SELECT title FROM teacher WHERE department='计算机系'); +------+--------+----------+---------------------+--------+------------+ | t_no | t_name | t_gender | t_birthday | title | department | +------+--------+----------+---------------------+--------+------------+ | 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 | | 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 | +------+--------+----------+---------------------+--------+------------+ 2 rows in set (0.01 sec)
mysql> SELECT t_name AS name, t_gender AS gender, t_birthday AS birthday FROM teacher WHERE t_gender='女' -> UNION -> SELECT s_name, s_gender, s_birthday FROM student WHERE s_gender='女'; +------+--------+---------------------+ | name | gender | birthday | +------+--------+---------------------+ | 王萍 | 女 | 1972-05-05 00:00:00 | | 刘冰 | 女 | 1977-08-14 00:00:00 | | 王丽 | 女 | 1976-01-23 00:00:00 | | 王芳 | 女 | 1975-02-10 00:00:00 | +------+--------+---------------------+ 4 rows in set (0.00 sec)
31. 查询成绩比该课程平均成绩低的同学的成绩表
1 2 3 4 5 6 7 8 9 10 11
mysql> SELECT * FROM score AS a WHERE grade<(SELECT AVG(grade) FROM score AS b WHERE a.c_no=b.c_no); +------+-------+-------+ | s_no | c_no | grade | +------+-------+-------+ | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | | 109 | 6-166 | 81 | +------+-------+-------+ 5 rows in set (0.00 sec)
32. 查询所有任课教师的 t_name 和 department
要注意是任课教师,所以需要结合课程表来查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14
mysql> SELECT t_name, department FROM teacher, course WHERE teacher.t_no=course.t_no; +--------+------------+ | t_name | department | +--------+------------+ | 李诚 | 计算机系 | | 王萍 | 计算机系 | | 刘冰 | 电子工程系 | | 张旭 | 电子工程系 | +--------+------------+ 4 rows in set (0.00 sec)
mysql> -- 也可以用子查询的方式 mysql> SELECT t_name, department FROM teacher WHERE t_no IN (SELECT t_no FROM course); mysql> -- 结果同上
33. 查出至少有2名男生的班号
1 2 3 4 5 6 7 8
mysql> SELECT class FROM student WHERE s_gender='男' GROUP BY class HAVING COUNT(*)>1; +-------+ | class | +-------+ | 95031 | | 95033 | +-------+ 2 rows in set (0.00 sec)
34. 查询 student 表中不姓 ‘王’ 的同学的记录
模糊查询可以使用 LIKE 关键字,如果要对模糊查询结果取反可以使用 NOT LIKE 也可以对复杂字符串匹配使用正则表达式,使用关键字 REGEXP
mysql> -- 或者使用子查询的方式 mysql> SELECT * FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_gender='男'); +-------+----------+------+ | c_no | c_name | t_no | +-------+----------+------+ | 3-245 | 操作系统 | 804 | | 6-166 | 数字电路 | 856 | +-------+----------+------+ 2 rows in set (0.00 sec)
39. 查询最高分同学的 s_no、c_no 和 grade
1 2 3 4 5 6 7
mysql> SELECT student.s_no, c_no, grade FROM student, score WHERE student.s_no=score.s_no AND grade=(SELECT MAX(grade) FROM score); +------+-------+-------+ | s_no | c_no | grade | +------+-------+-------+ | 103 | 3-105 | 92 | +------+-------+-------+ 1 row in set (0.00 sec)
40. 查询和 ‘李军’ 同性别的所有同学的 s_name
1 2 3 4 5 6 7 8 9 10 11 12 13
mysql> SELECT s_name FROM student WHERE s_gender=(SELECT s_gender FROM student WHERE s_name='李军'); +--------+ | s_name | +--------+ | 曾华 | | 匡明 | | 李军 | | 陆军 | | 王尼玛 | | 张全蛋 | | 赵铁柱 | +--------+ 7 rows in set (0.00 sec)
41. 查询和 ‘李军’ 同性别并且同班的所有同学的 s_name
1 2 3 4 5 6 7 8 9
mysql> SELECT s_name FROM student WHERE s_gender=(SELECT s_gender FROM student WHERE s_name='李军') AND class=(SELECT class FROM student WHERE s_name='李军'); +--------+ | s_name | +--------+ | 曾华 | | 李军 | | 王尼玛 | +--------+ 3 rows in set (0.00 sec)
42. 查询所有选修 ‘计算机导论’ 课程的 ‘男’ 同学的成绩表
1 2 3 4 5 6 7
mysql> 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_gender='男'); +------+-------+-------+ | s_no | c_no | grade | +------+-------+-------+ | 109 | 3-105 | 76 | +------+-------+-------+ 1 row in set (0.00 sec)
43. 新建一张 rank 表,查询所有学生的 s_no、c_no和 level 列
新建一个 rank 表代表学生的成绩等级,并插入数据:
1 2 3 4 5 6 7 8 9 10 11
CREATETABLErank ( lowINT(3), upp INT(3), levelchar(1) );