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

Please read this disclaimer carefully before you start to use the service. By using the service, you acknowledge that you have agreed to and accepted the content of this disclaimer in full. You may choose not to use the service if you do not agree to this disclaimer. This document is automatically generated based on public content on the Internet captured by Machine Learning Platform for AI. The copyright of the information in this document, such as web pages, images, and data, belongs to their respective author and publisher. Such automatically generated content does not reflect the views or opinions of Alibaba Cloud. It is your responsibility to determine the legality, accuracy, authenticity, practicality, and completeness of the content. We recommend that you consult a professional if you have any doubt in this regard. Alibaba Cloud accepts no responsibility for any consequences on account of your use of the content without verification. If you have feedback or you find that this document uses some content in which you have rights and interests, please contact us through this link: https://www.alibabacloud.com/campaign/contact-us-feedback. We will handle the matter according to relevant regulations.