MySQL DML操作--------多表聯(lián)合查詢實戰(zhàn)

1. 背景

   * 多表聯(lián)合查詢是把不同表的記錄到一起的一種方式

   * 在SQL標準中規(guī)劃的聯(lián)合(join)大致分內(nèi)連接，外連接，全連接。其中外連接又分左外連接，右外連接。

2. 內(nèi)連接例子 (inner join) [ 員工 --> 部門 ]　

   * 查看員工表[ employees ]和部門表[ departments ]結(jié)構(gòu)

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 ]數(shù)據(jù)

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. 外連接例子

   左外連接 [ 以左表為基礎(chǔ)，左表的全部數(shù)據(jù)，右表有的組合。右表沒有的為null ]

   右外連接 [ 以右表為基礎(chǔ)，右表的全部數(shù)據(jù)，左表有的組合。左表沒有的為null ]

   * 查看a表和b表結(jié)構(gòu)

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表數(shù)據(jù)

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表為基礎(chǔ)，顯示a表所有數(shù)據(jù)，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表為基礎(chǔ)，顯示b表所有數(shù)據(jù)，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 子句的交叉聯(lián)接將產(chǎn)生聯(lián)接所涉及的表的笛卡爾積。

     第一個表的行數(shù)乘以第二個表的行數(shù)等于笛卡爾積結(jié)果集的大小。

    （a和b交叉連接產(chǎn)生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. 總結(jié)

以需求驅(qū)動技術(shù)，技術(shù)本身沒有優(yōu)略之分，只有業(yè)務(wù)之分。