====== Node.js 连接 MySQL ======


===== 安装驱动 =====
  $ npm install mysql

===== 常用mysql语句 =====
  // 查询表websites中所有内容（select后面的*代表输出所有字段，可设置需要显示的字段，逗号分隔）
  'SELECT * FROM websites';
  
  // 添加数据（id可以不填，默认自增长）
  'INSERT INTO websites(Id,name,url,alexa,country) VALUES(0,?,?,?,?)';
  
  // 修改内容
  'UPDATE websites SET name = ?,url = ? WHERE Id = ?';
  
  // 删除数据
  'DELETE FROM websites where id=6';
  
  // 连表查询，排序，分页（as定义表别名，语句可调用，asc升序，desc降序，默认asc）
  'SELECT * FROM `user` as u left JOIN grade as g on g.userid = u.id where u.age = 98 ORDER BY u.id, u.age DESC limit 0,2';
  
  // 模糊查询（like替换=，%代表0个或多个字符）
  'SELECT * FROM `user` WHERE username like "%hui%"'
  
  // 查询表数据的数量
  'SELECT count(0) FROM `user`

===== 连接数据库 =====
  var mysql = require('mysql');
  var connection = mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: '123456',
      database: 'test'
  });
    
  connection.connect();
  
===== 查询数据 =====
  var mysql = require('mysql');
  
  var connection = mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: '123456',
      port: '3306',
      database: 'test',
  });
  
  connection.connect();
  
  var sql = 'SELECT * FROM websites';
  
  connection.query(sql, function(err, result) {
      if (err) {
          console.log('[SELECT ERROR] - ', err.message);
          return;
      }
  
      console.log('--------------------------SELECT----------------------------');
      console.log(result);
      console.log('------------------------------------------------------------\n\n');
  });
  
  connection.end();
  
===== 添加数据 =====
  var mysql = require('mysql');
  
  var connection = mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: '123456',
      port: '3306',
      database: 'test',
  });
  
  connection.connect();
  
  var addSql = 'INSERT INTO websites(Id,name,url,alexa,country) VALUES(0,?,?,?,?)';
  var addSqlParams = ['菜鸟工具', 'https://c.runoob.com', '23453', 'CN'];
  connection.query(addSql, addSqlParams, function(err, result) {
      if (err) {
          console.log('[INSERT ERROR] - ', err.message);
          return;
      }
  
      console.log('--------------------------INSERT----------------------------');  
      console.log('INSERT ID:', result);
      console.log('-----------------------------------------------------------------\n\n');
  });
  
  connection.end();
  
===== 修改更新数据 =====
  var mysql = require('mysql');
  
  var connection = mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: '123456',
      port: '3306',
      database: 'test',
  });
  
  connection.connect();
  
  var modSql = 'UPDATE websites SET name = ?,url = ? WHERE Id = ?';
  var modSqlParams = ['菜鸟移动站', 'https://m.runoob.com', 6];
  connection.query(modSql, modSqlParams, function(err, result) {
      if (err) {
          console.log('[UPDATE ERROR] - ', err.message);
          return;
      }
      console.log('--------------------------UPDATE----------------------------');
      console.log('UPDATE affectedRows', result.affectedRows);
      console.log('-----------------------------------------------------------------\n\n');
  });
  
  connection.end();
  
===== 删除数据 =====
  var mysql = require('mysql');
  
  var connection = mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: '123456',
      port: '3306',
      database: 'test',
  });
  
  connection.connect();
  
  var delSql = 'DELETE FROM websites where id=6';
  connection.query(delSql, function(err, result) {
      if (err) {
          console.log('[DELETE ERROR] - ', err.message);
          return;
      }
  
      console.log('--------------------------DELETE----------------------------');
      console.log('DELETE affectedRows', result.affectedRows);
      console.log('-----------------------------------------------------------------\n\n');
  });
  
  connection.end();