MySQL DML操作--------多表联合查询实战

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL DML操作--------多表联合查询实战

技术小胖子 2017-11-08 17:59:00 浏览904
展开阅读全文

1. 背景

   * 多表联合查询是把不同表的记录到一起的一种方式

   * 在SQL标准中规划的联合(join)大致分内连接,外连接,全连接。其中外连接又分左外连接,右外连接。


2. 内连接例子 (inner join) [ 员工 --> 部门 ] 

   * 查看员工表[ employees ]和部门表[ departments ]结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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    |                |
+-----------+---------------+------+-----+---------+----------------+
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    |                |
+-------+-------------+------+-----+---------+----------------+
rows in set (0.00 sec)


   * 查看员工表[ employees ]和部门表[ departments ]数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 |
+----+-------+-----+-----+-----------+
rows in set (0.00 sec)
 
mysql> select from departments;
+----+------+
| id | name |
+----+------+
|  1 | dev  |
|  2 | test |
|  3 | ops  |
+----+------+
rows in set (0.00 sec)


   * 查询并显示所有员工id, 姓名,姓别,年龄,所在部门 (方法一)

1
2
3
4
5
6
7
8
9
10
11
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

1
2
3
4
5
6
7
8
9
10
11
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表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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表数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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

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


   * 右外连接查询 以b表为基础,显示b表所有数据,a表有的组合,没有显示NULLL

        right outer join 可以写成 right  join

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


   * 完全(交叉)连接查询

     没有 where 子句的交叉联接将产生联接所涉及的表的笛卡尔积。

     第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。

    (a和b交叉连接产生3*4=12条记录) 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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. 总结


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




      本文转自asd1123509133 51CTO博客,原文链接:http://blog.51cto.com/lisea/1943732,如需转载请自行联系原作者




网友评论

登录后评论
0/500
评论
技术小胖子
+ 关注