mysql数据库：连接查询\子查询\union\limit\DQL语句总结 （老杜）

目录

一、连接查询

    1.1定义：联合两张及以上的跨表查询数据

    1.2分类：

    1.3两张表的连接：

    1.4内连接

    1.5外连接  

二、子查询

    2.1定义：select语句中嵌套select语句，被嵌套的select语句被称为子查询。

    2.2安放位置：

    2.3where子句中的子查询（掌握）

    2.4from子句中的子查询（掌握）

    2.5select子句中的子查询（了解）        

三、union集合的合并与相加

四、limit查询结果的部分取出        

五、DQL语句总结    

---

一、连接查询

    1.1定义：联合两张及以上的跨表查询数据

    1.2分类：

        1.2.1按照年代：
            sql92：1992年出现的语法
            sql99：1999年出现的语法//重点学习项目

        1.2.2根据表连接：

            1）内连接：
                等值连接
                非等值连接
                自连接

            2）外连接：
                左外连接（左连接）
                右外连接（右连接）

            3）全连接： 

    1.3两张表的连接：

        select ename,dname from emp,dept//在from 后面添加，表示此时数据来自两张表

        1.3.1笛卡尔积现象：
            表1数据条数*表2数据条数=生成新表数据条数//此种条件被称为笛卡尔积现象（笛卡尔发现的数学现象）

        1.3.2避免笛卡尔积：
            操作：连接时添加条件，满足条件的记录被筛选出来；

                案例：查询所有员工的部门名称；
            select
                ename,dname
            from
                emp,dept
            where
                emp.deptno=dept.deptno;//类似类别选择器
            结论：最终查询结果符和常规14条，但匹配次数未减少，依旧是56此，但进行了4选1；
            mysql> select ename,dname from emp,dept where emp.deptno =dept.deptno;
                +--------+------------+
                | ename  | dname      |
                +--------+------------+
                | SMITH  | RESEARCH   |
                | ALLEN  | SALES      |
                | WARD   | SALES      |
                | JONES  | RESEARCH   |
                | MARTIN | SALES      |
                | BLAKE  | SALES      |
                | CLARK  | ACCOUNTING |
                | SCOTT  | RESEARCH   |
                | KING   | ACCOUNTING |
                | TURNER | SALES      |
                | ADAMS  | RESEARCH   |
                | JAMES  | SALES      |
                | FORD   | RESEARCH   |
                | MILLER | ACCOUNTING |
                +--------+------------+

        1.3.3提高表的效率

            1）针对表起别名：from emp e,dept d
            起表名后的使用：where e.deptno = d.deptno;//sql92语法。
            
            2）减少表的连接次数
            注释：连接表数量越多，表的效率降低
            
            3）select 查询阶段指明字段出处：select emp.ename,dept.deptno;
            注释：系统不清楚字段出自哪张表，会在引用的每张表查询，从而降低效率
            ↓↓↓↓↓完整写法↓↓↓↓↓↓
            select
                e.name,d.dept-------------->(3)
            from
                emp e,dept d--------------->(1)
            where
                e.deptno =d.deptno;------->(2)//sql92语法。
            注释：由于函数关键字的执行顺序可得；

    1.4内连接

        定义：查询两张表的满足某条件的行，两张表无主次关系。
        1.4.1表的等值连接：
            案例：查询每个员工所在部门名称，显示员工名和部门名：
            连接条件：两张表的deptno部门编号为公有部分。
            我的思路：（）
            mysql> select e.ename , d.dname from emp e, dept d where e.deptno = d.deptno;//sql92（几乎不用）
            错误原因：deptno√----depton×
            sql99语法：
            select 表1.a,表2.b from 表1 as 名1 (inner) join表2 as 名2 on 名1.相同字段=名2.相同字段；
            mysql> select
                    e.ename,d.dname 
                    from 
                    emp as e 
                    join 
                    dept as d 
                    on 
                    e.deptno= d.deptno;
            注释：inner join on的sql99结构更清晰的区分表的连接条件与表的where限制部分;
                 inner部分可以省略，好处可直观辨别结构为内连接，提高可读性；
                +--------+------------+
                | ename  | dname      |
                +--------+------------+
                | SMITH  | RESEARCH   |
                | ALLEN  | SALES      |
                | WARD   | SALES      |
                | JONES  | RESEARCH   |
                | MARTIN | SALES      |
                | BLAKE  | SALES      |
                | CLARK  | ACCOUNTING |
                | SCOTT  | RESEARCH   |
                | KING   | ACCOUNTING |
                | TURNER | SALES      |
                | ADAMS  | RESEARCH   |
                | JAMES  | SALES      |
                | FORD   | RESEARCH   |
                | MILLER | ACCOUNTING |
                +--------+------------+

        1.4.2表的非等值连接：
            案例：查找每个员工的薪资等级，要求显示员工名、薪资、薪资等级；
            select e.ename,s.sal,s.grade from emp as e join salgrade as s on  ???
            修改：on e.sal between s.losal and s.hisal;
            筛选条件：筛选出这两张表的条件是薪资需要在工资losal与hisal之间；
            注释：筛选条件不是一个等量关系，称为非等值连接；
            select 
                e.ename,e.sal,s.grade
            from
                emp e
            (inner) join
                salgrade s
            on
                e.sal beteween s.losal and s.hisal;

        1.4.3表的自连接：
            案例：查询员工的上级领导，要求显示员工名和对应的领导名；
            思路me：员工--上级编号--上级名称；
            select ename,empno from emp where 
            思路du:
                一张表看作两张表 emp as a,emp as b;
                a表的员工编号=b表的上级编号；
                将a.ename,b.ename重命名；

            mysql> select
                    -> a.ename as '员工名',b.ename as '领导名'
                    -> from
                    -> emp as a
                    -> inner join
                    -> emp as b
                    -> on a.empno = b.mgr;
                +--------+--------+
                | 员工名 | 领导名 |
                +--------+--------+
                | FORD   | SMITH  |
                | BLAKE  | ALLEN  |
                | BLAKE  | WARD   |
                | KING   | JONES  |
                | BLAKE  | MARTIN |
                | KING   | BLAKE  |
                | KING   | CLARK  |
                | JONES  | SCOTT  |
                | BLAKE  | TURNER |
                | SCOTT  | ADAMS  |
                | BLAKE  | JAMES  |
                | JONES  | FORD   |
                | CLARK  | MILLER |
                +--------+--------+
            注释：员工名和领导名反了
            原因：a表取得参数是员工，b表取得参数是领导；自然需要a表的领导a.mgr=b表的员工。
            注释：只有13条记录，king没有上级

        1.5外连接  

             1.5.1定义：除开查询出满足条件的，还要显示出某表条件之外（右/左边）部分；
            语法：select 
                    字段 as ''，字段 as ''
                from
                    表1
                (left/right) outer join
                    表2
                on
                    条件；
            案例：查询所有员工（包括king）的领导名，并显示出来；
        
        1.5.2左外连接（左连接）
            注释：左边是主表，主表显示数据完全；
            select
                a.ename as '员工名',b.ename as '领导名'
            from
                emp a
            left join
                emp b
            on
                a.mgr = b.empno;
            
        1.5.3右外连接（右连接）
            注释：右边是主表，主表显示数据完全；
            select 
                a.ename '员工姓名',b.ename '领导姓名';
            from
                emp b
            right join 
                emp a
            on
                a.mar = b.empno;

        1.5.4三（多）张表连接
            1）语法：
                select
                    字段1....
                from
                    a表
                join
                    b表
                on
                    a,b连接条件
                join
                    c表
                on
                    a,c连接条件
            注释：
                一条sql中内连接外连接可以混合使用；
            
            2）案例1：找出每个员工的部门名称以及工资等级，要求显示：员工名、部门名、薪资、薪资等级；
                select 
                    e.ename,d.dname,e.sal,s.grade            
                from
                    emp e
                join 
                    dept d
                on
                    e.deptno = d.deptno
                left join
                    salgrade s
                on
                    e.sal between s.losal and s.hisal;
            理解：a与b连接后a与c连接；a与b连接后的结果与c连接;
            
            3）案例2：查询每个员工的部门名以及工资等级，上级领导；要求显示员工名、领导名、部门名、薪资、薪资等级；
                select
                    a.ename '员工名',b.ename'领导名',d.dname,a.sal,s.grade
                from
                    emp as a
                left join
                    emp as b
                on
                    a.mgr = b.empno
                join
                    dept d
                on
                    a.deptno =d.deptno
                left join
                    salgrade s
                on
                    a.sal between s.losal and s.hisal;
            注释：易错点包括表的命名忽略在select部分修改；
                 只有13条记录，改1连第二张表left主表，改2暂时不将自连部分放在前两节但自连部分仍须外连；
                select
                    a.ename '员工名',b.ename'领导名',d.dname,a.sal,s.grade
                from
                    emp as a
                join
                    dept d
                on
                    a.deptno =d.deptno
                left join
                    emp as b
                on
                    a.mgr = b.empno
                join
                    salgrade s
                on
                    a.sal between s.losal and s.hisal;

