本文主要是介绍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操作数据库-基础用法使用案例的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!