Week 10.pdf

CP476A Internet Computing Week 10 – 1 Node.js – MySQL Shaun Gao, Ph.D., P.Eng. Agenda SQL Introduction Data Definition Language (DDL) Data Manipulation Language (DML) Node.js MySQL MySQL database creation MySQL database connection MySQL database manipulation Insert, select, delete, update SQL Introd...

CP476A Internet Computing Week 10 – 1 Node.js – MySQL Shaun Gao, Ph.D., P.Eng. Agenda SQL Introduction Data Definition Language (DDL) Data Manipulation Language (DML) Node.js MySQL MySQL database creation MySQL database connection MySQL database manipulation Insert, select, delete, update SQL Introduction SQL stands for Structured Query Language, which is basically a language used by relational databases. Standard language for querying and manipulating data in relational database. SQL contains Data Definition Language Data Manipulation Language Difference between DML and DDL Question? Data Manipulation Language (DML) statements SELECT SELECT column1, column2,... FROM table_name; INSERT INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...); UPDATE UPDATE table_name SET column1 = value1, column2 = value2,... WHERE condition; DELETE DELETE FROM table_name WHERE condition; Data Definition Language (DDL) statements Create Database CREATE DATABASE databasename; Create tables CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype,.... ); Examples: CREATE TABLE IF NOT EXISTS vendors ( vendor_id INT AUTO_INCREMENT PRIMARY KEY, vendor_name VARCHAR(60), vendor_address VARCHAR(125), vendor_city VARCHAR(65), vendor_state VARCHAR(35) ); MySQL Demo Open a DOS command window, log into MySQL server and run the following commands. Select Insert Update Delete Node.js – MySQL overview Node.js has built-in modules for handling MySQL access. All the activities from command line can be done with JavaScript modules Node.js – MySQL Install MySQL from https://www.mysql.com/downloads/ Install MySQL Driver by using npm npm install mysql  install mysql driver Include mysql module in JavaScript source code var mysql = require('mysql’); Connect to MySQL database var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "yourusername", password: "yourpassword" }); Demo01 Node.js - MySQL Create Database Use the "CREATE DATABASE" statement to create a database. var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "yourusername", password: "yourpassword" }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); con.query("CREATE DATABASE mydb", function (err, result) { if (err) throw err; console.log("Database created"); }); }); Demo02 Node.js - MySQL Create Table use the "CREATE TABLE" statement. Make sure to indicate the DB var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "yourusername", password: "yourpassword", database: "mydb" }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); var sql = "CREATE TABLE persons (ID int AUTO_INCREMENT, lastName VARCHAR(255), fisrtName varchar(255), Age int, PRIMARY KEY(ID))"; con.query(sql, function (err, result) { if (err) throw err; console.log("Table created"); }); }); Demo03 Node.js - MySQL Insert Into use the "INSERT INTO" statement to insert data into a table var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "yourusername", password: "yourpassword", database: "mydb" }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); var sql = "INSERT INTO persons (lastName, firstName, Age) VALUES (‘Hao', ‘Melia’, 24)"; con.query(sql, function (err, result) { if (err) throw err; console.log("1 record inserted"); }); }); Demo04 Node.js - MySQL Insert multiple records use "INSERT INTO" statement to insert data into a table var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "yourusername", password: "yourpassword", database: "mydb" }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); var sql = "INSERT INTO persons (lastName, firstName, Age) VALUES ?"; var values = [ [‘Gao', ‘Sean’, 28], ['Peter', ‘Ugin’, 37], [‘Hua’, ‘Hanah’, 25] ]; con.query(sql, [values], function (err, result) { if (err) throw err; console.log("Number of records inserted: " + result.affectedRows); }); }); Demo05 Node.js - MySQL Select Use “SELECT" statement to query information from a table. var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "yourusername", password: "yourpassword", database: "mydb" }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); var sql = “SELECT * from persons"; con.query(sql, function (err, result, fields) { if (err) throw err; console.log(result); }); }); Demo06 Node.js - MySQL Delete Use “DELETE" statement to remove record(s) from a table. var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "yourusername", password: "yourpassword", database: "mydb" }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); var sql = “DELETE from persons where lastName =‘Gao’"; con.query(sql, function (err, result, fields) { if (err) throw err; console.log("Number of records deleted: " + result.affectedRows); }); }); Demo07 Node.js - MySQL update Use “UPDATE" statement to modify data in a table. var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "yourusername", password: "yourpassword", database: "mydb" }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); var sql = “UPDATE persons set lastName =‘Hao’ where ID !=0"; con.query(sql, function (err, result, fields) { if (err) throw err; console.log(result.affectedRows + " record(s) updated"); }); }); Demo08 Summary Introduction to MySQL Basic concept of relational DB Attribute, tuple Entity, entity-relationship diagram Node.js MySQL MySQL database creation MySQL database connection MySQL database manipulation Insert, select, delete, update Announcement Group project presentation order Mar. 29(Friday): Group 13, 10, 3, 5 Apr. 1(Monday): Group 7, 2, 6, 12 Apr. 3(Wednesday): Group 8, 4, 14, 11 Apr. 5(Friday): Group 15, 1, 9 Project report – first page indicates either SDD or TR Software design document (SDD) Technical Report (TR) CP476 Internet Computing Week 10 – 2 Node.js and MongoDB Shaun Gao, Ph.D., P.Eng. Agenda Introduction MongoDB installation – in this week tutorial Concept comparison between MongoDB and RDBMS MongoDB CRUD operations Demo from DOS command line Node.js and MongoDB Installing the NPM Modules Connection from node.js Node.js MongoDB CRUD operation Summary Introduction Name comparisons RDBMS Database Table Index Row/record Column JOIN Foreign Key Partition Primary key MongoDB Database Collection Index Document Field Linking & embedding Reference Shard _id Demos – command line Demonstrate how MongoDB works from DOS command line Step 1. start MongoDB server (make sure server is running) mongod Step 2. start MongoDB client by typing mongo from terminal Step 3. practice MongoDB CRUD operations Please refer to below for reference https://docs.mongodb.com/manual/crud/ Note: MongoDB does not require data definition language that SQL required MongoDB CRUD operations CRUD: Create, read, update, delete. Creating a MongoDB Database with the CLI (the MongoDB shell) There is no “create” command in the MongoDB shell. In order to create a database, you will first need to switch the context to a non-existing database using the use command: use database_name MongoDB only creates the database when you first store data in that database. db.collection_name.insert({name: "Ada Lovelace", age: 205}) MongoDB read command db.collection_name.find(); MongoDB CRUD operations – cont. Insert and updating MongoDB MongoDB CRUD operations – cont. db.collection.deleteMany() db.collection.deleteOne() Example: db.users.deleteMany({ status: "reject" }) db.users.deleteOne( { status: "pending" } ) MongoDB CRUD operations – cont. Reading documents Collection name → users The condition is that age is greater than 18 Projection Use this scheme to specifically include (:1) or exclude (:0) fields. The output only show name and address Node.js and MongoDB Install MongoDB Driver 1. From VS code open a new folder that connects to MongoDB 2. From DOS command line, navigate to that folder run the following >npm install mongodb You cannot keep both node.js MySQL driver and node.js Mongodb driver in the same folder. If you do so, you will get error. Please separate each driver in different folders. MongoDB Connection from node.js Syntax: var MongoClient = require('mongodb').MongoClient; var url = "mongodb://localhost:27017/mydb"; MongoClient.connect(url, function(err, db) { if (err) throw err; console.log(“Connected and database created!"); db.close(); }); Demo01 General form of url Node.js MongoDB CRUD operation Create a collection of MongoDB with JavaScript from Node.js Syntax: var MongoClient = require('mongodb').MongoClient; var url = "mongodb://localhost:27017/"; MongoClient.connect(url, function(err, db) { if (err) throw err; var dbo = db.db("mydb"); dbo.createCollection(“persons", function(err, res) { if (err) throw err; console.log("Collection ‘persons’ created!"); db.close(); }); }); Demo02 Node.js MongoDB CRUD operation – cont. Insert Into Collection from Node.js Syntax: var MongoClient = require('mongodb').MongoClient; var url = "mongodb://localhost:27017/"; MongoClient.connect(url, function(err, db) { if (err) throw err; var dbo = db.db("mydb"); var myobj = { lastName: “Hao", firstName: “Yuan”, age: 28 }; dbo.collection(“persons").insertOne(myobj, function(err, res) { if (err) throw err; console.log("1 document inserted"); db.close(); }); }); Demo03 Node.js MongoDB CRUD operation – cont. Update Document from Node.js Syntax: var MongoClient = require('mongodb').MongoClient; var url = "mongodb://127.0.0.1:27017/"; MongoClient.connect(url, function(err, db) { if (err) throw err; var dbo = db.db("mydb"); var myquery = { age: 28 }; //  condition var newvalues = { $set: {lastName: "Mao", firstName: "Zheng" } }; //  new values dbo.collection("persons").updateOne(myquery, newvalues, function(err, res) { if (err) throw err; console.log("1 document updated"); db.close(); }); }); Demo04 Node.js MongoDB CRUD operation – cont. Find data in a Collection from Node.js Syntax: var MongoClient = require('mongodb').MongoClient; var url = "mongodb://localhost:27017/"; MongoClient.connect(url, function(err, db) { if (err) throw err; var dbo = db.db("mydb"); dbo.collection("persons").findOne({}, function(err, result) { if (err) throw err; console.log(result.lastName); db.close(); }); }); Demo05 Node.js MongoDB CRUD operation – cont. Find all data in a Collection from Node.js Syntax: var MongoClient = require('mongodb').MongoClient; var url = "mongodb://localhost:27017/"; MongoClient.connect(url, function(err, db) { if (err) throw err; var dbo = db.db("mydb"); dbo.collection("persons").find({}).toArray(function(err, result) { if (err) throw err; console.log(result); //db.close(); }); }); Demo06 Node.js MongoDB CRUD operation – cont. MongoDB query from Node.js The first argument of the find() method is a query object and is used to limit the search. Syntax: var MongoClient = require('mongodb').MongoClient; var url = "mongodb://localhost:27017/"; MongoClient.connect(url, function(err, db) { if (err) throw err; var dbo = db.db("mydb"); var query = { lastName: "Mao" }; dbo.collection("persons").find(query).toArray(function(err, result) { if (err) throw err; console.log(result); }); }); Demo07 Node.js MongoDB CRUD operation – cont. Delete Document from Node.js Syntax: var MongoClient = require('mongodb').MongoClient; var url = "mongodb://localhost:27017/"; MongoClient.connect(url, function(err, db) { if (err) throw err; var dbo = db.db("mydb"); var myquery = { age: 48 }; dbo.collection("persons").deleteMany(myquery, function(err, obj) { if (err) throw err; console.log(obj. deletedCount + " document(s) deleted"); db.close(); }); }); Demo08 SQL vs. NoSQL Summary Concept comparison between MongoDB and RDBMS MongoDB CRUD operations Demo from DOS command line Node.js and MongoDB Installing the NPM Modules Connection from node.js Node.js MongoDB CRUD operation Case sensitive in MongoDB, differs from SQL language Announcement Group project presentation order Apr. 1(Monday): Group 13, 10, 3, 5, 7 Apr. 3(Wednesday): Group 2, 6, 12, 8, 4 Apr. 5(Friday): Group 14, 11, 15, 1, 9 Project report – first page indicates either SDD or TR Software design document (SDD) Technical Report (TR)