Sequelize | 6. 模型 - 查询数据

模型

const { Sequelize, Model, DataTypes } = require('sequelize');

const sequelize = new Sequelize('db_stu', 'db_username', 'db_password', {
  host: '127.0.0.1',
  dialect: 'mysql',
  pool: {
    max: 50,
    min: 0,
    idle: 5000
  }
});

class UserModel extends Model { }

UserModel.init({
  firstName: {
    type: DataTypes.STRING,
    allowNull: false
  },
  lastName: {
    type: DataTypes.STRING,
    allowNull: true
  },
  birthday: {
    type: DataTypes.DATEONLY,
    allowNull: true
  },
  sex: {
    type: DataTypes.ENUM('male', 'female'),
    allowNull: true
  },
  teacherId: {
    type: Sequelize.INTEGER,
    references: {
      model: 't_teachers',
      key: 'id'
    }
  },
  Enable: {
    type: DataTypes.BOOLEAN,
    allowNull: true
  }
}, {
  sequelize,
  modelName: 't_users',
  freezeTableName: true,
  timestamps: false
});

findAll()方法

• findAll方法的基础用法
  相当于SELECT * FROM t_users;

(async () => {
  let users = await UserModel.findAll();
  console.log(JSON.stringify(users, null, 2));
})();

• findAll方法中，带attributes参数的用法

attributes参数的基础用法
相当于SELECT id, first_name, last_name FROM t_users;

(async () => {
  let users_less = await UserModel.findAll({
    attributes: ['id', 'first_name', 'last_name']
  })
  console.log(JSON.stringify(users_less, null, 2));
})();

attributes参数中使用sequelize.fn + sequelize.col方法来聚合数据
相当于SELECT first_name, COUNT(first_name) AS total FROM t_users;

(async () => {
  let users_count = await UserModel.findAll({
    attributes: [
      'first_name',
      [sequelize.fn('COUNT', sequelize.col('first_name')), 'total']
    ]
  })
  console.log(JSON.stringify(users_count, null, 2));
})();

attributes参数中，使用exclude来排除某个字段，使用include来添加字段到末尾
相当于SELECT first_name, last_name, birthday, sex, teacher_id, enable, COUNT(first_name) AS total FROM t_users;

(async () => {
  let users_count = await UserModel.findAll({
    attributes: {
      exclude: ['id'],
      include: [
        [sequelize.fn('COUNT', sequelize.col('first_name')), 'total']
      ]
    }
  })
  console.log(JSON.stringify(users_count, null, 2));
})();

findAll方法中，带where参数的用法

基础where查询
相当于SELECT * FROM t_users WHERE first_name = 'Guangming';

(async () => {
  let users_where = await UserModel.findAll({
    where: {
      first_name: 'Guangming'
    }
  });
  console.log(JSON.stringify(users_where, null, 2));
})();

逻辑运算符Op，与上面用法效果一致。

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

(async () => {
  let users_or = await UserModel.findAll({
    where: {
      id: {
        [Op.eq]: 2
      }
    }
  })
  console.log(JSON.stringify(users_or, null, 2));
})()

where和and组合查询
相当于SELECT * FROM t_users WHERE first_name = 'Guangming' and last_name = 'Lee';

(async() => {
  let user_and = await UserModel.findAll({
    where: {
      first_name: 'Guangming',
      last_name: 'Lee'
    }
  });
  console.log(JSON.stringify(user_and, null, 2));
})();

逻辑运算符Op，与上面用法效果一致。

(async () => {
  let users_and = await UserModel.findAll({
    where: {
      [Op.and]: [
        { first_name: 'Guangming' },
        { last_name: 'Lee' }
      ]
    }
  })
  console.log(JSON.stringify(users_and, null, 2));
})();

where和or组合查询
相当于SELECT * FROM t_users WHERE id = 2 OR id = 3;

(async () => {
  let user_or = await UserModel.findAll({
    where: {
      [Op.or]: [
        { id: 2 },
        { id: 3 }
      ]
    }
  });
  console.log(JSON.stringify(user_or, null, 2));
})();

where和in组合查询
相当于SELECT * FROM t_users WHERE id IN (2,3);

(async () => {
  let user_in = await UserModel.findAll({
    where: {
      id: [2, 3]
    }
  });
  console.log(JSON.stringify(user_in, null, 2));
})();

where和like组合查询
相当于SELECT * FROM t_users WHERE first_name LIKE '%min%';

(async () => {
  let user_like = await UserModel.findAll({
    where: {
      first_name : {
        [Op.like] : '%min%'
      }
    }
  });
  console.log(JSON.stringify(user_like, null, 2));
})();

其他条件组合查询

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: {
      // 基本
      [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)

      // 使用方言特定的列标识符 (以下示例中使用 PG):
      [Op.col]: 'user.organization_id',        // = "user"."organization_id"

      // 数字比较
      [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

      // 其它操作符

      [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' (不区分大小写) (仅 PG)
      [Op.notILike]: '%hat',                   // NOT ILIKE '%hat'  (仅 PG)
      [Op.regexp]: '^[h|a|t]',                 // REGEXP/~ '^[h|a|t]' (仅 MySQL/PG)
      [Op.notRegexp]: '^[h|a|t]',              // NOT REGEXP/!~ '^[h|a|t]' (仅 MySQL/PG)
      [Op.iRegexp]: '^[h|a|t]',                // ~* '^[h|a|t]' (仅 PG)
      [Op.notIRegexp]: '^[h|a|t]',             // !~* '^[h|a|t]' (仅 PG)

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

      // 在 Postgres 中, Op.like/Op.iLike/Op.notLike 可以结合 Op.any 使用:
      [Op.like]: { [Op.any]: ['cat', 'hat'] }  // LIKE ANY ARRAY['cat', 'hat']

      // 还有更多的仅限 postgres 的范围运算符,请参见下文
    }
  }
});