Please enable JavaScript.

Coggle requires JavaScript to display documents.

MySQL/ MongoDB - Coggle Diagram

- - - - [ 範例 ]
        SELECT <columns> FROM <table> WHERE <condition>; SELECT <columns> FROM <table> WHERE <column> = <value>; SELECT <columns> FROM <table> WHERE <column> != <value>;
        // 條件篩選(equality/ inequality/ relation operator) = != > < <= >=
        // value 為數字型態不需要加引號
        SELECT <columns> FROM <table> WHERE <condition1> AND <condition2>; SELECT <columns> FROM <table> WHERE <condition1> OR <condition2>;
        // 串接兩個條件 AND OR
        SELECT <columns> FROM <table> WHERE <column> IN (<value1>, <value2>...); SELECT <columns> FROM <table> WHERE <column> NOT IN (<value1>, <value2>...);
        // 多個數值帶入 column
        SELECT <columns> FROM <table> WHERE <column> BETWEEN <minimum> AND <maximum>;
        // 介於範圍之間的值，最小值要先寫
        SELECT <columns> FROM <table> WHERE <column> IS NULL; SELECT <columns> FROM <table> WHERE <column> IS NOT NULL;
        // 拿取空值 missing value
        
        [範例]
        SELECT * FROM <table1>, <table2> WHERE <table1>.<table1 column> = <table2>.<table2 column>;
        // 一次選取兩張表個，
        
        [ 不直接將input 放入query，透過存入變數再放入 ]
        [ 範例 ]
        
        單個變數
        
        var sql ="SELECT * FROM table_name WHERE Email = ?";
        db.query(sql, req.body.email, (err, result)=>{ if (err) throw err; if(result.length>0){ res.render("home") }else{ res.cookie("user", req.body.email); res.redirect("/member"); } })
        [ 範例二 ]
        
        多個變數
        var sql = "SELECT * FROM table_name WHERE Email = ? AND Password = ?";
        db.query(sql, [req.body.email, req.body.password], (err, result)=>{ if (err) throw err; if(result.length>0){ res.cookie("user", req.body.email); res.redirect("/member") }else{ res.render("home"); } })
