nodejs 教程三 （接入mysql

接着教程二
进入myApp项目下新建文件mysql.js并键入如下代码：

1. 创建

var mysql  = require('mysql');  //调用MySQL模块
//创建一个connection
var connection = mysql.createConnection({    
    connectionLimit: 50,
    host: 'localhost',
    user: 'root',
    password: '123',
    database: 'nodesample'
});

//创建一个connection
connection.connect(function(err){
    if(err){       
        console.log('[query] - :'+err);
        return;
    }
    console.log('[connection connect]  succeed!');
}); 

//执行SQL语句
connection.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
    if (err) {
        console.log('[query] - :'+err);
        return;
    }
    console.log('The solution is: ', rows[0].solution); 
}); 

//关闭connection
connection.end(function(err){
    if(err){       
        return;
    }
    console.log('[connection end] succeed!');
});

确保安装了mysql模块的情况下（npm install mysql --save），接着执行命令

node mysql

结果返回：

image

2. 增加
   这里需要打开mysql链接数据库新建一个表名为mytable的数据表，表结构如下：
   、、、、、、、、、、、、、
   | id | name | age | address |其中id为主键。将mysql.js里面修改为如下并在命令行执行命令node mysql：

var mysql = require('mysql');
var connection = mysql.createConnection({
    connectionLimit: 50,
    host: 'localhost',
    user: 'root',
    password: '123',
    database: 'nodesample'
});

connection.connect();

var addVip = 'insert into mytable(name, age, address) values(?,?,?)';
var param = ['测试',100,'测试'];
connection.query(addVip, param, function(error, result){
    if(error)
    {
        console.log(error.message);
    }else{
        console.log('insert id: '+result.insertId);
    }
});
connection.end();

结果返回 ：

image

刷新数据库查看已经增加了一条数据：

image

3. 删除
   将mysql.js文件修改为如下并在命令行执行node mysql

var mysql = require('mysql');
var connection = mysql.createConnection({
    connectionLimit: 50,
    host: 'localhost',
    user: 'root',
    password: '123',
    database: 'nodesample'
});

connection.connect();

var addVip = 'delete from mytable where id = 2';
connection.query(addVip, function(error, result){
    if(error)
    {
        console.log(error.message);
    }else{
        console.log('affectedRows: '+result.affectedRows);
    }
});

connection.end();

返回结果如下：

image

image

4. 查找
   将mysql.js文件修改为如下并在命令行执行node mysql

ql = require("mysql");
var connection = mysql.createConnection({
    connectionLimit: 50,
    host: 'localhost',
    user: 'root',
    password: '123',
    database: 'nodesample'
});

connection.connect();
connection.query('select * from mytable', function(error, results, fields){
    if (error) {
        throw error;
    }
    if (results) {
        for(var i = 0; i < results.length; i++)
        {
            console.log('%s\t%s',results[i].name,results[i].id);
        }
    }
});

connection.end();

结果返回如下：

image

image

5. 修改
   将mysql.js文件修改为如下并在命令行执行node mysql

= require('mysql');
var connection = mysql.createConnection({
    connectionLimit: 50,
    host: 'localhost',
    user: 'root',
    password: '123',
    database: 'nodesample'
});
connection.connect();
var userSql = "update mytable set age = age-1 where id = ?";
var param = [1000, 2];
connection.query(userSql, param, function (error, result) {
    if(error)
    {
        console.log(error.message);
    }else{
        console.log('affectedRows: '+result.affectedRows);
    }
});
connection.end();

结果返回如下

image

image

image