sequelize多条件_Node项目使用Sequelize（二）（包括条件查询、排序、分页，批量增删改等)...

本篇包括(条件查询、AND 、OR 、NOT 、排序和分页、批量操作等)

1. 查询

查询全部

模型.findAll(findOptions: Object)

在数据库中搜索多个记录，返回数据和总计数

findOptions.where：搜索条件

findOptions.limit：记录条数限制

findOptions.offset：记录偏移

findOptions.order：记录排序方式

Sequelize 的 where 配置项完美支持了 SQL 的 where 子句的功能

const users = await UsersModel.findAll({

attributes: ['id', 'username', 'password'],

where: {

id: [4,5],

username: 'John'

}

});

// 找到之后更改一下它的password 因为findAll 返回的是一个数组

users[0].password = '111111111111111111'

users[0].save();

// 最后得到的users只有一条符合条件

键值对被转换成了 key = value 的形式，若一个对象包含多个键值对会被转换成了 AND 条件，即：k1: v1, k2: v2 转换为 k1 = v1 AND k2 = v2

2. AND 条件

const Op = Sequelize.Op;

const users = await UsersModel.findAll({

attributes: ['id', 'username', 'password'],

where: {

[Op.and]: [

{id: [4,5]},

{username: 'John'}

]

}

});

users[0].password = '105555555'

users[0].save();

console.log(users);

3. OR 条件

const Op = Sequelize.Op;

const users = await UsersModel.findAll({

attributes: ['id', 'username', 'password'],

where: {

[Op.or]: [

{id: [4,5]},

{username: 'John'}

]

}

});

users[0].password = '105555555'

users[0].save();

console.log(users);

4. not 条件

const Op = Sequelize.Op;

const users = await UsersModel.findAll({

attributes: ['id', 'username', 'password'],

where: {

[Op.not]: [

{id: [4,5]}

]

}

});

5. 其他条件 (如果你懂数据库语言下边代码看起来不难)

const { Op } = require("sequelize");

