参考资源
- https://itbilu.com/nodejs/npm/VJIR1CjMb.html
- https://itbilu.com/nodejs/npm/V1PExztfb.html#api-findAll
- https://www.cnblogs.com/wanghui-garcia/p/10063226.html
- https://stackoverflow.com/questions/34364518/sequelize-where-on-parent-and-child-tables
- http://www.cnblogs.com/hss-blog/articles/10220267.html （组合查询几种方式）
- http://www.cppcns.com/wangluo/javascript/173346.html（group by 操作）
- https://itbilu.com/nodejs/npm/41qaV3czb.html#associations-one-to-one（连表关系理解）
基本 操作
#### 示例 datebase
1 | // 用户表 ---> 信息表 |
Sequelize 操作单表
1 | //举个例子 |
关联表查询 方式一 （查询时候建立连接）
1 | let rows = await models.yd_user.findAndCountAll({ |
原始查询 query
1 | let rows = await db['sequelize_yidian'].query( // db['sequelize_yidian'] = 自己Sequelize 连接池 |
group by 操作
1 |
|
model 建立外键关系的情况
https://itbilu.com/nodejs/npm/41qaV3czb.html#associations-one-to-one
1:1 一对一
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33//建立外键关系
yd_user.associate = function(models){
yd_user.belongsTo(models.yd_info,{foreignKey:'info_id',as:'info'}); //外键关系 info_id --> id 主键
yd_user.belongsTo(models.yd_class,{targetKey: 'class_id',foreignKey:'class_id',as:'class'}); //外键关系 class_id --> class_id 非主键
}
//外键关系
yd_info.associate = function (models) {
yd_info.hasOne(models.yd_user,{foreignKey:'info_id',as:'user'});
}
//测试执行
let rows = await models.yd_info.findAndCountAll({ //建立了外键关系
logging:true,
attributes:[
'id','age','user_name','account'
],
raw: true,
order: [['id','DESC']],
where:{id:'1'}, //关联表条件
include:[
{
model:models.yd_user,
as:'user',
attributes:[],
required:true
},
]
});1：n 一对多
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
//建立外键关系
yd_user.associate = function(models){
yd_user.belongsTo(models.yd_info,{foreignKey:'info_id',as:'info'}); //外键关系 info_id --> id 主键
yd_user.belongsTo(models.yd_class,{targetKey: 'class_id',foreignKey:'class_id',as:'class'}); //外键关系 class_id --> class_id 非主键
}
yd_class.associate = function(models){
yd_class.hasMany(models.yd_user,{sourceKey:'class_id',foreignKey:'class_id',as:'user'}); // 多对一
}
//运行测试
let rows = await models.yd_class.findAndCountAll({ //建立了外键关系
logging:true,
attributes:[
'id','class_id','class_name'
],
raw: true,
order: [['id','DESC']],
where:{class_id:'一'}, //关联表条件
include:[
{
model:models.yd_user,
as:'user',
attributes:[],
required:true
},
]
});
原始查询query执行delete与update与model操作 更新update与destory 区别
- https://cloud.tencent.com/developer/ask/47306
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51// 原始查询 query 执行 delete
let del = await await db['sequelize_yidian'].query(
`delete from yd_class where id = ? `, {
replacements: [3],
// raw:true,
//type: 'DELETE' //加上type 返回 undefined，不好判断
});
let row = del[0].affectedRows;
// let row1 = del[0]['affectedRows'];
return {
row1
};
//exp:1
let del = await await db['sequelize_yidian'].query(
`delete from yd_class where id = ? `, {
replacements: [3],
// raw:true,
//type: 'DELETE'
}).then(function(tagData){
console.log(tagData.toJSON());
});
//model.destory
let delss = await models.yd_class.destory({
where :{id:3}
});
return {
1
}
//query . update
let up = await await db['sequelize_yidian'].query(
`update yd_class set create_person= ? where id = ? `, {
replacements: ['2018121',4],
raw:false,
type: 'UPDATE' // [null,0] 加type的区别
});
// let rw =up[1];
// let rw = up[0].affectedRows;
return {data:rw};
