增筏勒、刪茅逮、改憔购、查
-
create創(chuàng)建 -
destroy刪除 -
update更新-
fields指定更新的字段
-
-
findOnefindByPkfindAllfindAndCountAll查找
//增加焦匈、創(chuàng)建
Role.create({name:'test'}).then(res=>{})
//刪除
Role.destroy({where: { id:1}}).then(res=>{})
//改 data為修改內(nèi)容鍵值對(duì) 對(duì)象
Role.update(data, {where: { id }, fields: ['name', 'limits'] }).then(res=>{}) ;
//查
Role.findOne({where: { id:1}}).then(res=>{})
//通過(guò)id查找
Role.findByPk(id).then(res=>{}) //findById 已被棄用
//查找所有
Role.findAll().then(res=>{})
Role.all().then(res=>{})
批量插入
bulkCreate
USer.bulkCreate([
{name:'',nickname:''},
{name:'',nickname:''},
])
排除一些指定的表字段
attributes: { exclude: ['password'] } 值指定字段數(shù)組
//查
User.findOne({
where: { id:1},
attributes: { exclude: ['password'] }
}).then(res=>{})
//通過(guò)id查找
User.findByPk(id,{ attributes: { exclude: ['password'] }}).then(res=>{})
返回指定字段
attributes 值指定字段數(shù)組
User.findOne({
where: { id:1},
attributes: ['username']
}).then(res=>{})
//findAll() all() 用法同上
//通過(guò)id查找
User.findByPk(id,{ attributes:['username','age'] }).then(res=>{})
重命名
屬性可以使用嵌套數(shù)組來(lái)重命名
Model.findAll({
attributes: ['foo', ['bar', 'baz']]
});
//SELECT foo, bar AS baz ...
字段自增 自減
1.單實(shí)例自增赘淮、自減
instance.increment(fields, [options]) 字段值自增
instance.decrement(fields, [options]) 字段值自減
User.findById(1).then(function(user){
user.increment('age').then(function(user){
console.log('success');
})
})
//生成如下sql
UPDATE `user` SET `age`=`age` + 1 WHERE `id` = 1
//多個(gè)字段設(shè)置
user.increment(['age', 'number'], {by:2}).then(function(user){
console.log('success');
})
//生成如下sql
UPDATE `user` SET `age`=`age` + 2,`number`=`number` + 2 WHERE `id` = 1
//fields參數(shù)還可以通過(guò)對(duì)象傳入辕录，并指定自增、自減值梢卸。這種情況下走诞，會(huì)忽略options.by參數(shù)。
//如低剔，將用戶的number增加2速梗、age減小1：
user.increment({age:-1, number:2}, {by:5}).then(function(user){
console.log('success');
})
//生成如下sql
UPDATE `user` SET `age`=`age` + -1,`number`=`number` + 2 WHERE `id` = 1
2.批量自增肮塞、自減
User.update({sex:sequelize.literal('`age` +1')}, {where:{number:{$gt:10}}}).then(function(user){
console.log('success');
})
//生成如下sql
UPDATE `user` SET `age`=`age` +1 WHERE `number` > 10
復(fù)雜查詢
const Op = Sequelize.Op;
user.findAll({
where: {
authorId: 12,
status: 'active'
}
});
// SELECT * FROM user WHERE authorId = 12 AND status = 'active';
user.findAll({
where: {
[Op.or]: [{authorId: 12}, {authorId: 13}]
}
});
// SELECT * FROM user WHERE authorId = 12 OR authorId = 13;
user.findAll({
where: {
authorId: {
[Op.or]: [12, 13]
}
}
});
// SELECT * FROM user WHERE authorId = 12 OR authorId = 13;
排序
order
user.findAll({
where: {
authorId: {
[Op.or]: [12, 13]
}
},
order: [
['createdAt', 'DESC'],
['id', 'DESC'],
],
});
分頁(yè)查詢 模糊查詢
findAndCountAll 能返回總頁(yè)數(shù)和當(dāng)前頁(yè) 數(shù)據(jù)
limit 限制條數(shù)
offset 偏移量
let {username,roles_id,page=1,pageSize=10}=ctx.request.query;
//注意分頁(yè)要轉(zhuǎn)化為number類(lèi)型
page=Number(page);
pageSize=Number(pageSize);
let wheres=null;
if(username||roles_id){
wheres={
username: {
// 模糊查詢
[Op.like]:'%' +username + '%',
},
roles_id:{
[Op.like]:`%${roles_id?roles_id:''}%`
}
}
}
//不需要分頁(yè)的時(shí)候直接用findAll 、all
//findAndCountAll 能返回總數(shù)
user.findAndCountAll({
where:wheres,
limit: pageSize,
offset: pageSize * (page - 1)
}).then(res=>{
//返回查到內(nèi)容
})
