MySQL多表資料記錄查詢詳解_Mysql

在實際應用中，經常需要實現在一個查詢語句中顯示多張表的資料，這就是所謂的多表資料記錄串連查詢，簡稱來年將誒查詢。    

在具體實現串連查詢操作時，首先將兩個或兩個以上的表按照某個條件串連起來，然後再查詢到所要求的資料記錄。串連查詢分為內串連查詢和外串連查詢。 

在具體應用中，如果需要實現多表資料記錄查詢，一般不使用串連查詢，因為該操作效率比較低。於是MySQL又提供 了串連查詢的替代操作，子查詢操作。
1.關係資料操作： 
在串連查詢中，首先需要對兩張或兩張以上的表進行串連操作。串連操作是關係資料操作中專門用於資料操作的關係運算。 

1.1 並（UNION）：
    在SQL語言中存在一種關係資料操作，叫做並操作。“並”就是把具有相同欄位數目和欄位類型的表合并到一起。通過並操作將兩張表的資料記錄合并到一起，合并後的欄位數為 表1的欄位數或者表2的欄位數；合并後的總記錄數為：表1的記錄數 + 表2的記錄數 - 表1和表2 重複的記錄數。 

1.2 笛卡爾積（CARTESIAN PRODUCT）：
    在SQL語言中存在一種關係資料操作，叫做笛卡爾積操作。笛卡爾就是沒有串連條件，表中的資料任意的組合。笛卡爾積的欄位數為 表1的欄位數 + 表2的欄位數。笛卡爾積的記錄數為：表1的記錄數 * 表2的記錄數。 

1.3 內串連（INNER JOIN）：
    為了便於操作，專門提供了一種針對資料庫操作的運算—串連（JOIN）。所謂串連就是在表關係的笛卡爾積資料記錄中，按照相應欄位值的比較條件進行選擇產生一個新的關係。串連又分為內串連（INNER JOIN）、外串連（OUTER JOIN）、交叉串連（CROSS JOIN）。
    所謂內串連，就是在表關係的笛卡爾積資料中，保留表關係中所有匹配的資料記錄，捨棄不匹配的資料記錄。按照匹配的條件可以分成自然串連、等值串連和不等串連。
    1.3.1 自然串連（NATURAL JOIN）：
    自然串連就是在表關係的笛卡爾積中，首先根據表關係中相同名稱的欄位自動進行記錄匹配，然後去掉重複的欄位。
    通過自然串連後，新關係的欄位數為：表1欄位數 + 表2欄位數 - 表1和表2中的重複欄位數。自然串連後新關係的記錄數為：表1的記錄數 * 表2的記錄數 - 表1和表2相同欄位的值不相等記錄數。
自然串連又如下特點：
    1. 在具體執行自然串連時，會自動判斷相同名稱的欄位，然後進行資料值的匹配。
    2. 在執行完自然串連的新關係中，雖然可以指定包含哪些欄位，但是不能指定執行過程中的匹配條件，即哪些欄位的值進行匹配。
    3. 在執行自然串連的新關係中，執行過程中所匹配的欄位名只有一個，即會去掉重複欄位。
    1.3.2 等值串連：
    所謂等值串連操作就是表關係的笛卡爾積中，選擇所匹配欄位值相等的資料記錄。
    通過等值串連後，新關係的欄位數為：表1欄位數 + 表2欄位數。等值串連後新關係的記錄數為：表1的記錄數 * 表2的記錄數 - 表1和表2相同欄位的值不相等記錄數。
    與自然串連相比，等值串連操作需要在執行過程中用“=”指定匹配條件，在新關係中不會去掉重複欄位。
    1.3.3 不等串連：
    所謂不等串連操作就是表關係的笛卡爾積中，選擇所匹配欄位值不相等的資料記錄。
    通過不等串連後，新關係的欄位數為：表1欄位數 + 表2欄位數。等值串連後新關係的記錄數為：表1的記錄數 * 表2的記錄數 - 表1和表2相同欄位的值相等的記錄數。
    與自然串連相比，等值串連操作需要在執行過程中用“!=”指定匹配條件，在新關係中不會去掉重複欄位。 

