mongodb聚合联系题目及参考答案

mongodb作业

作业要求

• 1.向emp集合中添加20条数据
  包含：员工姓名，职位，薪水，入职日期，部门，电话，爱好 等信息
  其中 部门包含部门名称和部门经理 爱好为数组

• 2.查询薪水在3000-5000之间的员工信息

• 3.查询入职最早的3名员工信息

• 4.查询每个职位的平均工资

• 5.查询每个部门的最高工资和最低工资

• 6.查询至少有唱歌和跳舞两个爱好的员工信息

• 7.对工资分等级、

  • 0–3000 蓝领
  • 3000-5000 白领
  • 5000-金领
  • 统计每个等级的人数

• 8.统计每个职位的员工姓名

• 9.查询电话号码中有豹子号的员工信息 “000” “111” … “999”

• 10.查询每个部门工资最高的员工电话

参考答案

0、创建数据库、集合

use my_db    //常见my_db数据库
db.createCollection('emp')// 创建emp 集合

1、 向emp集合中添加20条数据

db.emp.insertMany([
	{name:"小王",age:28,position:"前台",salary:3500,date:"2020/4/5",department:{bm:"人才部",job:"员工"},phone:"8871085421",address:"武汉",hobby:["爬山","打球","唱歌"]
},
	{name:"小张",age:29,position:"前台",salary:3500,date:"2020/8/5",department:{bm:"人才部",job:"员工"},phone:"8871085621",address:"重庆",hobby:["跳舞","打球","唱歌"]
},
{name:"刘妮",age:18,position:"老板娘",salary:100000,date:"2020/3/14",department:{bm:"领导",job:"老板娘"},phone:"18871085421",address:"钟祥",hobby:["睡觉","旅游","唱歌"]
},
	{name:"小何",age:21,position:"技术部主管",salary:8500,date:"2020/5/3",department:{bm:"技术部",job:"技术总监"},phone:"18871085421",address:"襄阳",hobby:["打游戏","打球","旅游"]
},
	{name:"胡八一",age:38,position:"软件工程师",salary:9000,date:"2020/3/15",department:{bm:"技术部",job:"员工"},phone:"18671085421",address:"武汉",hobby:["爬山","打球","唱歌"]
},
	{name:"小肖",age:29,position:"HR",salary:4500,date:"2020/7/5",department:{bm:"销售部",job:"员工"},phone:"15871085621",address:"仙桃",hobby:["跑步","喝酒","吹牛"]
},
	{name:"瑞哥",age:58,position:"软件工程师",salary:9000,date:"2020/3/15",department:{bm:"技术部",job:"员工"},phone:"18671085421",address:"武汉",hobby:["爬山","打球","唱歌"]
},
	{name:"江哥",age:49,position:"数据库管理员",salary:7500,date:"2020/4/5",department:{bm:"技术部",job:"员工"},phone:"13465145621",address:"襄阳",hobby:["短跑","跳远","钓鱼"]
},
	{name:"王宝",age:38,position:"网管工程师",salary:9000,date:"2020/3/15",department:{bm:"技术部",job:"员工"},phone:"18671095421",address:"武汉",hobby:["爬山","打球","喝酒"]
},
	{name:"小五",age:29,position:"网络管理员",salary:4500,date:"2020/6/5",department:{bm:"技术部",job:"员工"},phone:"13471084521",address:"山东",hobby:["睡觉","打游戏"]
},
	{name:"小韩",age:19,position:"技术部",salary:4500,date:"2020/7/5",department:{bm:"技术部",job:"员工"},phone:"13471085621",address:"山东",hobby:["钓鱼","打游戏","喝酒"]
},
	{name:"胡一",age:38,position:"系统工程师",salary:9000,date:"2020/3/15",department:{bm:"技术部",job:"员工"},phone:"16871085421",address:"上海",hobby:["爬山","打球","唱歌"]
},
	{name:"王五",age:38,position:"销售",salary:5000,date:"2020/5/15",department:{bm:"销售部",job:"负责人"},phone:"17671085421",address:"美国",hobby:["爬山","打球","唱歌"]
},
	{name:"张二狗",age:29,position:"保安",salary:2500,date:"2020/4/5",department:{bm:"保安部",job:"员工"},phone:"12012054110",address:"仙桃",hobby:["跑步","打游戏","看电影"]
},
	{name:"李二狗",age:48,position:"保安",salary:3000,date:"2020/3/15",department:{bm:"保安部",job:"负责人"},phone:"78945612311",address:"武汉",hobby:["爬山","打球","唱歌"]
},
	{name:"冥蒙",age:49,position:"推销员",salary:7500,date:"2020/5/5",department:{bm:"销售部",job:"员工"},phone:"134651453321",address:"襄阳",hobby:["短跑","跳远","聊天"]
},
	{name:"小邓",age:28,position:"策划员",salary:5000,date:"2020/6/15",department:{bm:"技术部",job:"员工"},phone:"17771095421",address:"甘肃",hobby:["爬山","打球","喝酒"]
},
	{name:"周三",age:29,position:"讲师",salary:6500,date:"2020/6/5",department:{bm:"培训部",job:"员工"},phone:"1111084521",address:"山西",hobby:["睡觉","游戏","吃饭"]
},
	{name:"赵飞",age:19,position:"讲师",salary:6500,date:"2020/7/4",department:{bm:"培训部",job:"负责人"},phone:"13471081111",address:"西安",hobby:["钓鱼","打游戏","喝酒"]
},
	{name:"小田",age:28,position:"辅导员",salary:3500,date:"2020/3/15",department:{bm:"人才部",job:"负责人"},phone:"12871085421",address:"上海",hobby:["爬山","跳舞","唱歌"]
}
])

