前言

一、SQL与Sequelize中的分组查询

Sequelize模型之间存在关联关系,这么些关系表示了数据库中对应表之间的主/外键关系。基于模型关系得以完毕关联表之间的接连查询、更新、删除等操作。本文将通过三个示范,介绍模型的定义,创制模型关联关系,模型与关系关系共同数据库,及涉嫌模型的增、删、改、查操作。

我们都驾驭在运用Sequelize进行关联模型(表)间连接查询时,我们会经过model/as来钦赐已存在关联关系的连日查询模型,或是通过association来一直钦点连接查询模型关系。那么,两个各应该在什么样意况下利用啊?

1.1 SQL中的分组查询

数据库中的表之间存在必然的涉及关系,表之间的关联基于主/外键进行关联、创制约束等。关系表中的数据分为1对1(1:1)、1对多(1:M)、多对多(N:M)三种关系关系。

一、 示例图谋

SQL查询中,通GROUP BY语名落成分组查询。GROUP BY子句要和聚合函数协作使用技巧幸不辱命分组查询,在SELECT查询的字段中,若无动用聚合函数就必须出现在ORDER BY子句中。分组查询后,查询结果为四个或七个列分组后的结果集。

在Sequelize中确立关系关系,通过调用模型(源模型)的belongsTo、hasOne、hasMany、belongsToMany方法,再将在建设构造关系的模子(指标模型)做为参数字传送入就可以。那一个方法会按以下准则创建关联关系:

模型定义

GROUP BY语法

hasOne – 与目标模型构建1:1涉嫌关系,关联关系(外键)存在于目标模型中。

先是,定义User和Company七个模型:

SELECT 列名, 聚合函数(列名)
FROM 表名
WHERE 列名 operator value
GROUP BY 列名 
[HAVING 条件表达式] [WITH ROLLUP]

belongsTo – 与对象模型创设1:1涉嫌关系,关联关系(外键)存在于源模型中。

'use strict'

const Sequelize = require('sequelize');

// 创建 sequelize 实例
const sequelize = new Sequelize('db1', 'root', '111111', {logging: console.log});

// 定义User模型
var User = sequelize.define('user', {
 id:{type: Sequelize.BIGINT(11), autoIncrement:true, primaryKey : true, unique : true},
 name: { type: Sequelize.STRING, comment:'姓名' },
 sex: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0, comment:'性别' },
 companyId: { type: Sequelize.BIGINT(11), field: 'company_id', allowNull: false, comment:'所属公司' },
 isManager: { type: Sequelize.BOOLEAN, field: 'is_manager', allowNull: false, defaultValue: false, comment:'是否管理员'}
}, 
{ charset: 'utf8',
 collate: 'utf8_general_ci'});

// 定义Company模型
var Company = sequelize.define('company', {
 id:{ type:Sequelize.BIGINT(11), autoIncrement:true, primaryKey : true, unique : true},
 name: { type: Sequelize.STRING, comment:'公司名称' }
}, 
{ charset: 'utf8',
 collate: 'utf8_general_ci'});

// 定义User-Company关联关系
User.belongsTo(Company, {foreignKey:'companyId'});

// sequelize.sync({force:true}).then(() => {
// process.exit();
// });

在以上语句中:

hasMany – 与对象模型创立1:N关联关系,关联关系(外键)存在于指标模型中。

如上所示,大家定义了User和Company四个模型,并由此belongsTo钦定了User-Company之间为1:1关乎。

聚合函数 – 分组查询普通要与聚合函数一同使用,聚合函数包括:

belongsToMany –
与对象模型创设N:M关联关系,会因此sourceId和targetId成立交叉表。

安插数据

  •      COUNT()-用于总计记录条数
  •      SUM()-用于计算字段的值的总和
  •      AVG()-用于计算字段的值的平均值
  •      MAX-用于查找查询字段的最大值
  •      MIX-用于查找查询字段的矮小值

为了能够理解表明模型关系的定义及涉嫌模型的行使,大家定义如下4个模型对象:

接下去基于刚(Yu-Gang)定义的关系模型插入一些测验数据:

GROUP BY子名-用于内定分组的字段

用户(User)-与别的模型存在1:1、1:N、N:M

