重新学习 MySQL 的基础知识,查询是数据库中常用的操作,本章主要是通过练习来实践查询操作。
准备数据 建数据表
创建数据库
1 CREATE DATABASE select_test;
切换数据库
创建学生表
1 2 3 4 5 6 7 CREATE TABLE student ( s_no VARCHAR (20 ) PRIMARY KEY COMMENT '学生学号' , s_name VARCHAR (20 ) NOT NULL COMMENT '学生姓名' , s_gender VARCHAR (10 ) NOT NULL COMMENT '学生性别' , s_birthday DATETIME COMMENT '学生生日' , class VARCHAR (20 ) COMMENT '学生班级' );
创建教师表
1 2 3 4 5 6 7 8 CREATE TABLE teacher ( t_no VARCHAR (20 ) PRIMARY KEY COMMENT '教师编号' , t_name VARCHAR (20 ) NOT NULL COMMENT '教师名称' , t_gender VARCHAR (10 ) NOT NULL COMMENT '教师性别' , t_birthday DATETIME COMMENT '教师生日' , title VARCHAR (20 ) NOT NULL COMMENT '教师职称' , department VARCHAR (20 ) NOT NULL COMMENT '教师部门' );
创建课程表
1 2 3 4 5 6 CREATE TABLE course ( c_no VARCHAR (20 ) PRIMARY KEY COMMENT '课程编号' , c_name VARCHAR (20 ) NOT NULL COMMENT '课程名字' , t_no VARCHAR (20 ) NOT NULL COMMENT '教师编号' , FOREIGN KEY(t_no) REFERENCES teacher(t_no) );
创建成绩表
1 2 3 4 5 6 7 8 CREATE TABLE score ( s_no VARCHAR (20 ) NOT NULL COMMENT '学生编号' , c_no VARCHAR (20 ) NOT NULL COMMENT '课程编号' , grade DECIMAL COMMENT '分数' , FOREIGN KEY(s_no) REFERENCES student(s_no), FOREIGN KEY(c_no) REFERENCES course(c_no), PRIMARY KEY(s_no, c_no) );
添加数据
添加学生表数据
1 2 3 4 5 6 7 8 9 INSERT INTO student VALUES ('101' , '曾华' , '男' , '1977-09-01' , '95033' );INSERT INTO student VALUES ('102' , '匡明' , '男' , '1975-10-02' , '95031' );INSERT INTO student VALUES ('103' , '王丽' , '女' , '1976-01-23' , '95033' );INSERT INTO student VALUES ('104' , '李军' , '男' , '1976-02-20' , '95033' );INSERT INTO student VALUES ('105' , '王芳' , '女' , '1975-02-10' , '95031' );INSERT INTO student VALUES ('106' , '陆军' , '男' , '1974-06-03' , '95031' );INSERT INTO student VALUES ('107' , '王尼玛' , '男' , '1976-02-20' , '95033' );INSERT INTO student VALUES ('108' , '张全蛋' , '男' , '1975-02-10' , '95031' );INSERT INTO student VALUES ('109' , '赵铁柱' , '男' , '1974-06-03' , '95031' );
添加教师表数据
1 2 3 4 INSERT INTO teacher VALUES ('804' , '李诚' , '男' , '1958-12-02' , '副教授' , '计算机系' );INSERT INTO teacher VALUES ('856' , '张旭' , '男' , '1969-03-12' , '讲师' , '电子工程系' );INSERT INTO teacher VALUES ('825' , '王萍' , '女' , '1972-05-05' , '助教' , '计算机系' );INSERT INTO teacher VALUES ('831' , '刘冰' , '女' , '1977-08-14' , '助教' , '电子工程系' );
添加课程表数据
1 2 3 4 INSERT INTO course VALUES ('3-105' , '计算机导论' , '825' );INSERT INTO course VALUES ('3-245' , '操作系统' , '804' );INSERT INTO course VALUES ('6-166' , '数字电路' , '856' );INSERT INTO course VALUES ('9-888' , '高等数学' , '831' );
添加成绩表数据
1 2 3 4 5 6 7 8 9 INSERT INTO score VALUES ('103' , '3-105' , '92' );INSERT INTO score VALUES ('103' , '3-245' , '86' );INSERT INTO score VALUES ('103' , '6-166' , '85' );INSERT INTO score VALUES ('105' , '3-105' , '88' );INSERT INTO score VALUES ('105' , '3-245' , '75' );INSERT INTO score VALUES ('105' , '6-166' , '79' );INSERT INTO score VALUES ('109' , '3-105' , '76' );INSERT INTO score VALUES ('109' , '3-245' , '68' );INSERT INTO score VALUES ('109' , '6-166' , '81' );
基本查询练习 1. 查询 student 表的所有记录 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SELECT * FROM student; + | s_no | s_name | s_gender | s_birthday | class | + | 101 | 曾华 | 男 | 1977 -09 -01 00 :00 :00 | 95033 | | 102 | 匡明 | 男 | 1975 -10 -02 00 :00 :00 | 95031 | | 103 | 王丽 | 女 | 1976 -01 -23 00 :00 :00 | 95033 | | 104 | 李军 | 男 | 1976 -02 -20 00 :00 :00 | 95033 | | 105 | 王芳 | 女 | 1975 -02 -10 00 :00 :00 | 95031 | | 106 | 陆军 | 男 | 1974 -06 -03 00 :00 :00 | 95031 | | 107 | 王尼玛 | 男 | 1976 -02 -20 00 :00 :00 | 95033 | | 108 | 张全蛋 | 男 | 1975 -02 -10 00 :00 :00 | 95031 | | 109 | 赵铁柱 | 男 | 1974 -06 -03 00 :00 :00 | 95031 | + 9 rows in set (0.00 sec)
2. 查询 student 表中的 s_name、s_gender 和 class 字段的所有记录 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SELECT s_name, s_gender, class FROM student; + | s_name | s_gender | class | + | 曾华 | 男 | 95033 | | 匡明 | 男 | 95031 | | 王丽 | 女 | 95033 | | 李军 | 男 | 95033 | | 王芳 | 女 | 95031 | | 陆军 | 男 | 95031 | | 王尼玛 | 男 | 95033 | | 张全蛋 | 男 | 95031 | | 赵铁柱 | 男 | 95031 | + 9 rows in set (0.00 sec)
3. 查询 teacher 表中不重复的 department 列 1 2 3 4 5 6 7 8 mysql> SELECT DISTINCT (department) FROM teacher; + | department | + | 计算机系 | | 电子工程系 | + 2 rows in set (0.00 sec)
4. 查询 score 表中成绩在60-80之间的所有记录(区间查询和运算符查询)
区间查询:BETWEEN a AND b
, 查询区间为 [a, b]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> mysql> SELECT * FROM score WHERE grade>= 60 AND grade<= 80 ; + | s_no | c_no | grade | + | 105 | 3 -245 | 75 | | 105 | 6 -166 | 79 | | 109 | 3 -105 | 76 | | 109 | 3 -245 | 68 | + 4 rows in set (0.00 sec)mysql> mysql> SELECT * FROM score WHERE grade BETWEEN 60 AND 80 ; mysql>
5. 查询 score 表中成绩为 85, 86 或 88 的记录 1 2 3 4 5 6 7 8 9 mysql> SELECT * FROM score WHERE grade IN (85 , 86 , 88 ); + | s_no | c_no | grade | + | 103 | 3 -245 | 86 | | 103 | 6 -166 | 85 | | 105 | 3 -105 | 88 | + 3 rows in set (0.00 sec)
6. 查询 student 表中 ‘95031’ 班或性别为 ‘女’ 的所有记录 1 2 3 4 5 6 7 8 9 10 11 12 mysql> SELECT * FROM student WHERE class= '95031' OR s_gender= '女' ; + | s_no | s_name | s_gender | s_birthday | class | + | 102 | 匡明 | 男 | 1975 -10 -02 00 :00 :00 | 95031 | | 103 | 王丽 | 女 | 1976 -01 -23 00 :00 :00 | 95033 | | 105 | 王芳 | 女 | 1975 -02 -10 00 :00 :00 | 95031 | | 106 | 陆军 | 男 | 1974 -06 -03 00 :00 :00 | 95031 | | 108 | 张全蛋 | 男 | 1975 -02 -10 00 :00 :00 | 95031 | | 109 | 赵铁柱 | 男 | 1974 -06 -03 00 :00 :00 | 95031 | + 6 rows in set (0.00 sec)
7. 以 class 降序的方式查询 student 表的所有记录
ORDER By
对字段进行排序:默认升序(ASC
),降序为 DESC
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SELECT * FROM student ORDER BY class DESC ; + | s_no | s_name | s_gender | s_birthday | class | + | 101 | 曾华 | 男 | 1977 -09 -01 00 :00 :00 | 95033 | | 103 | 王丽 | 女 | 1976 -01 -23 00 :00 :00 | 95033 | | 104 | 李军 | 男 | 1976 -02 -20 00 :00 :00 | 95033 | | 107 | 王尼玛 | 男 | 1976 -02 -20 00 :00 :00 | 95033 | | 102 | 匡明 | 男 | 1975 -10 -02 00 :00 :00 | 95031 | | 105 | 王芳 | 女 | 1975 -02 -10 00 :00 :00 | 95031 | | 106 | 陆军 | 男 | 1974 -06 -03 00 :00 :00 | 95031 | | 108 | 张全蛋 | 男 | 1975 -02 -10 00 :00 :00 | 95031 | | 109 | 赵铁柱 | 男 | 1974 -06 -03 00 :00 :00 | 95031 | + 9 rows in set (0.00 sec)
8. 以 c_no 升序、grade 降序查询 score 表的所有记录 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SELECT * FROM score ORDER BY c_no ASC , grade DESC ; + | s_no | c_no | grade | + | 103 | 3 -105 | 92 | | 105 | 3 -105 | 88 | | 109 | 3 -105 | 76 | | 103 | 3 -245 | 86 | | 105 | 3 -245 | 75 | | 109 | 3 -245 | 68 | | 103 | 6 -166 | 85 | | 109 | 6 -166 | 81 | | 105 | 6 -166 | 79 | + 9 rows in set (0.00 sec)
9. 查询 “95031” 班的学生人数
使用 COUNT()
函数进行统计
1 SELECT COUNT (* ) FROM student WHERE class= "95031";
10. 查询 score 表中的最高分的学生学号和课程编号(子查询或排序查询)
LIMIT 关键字用法:LIMIT 起始位置, 数量
1 2 3 4 5 6 7 8 9 10 11 mysql> SELECT s_no, c_no FROM score WHERE grade= (SELECT MAX (grade) FROM score); + | s_no | c_no | + | 103 | 3 -105 | + 1 row in set (0.00 sec)mysql> mysql> SELECT s_no, c_no FROM score ORDER BY grade DESC LIMIT 0 , 1 ; mysql>
11. 查询每门课的平均成绩
AVG()
函数可以计算平均值; GROUP BY
可以按列分组
1 2 3 4 5 6 7 8 9 10 11 mysql> mysql> SELECT c_no, AVG (grade) FROM score GROUP BY c_no; + | c_no | AVG (grade) | + | 3 -105 | 85.3333 | | 3 -245 | 76.3333 | | 6 -166 | 81.6667 | + 3 rows in set (0.00 sec)
12. 查询 score 表中至少有 2 名学生选修的, 并且以 3 开头的课程的平均分
模糊查询可以使用 LIKE
关键字,”%
“ 是一个通配符,3%
用来匹配以 3 开头的课程 在 GROUP BY
使用条件查询需要使用 HAVING
关键字而非 WHERE
1 2 3 4 5 6 7 8 mysql> SELECT c_no, AVG (grade), COUNT (c_no) FROM score GROUP BY c_no HAVING COUNT (c_no) >= 2 AND c_no LIKE '3%' ; + | c_no | AVG (grade) | COUNT (c_no) | + | 3 -105 | 85.3333 | 3 | | 3 -245 | 76.3333 | 3 | + 2 rows in set (0.00 sec)
13. 查询分数大于 70 但是小于 90 的 s_no 列: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 mysql> SELECT s_no, grade FROM score WHERE grade > 70 AND grade < 90 ; + | s_no | grade | + | 103 | 86 | | 103 | 85 | | 105 | 88 | | 105 | 75 | | 105 | 79 | | 109 | 76 | | 109 | 81 | + 7 rows in set (0.04 sec)mysql> SELECT s_no, grade FROM score WHERE grade BETWEEN 71 AND 89 ; mysql>
14. 查询所有的学生 s_name , c_no, grade 列 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SELECT s_name, c_no, grade FROM student, score WHERE student.s_no= score.s_no; + | s_name | c_no | grade | + | 王丽 | 3 -105 | 92 | | 王丽 | 3 -245 | 86 | | 王丽 | 6 -166 | 85 | | 王芳 | 3 -105 | 88 | | 王芳 | 3 -245 | 75 | | 王芳 | 6 -166 | 79 | | 赵铁柱 | 3 -105 | 76 | | 赵铁柱 | 3 -245 | 68 | | 赵铁柱 | 6 -166 | 81 | + 9 rows in set (0.04 sec)
15. 查询所有学生的 s_no, c_name, grade 列 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SELECT s_no, c_name, grade FROM course, score WHERE course.c_no= score.c_no; + | s_no | c_name | grade | + | 103 | 计算机导论 | 92 | | 103 | 操作系统 | 86 | | 103 | 数字电路 | 85 | | 105 | 计算机导论 | 88 | | 105 | 操作系统 | 75 | | 105 | 数字电路 | 79 | | 109 | 计算机导论 | 76 | | 109 | 操作系统 | 68 | | 109 | 数字电路 | 81 | + 9 rows in set (0.07 sec)
16. 查询所有的学生 s_name , c_name, grade 列 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SELECT s_name, c_name, grade FROM student, course, score WHERE student.s_no= score.s_no AND course.c_no= score.c_no; + | s_name | c_name | grade | + | 王丽 | 计算机导论 | 92 | | 王丽 | 操作系统 | 86 | | 王丽 | 数字电路 | 85 | | 王芳 | 计算机导论 | 88 | | 王芳 | 操作系统 | 75 | | 王芳 | 数字电路 | 79 | | 赵铁柱 | 计算机导论 | 76 | | 赵铁柱 | 操作系统 | 68 | | 赵铁柱 | 数字电路 | 81 | + 9 rows in set (0.00 sec)
17. 查询班级是’95031’班学生每门课的平均分 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql> mysql> SELECT c_no, grade FROM student, score WHERE student.s_no= score.s_no AND class= '95031' ; mysql> mysql> SELECT c_no, AVG (grade) FROM student, score WHERE student.s_no= score.s_no AND class= '95031' GROUP BY c_no; + | c_no | AVG (grade) | + | 3 -105 | 82.0000 | | 3 -245 | 71.5000 | | 6 -166 | 80.0000 | + 3 rows in set (0.00 sec)mysql> mysql> SELECT c_no, AVG (grade) FROM score WHERE s_no IN (SELECT s_no FROM student WHERE class= '95031' ) GROUP BY c_no; mysql>
18. 查询选修 ‘3-105’ 课程且成绩高于 ‘109’ 号同学的 ‘3-105’ 课程成绩的所有同学的记录 1 2 3 4 5 6 7 8 9 10 11 12 mysql> mysql> SELECT grade FROM student, score WHERE student.s_no= score.s_no AND c_no= '3-105' AND score.s_no= '109' ; mysql> 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)
19. 查询成绩高于选修了 ‘3-105’ 课程且学号为 ‘109’ 的同学的成绩的所有记录
相比于 T18,该问题不限制课程号,只要成绩大于109号同学的3-105课程成绩即可,并且查询的只是 score 表
1 2 3 4 5 6 7 8 9 10 11 12 mysql> SELECT * FROM score WHERE 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 | c_no | grade | + | 103 | 3 -105 | 92 | | 103 | 3 -245 | 86 | | 103 | 6 -166 | 85 | | 105 | 3 -105 | 88 | | 105 | 6 -166 | 79 | | 109 | 6 -166 | 81 | + 6 rows in set (0.00 sec)
20. 查询与学号为 101 和 108 的同学同年出生的所有学生的 s_no、s_name 和 s_birthday
因为要匹配同年出生,所以要对取出生日的年份,使用 YEAR()
函数
1 2 3 4 5 6 7 8 9 10 mysql> SELECT s_no, s_name, s_birthday FROM student WHERE YEAR (s_birthday) IN (SELECT YEAR (s_birthday) FROM student WHERE s_no IN ('101' , '108' )); + | s_no | s_name | s_birthday | + | 101 | 曾华 | 1977 -09 -01 00 :00 :00 | | 102 | 匡明 | 1975 -10 -02 00 :00 :00 | | 105 | 王芳 | 1975 -02 -10 00 :00 :00 | | 108 | 张全蛋 | 1975 -02 -10 00 :00 :00 | + 4 rows in set (0.00 sec)
21. 查询任课教师为 ‘张旭’ 的学生的成绩 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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> mysql> SELECT t_no FROM teacher WHERE t_name= '张旭' ; mysql> mysql> SELECT c_no FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_name= '张旭' ); mysql> 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>
22. 查询选修人数大于 2 人的课程的教师姓名 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 mysql> mysql> SELECT course.c_no, c_name, t_name, COUNT (course.c_no) FROM teacher, course, score WHERE teacher.t_no= course.t_no AND course.c_no= score.c_no GROUP BY score.c_no HAVING COUNT (course.c_no) > 2 ; + | c_no | c_name | t_name | COUNT (course.c_no) | + | 3 -105 | 计算机导论 | 王萍 | 3 | | 3 -245 | 操作系统 | 李诚 | 3 | | 6 -166 | 数字电路 | 张旭 | 3 | + 3 rows in set (0.00 sec)mysql> mysql> mysql> SELECT c_no FROM score GROUP BY c_no HAVING COUNT (* ) > 2 ; mysql> mysql> SELECT t_no FROM course WHERE c_no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT (* ) > 2 ); mysql> 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)
23. 查询 95033 班和 95031 班全体学生的记录 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SELECT * FROM student WHERE class IN ('95033' , '95031' ); + | s_no | s_name | s_gender | s_birthday | class | + | 101 | 曾华 | 男 | 1977 -09 -01 00 :00 :00 | 95033 | | 102 | 匡明 | 男 | 1975 -10 -02 00 :00 :00 | 95031 | | 103 | 王丽 | 女 | 1976 -01 -23 00 :00 :00 | 95033 | | 104 | 李军 | 男 | 1976 -02 -20 00 :00 :00 | 95033 | | 105 | 王芳 | 女 | 1975 -02 -10 00 :00 :00 | 95031 | | 106 | 陆军 | 男 | 1974 -06 -03 00 :00 :00 | 95031 | | 107 | 王尼玛 | 男 | 1976 -02 -20 00 :00 :00 | 95033 | | 108 | 张全蛋 | 男 | 1975 -02 -10 00 :00 :00 | 95031 | | 109 | 赵铁柱 | 男 | 1974 -06 -03 00 :00 :00 | 95031 | + 9 rows in set (0.00 sec)
24. 查询存在 85 分以上成绩的课程 c_no 1 2 3 4 5 6 7 8 9 mysql> SELECT c_no FROM score WHERE grade > 85 ; + | c_no | + | 3 -105 | | 3 -245 | | 3 -105 | + 3 rows in set (0.00 sec)
25. 查出所有 ‘计算机系’ 教师所教课程的成绩表 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 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 department= '计算机系' ; + | s_no | c_no | grade | + | 103 | 3 -245 | 86 | | 105 | 3 -245 | 75 | | 109 | 3 -245 | 68 | | 103 | 3 -105 | 92 | | 105 | 3 -105 | 88 | | 109 | 3 -105 | 76 | + 6 rows in set (0.00 sec)mysql> mysql> mysql> SELECT t_no FROM teacher WHERE department= '计算机系' ; mysql> mysql> SELECT c_no FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE department= '计算机系' ); mysql> 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)
27. 查询在 ‘3-105’ 课程中考得至少比其中一些 ‘3-245’ 课程好的同学的 c_no, s_no 和 grade, 并且按照 grade 从高到低次序排序
只要 ‘3-105’ 课程的学生成绩比其中至少一个 ‘3-245’ 课程的学生成绩好即符合要求,要表达 任意一个
的目的,可以使用 ANY()
函数
1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> SELECT * FROM score WHERE c_no= '3-105' AND grade> ANY (SELECT grade FROM score WHERE c_no= '3-245' ) ORDER BY grade DESC ; + | s_no | c_no | grade | + | 103 | 3 -105 | 92 | | 105 | 3 -105 | 88 | | 109 | 3 -105 | 76 | + 3 rows in set (0.00 sec)mysql> mysql> SELECT * FROM score WHERE c_no= '3-105' AND grade> (SELECT MIN (grade) FROM score WHERE c_no= '3-245' ) ORDER BY grade DESC ; mysql>
28. 查询在 ‘3-105’ 课程中考得比 ‘3-245’ 课程中都好的同学的 c_no, s_no 和 grade
相比于 T27,本题要表达的是 都要好
的概念,因此需要使用 ALL()
函数
1 2 3 4 5 6 7 8 9 10 11 12 mysql> SELECT * FROM score WHERE c_no= '3-105' AND grade> ALL (SELECT grade FROM score WHERE c_no= '3-245' ); + | s_no | c_no | grade | + | 103 | 3 -105 | 92 | | 105 | 3 -105 | 88 | + 2 rows in set (0.00 sec)mysql> mysql> SELECT * FROM score WHERE c_no= '3-105' AND grade> (SELECT MAX (grade) FROM score WHERE c_no= '3-245' ); mysql>
29. 查询所有教师和同学的 name、gender、birthday
因为 t_name 和 s_name 都表示姓名,并且在联合查询的结果中,会发现只有 t_name、t_gender、t_birthday,所以为了统一,可以使用别名,利用 AS
关键字即可
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 mysql> SELECT t_name AS name, t_gender AS gender, t_birthday AS birthday FROM teacher - > UNION - > SELECT s_name, s_gender, s_birthday FROM student; + | name | gender | birthday | + | 李诚 | 男 | 1958 -12 -02 00 :00 :00 | | 王萍 | 女 | 1972 -05 -05 00 :00 :00 | | 刘冰 | 女 | 1977 -08 -14 00 :00 :00 | | 张旭 | 男 | 1969 -03 -12 00 :00 :00 | | 曾华 | 男 | 1977 -09 -01 00 :00 :00 | | 匡明 | 男 | 1975 -10 -02 00 :00 :00 | | 王丽 | 女 | 1976 -01 -23 00 :00 :00 | | 李军 | 男 | 1976 -02 -20 00 :00 :00 | | 王芳 | 女 | 1975 -02 -10 00 :00 :00 | | 陆军 | 男 | 1974 -06 -03 00 :00 :00 | | 王尼玛 | 男 | 1976 -02 -20 00 :00 :00 | | 张全蛋 | 男 | 1975 -02 -10 00 :00 :00 | | 赵铁柱 | 男 | 1974 -06 -03 00 :00 :00 | + 13 rows in set (0.00 sec)
30. 查询所有 ‘女’ 教师和 ‘女’ 学生的 name、gender 和 birthday 1 2 3 4 5 6 7 8 9 10 11 12 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
不姓 '王' 的同学
的正则表达式:^[^王].+
,第一个 ^
表示要匹配的字符串的开头,[]
内的 ^
表示剔除方括号内的内容, ^[^王]
表示第字符串开头不为王,.
表示任意任意字符,.+
表示匹配任意字符一次或多次,因为中文名字不会只有姓氏,还至少包含一个名
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 mysql> SELECT * FROM student WHERE s_name NOT LIKE '王%' ; + | s_no | s_name | s_gender | s_birthday | class | + | 101 | 曾华 | 男 | 1977 -09 -01 00 :00 :00 | 95033 | | 102 | 匡明 | 男 | 1975 -10 -02 00 :00 :00 | 95031 | | 104 | 李军 | 男 | 1976 -02 -20 00 :00 :00 | 95033 | | 106 | 陆军 | 男 | 1974 -06 -03 00 :00 :00 | 95031 | | 108 | 张全蛋 | 男 | 1975 -02 -10 00 :00 :00 | 95031 | | 109 | 赵铁柱 | 男 | 1974 -06 -03 00 :00 :00 | 95031 | + 6 rows in set (0.00 sec)mysql> mysql> SELECT * FROM student WHERE s_name REGEXP '^[^王].+' ; mysql>
35. 查询 student 中每个学生的姓名和年龄
年龄 = 当前时间 - 出生年份,当前时间可以通过 NOW()
函数获取,年份可以通过 YEAR()
函数获取
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SELECT s_name, YEAR (NOW()) - YEAR (s_birthday) AS age FROM student; + | s_name | age | + | 曾华 | 43 | | 匡明 | 45 | | 王丽 | 44 | | 李军 | 44 | | 王芳 | 45 | | 陆军 | 46 | | 王尼玛 | 44 | | 张全蛋 | 45 | | 赵铁柱 | 46 | + 9 rows in set (0.00 sec)
36. 查询 student 表中最大和最小的 s_birthday 的值 1 2 3 4 5 6 7 mysql> SELECT MAX (s_birthday), MIN (s_birthday) FROM student; + | MAX (s_birthday) | MIN (s_birthday) | + | 1977 -09 -01 00 :00 :00 | 1974 -06 -03 00 :00 :00 | + 1 row in set (0.00 sec)
37. 以班级号和年龄从大到小的顺序查询 student 表中的全部记录 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SELECT * FROM student ORDER BY class DESC , s_birthday; + | s_no | s_name | s_gender | s_birthday | class | + | 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 | + 9 rows in set (0.00 sec)
38. 查询所有 ‘男’ 教师及其所教授的课程 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 mysql> SELECT * FROM teacher, course WHERE t_gender= '男' AND teacher.t_no= course.t_no; + | t_no | t_name | t_gender | t_birthday | title | department | c_no | c_name | t_no | + | 804 | 李诚 | 男 | 1958 -12 -02 00 :00 :00 | 副教授 | 计算机系 | 3 -245 | 操作系统 | 804 | | 856 | 张旭 | 男 | 1969 -03 -12 00 :00 :00 | 讲师 | 电子工程系 | 6 -166 | 数字电路 | 856 | + 2 rows in set (0.00 sec)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 CREATE TABLE rank ( low INT (3 ), upp INT (3 ), level char (1 ) ); INSERT INTO rank VALUES (90 , 100 , 'A' );INSERT INTO rank VALUES (80 , 89 , 'B' );INSERT INTO rank VALUES (70 , 79 , 'C' );INSERT INTO rank VALUES (60 , 69 , 'D' );INSERT INTO rank VALUES (0 , 59 , 'E' );
查询所有学生的 s_no、c_no和 level 列
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SELECT s_no, c_no, level FROM score, rank WHERE grade BETWEEN low AND upp; + | s_no | c_no | level | + | 103 | 3 -105 | A | | 103 | 3 -245 | B | | 103 | 6 -166 | 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 | + 9 rows in set (0.00 sec)
进阶查询练习 - 连接查询 SQL 共有四种连接查询
内连接 inner join
或 join
外连接
左连接 left join
或者 left outer join
右连接 right join
或者 right outer join
完全外连接 full join
或者 full outer join
数据准备
创建数据库
1 CREATE DATABASE join_select_test;
切换数据库
创建 person
表
1 2 3 4 5 CREATE TABLE person ( id INT , name VARCHAR (20 ), cardId INT );
创建 card
表
1 2 3 4 CREATE TABLE card ( id INT , name VARCHAR (20 ) );
添加数据
添加 person
表数据
1 INSERT INTO person VALUES (1 , '张三' , 1 ), (2 , '李四' , 3 ), (3 , '王五' , 6 );
添加 card
表数据
1 INSERT INTO card VALUES (1 , '饭卡' ), (2 , '建行卡' ), (3 , '农行卡' ), (4 , '工商卡' ), (5 , '邮政卡' );
分析两张表发现,person
表并没有为 cardId
字段设置一个在 card
表中对应的 id
外键。如果设置了的话,person
中 cardId
字段值为 6
的记录就插不进去,因为该 cardId
值在 card
表中并对应的存在。
1. 内连接
要查询这两张表中有关系 的数据,可以使用 JOIN
或者 INNER JOIN
( 内连接 ) 将它们连接在一起。内连接查询条件需要使用 ON
关键字
1 2 3 4 5 6 7 8 9 10 11 mysql> SELECT * FROM person INNER JOIN card on person.cardId = card.id; + | id | name | cardId | id | name | + | 1 | 张三 | 1 | 1 | 饭卡 | | 2 | 李四 | 3 | 3 | 农行卡 | + 2 rows in set (0.00 sec)mysql> SELECT * FROM person JOIN card on person.cardId = card.id; mysql>
可见内连接就是把两张表中有关联的数据连接在了一起,同样的效果也可以使用 WHERE
条件查询实现。
1 2 3 4 5 6 7 8 mysql> SELECT * FROM person, card WHERE person.cardId= card.id; + | id | name | cardId | id | name | + | 1 | 张三 | 1 | 1 | 饭卡 | | 2 | 李四 | 3 | 3 | 农行卡 | + 2 rows in set (0.00 sec)
2. 左外连接
表 A
左外连接表 B
,那么表 A
中的列都会完整显示,而表 B
中对应的缺失部分会用 NULL
代替,可以使用 LEFT JOIN
或者 LEFT OUTER JOIN
( 左外连接 ) 将它们连接在一起
1 2 3 4 5 6 7 8 9 10 11 12 mysql> SELECT * FROM person LEFT JOIN card on person.cardId = card.id; + | id | name | cardId | id | name | + | 1 | 张三 | 1 | 1 | 饭卡 | | 2 | 李四 | 3 | 3 | 农行卡 | | 3 | 王五 | 6 | NULL | NULL | + 3 rows in set (0.00 sec)mysql> SELECT * FROM person LEFT OUTER JOIN card on person.cardId = card.id; mysql>
左连接可以理解为,保证了左边表的完整性,而右边表为了配合左边表会将缺失值用 NULL
代替。
3. 右外连接
表 A
右外连接表 B
,那么表 B
中的列都会完整显示,而表 A
中对应的缺失部分会用 NULL
代替,可以使用 RIGHT JOIN
或者 RIGHT OUTER JOIN
( 右外连接 ) 将它们连接在一起
1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> SELECT * FROM person RIGHT JOIN card on person.cardId = card.id; + | id | name | cardId | id | name | + | 1 | 张三 | 1 | 1 | 饭卡 | | 2 | 李四 | 3 | 3 | 农行卡 | | NULL | NULL | NULL | 2 | 建行卡 | | NULL | NULL | NULL | 4 | 工商卡 | | NULL | NULL | NULL | 5 | 邮政卡 | + 5 rows in set (0.00 sec)mysql> SELECT * FROM person RIGHT OUTER JOIN card on person.cardId = card.id; mysql>
与左外连接正好相反的理解,右外连接保证了右边表的完整性,而左边表为了配合右边表会将缺失值用 NULL
代替。
4. 全外连接 注意 MySQL 不支持全外连接关键字(FULL JOIN
或者 FULL OUTER JOIN
),但是可以使用 UNION 来实现全外连接的效果
全外连接,就是保证两张表的数据完整性,而两张表对应部分的缺失值就会用 NULL
代替
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> mysql> SELECT * FROM person LEFT JOIN card on person.cardId = card.id - > UNION - > SELECT * FROM person RIGHT JOIN card on person.cardId = card.id; + | id | name | cardId | id | name | + | 1 | 张三 | 1 | 1 | 饭卡 | | 2 | 李四 | 3 | 3 | 农行卡 | | 3 | 王五 | 6 | NULL | NULL | | NULL | NULL | NULL | 2 | 建行卡 | | NULL | NULL | NULL | 4 | 工商卡 | | NULL | NULL | NULL | 5 | 邮政卡 | + 6 rows in set (0.00 sec)
为了便于理解,下面用文氏图来表示两张表四种连接查询的关系
根据文氏图的表达结果,还可以有另外几种情况,下面将所有情况统一列举