筆記17課持久化-mysql

1、數(shù)據(jù)持久化 -數(shù)據(jù)持久化 -回顧課堂目標(biāo)資源node.js中實(shí)現(xiàn)持久化的多種方法文件系統(tǒng)數(shù)據(jù)庫安裝、配置node.js原生驅(qū)動(dòng)Node.js ORM - Sequelize購物車相關(guān)接口實(shí)現(xiàn)回顧模塊系統(tǒng)全局變量API課堂目標(biāo)掌握node.js中實(shí)現(xiàn)持久化的多種方法掌握、安裝和配置掌握node.js中原生驅(qū)動(dòng)模塊的應(yīng)用掌握node.js中的OR 模塊Sequelize的應(yīng)用實(shí)現(xiàn)商城案例中所需接口資源相關(guān)：node驅(qū)動(dòng)：文檔 Sequelize：文檔、apimongodb相關(guān)：MongoDB： node驅(qū)動(dòng)：文檔 mongoose：文檔redis相關(guān)：redis： node_redis：文檔nod

2、e.js中實(shí)現(xiàn)持久化的多種方法文件系統(tǒng) fs數(shù)據(jù)庫關(guān)系型數(shù)據(jù)庫-文檔型數(shù)據(jù)庫-mongodb鍵值對(duì)數(shù)據(jù)庫-redis文件系統(tǒng)數(shù)據(jù)庫/ 實(shí)現(xiàn)一個(gè)文件系統(tǒng)讀寫數(shù)據(jù)庫const fs = require(fs);function get(key) fs.readFile(./db.json, (err, data) = const json = JSON.parse(data); console.log(jsonkey););function set(key, value) fs.readFile(./db.json, (err, data) = / 可能是空文件，則設(shè)置為空對(duì)象const json

3、 = data ? JSON.parse(data) : ; jsonkey = value; / 設(shè)置值/ 重新寫入文件fs.writeFile(./db.json, JSON.stringify(json), err = if (err) console.log(err);console.log(寫入成功！);););/ 命令行接口部分const readline = require(readline); const rl = readline.createerface(input: pros.stdin,output: pros.stdout);rl.on(line, function(

4、input) const op, key, value = input.split( );if (op = get) get(key) else if (op = set) set(key, value) else if(op = quit) rl.close();else console.log(沒有該操作););rl.on(close, function() console.log(程序結(jié)束);pro);s.exit(0);安裝、配置macwindowsnode.js原生驅(qū)動(dòng)安裝模塊： npm i模塊基本使用-saveconst= require();/ 連接配置const cfg = h

