模型
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 的范围运算符,请参见下文
}
}
});
网友评论