Scribd
Upload
9 views6 pages

MySQL Connection and Query Examples

The document provides a series of JavaScript code snippets for performing various MySQL operations using the 'mysql' library. Operations include connecting to a MySQL server, executing SELECT, INSERT, UPDATE, and DELETE queries, creating databases and tables, and displaying results in the console or via an Express server. Each snippet demonstrates a specific functionality related to database management and manipulation.

Uploaded by

Petricia Roseline
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
9 views6 pages

MySQL Connection and Query Examples

The document provides a series of JavaScript code snippets for performing various MySQL operations using the 'mysql' library. Operations include connecting to a MySQL server, executing SELECT, INSERT, UPDATE, and DELETE queries, creating databases and tables, and displaying results in the console or via an Express server. Each snippet demonstrates a specific functionality related to database management and manipulation.

Uploaded by

Petricia Roseline
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

1.

Connection Test

const mysql = require('mysql');


const con = [Link]({
host: 'localhost',
user: 'user1',
password: '1234',
});

[Link]((err) => {
if (err) throw err;
[Link]('Connected to MySQL Server!');
});

2. select Query
const mysql = require('mysql');
const con = [Link]({
host: 'localhost',
user: 'user1',
password: '1234',
database: "emp"
});

[Link]((err) => {
if (err) throw err;
[Link]('Connected to MySQL Server!');
[Link]("SELECT * FROM epersonal", function (err, result, fields) {
if (err) throw err;
[Link](result);
});
});

3. create database
var mysql=require('mysql');

var con=[Link]({
host:"localhost",
user:"user1",
password:"1234"
});

[Link](function(err) {
if(err) throw err;
[Link]("connected");
//Create a database named "inventory"
[Link]("CREATE DATABASE stu1", function(err,result){
if(err) throw err;
else{
[Link]("Database Created");
[Link]("use stu1");
var sql = "CREATE TABLE student (sno INT, sname
VARCHAR(25), percent INT(3))";
[Link](sql,function(err,result){
if(err) throw err;
[Link]("Table Created");
});
}
});
});

4. Slip 5 – Select all records from customer table and delete specified record

var mysql = require('mysql');

var con = [Link]({


host: "localhost",
user: "user1",
password: "1234",
database: "invoice"
});

[Link](function(err) {
if (err) throw err;
[Link]("SELECT * FROM customers", function (err, result, fields) {
if (err) throw err;
[Link](result);
});
var sql = "DELETE FROM customers WHERE cno = 40";
[Link](sql, function (err, result) {
if (err) throw err;
[Link]("Number of records deleted: " + [Link]);
});

});

5. slip 6- insert multiple records


var mysql=require('mysql');

var con =[Link]({


host:"localhost",
user:"root",
password:"",
database:"studentsdb"
});

[Link](function(err){
if(err) throw err;
var records = [
['Arun', 25, 85],
['Jack', 16, 82],
['Priya', 17, 88],
['Amy', 15, 74]
];
[Link]("INSERT INTO students (name,rollno,marks) VALUES ?",
[records],function(err,result,fields){
if(err) throw err;

[Link](result);
[Link]("Number of rows affected : " + [Link]);
[Link]("Number of records affected with warning : " +
[Link]);
[Link]("Message from Mysql server : " + [Link]);
});
});

6. slip7 – display all record from customer table – same as slip5

7. slip13 -update student mark

var mysql = require('mysql');

var con = [Link]({


host: "localhost",
user: "user1",
password: "1234",
database: "stu"
});

[Link](function(err) {
if (err) throw err;

var sql = "UPDATE stu SET marks=55 Where rno=10;";


[Link](sql, function (err, result) {
if (err) throw err;
[Link]("Number of records updated: " + [Link]);
});

[Link]("SELECT * FROM stu", function (err, result, fields) {


if (err) throw err;
[Link](result);
});

});

8. Slip10
SELECT MAX(salary),min(salary),avg(salary) FROM emp,dept WHERE [Link] =
[Link] GROUP BY(dname)

var mysql = require('mysql');

var con = [Link]({


host: "localhost",
user: "user1",
password: "1234",
database: "invoice"
});

[Link](function(err) {
if (err) throw err;
[Link]("SELECT MAX(salary),min(salary),avg(salary) FROM emp,dept WHERE [Link] =
[Link] GROUP BY(dname)", function (err, result, fields) {
if (err) throw err;
[Link](result);
});

});

9. slip 16 update emp -same as slip 13

10. slip17- emp detail orderby salary

SELECT * FROM employee ORDER BY salary

var http = require('http');

[Link](function (req, res) {


[Link](200, {'Content-Type': 'text/html'});
[Link]('<table border=1">');
[Link]('<tr>');
[Link]('<td>rollNo');
[Link]('<td>Name');
[Link]('</tr>');
[Link]('</table>');
return [Link]();
}).listen(8080);

Extract field values

const mysql = require('mysql');


