How to write mysql query in node.js?

Insert Query

File Name :

var sql = "INSERT INTO customers (name, address) VALUES ('Sana', 'Dhandiha')";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("1 record inserted");
});

Insert Multiple Records

File Name :

var sql = "INSERT INTO customers (name, address) VALUES ?";
var values = [
['Mahtab', 'Koeilwar'],
['Sana', 'Ranchi'],
['Habib', 'Dhandiha'],
['Sara', 'Patna'],
['Arham', 'Delhi']
];
con.query(sql, [values], function (err, result) {
if (err) throw err;
console.log("Number of records inserted: " + result.affectedRows);
});

Inserted ID

File Name :

var sql = "INSERT INTO customers (name, address) VALUES ('Mahira Mahtab', 'ARA')";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("1 record inserted, ID: " + result.insertId);
});

Insert Query

File Name :

const author = { name: 'Nusrat', city: 'Ranchi' };
con.query('INSERT INTO authors SET ?', author, (err, res) => {
if(err) throw err;

console.log('Last insert ID:', res.insertId);
});

Select Query

File Name :

con.query("SELECT * FROM ittutorial", function (err, result, fields) {
if (err) throw err;
console.log(result);
console.log(fields);
});

File Name :

con.query("SELECT name, address FROM users", function (err, result, fields) {
if (err) throw err;
console.log(result);
console.log(fields[1].name); });

where clause :-

File Name :

con.query("SELECT * FROM users WHERE name = 'sana'", function (err, result) {
if (err) throw err;
console.log(result);
});

where clause :-

File Name :

var sql = "SELECT * FROM users WHERE name = 'sana'"; con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});

Wildcard Characters

select the records that starts, includes, or ends with a given letter. Use the '%' wildcard to represent zero, one or multiple characters:

Select data where the name starts with the letter 'S':

File Name :

con.query("SELECT * FROM users WHERE name LIKE 'S%'", function (err, result) {
if (err) throw err;
console.log(result);
});

Escaping Query Values

When query values are variables provided by the user, you should escape the values.

This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.

var address = 'Dhandiha';
var sql = 'SELECT * FROM ittutorial WHERE address = ' + mysql.escape(address);
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});

use a ? as a placeholder for the values

File Name :

var address = 'Dhandiha';
var sql = 'SELECT * FROM ittutorial WHERE address = ?';
con.query(sql, [address], function (err, result) {
if (err) throw err;
console.log(result);
});

Multiple values use array.

var name = 'Sana';
var address = 'Dhandiha, Koeilwar';
var sql = 'SELECT * FROM users WHERE name = ? OR address = ?';
con.query(sql, [name, address], function (err, result) {
if (err) throw err;
console.log(result);
});

Node.js MySQL Order By :- Sort the Result

Use the ORDER BY statement to sort the result in ascending or descending order.

The ORDER BY keyword sorts the result ascending by default.

asc

desc

File Name :

con.query("SELECT * FROM users ORDER BY name", function (err, result) {
if (err) throw err;
console.log(result);
});

order by desc :

File Name :

con.query("SELECT * FROM customers ORDER BY name DESC", function (err, result) {
if (err) throw err;
console.log(result);
});

Node.js MySQL Delete

var sql = "DELETE FROM ittutorial WHERE name = 'mahi'";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("Number of records deleted: " + result.affectedRows);
});

Node.js MySQL Update

update existing records in a table by using the "UPDATE" statement

File Name :

var sql = "UPDATE users SET address = 'Koeilwar' WHERE address = 'Dhandiha'";
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result.affectedRows + " record(s) updated");
});

MySQL Limit

limit the number of records returned from the query, by using the "LIMIT"

var sql = "SELECT * FROM users LIMIT 5";
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});

Limit offset :

Start from position 3, and return the next 5 records:

Note: "OFFSET 2", means starting from the third position, not the second!

File Name :

var sql = "SELECT * FROM customers LIMIT 5 OFFSET 2";
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});

