MongoDB数据库高级查询

Mongodb数据库安装和介绍： Windows安装Mongodb、Liunx安装mongodb2.6、MongoDB数据库集合、文档和对象   1. 数据准备 用户组字段： 组ID（id），名称（name），父组（pid），编码（code），级别（level），描述（desc） 用户组JSON数据：

{ "id":"01" , "name":"山西省" , "code":"01" , "level": 1 , desc:"顶级节点" }
{ "id":"0101" , "name":"太原市" , "pid":"01" , "code":"0101","level": 2 , desc : "" }
{ "id":"010101" , "name":"迎泽区" , "pid":"0101" , "code":"010101","level": 3 , desc : "" }
{ "id":"010102" , "name":"杏花岭区" , "pid":"0101" , "code":"010102","level": 3 , desc : "" }
{ "id":"010103" , "name":"尖草坪区" , "pid":"0101" , "code":"010103","level": 3 , desc : "" }
{ "id":"010104" , "name":"小店区" , "pid":"0101" , "code":"010104","level": 3 , desc : "" }
{ "id":"0102" , "name":"晋中市" , "pid":"01" , "code":"0102","level": 2 , desc : "" }
{ "id":"0103" , "name":"大同市" , "pid":"01" , "code":"0103","level": 2 , desc : "" }
{ "id":"0104" , "name":"吕梁市" , "pid":"01" , "code":"0104","level": 2 , desc : "" }
{ "id":"010401" , "name":"离市区" , "pid":"0104" , "code":"010401","level": 3 , desc : "" }
{ "id":"010402" , "name":"汾阳市" , "pid":"0104" , "code":"010402","level": 3 }
{ "id":"010403" , "name":"柳林县" , "pid":"0104" , "code":"010403","level": 3 }
{ "id":"0105" , "name":"临汾市" , "pid":"01" , "code":"0105","level": 2 , desc : "" }

用户字段： 用户ID（id），登录名（login），密码（passwd），名称（name），所属组（gid），年龄（age），性别（sex），邮箱（email），手机（mobile） 用户JSON数据：

{ "id":"01" , "login":"test01" , "passwd":"010001" , "name":"测试" ,
 "gid":"010101" , "age":24 , "sex":1 , "email":"01@what21.com" , 
"mobile":["13000001234","13000004321"] , 
address:{ "addr1":"XXXX101号", "addr2":"XX101号" } }
{ "id":"02" , "login":"test02" , "passwd":"010002" , "name":"测试" ,
 "gid":"010101" , "age":22 , "sex":2 , "email":"02@what21.com" , 
"mobile":["13100001234","13100004321"] , 
address:{ "addr1":"XXXX102号", "addr2":"XX102号" } }
{ "id":"03" , "login":"test03" , "passwd":"010003" , "name":"测试" ,
 "gid":"010102" , "age":23 , "sex":2 , "email":"03@what21.com" ,
 "mobile":["13200001234","13200004321"] , 
address:{ "addr1":"XXXX103号", "addr2":"XX103号" } }
{ "id":"04" , "login":"test04" , "passwd":"010004" , "name":"测试" ,
 "gid":"010102" , "age":21 , "sex":1 , "email":"04@what21.com" , 
"mobile":["13300001234","13300004321"] , 
address:{ "addr1":"XXXX104号", "addr2":"XX104号" } }
{ "id":"05" , "login":"test05" , "passwd":"010005" , "name":"测试" ,
 "gid":"010103" , "age":29 , "sex":1 , "email":"05@what21.com" , 
"mobile":["13400001234","13400004321"] , 
address:{ "addr1":"XXXX105号", "addr2":"XX105号" } }
{ "id":"06" , "login":"test06" , "passwd":"010006" , "name":"测试" ,
 "gid":"010103" , "age":28 , "sex":1 , "email":"06@what21.com" , 
"mobile":["13500001234","13500004321"] }
{ "id":"07" , "login":"test07" , "passwd":"010007" , "name":"测试" ,
 "gid":"010103" , "age":34 , "sex":2 , "email":"07@what21.com" ,
 "mobile":["13600001234","13600004321"] }
{ "id":"08" , "login":"test08" , "passwd":"010008" , "name":"测试" ,
 "gid":"010103" , "age":25 , "sex":2 , "email":"08@what21.com" , 
"mobile":["13700001234","13700004321"] }
{ "id":"09" , "login":"test09" , "passwd":"010009" , "name":"测试" ,
 "gid":"010104" , "age":32 , "sex":2 , "email":"09@what21.com" , 
"mobile":["13800001234","13800004321"] }
{ "id":"10" , "login":"test10" , "passwd":"010001" , "name":"测试" ,
 "gid":"010104" , "age":31 , "sex":1 , "email":"10@what21.com" , 
"mobile":["13900001234","13900004321"] }
{ "id":"11" , "login":"test11" , "passwd":"010002" , "name":"测试" ,
 "gid":"010104" , "age":24 , "sex":1 , "email":"11@what21.com" ,
 "mobile":["15100001234","15100004321"] }
{ "id":"12" , "login":"test12" , "passwd":"010003" , "name":"测试" ,
 "gid":"010104" , "age":34 , "sex":1 , "email":"12@what21.com" , 
"mobile":["15500001234","15500004321"] }
{ "id":"13" , "login":"test13" , "passwd":"010004" , "name":"测试" ,
 "gid":"010104" , "age":23 , "sex":2 , "email":"13@what21.com" , 
"mobile":["15600001234","15600004321"] }
{ "id":"14" , "login":"test14" , "passwd":"010005" , "name":"测试" ,
 "gid":"010104" , "age":22 , "sex":1 , "email":"14@what21.com" ,
 "mobile":"15800001234" }
{ "id":"15" , "login":"test15" , "passwd":"010006" , "name":"测试" ,
 "gid":"010104" , "age":21 , "sex":2 , "email":"15@what21.com" , 
"mobile":"15900001234" }

