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

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

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

1. 查询

  •  查询全部

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

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

 
  1. const users = await UsersModel.findAll({

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

  3. where: {

  4. id: [4,5],

  5. username: 'John'

  6. }

  7. });

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

  9. users[0].password = '111111111111111111'

  10. users[0].save();

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

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

2. AND 条件

 
  1. const Op = Sequelize.Op;

  2. const users = await UsersModel.findAll({

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

  4. where: {

  5. [Op.and]: [

  6. {id: [4,5]},

  7. {username: 'John'}

  8. ]

  9. }

  10. });

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

  12. users[0].save();

  13. console.log(users);

3. OR 条件

 
  1. const Op = Sequelize.Op;

  2. const users = await UsersModel.findAll({

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

  4. where: {

  5. [Op.or]: [

  6. {id: [4,5]},

  7. {username: 'John'}

  8. ]

  9. }

  10. });

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

  12. users[0].save();

  13. console.log(users);

4. not 条件

 
  1. const Op = Sequelize.Op;

  2. const users = await UsersModel.findAll({

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

  4. where: {

  5. [Op.not]: [

  6. {id: [4,5]}

  7. ]

  8. }

  9. });

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

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

  2. Post.findAll({

  3. where: {

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

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

  6. someAttribute: {

  7. // Basics

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

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

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

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

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

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

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

  16.  
  17. // Number comparisons

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

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

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

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

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

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

  24.  
  25. // Other operators

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

  28.  
  29. [Op.in]: [1, 2], // IN [1, 2]

  30. [Op.notIn]: [1, 2], // NOT IN [1, 2]

  31.  
  32. [Op.like]: '%hat', // LIKE '%hat'

  33. [Op.notLike]: '%hat', // NOT LIKE '%hat'

  34. [Op.startsWith]: 'hat', // LIKE 'hat%'

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

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

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

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

  39. [Op.regexp]: '^[h|a|t]', // REGEXP/~ '^[h|a|t]' (MySQL/PG only)

  40. [Op.notRegexp]: '^[h|a|t]', // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)

  41. [Op.iRegexp]: '^[h|a|t]', // ~* '^[h|a|t]' (PG only)

  42. [Op.notIRegexp]: '^[h|a|t]', // !~* '^[h|a|t]' (PG only)

  43.  
  44. [Op.any]: [2, 3], // ANY ARRAY[2, 3]::INTEGER (PG only)

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

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

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

  50. }

  51. }

  52. });

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

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

  2.  
  3. Foo.findAll({

  4. where: {

  5. rank: {

  6. [Op.or]: {

  7. [Op.lt]: 1000,

  8. [Op.eq]: null

  9. }

  10. },

  11. // rank < 1000 OR rank IS NULL

  12.  
  13. {

  14. createdAt: {

  15. [Op.lt]: new Date(),

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

  17. }

  18. },

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

  20.  
  21. {

  22. [Op.or]: [

  23. {

  24. title: {

  25. [Op.like]: 'Boat%'

  26. }

  27. },

  28. {

  29. description: {

  30. [Op.like]: '%boat%'

  31. }

  32. }

  33. ]

  34. }

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

  36. }

  37. });

第二个例子

 
  1. Project.findAll({

  2. where: {

  3. name: 'Some Project',

  4. [Op.not]: [

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

  6. {

  7. description: {

  8. [Op.like]: 'Hello%'

  9. }

  10. }

  11. ]

  12. }

  13. });

相当于下边这个sql语句

 
  1. SELECT *

  2. FROM `Projects`

  3. WHERE (

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

  5. AND NOT (

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

  7. OR

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

  9. )

  10. )

7. 搜索单条记录

findOne 方法  

 
  1. const Op = Sequelize.Op;

  2. const user = await UsersModel.findOne({

  3. where: {

  4. username: 'zhangsan'

  5. }

  6. })

  7. console.log(user);

  8. console.log(user.id);