SQL statement like this "LIMIT 2, 5" which returns the same as the offset example

File Name :

var sql = "SELECT * FROM users LIMIT 5,10";
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});

MySQL Join

You can combine rows from two or more tables, based on a related column between them, by using a JOIN statement.

var sql = "SELECT users.name AS user, products.name AS favorite FROM users JOIN products ON users.favorite_product = products.id";
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});

Left Join

If you want to return all users, no matter if they have a favorite product or not, use the LEFT JOIN statement:

File Name :

Right Join

If you want to return all products, and the users who have them as their favorite, even if no user have them as their favorite, use the RIGHT JOIN statement:

File Name :

Inner Join

// Inner Join
conn.query("SELECT village.vid,village.vname,village.people,field.district,field.type
FROM village INNER JOIN field ON village.vid = field.id", function (error, result) {
let text = "";
//Display the records one by one
console.log(result);
});

// Inner Join with where condition :- connection_data.query("SELECT village.vid,village.vname,village.people,field.district,field.type
FROM village INNER JOIN field ON village.vid = field.id WHERE village.people>50", function (error, result) {
let text = "";
//Display the records one by one
console.log(result);
});

Like

File Name :

connection.query('SELECT * FROM ittutorial where domainname like ?', ['%' + request.body.domain + '%'] ,function(error, results){ response.render('./results',{rows:results}); });

like query

File Name :

var search_query = " (name LIKE '%"+search_value+"%' OR Address LIKE '%"+search_value+"%' OR email LIKE '%"+search_value+"%' OR mobile LIKE '%"+search_value+"%')";

var sql = "select count(*) as total from user_details where 1 AND "+search_query;

conn.query(sql,function(error,data){

});

File Name :

var search_query = " (name LIKE '%"+search_value+"%' OR Address LIKE '%"+search_value+"%' OR email LIKE '%"+search_value+"%' OR mobile LIKE '%"+search_value+"%')";
var column_name = 'id';
var column_sort_order = ''name;
var query = "select * from user_details where 1 AND "+search_query+ "order by '"+column_name+"' '"+column_sort_order+"' LIMIT "+start+ ',' +length; conn.query(query,function(error,data){ });

IN clause in mysql nodejs

File Name :

var query=select * from User where userid in (?);
var data=['arham','boby','sana'];
var queryData=[data];

conn.query(query, queryData, function (err, results) {

})

IN clause :

File Name :

var ids = [1,2,3,4,5];
// then, create a dynamic list of comma-separated question marks
var tokens = new Array(ids.length).fill('?').join(',');

var query = `SELECT * FROM User WHERE userid IN (${tokens})`;

connection.query(query, ids, (err, data) => {
// do something with `err` or `data`

});

between

File Name :

const query = 'SELECT * FROM `users` ' +
'WHERE `id` BETWEEN ? AND ?';
const values = [2, 4];
// id >= 2 AND id <= 4

connection.query(query, values, (error, result) => {
// sends queries
connection.end();
// closes connection
if (error) throw error;
console.log(result);
});

count

File Name :

const query ='SELECT COUNT(*) AS `number of users` FROM `users`';
conn.query(query, (error, result) => {

conn.end();
// closes connection
if (error) throw error;
console.log(result);
});

Group by

File Name :

const query ='SELECT COUNT(`id`) AS `number of users`, `country` ' + 'FROM `users` ' + 'GROUP BY `country` ' + 'ORDER BY COUNT(`id`) DESC;';

conn.query(query, (error, result) => {
conn.end();
if (error) throw error;
console.table(result);
});

Having

File Name :

const query ='SELECT COUNT(`id`) AS `number of users`, `country` ' +
'FROM `users` ' +
'GROUP BY `country` ' +
'HAVING COUNT(`id`) >= ?;';
const minimum = [2];
// Each group will have at least 2 users.

