注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

mmicky 的博客

追逐刹那的惊艳

 
 
 

日志

 
 

mongdb测试之emp查询  

2013-08-18 23:47:25|  分类: NOSQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
1:装载测试数据,将测试数据加入新的数据库week8.
方法一:编写插入语句,将测试数据插入week8。
use week8
db.emp.insert({"EMPNO":7369,"ENAME":"SMITH","JOB":"CLERK","MGR":7902,"HIREDATE":"1980-12-17","SAL":800,"COMM":null,"DEPTNO":20})
db.emp.insert({"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-02-20","SAL":1600,"COMM":300,"DEPTNO":30})
db.emp.insert({"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-02-22","SAL":1250,"COMM":500,"DEPTNO":30})
db.emp.insert({"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-04-02","SAL":2975 ,"COMM":null,"DEPTNO":20})
db.emp.insert({"EMPNO":7654,"ENAME":"MARTIN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-09-28","SAL":1250,"COMM":1400,"DEPTNO":30})
db.emp.insert({"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-01-05","SAL":2850,"COMM":null,"DEPTNO":30})
db.emp.insert({"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-06-09","SAL":2450,"COMM":null,"DEPTNO":10})
db.emp.insert({"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":"1981-11-17","SAL":5000,"COMM":null,"DEPTNO":10})
db.emp.insert({"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-08-09","SAL":1500,"COMM":0,"DEPTNO":30})
db.emp.insert({"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7698,"HIREDATE":"1981-03-12","SAL":950,"COMM":null,"DEPTNO":30})
db.emp.insert({"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST","MGR":7566,"HIREDATE":"1981-03-12","SAL":3000,"COMM":null,"DEPTNO":20})
db.emp.insert({"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"1982-01-23","SAL":1300,"COMM":null,"DEPTNO":10})
> db.dept.insert({"DEPTNO":10,"DNAME":"ACCOUNTING","LOC":"NEW YORK"})
db.dept.insert({"DEPTNO":20,"DNAME":"RESEARCH","LOC":"DALLAS"})
db.dept.insert({"DEPTNO":30,"DNAME":"SALES","LOC":"CHICAGO"})
db.dept.insert({"DEPTNO":40,"DNAME":"OPERATIONS","LOC":"BOSTON"})

方法二:将测试数据修改成CSV文件,然后倒入新的数据库week8。
emp数据格式如下,放置文件/app/8_demo1.csv
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,1980-12-17,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09,2450,,10
7839,KING,PRESIDENT,,1981-11-17,5000,,10
7844,TURNER,SALESMAN,7698,1981-08-09,1500,0,30
7900,JAMES,CLERK,7698,1981-12-03,950,,30
7902,FORD,ANALYST,7566,1981-12-03,3000,,20
7934,MILLER,CLERK,7782,1982-01-23,1300,,10

dept数据格式如下,放置文件/app/8_demo2.csv
DEPTNO,DNAME,LOC
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

导入数据:
[root@nosql3 app]# /app/mongodb/bin/mongoimport -d week8 -c emp --type csv --headerline -file /app/8_demo1.csv
[root@nosql3 app]# /app/mongodb/bin/mongoimport -d week8 -c dept --type csv --headerline -file /app/8_demo2.csv

最终数据:
mongdb测试之emp查询 - mmicky - mmicky 的博客
 
2:查询测试
a:列出每个部门的名称和人数
方法一:不修改数据模型
function getdeptname(deptno){
var cursor=db.dept.find({"DEPTNO":deptno});
if (cursor.hasNext()){
obj=cursor.next();
return obj.DNAME;
}
else{
return null;
}
}
db.emp.group({key:{DEPTNO:1},initial:{num:0},$reduce:function(obj,prev){
prev.num++}
}).forEach((function(doc){ print("dept:" +getdeptname(doc.DEPTNO) +"\t" + "person number:" + doc.num);}))
mongdb测试之emp查询 - mmicky - mmicky 的博客
 
b:列出工资最高的头三名员工姓名及其工资
db.emp.find({},{"ENAME":1,"SAL":1,"_id":0}).limit(3).sort({"SAL":-1})
mongdb测试之emp查询 - mmicky - mmicky 的博客
 
c:列出工资比上司高的员工姓名
db.emp.find().forEach(function(doc){
if (doc.MGR != "" && doc.SAL > db.emp.findOne({"EMPNO":doc.MGR}).SAL){
print("empno:" +doc.EMPNO +"\t" + "ename:" +doc.ENAME +"\t" + "sal:" +doc.SAL +"\t" + "mgrsal:" +db.emp.findOne({"EMPNO":doc.MGR}).SAL +"\t");
}
})
mongdb测试之emp查询 - mmicky - mmicky 的博客
 
d:列出平均工资高于公司总平均工资的部门名称
var total_avg=db.emp.group({initial:{salsum:0,num:0,average:0},$reduce:function(obj,prev){
prev.salsum +=obj.SAL;
prev.num++;
},
finalize:function(prev){
prev.average=prev.salsum/prev.num;
}
})

function getdeptname(deptno){
var cursor=db.dept.find({"DEPTNO":deptno});
if (cursor.hasNext()){
obj=cursor.next();
return obj.DNAME;
}
else{
return null;
}
}

db.emp.group({key:{DEPTNO:1},initial:{salsum:0,num:0},$reduce:function(obj,prev){
prev.salsum +=obj.SAL;
prev.num++;
}
}).forEach(function(doc){if (doc.salsum/doc.num>total_avg[0].average) print("dept:" +getdeptname(doc.DEPTNO) +"\t dept_avg:" + parseInt(doc.salsum/doc.num)+"\t corp_avg:" + parseInt(total_avg[0].average));})

mongdb测试之emp查询 - mmicky - mmicky 的博客
 


  评论这张
 
阅读(110)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017