创建数据库： use what21;   插入组数据：

db.groups.insert({ "id":"01" , "name":"山西省" , "code":"01" , "level": 1 ,
 desc:"顶级节点" });
db.groups.insert({ "id":"0101" , "name":"太原市" , "pid":"01" , "code":"0101",
"level": 2 , desc : "" });
db.groups.insert({ "id":"010101" , "name":"迎泽区" , "pid":"0101" , "code":"010101",
"level": 3 , desc : "" });
db.groups.insert({ "id":"010102" , "name":"杏花岭区" , "pid":"0101" , "code":"010102",
"level": 3 , desc : "" });
db.groups.insert({ "id":"010103" , "name":"尖草坪区" , "pid":"0101" , "code":"010103",
"level": 3 , desc : "" });
db.groups.insert({ "id":"010104" , "name":"小店区" , "pid":"0101" , "code":"010104",
"level": 3 , desc : "" });
db.groups.insert({ "id":"0102" , "name":"晋中市" , "pid":"01" , "code":"0102",
"level": 2 , desc : "" });
db.groups.insert({ "id":"0103" , "name":"大同市" , "pid":"01" , "code":"0103",
"level": 2 , desc : "" });
db.groups.insert({ "id":"0104" , "name":"吕梁市" , "pid":"01" , "code":"0104",
"level": 2 , desc : "" });
db.groups.insert({ "id":"010401" , "name":"离市区" , "pid":"0104" , "code":"010401",
"level": 3 , desc : "" });
db.groups.insert({ "id":"010402" , "name":"汾阳市" , "pid":"0104" , "code":"010402",
"level": 3 });
db.groups.insert({ "id":"010403" , "name":"柳林县" , "pid":"0104" , "code":"010403",
"level": 3 });
db.groups.insert({ "id":"0105" , "name":"临汾市" , "pid":"01" , "code":"0105",
"level": 2 , desc : "" });

插入用户数据：

db.users.insert({ "id":"01" , "login":"test01" , "passwd":"010001" , "name":"测试" , 
"gid":"010101" , "age":24 , "sex":1 , "email":"01@what21.com" , 
"mobile":["13000001234","13000004321"] , address:{ "addr1":"XXXX101号", "addr2":"XX101号" } });
db.users.insert({ "id":"02" , "login":"test02" , "passwd":"010002" , "name":"测试" , 
"gid":"010101" , "age":22 , "sex":2 , "email":"02@what21.com" , 
"mobile":["13100001234","13100004321"] , address:{ "addr1":"XXXX102号", "addr2":"XX102号" } });
db.users.insert({ "id":"03" , "login":"test03" , "passwd":"010003" , "name":"测试" ,
 "gid":"010102" , "age":23 , "sex":2 , "email":"03@what21.com" , 
"mobile":["13200001234","13200004321"] , address:{ "addr1":"XXXX103号", "addr2":"XX103号" } });
db.users.insert({ "id":"04" , "login":"test04" , "passwd":"010004" , "name":"测试" ,
 "gid":"010102" , "age":21 , "sex":1 , "email":"04@what21.com" , 
"mobile":["13300001234","13300004321"] , address:{ "addr1":"XXXX104号", "addr2":"XX104号" } });
db.users.insert({ "id":"05" , "login":"test05" , "passwd":"010005" , "name":"测试" , 
"gid":"010103" , "age":29 , "sex":1 , "email":"05@what21.com" ,
 "mobile":["13400001234","13400004321"] , address:{ "addr1":"XXXX105号", "addr2":"XX105号" } });