conn.query(query, minimum, (error, result) => {
conn.end();

if (error) throw error;
console.table(result);
});

Node.js - how to prevent SQL injection

SQL injection is a very serious problem for web developers. It involves entering a SQL subquery in place of the main query value, which may then be incorrectly processed by the database.

3 ways to defend against SQL injection Attack:

escape() method

placeholders

named-placeholders - external npm package

File Name :

const query = 'SELECT * FROM `users` WHERE `salary` < ' + conn.escape(value);

conn.query(query, (error, result) => {
conn.end();

if (error) throw error;
console.log(result);
});

Placeholders

In this case, we create a query that has question marks in the places where we want to insert something. values in the array are inserted in the order in which they were passed.

Note: ? is for ordinary values, ?? is used for column and table names.

const query = 'SELECT * FROM ?? WHERE `salary` < ?';
const values = ['users', 3700];
// SELECT * FROM `users` WHERE `salary` < 3700

conn.query(query, values, (error, result) => {
conn.end();
if (error) throw error;
console.log(result);
});

Distinct

File Name :

const query ='SELECT DISTINCT `country` FROM `users`';

Node.js - MySQL Inner Join

File Name :

const query = 'SELECT * ' + 'FROM `users` ' + 'JOIN `departments` ON `departments`.`id` = `users`.`department_id`';

File Name :

const query = `SELECT *
FROM ??
JOIN ?? ON ??.?? = ??.??`;
const values = [
'users', // SELECT *
'departments', // FROM `users`
'departments', // JOIN `departments`
'id', // ON departments.id = users.department_id
'users',
'department_id',
];

connection.query(query, values, (error, result) => { // sends queries
connection.end(); // closes connection
if (error) throw error;
console.log(result);
});

File Name :

const query = `SELECT * FROM ?? AS A
JOIN ?? AS B ON A.?? = B.??`;
const values = [
'users', // SELECT * FROM `users` AS A
'departments', // JOIN `departments` AS B ON B.id = A.department_id
'users',
'department_id',
];

connection.query(query, values, (error, result) => { // sends queries
connection.end(); // closes connection
if (error) throw error;
console.log(result);
});

Stored Procedures

a stored procedure is prepared SQL code that you can save to a database, so that it can easily be reused.

A stored routine is a set of SQL statements that can be stored in the server.

Delimiter (Creating a Stored Procedure)

The delimiter is the character or string of characters that you'll use to tell the mySQL client that you've finished typing in an SQL statement. For ages, the delimiter has always been a semicolon. in a stored procedure, one can have many statements, and each must end with a semicolon. In this tutorial I will use “//”

File Name :

DELIMITER //
CREATE PROCEDURE `p2` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A procedure'
BEGIN
SELECT 'Hello World !';
END//

The first part of the statement creates the procedure. The next clauses defines the optional characteristics of the procedure. Then you have the name and finally the body or routine code. Stored procedure names are case insensitive, and you cannot create procedures with the same name.

The four characteristics of a procedure are: Language : For portability purposes; the default value is SQL. Deterministic : If the procedure always returns the same results, given the same input. This is for replication and logging purposes. The default value is NOT DETERMINISTIC. SQL Security : At call time, check privileges of the user. INVOKER is the user who calls the procedure. DEFINER is the creator of the procedure. The default value is DEFINER. Comment : For documentation purposes; the default value is ""

Calling a Stored Procedure

To call a procedure, you only need to enter the word CALL, followed by the name of the procedure, and then the parentheses, including all the parameters between them (variables or values). Parentheses are compulsory.

File Name :

CALL stored_procedure_name (param1, param2, ....)
CALL procedure1(10 , 'string parameter' , @parameter_var);

create procedure

File Name :

DELIMITER $$

CREATE PROCEDURE `sp_get_authors`()
BEGIN
SELECT id, name, city FROM authors;
END $$

call the procedure

File Name :

con.query('CALL sp_get_authors()',function(err, rows){
if (err) throw err;

console.log('Data received from Db:');
console.log(rows);
});

