MySQL DML操作--------多表聯集查詢實戰

標籤：join   聯集查詢   mysql dba   

1. 背景

   * 多表聯集查詢是把不同表的記錄到一起的一種方式

   * 在SQL標準中規劃的聯合(join)大致分內串連，外串連，全串連。其中外串連又分左外串連，右外串連。

2. 內串連例子 (inner join) [ 員工 --> 部門 ]　

   * 查看員工表[ employees ]和部門表[ departments ]結構

mysql> desc employees;+-----------+---------------+------+-----+---------+----------------+| Field     | Type          | Null | Key | Default | Extra          |+-----------+---------------+------+-----+---------+----------------+| id        | bigint(20)    | NO   | PRI | NULL    | auto_increment || name      | varchar(64)   | NO   |     | NULL    |                || sex       | enum(‘M‘,‘F‘) | NO   |     | NULL    |                || age       | int(11)       | NO   |     | NULL    |                || depart_id | bigint(20)    | NO   |     | NULL    |                |+-----------+---------------+------+-----+---------+----------------+5 rows in set (0.00 sec)mysql> desc departments;+-------+-------------+------+-----+---------+----------------+| Field | Type        | Null | Key | Default | Extra          |+-------+-------------+------+-----+---------+----------------+| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment || name  | varchar(64) | NO   |     | NULL    |                |+-------+-------------+------+-----+---------+----------------+2 rows in set (0.00 sec)

   * 查看員工表[ employees ]和部門表[ departments ]資料

mysql> select * from employees;+----+-------+-----+-----+-----------+| id | name  | sex | age | depart_id |+----+-------+-----+-----+-----------+|  1 | tom   | M   |  25 |         1 ||  2 | jak   | F   |  35 |         2 ||  3 | lisea | M   |  22 |         3 |+----+-------+-----+-----+-----------+3 rows in set (0.00 sec)mysql> select * from departments;+----+------+| id | name |+----+------+|  1 | dev  ||  2 | test ||  3 | ops  |+----+------+3 rows in set (0.00 sec)

   * 查詢並顯示所有員工id, 姓名，姓別，年齡，所在部門 (方法一)

mysql> select e.id id, e.name name, IF(e.sex = ‘M‘, ‘male‘, ‘female‘) sex, e.age age, d.name    -> from employees e, departments d    -> where e.depart_id = d.id;+----+-------+--------+-----+------+| id | name  | sex    | age | name |+----+-------+--------+-----+------+|  1 | tom   | male   |  25 | dev  ||  2 | jak   | female |  35 | test ||  3 | lisea | male   |  22 | ops  |+----+-------+--------+-----+------+3 rows in set (0.03 sec)

   * 查詢並顯示所有員工id, 姓名，姓別，年齡，所在部門 (方法二)　[ 方法一等價於方法二 ]

     inner join 可以省寫成 join

mysql> select e.id id, e.name name, IF(e.sex = ‘M‘, ‘male‘, ‘female‘) sex, e.age age, d.name    -> from employees e inner join departments d    -> on e.depart_id = d.id;+----+-------+--------+-----+------+| id | name  | sex    | age | name |+----+-------+--------+-----+------+|  1 | tom   | male   |  25 | dev  ||  2 | jak   | female |  35 | test ||  3 | lisea | male   |  22 | ops  |+----+-------+--------+-----+------+3 rows in set (0.00 sec)

3. 外串連例子

   左外串連 [ 以左表為基礎，左表的全部資料，右表有的組合。右表沒有的為null ]

   右外串連 [ 以右表為基礎，右表的全部資料，左表有的組合。左表沒有的為null ]

   * 查看a表和b表結構

mysql> desc a;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| data  | int(11) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+1 row in set (0.00 sec)mysql> desc b;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| data  | int(11) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+1 row in set (0.00 sec)

   * 查看a表和b表資料

mysql> select * from a;+------+| data |+------+|    1 ||    2 ||    4 |+------+3 rows in set (0.00 sec)mysql> select * from b;+------+| data |+------+|    2 ||    4 ||    5 ||    6 |+------+4 rows in set (0.01 sec)

　

   * 左外串連查詢 (left join)　以a表為基礎，顯示a表所有資料，b表有的組合，沒有顯示NULLL

        left outer join 可以寫成 left join

mysql> select * from a left outer join b on a.data = b.data;+------+------+| data | data |+------+------+|    2 |    2 ||    4 |    4 ||    1 | NULL |+------+------+3 rows in set (0.00 sec)

   * 右外串連查詢 以b表為基礎，顯示b表所有資料，a表有的組合，沒有顯示NULLL

        right outer join 可以寫成 right  join

mysql> select * from a right outer join b on a.data = b.data;+------+------+| data | data |+------+------+|    2 |    2 ||    4 |    4 || NULL |    5 || NULL |    6 |+------+------+4 rows in set (0.00 sec)

   * 完全(交叉)串連查詢

     沒有 where 子句的交叉聯結將產生聯結所涉及的表的笛卡爾積。

     第一個表的行數乘以第二個表的行數等於笛卡爾積結果集的大小。

    （a和b交叉串連產生3*4=12條記錄） 

mysql> select * from a corss join b;+------+------+| data | data |+------+------+|    1 |    2 ||    2 |    2 ||    4 |    2 ||    1 |    4 ||    2 |    4 ||    4 |    4 ||    1 |    5 ||    2 |    5 ||    4 |    5 ||    1 |    6 ||    2 |    6 ||    4 |    6 |+------+------+12 rows in set (0.00 sec)

4. 總結

以需求驅動技術，技術本身沒有優略之分，只有業務之分。

本文出自 “sea” 部落格，請務必保留此出處http://lisea.blog.51cto.com/5491873/1943732

MySQL DML操作--------多表聯集查詢實戰