模型
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參數(shù)的用法
attributes參數(shù)的基礎用法
相當于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參數(shù)中使用sequelize.fn+sequelize.col方法來聚合數(shù)據(jù)
相當于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參數(shù)中，使用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參數(shù)的用法
基礎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"
// 數(shù)字比較
[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' (不區(qū)分大小寫) (僅 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 的范圍運算符,請參見下文
}
}
});
