js mysql orm_1 教你如何用js去操作数据库 mysql-orm学习笔记

Node.js ORM - Sequelize

基本

概述：基于Promise的ORM(Object Relation Mapping)，支持多种数据库、事务、关联等

安装： npm i sequelize mysql2 -S

基本使用：

const Sequelize = require("sequelize");

// 建立连接

const sequelize = new Sequelize("kaikeba", "root", "admin", {

host: "localhost",

dialect: "mysql",

operatorsAliases: false

});

// 定义模型

const Fruit = sequelize.define("Fruit", {

name: {type: Sequelize.STRING(20), allowNull: false},

price: {type: Sequelize.FLOAT, allowNull: false},

stock: {type: Sequelize.INTEGER, defaultValue: 0}

});

// 同步数据库，force: true则会删除已存在表

Fruit.sync().then(() => {

// 添加测试数据

return Fruit.create({name: "香蕉", price: 3.5});

}).then(() => {

// 查询

Fruit.findAll().then(fruits => {

console.log(JSON.stringify(fruits));

});

});

强制同步是什么？如何使用？(会重置已有的表)

Fruit.sync({force: true})

表是默认生成时间戳的，如何去掉？

const Fruit = sequelize.define("Fruit", {}, {  timestamps: false });

指定表名： freezeTableName: true 或 tableName:'xxx'

设置前者则以modelName作为表名；设置后者则按其值作为表名。

Getters & Setters有什么作用？：可用于定义伪属性或映射到数据库字段的保护属性

// 定义为属性的一部分

name: {

type: Sequelize.STRING,

allowNull :false,

get(){

const fname = this.getDataValue("name");

const price = this.getDataValue("price");

const stock = this.getDataValue("stock");

return `${fname}(价格：￥${price} 库存：${stock}kg)`;

}

}

// 定义为模型选项

{

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);

}

}

}

// 通过模型实例触发setterMethods

Fruit.findAll().then(fruits => {

console.log(JSON.stringify(fruits));

// 修改amount，触发setterMethods

fruits[0].amount = '150kg';

fruits[0].save();

});

如何进行校验？：可以通过校验功能验证模型字段格式、内容，校验会在 create 、 update 和 save 时自动运行

price: {

validate: {

isFloat: {msg: "价格字段请输入数字"},

min: {args: [0], msg:"价格字段必须大于0"}

}

},

stock: {

validate: {

isNumeric: {msg: "库存字段请输入数字"}

}

}

如何进行模型扩展？：可添加模型实例方法或类方法扩展模型

// 添加类级别方法

Fruit.classify = function (name) {

const tropicFruits = ['香蕉', '芒果', '椰子'];

// 热带水果

return tropicFruits.includes(name) ? '热带水果':'其他水果'; };

// 添加实例级别方法

Fruit.prototype.totalPrice = function (count) {

return (this.price * count).toFixed(2);

};

// 使用类方法

['香蕉', '草莓'].forEach(f => console.log(f + '是' + Fruit.classify(f)));

// 使用实例方法

Fruit.findAll().then(fruits => {

const [f1] = fruits;

console.log(`买5kg${f1.name}需要￥${f1.totalPrice(5)}`);

});

增删改查

1增

ret = await Fruit.create({

name: "香蕉",

price: 3.5

})

2删

// 方式1

Fruit.findOne({ where: { id: 1 } }).then(r => r.destroy());

// 方式2

Fruit.destroy({ where: { id: 1 } }).then(r => console.log(r));

3改

// 方式1

Fruit.findById(1).then(fruit => {

fruit.price = 4;

fruit.save().then(() => console.log('update!!!!'));

});

// 方式2

Fruit.update({price: 4}, {where: {id: 1}}).then(r => {

console.log(r);

console.log('update!!!!')

})

// 通过属性查询

Fruit.findOne({where: {name: "香蕉"}}).then(fruit => {

// fruit是首个匹配项，若没有则为null

console.log(fruit.get());

});

// 获取数据和总条数

Fruit.findAndCountAll().then(result => {

console.log(result.count);

console.log(result.rows.length);

});

// 查询操作符

const Op = Sequelize.Op;

Fruit.findAll({

// where: { price: { [Op.lt()]:4 }, stock: { [Op.gte()]: 100 } }

where: {price: {[Op.lt]: 4, [Op.gt]: 2}}

}).then(fruits => {

console.log(fruits.length);

});

// 或语句

Fruit.findAll({

// where: { [Op.or]:[{price: { [Op.lt]:4 }}, {stock: { [Op.gte]: 100 }}] }

where: {price: {[Op.or]: [{[Op.gt]: 3}, {[Op.lt]: 2}]}}

}).then(fruits => {

console.log(fruits[0].get());

});

// 分页

Fruit.findAll({offset: 0, limit: 2,})

// 排序

Fruit.findAll({order: [['price', 'DESC']],})

// 聚合

setTimeout(() => {

Fruit.max("price").then(max => {

console.log("max", max)

})

Fruit.sum("price").then(sum => {

console.log("sum", sum);

});

}, 500)

4-2关联查询

关联

//1:N关系

const Player = sequelize.define('player', {name: Sequelize.STRING});

const Team = sequelize.define('team', {name: Sequelize.STRING});

// 会添加teamId到Player表作为外键

Player.belongsTo(Team);// 1端建立关系

Team.hasMany(Player); // N端建立关系

// 同步

sequelize.sync({force: true}).then(async () => {

await Team.create({name: '火箭'});

await Player.bulkCreate([{name: '哈登', teamId: 1}, {name: '保罗', teamId: 1}]);

// 1端关联查询

const players = await Player.findAll({include: [Team]});

console.log(JSON.stringify(players, null, '\t'));

// N端关联查询

const team = await Team.findOne({where: {name: '火箭'}, include: [Player]});

console.log(JSON.stringify(team, null, '\t'));

});

// 多对多关系

const Fruit = sequelize.define("fruit", {name: Sequelize.STRING});

const Category = sequelize.define("category", {name: Sequelize.STRING});

Fruit.FruitCategory = Fruit.belongsToMany(

Category, {through: "FruitCategory"}

);