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

  1. 创建
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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），接着执行命令

1
node mysql

结果返回：
image

  1. 增加
    这里需要打开mysql链接数据库新建一个表名为mytable的数据表，表结构如下：
    、、、、、、、、、、、、、
    | id | name | age | address |其中id为主键。将mysql.js里面修改为如下并在命令行执行命令node mysql：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    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

  1. 删除
    将mysql.js文件修改为如下并在命令行执行node mysql
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    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

  1. 查找
    将mysql.js文件修改为如下并在命令行执行node mysql
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    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

  1. 修改
    将mysql.js文件修改为如下并在命令行执行node mysql
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    = 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