Node.js 连接 MySQL ，常用SQL语句全套攻略

安装驱动

$ 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://www.dingsky.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://www.dingsky.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();