IT技术博客

 1.使用find方法可以查询集合(在关系型数据库中也叫做表)的数据，传入空条件{}或不传条件会查询集合中所有的数据

> db.emp.find()

 { "_id" : ObjectId("54f3b29ccc2f0a163b5a9208"), "EMPNO" : 7369, "ENAME" : "SMITH", "JOB" : "CLERK", "MGR" : 7902, "HIREDATE" : "1980/12/17", "SAL" : 800, "COMM" : "", "DE

{ "_id" : ObjectId("54f3b29ccc2f0a163b5a9209"), "EMPNO" : 7499, "ENAME" : "ALLEN", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : "1981/2/20", "SAL" : 1600, "COMM" : 300,

{ "_id" : ObjectId("54f3b29ccc2f0a163b5a920a"), "EMPNO" : 7521, "ENAME" : "WARD", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : "1981/2/22", "SAL" : 1250, "COMM" : 500,

{ "_id" : ObjectId("54f3b29ccc2f0a163b5a920b"), "EMPNO" : 7566, "ENAME" : "JONES", "JOB" : "MANAGER", "MGR" : 7839, "HIREDATE" : "1981/4/2", "SAL" : 2975, "COMM" : "", "D

{ "_id" : ObjectId("54f3b29ccc2f0a163b5a920c"), "EMPNO" : 7654, "ENAME" : "MARTIN", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : "1981/9/28", "SAL" : 1250, "COMM" : 140

{ "_id" : ObjectId("54f3b29ccc2f0a163b5a920d"), "EMPNO" : 7698, "ENAME" : "BLAKE", "JOB" : "MANAGER", "MGR" : 7839, "HIREDATE" : "1981/5/1", "SAL" : 2850, "COMM" : "", "D

 { "_id" : ObjectId("54f3b29ccc2f0a163b5a920e"), "EMPNO" : 7782, "ENAME" : "CLARK", "JOB" : "MANAGER", "MGR" : 7839, "HIREDATE" : "1981/6/9", "SAL" : 2450, "COMM" : "", "D

{ "_id" : ObjectId("54f3b29ccc2f0a163b5a920f"), "EMPNO" : 7788, "ENAME" : "SCOTT", "JOB" : "ANALYST", "MGR" : 7566, "HIREDATE" : "1982/12/9", "SAL" : 4000, "COMM" : "", "

 { "_id" : ObjectId("54f3b29ccc2f0a163b5a9210"), "EMPNO" : 7839, "ENAME" : "KING", "JOB" : "PRESIDENT", "MGR" : "", "HIREDATE" : "1981/11/17", "SAL" : 5000, "COMM" : "", "

{ "_id" : ObjectId("54f3b29ccc2f0a163b5a9211"), "EMPNO" : 7844, "ENAME" : "TURNER", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : "1981/9/8", "SAL" : 1500, "COMM" : 0, "

 { "_id" : ObjectId("54f3b29ccc2f0a163b5a9212"), "EMPNO" : 7876, "ENAME" : "ADAMS", "JOB" : "CLERK", "MGR" : 7788, "HIREDATE" : "1983/1/12", "SAL" : 1100, "COMM" : "", "DE

{ "_id" : ObjectId("54f3b29ccc2f0a163b5a9213"), "EMPNO" : 7900, "ENAME" : "JAMES", "JOB" : "CLERK", "MGR" : 7698, "HIREDATE" : "1981/12/3", "SAL" : 950, "COMM" : "", "DEP

 { "_id" : ObjectId("54f3b29ccc2f0a163b5a9215"), "EMPNO" : 7934, "ENAME" : "MILLER", "JOB" : "CLERK", "MGR" : 7782, "HIREDATE" : "1982/1/23", "SAL" : 1300, "COMM" : "", "D

 { "_id" : ObjectId("54f3b29ccc2f0a163b5a9214"), "EMPNO" : 7902, "ENAME" : "FORD", "JOB" : "ANALYST", "MGR" : 7566, "HIREDATE" : "1981/12/33", "SAL" : 3000, "DEPTNO" : 20,

2.输入查询条件

通过输入查询条件可以定制要返回的数据，查询条件要求以JSON格式传入

例如查询部门变化是20的员工信息

> db.emp.find({"DEPTNO" : 20})

 { "_id" : ObjectId("54f3b29ccc2f0a163b5a9208"), "EMPNO" : 7369, "ENAME" : "SMITH", "JOB" : "CLERK", "MGR" : 7902, "HIREDATE" : "1980/12/17", "SAL" : 8 00, "COMM" : "", "DEPTNO" : 20 }

{ "_id" : ObjectId("54f3b29ccc2f0a163b5a920b"), "EMPNO" : 7566, "ENAME" : "JONES", "JOB" : "MANAGER", "MGR" : 7839, "HIREDATE" : "1981/4/2", "SAL" : 2 975, "COMM" : "", "DEPTNO" : 20 }

 { "_id" : ObjectId("54f3b29ccc2f0a163b5a920f"), "EMPNO" : 7788, "ENAME" : "SCOTT", "JOB" : "ANALYST", "MGR" : 7566, "HIREDATE" : "1982/12/9", "SAL" : 4000, "COMM" : "", "DEPTNO" : 20 }

{ "_id" : ObjectId("54f3b29ccc2f0a163b5a9212"), "EMPNO" : 7876, "ENAME" : "ADAMS", "JOB" : "CLERK", "MGR" : 7788, "HIREDATE" : "1983/1/12", "SAL" : 11 00, "COMM" : "", "DEPTNO" : 20 }

{ "_id" : ObjectId("54f3b29ccc2f0a163b5a9214"), "EMPNO" : 7902, "ENAME" : "FORD", "JOB" : "ANALYST", "MGR" : 7566, "HIREDATE" : "1981/12/33", "SAL" : 3000, "DEPTNO" : 20, "COMM" : [ "c", "e", "f", "g", "h", "i", "g", "c" ] }

当然你可以传入多个条件，类似于关系型数据库里的AND

我要查询部门是20，工作是ANALYST的员工信息：

> db.emp.find({"DEPTNO" : 20, "JOB" : "ANALYST"})

{ "_id" : ObjectId("54f3b29ccc2f0a163b5a920f"), "EMPNO" : 7788, "ENAME" : "SCOTT", "JOB" : "ANALYST", "MGR" : 7566, "HIREDATE" : "1982/12/9", "SAL" : 4000, "COMM" : "", "DEPTNO" : 20 }

 { "_id" : ObjectId("54f3b29ccc2f0a163b5a9214"), "EMPNO" : 7902, "ENAME" : "FORD", "JOB" : "ANALYST", "MGR" : 7566, "HIREDATE" : "1981/12/33", "SAL" : 3000, "DEPTNO" : 20, "COMM" : [ "c", "e", "f", "g", "h", "i", "g", "c" ] }

3.默认是返回集合的所有列，你可以通过传入一个JSON对象来定制要返回的列，

1表示显示

0表示不显示

例如我只想返回员工姓名和工资两列

注意_id作为伪列，除非明确指定不显示，否则一定会显示

> db.emp.find({"DEPTNO" : 20},{"ENAME" : 1, "SAL" : 1 })

 { "_id" : ObjectId("54f3b29ccc2f0a163b5a9208"), "ENAME" : "SMITH", "SAL" : 800 }

 { "_id" : ObjectId("54f3b29ccc2f0a163b5a920b"), "ENAME" : "JONES", "SAL" : 2975 }

 { "_id" : ObjectId("54f3b29ccc2f0a163b5a920f"), "ENAME" : "SCOTT", "SAL" : 4000 }

 { "_id" : ObjectId("54f3b29ccc2f0a163b5a9212"), "ENAME" : "ADAMS", "SAL" : 1100 }

 { "_id" : ObjectId("54f3b29ccc2f0a163b5a9214"), "ENAME" : "FORD", "SAL" : 3000 }

例如：

> db.emp.find({"DEPTNO" : 20},{"ENAME" : 1, "SAL" : 1, "_id" : 0 })

{ "ENAME" : "SMITH", "SAL" : 800 }

{ "ENAME" : "JONES", "SAL" : 2975 }

{ "ENAME" : "SCOTT", "SAL" : 4000 }

{ "ENAME" : "ADAMS", "SAL" : 1100 }

{ "ENAME" : "FORD", "SAL" : 3000 }

此时_id列不再显示

指定不需要返回的列：

> db.emp.find({"DEPTNO" : 20},{"ENAME" : 0, "SAL" : 0, "_id" : 0 })

{ "EMPNO" : 7369, "JOB" : "CLERK", "MGR" : 7902, "HIREDATE" : "1980/12/17", "COMM" : "", "DEPTNO" : 20 }

{ "EMPNO" : 7566, "JOB" : "MANAGER", "MGR" : 7839, "HIREDATE" : "1981/4/2", "COMM" : "", "DEPTNO" : 20 }

{ "EMPNO" : 7788, "JOB" : "ANALYST", "MGR" : 7566, "HIREDATE" : "1982/12/9", "COMM" : "", "DEPTNO" : 20 }

{ "EMPNO" : 7876, "JOB" : "CLERK", "MGR" : 7788, "HIREDATE" : "1983/1/12", "COMM" : "", "DEPTNO" : 20 }

{ "EMPNO" : 7902, "JOB" : "ANALYST", "MGR" : 7566, "HIREDATE" : "1981/12/33", "DEPTNO" : 20, "COMM" : [ "c", "e", "f", "g", "h", "i", "g", "c" ] }

如果条件既有要返回的列，又有要屏蔽的列呢？

> db.emp.find({"DEPTNO" : 20},{"ENAME" : 1, "SAL" : 0, "_id" : 0 }) error: { "$err" : "Can't canonicalize query: BadValue Projection cannot have a mix of inclusion and exclusion.", "code" : 17287 }

直接抛异常了，提示不能混合使用包含和排除

4.比较运算符

"$lt":小于

"$lte" :小于等于

"$gt":大于

"$gte":大于等于

“$ne”:不等于

例如我想查询工资大于4000的员工信息

> db.emp.find({"SAL" : {$gt : 4000}},{"ENAME" : 1, "SAL" : 1, "_id" : 0 })

 { "ENAME" : "KING", "SAL" : 5000 }

5.$in操作符

相当于关系型数据库里的in，例如我要查询员工编号是7902和7934员工的工资信息

> db.emp.find({"EMPNO":{$in:[7902,7934]}},{"_id":0,"EMPNO":1,"SAL":1})

{ "EMPNO" : 7934, "SAL" : 1300 }

{ "EMPNO" : 7902, "SAL" : 3000 }

$nin是$in的反操作，就是查询不在参数列表的信息，例如我要查询部门编号不是10，20的员工信息

> db.emp.find({"DEPTNO":{$nin:[10,20]}},{"_id":0})

 { "EMPNO" : 7499, "ENAME" : "ALLEN", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : "1981/2/20", "SAL" : 1600, "COMM" : 300, "DEPTNO" : 30 }

 { "EMPNO" : 7521, "ENAME" : "WARD", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : "1981/2/22", "SAL" : 1250, "COMM" : 500, "DEPTNO" : 30 }

{ "EMPNO" : 7654, "ENAME" : "MARTIN", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : "1981/9/28", "SAL" : 1250, "COMM" : 1400, "DEPTNO" : 30 }

{ "EMPNO" : 7698, "ENAME" : "BLAKE", "JOB" : "MANAGER", "MGR" : 7839, "HIREDATE" : "1981/5/1", "SAL" : 2850, "COMM" : "", "DEPTNO" : 30 }

{ "EMPNO" : 7844, "ENAME" : "TURNER", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : "1981/9/8", "SAL" : 1500, "COMM" : 0, "DEPTNO" : 30 }

{ "EMPNO" : 7900, "ENAME" : "JAMES", "JOB" : "CLERK", "MGR" : 7698, "HIREDATE" : "1981/12/3", "SAL" : 950, "COMM" : "", "DEPTNO" : 30 }

6.$or操作符

$or操作符可以连接多个查询条件，由一个为真则返回数据，它可以实现$in的功能

> db.emp.find({$or:[{"EMPNO" : 7902},{"EMPNO" : 7934}]},{"_id" : 0,"EMPNO" : 1,"SAL" : 1}) { "EMPNO" : 7934, "SAL" : 1300 } { "EMPNO" : 7902, "SAL" : 3000 }

当然$or操作可也可间接不同的键名，例如我要查询员工姓名是KING或者部门编号是20的员工信息：

> db.emp.find({$or : [{"ENAME" : "KING"}, {"DEPTNO" : 20}]}, {"_id" : 0, "ENAME" : 1, "DEPTNO" : 1})

{ "ENAME" : "SMITH", "DEPTNO" : 20 }

 { "ENAME" : "JONES", "DEPTNO" : 20 }

 { "ENAME" : "SCOTT", "DEPTNO" : 20 }

 { "ENAME" : "KING", "DEPTNO" : 10 }

 { "ENAME" : "ADAMS", "DEPTNO" : 20 }

 { "ENAME" : "FORD", "DEPTNO" : 20 }

7.$not操作符

$not操作符是可以在其他操作符之上，用来做取反，例如$mod是取模运算

先将员工编号除以3返回余数是1的员工信息：

> db.emp.find({"EMPNO" : {$mod : [3,1]}}, {"_id" :0, "EMPNO" : 1})

{ "EMPNO" : 7369 }

 { "EMPNO" : 7654 }

{ "EMPNO" : 7876 }

{ "EMPNO" : 7900 }

现在取反，返回余数不是1的员工信息：

> db.emp.find({"EMPNO" : {$not : {$mod : [3,1]}}}, {"_id" :0, "EMPNO" : 1})

{ "EMPNO" : 7499 }

{ "EMPNO" : 7521 }

{ "EMPNO" : 7566 }

{ "EMPNO" : 7698 }

{ "EMPNO" : 7782 }

{ "EMPNO" : 7788 }

{ "EMPNO" : 7839 }

{ "EMPNO" : 7844 }

{ "EMPNO" : 7934 }

{ "EMPNO" : 7902 }

8.$and

$and操作符和$or操作符类似，它连接的操作符都是真才返回数据

例如我要查询员工工资在800到2000的员工信息：

> db.emp.find({$and : [{"SAL" : {$gt : 800}} ,{"SAL" : {$lt : 2000 }}]}, {"_id" : 0, "EMPNO" : 1, "SAL" : 1})

{ "EMPNO" : 7499, "SAL" : 1600 }

 { "EMPNO" : 7521, "SAL" : 1250 }

 { "EMPNO" : 7654, "SAL" : 1250 }

 { "EMPNO" : 7844, "SAL" : 1500 }

 { "EMPNO" : 7876, "SAL" : 1100 }

 { "EMPNO" : 7900, "SAL" : 950 }

 { "EMPNO" : 7934, "SAL" : 1300 }

$and操作符也可以连接不同的键名，例如我要查询部门编号是10里的员工工资大于2000的员工信息：

> db.emp.find({$and : [{"SAL" : {$gt : 2000}} ,{"DEPTNO" : 10}]}, {"_id" : 0, "EMPNO" : 1, "SAL" : 1, "DEPTNO" : 1})

{ "EMPNO" : 7782, "SAL" : 2450, "DEPTNO" : 10 }

{ "EMPNO" : 7839, "SAL" : 5000, "DEPTNO" : 10 }