多表查詢之關(guān)連查詢

多表數(shù)據(jù)連接查詢，簡稱連接查詢。本篇我們來一同學(xué)習(xí)多表連接查詢的相關(guān)用法，主要內(nèi)容有：

1. 內(nèi)連接查詢
2. 外連接查詢
3. 查詢結(jié)果合并
4. 子查詢

首先第一張表還是我們之前用到t_employee，我們在另外新建一個表t_dept（部門表）建表命令如下：

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

插入數(shù)據(jù)：

insert into t_dept
(_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;

• 結(jié)果如下：

從結(jié)果可以看出，兩張表中的每條記錄都進(jìn)行了重組，導(dǎo)致最終查詢結(jié)果的條數(shù)為兩張表中的記錄數(shù)的乘積，這樣的結(jié)果積在數(shù)學(xué)上稱為笛卡爾積。簡單比喻下就是所有可能的組合都進(jìn)行了組合。

內(nèi)連接（INNER JOIN）

使用比較運算符（包括=、>、<、<>、>=、<=、!>和!<）進(jìn)行表間的比較操作，查詢與連接條件相匹配的數(shù)據(jù)。

• 特殊：自連接：查詢每個員工的姓名、職位和領(lǐng)導(dǎo)姓名
  方法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;

• 查詢每個員工的編號、姓名、基本工資、職位、領(lǐng)導(dǎo)姓名、部門名稱和部門所在位置

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)

返回左表中的所有行，如果左表中行在右表中沒有匹配行，則結(jié)果中右表中的列返回空值。

• 查詢每個員工的姓名、職位和領(lǐng)導(dǎo)姓名

select t1.ename,t1.job,t2.ename as mgr_name
from t_employee t1
left join t_employee t2 on t1.mgr = t2.empno;

結(jié)果：

可以看到查詢結(jié)果的第二行，t2表無對應(yīng)匹配行，直接顯示為null，t1表所有值均顯示完全。專業(yè)一點說，即 左外連接 = 內(nèi)連接 + 左邊表內(nèi)連接之后剩余的其他記錄，右邊補(bǔ)NULL。

右外連接(right join)

恰與左連接相反，返回右表中的所有行，如果右表中行在左表中沒有匹配行，則結(jié)果中左表中的列返回空值。

• 右外連接：查詢每個員工的編號、姓名、職位和部門名稱和部門位置

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;

結(jié)果：

全連接(full join)

返回左表和右表中的所有行。當(dāng)某行在另一表中沒有匹配行，則另一表中的列返回空值
這里為了測試效果，插入一個不在在部門表搜索不到的員工記錄。如下：

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;

......運行了一下發(fā)現(xiàn)MySQL并不支持全連接，但是可以通過其他途徑實現(xiàn)全連接的查詢結(jié)果。接下來我們學(xué)習(xí)的合并查詢記錄就可以實現(xiàn)我們想要的結(jié)果。

合并查詢數(shù)據(jù)記錄

合并查詢數(shù)據(jù)使用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;

結(jié)果：

看下查詢結(jié)果，即是全連接的效果了。滿足了，我們再來看看UNION和UNION ALL的區(qū)別：

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;

大家自行看查詢結(jié)果吧，區(qū)別還是很顯而易見的，即：
UNION執(zhí)行了去重的操作，而UNION ALL并沒有去重。

子查詢

子查詢說通俗一點，即是嵌套查詢?？匆幌虏樵冋Z句，大家一下就明白了。

• 查詢工資比SMITH還要高的全部員工的信息

select *
from t_employee t
where t.sal > (
           select sal
           from t_employee
           where ename = 'SMITH'); 

是不是已經(jīng)明白了，即是在上一次查詢結(jié)果的基礎(chǔ)上再做一次查詢。再練習(xí)一下。

• 查詢工資和職位都與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題。題目如下：

執(zhí)行一條SQL語句，刪除表內(nèi)的重復(fù)行。如下結(jié)果(*表示值不限定)：

大家可能會想到的解決辦法如下：

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語句同時出現(xiàn)在同一個表中。在網(wǎng)上找到了解決辦法，非常巧妙的解決～將語句改成如下：

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，它將表的所有數(shù)據(jù)做成了一個子集，這樣就巧妙的解決了select語句與delete、update語句同時出現(xiàn)在同一個表中的問題了。很有啟發(fā)性～
以上兩篇即是關(guān)于SQL語句的學(xué)習(xí)。：）