File Name :

DELIMITER $$

CREATE PROCEDURE `sp_get_author_details`(
in author_id int
)
BEGIN
SELECT name, city FROM authors where id = author_id;
END $$

Delete a Stored Procedure

File Name :

DROP PROCEDURE IF EXISTS p2;

Modify a Stored Procedure

MySQL provides an ALTER PROCEDURE statement to modify a routine, but only allows for the ability to change certain characteristics. If you need to alter the body or the parameters, you must drop and recreate the procedure.

File Name :

Variables

define variables, and store values inside a procedure. You must declare them explicitly at the start of the BEGIN/END block, along with their data types. Once you've declared a variable, you can use it anywhere that you could use a session variable, or literal, or column name.

File Name :

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

DECLARE a, b INT DEFAULT 5;
DECLARE str VARCHAR(50);
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT;

Working with variables

File Name :

DELIMITER //
CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20))
BEGIN
DECLARE a, b INT DEFAULT 5;
DECLARE str VARCHAR(50);
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT;
INSERT INTO table1 VALUES (a);
SET str = 'I am a string';
SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5;
END //

Flow Control Structures

MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE and REPEAT constructs for flow control within stored programs.

File Name :

DELIMITER //
CREATE PROCEDURE `proc_IF` (IN param1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;

IF variable1 = 0 THEN
SELECT variable1;
END IF;
IF param1 = 0 THEN
SELECT 'Parameter value = 0';
ELSE
SELECT 'Parameter value <> 0';
END IF;
END //

CASE statement

The CASE statement is another way to check conditions and take the appropriate path. It's an excellent way to replace multiple IF statements.

File Name :

DELIMITER //
CREATE PROCEDURE `proc_CASE` (IN param1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;

CASE variable1
WHEN 0 THEN
INSERT INTO table1 VALUES (param1);
WHEN 1 THEN
INSERT INTO table1 VALUES (variable1);
ELSE
INSERT INTO table1 VALUES (99);
END CASE;
END //

############# OR ####################

DELIMITER //
CREATE PROCEDURE `proc_CASE` (IN param1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;

CASE
WHEN variable1 = 0 THEN
INSERT INTO table1 VALUES (param1);
WHEN variable1 = 1 THEN
INSERT INTO table1 VALUES (variable1);
ELSE
INSERT INTO table1 VALUES (99);
END CASE;
END //

WHILE statement

File Name :

DELIMITER //
CREATE PROCEDURE `proc_WHILE` (IN param1 INT)
BEGIN
DECLARE variable1, variable2 INT;
SET variable1 = 0;

WHILE variable1 < param1 DO
INSERT INTO table1 VALUES (param1);
SELECT COUNT(*) INTO variable2 FROM table1;
SET variable1 = variable1 + 1;
END WHILE;
END //

Cursors

Cursor is used to iterate through a set of rows returned by a query and process each row.

File Name :

DELIMITER //
CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)
BEGIN
DECLARE a, b, c INT;
DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
OPEN cur1;
SET b = 0;
SET c = 0;

WHILE b = 0 DO
FETCH cur1 INTO a;
IF b = 0 THEN
SET c = c + a;
END IF;
END WHILE;
CLOSE cur1;
SET param1 = c;
END //

Union

UNION() is used to return the values from two or more tables without duplicates.

Note :-1. Column names may not be same but they are in same order with respect to datatypes.
2. Total number of columns while selecting the columns with UNION must be same.

File Name :

https://www.gkindex.com/nodejs/nodejs-mysql-length.jsp

Previous Next

Review & Rating

0.0 / 5

0 Review

(0)

Most Popular Tutorials

Most Popular Tutorials :-

How to write mysql query in node.js?

PHP

PHP OOPs

Codeigniter

Laravel

NODE.JS

Javascript

0.0 / 5

0 Review

Write Review Here

Most Popular Tutorials

Important Link