8. 获取数据总数

findAndCountAll 方法

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

  2. console.log(result.count);

9. 排序

 
  1. const users = await UsersModel.findAll({

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

  3. order: [

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

  5. // ['id'] 正序

  6. ]

  7. })

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

10. 分页

 
  1. let countPerPage = 2, currentPage = 1;

  2. const users = await UsersModel.findAll({

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

  4. order: [

  5. ['id', 'DESC']

  6. ],

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

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

  9. })

  10. console.log(users);

11. 批量插入

 
  1. const userCreat = await UsersModel.bulkCreate([

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

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

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

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

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

  7. ])

12.  更新指定

 
  1. const Op = Sequelize.Op;

  2. const affectedRows = await UsersModel.update(

  3. { username: "King" },

  4. {

  5. where: {

  6. password: '9991'

  7. }

  8. }

  9. );

13.删除指定

 
  1. const affectedRows = await UsersModel.destroy(

  2. {

  3. where: {

  4. password: '9991'

  5. }

  6. }

  7. );

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

14. 测试的全部代码

 
  1. (async function() {

  2. const Sequelize = require('sequelize');

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

  4. host: 'localhost',

  5. dialect: 'mysql'

  6. });

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

  8. id: {

  9. allowNull: false,

  10. autoIncrement: true,

  11. primaryKey: true,

  12. type: Sequelize.INTEGER

  13. },

  14. username: {

  15. type: Sequelize.STRING(20),

  16. allowNull: false

  17. },

  18. password: {

  19. type: Sequelize.CHAR(32),

  20. allowNull: false

  21. },

  22. createdAt: {

  23. allowNull: false,

  24. type: Sequelize.DATE

  25. },

  26. updatedAt: {

  27. allowNull: false,

  28. type: Sequelize.DATE

  29. }

  30. }, {

  31. tableName: 'users'

  32. });

  33. // 单查

  34. // const Op = Sequelize.Op;

  35. // const user = await UsersModel.findOne({

  36. // where: {

  37. // username: 'zhangsan'

  38. // }

  39. // })

  40. // console.log(user.id);

  41. // 总条数

  42. // const Op = Sequelize.Op;

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

  44. // console.log(result.count);

  45. // 分页

  46. // let countPerPage = 2, currentPage = 1;

  47. // const users = await UsersModel.findAll({

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

  49. // order: [

  50. // ['id', 'DESC']

  51. // ],

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

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

  54. // })

  55. // console.log(users);

  56. // 批量增

  57. // const userCreat = await UsersModel.bulkCreate([

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

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

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

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

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

  63. // ])

  64. // 更新某条

  65. // const Op = Sequelize.Op;

  66. // const affectedRows = await UsersModel.update(

  67. // { username: "King" },

  68. // {

  69. // where: {

  70. // password: '9991'

  71. // }

  72. // }

  73. // );

  74. // 删除指定

  75. // const Op = Sequelize.Op;

  76. // const affectedRows = await UsersModel.destroy({

  77. // where: {

  78. // password: '9991'

  79. // }

  80. // });

  81. // 条件查询

  82. // const users = await UsersModel.findAll({

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

  84. // where: {

  85. // id: [4,5],

  86. // username: 'John'

  87. // }

  88. // });

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

  90. // users[0].save();

  91. // const Op = Sequelize.Op;

  92. // const users = await UsersModel.findAll({

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

  94. // where: {

  95. // [Op.or]: [

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

  97. // {username: 'John'}

  98. // ]

  99. // }

  100. // });

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

  102. // users[0].save();

  103. // console.log(users);

  104. // const Op = Sequelize.Op;

  105. // const users = await UsersModel.findAll({

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

  107. // where: {

  108. // [Op.not]: [

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

  110. // ]

  111. // }

  112. // });

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

  114. // users[0].save();

  115. // console.log(users);

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

  117. // console.log(users.rows);

  118. })()

未完待续。。。

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值