Company.create({name:'某公司'}).then((result) => {
 return Promise.all([
 User.create({name:'何民三', sex:1, companyId:result.id, isManager: true}),
 User.create({name:'张老二', sex:1, companyId:result.id})
 ])
}).then((result) => {
 console.log('done');
}).catch((err) => {
 console.error(err);
});

HAVING子名-用于过滤分组结果,符合条件表明式的结果将会被出示

用户登陆消息(UserCheckin)-与User存在1:1关乎

二、使用model/as

WITH ROLLUP子名-用于内定追加一条记下,用于汇总前面包车型地铁数据

用户地址(UserAddress)-与User存在N:1关系

在展开三番五次查询时,假使已经定义模型间的关联关系。就足以在inlude查询选择中,通过’model’属性钦点要连接查询的模型,还足以由此’as’属性钦定小名。

1.2 Sequelize中的分组查询

角色(Role)-与User存在N:M关系

如,从User模型中查询贰个用户,并询问该用户所在的商家音信:

接纳聚合函数

那多少个模型的E-Murano结构如下:

var include = [{
 model: Company,
 as: 'company'
}];
User.findOne({include:include}).then((result) => {
 console.log(result.name + ' 是 '+result.company.name+' 的员工');
}).catch((err) => {
 console.error(err);
});

Sequelize提供了聚合函数,能够直接对模型实行联谊查询:

必发娱乐官方网站 1

询问结果如下:

  1. aggregate(field, aggregateFunction,
    [options])-通过点名的聚合函数实行询问
  2. sum(field, [options])-求和
  3. count(field, [options])-计算查询结果数
  4. max(field, [options])-查询最大值
  5. min(field, [options])-查询最小值

接下去上代码,代码和瓷土不符,请小心!

何民三 是 某公司 的员工

以上那个聚合函数中,能够经过options.attributesoptions.attributes质量钦定分组相关字段,并得以由此options.having内定过滤条件,但并未有直接钦赐WITH ROLLUP子句的参数。

代码写的有一点点low,无法,!

三、使用association

如,使用.sum()查询订单数量抢先1的用户订单额:

/**
 * 大家就按照我的步骤来,一点一点,要有耐心哦
 * 我相信,最后肯定有你想要的!加油
 */
//引入框架
const Sequelize = require('sequelize');
//创建ORM实例
const sequelize = new Sequelize('sequlizedb', 'root', 'guoguo',
 {
  'dialect': 'mysql', // 数据库使用mysql
 }
);
//验证连接
sequelize
 .authenticate()
 .then(() => {
  console.log('链接成功');
 })
 .catch((error) => {
  console.log('链接失败' + error);
 })
//模型的创建

const User = sequelize.define('user', {
 name: Sequelize.STRING,
 age: Sequelize.INTEGER,
}, {
  freezeTableName: true,
 });

// User.create({
//  name: 'guo',
//  age: 25
// })
//  .then((result) => {
//   console.log('=======添加成功===================');
//   console.log(result);
//   console.log('==========================');

//  })
//  .catch((error) => {
//   console.log('==========================');
//   console.log('添加失败' + error);
//   console.log('==========================');

//  });

// const Role=sequelize.define('role',{
//  name:{
//   type:sequelize.STRING,
//  }
// },
// {freezeTableName:true});


const Message = sequelize.define('message', {
 text: Sequelize.STRING,
}, {
  freezeTableName: true,
 });

const Image = sequelize.define('image', {
 url: Sequelize.STRING,
}, {
  freezeTableName: true,
 });
//删除表
// sequelize.drop()
// .then((logging)=>{
//  console.log('==========================');
//  console.log('删除成功!'+logging);
//  console.log('==========================');

// })
// .catch((error)=>{
//  console.log('==========================');
//  console.log('删除失败'+error);
//  console.log('==========================');

// });

//建立关系
// Message.belongsTo(User);
// Message.hasMany(Image);
//同步到数据库
// sequelize.sync({
//  force: true,
// }).then(() => {
//  console.log('==========================');
//  console.log('同步成功');
//  console.log('==========================');

// }).catch(() => {
//  console.log('==========================');
//  console.log('同步失败');
//  console.log('==========================');

// });

