Mysql连接,多表,子连接查询,子查询

1. 连接查询
                          内     连接
    select ... from 表1 inner join 表2 on 连接条件 WHERE ... group by ... HAVING ... ORDER BY ... limit ... (重点)
    
                          左    外  
    select ... from 表1 left [outer] join 表2 on 连接条件 (重点)
    
                          右    外
    select ... from 表1 right [outer] join 表2 on 连接条件
    
                          全
    select ... from 表1 full join 表2 on 连接条件 (mysql不支持全连接)
   

    例：连接部门表和员工表
    select * from emp a inner join dept b on a.deptno=b.deptno;

    注意：
    1) 内连接是将两张表中所有符合连接条件的数据列入结果，不符合连接条件的结果中没有，例如40号部门
    2) 如果连接的两表中有同名的列，列前面要加表名(或表别名)来区分（否则会报歧义错误）
    3) inner join ... on 的写法是符合SQL-92标准写法,其实还有一种内连接的写法：        
        select ... from 表1, 表2 where 连接条件;        
    例： 还是连接部门表和员工表  

select * from emp a, dept b where a.deptno=b.deptno; 

    连接学生表成绩表
    课程表连接老师表
    课程表连接成绩表    
        
    例子：采用左外连接员工表和部门表

    select * from emp a left join dept b on a.deptno = b.deptno;

     例子：采用左外连接部门表和员工表
  

 select * from dept b left join emp a on a.deptno = b.deptno;

左外连接，首先将符合连接条件的记录连在一起，作为结果，其次左边表中不符合连接条件的记录也会出现在结果中，只不过它对应的右边的列都是NULL
    
    例子：右外连接
    

select * from emp a right join dept b on a.deptno = b.deptno;

    注意：左外和右外与表的先后次序有关，而内连接与表的先后次序无关
    
2. 多表连接
    select * from 表1 
        inner join 表2 on 连接条件
        inner join 表3 on 连接条件
        ...
        
    例子

    select * from student a inner join sc b on a.sid = b.sid
                             inner join course c on b.cid = c.cid
                             inner join teacher d on c.tid = d.tid
                             order by a.sid, b.cid;

  
    等价写法：
    

select * from student a, sc b, course c, teacher d
                 where a.sid=b.sid and b.cid=c.cid and c.tid=d.tid;

    
    左外多表连接：
    

select * from student a left join sc b on a.sid = b.sid
                             left join course c on b.cid = c.cid
                             left join teacher d on c.tid = d.tid
                             order by a.sid, b.cid;

    注意：左外多表连接要全部使用left join，不能再出现inner join;
    性能上：连接的表越多，性能越低, 可以把连接查询变成分多次查询
    
3. 自连接
    一个表自己和自己连接
    找到员工的姓名和上级的姓名

 select a.empno,a.ename,a.mgr,b.empno,b.ename,b.mgr
     from emp a left join emp b on a.mgr=b.empno;

4. 子查询
    把某个select结果当做一个值，或一张表做进一步的查询

    情况1：找具有最高工资的员工信息（子查询作为值）

select max(sal) from emp; // 5000

select * from emp where sal = (select max(sal) from emp);

把select max(sal) from emp当成了一个值，代入到主查询当中，代入时需要在子查询的两边加()

情况2： 获取每个部门的平均工资和部门的名称（子查询作为表）

先查询平均工资
(select deptno,avg(sal) from emp group by deptno) a

再把子查询看做临时表，与其它表做表连接
select * from (子查询)a inner join dept b on a.deptno=b.deptno;

情况3： 将子查询当做一个函数 (了解)

(select max(sal) from emp where deptno =?)

m(deptno)   返回结果是这个deptno下的最大工资

部门编号是一个入参, 最大工资是返回结果

select * from emp e where sal = m(e.deptno);  // 伪代码

select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno);