MongoDB数据库高级查询

MongoDB基础 04/23 阅读 8610 views次 人气 784
摘要:

MongoDB数据库的各中查询详细解说。

Mongodb数据库安装和介绍: Windows安装MongodbLiunx安装mongodb2.6MongoDB数据库集合、文档和对象   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.*/});

评论

该文章不支持评论!

分享到: