MySQL 子查询

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‘);

-- 查询所有的数据
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);