1.4 外串連（OUTER JOIN）：
    所謂外串連（OUTER JOIN）,就是在表關係的笛卡爾積資料記錄中，不僅保留表關係中所有匹配的資料記錄，而且還會保留部分不匹配的資料記錄。按照保留不不匹配條件資料記錄來源可以分為：左外串連、右外串連、全外串連。
    1.4.1 左外串連：
所謂左外串連操作就是表關係的笛卡爾積中，除了選擇相匹配的資料記錄，還包含關聯左邊表中不匹配的資料記錄。
    通過左外串連後，新關係的欄位數為：左表欄位數 + 右表欄位數。左外串連後新關係的記錄數為：左表的記錄數 * 右表的記錄數 - 左表和右表相同欄位的值不相等的記錄數 + 左表中未匹配的記錄數。
    1.4.2 右外串連：
所謂右外串連操作就是表關係的笛卡爾積中，除了選擇相匹配的資料記錄，還包含關聯右邊表中不匹配的資料記錄。
    通過右外串連後，新關係的欄位數為：左表欄位數 + 右表欄位數。右外串連後新關係的記錄數為：左表的記錄數 * 右表的記錄數 - 左表和右表相同欄位的值不相等的記錄數 + 右表中未匹配的記錄數。
    1.4.3 全外串連：
所謂右外串連操作就是表關係的笛卡爾積中，除了選擇相匹配的資料記錄，還包含關聯左右兩邊表中不匹配的資料記錄。
    通過全外串連後，新關係的欄位數為：左表欄位數 + 右表欄位數。全外串連後新關係的記錄數為：左表的記錄數 * 右表的記錄數 - 左表和右表相同欄位的值不相等的記錄數 + 左表未匹配的記錄數 + 右表中未匹配的記錄數。

2.內串連查詢：
    在MySQL中實現串連查詢有兩種文法：
    1. 在from子句中利用逗號區分多個表，在where子句中通過邏輯運算式來實現匹配條件，從而實現表的串連。
    2. ANSI串連文法形式，在from子句中使用“join on”關鍵字，而串連條件寫在關鍵字on子句中。推薦使用第二種方式。
    按照匹配條件，內串連查詢可以分為兩類：等值串連；不等串連。

內串連查詢文法為：

select field1, field2 ...fieldn   from join_tablename1 inner join join_tablename2 [inner join join_tablename]     on join_condition

//參數filedn 表示所要查詢的欄位名稱，來源於所串連的表join_tablename1 和 join_tablename2,關鍵字inner join表進行內串連，join_condition表示進行匹配的條件。

2.1 自串連：
    內串連查詢中存在一種特殊的等值串連—自串連。所謂自串連就是指表與其自身進行串連。

樣本（查詢每個僱員的姓名、職位、領導姓名）：

mysql> select e.ename,e.job,l.ename from t_employee e inner join t_employee l on e.MGR=l.empno;+---------+----------+-------+| ename  | job   | ename |+---------+----------+-------+| SCOTT  | ANALYST | JONES || FORD  | ANALYST | JONES || ALLEN  | SALESMAN | BLAKE || MARD  | SALESMAN | BLAKE || MARRTIN | SALESMAN | BLAKE || TURNER | SALESMAN | BLAKE || JAMES  | CLEAR  | BLAKE || MILLER | CLEAR  | CLARK || ADAMS  | CLEAR  | SCOTT || JONES  | MANAGER | KING || BLAKE  | MANAGER | KING || CLARK  | MANAGER | KING || SMITH  | CLEAR  | FORD |+---------+----------+-------+13 rows in set (0.00 sec)

2.2等值串連：
    內串連查詢中的等值串連，就是在關鍵字on後的匹配條件中通過等於關係運算子“=”來實現等值條件。

樣本：

