Retrieve a unique data from the table “employees”.
Create a js file named selectwhere.js having the following data in DBexample folder.
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "12345",
database: "javatpoint"
});
con.connect(function(err) {
if (err) throw err;
con.query("SELECT * FROM employees WHERE id = '1'", function (err, result) {
if (err) throw err;
console.log(result);
});
});
Now open command terminal and run the following command:
Node selectwhere.js
Node.js MySQL Select Wildcard
Retrieve a unique data by using wildcard from the table “employees”.
Create a js file named selectwildcard.js having the following data in DBexample folder.
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "12345",
database: "javatpoint"
});
con.connect(function(err) {
if (err) throw err;
con.query("SELECT * FROM employees WHERE city LIKE 'A%'", function (err, result) {
if (err) throw err;
console.log(result);
});
});
Now open command terminal and run the following command:
Node selectwildcard.js
It will retrieve the record where city start with A.
Create a js file named select.js having the following data in DBexample folder.
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "12345",
database: "javatpoint"
});
con.connect(function(err) {
if (err) throw err;
con.query("SELECT * FROM employees", function (err, result) {
if (err) throw err;
console.log(result);
});
});
Now open command terminal and run the following command:
The DELETE FROM command is used to delete records from the table.
Example
Delete employee from the table employees where city is Delhi.
Create a js file named “delete” in DBexample folder and put the following data into it:
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "12345",
database: "javatpoint"
});
con.connect(function(err) {
if (err) throw err;
var sql = "DELETE FROM employees WHERE city = 'Delhi'";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("Number of records deleted: " + result.affectedRows);
});
});
Now open command terminal and run the following command:
Node delete.js
You can verify the deleted record by using SELECT statement:
The UPDATE command is used to update records in the table.
Example
Update city in “employees” table where id is 1.
Create a js file named “update” in DBexample folder and put the following data into it:
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "12345",
database: "javatpoint"
});
con.connect(function(err) {
if (err) throw err;
var sql = "UPDATE employees SET city = 'Delhi' WHERE city = 'Allahabad'";
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result.affectedRows + " record(s) updated");
});
});
Now open command terminal and run the following command:
Node update.js
It will change the city of the id 1 is to Delhi which is prior Allahabad.
You can check the updated record in the new table:
In the old table city of “Ajeet Kumar” is Allahabad.
CREATE TABLE command is used to create a table in MySQL. You must make it sure that you define the name of the database when you create the connection.
Example
For creating a table named “employees”.
Create a js file named employees.js having the following data in DBexample folder.
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "12345",
database: "javatpoint"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
var sql = "CREATE TABLE employees (id INT, name VARCHAR(255), age INT(3), city VARCHAR(255))";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("Table created");
});
});
Now open command terminal and run the following command:
Node employees.js
Verification
To verify if the table is created or not, use the SHOW TABLES command.
You can also check the structure of the table using DESC command:
Create Table Having a Primary Key
Create Primary Key in New Table:
Let’s create a new table named “employee2” having id as primary key.
Create a js file named employee2.js having the following data in DBexample folder.
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "12345",
database: "javatpoint"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
var sql = "CREATE TABLE employee2 (id INT PRIMARY KEY, name VARCHAR(255), age INT(3), city VARCHAR(255))";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("Table created");
});
});
Now open command terminal and run the following command:
Node employee2.js
Verification
To verify if the table is created or not, use the SHOW TABLES command.
You can also check the structure of the table using DESC command to see that id is a primary key :
Add columns in existing Table:
ALTER TABLE statement is used to add a column in an existing table. Take the already created table “employee2” and use a new column salary.
Replace the data of the “employee2” table with the following data:
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "12345",
database: "javatpoint"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
var sql = "ALTER TABLE employee2 ADD COLUMN salary INT(10)";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("Table altered");
});
});
Now open command terminal and run the following command:
We can use Node.js in database applications. Here we use MySQL as a database with Node.js.
Install MySQL on your computer.
You can download it from here https://www.mysql.com/downloads/.
Once the MySQL is installed and running, you can access it by using Node.js.
Install MySQL Driver
You have to install MySQL driver to access a MySQL database with Node.js. Download MySQl module from npm.
To download and install the “mysql” module, open the Command Terminal and execute the following:
npm install mysql
Create Connection
Create a folder named “DBexample”. In that folder create a js file named “connection.js” having the following code:
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "12345"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
});
Now open the command terminal and use the following command:
