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

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

续上一篇 Node项目使用Sequelize操作数据库（一）（包括模型，增，删、改等)

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

1. 查询

•  查询全部

模型.findAll(findOptions: Object)
        在数据库中搜索多个记录，返回数据和总计数
        findOptions.where：搜索条件
        findOptions.limit：记录条数限制
        findOptions.offset：记录偏移
        der：记录排序方式

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,                              // = ]: 20,                             // != 20[Op.is]: null,                           // IS ]: true,                          // IS NOT ]: [5, 6],                         // (someAttribute = 5) OR (someAttribute = 6)// Using dialect specific column identifiers (PG in the following example):[Op.col]: 'useranization_id',        // = "user"."organization_id"// Number ]: 6,                              // > ]: 6,                             // >= 6[Op.lt]: 10,                             // < 10[Op.lte]: 10,                            // <= 10[Op.between]: [6, 10],                   // BETWEEN 6 AND Between]: [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.Like 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.unt);

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.unt);// 分页// 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.ws);
})()

未完待续。。。