install

Mysql installation
http://ftp.ntu.edu.tw/MySQL/Downloads/MySQLInstaller/
Version: mysql-installer-community-5.7.17.0.msi
Visual Interface Tool: MySQL-Front_V5.4.4.153_Setup
Installation tutorial: http://jingyan.baidu.com/article/363872ec2e27076e4ba16fc3.html
About passwords: admin/admin root/admin
http://www.jb51.net/article/71888.htm
http://blog.csdn.net/kindroid/article/details/51018107

Node.js interacts with MySQL in many libraries, specifically https://www.npmjs.org/search?q=mysql See.
Address: https://github.com/felixge/node-mysql
　　　　 https://www.npmjs.org/package/mysql

install

npm install mysql

Testing MySQL

Build a library and insert records

CREATE DATABASE IF NOT EXISTS learn_nodejs CHARACTER SET UTF8;

USE learn_nodejs;

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `UserName` varchar(64) NOT NULL COMMENT 'User name',
  `UserPass` varchar(64) NOT NULL COMMENT 'User password',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User Information Table';


show databases

Other: http://blog.csdn.net/u010882595/article/details/9666819

Noejs connects mysql -- 1_mysql.js

//1_mysql.js
var mysql = require('mysql');  //Call mysql module

//Create a connection

var connection = mysql.createConnection({
  host     : '192.168.41.36',
  user     : 'root',
  password : 'admin',
  port: '3306',
  database: 'learn_nodejs',
});

//Establish a connection
connection.connect(function(err){
    if(err){
        console.log('connection connect err - :'+err);
        return;
    }

        console.log('connection connect success!');
});


//Executing sql
connection.query('select 1 AS solution', function(err,rows,fields){
    if(err){
        console.log('connection query err - :'+err);
        return;
    }

        console.log('solution :'+rows[0].solution);
});


//Close connection
connection.end(function(err){
    if(err){
        console.log('connection end err - :'+err);
        return;
    }

        console.log('connection end success!');
});

Report errors:

Reason: Users are not empowered
Solve:
Main empowerment sql

1. GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
2.FLUSH PRIVILEGES;

Execution return display:

ps: Link here is another library at that time. It is regarded as a test of Library links. The real form library below is still learning_nodejs, which was written last week. Later, it was reorganized. The screenshots were not modified. That's all right. Here's a description.

Connection Options

host: host address (default: localhost)
user: Username
password: password
port: port number (default: 3306)
Database: database name
charset: Connect character sets (default:'UTF8_GENERAL_CI', note that all letters in the character set are capitalized)
localAddress: This IP is used for TCP connections (optional)
socketPath: Connect to the unix domain path, which is ignored when using host and port
Time zone: Time zone (default:'local')
Connection Timeout: Connection timeout (default: unlimited; unit: milliseconds)
stringifyObjects: Whether to serialize objects (default:'false'; security-related) https://github.com/felixge/node-mysql/issues/501)
TypeeCast: Whether to convert column values to native JavaScript type values (default: true)
queryFormat: Custom query statement formatting method https://github.com/felixge/node-mysql#custom-format
Support BigNumbers: When the database supports columns of type bigint or decimal, you need to set this option to true (default: false)
BigNumberStrings: Support BigNumbers and bigNumberStrings enable forced bigint or decimal columns to be returned as JavaScript string types (default: false)
dateStrings: Force timestamp,datetime,data type to return as a string type instead of JavaScript Date type (default: false)
debug: Open debugging (default: false)
Multiple Statements: Is it possible to have multiple MySQL statements in a query (default: false)
Flags: Used to modify connection flags, more details: https://github.com/felixge/node-mysql#connection-flags
ssl: Currently only Amazon RDS configuration files are bundled using ssl parameters (with crypto.createCredenitals parameter formats one to one) or a string containing ssl configuration file names

Achieve additions, deletions, modifications, checks

increase

//1_mysql.js
var mysql = require('mysql');  //Call mysql module

