mysql 數據庫

• [1] 首先配置 docker 環境
  采用 docker-compose 方法
  源碼: /test-mysql/docker-compose.yml

version: '3.1'
services:mysql:image: mysqlcommand: --default-authentication-plugin=mysql_native_passwordrestart: alwaysenvironment:MYSQL_ROOT_PASSWORD: exampleports:- 3307:3306adminer:image: adminerrestart: alwaysports:- 8081:8080

運行如下命令生成鏡像

docker-compose up

• [2] 打開瀏覽器 localhost:8081
  (1)登錄管理頁面
  (2)創建新數據庫 mar-mysql
  
• [3] 寫如下配置文件連接數據庫
  源碼: /test-mysql/mysql2.js
  部分代碼說明:
  (1) 連接數據庫

const mysql = require('mysql2/promise')
const config = {host: 'localhost',user: 'root',password: 'example',database: 'mar-mysql'
}// 創建連接
const connection = await mysql.createConnection(config)

(2) 創建表

let ret = await connection.execute(`CREATE TABLE IF NOT EXISTS test(id INT NOT NULL AUTO_INCREMENT,message VARCHAR(45) NULL,PRIMARY KEY (id))`
)

(3) 插入數據

let ret = await connection.execute(`INSERT INTO test (message) VALUE(?)`,['abc']);

(4) 查詢

const [rows] = await connection.execute(`
SELECT * FROM test
`)

mysql2.js

(async () => {const mysql = require('mysql2/promise');const cfg = {host: "localhost",user: "root",password: "example", // 修改為你的密碼database: "mar-mysql" // 確保數據庫存在};// 創建連接const connection = await mysql.createConnection(cfg);// console.log('conn:', connection);// IF NOT EXISTS: 如果不存在就創建// NOT NULL AUTO_INCREMENT: 非空自增// PRIMARY KEY (id): 主鍵是id// 創建表let ret = await connection.execute(`CREATE TABLE IF NOT EXISTS test(id INT NOT NULL AUTO_INCREMENT,message VARCHAR(45) NULL,PRIMARY KEY (id))`)console.log('ret:', ret);// 插入數據// ret = await connection.execute(`// INSERT INTO test (message) VALUE(?)// `, ['abc']);// console.log('insert:', ret);// 查詢const [rows, fields] = await connection.execute(`SELECT * FROM test`);console.log('select', JSON.stringify(rows));})()

執行mysql2

node .\mysql2.js

ORM

• 對象關系映射: Object Relational Mapping
• 目的: 通過使用描述對象和數據庫之間的關系的元數據,將面向對象語言程序中的對象自動持續化到關系數據庫中.
• 簡單說明: 就是使用對象的方式(而非SQL語句)來操作數據庫
• 栗子: Sequelize庫

Sequelize

• [1] 連接

const Sequelize = require('sequelize')const sequelize = new Sequelize('mar-mysql', 'root', 'example', {host: 'localhost',dialect: 'mysql',operatorAliases: false
})

• [2] 定義模型

const Fruit = sequelize.define('Fruit', {name: { type: Sequelize.STRING(20), allowNull: false },price: { type: Sequelize.FLOAT, allowNull: false },stock: { type: Sequelize.INTEGET, defaultValue: 0 }
})let ret = await Fruit.sync();

• [3] 執行

node .\sequelize

• [4] 說明: 以對象的方式編寫的程序,通過Sequelize庫,在運行時自動轉換成SQL語句

• [5] 補充: 往剛才創建的Fruit表中 插入一條數據 banana

let ret = await Fruit.create({name: "香蕉",price: 3.5
});
console.log("banana:", ret);