db.users.insert({ "id":"06" , "login":"test06" , "passwd":"010006" , "name":"测试" ,
 "gid":"010103" , "age":28 , "sex":1 , "email":"06@what21.com" , 
"mobile":["13500001234","13500004321"] });
db.users.insert({ "id":"07" , "login":"test07" , "passwd":"010007" , "name":"测试" ,
 "gid":"010103" , "age":34 , "sex":2 , "email":"07@what21.com" , 
"mobile":["13600001234","13600004321"] });
db.users.insert({ "id":"08" , "login":"test08" , "passwd":"010008" , "name":"测试" ,
 "gid":"010103" , "age":25 , "sex":2 , "email":"08@what21.com" , 
"mobile":["13700001234","13700004321"] });
db.users.insert({ "id":"09" , "login":"test09" , "passwd":"010009" , "name":"测试" ,
 "gid":"010104" , "age":32 , "sex":2 , "email":"09@what21.com" , 
"mobile":["13800001234","13800004321"] });
db.users.insert({ "id":"10" , "login":"test10" , "passwd":"010001" , "name":"测试" ,
 "gid":"010104" , "age":31 , "sex":1 , "email":"10@what21.com" , 
"mobile":["13900001234","13900004321"] });
db.users.insert({ "id":"11" , "login":"test11" , "passwd":"010002" , "name":"测试" ,
 "gid":"010104" , "age":24 , "sex":1 , "email":"11@what21.com" , 
"mobile":["15100001234","15100004321"] });
db.users.insert({ "id":"12" , "login":"test12" , "passwd":"010003" , "name":"测试" , 
"gid":"010104" , "age":34 , "sex":1 , "email":"12@what21.com" , 
"mobile":["15500001234","15500004321"] });
db.users.insert({ "id":"13" , "login":"test13" , "passwd":"010004" , "name":"测试" , 
"gid":"010104" , "age":23 , "sex":2 , "email":"13@what21.com" ,
 "mobile":["15600001234","15600004321"] });
db.users.insert({ "id":"14" , "login":"test14" , "passwd":"010005" , "name":"测试" ,
 "gid":"010104" , "age":22 , "sex":1 , "email":"14@what21.com" , "mobile":"15800001234" });
db.users.insert({ "id":"15" , "login":"test15" , "passwd":"010006" , "name":"测试" , 
"gid":"010104" , "age":21 , "sex":2 , "email":"15@what21.com" , "mobile":"15900001234" });

删除数据： db.groups.remove({}); db.users.remove({});   查看数据： db.groups.find(); db.users.find();   2. 一般查询 查询返回所有用户组数据： db.groups.find(); 查询返回一条用户组数据： db.groups.findOne(); 查询登录为test01的用户： db.users.find( {"login":"test01"} );   3. 条件操作符查询（$gt、$lt、$gte、$lte） (1). > 大于 $gt 查询年龄大于28岁的用户： db.users.find( {"age":{ $gt: 28 }} ); (2). < 小于 $lt 查询年龄小于28岁的用户： db.users.find( {"age":{ $lt: 28 }} ); (3). >= 大于等于 $gte 查询年龄大于等于28岁的用户： db.users.find( {"age":{ $gte: 28 }} ); (4). <= 小于 $lte 查询年龄小于等于28岁的用户： db.users.find( {"age":{ $lte: 28 }} );   4. 字段类型查询（$type） $type操作符是基于BSON类型来检索集合中匹配的结果。 MongoDB数据类型 BSON数据类型对应表：

