( Node.js + MySQL )
To begin, we verify that the database connection is working properly.
connection.js
// -------------------------Codes start here--------------------------
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: ""
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
});
// -------------------------Codes end here--------------------------
Result
Connected!
In 'phpmyadmin', you'll notice that a new database named 'nodejs' has been created. http://localhost/phpmyadmin/ is the URL. Use the code.
dbcreation.js
// -------------------------Codes start here--------------------------
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: ""
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
con.query("CREATE DATABASE nodejs", function (err, result) {
if (err) throw err;
console.log("Database created");
});
});
// -------------------------Codes end here--------------------------
Result
Connected!
Database created
tablecreation.js
// -------------------------Codes start here--------------------------
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "nodejs"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
//var sql = "CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))";
var sql = "CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("Table created");
});
});
// -------------------------Codes end here--------------------------
Result
Connected!
Table created
Inside the database 'nodejs,' a new table named 'customers' has been created. Under the table 'customers,' three different columns titled 'id,' 'name,' and 'address' have also been generated.
insertion.js
// -------------------------Codes start here--------------------------
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "nodejs"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
//Make SQL statement:
var sql = "INSERT INTO customers (name, address) VALUES ?";
//Make an array of values:
var values = [
['Jakie', 'China'],
['Danny', 'UK'],
['Peter', 'South Korea'],
['Jackson', 'Poland'],
['david', 'Huston Street'],
['Kausal', 'Valley 345'],
['Sandy', 'Australlia'],
['Amy', 'Green Grass 1'],
['Anderson', 'UK'],
['Susan', 'One way 98'],
['Ramesh', 'Yellow Garden 2'],
['Ben', 'Park Lane 38'],
['Beans', 'Central st 954'],
['Pitterson', 'Main Road 989'],
['Hu', 'China']
];
//Execute the SQL statement, with the value array:
con.query(sql, [values], function (err, result) {
if (err) throw err;
console.log("Number of records inserted: " + result.affectedRows);
});
});
// -------------------------Codes end here--------------------------
Result
Connected!
Number of records inserted: 15
When you look at the table 'customers,' you'll notice that 15 new records have been entered into the columns.
update.js
// -------------------------Codes start here--------------------------
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "nodejs"
});
con.connect(function(err) {
if (err) throw err;
var sql = "UPDATE customers SET address = 'Japan' WHERE address = 'China'";
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result.affectedRows + " record(s) updated");
});
});
// -------------------------Codes end here--------------------------
Result
2 record(s) updated
As you can see, it was formerly addressed as 'China,' but has since been amended to 'Japan.' As a result, the amended address for such cases is 'Japan.'
findindb.js
// -------------------------Codes start here--------------------------
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "nodejs"
});
con.connect(function(err) {
if (err) throw err;
//Select all customers where the address starts with an "S"
con.query("SELECT * FROM customers WHERE address LIKE 'S%'", function (err, result) {
if (err) throw err;
console.log(result);
});
});
// -------------------------Codes end here--------------------------
Result
[ Raw packet { id: 3, name: ‘Peter’, address: ‘South Korea’ } ]
You can see here the data in table ‘customers’ which contains character ‘s’ has been displayed. If we are pretty sure about the exact name or address then we can use clause SELECT * FROM customers WHERE address = ‘UK’ , like that.