MySQL資料庫——多表查詢

多表查詢

多表查詢有如下幾種：

1. 合并結果集；UNION 、 UNION ALL

2. 連接配接查詢

   2.1内連接配接 [INNER] JOIN ON

   2.2外連接配接 OUTER JOIN ON

   • 左外連接配接 LEFT [OUTER] JOIN
   • 右外連接配接 RIGHT [OUTER] JOIN
3. 子查詢

一、合并結果集

作用：合并結果集就是把兩個select語句的查詢結果合并到一起！

合并結果集有兩種方式：

• UNION：去除重複記錄，例如：SELECT* FROM t_1 UNION SELECT * FROM t_2；
• UNION ALL：不去除重複記錄，例如：SELECT * FROM t_1 UNION ALL SELECT * FROM t_2。

注意：被合并的兩個結果：列數、列類型必須相同（列類型可以不同，但這樣将無意義，是以規定還是相同）。

1、 使用 UNION ：

2 、使用 UNION ALL ：

二、連接配接查詢

連接配接查詢就是求出多個表的乘積（笛卡爾積），例如t1連接配接t2，那麼查詢出的結果就是t1*t2

但這樣的結果并不是我們想要的，那麼怎麼去除重複的，不想要的記錄呢，當然是通過條件過濾。是以此時就規定我們對于要查詢的多個表之間必須存在關聯關系，通過關聯關系去除笛卡爾積。

現在我們仿照oracle資料庫的兩張表舉例：

emp表：

CREATE TABLE emp(
    empno   int,
    ename   varchar(),
    job     varchar(),
    mgr     int,
    hiredate    date,
    sal     decimal(,),
    comm    decimal(,),
    deptno  int
);
#添加資料SQL語句省略
           

dept表：

CREATE TABLE dept(
    deptno      int,
    dname       varchar(),
    loc     varchar()
);
#添加資料SQL語句省略
           

以上表建立好以後，我們就可以進行連接配接查詢了，連接配接查詢分為内連接配接和外連結（包括左外連接配接和右外連結），如下所示：

一：内連接配接

内連接配接的特點：查詢結果必須滿足關聯條件。

SQL标準的内連接配接為：

• SELECT * FROM 表1 [别名] INNER JOIN 表2 [别名] ON 關聯條件;

即：

注意：on後面 主外鍵關系
           

此文法為MySQL提供的标準内連接配接文法，内連接配接還有另外一種寫法（方言）：

• SELECT * FROM 表1 [别名] , 表2 [别名] , …, 表n [别名]

  WHERE 關聯條件;

例如：

SELECT * FROM emp e,dept d 
WHERE e.deptno=d.deptno;
           

二、外連接配接

包括左外連接配接和右外連接配接，外連接配接的特點：查詢出的結果存在不滿足條件的可能。

1、左外連接配接

特點：左連接配接是先查詢出左表（即以左表為主），然後查詢右表，左表中滿足條件和不滿足條件都顯示出來，右邊不滿足條件的顯示NULL。

文法 ： SELECT * FROM 左表 [别名] LEFT OUTER JOIN 右表 [别名] ON 關聯條件;

例如：

2、右外連接配接

特點：與左外連接配接相反，右連接配接就是先把右表中所有記錄都查詢出來，然後左表滿足條件的顯示，不滿足顯示NULL。

文法：SELECT * FROM 左表 [别名] RIGHT OUTER JOIN 右表 [别名] ON 關聯條件;

例如：

注：使用外連結隻是從顯示的角度消除了笛卡爾積，并沒有真正避免笛卡爾積的産生，是以一般在資料量比較小的時候會使用外連結，資料量大或表比較多時，禁止使用外連結查詢。

三、子查詢

一個select語句中包含另一個完整的select語句。

子查詢就是嵌套查詢，即SELECT中包含SELECT，如果一條語句中存在兩個，或兩個以上SELECT，那麼就是子查詢語句了。裡面的查詢叫做子查詢，外層的查詢叫父查詢，一般情況都是先執行子查詢，再執行父查詢。

子查詢出現的位置：

a. where後，作為被查詢的條件的一部分；（傳回結果必須為單列）

b. from後，作臨時表；（傳回結果一般為多行多列）
           

當子查詢出現在where後作為條件時，還可以使用如下關鍵字：

a. any -- 存在即傳回true

b. all  -- 全部滿足傳回true
           

子查詢結果集的形式：

a. 單行單列（用于條件）

b. 多行單列（用于條件）

c. 多行多列（用于表）
           

示例：

• 查詢工資高于JONES的員工。

-- 分析：

-- 查詢條件：工資>JONES工資，其中JONES工資需要一條子查詢。

-- 第一步：查詢JONES的工資

    SELECT sal FROM emp WHERE ename='JONES';

-- 第二步：查詢高于JONES工資的員工

    SELECT * FROM emp WHERE sal > (第一步結果);

-- 結果：

    SELECT * 
    FROM emp 
    WHERE sal > ( SELECT sal 
                  FROM emp  
                  WHERE ename='JONES');

           

• 查詢與SCOTT同一個部門的員工。

-- 子查詢作為條件
-- 子查詢形式為單行單列

-- 分析：
-- 查詢條件：部門=SCOTT的部門編号，其中SCOTT 的部門編号需要一條子查詢。
-- 第一步：查詢SCOTT的部門編号

    SELECT deptno FROM emp WHERE ename='SCOTT';

-- 第二步：查詢部門編号等于SCOTT的部門編号的員工

    SELECT *  
    FROM emp  
    WHERE deptno = (SELECT deptno  
                    FROM emp  
                    WHERE ename='SCOTT');
           

• 工資高于30号部門所有人的員工資訊

-- 方法一：不使用子查詢：

    SELECT * FROMemp WHERE sal>(SELECT MAX(sal)FROM emp WHERE deptno=);

-- 方法二 ：使用子查詢
-- 查詢條件：工資高于30部門所有人工資，其中30部門所有人工資是子查詢。高于所有需要使用all關鍵字。
-- 第一步：查詢30部門所有人工資

    SELECT sal FROM emp WHERE deptno=;

-- 第二步：查詢高于30部門所有人工資的員工資訊

    SELECT * FROM emp WHERE sal > ALL (第一步)

-- 結果：

    SELECT *  
    FROM emp  
    WHERE sal > ALL (SELECT sal  
                     FROM emp  
                     WHERE deptno=)

--  子查詢作為條件
--  子查詢形式為多行單列（當子查詢結果集形式為多行單列時可以使用ALL或ANY關鍵字）
           

FROM 後面子查詢

子查詢的結果将作為一張臨時表使用（多行多列），為了友善使用臨時表中的資料，一般情況下要為其起個響亮的别名。

SELECT ename,job,hiredate  
 FROM (SELECT ename,job,hiredate  
       FROM emp  
       WHERE hiredate>'1987-1-1') AS temp;