//执行多表结合查询
var mysql = require('mysql');var connection =mysql.createConnection({
host :'localhost',
port :3306,
database :'test',
user :'root',
password :''})
connection.connect(function(err){if(err){
console.log('与mysql数据库建立连接失败');
}else{
console.log('与mysql数据库建立连接成功');
connection.query('select admin.id,admin.username,admin.password,user.id,user.username from admin inner join user on admin.username = user.username',function(err,result){if(err){
console.log('查询数据失败');
}else{
console.log('查询数据成功');
console.log(result);
connection.end();
}
})
}
})//[{id:2,username:'kong',password:'123'}]//注意:联合查询时,select + ...字段,如果字段名相同(虽然表名不同)会使后一个字段覆盖前一个字段,产生我们不想要的结果//以上例子admin表和user表的id、username字段由于名字相同,导致前面的被覆盖,最后查询出来的结果是user.id、user.username、admin.password//使用*也会导致覆盖问题
//解决办法//方案一(对重复的字段使用别名)
var mysql = require('mysql');var connection =mysql.createConnection({
host :'localhost',
port :3306,
database :'test',
user :'root',
password :''})
connection.connect(function(err){if(err){
console.log('与mysql数据库建立连接失败');
}else{
console.log('与mysql数据库建立连接成功');
connection.query('select admin.id,admin.username,admin.password,user.id id1,user.username username1 from admin inner join user on admin.username = user.username',function(err,result){if(err){
console.log('查询数据失败');
}else{
console.log('查询数据成功');
console.log(result);
connection.end();
}
})
}
})//注意,select + ...字段使用了别名id1和username1//[{id:1,username:'kong',password:'123',id1:2,username1:'kong'}]
//方案二(使用nestTables属性并将属性值设定为true)
var mysql = require('mysql');var connection =mysql.createConnection({
host :'localhost',
port :3306,
database :'test',
user :'root',
password :''})
connection.connect(function(err){if(err){
console.log('与mysql数据库建立连接失败');
}else{
console.log('与mysql数据库建立连接成功');
connection.query({sql:'select admin.id,admin.username,admin.password,user.id,user.username from admin inner join user on admin.username = user.username',nestTables:true},function(err,result){if(err){
console.log('查询数据失败');
}else{
console.log('查询数据成功');
console.log(result);
connection.end();
}
})
}
})//注意和方案一查询的数据格式不一样//[{admin:{id:1,username:'kong',password:'123'},user:{id:2,username:'kong'}}]
//方案三(使用nestTables属性并将属性值设定为一个分隔字符)
var mysql = require('mysql');var connection =mysql.createConnection({
host :'localhost',
port :3306,
database :'test',
user :'root',
password :''})
connection.connect(function(err){if(err){
console.log('与mysql数据库建立连接失败');
}else{
console.log('与mysql数据库建立连接成功');
connection.query({sql:'select admin.id,admin.username,admin.password,user.id,user.username from admin inner join user on admin.username = user.username',nestTables:'_'},function(err,result){if(err){
console.log('查询数据失败');
}else{
console.log('查询数据成功');
console.log(result);
connection.end();
}
})
}
})//[{admin_id:1,admin_username:'kong',admin_password:'123',user_id:2,user_username:'kong'}]