SQL进阶之联合查询

概述

为了获取更多相关信息，需要从两个或者多个表进行查询，通过联合查询可以把不同表之间的数据交叉捆绑在一起。

MySQL安装

本文以Mac系统为例

1. 打开terminal，输入homebrew安装命令

$ brew install mysql

2. 设置MySQL服务自启动

$ brew services start mysql

更多安装说明可参考官方文档：https://dev.mysql.com/doc/refman/8.0/en/macos-installation-notes.html

数据准备

1. 进入MySQL

$ mysql -uroot

2. 创建数据库

> create database join_test;
Query OK, 1 row affected (0.000 sec)

3. 选择使用上一步创建的数据库

> show databases;
> use join_test;
Database changed

4. 创建表staff_info，插入数据

> create table `staff_info`(
  `id` bigint(32) unsigned not null auto_increment, 
  `staff_name` varchar(64), 
  `staff_no` int, 
  `staff_sex` varchar(8),
  primary key (`id`)
  );
Query OK, 0 rows affected (0.029 sec)

> insert into `staff_info`
  (`staff_name`,`staff_no`,`staff_sex`)
  values
  ('Robert', 1, 'male'),
  ('John', 2, 'male'),
  ('Lisa', 3, 'female')
  ;
Query OK, 3 rows affected (0.002 sec)
Records: 3  Duplicates: 0  Warnings: 0

5. 查看staff_info表数据

> select * from staff_info;
+----+------------+----------+-----------+
| id | staff_name | staff_no | staff_sex |
+----+------------+----------+-----------+
|  1 | Robert     |        1 | male      |
|  2 | John       |        2 | male      |
|  3 | Lisa       |        3 | female    |
+----+------------+----------+-----------+
3 rows in set (0.000 sec)

6. 创建表staff_kaoqin，插入数据

> CREATE TABLE `staff_kaoqin` (
  `id` bigint(32) unsigned NOT NULL AUTO_INCREMENT,
  `staff_no` int,
  `staff_attend` varchar(256) COMMENT '员工考勤',
  PRIMARY KEY (`id`)
  );

> insert into `staff_kaoqin`
    (`staff_no`,`staff_attend`)
    values
    ('1','22'),
    ('3','22'),
    ('5','21');

Query OK, 3 rows affected (0.002 sec)
Records: 3 Duplicates: 0 Warnings: 0

7. 查看staff_kaoqin表数据

> select * from staff_kaoqin;
+----+----------+--------------+
| id | staff_no | staff_attend |
+----+----------+--------------+
|  1 |        1 | 22           |
|  2 |        3 | 22           |
|  3 |        5 | 21           |
+----+----------+--------------+
3 rows in set (0.000 sec)

联合查询分类

SQL Inner Join - 内联

> select * from staff_info inner join staff_kaoqin on staff_info.staff_no=staff_kaoqin.staff_no;
+----+------------+----------+-----------+----+----------+--------------+
| id | staff_name | staff_no | staff_sex | id | staff_no | staff_attend |
+----+------------+----------+-----------+----+----------+--------------+
|  1 | Robert     |        1 | male      |  1 |        1 | 22           |
|  3 | Lisa       |        3 | female    |  2 |        3 | 22           |
+----+------------+----------+-----------+----+----------+--------------+
2 rows in set (0.000 sec)

如果表中有至少一个匹配，则返回行

SQL Left Join - 左联

> select * from staff_info left join staff_kaoqin on staff_info.staff_no=staff_kaoqin.staff_no;
+----+------------+----------+-----------+------+----------+--------------+
| id | staff_name | staff_no | staff_sex | id   | staff_no | staff_attend |
+----+------------+----------+-----------+------+----------+--------------+
|  1 | Robert     |        1 | male      |    1 |        1 | 22           |
|  3 | Lisa       |        3 | female    |    2 |        3 | 22           |
|  2 | John       |        2 | male      | NULL |     NULL | NULL         |
+----+------------+----------+-----------+------+----------+--------------+
3 rows in set (0.000 sec)

即使右表中没有匹配，也从左表返回所有的行

SQL Right Join - 右连

> select * from staff_info right join staff_kaoqin on staff_info.staff_no=staff_kaoqin.staff_no;
+------+------------+----------+-----------+----+----------+--------------+
| id   | staff_name | staff_no | staff_sex | id | staff_no | staff_attend |
+------+------------+----------+-----------+----+----------+--------------+
|    1 | Robert     |        1 | male      |  1 |        1 | 22           |
|    3 | Lisa       |        3 | female    |  2 |        3 | 22           |
| NULL | NULL       |     NULL | NULL      |  3 |        5 | 21           |
+------+------------+----------+-----------+----+----------+--------------+
3 rows in set (0.001 sec)

即使左表中没有匹配，也从右表返回所有的行

SQL Full Join - 全连

> select * from staff_info full join staff_kaoqin;
+----+------------+----------+-----------+----+----------+--------------+
| id | staff_name | staff_no | staff_sex | id | staff_no | staff_attend |
+----+------------+----------+-----------+----+----------+--------------+
|  1 | Robert     |        1 | male      |  1 |        1 | 22           |
|  2 | John       |        2 | male      |  1 |        1 | 22           |
|  3 | Lisa       |        3 | female    |  1 |        1 | 22           |
|  1 | Robert     |        1 | male      |  2 |        3 | 22           |
|  2 | John       |        2 | male      |  2 |        3 | 22           |
|  3 | Lisa       |        3 | female    |  2 |        3 | 22           |
|  1 | Robert     |        1 | male      |  3 |        5 | 21           |
|  2 | John       |        2 | male      |  3 |        5 | 21           |
|  3 | Lisa       |        3 | female    |  3 |        5 | 21           |
+----+------------+----------+-----------+----+----------+--------------+
9 rows in set (0.001 sec)

只要其中一个表中存在匹配，就返回行