mysql记录所有dml操作_MySQL DML操作--------多表联合查询实战

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 可以省写成 joinmysql> 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 joinmysql> 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  joinmysql> 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. 总结

以需求驱动技术，技术本身没有优略之分，只有业务之分。