二、子查询

    2.1定义：select语句中嵌套select语句，被嵌套的select语句被称为子查询。

    2.2安放位置：

        select
            ...(select)
        from
            ...(select)
        where
            ...(select)

    2.3where子句中的子查询（掌握）

        2.3.1案例：查询比最低工资高的员工姓名和工资
            步骤1：查询最低的员工工资数；
                select min(sal)
                from emp;//800
            步骤2：查询大于最低工资的员工；
                select ename,sal
                from emp
                where sal > 800;
            步骤3：合并；
                select 
                    ename,sal
                from
                    emp
                where
                    sal >(select min(sal)from emp);

    2.4from子句中的子查询（掌握）

        2.4.1案例：查找每个岗位平均工资的薪资等级
            我的思路：
                select 
                    avg(sal),s.grade,e.job
                from
                    emp e
                join
                    salgrade s
                on
                    e.sal between s.losal and s.hisal
                group by 
                    e.job；
            错误：between and之间并非e.sal而是avg(sal)

            步骤1：查找每个岗位的平均工资
                select
                    avg(sal),job
                from
                    emp
                group by
                    job;
                +-------------+-----------+
                | avg(sal)    | job       |
                +-------------+-----------+
                | 1037.500000 | CLERK     |
                | 1400.000000 | SALESMAN  |
                | 2758.333333 | MANAGER   |
                | 3000.000000 | ANALYST   |
                | 5000.000000 | PRESIDENT |
                +-------------+-----------+
            步骤2：查出数据看作新表t，使用t中的参数avg(sal)
                   联立t表与s表的
                select
                    t.*
                from
                    t
                join
                    salgrade s
                on
                    t.avg(sal) between s.losal and s.hisal
                group by
                    job;
            步骤3：t表本身不存在，将步骤1换得到from--t中，并命名为t；
                select
                    t.*,s.grade
                from
                    (select avg(sal),job from emp group by job) t
                join
                    salgrade s
                on
                    t.avg(sal) between s.losal and s.hisal;
            注释：t.avg(sal)整个被系统判定为一种函数，而该种函数不存在，需要给avg(sal)重新命名来解决此问题；
            步骤4：avg(sal)命名为avgsal;
                select
                    t.*,s.grade
                from
                    (select avg(sal) avgsal,job from emp group by job) t
                join
                    salgrade s
                on
                    t.avgsal between s.losal and s.hisal;
                +-------------+-----------+-------+
                | avgsal      | job       | grade |
                +-------------+-----------+-------+
                | 1037.500000 | CLERK     |     1 |
                | 1400.000000 | SALESMAN  |     2 |
                | 2758.333333 | MANAGER   |     4 |
                | 3000.000000 | ANALYST   |     4 |
                | 5000.000000 | PRESIDENT |     5 |
                +-------------+-----------+-------+
            总结：将表的查询结果当作临时使用的表

    2.5select子句中的子查询（了解）        

        2.5.1案例：查询每个员工的部门名称，显示员工名，部门名
                1）select法：
                    select
                        e.ename,(select d.dname from dept d where e.deptno = d.depton) as dname
                    from
                        emp e;
                    使用条件：
                   括号内查询出的信息条数需=e.ename的条数，否则无法使用，因此此处添加了限制条件；

                2）内连接法：
                    select
                        e.ename,d.dname
                    from
                        emp e
                    join
                        dept d
                    on
                        e.deptno = d.deptno;
                总结：select 法省略了两表的连接，提高效率;不会报错的原因，执行顺序外层的from优先于select;
        

