Sequelize基本用法

安装

$ npm install --save sequelize
# 还需要安装以下之一:
$ npm install --save pg pg-hstore  // postgreSql
$ npm install --save mysql // mysql 或 mariadb
$ npm install --save sqlite3  
$ npm install --save tedious // MSSQL

建立连接

const Sequelize = require('sequelize')
const sequelize = new Sequelize(db.database, db.user, db.password, { //表名 用户名 密码
    host: db.host, //地址
    port: db.port, //端口
    dialect: 'mysql', //数据库类型:'mysql'|'mariadb'|'sqlite'|'postgres'|'mssql'
    pool: { // 连接池配置
        max: 5,
        min: 0,
        acquire: 30000,
        idle: 10000,
    },
    timezone: '+08:00' //时区转换
})

定义模型

const Sequelize = require('sequelize')
const moment=require('moment');
moment.locale('zh-cn');

User: sequelize.define('user', {
    id: {
        type: Sequelize.STRING(255),
        primaryKey: true, //主键
    },
    name: {
        type: Sequelize.STRING,
        defaultValue: 'test1' //设置默认值
    }
    role: Sequelize.INTEGER(11),
    open_id: Sequelize.STRING,
    describe: Sequelize.STRING,
    status: Sequelize.INTEGER(11),
    lv: Sequelize.INTEGER(11),
    token: Sequelize.STRING,
    create_time:{
    type: Sequelize.DATE,
        get() {return moment(this.getDataValue('create_time')).format('YYYY-MM-DD HH:mm:ss');}
    },
    update_time:{
        type: Sequelize.DATE,
        get() {return moment(this.getDataValue('update_time')).format('YYYY-MM-DD HH:mm:ss');}
    }
}, {
    freezeTableName: true,
    timestamps: false
})

sql、orm对应关系

sql orm
select findAll,findOne,findById,findOrCreate,findAndCountAll
update update
insert create
delete destroy

查询

查询单条数据

User.findOne({
    attributes: ['id', 'name', 'role', 'open_id', 'describe'],
    where: {
        id: id
    }
}).then(result => {
    console.log(result)
}).catch(err => {
    console.log(err)
});

结果过滤

//只显示 id,name 字段
attributes: ['id', 'name', 'role', 'open_id', 'describe']
//不显示 role 字段
attributes: { exclude: ['role'] }

查询多条

findAll

User.findAll()

分页查询

findAndCountAll

User.findAndCountAll({
    limit:10,//每页10条
    offset:0*10,//第x页*每页个数
    where:{}
});

通过id查询

findById(id,opts)

User.findById(1);

查询,不存在就新建一个

findOrCreate(opts)或者findCreateFind

User.findOrCreate({
    where: {
        open_id: req.body.open_id
    },
    defaults: {
        id: id,
        name: req.body.name,
        open_id: req.body.open_id,
        token: token,
        create_time: Date.now()
    }
}).then(result => {
    //返回值为数组,[json,created] 第一位是查询或创建的数据,第二位标识是否新建
})

只返回数据库查询结果

findOrCreate(opts)或者findCreateFind

User.findAll({
    raw:true
}).then(result => {

})

分组查询

分组查询通常要与聚合函数一起使用,聚合函数包括:

聚合函数 功能
COUNT() 用于统计记录条数
SUM() 用于计算字段的值的总和
AVG() 用于计算字段的值的平均值
MAX 用于查找查询字段的最大值
MIX 用于查找查询字段的最小值
//求表中like字段值的和
orm.Article.findAll({
    attributes: [[Sequelize.fn('SUM', Sequelize.col('like')), 'likes']],
}).then(result=>{
    result[0].get('likes')
})

更新

User.update({
    token: 'token'
}, {
    where: {
        id: l
    }
}).then(result => {
    console.log(result)
}).catch(err => {
    console.log(err)
});

新增

User.create({
    id: id,
    name: req.body.name,
    open_id: req.body.open_id,
    create_time: Date.now()
}).then(result => {
    console.log(result)
    }).catch(err => {
    console.log(err)
});

批量新增

let data = [{id: 1, name: '张三'}, {id: 1, name: '李四'}]
User.bulkCreate(data, {
    raw: true,
}).then(res => res)

删除

User.destroy({
    where: {
        id: 1
    }
}).then(result => {
    console.log(result)
}).catch(err => {
    console.log(err)
});

关联查询

一对一

sequelize 提供了两种一对一关系关联方法 belongsTo 和 hasOne

