MySQL笔记3

连接查询
1、什么是连接查询？
    从一张表中单独查询称为“单表查询”。
    emp表和dept表联合起来查询数据，从emp表中获取员工的名字，从dept表中获取部门的名字。
    这种跨表查询，多张表联合起来查询数据，被称为连接查询。
2、连接查询的分类：
    根据语法的年代分类：
        SQL92：1992年出现的语法
        SQL99：1999年出现的语法
        我们这重点学习SQL99
    根据表连接的方式分类：
        内连接:
            等值连接
            非等值连接
            自连接
        外连接：
            左外连接（左连接）
            右外连接（右链接）
        全连接
2.1多个表连接查询的时候，我们只在select 后面直接写一个“*”，代表所有表的所有字段，全查出来。
    如果加上“表名.*”，只是代表这张表的“所有字段”。
3、当两种表进行连接查询时，没有任何条件的限制会发生什么现象？
    select ename,dname from emp,dept; [发现查询结果右56条记录]
    emp表中有14条记录，dept表中有4条记录。
    没有任何条件的情况下会发现：
    最终的查询结果集会用emp表中的第一条记录与dept表中的每一条记录进行匹配，最终dept表中有多少条记录就会生成多少条新的记录
    最终的查询结果集会用emp表中的第二条记录与dept表中的每一条记录进行匹配，最终dept表中有多少条记录就会生成多少条新的记录
    ... 
    以此类推，查询结果集最终生成的记录条数 = emp的记录条数 * dept的记录条数
    最终的查询结果集会用emp表中的每一条记录与dept表中的每一条记录进行匹配，生成一条新的记录
    三张表也是一样的道理：以此类推。
4、两种表进行连接查询的时候，没有任何条件限制的时候，最终的查询结果条数，是两种表条数的乘积，
   这种现象被称为：笛卡尔积现象。（笛卡尔发现的，这是一个数学现象。）
5、怎样避免笛卡尔积现象？
    连接时加条件，满足这个条件的记录被筛选出来。
    示例：查询某个员工始于哪个部门的？
    select ename,dname from emp,dept where emp.deptno=dept.deptno;
    emp.deptno:员工表的部门编号
    dept.deptno:部门表的部门编号
    查询结果是使用某条员工的记录去匹配所有部门的记录。
    不管该员工是不是这个部门的，该员工的信息都要与这个部门的这条记录组成一条新的记录。
    如果该员工不是某个部门的，组成的这条记录是没有意义的，所以要加条件“emp.deptno=dept,deptno”。
    当员工表的部门编号和部门表的部门编号相同时,说明员工就是这个部门的，此时这条记录才有意义，才保留此条记录。
6、只要有表的连接肯定会有“笛卡尔积”。通过where加了条件限制之后，虽然查询出来的记录数减少了，
    但是最终匹配的次数是不会减少的，只是匹配出来的结果集被筛选条件过滤了。
    如果通过on加的条件就会在形成临时表之前使用过滤条件，避免了生成一笛卡尔积临时表，提高了效率。
    例如：select ename from emp,dept;
    虽然没有用到dept表中的字段，但是只要写到“... from emp,dept”的表都会连接形成笛卡尔积。
7、    select ename,dname from emp,dept where emp.deptno=dept.deptno;【效率低，因为ename，和dname没有指定是在哪个表中的字段，MySQL需要去两种表或者多张表中找】
    select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;【效率高】
8、select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;
    表也可以起别名：
   select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;【as可以省略】
   字段起了别名之后：可以使用原名也可以使用别名。
   但是表起了别名之后就只能使用别名了，原名就失效了。
   mysql> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
    +--------+------------+
    | ename  | dname      |
    +--------+------------+
    这种语法查询之后字段名就只是，ename、dname了前面的e和d都省略了。
9、降低连接的次数
    通过笛卡尔积现象，得出表的连接次数越多效率越低，尽量减少表的连接次数。【可以使用on加条件，在形成临时表的时候使用的条件】

内连接之等值连接
案例：查询每个员工坐在的部门名称，显示员工名和部门名。
    【SQL92语法】
    select 
        e.ename,d.dname 
    from 
        emp e,dept d 
    where 
        e.deptno=d.deptno;【因为条件是等值关系所以被称为“等值连接”】
    【SQL99语法】:把92语法中多个表之间的逗号换成了join...，
                  把92语法中的where换成了on
    select
        e.ename,d.dname 
    from 
        emp e
    inner join 【inner 可以省略不写，默认就是内(inner)连接,可以写多个join】
        dept d 
    on 
        e.deptno=d.deptno;【可以只写inner join，省略后面的连接条件，不过这样做没有意义】
    
    在92语法中，表连接的条件和后期连接完过滤的条件，糅杂在一起，都写在where语句中。【结构不清晰】
    在99语法中，表连接的条件和后期连接完过滤的条件分离，表连接的条件写在on后面，连接完后过滤的条件写在where之后。【结构清晰】
