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子查询,学习柠檬班公开课后总结,不足之处后续修正!