类型描述	类型值
Double	1
String	2
Object	3
Array	4
Binary data	5
Object id	7
Boolean	8
Date	9
Null	10
Regular expression	11
JavaScript code	13
Symbol	14
JavaScript code with scope	15
32-bit integer	16
Timestamp	17
64-bit integer	18
Min key	255
Max key	127

查询父组类型为String的组： db.groups.find( {"pid" : {$type : 2}} ); 查询地址类型为Object的用户： db.users.find( {"address" : {$type : 3}} );   5. 字段存在查询（$exists） $exists用来判断一个元素是否存在。 查询存在父组的用户组： db.groups.find( {"pid":{ "$exists":true }} ); 查询不存在父组的用户组： db.groups.find( {"pid":{ "$exists":false }} ); 查询存在地址的用户： db.users.find({ "address": {$exists: true} }); 查询不存在地址的用户： db.users.find({ "address": {$exists: false} });   6. 字段数量查询（$size） $size是用来匹配数组内的元素数量的。 查询有2个手机号的用户： db.users.find({ "mobile": {$size: 2 } });   7. 字段属于查询（$all） 查找字段的值为数组，并且包含所有给定的值的文档。 查询所属010104组的用户： db.users.find( {"gid" : { $all : ["010104"]}} ); 查询手机号码为15600001234和15600004321的用户： db.users.find( {"mobile" : { $all : ["15600001234","15600004321"]}} );   8. 字段属于和不属于查询（$in和$nin） $in查找字段的值为数组，并且包含一个或多个给定的值的文档。 查询所属用户为010104的用户： db.users.find( {"gid" : { $in : ["010104"]}} ); 查询所属用户为010101、010104的用户： db.users.find( {"gid" : { $in : ["010101","010104"]}} ); 查询所属用户不为010104的用户： db.users.find( {"gid" : { $nin : ["010104"]}} ); 查询所属用户不为010101、010104的用户： db.users.find( {"gid" : { $nin : ["010101","010104"]}} );   9. 取模运算查询（$mod） 取模运算，如：对2取模等于0，$mod : [2,0]。 查询年龄是偶数的用户： db.users.find( { "age": { $mod : [2,0]}} );   10. 嵌入文档查询 查询地址为XXXX101号的用户： db.users.find( { "address.addr1" : "XXXX101号" } );   11. 正则表达式查询（模糊查询） 查询登录名包含test的用户： db.users.find( {"login": /.*test.*/} ); 查询登录名不包含test0的用户： db.users.find( {"login": {$not: /.*test0.*/}} );   12. AND查询 查询登录名包含test且年龄是21岁的用户： db.users.find( {"login":/^test.*/ , "age":21} ); 查询登录名包含test，年龄是21岁且性别为男（1）的用户： db.users.find( {"login":/^test.*/ , "age":21 , "sex":1} ); 查询登录名包含test，名称包含测试，且年龄是21岁的用户： db.users.find( {"login":/^test.*/ , "name":/^测试.*/ ,"age":21} ); 查询登录名包含test，名称包含测试，年龄是21岁，且性别为男（1）的用户： db.users.find( {"login":/^test.*/ , "name":/^测试.*/ ,"age":21 , "sex":1} );   13. OR查询（$or） 查询ID为02或年龄是21岁的用户： db.users.find( {$or : [{"id":"02"} , {"age":21}]} ); 查询ID为02或年龄是21岁或性别是男（1）的用户： db.users.find( {$or : [{"id":"02"} , {"age":21} , {"sex":1}]} ); 查询登录名包含test或年龄是21岁的用户： db.users.find( {$or : [{"login":/^test.*/} , {"age":21}]} );   14. NOT查询（$not） 查询登录名不为test01的用户： db.users.find( {"login" : {$not : /^test0.*/ }} ); db.users.find( {"login" : {$not: /.*test0.*/ }} ); 查询年龄不大于28的用户： db.users.find( { "age" : { $not : { $gte: 28 }}} );   15. size()、count()、limit()和skip() 查询所有用户的个数： db.users.find().size(); db.users.find().count(); 查询用户的前5条用户数据： db.users.find().limit(5); 跳过前5条用户数据： db.users.find().skip(5).limit(5); db.users.find().skip(10).limit(5); db.users.find().skip(15).limit(5);   16. 查询返回指定列 第一个{}，第二个为条件： db.users.find({},{"login": /.*test.*/});