10、还可以表连接的时候使用join，但是写连接条件的时候使用where。
    还可以在一条SQL中表连接同时使用join和逗号“，”
    表连接的过程中只要有逗号的存在那么连接条件必须得写在where后面，
    所有的表连接都使用join的时候，表的连接条件才可以写在on中。

内连接之非等值连接
示例：找出每个员工的薪资等级，要求显示员工名、薪资、工作位置、薪资等级：
        select 
            ename,loc,sal,grade 
        from 
            emp 
        join         【省略默认是inner内连接】
            dept 
        join         【省略默认是inner内连接】
            salgrade 
        on 
            emp.deptno=dept.deptno 
        and 
            (sal between losal and hisal);【条件不是一个等量关系称为非等值连接】            

内连接之自连接
1、查询员工的上级领导，要求显示员工名和对应的领导名？
   select e1.ename,e2.ename from emp e1 join emp e2 on e1.mgr=e2.empno;
   e1表：左边的是员工表的信息，e1.ename是员工的姓名
   e2表：右边是领导表的信息，e2.name是员工的领导的姓名
   连接之后形成了笛卡尔积，通过“员工的领导的编号”=“当领导的员工的编号”过滤【员工的领导编号=领导的员工编号】
    【自连接必须定义表的别名，因为多张表连接查询的时候，表名必须唯一，
    表一旦定义了别名就只能使用别名访问表中的字段了，所以就形成了唯一的表名】
2、自连接的技巧，一张表看成两张或多张表。
    
外连接
1、示例：查询员工的姓名和员工的所属部门，如果这个部门没有员工就显示null。
    select 
        ename,dname 
    from 
        emp  
    right outer join   【这里的outer可以省略不写，但是rigth或者left不能省略】
        dept 
    on 
        emp.deptno=dept.deptno;
        
2、直接写join等价于inner join  【内连接】
   直接写left join 等价于 left outer join 【左外连接】
   直接写right join 等价于 right outer join 【右外连接】
3、inner join可以单独书写，不带连接条件on。【默认就是连接成一个笛卡尔积】
   outer join(左、右)不能单独书写，不带连接条件on是错误的。
4、同一条SQL语句中可以即存在内连接又存在外连接。
5、内连接两种表是平等的关系：
        能匹配上就显示，不能匹配上就不显示。
   外连接表之间产生了主次关系：
        左连接左边的是主表，右边的是副表。
        右连接右边的是主表，左边的是副表。
   主表中的数据会全部显示出来，能满足某个条件的话，关联着把副表的记录也查出来。
6、使用副表中的第一条记录匹配主表中的所有记录，符合规则就查出来
   使用副表中的第二条记录匹配主表中的所有记录，符合规则就查出来
   ....以此类推，查询结果集中副表中的记录是按规则排列的。
   如果副表中的记录都已经遍历比较完了，之后主表中还有未显示的记录
   那么主表的记录显示出来，与之同一条记录的副表中的该字段处显示“null”。
   【这是MySQL5.7的规则】
   使用主表中的第一条记录，与副表中的每一条记录，符合规则的就查出来
   使用主表中的第二条记录，与副表中的每一条记录，符合规则的就查出来
   ...以此类推，查询结果集中主表的记录是按照规怎排列的。
   如果在遍历的过程中副表中没有与主表中该记录对应的行，
   那么主表本行单独生成一行记录，副表的位置自动填充null
   主表遍历比较完了，主表的记录也就全部显示了。
   【这是MySQL5.5的规则】
7、任何一个右连接都有左连接的写法。
   任何一个左连接都有右连接的写法。
8、示例：查询每个员工的上级领导，要求显示所有员工的名字和领导名?
    select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno;
9、示例：找出每个员工的部门名称以及工资等级，
    要求显示员工名、部门名、薪资、薪资等级？
    第一种方式：
        select     
            dname,ename,sal,grade
        from 
            emp 
        left join 
            dept 
        on 
            emp.deptno=dept.deptno
        left join 
            salgrade 
        on 
            (sal between losal and hisal);
        【语法结构】
        select
            ...
        from 
            ...
        letf join
            ...
        on
            ....
        letf join
            ...
        on
            ....
    第二种方式：
        select 
            ename,dname,sal,grade
        from 
            emp 
        join 
            dept 
        join 
            salgrade
        on 
            emp.deptno=dept.deptno 
        and 
            (sal between losal and hisal);
            
        【语法结构】
        select
            ...
        from 
            ...
        join
            ...
        join
            ...
        on
            ...
        and
            ...
10、示例：找出每个员工的部门名称以及工资等级，还有上级领导。
    要求显示员工名、领导名、部门名、薪资、薪资等级。
    select
        e1.ename,e2.ename,dname,e1.sal,grade
    from
        emp e1 
    left join 
        emp e2
    on
        e1.mgr=e2.empno
    left join 
        dept 
    on 
        e1.deptno=dept.deptno
    left join 
        salgrade 
    on 
        e1.sal between losal and hisal;
        
    【或者,内连接和外连接组合使用】
    select
        e1.ename '员工名',e2.ename '领导名',dname,e1.sal,grade
    from
        emp e1 
    left join    【在这里仅仅保留左边表中全部的数据】
        emp e2
    on
        e1.mgr=e2.empno
    join 
        dept 
    on 
        e1.deptno=dept.deptno
    join 
        salgrade 
    on 
        e1.sal between losal and hisal;