5、ost: localhost, user: root,password: admin, / 修改為你的database: kaikeba / 請(qǐng)確保數(shù)據(jù)庫存在;/ 創(chuàng)建連接對(duì)象const conn =.createConnection(cfg);/ 連接conn.connect(err = if (err) throw err; else console.log(連接成功！););/ 查詢 conn.query()/ 創(chuàng)建表const CREATE_SQL = CREATE TABLE IF NOT EXISTS test (idNOT NULL AUTO_INCREMENT,Node.js

6、ORM - Sequelize概述：基于Promise的ORM(Object Relation Map)，支持多種數(shù)據(jù) 、事務(wù)、關(guān)聯(lián)等安裝： npm i sequelize基本使用：2 -Sconst Sequelize = require(sequelize);/ 建立連接const sequelize = new Sequelize(kaikeba, root, admin, host: localhost,dialect: , operatorsAliases: false);/ 定義模型const Fruit = sequelize.define(Fruit, name: type:

7、Sequelize.STRING(20), allowNull: false , price: type: Sequelize.FLOAT, allowNull: false , stock: type: Sequelize.EGER, defaultValue: 0 );/ 同步數(shù)據(jù)庫，force: true則會(huì)刪除已存在表 Fruit.sync().then() = / 添加測(cè)試數(shù)據(jù)return Fruit.create( name: 香蕉,message VARCHAR(45) NULL, PRIMARY KEY (id);const INSERT_SQL = INSERTO test(

8、message) VALUES(?); const SELECT_SQL = SELECT * FROM test; conn.query(CREATE_SQL, err = if (err) throw err;/數(shù)據(jù)conn.query(INSERT_SQL, o,world, (err, result) = if (err) throw err;console.log(result); conn.query(SELECT_SQL, (err, results) = console.log(results);conn.end(); / 若query語句有嵌套，則end需在此執(zhí)行););強(qiáng)制

9、同步：創(chuàng)建表之前先刪除已存在的表Fruit.sync(force: true)避免自動(dòng)生成時(shí)間戳字段指定表名： freezeTableName: true 或 tableName:設(shè)置前者則以mName作為表名；設(shè)置后者則按其值作為表名。Getters & Setters：可用于定義偽屬性或到數(shù)據(jù)庫字段的保護(hù)屬性/ 定義為屬性的一部分name: type: Sequelize.STRING, allowNull: false,get() const fname = this.getDataValue(name); const price = this.getDataValue(price);

10、const stock = this.getDataValue(stock);return $fname(價(jià)格： $price 庫存：$stockkg);/ 定義為模型選項(xiàng)getterMethods: amount()return this.getDataValue(stock) + kg;,setterMethods: amount(val)const idx = val.indexOf(kg); const v = val.slice(0, idx); this.setDataValue(stock, v);const Fruit = sequelize.define(Fruit, , t

11、imests: false);price: 3.5);).then() = / 查詢Fruit.findAll().then(fruits = console.log(JSON.stringify(fruits);););校驗(yàn)：可以通過校驗(yàn)功能驗(yàn)證模型字段格式、內(nèi)容，校驗(yàn)會(huì)在 create 、 update 和 save 時(shí)自動(dòng)運(yùn)行模型擴(kuò)展：可添加模型實(shí)例方法或類方法擴(kuò)展模型數(shù)據(jù)查詢/ 通過id查詢Fruit.findById(1).then(fruit = / fruit是一個(gè)Fruit實(shí)例，若沒有則為null/ 添加類級(jí)別方法Fruit.classify = function(name)

12、const tropicFruits = 香蕉, 芒果, 椰子; / 熱帶水果return tropicFruits.includes(name) ? 熱帶水果:其他水果;/ 添加實(shí)例級(jí)別方法Ftotype.totalPrice = function(count) return (this.price * count).toFixed(2);/ 使用類方法香蕉,草莓.forEach(f = console.log(f+是+Fruit.classify(f);/ 使用實(shí)例方法Fruit.findAll().then(fruits = const f1 = fruits;conso

13、le.log(買5kg$需要￥$f1.totalPrice(5););price: validate: isFloat: msg: 價(jià)格字段請(qǐng)輸入數(shù)字 ,min: args: 0, msg: 價(jià)格字段必須大于0 ,stock: validate: isNumeric: msg: 庫存字段請(qǐng)輸入數(shù)字 / 通過模型實(shí)例觸發(fā)setterMethods Fruit.findAll().then(fruits = console.log(JSON.stringify(fruits);/ 修改amount，觸發(fā)setterMethods fruits0.amount = 150kg; fru

14、its0.save(););console.log(fruit.get(););/ 通過屬性查詢Fruit.findOne( where: name: 香蕉 ).then(fruit = / fruit是首個(gè)匹配項(xiàng)，若沒有則為nullconsole.log(fruit.get(););/ 獲取數(shù)據(jù)和總條數(shù)Fruit.findAndCountAll().then(result = console.log(result.count); console.log(result.rows.length););/ 查詢操作符const Op = Sequelize.Op; Fruit.findAll(/

15、where: price: Op.lt:4 , stock:where: price: Op.lt:4,Op.gt:2 ).then(fruits = console.log(fruits.length);); Op.gte: 100 / 或語句Fruit.findAll(/ where: Op.or:price: Op.lt:4, stock: Op.gte:100where: price: Op.or:Op.gt:3).then(fruits = console.log(fruits0.get(););,Op.lt:2/ 分頁Fruit.findAll( offset: 0,limit:

16、2,)/ 排序Fruit.findAll(order: price, DESC,)/ 聚合setTimeout() = Fruit.max(price).then(max = n console.log(max, max););Fruit.sum(price).then(sum = console.log(sum, sum););, 500);更新Fruit.findById(1).then(fruit = / 方式1 fruit.price = 4;fruit.save().then()=console.log(update!););/ 方式2Fruit.update(price:4, wh

17、ere:id:1).then(r = console.log(r);console.log(update!)刪除關(guān)聯(lián)視時(shí)間而定，充裕則講，不夠則在項(xiàng)目期帶出/ 1:N關(guān)系const Player = sequelize.define(player, name: Sequelize.STRING); const Team = sequelize.define(team, name: Sequelize.STRING);/ 會(huì)添加teamId到Player表作為外鍵 Player.belongsTo(Team); / 1端建立關(guān)系 Team.hasMany(Player); / N端建立關(guān)系/ 同

18、步sequelize.sync(force:true).then(async ()= await Team.create(name: 火箭);await Player.bulkCreate(name: , teamId:1,name: , teamId:1);/ 1端關(guān)聯(lián)查詢const players = await Player.findAll(include:Team); console.log(JSON.stringify(players,null,t);/ N端關(guān)聯(lián)查詢const team = await Team.findOne(where:name:火箭,include:Player);console.log(JSON.string