- - - - Sequelize
        
        [ 什麼是 Sequelize? ]
        
        Sequelize 是一個使用 JavaScript 的 ORM (Object Relational Mapping)，物件關聯對映。
        
        一種將關聯式資料庫 (MySQL) 映射 (mapping) 至物件導向 (OOP) 的資料抽象化技術。
        
        JavaScript (物件) --> ORM --> SQL (資料)
        
        Sequelize is all about models，都是透過 model 和 database 溝通，model 如同 db 裡面的 table
        
        建立資料
        
        [ Sequelize and SQLite in Node.js ]
        
        資料庫用 SQLite，和 ORM Sequelize 範例說明
        
        [ 下載和建立資料庫連線 ]
        
        下載安裝 Sequelize/ SQLite
        npm install --save sequelize npm install --save sqlite
        
        載入 sequelize 到專案
        const Sequelize = require("sequelize");
        
        利用 sequelize建構式，建立連線到 SQLite database
        
        並在建構式中加入連線資訊
        // dialect 指SQL version， storage指 SQLite store engine 的檔案路徑
        // Sequelize 會幫你建立 database 在專案中
        const Sequelize = require("sequelize"); const sequelize = new Sequelize({ dialect: "sqlite", storage: "movies.db" });
        
        定義 async function
        
        測試資料庫連線
        // Sequelize 的 authenticate() function 會回傳 promise 物件，resolve 表示成功連線到 database
        (async () => { try{ await sequelize.authenticate(); console.log("connect successful!") }catch(err){ console.log("error connect!") } })();
        
        輸入 node app.js 就會建立 database 在專案中
        
        [ Sequelize and SQLite in Node.js ]
        [ 建立 model & 測試 database ]
        
        定義一個 Movie model，在 javascript class 中 extends 用在建立 subclass
        class Movie extends Sequelize.Model{}
        
        初始化 model，設定 model 屬性，設定其他 options model
        // sequelize 只允許分配特定類型的屬性，title 要是一個字串(default 255 字元)
        Movie.init( { title: Sequelize.STRING }, { sequelize: sequelize } );
        
        讓 model 和 database 同步，在 async 中加入 sequelize.sync() 讓所有模型同步
        // CREATE TABLE IF NOT EXISTS
        // 加入{ force: true }會多做 DROP TABLE IF EXISTS
        await Movie.sync({force: true});
        
        輸入資料在 try block 中，model.create(); 帶入物件屬性的值
        await Movie.create({ title: "Toy Story", });
        
        下載用 DB Browser 查看，或印出在console
        // 將model.create()存在變數 movie 中，console.log(movie.toJSON()); 查看
        
        [ Project Layout ]
        [ 專案架構 ]
        app.js db -- index.js -- models ---- movie.js movies.db package.json package-lock.json
        [ models 資料夾中的 movie.js ]
        
        要放 require sequelize module 和 init model
        const Sequelize = require('sequelize'); module.exports = (sequelize) => { class Movie extends Sequelize.Model {} Movie.init({ title: Sequelize.STRING, }, { sequelize }); return Movie; };
        
        [ index.js 在 db 資料夾中 ]
        
        要放 Sequelize instance 和 require movie model
        const Sequelize = require('sequelize'); const sequelize = new Sequelize({ ... }); const db = { sequelize, Sequelize, models: {}, }; db.models.Movie = require('./models/movie.js')(sequelize); module.exports = db;
        
        [ app.js ]
        
        載入 database 和 model from db.models
        const db = require('./db'); const { Movie } = db.models; (async () => { ... })();
        
        1 more item...
        
        查找/ 更新資料
        
        [ Retrieve Records ]
        
        取回/ 查找資料
        
        findByPk()，透過 primary key 查找，效率佳
        
        findOne()，返回第一個查找到的元素
        
        findAll()，返回所有的 records
        
        (async () => { await db.sequelize.sync({ force: true }); try { // ... All model instances // -- findByPk()-- const movieById = await Movie.findByPk(1); console.log(movieById.toJSON()); // - findOne()-- const movieByRuntime = await Movie.findOne({ where: { runtime: 115 } }); console.log(movieByRuntime.toJSON()); // - findAll()-- const movies = await Movie.findAll(); console.log( movies.map(movie => movie.toJSON()) ); // - findAll() where-- const movies = await Movie.findAll({ where: { runtime: 92, isAvailableOnVHS: true } } catch(error) { ... } })();
        
        [ Attributes, Operators and Ordering ]
        
        屬性，添加屬性篩選去返回
        
        運算，使用Sequelize package 運算，簡化條件篩選
        
        排列，依照遞增遞減序排列
        
        (async () => { await db.sequelize.sync({ force: true }); try { // ... All model instances const movies = await Movie.findAll({ attributes: ['id', 'title'], // return only id and title where: { isAvailableOnVHS: true, releaseDate: { [Op.gte]: '2004-01-01', // greater than or equal to the date }, runtime: { [Op.gt]: 95, // greater than 95 [Op.between]: [75, 115], // between 75 and 115 }, title: { [Op.endsWith]: 'story' }, }, order: [['id', 'DESC']] // IDs in descending order order: [['releaseDate', 'ASC']], // dates in ascending order }); console.log( movies.map(movie => movie.toJSON()) ); } catch(error) { ... } })();
        const db = require('./db'); const { Movie, Person } = db.models; const { Op } = db.Sequelize;
        // in app.js
        
        [ 查找更新 Records ]
        
        使用 findByPk() 查找
        
        使用 save() update() 更新
        
        在 update 中加入 fields 屬性到 options 物件，其他屬性的更新會被忽略
        
        (async () => { await db.sequelize.sync({ force: true }); try { // ... All model instances const toyStory3 = await Movie.findByPk(3); // --save()-- toyStory3.isAvailableOnVHS = true; await toyStory3.save(); // -- update()-- await toyStory3.update({ title: 'Trinket Tale 3', // new title isAvailableOnVHS: true, }, { fields: ['isAvailableOnVHS'] }); // new title will be ignored console.log( toyStory3.get({ plain: true }) ); } catch(error) { ... } })();
        
        1 more item...
- - - - [ 步驟1: 準備好資料庫 ]
        
        建立資料庫 database
        CREATE DATABASE database_name;
        
        建立 table (確認欄位、資料型態)
        CREATE TABLE table_name( column1 datatype, column2 datatype, ... );
        
        插入資料 (value 用單引號)
        INSERT INTO table_name (column1, column2, ...) VALUES (values1, values2, ...)
        
        [ 步驟2: 連線資料庫 ]
        // db.js
        const mysql = require("mysql"); const db = mysql.createConnection({ host: process.env.DB_HOST, user: process.env.DB_USER, password: process.env.DB_PASS, database.process.env.DB_DATABASE, }); db.connect((err)=>{ if(err) throw err; console.log("mysql connected!"); }); module.exports = db;
    - - Database Keys
        
        [ Database Keys ]
        
        Unique Keys
        // 可以是null
        // 可以有多個 unique keys
        // 可以修改 value
        
        Primary Keys
        // 不可 null
        // 一個table只能有一個 primary key
        // 不能修改 value
        
        Foreign Keys
        // 兩個tabel都要有的column
        // ex: (table 2 中的 ) Foreign key value 要在(主要的table ) table1 中為 PRIMARY key value
        // foreign key constrain 限制
        // ex: 在 primary key 中不含有的 value，在 forgiven key (table 2 ) 就無法被新增
        
        [ 如何分辨 Primary/Foreign key? ]
        
        有重複的值出現的地方，為 Foreign key，Primary key 不會重複
        
        Table Relationship - Joins
        
        [ Inner Join ]
        
        兩個 table 的交集
        
        A inner join B ，在A中同時也有B的
        // 可以同時 JOIN 多個
        SELECT <columns> FROM <table 1> INNER JOIN <table 2> ON <table1.column> = <table 2.column> INNER JOIN <table 2> ON <table1.column> = <table 2.column> WHERE <table 1.column> = "value";
        
        [ 範例 ]
        // ex: Make , Model 兩張表，inner join 欄位 MakeID，並透過僅選取特定的欄位和 AS 精簡名稱增加效率
        SELECT mk.MakeName, md.ModelName FROM make AS mk INNER JOIN model AS md ON mk.MakeID = md.MakeID WHERE mk.MakeName = "Chevy";
        
        [ Outer Join ]
        
        Left Join (A)
        
        Right Join (B)
        
        Full Join (A&B)
        
        [ Left Join ]
        
        包含左邊表格的內容
        
        A left join B，為在A和B中的，或只在A中的
        // 可以同時 JOIN 多個
        SELECT <columns> FROM <table 1> LEFT OUTER JOIN <table 2> ON <table1.column> = <table 2.column> INNER JOIN <table 3> ON <equality criteria> WHERE <search criteria>...
        
        [ 範例 ]
        // ex: Make, Model 兩張表，根據 table 1(左側) LEFT OUTER JOIN table2 (右側) ，去數在 table2(右側) 中的 Model 有幾個，group by table1的 MakeName column
        // 輸出的表為欄位 MakeName (tabel1的) 和 NumberOfModel (數字)
        SELECT mk.MakeName, COUNT(md.ModelName)AS NumberOfModels FROM make AS mk LEFT OUTER JOIN model AS md ON mk.MakeID = md.MakeID GROUP BY mk.MakeName;
        
        [ 範例練習 ]
        // In a car database there is a Model table with columns, ModelID, MakeID and ModelName and a Car table with columns, CarID, ModelID, VIN, ModelYear and StickerPrice.
        // For all cars in the database, show Model Name, VIN and Sticker Price in one result set.
        SELECT md.ModelName, c.VIN, c.StickerPrice FROM Model AS md INNER JOIN Car AS c ON md.ModelID=c.ModelID;
        
        Set Operation
        
        [ 集合運算 ]
        
        UNION
        
        UNION ALL
        
        INTERSECT
        
        EXCEPT
        
        // 使用集合兩個表的 column 數量要一致
        
        [ UNION ]
        
        將兩個集合合併為一個，垂直合併兩個表格 (所有欄位都會被保留)
        
        和 INNER JOIN 不同，INNER JOIN 的結果會將兩個表格相同的 key value (ex: ID) 給排除
        
        合併後，重複的 colum 會被算在一起，僅會回傳一個結果
        
        [ 範例 ]
        // 有Make, ForeignMake 兩張表，合併兩個表並顯示出 MakeID 和 MakeName，依照字母順序排序
        SELECT MakeID, MakeName FROM Make WHERE MakeName < "D" UNION SELECT ForeignMakeID, MakeName FROM ForeignMake WHERE MakeName < "D" ORDER BY MakeName;
        // SELECT 後面接的 colum 數量要一致
        [ UNION ALL ]
        
        和 UNION 不同，有重複的 column 也都會回傳
        <query1> UNION ALL <query2>
        
        [ Intersect ]
        
        僅回傳兩個表格皆有的結果 (Overlap)
        
        若ID不同，則不會回傳
        
        [ 範例 ]
        // 有Make, ForeignMake 兩張表，合併兩個表並顯示出兩個表皆有的 MakeName，依照字母反向排序
        SELECT MakeName FROM Make INTERSECT SELECT MakeName FROM ForeignMake ORDER BY MakeName DESC;
        
        1 more item...
        
        Subqueries
        
        [ Subqueries ]
        
        query 中的 query
        
        用 IN/ NOT IN
        // 只能回傳一個column的資料
        
        [ 寫法 ]
        SELECT <columns> FROM <table1> WHERE <column1> IN ( SELECT column1 FROM <table2> WHERE <search criteria> );
        // subquery 僅能回傳一個 column 的值
        
        [ Temporary Table/ Derived Table ]
        
        用 (，透過 subquery 建立出衍生的表格
        
        不只回傳一個，可以使用多個 columns
        // 一定要使用 AS (alias) 命名
        
        [ 寫法 ]
        SELECT <columns> FROM <table> <INNER | OUTER> JOIN ( SELECT <columns> FROM <table> WHERE <search criteria> ) AS <alias> ON <JOIN criteria> ;
        [ 範例 ]
        // 兩張表Sale、Car，取所有的 Sale 資料當中，在Car表中的ModelYear 是2015的
        SELECT * FROM Sale AS s INNER JOIN (SELECT CarID, ModelYear FROM Car WHERE ModelYear = 2015) AS t ON s.Card=t.CarID;
        // 建出來的 temporary table (t) 的欄位資料會顯示在結果中
        
        1 more item...
      - [ Data Type ]
        [ 數值 ]
        
        INT, INTEGER 整數
        
        NUMERIC 浮點數
        
        [ 日期/時間 ]
        
        TIMESTAMP 根據時區變換
        
        [ 字串 ]
        
        VARCHAR(255) 可變長度
- - - - [ 指令 ]
        mongod #開啟mongo
        > help #檢視常用指令 > show dbs #檢視目前所有database > show collections #檢視當前db有的collection > db #顯示當前所在的資料庫 > use [DatabaseName] #使用此資料庫，如果該資料庫不存在則自動「建立」並使用
    - - [ 定義 ]
        
        most popular ODM work with Express and MongoDB
        
        ODM object data modeling library
        
        簡化 adding, searching, updating records in document-based database
        npm install --save mongoose
        
        [ 串接mongodb ]
        
        用 mongoose 串接 mongodb
        
        [ app.js ]
        const mongoose = require("mongoose"); mongoose.connect("mongoldb://localhost:27017/dbName") const db = mongoose.connection; db.on("error", ()=>{ console.error.bind(console, "connection error"); })
        
        用 mongoose 串接 mondodb Atlas ( 雲端cluster )
        
        先去 cluster 中點選 connect ，選擇 application，拿取連接資訊
        const mongoose = require("mongoose"); mongoose.connect("uri")
        
        [ 範例 ]
        const mongoose = require("mongoose"); const uri = "mongodb+srv://root:<password>@cluster0.z3laj.mongodb.net/<myFirstDatabase>?retryWrites=true&w=majority"; try{ mongoose.connect( uri, { useNewUrlParser: true, useUnifiedTopology: true}, ()=> console.log("Mongoose is connected") ); }catch(e){ console.log("could not connect") }
        // uri 中替換掉 <password> 和 <myFirstDatabase>
        // Replace myFirstDatabase with the name of the database that connections will use by default.
        
        [ 建立/定義 document schema ]
        
        scheme 定義 document(data) 的 property
        
        建立 models 資料夾，再建立不同的 js 檔
        
        [ user.js in models file ]
        const mongoose = require("mongoose"); const UserSchema = new mongoose.Schema({ email: { type: String, unique: true, required: true, trim: true }, password: { type: String, required: true } });
        // schema的命名會用大寫
        // trim 會將text前後的空白移除掉
        [ export model ]
        const User = mongoose.model("User", UserSchema); module.exports = User;
        // mongoose.model(modelName, schema)
        
        [ 載入模組 ]
        
        先 require mongoose schema
        
        [ index.js in routes file ]
        const User = require("../models/user");
        [ 加到 database ]
        router.post("/addData", (req, res)=>{ if(req.body.email && req.body.name && req.body.password ){ const userData = { email: req.body.email, name: req.body.name, password: req.body.password }; User.create(userData, function(error, user){ if(error){ return next(error); }else{ return res.redirect("/member"); } }); }else{ const err = new Error("All fields required."); err.status = 400; return next(err); } });