This article mainly introduces how to use Node. js to operate the mysql database for addition, deletion, modification, and query. If you need more information, please read the full text. The details are as follows:
Install the mysql Module
npm install mysql
Database preparation
The IP address of the machine where mysql server is located is 192.168.0.108. to log on to the account, use root @ 123456.
Create a test database in mysql
Create a users table in the test Database
Operation
Connect to database
var mysql=require('mysql');var connection = mysql.createConnection({host : '192.168.0.108',user : 'root',password : '123456',database : 'test1',port:'3306'});connection.connect();
Insert a user
var usr={name:'zhangsan',password:'pwdzhangsan',mail:'zhangsan@gmail.com'};connection.query('insert into users set ?', usr, function(err, result) {if (err) throw err;console.log('inserted zhangsan');console.log(result);console.log('\n');});
Update user with conditions
connection.query('update users set password="ddd" where name="zhangsan"', {password:'ppp'}, function(err, result) {if (err) throw err;console.log('updated zhangsan\'s password to ddd');console.log(result);console.log('\n');});
Delete a user with conditions
connection.query('delete from users where name="zhangsan"', {password:'ppp'}, function(err, result) {if (err) throw err;console.log('deleted zhangsan');console.log(result);console.log('\n');});
Query user, all
connection.query('select * from users', function(err, rows, fields) {if (err) throw err;console.log('selected after deleted');for(var i= 0,usr;usr=rows[i++];){console.log('user nae='+usr.name + ', password='+usr.password);}console.log('\n');});
Close database connection
Connection. end ();
Basic CRUD completed
For a complete description of the functions of the mysql module, see the official website:
Https://www.npmjs.com/package/mysql
Https://github.com/felixge/node-mysql
All demo code
var mysql=require('mysql');var connection = mysql.createConnection({host : '192.168.0.108',user : 'root',password : '123456',database : 'test1',port:'3306'});connection.connect();var usr={name:'zhangsan',password:'pwdzhangsan',mail:'zhangsan@gmail.com'};connection.query('insert into users set ?', usr, function(err, result) {if (err) throw err;console.log('inserted zhangsan');console.log(result);console.log('\n');});connection.query('select * from users', function(err, rows, fields) {if (err) throw err;console.log('selected after inserted');for(var i= 0,usr;usr=rows[i++];){console.log('user nae='+usr.name + ', password='+usr.password);}console.log('\n');});connection.query('update users set password="ddd" where name="zhangsan"', {password:'ppp'}, function(err, result) {if (err) throw err;console.log('updated zhangsan\'s password to ddd');console.log(result);console.log('\n');});connection.query('select * from users', function(err, rows, fields) {if (err) throw err;console.log('selected after updated');for(var i= 0,usr;usr=rows[i++];){console.log('user nae='+usr.name + ', password='+usr.password);}console.log('\n');});connection.query('delete from users where name="zhangsan"', {password:'ppp'}, function(err, result) {if (err) throw err;console.log('deleted zhangsan');console.log(result);console.log('\n');});connection.query('select * from users', function(err, rows, fields) {if (err) throw err;console.log('selected after deleted');for(var i= 0,usr;usr=rows[i++];){console.log('user nae='+usr.name + ', password='+usr.password);}console.log('\n');});connection.end();