//cudr
function addUers(name, age) {
 User.create({
  name: name,
  age: age,
 }).then((log) => {
  log = JSON.stringify(log);
  console.log('==========================');
  console.log('增加用户成功' + log);
  console.log('==========================');

 }).catch((error) => {
  console.log('==========================');
  console.log('增加用户失败' + error);
  console.log('==========================');

 });

}
function addMessage(userId, text) {
 Message.create({
  text: text,
  userId: userId,
 }).then((log) => {
  log = JSON.stringify(log);
  console.log('==========================');
  console.log('增加成功!' + log);
  console.log('==========================');

 }).catch((error) => {
  console.log('==========================');
  console.log('增加失败!' + error);
  console.log('==========================');

 });
}
function addImage(messageId, imageUrl) {
 Image.create({
  url: imageUrl,
  messageId: messageId,
 }).then((log) => {
  log = JSON.stringify(log);
  console.log('==========================');
  console.log('添加图片成功' + log);
  console.log('==========================');

 }).catch((error) => {
  console.log('==========================');
  console.log('添加图片失败' + error);
  console.log('==========================');

 });
}
//测试
//addUers('杨雪娇',22);
//addMessage(2, '杨雪娇发来的消息3');

// addImage(5,'http://3.png');
// addImage(6,'http://4.png');
// addImage(2,'http://2.png');
// //
function getAllMessage() {
 Message.findAll({
  where: {
   userId: 2
  },
  include: [
   {
    model: User,
    attributes: [
     'id',
     'name',
    ],
   },
   {
    model: Image,
    attributes: [
     'id',
     'url'
    ]
   }
  ],
 }).then((result) => {
  result = JSON.stringify(result);
  console.log('==========================');
  console.log(result);
  console.log('==========================');


 }).catch((error) => {
  console.log('==========================');
  console.log('查询失败' + error);
  console.log('==========================');

 });
}
//测试
//getAllMessage();
//删除消息
function delMessage(userId, messageId) {
 Message.destroy({
  where: {
   userId: userId,
   id: messageId,
  },

 }).then((log) => {
  log = JSON.stringify(log);
  console.log('==========================');
  console.log('删除消息成功!' + log);
  console.log('==========================');

 }).catch((error) => {
  console.log('==========================');
  console.log('删除消息失败!' + error);
  console.log('==========================');

 });
}
//测试
//测试发现问题 如果不设置级联 则,从属message表的image表记录不会删除,而只是出现对应messageId 为NULL的现象
//delMessage(2,4);

const Role = sequelize.define('role', {
 name: {
  type: Sequelize.STRING, allowNull: true,
 }
}, {
  freezeTableName: true,
 });


//对于单个模型的同步
// Role.sync().then((log) => {
//  log = JSON.stringify(log);
//  console.log('==========================');
//  console.log('Role表数据同步成功' + log);
//  console.log('==========================');
//  Role.create({
//   name: '管理员'
//  }).then((log) => {
//   log = JSON.stringify(log);
//   console.log('==========================');
//   console.log('添加的数据为' + log);
//   console.log('==========================');

//  }).catch((error) => {
//   console.log('==========================');
//   console.log('添加数据失败' + error);
//   console.log('==========================');

//  });

// }).catch((error) => {
//  console.log('==========================');
//  console.log('Role模型与表数据同步失败' + error);
//  console.log('==========================');

// });

//定义User1模型
const User1 = sequelize.define('user1', {
 name: {
  type: Sequelize.STRING,
  validate: {
   notEmpty: true,
   len: [2, 30],
  }
 },
 age: {
  type: Sequelize.STRING,
  defaultValue: 21,
  validate: {
   isInt: {
    msg: '年龄必须是整数!',
   }
  }

 },
 email: {
  type: Sequelize.STRING,
  validate: {
   isEmail: true,
  }
 },
 userpicture: Sequelize.STRING,
}, {
  freezeTableName: true,
 });
//
//同步User1模型
// User1.sync().then((log) => {
//  log = JSON.stringify(log);
//  console.log('==========================');
//  console.log('User1表数据同步成功' + log);
//  console.log('==========================');
// }).catch((error) => {
//  console.log('==========================');
//  console.log('User1模型与表数据同步失败' + error);
//  console.log('==========================');
// });