mysql> select e.empno,e.ename,e.job,d.dname,d.loc from t_employee e inner join t_dept d on e.deptno=d.deptno;+-------+---------+-----------+------------+----------+| empno | ename  | job    | dname   | loc   |+-------+---------+-----------+------------+----------+| 7788 | SCOTT  | ANALYST  | ACCOUNTING | NEW YORK || 7839 | KING  | PRESIDENT | ACCOUNTING | NEW YORK || 7934 | MILLER | CLEAR   | ACCOUNTING | NEW YORK || 7369 | SMITH  | CLEAR   | RESEARCH  | DALLAS  || 7499 | ALLEN  | SALESMAN | RESEARCH  | DALLAS  || 7566 | JONES  | MANAGER  | RESEARCH  | DALLAS  || 7782 | CLARK  | MANAGER  | RESEARCH  | DALLAS  || 7876 | ADAMS  | CLEAR   | RESEARCH  | DALLAS  || 7902 | FORD  | ANALYST  | RESEARCH  | DALLAS  || 7521 | MARD  | SALESMAN | SALES   | CHICAGO || 7654 | MARRTIN | SALESMAN | SALES   | CHICAGO || 7698 | BLAKE  | MANAGER  | SALES   | CHICAGO || 7844 | TURNER | SALESMAN | SALES   | CHICAGO || 7900 | JAMES  | CLEAR   | SALES   | CHICAGO |+-------+---------+-----------+------------+----------+14 rows in set (0.00 sec)

2.3不等串連：
    內串連查詢中的不等串連，就是在關鍵字on後的匹配條件中通過除了等於關係運算子來實現不等條件外，可以使用的關係運算子包含> >= < <= !=

樣本：

mysql> select e.ename employeename, e.job,l.ename loadername from t_employee e inner join t_employee l on e.mgr=l.empnoand e.empno>l.empno;+--------------+----------+------------+| employeename | job   | loadername |+--------------+----------+------------+| SCOTT    | ANALYST | JONES   || FORD     | ANALYST | JONES   || TURNER    | SALESMAN | BLAKE   || JAMES    | CLEAR  | BLAKE   || MILLER    | CLEAR  | CLARK   || ADAMS    | CLEAR  | SCOTT   |+--------------+----------+------------+6 rows in set (0.00 sec)

3.外串連查詢：
    外串連查詢會返回所動作表中至少一個表的所有資料。外串連分為三類：左外串連、右外串連、全外串連

文法為：

select field1, field2, ...fieldn  from join_tablename1 left|rigth|full [outer] join join_tablename2  on join_condition

3.1左外串連：
    外串連查詢中的左外串連，就是指新關係中執行匹配條件時，以關鍵字left join 左邊的表為參考。

樣本：

mysql> select e.ename employeename, e.job job,l.ename leadername from t_employee e left join t_employee l on e.mgr=l.empno;+--------------+-----------+------------+| employeename | job    | leadername |+--------------+-----------+------------+| SMITH    | CLEAR   | FORD    || ALLEN    | SALESMAN | BLAKE   || MARD     | SALESMAN | BLAKE   || JONES    | MANAGER  | KING    || MARRTIN   | SALESMAN | BLAKE   || BLAKE    | MANAGER  | KING    || CLARK    | MANAGER  | KING    || SCOTT    | ANALYST  | JONES   || KING     | PRESIDENT | NULL    || TURNER    | SALESMAN | BLAKE   || ADAMS    | CLEAR   | SCOTT   || JAMES    | CLEAR   | BLAKE   || FORD     | ANALYST  | JONES   || MILLER    | CLEAR   | CLARK   |+--------------+-----------+------------+14 rows in set (0.00 sec)

3.2右外串連：
    外串連查詢中的右外串連，就是指新關係中執行匹配條件時，以關鍵字right join 右邊的表為參考。

