概念：SQL语句中嵌套SELECT语句，成为嵌套查询，又称子查询。

SELECT * FROM t1 WHERE cloumn1 = (SELECT column FROM t2);

子查询外部的语句可以是 INSERT/UPDATE/DELETE/SELECT的任意一个。

  2. SET NAMES utf8mb4;
  3. SET FOREIGN_KEY_CHECKS = 0;
  5. -- ----------------------------
  6. -- Table structure for emp
  7. -- ----------------------------
  8. DROP TABLE IF EXISTS `emp`;
  9. CREATE TABLE `emp`  (
  10.   `id` int NULL DEFAULT NULL COMMENT '编号',
  11.   `workno` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '工号',
  12.   `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
  13.   `gender` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '性别',
  14.   `age` tinyint UNSIGNED NULL DEFAULT NULL COMMENT '年龄',
  15.   `job` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '工作',
  16.   `idcard` char(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '身份证号',
  17.   `salary` decimal(10, 2) NULL DEFAULT NULL COMMENT '工资',
  18.   `managerid` int NULL DEFAULT NULL COMMENT '直属id',
  19.   `workaddress` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '工作地址',
  20.   `dept_id` int NULL DEFAULT NULL COMMENT '部门id',
  21.   `entrydate` date NULL DEFAULT NULL COMMENT '入职时间',
  22.   INDEX `emp_ibfk_1`(`dept_id` ASC) USING BTREE,
  23.   CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
  24. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '员工表' ROW_FORMAT = Dynamic;
  26. -- ----------------------------
  27. -- Records of emp
  28. -- ----------------------------
  29. INSERT INTO `emp` VALUES (1, '00001', '柳岩', '女', 20, '职员', '123456789012345678', 7000.00, 12, '北京', 2, '2000-01-01');
  30. INSERT INTO `emp` VALUES (2, '00002', '张无忌', '男', 18, '职员', '123456789012345670', 7000.00, 12, '北京', 2, '2005-09-01');
  31. INSERT INTO `emp` VALUES (3, '00003', '韦一笑', '女', 38, '会计', '123456789012345670', 8500.00, 9, '上海', 3, '2005-08-01');
  32. INSERT INTO `emp` VALUES (4, '00004', '赵敏', '女', 18, '销售总监', '123456789012345670', 8000.00, 13, '北京', 4, '2009-12-01');
  33. INSERT INTO `emp` VALUES (5, '00005', '小昭', '女', 16, '销售', '123456789012345678', 6000.00, 4, '上海', 4, '2007-07-01');
  34. INSERT INTO `emp` VALUES (6, '00006', '杨逍', '男', 28, '职员', '12345678901234567X', 7500.00, 12, '北京', 2, '2006-01-01');
  35. INSERT INTO `emp` VALUES (7, '00007', '范瑶', '男', 40, '开发', '123456789012345670', 12000.00, 14, '北京', 1, '2005-05-01');
  36. INSERT INTO `emp` VALUES (8, '00008', '黛绮丝', '女', 38, '出纳', '123456789012345670', 8500.00, 9, '天津', 2, '2015-05-01');
  37. INSERT INTO `emp` VALUES (9, '00009', '范凉凉', '女', 45, '财务部总监', '123456789012345678', 13000.00, 13, '北京', 3, '2010-04-01');
  38. INSERT INTO `emp` VALUES (10, '00010', '陈友谅', '男', 53, '程序员鼓励师', '123456789012345670', 5000.00, 14, '上海', 1, '2011-01-01');
  39. INSERT INTO `emp` VALUES (11, '00011', '张士诚', '男', 55, '开发', '123456789012345670', 12000.00, 14, '江苏', 1, '2015-05-01');
  40. INSERT INTO `emp` VALUES (12, '00012', '常遇春', '男', 32, '市场部总监', '123456789012345670', 8000.00, 13, '北京', 2, '2004-02-01');
  41. INSERT INTO `emp` VALUES (13, '00013', '张三丰', '男', 88, '总裁', '123456789012345678', 20000.00, NULL, '江苏', 5, '2020-11-01');
  42. INSERT INTO `emp` VALUES (14, '00014', '灭绝', '女', 65, '项目经理', '123456789012345670', 15000.00, 13, '西安', 1, '2019-05-01');
  43. INSERT INTO `emp` VALUES (15, '00015', '胡青牛', '男', 70, '开发', '12345678901234567X', 12000.00, 14, '西安', 1, '2018-04-01');
  44. INSERT INTO `emp` VALUES (16, '00016', '周芷若', '女', 18, '销售', '123456789012345671', 7000.00, 4, '北京', 4, '2012-06-01');
  46. SET FOREIGN_KEY_CHECKS = 1;
  1. SET NAMES utf8mb4;
  2. SET FOREIGN_KEY_CHECKS = 0;
  4. -- ----------------------------
  5. -- Table structure for dept
  6. -- ----------------------------
  7. DROP TABLE IF EXISTS `dept`;
  8. CREATE TABLE `dept`  (
  9.   `id` int NOT NULL AUTO_INCREMENT,
  10.   `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '部门名称',
  11.   PRIMARY KEY (`id`) USING BTREE
  12. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
  14. -- ----------------------------
  15. -- Records of dept
  16. -- ----------------------------
  17. INSERT INTO `dept` VALUES (1, '研发部');
  18. INSERT INTO `dept` VALUES (2, '市场部');
  19. INSERT INTO `dept` VALUES (3, '财务部');
  20. INSERT INTO `dept` VALUES (4, '销售部');
  21. INSERT INTO `dept` VALUES (5, '总经办');
  23. SET FOREIGN_KEY_CHECKS = 1;

所有举的例子来源于以上两张表

子查询分类

标量子查询（子查询结果为单个值）

  1. 概念：
    1. 子查询返回结果是单个值（数字，字符串，日期等），最简单的形式。
  2. 常用操作符：
    1. =、<>、>、>=、<、<=

  3. 举例：

    1. -- 根据销售部部门ID，查询员工信息
    2. SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE `name`='销售部');
    3. -- 查询“赵敏”入职之后的员工信息
    4. SELECT * FROM emp WHERE entrydate > (SELECT entrydate FROM emp WHERE `name`='赵敏');

    列子查询（子查询结果为一列）

  4. 概念：

    1. 子查询返回的结果是一列（可以是多行）。

  5. 常用操作符： | 操作符 | 描述 | | —- | —- | | IN | 在指定的集合范围内，多选一 | | NOT IN | 不在指定的集合范围内 | | ANY | 子查询返回列表中，有任意一个满足即可 | | SOME | 与ANY等同，使用SOME的地方都可以使用ANY | | ALL | 子查询返回列表的所有值都必须满足 |

  6. 举例：

    1. -- 查询"销售部"和“市场部”的员工信息
    2. SELECT * FROM emp WHERE dept_id in ( SELECT id from dept where `name` = '销售部' or `name` ='市场部' );
    3. -- 查询比“财务部”所有人工资都要高的员工信息
    4. SELECT * FROM emp WHERE salary > ALL( SELECT salary FROM emp WHERE  dept_id =( SELECT id FROM dept WHERE `name`='财务部' ) );
    5. -- 查询比“研发部”其中任意一人工资高的员工信息
    6. SELECT * FROM emp WHERE salary > ANY( SELECT salary FROM emp WHERE  dept_id =( SELECT id FROM dept WHERE `name`='研发部' ) );

    行子查询（子查询结果为一行）

  7. 概念：

    1. 子查询返回的结果是一行（可以是多列）。
  8. 常用操作符：
    1. =、<>、IN、NOT IN

  9. 举例：

    1. -- 查询与“张无忌”的薪资及直属领导相同的员工信息
    2. SELECT * FROM emp WHERE (salary,managerid) =( SELECT salary,managerid FROM emp WHERE `name`='张无忌' );

    表子查询（子查询结果为多行多列）

  10. 概念

    1. 返回的结果是多行多列
  11. 常用操作符
    1. IN
  12. 举例：
    1. -- 查询与 "张士诚" , "柳岩" 的职位和薪资相同的员工信息
    2. SELECT * FROM emp WHERE (job,salary) IN ( SELECT job,salary FROM emp WHERE `name`='张士诚' or `name`='柳岩' );
    3. -- 查询入职日期是 "2012-01-01" 之后的员工信息 , 及其部门信息
    4. SELECT e.* ,d.`name` '部门'  FROM ( SELECT * FROM emp WHERE entrydate >'2012-01-01' ) e 
    5. LEFT JOIN dept d on e.dept_id = d.id

    根据子查询位置分类

    WHERE之后子查询
    FROM之后子查询
    SELECT之后子查询