MySQL 子查询

1.表数据准备

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
-------------------------------
CREATE TABLE `emp` (
  `dept_no` varchar(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('a', '张三', '50');
INSERT INTO `emp` VALUES ('a', '李四', '60');
INSERT INTO `emp` VALUES ('a', '王五', '70');
INSERT INTO `emp` VALUES ('b', '赵六', '60');
INSERT INTO `emp` VALUES ('b', '周七', '80');
INSERT INTO `emp` VALUES ('c', '朱八', '100');
INSERT INTO `emp` VALUES ('c', '龙九', '50');

2.SQL子查询

-- 查询所有的数据
SELECT * FROM emp;
-- 查询高于平均分的人员信息
SELECT AVG( score ) FROM emp;
-- 应用子查询进行查询高于平均分的人员信息
SELECT *  FROM emp  WHERE score > ( SELECT AVG( score ) FROM tb_lemon_emp );
-- 查询有不及格员工的部门
SELECT * FROM emp  WHERE score < 60;
SELECT * FROM emp a WHERE a.dept_no = 'a' or a.dept_no = 'c';
-- in ----使用in的时候注意只能in 单列 不能in多列
select * from emp a where a.dept_no in ('a','c');
select * from emp a where a.dept_no in (SELECT dept_no FROM emp  WHERE score < 60);
-- not in 取反---
select * from emp a where a.dept_no not in ('a','c');
-- exists 判断条件是否成立 
select * from emp a where  exists (SELECT dept_no FROM emp  WHERE score < 60);  -- 恒能查到结果
select * from emp a where  exists (SELECT dept_no FROM emp  WHERE score > 100);  -- 恒不能查到结果

-- 如果exists 外表（主）和里表（子），如果是条件关系的联系，那么一般是没用
select * from emp a where  exists (SELECT dept_no  FROM emp b  WHERE score < 60 and a.dept_no=b.dept_no);
-- not exists ---
select * from emp a where  not exists (SELECT dept_no  FROM emp b  WHERE score < 60 and a.dept_no=b.dept_no);

3.in和exists的区别

　　in 是先执行子查询然后得到子查询的结果集，再用子查询的结果去匹配外部表。（子查询结果的长度 * 外表索引的时间）

　　exists 是先遍历循环外表，然后看外表中的记录有没有和内表中的数据一样的，如果一样就将结果放到结果集中。（外表结果集长度 * 内表索引时间）

　　结论：外表大，子查询结果小，就用 in ；如果外表小，子查询结果大 ，就用 exists；

4. not in 和 not exists 的区别

　　如果查询语句使用了not in ， 那么对内外表都是进行的全表的扫描，没有用到索引；

　　而 使用 not exists 的子查询依然用到表上的索引，所以无论哪个表大，用 not exists 都比 not in 要快！

5.总结：测试人员必备的SQL子查询，学习柠檬班公开课后总结，不足之处后续修正！