4.合并查詢資料記錄：
    在MySQL中通過關鍵字UNION來實現並操作，即可以通過其將多個select語句的查詢結果合并在一起組成新的關係。
    1. 關鍵字union的合併作業
       關鍵字union會把查詢結果集直接合并在一起，同時將會去掉重複資料記錄。
    2. 關鍵字union all的合併作業
       關鍵字union all會把查詢結果集直接合并在一起。

文法為：

select field1, field2, ...fieldn   from tablename1union | union allselect field1, field2, ...fieldn  from tablename2union | union allselect field1, field2, ...fieldn  from tablename3......

5.子查詢：
    在MySQL中雖然可以通過串連查詢實現多表查詢資料記錄，但卻不建議使用。這是因為串連查詢的效能很差。因此出現了串連查詢的替代者子查詢。推薦使用子查詢來實現多表查詢資料記錄。 

5.1 為什麼使用子查詢：
    在日常開發中，經常接觸到查詢多表資料記錄操作，例如查詢部門表t_dept和僱員表t_employee表的資料記錄。對於新手，直接使用select * from t_dept t,t_employee e where t.deptno=e.deptno;這條sql語句在執行時，首先會對兩個表進行笛卡爾積操作，然後在選取符合匹配條件的資料記錄。如果兩張表的資料量較大，則在進行笛卡爾積操作時會造成死機。有經驗的開發人員通常會首先用統計函數查看動作表笛卡爾積後的資料記錄數，然後再進行多表查詢。因此多表查詢一般會經過如下步驟：
    1. 通過統計函數count(1)查詢所關聯表笛卡爾積後的資料的記錄數。然後再進行多表查詢。
    2. 如果查詢到的資料記錄數mysql可以接受，然後再進行多表查詢，否則就應該考慮通過其他方式來實現。
    如果笛卡爾積後的資料遠遠大於mysql軟體可以接受的範圍，為瞭解決多表查詢，mysql提供了子查詢來實現多表查詢。
    所謂子查詢，就是指在一個查詢中嵌套了其他若干查詢，即在一個select 查詢語句的where或from子句中包含另一個select查詢語句。在查詢語句中，外層select查詢語句稱為主查詢，where子句中select查詢語句被稱為子查詢，也被稱為巢狀查詢。
    通過子查詢可以實現多表查詢，該查詢語句中可能包含in，any，all，exists等關鍵字。除此之外還可能包含比較子。理論上子查詢可以出現在查詢語句的任何位置，但在實際開發中，子查詢經常出現在where或from子句中。
    where子句中的子查詢，該位置處的子查詢一般返回單行單列、多行多列、單行多列資料記錄。
    from子句中的子查詢，該位置處的子查詢一般返回多行多列資料記錄，可以當作一張暫存資料表。

5.2 返回結果為單行單列和單行多列子查詢：
當子查詢的返回結果為單行蛋類資料記錄時，該子查詢語句一般在主查詢語句的where子句中，通常會包含比較子（> < = != 等）
    5.2.1 單行單列子查詢：

樣本（工資比Smith高的全部僱員資訊）：

