# Node.js / MySQL
## 1- Access MySQL
```js
var mysql = require('mysql');
```
## 2- Connecting to MySQL
```js
var con = mysql.createConnection({config});
/*
con = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "mydb"
});
*/
```
## 3- Executing Queries
```js
con.query("SELECT * FROM customers ORDER BY name", function(err, result) {
if (err) throw err;
console.log(result);
});
```
## 4- Closing Connection
```js
con.end();
```
# Examples
## 1- ORDER BY
```js
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "yourusername",
password: "yourpassword",
database: "mydb"
});
con.connect(function(err) {
if (err) throw err;
con.query("SELECT * FROM customers ORDER BY name DESC", function(err, result) {
if (err) throw err;
console.log(result);
});
});
```
## 2- Limit
```js
con.connect(function(err) {
if (err) throw err;
var sql = "SELECT * FROM customers LIMIT 5";
con.query(sql, function(err, result) {
if (err) throw err;
console.log(result);
});
});
```
## 3- join
```js
con.connect(function(err) {
if (err) throw err;
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);
});
});
```
## 3- Delete
```js
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "yourusername",
password: "yourpassword",
database: "mydb"
});
con.connect(function(err) {
if (err) throw err;
var sql = "DELETE FROM customers WHERE address = 'Mountain 21'";
con.query(sql, function(err, result) {
if (err) throw err;
console.log("Number of records deleted: " + result.affectedRows);
});
});
```
## 4- Drop
```js
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "yourusername",
password: "yourpassword",
database: "mydb"
});
con.connect(function(err) {
if (err) throw err;
var sql = "DROP TABLE customers";
con.query(sql, function(err, result) {
if (err) throw err;
console.log("Table deleted");
});
});
```
## 5- Update
```js
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "yourusername",
password: "yourpassword",
database: "mydb"
});
con.connect(function(err) {
if (err) throw err;
var sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'";
con.query(sql, function(err, result) {
if (err) throw err;
console.log(result.affectedRows + " record(s) updated");
});
});
```
## 6- insert table
```js
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
var sql = "UPDATE users SET email = 'newemail@example.com' WHERE id = ?";
var userId = 123;
con.query(sql, [userId], function(err, result) {
if (err) throw err;
console.log(result.affectedRows + " record(s) updated");
});
});
```
# Node.js / MongoDB
## 1- connecting mongodb
```js
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/mydb";
MongoClient.connect(url, function(err, db) {
if (err) throw err;
console.log("Database created!");
});
```
## 2- Collection: Inserting Data, select, delete
```js
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("customers", function(err, res) {
if (err) throw err;
console.log("Collection created!");
});
});
```
## 3- Closing Connection
```js
db.close();
```
# Examples
## 1- Insert
### insertone
```js
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 = { name: "Company Inc", address: "Highway 37" };
dbo.collection("customers").insertOne(myobj, function(err, res) {
if (err) throw err;
console.log("1 document inserted");
db.close();
});
});
```
### insertMany
```js
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("mydb");
var myobj = [
{ name: 'John', address: 'Highway 71'},
{ name: 'Peter', address: 'Lowstreet 4'},
{ name: 'Amy', address: 'Apple st 652'},
{ name: 'Hannah', address: 'Mountain 21'},
{ name: 'Michael', address: 'Valley 345'},
{ name: 'Viola', address: 'Sideway 1633'}
];
dbo.collection("customers").insertMany(myobj, function(err, res) {
if (err) throw err;
console.log("Number of documents inserted: " + res.insertedCount);
db.close();
});
});
```
## 2- unique id
```js
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("mydb");
var myobj = [
{ _id: 154, name: 'Chocolate Heaven'},
{ _id: 155, name: 'Vanilla Dream'}
];
dbo.collection("products").insertMany(myobj, function(err, res) {
if (err) throw err;
console.log(res);
db.close();
});
});
```
## 3- find
### findOne -- find the first occurence
```js
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("customers").findOne({name: 'John'}, function(err, result) {
if (err) throw err;
console.log(result.name);
db.close();
});
});
```
### find -- general to find all matches
```js
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("customers").find().toArray(function(err, result) {
if (err) throw err;
console.log(result.name);
db.close();
});
});
```
## 4- query
### Basic Query
```js
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 = { address: "Park Lane 38" };
dbo.collection("customers").find(query).toArray(function(err, result) {
if (err) throw err;
console.log(result);
db.close();
});
});
```
### Find documents where the address starts with specific letter
```js
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("mydb");
var query = { address: /^S/ };
dbo.collection("customers").find(query).toArray(function(err, result) {
if (err) throw err;
console.log(result);
db.close();
});
});
```
## 5- Limit
```js
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("customers").find().limit(5).toArray(function(err, result) {
if (err) throw err;
console.log(result);
db.close();
});
});
```
## 6- sort
```js
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 mysort = { name: 1 };
dbo.collection("customers").find().sort(mysort).toArray(function(err, result) {
if (err) throw err;
console.log(result);
db.close();
});
});
```
```js
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 mysort = {age: -1, name: 1};
dbo.collection("customers").find().sort(mysort).toArray(function(err, result) {
if (err) throw err;
console.log(result);
db.close();
});
});
```
## 7- Delete
### deleteone
```js
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 = { address: 'Mountain 21' };
dbo.collection("customers").deleteOne(myquery, function(err, obj) {
if (err) throw err;
console.log("1 document deleted");
db.close();
});
});
```
### deletemany
```js
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 = { address: /^O/ };
dbo.collection("customers").deleteMany(myquery, function(err, obj) {
if (err) throw err;
console.log(obj.result.n + " document(s) deleted");
db.close();
});
});
```
## 8- Drop
### drop()
```js
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("customers").drop(function(err, delOK) {
if (err) throw err;
if (delOK) console.log("Collection deleted");
db.close();
});
});
```
- الكود دا اسهل وافضل فالاستخدام ⬇️
### dropcollection
```js
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.dropCollection("customers", function(err, delOK) {
if (err) throw err;
if (delOK) console.log("Collection deleted");
db.close();
});
});
```
## 9- update
### updateOne
```js
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");
// the old variable
var myquery = { address: "Valley 345" };
// the new variable
var newvalues = { $set: {name: "Mickey", address: "Canyon 123" } };
dbo.collection("customers").updateOne(myquery, newvalues, function(err, res) {
if (err) throw err;
console.log("1 document updated");
db.close();
});
});
```
### updatemany
```js
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 = { address: /^S/ };
var newvalues = {$set: {name: "Minnie"} };
dbo.collection("customers").updateMany(myquery, newvalues, function(err, res) {
if (err) throw err;
console.log(res.result.nModified + " document(s) updated");
db.close();
});
});
```
Sign in with Wallet
Connect another wallet