三、union集合的合并与相加

    3.1案例：查询工作岗位是manager和salesman的员工；
        思路1：select ename,job from emp where job='manager' or job='salesman';
        思路2：select ename,job from emp where job in('manager'.'salesman');
        思路3：select ename,job from emp where job='manager'
               union
               select ename,job from emp where job='salesman';
        注释：思路3效率高，减少表连接的次数，避免笛卡尔积现象的产生，完成两个结果积的拼接；
    
    3.2优势体现案例：
        1）表连接：
            a连接b连接c10*10*10=1000
        2）union连接：
            a连接b---->10*10=100
            a连接c---->10*10=100
            100+100=200

    3.3使用注意：
        1）结果合并的列数相同；
            select ename,job from emp where job ='manager'
            union
            select ename from emp where job='saleman';

        2）oracle中语法严格甚于mysql，要求合并列的数据类型相同；
            如：varchar或float或double或int；

四、limit查询结果的部分取出        

    4.1定义：查询结果集的部分取出，通常使用在分页查询；

    4.2作用：提高用户体验，翻页查看；

    4.3语法：
        select
            字段
        from
            表名
        order by 
            字段（desc/asc）
        limit  起始位置，长度 / 长度 
        注释:limit开始取下标0开始，基础格式是两个数字，一个数字默认从0开始;
            mysql中limit的执行顺序排在order by 之后。
            
    4.4案例：
        1）取出工资排名前5员工
            select ename,sal
            from emp
            order by sal desc
            limit 5;

        2）取出工资排名3-5的员工（包含3）
            select ename,sal
            from emp
            order by sal desc
            limit 2,3;

    4.5分页：
        1）参数：
            每页记录数：pagesize条；
            页数：第PageNo页；
            limit pagesize(pageNo-1) ,pagesize;
        2)java的应用：
            暂时不会；

五、DQL语句总结    

        5.1语法：
                select
                from
                join
                on
                where
                group by
                having
                order by
                limit
            5.2执行顺序：
                from
                where
                group by
                having
                select
                order by
                limit