mysql> select * from t_employee where sal > (select sal from t_employee where ename='smith');+-------+---------+-----------+------+------------+---------+---------+--------+| empno | ename  | job    | MGR | Hiredate  | sal   | comm  | deptno |+-------+---------+-----------+------+------------+---------+---------+--------+| 7499 | ALLEN  | SALESMAN | 7698 | 1982-03-12 | 1600.00 | 300.00 |   20 || 7521 | MARD  | SALESMAN | 7698 | 1983-03-12 | 1250.00 | 500.00 |   30 || 7566 | JONES  | MANAGER  | 7839 | 1981-03-12 | 2975.00 |  NULL |   20 || 7654 | MARRTIN | SALESMAN | 7698 | 1981-03-12 | 2850.00 | 1400.00 |   30 || 7698 | BLAKE  | MANAGER  | 7839 | 1981-03-12 | 2850.00 |  NULL |   30 || 7782 | CLARK  | MANAGER  | 7839 | 1985-03-12 | 2450.00 |  NULL |   20 || 7788 | SCOTT  | ANALYST  | 7566 | 1981-03-12 | 3000.00 |  NULL |   10 || 7839 | KING  | PRESIDENT | NULL | 1981-03-12 | 5000.00 |  NULL |   10 || 7844 | TURNER | SALESMAN | 7698 | 1989-03-12 | 1500.00 |  0.00 |   30 || 7876 | ADAMS  | CLEAR   | 7788 | 1998-03-12 | 1100.00 |  NULL |   20 || 7900 | JAMES  | CLEAR   | 7698 | 1987-03-12 | 950.00 |  NULL |   30 || 7902 | FORD  | ANALYST  | 7566 | 0000-00-00 | 3000.00 |  NULL |   20 || 7934 | MILLER | CLEAR   | 7782 | 1981-03-12 | 1300.00 |  NULL |   10 |+-------+---------+-----------+------+------------+---------+---------+--------+13 rows in set (0.00 sec)

    5.2.2 單行多列子查詢：

    where子句中的子查詢除了是返回單行單列的資料記錄外，還可以是返回多行多列的資料記錄，不過這種子查詢很少出現。

樣本（工資和職位和Smith一樣的全部僱員）：

mysql> select ename,job,sal from t_employee where (sal,job)=(select sal,job from t_employee where ename='smith');+-------+-------+--------+| ename | job  | sal  |+-------+-------+--------+| SMITH | CLEAR | 800.00 |+-------+-------+--------+1 row in set (0.00 sec)

5.3 返回結果為多行單列子查詢：
    當子查詢的返回結果為多行單列資料記錄時，該子查詢語句一般會在主查詢語句的where子句中出現，通常會包含IN ANY ALL EXISTS等關鍵字。
    5.3.1 帶有關鍵字in的子查詢：
        當主查詢的條件在子查詢的查詢結果中時，可以通過關鍵字in來進行判斷。相反，如果想實現主查詢的條件不在子查詢的查詢結果中時，可以通過關鍵字not in來進行判斷。

樣本：

mysql> select * from t_employee where deptno in(select deptno from t_dept);+-------+---------+-----------+------+------------+---------+---------+--------+| empno | ename  | job    | MGR | Hiredate  | sal   | comm  | deptno |+-------+---------+-----------+------+------------+---------+---------+--------+| 7369 | SMITH  | CLEAR   | 7902 | 1981-03-12 | 800.00 |  NULL |   20 || 7499 | ALLEN  | SALESMAN | 7698 | 1982-03-12 | 1600.00 | 300.00 |   20 || 7521 | MARD  | SALESMAN | 7698 | 1983-03-12 | 1250.00 | 500.00 |   30 || 7566 | JONES  | MANAGER  | 7839 | 1981-03-12 | 2975.00 |  NULL |   20 || 7654 | MARRTIN | SALESMAN | 7698 | 1981-03-12 | 2850.00 | 1400.00 |   30 || 7698 | BLAKE  | MANAGER  | 7839 | 1981-03-12 | 2850.00 |  NULL |   30 || 7782 | CLARK  | MANAGER  | 7839 | 1985-03-12 | 2450.00 |  NULL |   20 || 7788 | SCOTT  | ANALYST  | 7566 | 1981-03-12 | 3000.00 |  NULL |   10 || 7839 | KING  | PRESIDENT | NULL | 1981-03-12 | 5000.00 |  NULL |   10 || 7844 | TURNER | SALESMAN | 7698 | 1989-03-12 | 1500.00 |  0.00 |   30 || 7876 | ADAMS  | CLEAR   | 7788 | 1998-03-12 | 1100.00 |  NULL |   20 || 7900 | JAMES  | CLEAR   | 7698 | 1987-03-12 | 950.00 |  NULL |   30 || 7902 | FORD  | ANALYST  | 7566 | 0000-00-00 | 3000.00 |  NULL |   20 || 7934 | MILLER | CLEAR   | 7782 | 1981-03-12 | 1300.00 |  NULL |   10 |+-------+---------+-----------+------+------------+---------+---------+--------+14 rows in set (0.00 sec)

    5.3.2 帶有關鍵字any的子查詢：
        關鍵字any：主查詢的條件為滿足子查詢的查詢結果中任意一條資料記錄，該關鍵字有三種匹配方式；