2、查询薪水在3000-5000之间的员工

db.emp.find({salary:{$gte:3000,$lte:5000}})

3、查询入职最早的3名员工信息

 db.emp.find({}).sort({"date":1}).limit(3)

4、查询每个职位的平均工资

 db.emp.aggregate(
 {
     "$group":{
         "_id":"$position",
         "avg":{"$avg":"$salary"}
         }
     }
 )

5、查询每个部门的最高工资和最低工资

 db.emp.aggregate(
 {
     "$group":{
         "_id":"$department",
         "max":{"$max":"$salary"},
         "min":{"$min":"$salary"}
         }
     }
 )
 

6、查询至少有唱歌和跳舞两个爱好的员工信息

 db.emp.find({$and:[{"hobby":"跳舞"},{"hobby":"唱歌"}]})

7、对工资分等级

• 0–3000 蓝领
• 3000-5000 白领
• 5000-金领
• 统计每个等级的人数

var a_count=0;
var b_count=0;
var c_count=0;
db.emp.find({}).forEach(
	function(emp)
	{
	    if(emp.salary>0 && emp.salary<3000)
	    {
	        a_count+=1;
	    }
	    else if(emp.salary>=3000 && emp.salary<5000)
	    {
	        b_count+=1;
	    }else if(emp.salary == 5000)
	    { 
	        c_count+=1;
	    }
	}
)
print(" 0--3000    蓝领="+a_count+"\n","3000--5000 白领="+b_count+"\n","5000       金领="+c_count)

8、统计每个职位的员工姓名

db.emp.aggregate({
    $group:{
        _id:"$position",
        con:{$push:"$name"}
    }
})

9、查询电话号码中有豹子号的员工信息 “000” “111” … “999”

db.emp.find({$or:[{phone:/000/},{phone:/111/},{phone:/222/},{phone:/333/},{phone:/444/},{phone:/555/},{phone:/666/},{phone:/777/},{phone:/888/},{phone:/999/}]})

10、查询每个部门工资最高的员工电话

db.emp.aggregate([{
		$group:{
         _id:"$department.bm",
         max_salary:{$max:"$salary"},
         phone:{$push:"$phone"}
         
		}}])

总结

• 聚合知识

Collection对象提供了aggregate()方法来对数据进行聚合操作。
aggregate()方法的语法如下
aggregate(operators,[options],callback)

在aggregate()方法聚合运算符

运算符	说明
$project	通过重命名，添加或删除字段重塑文档。你也可以重新计算值，并添加子文档。
	包括title并排除name：{$project:{title:1,name:0}}
	把name重命名为title {KaTeX parse error: Expected '}', got 'EOF' at end of input: project{title:"name"}}
	添加一个新的total字段，并用price和tax字段计算它的值 {KaTeX parse error: Expected '}', got 'EOF' at end of input: project{total:{add:[“$price","$tax”]}}}
$match	通过使用query对象运算符来过滤文档集。
$limit	限定可以传递到聚合操作的下一个管道中的文档数量。例如{$limit:5}
$skip	指定处理聚合操作的下一个管道前跳过的一些文档
$unwind	指定一个数组字段用于分割，对每个值创建一个单独的文档。例如{$unwind:"$myArr"}
$group	把文档分成一组新的文档用于在管道中的下一级。新对象的字段必须在KaTeX parse error: Expected '}', got 'EOF' at end of input: …面的语句汇总value字段：{group:{set_id:“KaTeX parse error: Expected '}', got 'EOF' at end of input: 0_id",total:{sum:”$value"}}}
$sort	在把文档传递给处理聚合操作的下一个管道前对它们排序。排序指定一个带有field:<sort_order>属性的对象，其中<sort_order>为1表示升序，而-1表示降序

$group表达式运算符

运算符	说明
$addToSet	返回一组文档中所有文档所选字段的全部唯一值的数组。例如:colors:{$addToSet:“color”}
$first	返回一组文档中一个字段的第一个值。例如：firstValue:{$first:"$value"}
$last	返回一组文档中一个字段的最后一个值。例如:lastValue:{$last:"$value"}
$max	返回一组文档中一个字段的最大值。例如:maxValue:{$max:"$value"}
$min	返回一组文档中一个字段的最小值。例如:minValue:{$min:"$value"}
$avg	返回一组文档中以个字段的平均值。例如:avgValue:{$avg:"$value"}
$push	返回一组文档中所有文档所选字段的全部值的数组。例如:username:{$push:"$username"}
$sum	返回一组文档中以个字段的全部值的总和。例如:total:{$sum:"$value"}

聚合表达式的字符串和算术运算符

运算符	说明
$add	计算数值的总和。例如：valuePlus5:{$add:["$value",5]}
$divide	给定两个数值，用第一个数除以第二个数。例如：valueDividedBy5:{$divide:["$value",5]}
$mod	取模。例如:{$mod:["$value",5]}
$multiply	计算数值数组的乘积。例如:{$multiply:["$value",5]}
$subtract	给定两个数值，用第一个数减去第二个数。例如:{$subtract:["$value",5]}
$concat	连接两个字符串 例如：{$concat:[“str1”,“str2”]}
$strcasecmp	比较两个字符串并返回一个整数来反应比较结果。例如 {$strcasecmp:["$value","$value"]}
$substr	返回字符串的一部分。例如:hasTest：{$substr:["$value",“test”]}
$toLower	将字符串转化为小写。
$toUpper	将字符串转化为大写