11、示例：每个领导下面各管理了多少个员工？没有管理员工的员工也显示出来。
    e1是员工表
    e2是领导表
    select 
        e1.ename,case ifnull(e2.ename,0) when '0' then '0' else count(*) end '管理的员工数量'
    from 
        emp e1 
    left join 
        emp e2
    on 
        e1.empno=e2.mgr
    group by
        e1.ename;
        
子查询
1、什么是子查询？
    select语句中嵌套select语句，被嵌套的select语句被称为子查询。
2、子查询都可以出现在哪里？
    select
        ...(select). 【可以出现在select语句后面，这里出现的子查询一次只能返回一条语句，多于一条就报错】
    from 
        ...(select). 【可以出现在from语句后】
    where
        ...(select). 【可以出现在where语句后，in里面】
3、where子句中的子查询：
    案例：查询比最低工资高的员工的姓名和工资：
        第一步：查询最低工资：
            mysql> select min(sal) from emp;
            +----------+
            | min(sal) |
            +----------+
            |   800.00 |
            +----------+
        第二步：找出大于800的：
            mysql> select ename,sal from emp where sal > 800;
        第三步：合并
            mysql> select ename,sal from emp where sal > (select min(sal) from emp);
            这条语句的执行顺序：先执行“(select min(sal) from emp)”在执行外层的查询
4、from子句中的子查询
    注意：from后面的子查询，可以将子查询的查询结果当作一张临时表。（技巧）
    案例：找出每个岗位的平均工资的薪资等级。
    select 
        t.*,s.grade
    from
        (select job,avg(sal) as avgsal from emp group by job) t【avg(sal)需要起一个别名，因为后续使用该字段的时候不能“t.avg(sal)”，才可以在后续的连接条件用，整个表起的别名叫做t，方便后续使用】
    join
        salgrade s
    on
        t.avgsal between s.losal and s.hisal;  【其中的“t.avgsal”可以省略t，直接写“avgsal”】

union
1、union用于合并查询结果集。
2、案例：查询工作岗位是manager或者salesman的员工：
    方式一：mysql> select ename,job from emp where job='manager' or job='salesman';
            mysql> select ename,job from emp where job in('manager','salesman');
    方式二：使用union方式合并查询结果集：
        select ename,job from emp where job = 'manager'
        union 
        select ename,job from emp where job='salesman';
    使用union的效率高一些。对于表连接来说，每连接一次新表，
    则匹配的此时满足笛卡尔积，成倍翻...
    union可以减少匹配的次数，减少匹配次数的情况下还能完成两个结果集的拼接。
3、注意：union在进行结果集的拼接的时候要求两个结果集的列数相同。
    //以下的SQL语句是错误的
    select ename,job from emp where job = 'manager'
    union 
    select ename from emp where job='salesman';
4、注意：union在进行结果集的拼接的时候上下只要列数相同就可以，
    字段名，甚至类型都可以不同，mysql都可以帮助我们拼接到一起。
    【拼接之后结果集的列名是 ename,job 以上面的为准】
    select ename,job from emp where job = 'manager'
    union 
    select ename,sal from emp where job='salesman';
    
limit
1、limit作用：将查询结果集的一部分取出来，通常使用在分页查询当中    
2、limit使用的语法格式：limit startIndex, length 【从第startIndex条开始输出length条记录，第一条记录的下标是0】
    案例：按照降序排序，去除排名在前5名的员工？
      mysql> select * from emp order by sal desc limit 5;【省略startIndex，默认startIndex就是0】
      mysql> select * from emp order by sal desc limit 0, 5;【如果到末尾也凑不够5条，那么凑了几条就输出几条】
3、注意：在MySQL中limit在order by之后执行。
通用分页公式：limit (pageNo-1)*pageSize,pageSize

SQL大总结
1、
select
    ...
from
    ...
where
    ...
group by
    ...
having
    ...
order by
    ...
limit
    ...
执行顺序：
    from
    where
    group by
    having
    select
    order by
    limit

    

【重点：条件子句】 
on、where、having三个都可以加条件子句中：
1.on最先执行，where次之、having最后执行。
2.因为on是先把不符合条件的记录过滤后才进行统计，它可以减少中间运算要处理的数据（但是匹配的次数一次都没有少），按理说应该速度最快。
3.on语句的执行实在join语句之前的，也就是说两张表数据行之间进行匹配的时候，会先判断数据行是否符合on语句后面的条件，再决定是否join。
4.先执行on，后执行where，on是关联关系，where是对关联关系的筛选。

具体什么时候使用内连接什么时候使用外连接呢？只需要由外键区分即可：
如果外键不能为空，优先使用内连接；
如果外键可以为空：
假如只需要查询那些在另一张表中有相对应的记录，使用内连接；
假如需要查询左（右）侧表中所有符合条件的记录，使用左（右）外连接；