Post.findAll({

where: {

[Op.and]: [{ a: 5 }, { b: 6 }], // (a = 5) AND (b = 6)

[Op.or]: [{ a: 5 }, { b: 6 }], // (a = 5) OR (b = 6)

someAttribute: {

// Basics

[Op.eq]: 3, // = 3

[Op.ne]: 20, // != 20

[Op.is]: null, // IS NULL

[Op.not]: true, // IS NOT TRUE

[Op.or]: [5, 6], // (someAttribute = 5) OR (someAttribute = 6)

// Using dialect specific column identifiers (PG in the following example):

[Op.col]: 'user.organization_id', // = "user"."organization_id"

// Number comparisons

[Op.gt]: 6, // > 6

[Op.gte]: 6, // >= 6

[Op.lt]: 10, // < 10

[Op.lte]: 10, // <= 10

[Op.between]: [6, 10], // BETWEEN 6 AND 10

[Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15

// Other operators

[Op.all]: sequelize.literal('SELECT 1'), // > ALL (SELECT 1)

[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.startsWith]: 'hat', // LIKE 'hat%'

[Op.endsWith]: 'hat', // LIKE '%hat'

[Op.substring]: 'hat', // LIKE '%hat%'

[Op.iLike]: '%hat', // ILIKE '%hat' (case insensitive) (PG only)

[Op.notILike]: '%hat', // NOT ILIKE '%hat' (PG only)

[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.any]: [2, 3], // ANY ARRAY[2, 3]::INTEGER (PG only)

// In Postgres, Op.like/Op.iLike/Op.notLike can be combined to Op.any:

[Op.like]: { [Op.any]: ['cat', 'hat'] } // LIKE ANY ARRAY['cat', 'hat']

// There are more postgres-only range operators, see below

}

}

});

6. 嵌套条件(一看代码就懂)

const { Op } = require("sequelize");

Foo.findAll({

where: {

rank: {

[Op.or]: {

[Op.lt]: 1000,

[Op.eq]: null

}

},

// rank < 1000 OR rank IS NULL

{

createdAt: {

[Op.lt]: new Date(),

[Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000)

}

},

// createdAt < [timestamp] AND createdAt > [timestamp]

{

[Op.or]: [

{

title: {

[Op.like]: 'Boat%'

}

},

{

description: {

[Op.like]: '%boat%'

}

}

]

}

// title LIKE 'Boat%' OR description LIKE '%boat%'

}

});

第二个例子

Project.findAll({

where: {

name: 'Some Project',

[Op.not]: [

{ id: [1,2,3] },

{

description: {

[Op.like]: 'Hello%'

}

}

]

}

});

相当于下边这个sql语句

SELECT *

FROM `Projects`

WHERE (

`Projects`.`name` = 'some project'

AND NOT (

`Projects`.`id` IN (1,2,3)

OR

`Projects`.`description` LIKE 'Hello%'

)

)

7. 搜索单条记录

findOne 方法

const Op = Sequelize.Op;

const user = await UsersModel.findOne({

where: {

username: 'zhangsan'

}

})

console.log(user);

console.log(user.id);

8. 获取数据总数

findAndCountAll 方法

const result = await UsersModel.findAndCountAll();

console.log(result.count);

9. 排序

const users = await UsersModel.findAll({

attributes: ['id', 'username', 'password'],

order: [

['id', 'DESC'] // 逆序

// ['id'] 正序

]

})

console.log(users[0].username);

10. 分页

let countPerPage = 2, currentPage = 1;

const users = await UsersModel.findAll({

attributes: ['id', 'username', 'password'],

order: [

['id', 'DESC']

],

limit: countPerPage, // 每页多少条

offset: countPerPage * (currentPage - 1) // 跳过多少条

})

console.log(users);

11. 批量插入

const userCreat = await UsersModel.bulkCreate([

{username: 'lisi1', password: '9991'},

{username: 'lisi2', password: '9992'},

{username: 'lisi3', password: '9993'},

{username: 'lisi4', password: '9994'},

{username: 'lisi5', password: '9995'},

])

12.  更新指定

const Op = Sequelize.Op;

const affectedRows = await UsersModel.update(

{ username: "King" },

{

where: {

password: '9991'

}

}

);

13.删除指定

const affectedRows = await UsersModel.destroy(

{

where: {

password: '9991'

}

}

);

关于统计，关联查询与预加载，外键等等有时间再补上吧

14. 测试的全部代码

(async function() {

const Sequelize = require('sequelize');

const sequelize = new Sequelize('list', 'root', '123456', {

host: 'localhost',

dialect: 'mysql'

});

const UsersModel = await sequelize.define('Users', {

id: {

allowNull: false,

autoIncrement: true,

primaryKey: true,

type: Sequelize.INTEGER

},

username: {

type: Sequelize.STRING(20),

allowNull: false

},

password: {

type: Sequelize.CHAR(32),

allowNull: false

},

createdAt: {

allowNull: false,

type: Sequelize.DATE

},

updatedAt: {

allowNull: false,

type: Sequelize.DATE

}

}, {

tableName: 'users'

});

// 单查

// const Op = Sequelize.Op;

// const user = await UsersModel.findOne({

// where: {

// username: 'zhangsan'

// }

// })

// console.log(user.id);

// 总条数

// const Op = Sequelize.Op;

// const result = await UsersModel.findAndCountAll();

// console.log(result.count);

// 分页

// let countPerPage = 2, currentPage = 1;

// const users = await UsersModel.findAll({

// attributes: ['id', 'username', 'password'],

// order: [

// ['id', 'DESC']

// ],

// limit: countPerPage, // 每页多少条

// offset: countPerPage * (currentPage - 1) // 跳过多少条

// })

// console.log(users);

// 批量增

// const userCreat = await UsersModel.bulkCreate([

// {username: 'lisi1', password: '9991'},

// {username: 'lisi2', password: '9992'},

// {username: 'lisi3', password: '9993'},

// {username: 'lisi4', password: '9994'},

// {username: 'lisi5', password: '9995'},

// ])

// 更新某条

// const Op = Sequelize.Op;

// const affectedRows = await UsersModel.update(

// { username: "King" },

// {

// where: {

// password: '9991'

// }

// }

// );

// 删除指定

// const Op = Sequelize.Op;

// const affectedRows = await UsersModel.destroy({

// where: {

// password: '9991'

// }

// });

// 条件查询

// const users = await UsersModel.findAll({

// attributes: ['id', 'username', 'password'],

// where: {

// id: [4,5],

// username: 'John'

// }

// });

// users[0].password = '999999999'

// users[0].save();

// const Op = Sequelize.Op;

// const users = await UsersModel.findAll({

// attributes: ['id', 'username', 'password'],

// where: {

// [Op.or]: [

// {id: [4,5]},

// {username: 'John'}

// ]

// }

// });

// users[0].password = '105555555'

// users[0].save();

// console.log(users);

// const Op = Sequelize.Op;

// const users = await UsersModel.findAll({

// attributes: ['id', 'username', 'password'],

// where: {

// [Op.not]: [

// {id: [4,5]}

// ]

// }

// });

// users[0].password = '105555555'

// users[0].save();

// console.log(users);

// const users = await UsersModel.findAndCountAll();

// console.log(users.rows);

})()

未完待续。。。