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

约定不等于承诺〃 2023-02-16 12:01 40阅读 0赞

续上一篇 [Node项目使用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 条件** ###

### 3. OR 条件 ###

### 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);
    })()

未完待续。。。

[Node_Sequelize]: https://blog.csdn.net/weixin_41229588/article/details/106646315