SQL语句常用命令整理——多表查询

清疚 2022-05-08 15:44 368阅读 0赞

### 多表查询之关连查询 ###

多表数据连接查询，简称连接查询。本篇我们来一同学习多表连接查询的相关用法，主要內容有：

1.  内连接查询
2.  外连接查詢
3.  查询结果合并
4.  子查询

首先第一张表还是我们之前用到t\_employee，我们在另外新建一个表t\_dept（部门表）建表命令如下：

drop table if exists t_dept;
    create table t_dept(
        t_id int primary key,
        deptno int(11),
        dname varchar(20),
        loc varchar(50));

插入数据：

insert into t_dept
    (t_id,deptno,dname,loc)
    values
    ('1','10','ACCOUNTING','NEW YORK'),
    ('2','20','RESEARCH','DALLAS'),
    ('3','30','SALES','CHICAGO'),
    ('4','40','OPERATIONS','BOSTON');

### 笛卡尔积 ###

select * from t_employee,t_dept;

*  结果如下：
    
    <table> 
     <tbody> 
      <tr> 
       <td><strong>empno</strong></td> 
       <td><strong>ename</strong></td> 
       <td><strong>job</strong></td> 
       <td><strong>mgr</strong></td> 
       <td><strong>hiredate</strong></td> 
       <td><strong>sal</strong></td> 
       <td><strong>comm</strong></td> 
       <td><strong>deptno</strong></td> 
       <td><strong>t_id</strong></td> 
       <td><strong>deptno</strong></td> 
       <td><strong>dname</strong></td> 
       <td><strong>loc</strong></td> 
      </tr> 
      <tr> 
       <td>7369</td> 
       <td>SMITH</td> 
       <td>CLERK</td> 
       <td>7902</td> 
       <td>2011-03-12</td> 
       <td>800</td> 
       <td>null</td> 
       <td>20</td> 
       <td>1</td> 
       <td>10</td> 
       <td>ACCOUNTING</td> 
       <td>NEW YORK</td> 
      </tr> 
      <tr> 
       <td>7369</td> 
       <td>SMITH</td> 
       <td>CLERK</td> 
       <td>7902</td> 
       <td>2011-03-12</td> 
       <td>800</td> 
       <td>null</td> 
       <td>20</td> 
       <td>2</td> 
       <td>20</td> 
       <td>RESEARCH</td> 
       <td>DALLAS</td> 
      </tr> 
      <tr> 
       <td>7369</td> 
       <td>SMITH</td> 
       <td>CLERK</td> 
       <td>7902</td> 
       <td>2011-03-12</td> 
       <td>800</td> 
       <td>null</td> 
       <td>20</td> 
       <td>3</td> 
       <td>30</td> 
       <td>SALES</td> 
       <td>CHICAGO</td> 
      </tr> 
      <tr> 
       <td>7369</td> 
       <td>SMITH</td> 
       <td>CLERK</td> 
       <td>7902</td> 
       <td>2011-03-12</td> 
       <td>800</td> 
       <td>null</td> 
       <td>20</td> 
       <td>4</td> 
       <td>40</td> 
       <td>OPERATIONS</td> 
       <td>BOSTON</td> 
      </tr> 
      <tr> 
       <td>7499</td> 
       <td>ALLEN</td> 
       <td>SALESMAN</td> 
       <td>7698</td> 
       <td>2012-03-12</td> 
       <td>1600</td> 
       <td>300</td> 
       <td>30</td> 
       <td>1</td> 
       <td>10</td> 
       <td>ACCOUNTING</td> 
       <td>NEW YORK</td> 
      </tr> 
      <tr> 
       <td>7499</td> 
       <td>ALLEN</td> 
       <td>SALESMAN</td> 
       <td>7698</td> 
       <td>2012-03-12</td> 
       <td>1600</td> 
       <td>300</td> 
       <td>30</td> 
       <td>2</td> 
       <td>20</td> 
       <td>RESEARCH</td> 
       <td>DALLAS</td> 
      </tr> 
      <tr> 
       <td>7499</td> 
       <td>ALLEN</td> 
       <td>SALESMAN</td> 
       <td>7698</td> 
       <td>2012-03-12</td> 
       <td>1600</td> 
       <td>300</td> 
       <td>30</td> 
       <td>3</td> 
       <td>30</td> 
       <td>SALES</td> 
       <td>CHICAGO</td> 
      </tr> 
      <tr> 
       <td>7499</td> 
       <td>ALLEN</td> 
       <td>SALESMAN</td> 
       <td>7698</td> 
       <td>2012-03-12</td> 
       <td>1600</td> 
       <td>300</td> 
       <td>30</td> 
       <td>4</td> 
       <td>40</td> 
       <td>OPERATIONS</td> 
       <td>BOSTON</td> 
      </tr> 
     </tbody> 
    </table>
    
    从结果可以看出，两张表中的每条记录都进行了重组，导致最终查询结果的条数为两张表中的记录数的乘积，这样的结果积在数学上称为笛卡尔积。简单比喻下就是所有可能的组合都进行了组合。
    
    ### 内连接（INNER JOIN） ###
    
    使用比较运算符（包括=、>、<、<>、>=、<=、!>和!<）进行表间的比较操作，查询与连接条件相匹配的数据。
 *  特殊：自连接：查询每个员工的姓名、职位和领导姓名  
    方法1：
    
        select t1.ename,t1.job,t2.ename 
        from
              t_employee t1,
              t_employee t2 
        where
                t1.mgr = t2.empno;
    
    方法2：
    
        select t1.ename,t1.job,t2.ename
        from t_employee t1
        inner join t_employee t2 on t1.mgr = t2.empno;
    
     
 *  查询每个员工的编号、姓名、职位、部门名称和部门所在位置
    
        select t1.empno,t1.ename,t1.job,t2.dname,t2.loc
        from t_employee t1
        inner join t_dept t2 on t1. deptno = t2.deptno;
        ---------------------------------- or ---------------------------------------------
        select  t1.empno,t1.ename,t1.job,t2.dname,t2.loc
        from t_employee t1,t_dept t2
        where t1.deptno = t2.deptno;
    
     
 *  查询每个员工的编号、姓名、基本工资、职位、领导姓名、部门名称和部门所在位置
    
        select t1.empno,t1.ename,t1.sal,t1.job,t2.ename as mgr_name,t3.dname,t3.loc
        from t_employee t1
        inner join t_employee t2 on t1.mgr = t2.empno
        inner join t_dept t3 on t1.deptno = t3.deptno;
        ---------------------------------- or ---------------------------------------------
        select t1.empno,t1.ename,t1.sal,t1.job,t2.ename as mgr_name,t3.dname,t3.loc
        from t_employee t1,t_employee t2,t_dept t3
        where t1.mgr = t2.empno and t1.deptno = t3.deptno;
    
    ### 外连接 ###
    
    外连接分为左连接（LEFT JOIN）或左外连接（LEFT OUTER JOIN）、右连接（RIGHT JOIN）或右外连接（RIGHT OUTER JOIN）、全连接（FULL JOIN）或全外连接（FULL OUTER JOIN）。我们就简单的叫：左连接、右连接和全连接。
    
    左外连接(left join)
    
    返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值。
 *  查询每个员工的姓名、职位和领导姓名
    
        select t1.ename,t1.job,t2.ename as mgr_name
        from t_employee t1
        left join t_employee t2 on t1.mgr = t2.empno;
    
    结果：
    
    <table style="width:500px;"> 
     <tbody> 
      <tr> 
       <th>ename</th> 
       <th>job</th> 
       <th>mgr_name</th> 
      </tr> 
      <tr> 
       <td>SCOTT</td> 
       <td>ANALYST</td> 
       <td>JONES</td> 
      </tr> 
      <tr> 
       <td>KING</td> 
       <td>PRESIDENT</td> 
       <td>null</td> 
      </tr> 
     </tbody> 
    </table>
    
    可以看到查询结果的第二行，t2表无对应匹配行，直接显示为null，t1表所有值均显示完全。专业一点说，即 左外连接 = 内连接 + 左边表内连接之后剩余的其他记录，右边补NULL。
    
    右外连接(right join)
    
    恰与左连接相反，返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值。
 *  右外连接：查询每个员工的编号、姓名、职位和部门名称和部门位置
    
        select t1.empno,t1.ename,t1.job,t2.dname,t2.loc
        from t_employee t1
        right join t_dept t2 on t1.deptno = t2.deptno;
    
    结果：
    
    <table style="width:500px;"> 
     <tbody> 
      <tr> 
       <th>empno</th> 
       <th>ename</th> 
       <th>job</th> 
       <th>dname</th> 
       <th>loc</th> 
      </tr> 
      <tr> 
       <td>7900</td> 
       <td>JAMES</td> 
       <td>CLERK</td> 
       <td>SALES</td> 
       <td>CHICAGO</td> 
      </tr> 
      <tr> 
       <td>null</td> 
       <td>null</td> 
       <td>null</td> 
       <td>OPERATIONS</td> 
       <td>BOSTON</td> 
      </tr> 
     </tbody> 
    </table>
    
    ### 全连接(full join) ###
    
    返回左表和右表中的所有行。当某行在另一表中没有匹配行，则另一表中的列返回空值  
    这里为了测试效果，插入一个不在在部门表搜索不到的员工记录。如下：
    
        insert into t_employee 
        values
        ('7935','TEST','CLERK','7782','2011-03-12','1300',NULL,'50');
    
     
 *  查询每个员工的编号、姓名、职位和部门名称和部门位置
    
        select t1.empno,t1.ename,t1.job,t2.dname,t2.loc
        from t_employee t1
        full join t_dept t2 on t1.deptno = t2.deptno;
    
    .....运行了一下发现MySQL并不支持全连接，但是可以通过其他途径实现全连接的查询结果。接下来我们学习的合并查询记录就可以实现我们想要的结果。
    
    ### 合并查询数据记录 ###
    
    合并查询数据使用UNION或UNION ALL,思考一下全连接 = 左连接 UNION 右连接，赶快来试试
    
        select t1.empno,t1.ename,t1.job,t2.dname,t2.loc
        from t_employee t1
        left join t_dept t2 on t1.deptno = t2.deptno
        UNION
        select t1.empno,t1.ename,t1.job,t2.dname,t2.loc
        from t_employee t1
        right join t_dept t2 on t1.deptno = t2.deptno;
    
    结果：
    
    <table style="width:500px;"> 
     <tbody> 
      <tr> 
       <th>empno</th> 
       <th>ename</th> 
       <th>job</th> 
       <th>dname</th> 
       <th>loc</th> 
      </tr> 
      <tr> 
       <td>7935</td> 
       <td>TEST</td> 
       <td>CLERK</td> 
       <td>NULL</td> 
       <td>NULL</td> 
      </tr> 
      <tr> 
       <td>NULL</td> 
       <td>NULL</td> 
       <td>NULL</td> 
       <td>OPERATIONS</td> 
       <td>BOSTON</td> 
      </tr> 
     </tbody> 
    </table>
    
    看下查询结果，即是全连接的效果了。满足了，我们再来看看UNION和UNION ALL的区别：
    
        select t1.empno,t1.ename,t1.job,t2.dname,t2.loc
        from t_employee t1
        left join t_dept t2 on t1.deptno = t2.deptno
        UNION ALL
        select t1.empno,t1.ename,t1.job,t2.dname,t2.loc
        from t_employee t1
        right join t_dept t2 on t1.deptno = t2.deptno;
    
    大家自行看查询结果吧，区别还是很显而易见的，即：  
    UNION执行了去重的操作，而UNION ALL并没有去重。
    
    --------------------
    
    ### 子查询 ###
    
    子查询说通俗一点，即是嵌套查询。看一下查询语句，大家一下就明白了。
 *  查询工资比SMITH还要高的全部员工的信息
    
        select *
        from t_employee t
        where t.sal > (
                   select sal
                   from t_employee
                   where ename = 'SMITH');
    
    是不是已经明白了，即是在上一次查询结果的基础上再做一次查询。再练习一下。
 *  查询工资和职位都与WARD一样，并且不是WARD本人的员工的信息
    
        SELECT
            *
        FROM
            t_employee
        WHERE
            (sal, job) = (
                SELECT
                    sal,job
                FROM
                    t_employee
                WHERE
                    ename = 'WARD'
            )
        AND ename != 'WARD';
    
     
 *  查询员工表中的员工信息，要求员工的编号都在部门表中有记录
    
        SELECT
            *
        FROM
            t_employee
        WHERE
            deptno IN (SELECT deptno FROM t_dept);
    
     
 *  查询员工表中的员工信息，要求员工的编号不在部门表中有记录
    
        select *
        from t_employee
        where deptno NOT IN (select deptno from t_dept);
    
    --------------------
    
    以上就是整理常见查询命令的使用方法，这里最后给大家留一道面试遇到的一道SQL题。题目如下：
    
    <table style="width:500px;"> 
     <tbody> 
      <tr> 
       <th>_id</th> 
       <th>name</th> 
       <th>value</th> 
      </tr> 
      <tr> 
       <td>1</td> 
       <td>a</td> 
       <td>1</td> 
      </tr> 
      <tr> 
       <td>2</td> 
       <td>b</td> 
       <td>2</td> 
      </tr> 
      <tr> 
       <td>3</td> 
       <td>a</td> 
       <td>1</td> 
      </tr> 
      <tr> 
       <td>4</td> 
       <td>b</td> 
       <td>2</td> 
      </tr> 
      <tr> 
       <td>5</td> 
       <td>b</td> 
       <td>2</td> 
      </tr> 
     </tbody> 
    </table>
    
    执行一条SQL语句，删除表内的重复行。如下结果(\*表示值不限定)：
    
    <table style="width:500px;"> 
     <tbody> 
      <tr> 
       <th>_id</th> 
       <th>name</th> 
       <th>value</th> 
      </tr> 
      <tr> 
       <td>*</td> 
       <td>a</td> 
       <td>1</td> 
      </tr> 
      <tr> 
       <td>*</td> 
       <td>b</td> 
       <td>2</td> 
      </tr> 
     </tbody> 
    </table>
    
    大家可能会想到的解决办法如下：
    
        delete from t_test
        where `_id` NOT IN (
                  select min(id)
                  from t_test
                  group by `name`,`value`
                  having count(*) > 1);
    
    但是这样会报这样一个错误`You can't specify target table 't_test' for update in FROM clause`,查了Google这是因为MySQL不支持select语句与delete、update语句同时出现在同一个表中。在网上找到了解决办法，非常巧妙的解决～将语句改成如下：
    
        DELETE FROM
            t_test
        WHERE
            `_id` NOT IN (
            select MIN(_id)
        from (SELECT * from t_test) t
        GROUP BY t.name,t.value
        HAVING count(*) > 1
        );
    
    重点就在`select min(_id) from (SELECT * from t_test) t`，它将表的所有数据做成了一个子集，这样就巧妙的解决了select语句与delete、update语句同时出现在同一个表中的问题了。很有启发性～