node 链接MySql数据库并 进行增删改查

在Navicat中创建数据库创建表

 那么就开始吧！

一、链接数据库

mysql - npmA node.js driver for mysql. It is written in JavaScript, does not require compiling, and is 100% MIT licensed.. Latest version: 2.18.1, last published: 3 years ago. Start using mysql in your project by running `npm i mysql`. There are 7130 other projects in the npm registry using mysql.https://www.npmjs.com/package/mysql创建一个项目，链接数据库

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'xbb123',
  database : 'test'
});
 
connection.connect();
 
connection.query('SELECT * from loan', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results);
});
 
connection.end();

 如果遇到一下问题，没有mysql模块，检查是否正常下载依赖：

 文件夹下建立依赖

 npm install mysql -g

二、 CRUD操作

// 新增
connection.query(`insert into user (account,password,creator,role_id,role_name) values ('ceshi','ceshi123','cy','1','approve')`, function (error, results, fields) {
    if (error) throw error;
    console.log(results);
  });

对于返回值 results 

完整代码

var mysql = require('mysql');
var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'cy111',
    database: 'test'
});

connection.connect();
// 查询
connection.query('SELECT * from user', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results);
});
//  create
connection.query(`insert into user (account,password,creator,role_id,role_name) values ('ceshi','ceshi123','cy','1','approve')`, function (error, results, fields) {
    if (error) throw error;
    console.log(results);
  });
// Update
connection.query(`update user set account = 'cyy' where id=21`, function (error, results, fields) {
    if (error) throw error;
    console.log(results);
});
// 删除
connection.query(`delete from user where id=22`, function (error, results, fields) {
    if (error) throw error;
    console.log(results);
});
connection.end();

三、关于查找操作的常用操作

精确查询 

let sql = 'SELECT * from user where role_id = ?';
connection.query(sql,[2], function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results);
});

 模糊查询 %xxx %xxx %xxx%

let sqlx = 'SELECT * from user where account like ?';
connection.query(sql,['%jia%'], function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results);
});

指定顺序 desc-倒序  asc-正序

let sqly = 'SELECT * from user order by reg_time desc';
connection.query(sqly, function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results);
});

分页      limit 起点[（当前页码-1）*数量]，容量

let sqlz = 'SELECT * from user order by reg_time desc limit 0,5';
connection.query(sqlz, function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results);
});

表数据集合 select 定义一个名称（不为空的列名字）from 表格 

let sqll = 'SELECT count(id) as total from user ';
connection.query(sqll, function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results);
});