User.belongsTo(Article, { foreignKey: 'id', as: 'article',targetKey:'user_id'})
User.hasOne(Article, { foreignKey: 'user_id', as: 'article'})

第一个参数为一个Model,第二个为options配置。
foreignKey:指定外键
as:指定别名
targetKey:目标键,是源模型上的外键列指向的目标模型上的列,默认情况下是目标模型的主键
两种方法都是把userInfo表关联到User表,区别是暴露外键的表不同:
- belongsTo暴露出的是User表的‘id’字段作为外键去查询UserInfo表
- hasOne方法暴露的是Article表的‘user_id’作为外键查询User表

使用

User.findeOne({
    where: {},
    include: {
        model: Article,
        as: 'article'
        where: {},
        required: false //仅对include的结果过滤
    }
})

belongsTo 生成的sql

SELECT `user`.`id`, `user`.`name`, `article`.`id` AS `article.id`, `article`.`title` AS `article.title`, `article`.`user_id` AS `article.user_id` FROM `user` AS `user` LEFT OUTER JOIN `article` AS `article` ON `user`.`id` = `article`.`user_id` WHERE `user`.`id` = '1';

hasOne 生成的sql

SELECT `user`.`id`, `user`.`name`,`article`.`id` AS `article.id`, `article`.`title` AS `article.title`, `article`.`user_id` AS `article.user_id` FROM `user` AS `user` LEFT OUTER JOIN `article` AS `article` ON `user`.`id` = `article`.`user_id` WHERE `user`.`id` = '1';

belongsTo 使用User的外键作为条件去查询Article的主键
hasOne使用Article的外键作为条件去查询User的主键

一对多

hasMany

多对多

belongToMany

常用符号运算符

Operators 解释
[Op.and]: {a: 5} 且 (a = 5) |
[Op.or]: [{a: 5}, {a: 6}] (a = 5 或 a = 6)
[Op.gt]: 6, >= 6
[Op.gte]: 6, <= 6
[Op.lt]: 10, < 10
[Op.lte]: 10, <= 10
[Op.ne]: 20, != 20
[Op.eq]: 3, = 3
[Op.not]: true, 不是 TRUE
[Op.between]: [6, 10], 在 6 和 10 之间
[Op.notBetween]: [11, 15], 不在 11 和 15 之间
[Op.in]: [1, 2], 在 [1, 2] 之中
[Op.notIn]: [1, 2], 不在 [1, 2] 之中
[Op.like]: '%hat', 包含 '%hat'
[Op.notLike]: '%hat' 不包含 '%hat'
[Op.iLike]: '%hat' 包含 '%hat' (不区分大小写) (仅限 PG)
[Op.notILike]: '%hat' 不包含 '%hat' (仅限 PG)
[Op.startsWith]: 'hat' LIKE 'hat%'
[Op.endsWith]: 'hat' LIKE '%hat'
[Op.substring]: 'hat' LIKE '%hat%'
[Op.regexp]: '^[h a|t]' | 匹配正则表达式/~ '^[h|a|t]' (仅限 MySQL/PG)
[Op.notRegexp]: '^[h a|t]' | 不匹配正则表达式/!~ '^[h|a|t]' (仅限 MySQL/PG)
[Op.iRegexp]: '^[h a|t]' | ~* '^[h|a|t]' (仅限 PG)
[Op.notIRegexp]: '^[h a|t]' | !~* '^[h|a|t]' (仅限 PG)
[Op.like]: { [Op.any]: ['cat', 'hat']} 包含任何数组['cat', 'hat'] - 同样适用于 iLike 和 notLike
[Op.overlap]: [1, 2] && [1, 2] (PG数组重叠运算符)
[Op.contains]: [1, 2] @> [1, 2] (PG数组包含运算符)
[Op.contained]: [1, 2] <@ [1, 2] (PG数组包含于运算符)
[Op.any]: [2,3] 任何数组[2, 3]::INTEGER (仅限PG)
[Op.col]: 'user.organization_id' = 'user'.'organization_id', 使用数据库语言特定的列标识符, 本例使用 PG
const Op = Sequelize.Op;
//查询age < 18 或者小于5的数据
User.findAll({
    where: {
        age:{
            [Op.or]: {
                [Op.lt]: 18,
                [Op.eq]: 5
            }
        }
    }
}).then(result => {
    console.log(result)
}).catch(err => {
    console.log(err)
});

永久链接: https://blog.qianxiaoduan.com/archives/776