Node.js learning notes (18. Connect to mysql)-Alibaba Cloud Developer Community

The previous education background is node.js RESTFul API, but there are only API operations for checking, adding, and modifying. Since Node.js is running on the server, it must be able to operate on the database.

Database

create a database:

CREAT DATABASE nodejs

create a table:

DROP TABLE IF EXISTS `role`; CREATE TABLE `role` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(30) NOT NULL,  `movie` varchar(30) NOT NULL,  PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

add some data to the table:

INSERT INTO `role` VALUES ('1', '燕双鹰', '英雄'); INSERT INTO `role` VALUES ('2', '李元芳', '神探狄仁杰'); INSERT INTO `role` VALUES ('3', '张小敬', '长安十二时辰'); INSERT INTO `role` VALUES ('4', '紫霞仙子', '大话西游');

install the driver

use the Taobao Image to install.

Connect to the database:

test.js:

var mysql = require('mysql'); var connection = mysql.createConnection({  host : 'localhost',  user : 'root',  password : '123456',  database : 'test' }); connection.connect(); connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {  if (error) throw error;  console.log('The solution is: ', results[0].solution); });

run:

OK, the connection is successful.

Connection parameters:

parameter description

Crud

it's time for the familiar add, delete, modify, and query.

Check

test.js:

var mysql = require('mysql'); var connection = mysql.createConnection({  host : 'localhost',  user : 'root',  password : '123456',  database : 'test' }); connection.connect(); var sql = 'SELECT * FROM role'; //查 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();

run:

increase

test.js:

var mysql = require('mysql'); var connection = mysql.createConnection({  host : 'localhost',  user : 'root',  password : '123456',  database : 'test' }); connection.connect(); var addSql = 'INSERT INTO role(name,movie) VALUES(?,?)'; var addSqlParams = ['洪世贤', '回家的诱惑']; //增 connection.query(addSql,addSqlParams,function (err, result) {  if(err){  console.log('[INSERT ERROR] - ',err.message);  return;  }  console.log('--------------------------INSERT----------------------------');  //console.log('INSERT ID:',result.insertId);  console.log('INSERT ID:',result);  console.log('-----------------------------------------------------------------\n\n'); }); connection.end();

execution:

take a look at the database:

new data is already included.

Change

test.js:

var mysql = require('mysql'); var connection = mysql.createConnection({  host : 'localhost',  user : 'root',  password : '123456',  database : 'test' }); connection.connect(); var modSql = 'UPDATE role SET movie= ? WHERE Id = ?'; var modSqlParams = ['品如衣之清醒渣男传',5]; //改 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();

execution:

view databases:

hong Shixian's movie has been changed.

Delete

test.js:

var mysql = require('mysql'); var connection = mysql.createConnection({  host : 'localhost',  user : 'root',  password : 'xing0515',  database : 'nodejs' }); connection.connect(); var delSql = 'DELETE FROM role where id=5'; //删 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();

run:

view databases:

well, Hong Shixian is gone.

Of course, it is also possible to write several functions to server.js.

Reference:

[1]], https://www.runoob.com/nodejs/nodejs-mysql.html

[2]], http://how2j.cn/k/nodejs/nodejs-mysql/1768.html#nowhere

Selected, One-Stop Store for Enterprise Applications
Support various scenarios to meet companies' needs at different stages of development

Start Building Today with a Free Trial to 50+ Products

Learn and experience the power of Alibaba Cloud.

Sign Up Now