function addUser1(userInfo) {
 User1.create({
  name: userInfo.name,
  age:userInfo.age,
  email:userInfo.email,
 }).then((log) => {
  log = JSON.stringify(log);
  console.log('==========================');
  console.log('添加的数据为' + log);
  console.log('==========================');

 }).catch((error) => {
  console.log('==========================');
  console.log('添加数据失败' + error);
  console.log('==========================');

 });
}
const userInfo={
 name:'郭东生',
 //age:0.1,//Validation error: 年龄必须是整数!
 age:22,
 email:'7758@qq.com',
 //email:'7758',//Validation error: Validation isEmail on email failed
}
addUser1(userInfo);

连接查询时,假如要三回九转查询的四个模型间事先未曾定义连接关系,可能要使用定义之外的连接关系。那时,能够由此association来定义或再度定义模型关系。

Order.sum('price', {attributes:['name'], group:'name', plain:false, having:['COUNT(?)>?', 'name', 1]}).then(function(result){
 console.log(result);
})

以上那篇浅谈Node.js
ORM框架Sequlize之表间关系就是我分享给大家的全体内容了,希望能给大家七个参谋,也愿意大家多多帮忙脚本之家。

如,查询Company模型中的任意三个小卖部,并询问该集团的总指挥:

变动的SQL语句如下:

var include = [{
 association: Company.hasOne(User, {foreignKey:'companyId', as:'manager'}),
 where: {isManager:true}
}]

Company.findOne({include:include}).then((result) => {
 console.log(result.name +' 的管理员是 ' +result.manager.name);
}).catch((err) => {
 console.error(err);
});
SELECT `name`, sum(`price`) AS `sum` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT('name')>1;

鉴于Company-User之间并从未优先定义模型关系,由此要求在inlude选项中内定连接查询时所要使用的涉及关系。

利用聚合参数

询问结果如下:

除直接行使聚合函数外,也得以在findAll()等格局中,钦点聚合查询有关参数达成聚合查询。查询时,一样可以透过通过options.attributesoptions.attributes品质钦点分组相关字段,并得以经过options.having点名过滤条件。与间接运用聚合函数查询不雷同,通过参数构建聚合查询时,要以数组或对象方式设置options.attributes参数中的聚合字段,并索要经过sequelize.fn()措施传入聚合函数。

某公司 的管理员是 何民三

如,使用.findAll()询问订单数量超过1的用户订单额:

association除了用于钦命此前从未概念的模子关系,还足以用于重新用于定义模型关系。如,假诺我们经过hasMany事先定义了Company-User之间存在1:N的关系。这种关联适用于查询公司下的具备职员和工人。而上例中,大家供给通过1:1关系来查公司的管理员,因而,那时能够经过association重新定义模型关系。

Order.findAll({attributes:['name', [sequelize.fn('SUM', sequelize.col('price')), 'sum']], group:'name', having:['COUNT(?)>?', 'name', 1], raw:true}).then(function(result){
 console.log(result);
})

总结

转换的SQL语句如下:

如上就是那篇文章的全体内容了,希望本文的剧情对我们的上学大概干活能带动一定的辅助,倘使有疑难我们能够留言沟通,感谢大家对剧本之家的支撑。

SELECT `name`, sum(`price`) AS `sum` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT('name')>1;

您大概感兴趣的稿子:

  • Node.js
    Sequelize怎么着完毕数据库的读写分离
  • Sequelize
    常用操作详解及实例代码
  • Sequelize中用group
    by举行分组聚合查询
  • node.js
    Sequelize完毕单实例字段或批量自增、自减

二、使用示例

于今订单表,数据如下:

> select * from orders;
+---------+-------------+--------+-----------+---------------------+
| orderId | orderNumber | price | name  | createdOn   |
+---------+-------------+--------+-----------+---------------------+
|  1 | 00001  | 128.00 | 张小三 | 2016-11-25 10:12:49 |
|  2 | 00002  | 102.00 | 张小三 | 2016-11-25 10:12:49 |
|  4 | 00004  | 99.00 | 王小五 | 2016-11-25 10:12:49 |
|  3 | 00003  | 199.00 | 赵小六 | 2016-11-25 10:12:49 |
+---------+-------------+--------+-----------+---------------------+

2.1 轻松利用

行使分组查询,总结每一个客户的订单总额。

使用SQL语句,能够像上面那样查询:

> select name, SUM(price) from orders GROUP BY name;
+-----------+------------+
| name  | SUM(price) |
+-----------+------------+
| 张小三 |  230.00 |
| 王小五 |  99.00 |
| 赵小六 |  199.00 |
+-----------+------------+

