官网：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.htmloptions.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‘});