mongdb测试之emp查询

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

最终数据：

 

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);}))

 

b：列出工资最高的头三名员工姓名及其工资

db.emp.find({},{"ENAME":1,"SAL":1,"_id":0}).limit(3).sort({"SAL":-1})

 

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");

}

})

 

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));})