MySQL is one of the best RDBMS being used for developing web-based software applications and in this article we will cover about using nodejs for CRUD operations with MySQL database with a step by step approach with code examples

• Database - A database is a collection of tables, with related data.
• Table - A table is a matrix with data. A table in a database looks like a simple spreadsheet.
• Primary Key - A primary key is unique. A key value can not occur twice in one table. With a key, you can find at most one row.
• Foreign Key - A foreign key is the linking pin between two tables.

Nodejs

Nodejs is a platform built on Chrome’s JavaScript runtime for easily building fast and scalable network applications.

Steps:

1) To install MySQL in nodejs use npm package:

npm install mysql

2) Link MySQL with Nodejs server

var mysql = require('mysql');

3) To create a new MySQL database and table - Create database: Creating separate space in MySQL

Syntax:

create database <DB_Name>;

Use database: To use the created Database

use <DB_Name>;

Create Table: Creating table in Database

Syntax:

create table <TableName>(Column1 Datatype,Column2 Datatype,...);

Connect MySQL and Nodejs:

MySQL configuration

Example :

var connection = mysql.createConnection({ // creating connection string
              host     : 'localhost', //host name
              user     : 'root', // mySQL username  
              password : '', // mySql Password
              database : 'db_name' // database Name
          });

connecting with nodejs

Example:

connection.connect(function(err){ 
              if(!err) {
                  console.log("Database is connected ... ");   
              } else {
                  console.log("Error connecting database ... ");   
              }
          });

Insert table: Insert data in created table

Syntax:

insert into <TableName> values('string_data',integer_data,....);

Example:

connection.query("insert into user values("+value1+",'"+value2+"',"+value2+",'" value4+'")",
          function(err, rows, fields) { // INSERT query in nodejs file
          connection.end();
            if (!err)
              console.log('The solution is: ', rows);
            else
              console.log('Error while performing Query.');
            });

Select table: To retrieve data from table

i) Syntax for access all Rows :

select * from <TableName>; (or) select <column_name> from <TableName>;

ii) Syntax for access particular Rows :

select * from <TableName> where <column_name>='<user_data'>;

Example :

var query="select * from db_name where column1='"+value1+"' and column2='"+value2+"'";
          connection.query(query, function(err, rows, fields) { // SELECT query in nodejs file
          connection.end();
              if (!err){
                  console.log('The solution is: ', rows);
              } else { 
                  console.log('Error while performing Query.');
              }
          });

Update table: Syntax for update row:

update <TableName> set <update_columnName> = <update_value> where <column_name>='<user_data'>; 
          

Example:

var query="update db_name set updatecolumn='"+updatevalue+"' 
          where conditioncolumn='"+conditionvalue+"'";
              connection.query(query, function(err, rows, fields) {           // SELECT query in nodejs file
              connection.end();
                if (!err)
                  console.log('The solution is: ', rows);
                else
                  console.log('Error while performing Query.');
                });

Delete table: To delete data or table

i) Syntax for delete all data:

delete table <TableName>;

ii) Syntax for delete particular data:

delete table <TableName> where <columnName>="<userdata>";

Example:

var query="delete table db_name where column1='"+value1;
          connection.query(query, function(err, rows, fields) { // SELECT query in nodejs file
              connection.end();
                if (!err){
                  console.log('The solution is: ', rows);
                }else{
                  console.log('Error while performing Query.');
                }
          });

MySQL aggregate functions:

The AVG function calculates the average value of a set of values. It ignores NULL values in the calculation.

Syntax:

select AVG(<ColumnName) from <TableName>;

The COUNT function returns the number of the rows in a table.

Syntax:

select COUNT(*) from <TableName> (or) select COUNT(<columnName>) from <TableName>;
          

The SUM function returns the sum of a set of values.

Syntax:

select SUM(<ColumnName>) from <TableName>;

The MIN function returns the minimum value in a set of values.

Syntax:

select MIN(<ColumnName>) from <TableName>;

The MAX function returns the maximum value in a set of values.

Syntax:

select MAX(<ColumnName>) from <TableName>;