const con = [Link]({
host: 'localhost',
user: 'user1',
password: '1234',
database: "emp"
});

[Link]((err) => {
if (err) throw err;
[Link]('Connected to MySQL Server!');
[Link]("SELECT * FROM epersonal", function (err, result, fields) {
if (err) throw err;

[Link](result).forEach(function(key) {
var row = result[key];
[Link]([Link],[Link])
});
});
});
[Link]

Using express display output of all records in the browser screen

var express = require('express'),


app = express(),
http = require("http").Server(app).listen(8888)
var mysql = require('mysql');
var formidable=require('formidable');
const path=require('path');
const fs=require('fs');

var con = [Link]({


host: "localhost",
user: "user1",
password: "1234",
database: "invoice"
});

[Link]("/",function(req,res){
[Link](200).sendFile([Link](__dirname,"[Link]"));
})

[Link]('/fapp',function(req,res){

[Link](function(err){
if(err)throw err;
var sql="select * from employees";
[Link](sql,function(err,result,fields){
//var d=[Link](result)

[Link](result)
})
})
})

[Link]( (row) => {


[Link](`${[Link]} lives in ${[Link]}`);
});

This gives you the following:


Michaela Lehr lives in Berlin
Michael Wanyoike lives in Nairobi
James Hibbard lives in Munich
Karolina Gawron lives in Wrocław

Creating
You can execute an insert query against a database, like so:
const author = { name: 'Craig Buckler', city: 'Exmouth' };
[Link]('INSERT INTO authors SET ?', author, (err, res) => {
if(err) throw err;

[Link]('Last insert ID:', [Link]);


});
Note how we can get the ID of the inserted record using the callback parameter.

Updating
Similarly, when executing an update query, the number of rows affected can be retrieved
using [Link]:
[Link](
'UPDATE authors SET city = ? Where ID = ?',
['Leipzig', 3],
(err, result) => {
if (err) throw err;

[Link](`Changed ${[Link]} row(s)`);


}
);

Destroying
The same thing goes for a delete query:
[Link](
'DELETE FROM authors WHERE id = ?', [5], (err, result) => {
if (err) throw err;

[Link](`Deleted ${[Link]} row(s)`);


}
);

Common questions

Powered by AI

Key SQL operations in Node.js for MySQL databases include SELECT for data retrieval, INSERT for adding data, UPDATE for modifying existing records, and DELETE for removing records. Each operation is pivotal for maintaining and manipulating the database, handling diverse data scenarios effectively .

Data can be ordered using the 'ORDER BY' clause in the query, specifying the column to sort by, such as 'salary'. This ordering is crucial for analysis, reporting, or when the sequence impacts data interpretation. Node.js executes this sorting using MySQL's native capabilities and returns results in the specified order .

Updating records in a MySQL database using Node.js changes the specified data points based on the conditions set in the SQL 'UPDATE' statement. Programmatically, the impact is reflected through 'result.affectedRows', which indicates how many rows were modified. This helps in verifying the update operation's effectiveness .

To insert multiple records into a MySQL table using Node.js, use the 'INSERT INTO ... VALUES ?' query format with an array of record tuples. After executing this query with 'con.query', the number of affected rows can be retrieved via 'result.affectedRows', and potential warnings and server messages may be reviewed through 'result.warningCount' and 'result.message' respectively .

Establishing a MySQL database connection using Node.js involves requiring the 'mysql' module, creating a connection using 'mysql.createConnection' with relevant host, user, and password details, and then calling the 'connect' method to initiate the connection. If there's an error during this process, it should be caught and handled appropriately .

Error handling is crucial when performing database operations to ensure stability and reliability of applications. Without appropriate error handling, connection failures or faulty query executions could lead to application crashes or data loss. In Node.js, errors are captured in callbacks, allowing for user-friendly messaging or alternative flow logic .

To query data from an existing table using Node.js with MySQL, you must first establish a connection to the MySQL server and select a database. Then, you can use the 'con.query' method with an SQL SELECT statement specifying the desired table. Handle any errors during the query execution and optionally process and display the results .

Integration of Node.js with MySQL can display data on a web page by using frameworks like Express to handle HTTP requests and Node.js to query databases. Utilizing 'res.send' or similar functions to send query results as the HTTP response aids in dynamically rendering data on web pages, offering real-time data access and a seamless user experience .

Using Node.js for CRUD operations enhances real-time web application responsiveness through its event-driven, non-blocking I/O model. This efficiency allows for simultaneous handling of multiple operations, crucial for applications requiring frequent data interactions. It minimizes downtime and latency, providing users with immediate feedback and interaction capabilities .

Creating a new database and table in Node.js with MySQL requires establishing a connection and executing an SQL CREATE DATABASE command via 'con.query'. Once the database is created, use the 'USE' statement to select it, then create a table with 'CREATE TABLE' specifying column names and data types. The process involves error handling to manage any potential exceptions .

You might also like