//Create a connection
var connection = mysql.createConnection({
  host     : '192.168.41.36',
  user     : 'root',
  password : 'admin',
  port: '3306',
  database: 'tiany_learnnodejs',
});

//Establish a connection
connection.connect(function(err){
    if(err){
        console.log('connection connect err - :'+err);
        return;
    }

        console.log('connection connect success!');
});

var  userAddSql = 'INSERT INTO userinfo(Id,UserName,UserPass) VALUES(0,?,?)';
var  userAddSql_Params = ['tiany', 'tiany'];
//increase
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('-----------------------------------------------------------------');  
});

//Close connection
connection.end(function(err){
    if(err){
        console.log('connection end err - :'+err);
        return;
    }

        console.log('connection end success!');
});

Successful implementation:

The database shows:

change

//3_mysql_update.js
var mysql = require('mysql');  //Call mysql module

//Create a connection
var connection = mysql.createConnection({
  host     : '192.168.41.36',
  user     : 'root',
  password : 'admin',
  port: '3306',
  database: 'learn_nodejs',
});

//Establish a connection
connection.connect(function(err){
  if(err){
    console.log('connection connect err - :'+err);
    return;
  }

    console.log('connection connect success!');
});

var userModSql = 'UPDATE userinfo SET UserName = ?,UserPass = ? WHERE Id = ?';
var userModSql_Params = ['liuzy', 'liuzy',1];
//change
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.affectedRows);
  console.log('-----------------------------------------------------------------');
});

//Close connection
connection.end(function(err){
  if(err){
    console.log('connection end err - :'+err);
    return;
  }

    console.log('connection end success!');
});

Implementation results:

check

//5_mysql_delete.js
var mysql = require('mysql');  //Call mysql module

//Create a connection
var connection = mysql.createConnection({
  host     : '192.168.41.36',
  user     : 'root',
  password : 'admin',
  port: '3306',
  database: 'learn_nodejs',
});

//Establish a connection
connection.connect(function(err){
    if(err){
        console.log('connection connect err - :'+err);
        return;
    }

        console.log('connection connect success!');
});

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

       console.log('--------------------------query----------------------------');
       console.log(result);        
       console.log('---------------------------------------------------------------');  
});

//Close connection
connection.end(function(err){
    if(err){
        console.log('connection end err - :'+err);
        return;
    }

        console.log('connection end success!');
});

Implementation results:

Delete

//4_mysql_query.js
var mysql = require('mysql');  //Call mysql module

//Create a connection
var connection = mysql.createConnection({
  host     : '192.168.41.36',
  user     : 'root',
  password : 'admin',
  port: '3306',
  database: 'learn_nodejs',
});

//Establish a connection
connection.connect(function(err){
    if(err){
        console.log('connection connect err - :'+err);
        return;
    }

        console.log('connection connect success!');
});

var  userDelSql = 'DELETE FROM userinfo';
//Delete
connection.query(userDelSql,function (err, result) {
        if(err){
          console.log('DELETE ERROR - ',err.message);
          return;
        }        

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

//Close connection
connection.end(function(err){
    if(err){
        console.log('connection end err - :'+err);
        return;
    }

        console.log('connection end success!');
});

Implementation results:

Two Methods and Differences of Ending Database Connection

There are actually two ways to end a connection: end(), destory().
　　end()
The end() method is executed after the queries are finished. The end() method receives a callback function. The queries execute errors and still end the connection. The errors will be returned to the err parameter of the callback function, which can be handled in the callback function.
　　destory()
Comparatively violent, no callback function, immediate execution, whether queries are completed or not!

Connection pool Pooling connections

You can see:
http://cnodejs.org/topic/58378543bde2b59e06141f5a
It will be explained in detail later when it comes to specific projects.

In addition:
When learning notes in nodejs (1), the reference source of notes has been written down. It is also considered to be standing on the shoulders of giants. Here is a link again. http://www.cnblogs.com/zhongweiv/p/nodejs_mysql.html Thank you again. porschev Great God Resources.