MySQL 子查询

MySQL 数据库中的子查询是一种嵌套在主查询中的查询，它可以在主查询中使用子查询的结果来进行条件判断或数据操作。子查询可以出现在SELECT、FROM、WHERE等子句中，根据子查询的位置和用途不同，其语法和用法也有所不同。而且可以进行多层嵌套。

子查询在 WHERE 中的语法格式如下：

WHERE <表达式> <操作符> (子查询)

其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。

1）IN | NOT IN

当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。

2）EXISTS | NOT EXISTS

用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。

案例

以学生表和成绩表做本案例讲解。

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sex` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三名', '男');
INSERT INTO `student` VALUES ('2', '李四浩', '男');
INSERT INTO `student` VALUES ('3', '王五期', '女');
INSERT INTO `student` VALUES ('5', '三七熊', '女');

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `studentid` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '1', '语文', '81');
INSERT INTO `course` VALUES ('2', '1', '数学', '74');
INSERT INTO `course` VALUES ('3', '1', '英语', '56');
INSERT INTO `course` VALUES ('4', '2', '语文', '71');
INSERT INTO `course` VALUES ('5', '2', '数学', '77');
INSERT INTO `course` VALUES ('6', '2', '英语', '66');
INSERT INTO `course` VALUES ('7', '3', '语文', '61');
INSERT INTO `course` VALUES ('8', '3', '数学', '87');
INSERT INTO `course` VALUES ('9', '3', '英语', '86');
INSERT INTO `course` VALUES ('10', '4', '语文', '71');
INSERT INTO `course` VALUES ('11', '4', '数学', '57');
INSERT INTO `course` VALUES ('12', '4', '英语', '76');

student学生表原始数据

mysql> SELECT * from student;
+----+-----------+------+
| id | name      | sex  |
+----+-----------+------+
|  1 | 张三名    | 男   |
|  2 | 李四浩    | 男   |
|  3 | 王五期    | 女   |
|  5 | 三七熊    | 女   |
+----+-----------+------+

course成绩表原始数据

学生分别有语文、数学、英语三门成绩，studentid是student学生表的id

mysql> SELECT * from course;
+----+-----------+--------+-------+
| id | studentid | name   | score |
+----+-----------+--------+-------+
|  1 |         1 | 语文   |    81 |
|  2 |         1 | 数学   |    74 |
|  3 |         1 | 英语   |    56 |
|  4 |         2 | 语文   |    71 |
|  5 |         2 | 数学   |    77 |
|  6 |         2 | 英语   |    66 |
|  7 |         3 | 语文   |    61 |
|  8 |         3 | 数学   |    87 |
|  9 |         3 | 英语   |    86 |
| 10 |         4 | 语文   |    71 |
| 11 |         4 | 数学   |    57 |
| 12 |         4 | 英语   |    76 |
+----+-----------+--------+-------+

EXISTS案例

查询的studentid查找student的id是否相等，就是是否有记录则输出

mysql> SELECT * from course c where  EXISTS  (SELECT * from student s where s.id=c.studentid);
+----+-----------+--------+-------+
| id | studentid | name   | score |
+----+-----------+--------+-------+
|  1 |         1 | 语文   |    81 |
|  2 |         1 | 数学   |    74 |
|  3 |         1 | 英语   |    56 |
|  4 |         2 | 语文   |    71 |
|  5 |         2 | 数学   |    77 |
|  6 |         2 | 英语   |    66 |
|  7 |         3 | 语文   |    61 |
|  8 |         3 | 数学   |    87 |
|  9 |         3 | 英语   |    86 |
+----+-----------+--------+-------+

IN案例

根据studentid在student有数据的记录

mysql> SELECT * from course where studentid in (SELECT id from student);
+----+-----------+--------+-------+
| id | studentid | name   | score |
+----+-----------+--------+-------+
|  1 |         1 | 语文   |    81 |
|  2 |         1 | 数学   |    74 |
|  3 |         1 | 英语   |    56 |
|  4 |         2 | 语文   |    71 |
|  5 |         2 | 数学   |    77 |
|  6 |         2 | 英语   |    66 |
|  7 |         3 | 语文   |    61 |
|  8 |         3 | 数学   |    87 |
|  9 |         3 | 英语   |    86 |
+----+-----------+--------+-------+

总结来说，EXISTS适用于子查询结果集较小且希望利用索引的情况，而IN适用于子查询结果集较小且不需要利用索引的情况。在实际选择使用时，应根据具体的查询需求和数据库的性能优化策略来决定使用哪一种方法。