MySQL JOIN 语句

使用 MySQL 的 JOIN 在两个或多个表中查询数据

可以在 SELECT, UPDATE 和 DELETE 语句中使用 MySQL 的 JOIN 来联合多表查询

JOIN 按照功能大致分为如下三类：

  • INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录
  • LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录
  • RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录

INNER JOIN

INNER JOIN 返回两个表中满足连接条件的匹配行，以下是 INNER JOIN 语句的基本语法：

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

参数说明：

  • column1, column2, ... 是你要选择的列的名称，如果使用 * 表示选择所有列
  • table1, table2 是要连接的两个表的名称
  • table1.column_name = table2.column_name 是连接条件，指定了两个表中用于匹配的列

以下示例在之前创建的RoboMaster数据库下进行操作

当前数据库数据

// Username数据表
mysql> select * from Username;
+------+------------+-----------+
| id   | Username   | Password  |
+------+------------+-----------+
|    2 | xin        | 123456789 |
|    1 | steins_xin | 123456    |
|    1 | s_xin      | 123456    |
|    2 | steins     | 123456789 |
+------+------------+-----------+
4 rows in set (0.00 sec)

// test数据表
mysql> select * from test;
+------+------------+--------------+
| id   | Username   | Password     |
+------+------------+--------------+
|    0 | steins_xin | 1234aaaaaa56 |
|    1 | steinsxin  | 123456       |
+------+------------+--------------+
2 rows in set (0.00 sec)

示例：

  1. INNER JOIN 基本语法
SELECT test.Password , test.id
FROM username
INNER JOIN test ON test.username = username.username;

获取test中与username数据表相同名字的行里面的Password 和id

结果：

mysql> SELECT test.Password , test.id
    -> FROM username
    -> INNER JOIN test ON test.username = username.username;
+--------------+------+
| Password     | id   |
+--------------+------+
| 1234aaaaaa56 |    0 |
+--------------+------+
1 row in set (0.01 sec)
  1. 使用表别名：
SELECT o.Password , o.id
FROM test as o
INNER JOIN username as c ON o.username = c.username;

以上 SQL 语句使用表别名 o 和 c 作为 test 和 username 表的别名。

结果：

mysql> SELECT o.Password , o.id
    -> FROM test as o
    -> INNER JOIN username as c ON o.username = c.username;
+--------------+------+
| Password     | id   |
+--------------+------+
| 1234aaaaaa56 |    0 |
+--------------+------+
1 row in set (0.00 sec)
  1. 多表 INNER JOIN：
SELECT username.id, test.username, new_test.password
FROM username
INNER JOIN test ON username.customer_id = test.customer_id
INNER JOIN new_test ON test.order_id = new_test.order_id
  1. 使用 WHERE 子句进行过滤：
SELECT username.id, test.username
FROM test 
INNER JOIN username ON test.username = username.username
WHERE test.id >= 0;

结果：

mysql> SELECT username.id, test.username
    -> FROM test
    -> INNER JOIN username ON test.username = username.username
    -> WHERE test.id >= 0;
+------+------------+
| id   | username   |
+------+------------+
|    1 | steins_xin |
+------+------------+
1 row in set (0.00 sec)

LEFT JOIN

LEFT JOIN 返回左表的所有行，并包括右表中匹配的行，如果右表中没有匹配的行，将返回 NULL 值，以下是 LEFT JOIN 语句的基本语法：

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

示例：

  1. 简单的 LEFT JOIN：
SELECT username.id, username.username, test.id
FROM username
LEFT JOIN test ON username.username = test.username;

返回左表(username)所有数据，只有右表(test)匹配的数据才会有id出现，不然为NULL

结果：

mysql> SELECT username.id, username.username, test.id
    -> FROM username
    -> LEFT JOIN test ON username.username = test.username;
+------+------------+------+
| id   | username   | id   |
+------+------------+------+
|    2 | xin        | NULL |
|    1 | steins_xin |    0 |
|    1 | s_xin      | NULL |
|    2 | steins     | NULL |
+------+------------+------+
4 rows in set (0.00 sec)
  1. 使用表别名：
SELECT u.id, u.username, t.id
FROM username as u
LEFT JOIN test as t ON u.username = t.username;

以上 SQL 语句使用表别名 u 和 t 分别代替 username 和 test 表的名称。

结果：

mysql> SELECT u.id, u.username, t.id
    -> FROM username as u
    -> LEFT JOIN test as t ON u.username = t.username;
+------+------------+------+
| id   | username   | id   |
+------+------------+------+
|    2 | xin        | NULL |
|    1 | steins_xin |    0 |
|    1 | s_xin      | NULL |
|    2 | steins     | NULL |
+------+------------+------+
4 rows in set (0.00 sec)
  1. 多表 LEFT JOIN：
SELECT username.id, test.username, new_test.password
FROM username
INNER JOIN test ON username.customer_id = test.customer_id
INNER JOIN new_test ON test.order_id = new_test.order_id
  1. 使用 WHERE 子句进行过滤：
SELECT username.id, username.username, test.id
FROM username
LEFT JOIN test ON username.username = test.username
WHERE username.id <= 0 OR test.id IS NOT NULL;

以上SQL语句将左表的输出限制住，但是OR条件下，有一个数据会匹配成功不为空

结果：

mysql> SELECT username.id, username.username, test.id
    -> FROM username
    -> LEFT JOIN test ON username.username = test.username
    -> WHERE username.id <= 0 OR test.id IS NOT NULL;
+------+------------+------+
| id   | username   | id   |
+------+------------+------+
|    1 | steins_xin |    0 |
+------+------------+------+
1 row in set (0.00 sec)

RIGHT JOIN

RIGHT JOIN 返回右表的所有行，并包括左表中匹配的行，如果左表中没有匹配的行，将返回 NULL 值，以下是 RIGHT JOIN 语句的基本语法：

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

RIGHT JOIN 并不经常使用，因为它可以用 LEFT JOIN 和表的顺序交换来实现相同的效果

results matching ""

    No results matching ""