MySQL学习总结：关联查询

关联查询：当在查询时，我们所需要的数据不在一张表中，可能在两张表或多张表中。此时我们需要同时操作这些表。即关联查询。

数据库中有员工表和部门表：

员工表：

部门表：

1、笛卡儿积

在做多张表查询时，我们使用某一张表中的每一条记录都与另外一张表的所有记录进行组合。比如表A有x条，表B有y条件，最终组合数为x*y，这个值就是笛卡尔积，通常没有意义。

select * from emp,dept;

一共16*4=64条记录

2、等值连接

在做多张表查询时，这些表中应该存在着有关联的两个字段。使用某一张表中的一条记录与另外一张表通过相关联的两个字段进行匹配，组合成一条记录。

显示员工信息以及所属部门信息：

select * from emp e,dept d where e.deptno=d.deptno;

3、内连接

查询效果与等值连接一样，使用了join on。

表A [inner] join 表B on 关联条件

select * from emp e join dept d on e.deptno=d.deptno;

效果与等值连接一样。

4、外连接

在做多张表查询时，我们所需要的数据除了满足关联条件的数据外，还有不满足关联条件的数据。

驱动表(主表)：除了显示满足条件的数据，还需要显示不满足条件的数据的表

从表(副表)：只显示满足关联条件的数据的表

4.1、左外连接

表A left [outer] join 表B on 关联条件

表A是驱动表，表B是从表

select * from emp e left join dept d on e.deptno=d.deptno;

8003号员工部门号为null，左外连接时部门信息全为null

4.2、右外连接

表A left [outer] join 表B on 关联条件。

表A是驱动表，表B是从表

select * from emp e right join dept d on e.deptno=d.deptno;

右外连接时，8003号员工的信息全为null

4.3、全外连接

两张表的数据不管满不满足条件，都做显示。mysql 不支持全外连接。

表A full [outer] join 表B on 关联条件

5、自连接

在多张表进行关联查询时，这些表的表名是同一个。

查询员工编号姓名职位及其领导的编号名称和职位：

select e1.empno,e1.ename,e1.job,e1.mgr,e2.empno,e2.ename,e2.job 
                                           from emp e1,emp e2 where e1.mgr=e2.empno;

查询员工编号，姓名及其下属编号和姓名

select e1.empno,e1.ename,e2.empno,e2.ename 
                          from emp e1 left join emp e2 on e1.empno=e2.mgr;

高级关联查询

有时我们要查询的数据一个简单的查询语句满足不了，并且使用的数据表中不能直观体现出来，而是需要先经过一次查询。那么先执行的查询称为子查询。被子查询嵌入的查询语句称为父查询。

子查询返回的数据特点：

(1) 可能是单行单列的数据。

(2) 可能是多行单列的数据

(3) 可能是单行多列的数据

(4) 可能是多行多列的数据

子查询的位置：

(1)子查询可以在where子句中

(2)子查询可以在from子句中

(3)子查询可以在having子句中

(4)子查询可以在select子句中，相当于外连接的另外一种写法。

1、子查询在where子句中

1.1、查询ford的上司信息

select * from emp where empno=(select mgr from emp where ename='ford');

1.2、查询工资大于jones工资的所有员工姓名，职位，工资

select ename,job,sal from emp where sal>(select sal from emp where ename='jones');

1.3、查询部门'sales'里的所有员工的信息

select * from emp where deptno =(select deptno from dept where dname='sales');

2、子查询在from语句中

2.1、查询员工的工资，姓名，及其部门的平均工资和平均奖金,按照部门号升序

select sal,ename,e2.avgSal,e2.avgComm from emp e1 
    join (select deptno,avg(ifnull(sal,0)) avgSal,
                     avg(ifnull(comm,0)) avgComm from emp group by deptno) e2 
        on e1.deptno=e2.deptno order by e2.deptno; 

2.2、查询部门名称，地址，最高工资，总人数

select dname,loc,maxSal,sumPep from dept d 
    left join (select deptno,max(sal) maxSal,count(*) sumPep 
        from emp group by deptno) e on d.deptno=e.deptno;

3、子查询在having语句中

查询部门平均工资大于30号部门平均工资的

select deptno,avg(ifnull(sal,0)) from emp group by deptno 
        having avg(ifnull(sal,0))>(select avg(ifnull(sal,0)) from emp where deptno=30);

4、子查询在select语句中

查询员工姓名，职位，和其上司姓名，职位

select ename,job,(select ename from emp b where a.mgr=b.empno) mgrName,
    (select job from emp c where a.empno=c.mgr) mgrJob from emp a;