官网：https://sequelize.org/v5/manual/querying.html
定义
model/Post.js
const Sequelize = require(‘sequelize‘);
const { INTEGER, STRING, DATE } = Sequelize;
module.exports = (app, database) => {
database.define(‘post‘, {
id: {
type: INTEGER,
primaryKey: true,
autoIncrement: true,
// 可以指定字段映射
field: ‘id‘,
},
code: STRING,
content: STRING,
description: STRING,
status: STRING,
principals: Sequelize.JSON,
createdAt: DATE,
updatedAt: DATE,
});
}
查
Post.findAll({
// 分页
limit: 10,
offset: 0,
// 列名，获取此表中的相关列
attributes: [‘id‘, ‘code‘, ‘content‘],
// 排序，跟着 id 降序排
order: [‘id‘, ‘DESC‘],
// where 条件
where: {
// authorId: 2
authorId: {
$in: [12, 13]
},
status: ‘active‘
}
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;
常用操作符号
const Op = Sequelize.Op
[Op.between]: [6, 10], // BETWEEN 6 AND 10
[Op.in]: [1, 2], // IN [1, 2]
[Op.like]: ‘%hat‘, // LIKE ‘%hat‘
// 别名
$between: Op.between,
$in: Op.in,
$like: Op.like,
调用语句查，内容长度小于 6 个字符
Post.findAll({
where: sequelize.where(sequelize.fn(‘char_length‘, sequelize.col(‘content‘)), 6)
});
// SELECT * FROM post WHERE char_length(content) = 6;
查 JSON
const options = {
offset: page.offset,
limit: page.limit,
where: {
},
raw: true,
};
// JSON_CONTAINS mysqk 5.7 加入，可以查看文档
// https://dev.mysql.com/doc/refman/5.7/en/json-functions.html
options.where = {
[Op.and]: [
options.where,
Sequelize.fn(
‘JSON_CONTAINS‘,
Sequelize.col(‘department‘),
JSON.stringify({
id: parseInt(params.departmentId),
}),
)
],
};
Post.findAndCountAll(options);
增
Post.create(params);
批量增
// 数组里有多项
const insertList = [{......}, {......}];
Post.bulkCreate(insertList);
删
Post.destroy({
where: {
id: 4
}
});
// 或者先查后删
const data = await Post.findById(id);
if (!data) throw new Error(‘data not found‘);
return data.destroy();
改
Post.update({
content: ‘112333‘,
}, {
where: {
id: 4,
}
});
// 或者先查后改
const data = await Post.findById(4);
if (!data) throw new Error(‘data not found‘);
return data.update({
content: ‘test‘
});