简单使用Node.js连接MySql数据库进行增删改查

连接数据库

 1 //引入mysql（既然要使用它，当然要引入啦）
 2 const mysql = require('mysql'); 
 3  
 4 //连接mysql,option可很具情况自行配置
 5 const option = {
 6   host: "主机地址",
 7   user: "Mysql用户名",
 8   password: "Mysql用户密码",
 9   database: "数据库名称",
10   port: '端口号',
11   connectTimeout: 5000, // 连接超时
12 };
13  
14 const connection = mysql.createConnection(option);
15  
16 //连接开始
17 connection.connect(function(err){
18     if(err){
19         console.log("连接失败");
20     }
21     else{
22         console.log('连接成功');
23     }
24 });

增

var userAddSql = 'INSERT INTO userinfo(Id,UserName,UserPass) VALUES(0,?,?)';
var userAddSql_Params = ['Wilson','abcd'];


//增
 connection.query(userAddSql,userAddSql_Params,function(err,result){
     if(err){
         console.log('[INSERT ERROR] - ',err.message);
        return;
    }
    
     console.log('--------------------------INSERT----------------------------');
     console.log('INSERT  ID:',result);
     console.log('-----------------------------------------------------------------\n\n');
 });

 

改

var userModSql = 'UPDATE userinfo SET UserName =?,UserPass = ?  WHERE Id = ?';
var userModSql_Params = ['钟馗','5678',1];

//改

connection.query(userModSql,userModSql_Params,function(err,result){
if(err){
    console.log('[UPDATE ERROR] - ',err.message);
    return;
} 
  console.log('--------------------------UPDATE----------------------------');
  console.log('UPDATE affectedRows',result);
  console.log('-----------------------------------------------------------------\n\n');
});

 

查

var userGetSql = 'SELECT * FROM userinfo';
 
 //查
 connection.query(userGetSql,function(err,result){
    if(err){
        console.log('[SELECT ERROR] - ',err.message);
     return;
    } 
    
    console.log('--------------------------SELECT----------------------------');
    console.log(result);        
    console.log('-----------------------------------------------------------------\n\n');  
 });

 

删

var userDelSql = 'DELETE FROM userinfo';

//删
connection.query(userDelSql,function(err,result){
if (err) {
    console.log('[DELETE ERROR] - ',err.message);
    return;
} 

console.log('--------------------------DELETE----------------------------');
console.log('DELETE affectedRows',result);
console.log('-----------------------------------------------------------------\n\n');  
});

结束连接

connection.end()

 

完整代码

 1 //引入mysql（既然要使用它，当然要引入啦）
 2 const mysql = require('mysql'); 
 3  
 4 //连接mysql,option可很具情况自行配置
 5 const option = {
 6   host: "101.201.145.134",
 7   user: "TestDB",
 8   password: "Wa8emaaN7ShSkcyR",
 9   database: "TestDB",
10   port: '3306',
11   connectTimeout: 5000, // 连接超时
12 };
13  
14 const connection = mysql.createConnection(option);
15  
16 //连接开始
17 connection.connect(function(err){
18     if(err){
19         console.log("连接失败");
20     }
21     else{
22         console.log('连接成功');
23     }
24 });
25  
26  
27 //这里就是增删改查的地方啦
28  
29 var userAddSql = 'INSERT INTO userinfo(Id,UserName,UserPass) VALUES(0,?,?)';
30 var userAddSql_Params = ['Wilson','abcd'];
31 
32 
33 //增
34 connection.query(userAddSql,userAddSql_Params,function(err,result){
35     if(err){
36         console.log('[INSERT ERROR] - ',err.message);
37         return;
38     }
39     
40     console.log('--------------------------INSERT----------------------------');
41     console.log('INSERT  ID:',result);
42     console.log('-----------------------------------------------------------------\n\n');
43 });
44  
45  
46  
47  
48  var userModSql = 'UPDATE userinfo SET UserName =?,UserPass = ?  WHERE Id = ?';
49  var userModSql_Params = ['钟馗','5678',1];
50  
51  //改
52  
53  connection.query(userModSql,userModSql_Params,function(err,result){
54     if(err){
55         console.log('[UPDATE ERROR] - ',err.message);
56         return;
57     } 
58       console.log('--------------------------UPDATE----------------------------');
59       console.log('UPDATE affectedRows',result);
60       console.log('-----------------------------------------------------------------\n\n');
61  });
62  
63  
64  var userGetSql = 'SELECT * FROM userinfo';
65  
66  //查
67  connection.query(userGetSql,function(err,result){
68     if(err){
69         console.log('[SELECT ERROR] - ',err.message);
70      return;
71     } 
72     
73     console.log('--------------------------SELECT----------------------------');
74     console.log(result);        
75     console.log('-----------------------------------------------------------------\n\n');  
76  });
77  
78  
79  var userDelSql = 'DELETE FROM userinfo';
80  
81  //删
82  connection.query(userDelSql,function(err,result){
83     if (err) {
84         console.log('[DELETE ERROR] - ',err.message);
85         return;
86     } 
87     
88     console.log('--------------------------DELETE----------------------------');
89     console.log('DELETE affectedRows',result);
90     console.log('-----------------------------------------------------------------\n\n');  
91  });
92  
93  
94  //结束连接
95  connection.end()

 

posted @ 2022-07-12 16:49  枫亦  阅读(8)  评论(0)    收藏  举报