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: Sequelize.STRING,
    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)
});

查询多条

findAll(opts) 或者 all(opts)

User.findAll()

分页查询

findAndCount(opts) 或者 findAndCountAll

User.findAndCount({
    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] 第一位是查询或创建的数据，第二位标识是否新建
})

分组查询

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

聚合函数	功能
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)
});

删除

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}	AND (a = 5)
[Op.or]: [{a: 5}, {a: 6}]	(a = 5 OR 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,	IS NOT TRUE
[Op.between]: [6, 10],	BETWEEN 6 AND 10
[Op.notBetween]: [11, 15],	NOT BETWEEN 11 AND 15
[Op.in]: [1, 2],	IN [1, 2]
[Op.notIn]: [1, 2],	NOT IN [1, 2]
[Op.like]: '%hat',	LIKE '%hat'
[Op.notLike]: '%hat'	NOT LIKE '%hat'
[Op.iLike]: '%hat'	ILIKE '%hat' (case insensitive) (PG only)
[Op.notILike]: '%hat'	NOT ILIKE '%hat' (PG only)
[Op.startsWith]: 'hat'	LIKE 'hat%'
[Op.endsWith]: 'hat'	LIKE '%hat'
[Op.substring]: 'hat'	LIKE '%hat%'
[Op.regexp]: '^[h	a	t]'	REGEXP/~ '^[h	a	t]' (MySQL/PG only)
[Op.notRegexp]: '^[h	a	t]'	NOT REGEXP/!~ '^[h	a	t]' (MySQL/PG only)
[Op.iRegexp]: '^[h	a	t]'	~* '^[h	a	t]' (PG only)
[Op.notIRegexp]: '^[h	a	t]'	!~* '^[h	a	t]' (PG only)
[Op.like]: { [Op.any]: ['cat', 'hat']}	LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
[Op.overlap]: [1, 2]	&& [1, 2] (PG array overlap operator)
[Op.contains]: [1, 2]	@> [1, 2] (PG array contains operator)
[Op.contained]: [1, 2]	<@ [1, 2] (PG array contained by operator)
[Op.any]: [2,3]	ANY ARRAY[2, 3]::INTEGER (PG only)
[Op.col]: 'user.organization_id'	= "user"."organization_id", with dialect specific column identifiers, PG in this example

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