而在Sequelize中得以像上面那样达成:

Order.findAll({attributes:['sum', [sequelize.fn('SUM', sequelize.col('name')), 'sum']], group:'name', raw:true}).then(function(result){
 console.log(result);
})

2.2 使用HAVING子句

总结订单数量凌驾1的用户的订单总金额。

应用SQL语句,能够像上面那样落成:

> select name, SUM(price) from orders GROUP BY name HAVING count(1)>1;
+-----------+------------+
| name  | SUM(price) |
+-----------+------------+
| 张小三 |  230.00 |
| 赵小六 |  199.00 |
+-----------+------------+

而利用Sequelize可以像上边那样查询:

Order.findAll({attributes:['sum', [sequelize.fn('SUM', sequelize.col('name')), 'sum']], group:'name', having:['COUNT(?)>?', 'name', 1], raw:true}).then(function(result){
 console.log(result);
})

2.3 使用WITH ROLLUP子句

WITH ROLLUP子句是MySQL
5.5+新扩展的性状,用于汇总总括结果。但本文揭露时,Sequelize还不援救该性子。

充实总额计算列:

> select name, SUM(price) from orders GROUP BY name WITH ROLLUP;
+-----------+------------+
| name  | SUM(price) |
+-----------+------------+
| 张小三 |  230.00 |
| 王小五 |  99.00 |
| 赵小六 |  199.00 |
| NULL  |  528.00 |
+-----------+------------+

2.4 连接查询与分组

为了管住有利于,大家会将不一样的新闻保存在区别的表中。如,大家会将订单信息放在一张表中,而将客户音信保存在另一张表中。对于存在涉嫌关系的两张表,我们会使用连接查询来寻找关联数据,在进展接二连三查询时,同样能够以利用聚合函数。

订单表如下:

> select * from orders;
+---------+-------------+--------+------------+---------------------+
| orderId | orderNumber | price | customerId | createdOn   |
+---------+-------------+--------+------------+---------------------+
|  1 | 00001  | 128.00 |   1 | 2016-11-25 10:12:49 |
|  2 | 00002  | 102.00 |   1 | 2016-11-25 10:12:49 |
|  3 | 00003  | 199.00 |   4 | 2016-11-25 10:12:49 |
|  4 | 00004  | 99.00 |   3 | 2016-11-25 10:12:49 |
+---------+-------------+--------+------------+---------------------+

客户表结构如下:

> select * from customers;
+----+-----------+-----+---------------------+---------------------+
| id | name  | sex | birthday   | createdOn   |
+----+-----------+-----+---------------------+---------------------+
| 1 | 张小三 | 1 | 1986-01-22 08:00:00 | 2016-11-25 10:16:35 |
| 2 | 李小四 | 2 | 1987-11-12 08:00:00 | 2016-11-25 10:16:35 |
| 3 | 王小五 | 1 | 1988-03-08 08:00:00 | 2016-11-25 10:16:35 |
| 4 | 赵小六 | 1 | 1989-08-11 08:00:00 | 2016-11-25 10:16:35 |
+----+-----------+-----+---------------------+---------------------+

采取连接查询并分组查询,总括种种客户的订单总额。

动用SQL语句询问如下:

> select c.name, SUM(o.price) AS sum from customers AS c INNER JOIN orders AS o ON o.customerId =c.id GROUP BY c.name;

Sequelize中张开延续查询时,首先必要建模间的关系关系:

Order.belongsTo(Customer, {foreignKey: 'customerId'});

延续查询及分组:

var include = [{
 model: Customer,
 required: true,
 attributes: ['name'],
}]
Order.findAll({include:include, attributes:[[sequelize.fn('SUM', sequelize.col('price')), 'sum']], group:'Customer.name', having:['COUNT(?)>?', 'name', 1], raw:true, rollup:true}).then(function(result){
 console.log(result);
})

总结

上述就是那篇小说的全体内容了,希望本文的内容对大家的学习或许办事能带来一定的救助,假诺有问号大家可以留言交换。

您恐怕感兴趣的小说:

  • Node.js
    Sequelize怎样贯彻数据库的读写分离
  • Sequelize
    常用操作详解及实例代码
  • node.js
    Sequelize完毕单实例字段或批量自增、自减
  • 关于Sequelize连接查询时inlude中model和association的区分详解

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注