面向对象操作数据库，类似Java中的JPA以及Mabatis中的mapper
我这里是使用sequelize，对数据库进行面向对象函数式编程
使用npm下载sequelize mysql2
npm install --save sequelize mysql2
连接数据库
const Sequelize = require('sequelize');
/** * 建立连接 * 数据库名称、账号、密码、OPTIONS、pool */
const sequelize =new Sequelize('node', 'root', '123456', {
host: 'localhost',
dialect: 'mysql',
operatorsAliases: false,
pool: {
max: 10,
min: 2,
acquire: 30000,
// 抛出错误之前将尝试获取连接的最长时间
idle: 10000
// 释放连接之前可以空闲的最长时间
}});
const sequelize =new Sequelize('node', 'root', '123456', {
host: 'localhost', dialect: 'mysql',
operatorsAliases: false,
pool: {
max: 10,
min: 2,
acquire: 30000, // 抛出错误之前将尝试获取连接的最长时间
idle: 10000 // 释放连接之前可以空闲的最长时间
}
});创建表/关联模型
const User = sequelize.define('user', {
// 字段
firstName: {
// 字段条件
type: Sequelize.STRING,
allowNull: false,
// 格式化返回
get() {
const firstName = this.getDataValue('firstName');
const lastName = this.getDataValue('lastName');
const age = this.getDataValue('age');
return `${lastName} ${firstName} is ${age} age;`
}
},
lastName: {
type: Sequelize.STRING,
allowNull: true // 默认为true
},
age: {
type: Sequelize.INTEGER,
allowNull: false
},
flag: {
type: Sequelize.STRING,
defaultValue: false
}
},
{
/**
* 默认表生产 创建时间 和 最后更改时间 两个字段
* 可以设置timestamps不自动生成时间戳字段
*/
timestamps: false,
// 指定值为表名 tableName: 'users',
// 指定模型名字（modelName）作为表名
freezeTableName: true,
/**
* 可在OPTIONS自定义模型的设置和获取
*/
getterMethods: {
age() {
return this.getDataValue('age')+'age';
}
},
setterMethods: {
age(val) {
const idx = val.indexOf('age');
const v = val.slice(0,idx);
this.setDataValue('age',v);
}
}
});关联某一个模型
const User = sequelize.define('users',{})同步模型
同步所有表
sequelize.sync()同步某一个表
// 强制同步（创建表），force: true 会删除已存在表
User.sync( { force: true } ).then(() => {
// return User.create({
// firstName: 'John',
// lastName: 'Hand',
// age: 11
// })
})添加数据
User.create({
firstName: 'Jane',
lastName: 'Doe',
age: 11})
.then(user => {
console.log(user.id)
})删除数据
User.destroy({ where: { id: 2 }}).then(() => { console.log('Done');})更改数据
User.update({ flag: "0" },{
where: {
id: 2
}
}).then( () => {
console.log('Update')
}查询数据
选择查询字段
User.findAll({
attributes: ['id','createdAt']
})
.then(users => {
users.forEach(user => {
console.log(user.dataValues)
})
})根据属性查询
User.findOne({
where: { id: 4 }
})
.then( user => {
console.log(user.dataValues)
})查询所有数据
User.findAll().then(users => {
users.forEach(user => {
console.log(user.dataValues)
})
})查询单一数据
User.findOne({ where: { id: 4 } }).then( user => {
console.log(user.dataValues)
})根据ID查询数据
User.findByPk(3).then(user => { console.log(user)})查询数据和总数
User.findAndCountAll().then(result => {
console.log('数据总数: ', result.count);
//console.log('数据: ', result.rows)
result.rows.forEach(user => {
console.log(user.dataValues);
})
})AND OR NOT IN
User.findAll({
where: {
firstName: 'Jane',
id: {
// [Op.not]: []
[Op.or]: [
[2,3,4],
{
[Op.gt]: 10
}
]
}
}
}).then(users => {
/**
* SELECT `id`, `firstName`, `lastName`, `age`, `flag`, `createdAt`, `updatedAt`
* FROM `users` AS `user`
* WHERE `user`.`firstName` = 'Jane' AND (`user`.`id` IN (2, 3, 4) OR `user`.`id` > 10);
*/
console.log(users.length)})User.findAll({
where: {
id: [1,2,3]
}
}).then(users => {
/**
* SELECT `id`, `firstName`, `lastName`, `age`, `flag`, `createdAt`, `updatedAt`
* FROM `users` AS `user`
* WHERE `user`.`id` IN (1, 2, 3);
*/
})User.findAll({
where: {
id: {
[Op.lt]: 4,
[Op.gt]: 2
}
}
}).then(users => {
/**
* lt: < , lte: <=
* gt: > , gte: >=
* SELECT `id`, `firstName`, `lastName`, `age`, `flag`, `createdAt`, `updatedAt`
* FROM `users` AS `user`
* WHERE (`user`.`id` < 4 AND `user`.`id` > 2);
*/
console.log(users.length)})分页查询
/**
* 分页
* SELECT `id`, `createdAt`, `updatedAt` FROM `users` AS `users` LIMIT 0, 2;
*/
User.findAll({ offset: 0, limit: 2})升序 降序
/**
* 排序 DESC 降序 ESC 升序
* SELECT `id`, `createdAt`, `updatedAt` FROM `users` AS `users` ORDER BY `users`.`id` DESC;
*/
User.findAll({
order: [
['id','DESC']
]
}).then(users => {
users.forEach(user => {
console.log(user.dataValues)
})
})分组
User.findAll({
group: 'id'
}).then(users => {
//SELECT `id`, `createdAt`, `updatedAt` FROM `users` AS `users` GROUP BY `id`;
users.forEach(user => { console.log(user.dataValues) })
})count sum max min
User.count('id').then(c => console.log(c))
// SELECT count(*) AS `count` FROM `users` AS `users`;User.count({
where: {
id: {
[Op.lt]: 10
}
}}).then(c => {
// SELECT count(*) AS `count` FROM `users` AS `users` WHERE `users`.`id` < 10;
console.log(c)
})User.max('createdAt').then(max => {
// SELECT max(`createdAt`) AS `max` FROM `users` AS `users`;
console.log(max)
})User.max('createdAt',{
where: {
id: {
[Op.lt]: 4
}
}}).then(max => {
// SELECT max(`createdAt`) AS `max` FROM `users` AS `users` WHERE `users`.`id` < 4;
console.log(max)
})User.max('createdAt',{
where: {
id: {
[Op.lt]: 4
}
}}).then(max => {
// SELECT max(`createdAt`) AS `max` FROM `users` AS `users` WHERE `users`.`id` < 4;
console.log(max)
})多表联合以及查询
1 : N
sequelize.sync({force: true}).then(async () => {
await Team.create({ name: '火箭'});
await Player.bulkCreate([
{ name: '哈登', teamId: 1 },
{ name: '哈罗', teamId: 1}
]);
// 1端关联查询
const players = await Player.findAll({include: [Team]});
console.log(JSON.stringify(players,null,'\t'));
// N端关联查询
const team = await Team.findOne({where: {name: '火箭'}, include: [Player]});
console.log(JSON.stringify(team, null, '\t'));
})N ：N
const Fruit = sequelize.define('fruit',{ name: Sequelize.STRING });
const Category = sequelize.define('category', { name: Sequelize.STRING });
Fruit.FruitCategory = Fruit.belongsToMany(Category, {
through: 'FruitCategory'
});
sequelize.sync({force: true}).then(async () => {
await Fruit.create(
{
name: '香蕉',
categories: [ { id: 1, name: '热带'}, { id: 2, name: '温带'} ]
},
{
include: [Fruit.FruitCategory]
}
);
const fruit = await Fruit.findOne({
where: {name: '香蕉'},
include: [{model: Category, through: {attributes: ['id','name']}}]
});
})事务（...暂时空缺，回头再补）
顺带贴上sequelize官网地址：sequelize.org/v5/
