Preliminary use of Node to connect to Mysql database_node.js

Using Node for web page development is basically to connect to the non-relational database mongodb. Here I tried to connect to the mysql database first, because mongodb is too unfamiliar compared to mysql, and I wanted to get the page out quickly, so I chose Relatively familiar with some mysql.

1. Install mysql

Download MySQL:MySQL Downloads and install it. After installation, you will be guided to configure the database, set the root password and create a normal user and password.

2. Install Node-mysql

Install the mysql software package through npm, which allows you to quickly call functions to connect to the mysql database. Enter the project folder and execute npm install mysql --save.

After installation, the mysql directory will be generated in the node_modules directory of the project folder.

3. View readme document

Enter the mysql directory and view the README document. This step is very important. Do not search on Baidu and Google for how to use it, because due to different versions, the answer you get may not allow you to successfully connect to the database. After all, Node is developing so fast.

If you read the README document carefully, you don’t need to read the next steps to avoid misleading you due to inconsistent versions.

4. Connect to mysql database

Enter the project document, create a new TestMysql.js example, and write the following code:

var mysql   = require('mysql');
var connection = mysql.createConnection({
 host   : 'localhost',
 user   : 'me',
 password : 'secret',
 database : 'my_db'
});

connection.connect();

connection.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
 if (err) throw err;

 console.log('The solution is: ', rows[0].solution);
});

connection.end();

Basic connection parameters

• host host name, localhost represents local
  
• user Mysql user
  
• password password
  
• database connected database
  

client.connect() connects to the database

client.query() executes SQL statement
client.end() closes the connection.
Then execute the program through node TestMysql.js, making sure you have started the Mysql service before executing.

5. Add, delete, modify and check

Using a database is nothing more than adding, deleting, modifying, and checking. The following example may be helpful to you.

var mysql   = require('mysql');
var connection = mysql.createConnection({
 host   : 'localhost',
 user   : 'me',
 password : 'secret',
 database : 'my_db'
});

connection.connect();

// 增加记录
client.query('insert into test (username ,password) values ("lupeng" , "123456")');

// 删除记录
client.query('delete from test where username = "lupeng"');

// 修改记录
client.query('update test set username = "pengloo53" where username = "lupeng"');

// 查询记录
client.query("select * from test" , function selectTable(err, rows, fields){
 if (err){
  throw err;
 }
 if (rows){
  for(var i = 0 ; i < rows.length ; i++){
   console.log("%d\t%s\t%s", rows[i].id,rows[i].username,rows[i].password);
  }
 }
});

connection.end();

At this point, the initial connection to the Mysql database has come to an end, and you can then use it in the Node project.

Hope everyone will continue to pay attention.