Node.js - MySQL 连接查询

本章将学习如何在 Node.js 应用程序中，针对 MySQL 数据库中的表实现 JOIN 查询。关系型数据库由多个相关的表组成，这些表通过公共列（称为外键列）链接在一起。MySQL 支持 JOIN 查询，可以使用它从 Node.js 应用程序中的多个表中提取和使用数据。与之前的示例一样，我们应该调用 mysql.query() 方法，并将 SQL 查询字符串传递给 mysql.query() 方法，该字符串应表示 MySQL JOIN 查询语法。

本章将使用以下表格：

Members 表

CREATE TABLE members (
   member_id INT AUTO_INCREMENT,
   name VARCHAR(100),
   PRIMARY KEY (member_id)
);

INSERT INTO members(name)
VALUES('John'),('Jane'),('Mary'),('David'),('Amelia');

Members 表数据：

mysql> select * from members;
+-----------+--------+
| member_id | name   |
+-----------+--------+
|         1 | John   |
|         2 | Jane   |
|         3 | Mary   |
|         4 | David  |
|         5 | Amelia |
+-----------+--------+

Committees 表

CREATE TABLE committees (
   committee_id INT AUTO_INCREMENT,
   name VARCHAR(100),
   PRIMARY KEY (committee_id)
);
INSERT INTO committees(name)
VALUES('John'),('Mary'),('Amelia'),('Joe');

Committees 表数据：

mysql> select * from committees;
+--------------+--------+
| committee_id | name   |
+--------------+--------+
|            1 | John   |
|            2 | Mary   |
|            3 | Amelia |
|            4 | Joe    |
+--------------+--------+

一些成员是委员会成员，而一些成员不是。另一方面，一些委员会成员在 members 表中，而一些成员不在。

MySQL INNER JOIN (内连接)

在 SELECT 语句中使用 inner join 子句的语法如下：

SELECT column_list
FROM table_1
INNER JOIN table_2 ON join_condition;

inner join 子句将第一个表中的每一行与第二个表中的每一行进行比较。如果两行的值满足连接条件，则在结果集中创建一个新行，其列包含来自两个表的两行的所有列，并将此新行包含在结果集中。换句话说，inner join 子句只包含来自两个表的匹配行。

如果连接条件使用等号 (=) 并且两个表中匹配的列名相同，则可以使用 USING 子句代替 ON 子句。

SELECT column_list
FROM table_1
INNER JOIN table_2 USING (column_name);

示例

在下面的 Node.js 代码中，我们将使用 members 和 Committees 表并获取它们的内连接。

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",
   database: "mydb"
});

var qry =`
SELECT 
   m.member_id, 
   m.name AS member, 
   c.committee_id, 
   c.name AS committee
FROM
   members m
INNER JOIN committees c ON c.name = m.name;
`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   con.query(qry, function (err, results) {
      if (err) throw err;
      results.forEach((row) => {
         console.log(JSON.stringify(row));
      });
   });

   con.end();
});

输出

{"member_id":1,"member":"John","committee_id":1,"committee":"John"}
{"member_id":3,"member":"Mary","committee_id":2,"committee":"Mary"}
{"member_id":5,"member":"Amelia","committee_id":3,"committee":"Amelia"}

MySQL LEFT JOIN (左连接)

左连接类似于内连接。使用左连接连接两个表时，左连接从左表开始选择数据。对于左表中的每一行，左连接都会与右表中的每一行进行比较。如果两行的值满足连接条件，则左连接子句将创建一个新行，其列包含两个表中两行的所有列，并将此行包含在结果集中。

如果两行的值不匹配，左连接子句仍然会创建一个新行，其列包含左表中行的列，而右表中行的列则为 NULL。

因此，左连接会选择左表中的所有数据，无论右表中是否存在匹配的行。

如果找不到右表中的匹配行，则左连接在结果集中使用来自右表的行的列的 NULL 值。

左连接子句的语法如下：

SELECT column_list 
FROM table_1 
LEFT JOIN table_2 USING (column_name);

让我们将 Node.js 代码中的查询字符串更改为以下语句：

示例

var qry =`
SELECT 
   m.member_id, 
   m.name AS member, 
   c.committee_id, 
   c.name AS committee
FROM
   members m
LEFT JOIN committees c USING(name);
`;

输出

{"member_id":1,"member":"John","committee_id":1,"committee":"John"}
{"member_id":2,"member":"Jane","committee_id":null,"committee":null}
{"member_id":3,"member":"Mary","committee_id":2,"committee":"Mary"}
{"member_id":4,"member":"David","committee_id":null,"committee":null}
{"member_id":5,"member":"Amelia","committee_id":3,"committee":"Amelia"}

不匹配的列将填充 NULL 数据。

要查找不是委员会成员的成员，您可以添加 WHERE 子句和 IS NULL 运算符。

SELECT 
   m.member_id, 
   m.name AS member, 
   c.committee_id, 
   c.name AS committee
FROM
   members m
LEFT JOIN committees c USING(name)
WHERE c.committee_id IS NULL;

MySQL RIGHT JOIN (右连接)

右连接子句类似于左连接子句，只是左表和右表的处理方式相反。右连接从右表开始选择数据，而不是从左表开始。

右连接子句选择右表中的所有行，并匹配左表中的行。如果右表中的一行没有左表中的匹配行，则左表的列在最终结果集中将为 NULL。

SELECT column_list 
FROM table_1 
RIGHT JOIN table_2 USING (column_name);

让我们修改查询字符串变量如下：

var qry =`
SELECT 
   m.member_id, 
   m.name AS member, 
   c.committee_id, 
   c.name AS committee
FROM
   members m
RIGHT JOIN committees c on c.name = m.name;
`;

结果返回两个表的 RIGHT JOIN：

{"member_id":1,"member":"John","committee_id":1,"committee":"John"}
{"member_id":3,"member":"Mary","committee_id":2,"committee":"Mary"}
{"member_id":5,"member":"Amelia","committee_id":3,"committee":"Amelia"}
{"member_id":null,"member":null,"committee_id":4,"committee":"Joe"}

打印页面