对于MySQL中的子查询的理解

每天记录一下自己的心得体会,今天写一下MySQL中的子查询，个人感觉子查询的难度比之前学的查询都比较难，可能是语句太多吧，对于初学者的难度会有一点，反正我第一次接触的时候是崩溃的，因为·代码有点多，因为内容比较多，所以小弟要是有讲的不到位的，希望各位在下面给出建议，好了，话不多说，我们先来建三张表

CREATE TABLE EMP(
      EMPNO  INT,
       ENAME  VARCHAR(50),
       JOB  VARCHAR(20),
       MGR INT,
        HIREDATE  DATE,
         SAL  DECIMAL(7,2),
         COMM  DECIMAL(7,2),
          DEPTNO  INT
);

INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (1001,'锤石','文员',1013,'2000-12-17',8000.00,NULL,20);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (1002,'女警','销售员',1006,'2001-2-20',16000.00,3000.00,30);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (1003,'小黄毛','销售员',1006,'2001-2-22',12500.00,5000.00,30);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (1004,'剑圣','经理',1009,'2001-04-02',29750.00,NULL,20);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (1005,'老鼠','销售员',1006,'2001-9-28',12500.00,14000.00,30);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (1006,'蛮子','经理',1009,'2000-05-01',28500.00,NULL,30);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (1007,'菊花信','经理',1009,'2001-09-01',24500.00,NULL,10);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (1008,'狮子狗','分析师',1004,'2007-04-19',30000.00,NULL,20);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (1009,'猴哥','董事长',NULL,'2001-11-17',50000.00,NULL,10);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (1010,'奥巴马','销售员',1006,'2001-09-08',15000.00,0.00,30);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (1011,'牛头','文员',1008,'2007-05-23',11000.00,NULL,20);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (1012,'风女','文员',1006,'2001-12-03',9500.00,NULL,30);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (1013,'螳螂','分析师',1004,'2001-12-03',30000.00,NULL,20);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (1014,'奶妈','销售员',1007,'2002-01-23',13000.00,NULL,10);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (1015,'花千骨','文员',1001,'2000-12-17',80000.00,NULL,50);
SELECT * FROM EMP;

这一张是员工信息表

CREATE TABLE dept(
    deptno      INT     PRIMARY KEY,
    dname       VARCHAR(50),
    loc         VARCHAR(50)
);

CREATE TABLE salgrade(
    grade       INT     PRIMARY KEY,
    losal       INT,
    hisal       INT
);

INSERT INTO dept VALUES (10, '教研部', '北京');
INSERT INTO dept VALUES (20, '学工部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '武汉');



INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);

SHOW TABLES;
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;

接下来的这两张是部门信息表和工资等级表
在我看来子查询就是将某个查询结果做为另一条查询语句的条件或者另一张表，也就是说将a查询语句放在b查询语句的表名位置或者是条件的位置
下面我会通过几个经典的查询语句来进行说明（可能会涉及多表查询）
1. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
首先对于这种查询我们需要有自己的思路
第一，筛选出员工的直接上级
第二，筛选受雇日期早于直接上级的所有员工的编号，姓名
租后，左外连接部门表完成查询
以下为代码：

select emp.empno,emp.ename,dept.dname 
from emp left join (select ename,empno,hiredate
            from emp) e1
on emp.mgr=e1.empno
left join dept 
on emp.deptno=dept.deptno 
where emp.hiredate<e1.hiredate;

1. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
   第一点需要知道部门表需要全部显示
   第二个，需要知道员工需要与部门表对应
   代码如下

select dept.dname,emp.*
from dept left join emp 
on dept.deptno=emp.deptno;

1. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。

select job,count(*)
from emp
group by job 
having min(sal)>15000;

1. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

select ename 
from emp 
where empno!=10 and empno!=20 and empno!=40;

1. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

SELECT AVG(sal) FROM emp;
SELECT emp.*,dept.`dname`,e2.ename,salgrade.`grade`
FROM emp LEFT JOIN dept 
ON emp.`DEPTNO`=dept.`deptno`
LEFT JOIN (SELECT ename,empno
            FROM emp) e2
ON emp.`MGR`=e2.empno 
LEFT JOIN salgrade
ON sal BETWEEN losal AND hisal
HAVING sal>(SELECT AVG(sal) FROM emp);

6.列出与狮子狗从事相同工作的所有员工及部门名称。

SELECT emp.`ENAME`,dept.`dname` 
FROM emp LEFT JOIN dept
ON emp.`DEPTNO`=dept.`deptno`
WHERE job = (SELECT job 
            FROM emp
            WHERE ename = "狮子狗");

7.列出薪金高于在部门30工作的所有员工的薪金　的员工姓名和薪金、部门名称

SELECT emp.`ENAME`,emp.`SAL`,dept.`dname`
FROM emp LEFT JOIN dept
ON emp.`DEPTNO`=dept.`deptno`
HAVING emp.`SAL`>(SELECT MAX(sal) FROM emp WHERE deptno=30);

下面补充一下，关于子查询和多表查询的一些定义
连接的分类
内连接：
只会找到满足条件的数据，不满足的会丢掉

select
from b表1 inner join 表二
on 条件

外连接
左外连接
左边的表中的数据一个都不能少
select
from 表1 left outer join 表2
on 去除笛卡尔积条件
右外连接
右边的表中的数据一个都不能少
select
from 表1 right outer join 表2
on 去除笛卡尔积条件
全外连接（MySql不支持全外连接）
两边表中的数据一个都不能少。
MySql可以使用合并结果集的方式来模拟出全外连接。

合并结果集
通过合并结果集的方式模拟出其他数据库的·全外连接的效果
合并结果集有两种方式
1.全部合并在一起 不会去重 UNION ALL
2.全部合并在一起 会取出重复的结果 UNION

子查询
我们可以将一个查询当做条件
还可以将查询结果当作一张表

内连接
SELECT *
FROM emp INNER JOIN dept
ON emp.deptno=dept.deptno;
外连接
左外连接
左表的数据一条都不能少
SELECT *
FROM emp LEFT JOIN dept
ON emp.deptno=dept.deptno;
查询出所有员工的姓名和部门名称
SELECT e.ename,IFNULL(d.dname,’无部门’)
FROM emp e LEFT JOIN dept d
ON e.deptno=d.deptno;

ifnull的用处，语法
IFNULL(可能存在null值的列，出现null值时取代它的值)