1. =any：其功能與關鍵字in一樣
2. > any（>=any）：只要大於（大於等於）子查詢中最小的一個即可。
3. < any（<=any）：只要小於（小於等於）子查詢中最大的一個即可。

樣本（查詢僱員工資不低於職位為manager的工資）：

mysql> select ename,sal from t_employee where sal>any(select sal from t_employee where job='manager');+---------+---------+| ename  | sal   |+---------+---------+| JONES  | 2975.00 || MARRTIN | 2850.00 || BLAKE  | 2850.00 || SCOTT  | 3000.00 || KING  | 5000.00 || FORD  | 3000.00 |+---------+---------+6 rows in set (0.00 sec)

    5.3.3 帶有關鍵字all的子查詢：
    關鍵字all用來表示主查詢的條件為滿足子查詢返回查詢結果中所有資料記錄，有兩種匹配方式：
    1. > all（>=all）：比子查詢結果中最大的還要大（大於等於）的資料記錄；
    2. < all（<= all）：比子查詢結果中最小的還要小（小於等於）的資料記錄。

樣本：

mysql> select ename,sal from t_employee where sal>all(select sal from t_employee where job='manager');+-------+---------+| ename | sal   |+-------+---------+| SCOTT | 3000.00 || KING | 5000.00 || FORD | 3000.00 |+-------+---------+3 rows in set (0.00 sec)

    5.3.4 帶有關鍵字exists的子查詢：
    關鍵字exists是一個boolean類型，當能返回結果集時為true，不能返回結果集時為false。查詢時exists對外表採用遍曆方式逐條查詢，每次查詢都會比較exists的條件陳述式，當exists裡的條件陳述式返回記錄行時則條件為真，此時返回當前遍曆到的記錄；反之，如果exists裡條件陳述式不能返回記錄行，則丟棄當前遍曆到的記錄。
  5.4 返回結果為多行多列子查詢：
    當子查詢的返回結果為多行多列資料記錄時，該子查詢語句一般會在主查詢語句的from子句裡，被當作一張暫存資料表的方式來處理。

樣本（查詢僱員表中各部門的部門號、部門名稱、部門地址、僱員人數、和平均工資）：
通過內串連來實現：

mysql> select d.deptno,d.dname,d.loc,count(e.empno) number,avg(e.sal) average from t_employee e inner join t_dept d on e.deptno=d.deptno group by d.deptno;+--------+------------+----------+--------+-------------+| deptno | dname   | loc   | number | average   |+--------+------------+----------+--------+-------------+|   10 | ACCOUNTING | NEW YORK |   3 | 3100.000000 ||   20 | RESEARCH  | DALLAS  |   6 | 1987.500000 ||   30 | SALES   | CHICAGO |   5 | 1880.000000 |+--------+------------+----------+--------+-------------+3 rows in set (0.00 sec)

通過子查詢來實現：

mysql> select d.deptno,d.dname,d.loc,number,average from t_dept d inner join(select deptno dno,count(empno) number,avg(sal) average from t_employee group by deptno) employee on d.deptno=employee.dno;+--------+------------+----------+--------+-------------+| deptno | dname   | loc   | number | average   |+--------+------------+----------+--------+-------------+|   10 | ACCOUNTING | NEW YORK |   3 | 3100.000000 ||   20 | RESEARCH  | DALLAS  |   6 | 1987.500000 ||   30 | SALES   | CHICAGO |   5 | 1880.000000 |+--------+------------+----------+--------+-------------+3 rows in set (0.00 sec)

以上就是本文的全部內容，希望對大家的學習有所協助，也希望大家多多支援雲棲社區。