代码改变世界

理解mysql执行多表联合查询

2019-04-25 23:55  龙恩0707  阅读(14657)  评论(0编辑  收藏  举报

阅读目录

查询多张表一般有如下连接方法:

1)内连接:join, inner join
2)外连接:left join, left outer join, right join, right outer join, union;
3) 交叉连接:cross join

join的含义是:用于多表中字段直接的联系。

基本语法如下:

select * from table1 inner|left|right join table2 on conditiona

说明:table1 是指第一张表。table2是指第二张表。 on 后面的含义是:满足这个条件。

join按照功能可以分成如下三类:

1) inner join(内连接,或叫等值连接):取得两个表中存在连接匹配关系的记录。
2)left join(左连接):取得左表(table1)完全记录,右表(table2)如果有条件相符合的记录就匹配,否则为null;
3) right join(右连接): 取得右表(table2)完全记录,左表(table1)如果有条件相符合的记录就匹配,否则为null;

我们来看一个列子:

如下是user1表和user2表中的数据如下所示:

一:inner join(内连接)

基本语法如下:

select * from user inner join user2 on user.age = user2.age;

如上代码的含义是:查询user表和user2表(内连接) 通过on后面的条件查询,user表中的age字段 等于 user2表中的age这个条件满足的话,才会把两个表中的数据都查询出来的,否则的话,查询数据就是空哦。如下所示:

二:left join(左连接)

基本语法如下:

select * from user left join user2 on user.age = user2.age;

如上代码的含义是:如果user表中的age字段值 等于 user2表中的age字段的值的话,那么就会把所有的数据都查询出来,如果该表中的字段age值不相等的话,那么user表中的记录会全部查询出来,user2表中的所有字段为null值。

如下所示:

三:right join(右连接)

基本语法如下:

select * from user right join user2 on user.age = user2.age;

和上面的 left join 的含义相反,就是说 如果条件相等的话,就会把两张表的数据都查询出来,如果不相等的话,就会把第二张表的数据查询出来,第一张表的数据字段值为null.

如下所示:

四:cross join(交叉连接)

交叉连接,得到的结果是两个表的乘积; 效果和 inner join 类似,如下所示:

五:union操作

select * from user left join user2 on user.username = user2.username union select * from user right join user2 on user.username = user2.username;

上面代码的含义是:查询user表和user2表,如果该两张表的username值相等的话,使用left join(左链接) 和 right join(右连接),如果都相等的话,就把两张表所有的数据查询出来。否则的话,两张表字段不相等的话,那么第一个条件 left join查询后的数据就是 按照user表查询出所有的数据,user2表中的数据字段值为null;union的含义的意思可以理解为或者的意思,我们可以理解它为or的意思,第二个条件是使用 right join 连接符,意思是如果username字段不相等的话,就按照第二张表的数据全部查询出来,第一张表的所有字段查询值为null。如下所示:

六:node+mysql 多表联合查询

在mysql模块中,我们可以使用Connection对象的query方法的第一个参数编写sql语句来执行多表联合查询,但是如果在sql语句中指定了相同的字段(比如相同的字段在不同的表中),那么在默认的情况下,后面的字段会覆盖前面的字段查询出来的数据。从而不是我们想要的结果。我们是想要把这两张表的数据同时查询出来。为了演示下,我们新建两张表,一张是user表,该表有4个字段,分别是id,username, password, age; 另外一张表是user2表,该表有四个字段,分别是id,username,password,age等字段。如下所示:

我们现在使用Connection对象的query方法结合查询这两张表中的所有数据,查询语句如下:

select * from user inner join user2 where user.age = user2.age;

因此所有的代码如下所示:

const mysql = require('mysql');
/*
 createConnection方法创建一个表示与Mysql数据库服务器之间连接的 Connection对象
*/
const connection = mysql.createConnection({
  host: 'localhost',
  port: 3306,
  database: 'my_db',
  user: 'root',
  password: '123456'
});

/*
 connection 对象被创建之后,可以使用该对象的 connect方法建立mysql数据库服务器之间的连接
*/
connection.connect((err) => {
  if (err) {
    console.log(err);
    console.log('数据库连接失败');
  } else {
    console.log('数据库连接成功');
    const sql = 'select * from user inner join user2 where user.age = user2.age';
    connection.query(sql, (err, res) => {
      if (err) {
        console.log('查询数据失败');
      } else {
        console.log(res);
        connection.end();
      }
    }); 
  }
});

然后我们在命令行中,运行 node app.js 后,可以看到如下所示:

如上图我们可以看到,查询后的数据就是user2表中的数据,user1表的数据被覆盖了,因为user表中的字段和user2表中的字段是一样的,虽然在不同的表中,但是因为字段一样,后面的user2表会把user表中的字段覆盖掉。

解决方法有如下几种:

1. 第一种是给sql语句重复的字段使用别名。这种方法比较繁琐,因为如果表中有多个字段相同的话,要设置多个别名。这种方法不折腾。

2. 第二种方法为在query方法中使用 nestTables属性,并将属性值设置为true。因此会将两个表中的数据以两个对象的形式输出来。如下sql语句改为如下:

{
  sql: 'select * from user inner join user2 where user.age = user2.age',
  nestTables: true
}

代码如下:

const mysql = require('mysql');
/*
 createConnection方法创建一个表示与Mysql数据库服务器之间连接的 Connection对象
*/
const connection = mysql.createConnection({
  host: 'localhost',
  port: 3306,
  database: 'my_db',
  user: 'root',
  password: '123456'
});

/*
 connection 对象被创建之后,可以使用该对象的 connect方法建立mysql数据库服务器之间的连接
*/
connection.connect((err) => {
  if (err) {
    console.log(err);
    console.log('数据库连接失败');
  } else {
    console.log('数据库连接成功');
    const sql = {
      sql: 'select * from user inner join user2 where user.age = user2.age',
      nestTables: true
    };
    connection.query(sql, (err, res) => {
      if (err) {
        console.log('查询数据失败');
      } else {
        console.log(res);
        connection.end();
      }
    }); 
  }
});

然后我们运行结果如下所示:

如上图可以看到,包含两个对象user和user2,对象里面是各自的数据。

3. 第三种方法是为在query方法中使用nestTables属性并将属性值设定为一个分割字符,这会将被结合的两张表中的数据以一个对象的形式输出。该对象的属性名 为 字段所属表名+分割字符+字段名。

sql语句改为如下:

{
  sql: 'select * from user inner join user2 where user.age = user2.age',
  nestTables: '_'
}

所有代码如下:

const mysql = require('mysql');
/*
 createConnection方法创建一个表示与Mysql数据库服务器之间连接的 Connection对象
*/
const connection = mysql.createConnection({
  host: 'localhost',
  port: 3306,
  database: 'my_db',
  user: 'root',
  password: '123456'
});

/*
 connection 对象被创建之后,可以使用该对象的 connect方法建立mysql数据库服务器之间的连接
*/
connection.connect((err) => {
  if (err) {
    console.log(err);
    console.log('数据库连接失败');
  } else {
    console.log('数据库连接成功');
    const sql = {
      sql: 'select * from user inner join user2 where user.age = user2.age',
      nestTables: '_'
    };
    connection.query(sql, (err, res) => {
      if (err) {
        console.log('查询数据失败');
      } else {
        console.log(res);
        connection.end();
      }
    }); 
  }
});

然后运行结果如下: