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. })()

未完待续。。。