egg中使用Sequelize操作数据库-基础用法使用案例

本文主要是介绍egg中使用Sequelize操作数据库-基础用法使用案例，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

Sequelize-文档1-文档2

安装

$ 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
[Op.notRegexp]: '^[h	a
[Op.iRegexp]: '^[h	a
[Op.notIRegexp]: '^[h	a
[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)
});

这篇关于egg中使用Sequelize操作数据库-基础用法使用案例的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---