1. MongoDB的数据操作
1.1 修改数据
命令格式:db.集合名.update({query}, {update}, {multi: boolean})
参数query:查询条件
参数update:更新操作符
参数multi:可选,默认是false,表示只更新找到的第⼀条数据,值为true表示把满足条件的数据全部更新。
> db.createCollection('test') # 创建一个空的集合
{ "ok" : 1 }
> db.test.find()
> db.test.insert({x:100,y:50,z:150}) # 插入一条数据
WriteResult({ "nInserted" : 1 })
> db.test.find()
{ "_id" : ObjectId("6052ac66b4d165f89a64a798"), "x" : 100, "y" : 50, "z" : 150 }
> db.test.update({x:100},{y:99}) # 查找x=100的数据,并将其更改
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.test.find()
{ "_id" : ObjectId("6052ac66b4d165f89a64a798"), "y" : 99 } # 修改后只有y=99,注意与save命令的区分
如果使用update命令时查找的数据不存在,就插入更新的数据,但第三个参数必须为true
> db.test.update({y:100},{y:101},true)
WriteResult({
"nMatched" : 0,
"nUpserted" : 1,
"nModified" : 0,
"_id" : ObjectId("6052ad85df8ccc450c88bd19")
})
> db.test.find()
{ "_id" : ObjectId("6052ac66b4d165f89a64a798"), "y" : 99 }
{ "_id" : ObjectId("6052ad85df8ccc450c88bd19"), "y" : 101 }
如果只想部分更新数据:db.集合名.update({query}, {$set:{update}}, {multi: boolean})
> db.test.insert([{x:100,y:99,z:150},{x:80,y:99},{y:99,z:70}]) # 插入三条新数据
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 3,
"nUpserted" : 0,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [ ]
})
> db.test.update({x:80},{$set:{z:100}}) # 查找x=80的第一条数据,修改z=100,没有就添加该数据,不影响其它数据
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.test.find()
{ "_id" : ObjectId("6052ac66b4d165f89a64a798"), "y" : 99 }
{ "_id" : ObjectId("6052ad85df8ccc450c88bd19"), "y" : 101 }
{ "_id" : ObjectId("6052af46b4d165f89a64a799"), "x" : 100, "y" : 99, "z" : 150 }
{ "_id" : ObjectId("6052af46b4d165f89a64a79a"), "x" : 80, "y" : 99, "z" : 100 }
{ "_id" : ObjectId("6052af46b4d165f89a64a79b"), "y" : 99, "z" : 70 }
更新多条数据,把第三项参数设为true:
> db.test.update({y:99},{$set:{y:100,z:60}},{multi:true})
WriteResult({ "nMatched" : 4, "nUpserted" : 0, "nModified" : 4 })
> db.test.find()
{ "_id" : ObjectId("6052ac66b4d165f89a64a798"), "y" : 100, "z" : 60 }
{ "_id" : ObjectId("6052ad85df8ccc450c88bd19"), "y" : 101 }
{ "_id" : ObjectId("6052af46b4d165f89a64a799"), "x" : 100, "y" : 100, "z" : 60 }
{ "_id" : ObjectId("6052af46b4d165f89a64a79a"), "x" : 80, "y" : 100, "z" : 60 }
{ "_id" : ObjectId("6052af46b4d165f89a64a79b"), "y" : 100, "z" : 60 }
1.2 删除数据
命令格式:db.集合名.remove({条件},{justOne:true})
,MongoDB为了防⽌误删除,条件必须写。
> db.test.remove({x:80})
WriteResult({ "nRemoved" : 1 })
> db.test.find()
{ "_id" : ObjectId("6052ac66b4d165f89a64a798"), "y" : 100, "z" : 60 }
{ "_id" : ObjectId("6052ad85df8ccc450c88bd19"), "y" : 101 }
{ "_id" : ObjectId("6052af46b4d165f89a64a799"), "x" : 100, "y" : 100, "z" : 60 }
{ "_id" : ObjectId("6052af46b4d165f89a64a79b"), "y" : 100, "z" : 60 }
> db.test.remove({y:100},{justOne:false}) # 把y=100的数据全部删除
WriteResult({ "nRemoved" : 3 })
> db.test.find()
{ "_id" : ObjectId("6052ad85df8ccc450c88bd19"), "y" : 101 }
1.3 小练习
测试数据:
> db.persons.insert([
{name:"jim",age:25,email:"75431457@qq.com",c:89,m:96,e:87,country:"USA",books:["JS","C++","EXTJS","MONGODB"]},
{name:"tom",age:25,email:"214557457@qq.com",c:75,m:66,e:97,country:"USA",books:["PHP","JAVA","EXTJS","C++"]},
{name:"lili",age:26,email:"344521457@qq.com",c:75,m:63,e:97,country:"USA",books:["JS","JAVA","C#","MONGODB"]},
{name:"zhangsan",age:27,email:"2145567457@qq.com",c:89,m:86,e:67,country:"China",books:["JS","JAVA","EXTJS","MONGODB"]},
{name:"lisi",age:26,email:"274521457@qq.com",c:53,m:96,e:83,country:"China",books:["JS","C#","PHP","MONGODB"]},
{name:"wangwu",age:27,email:"65621457@qq.com",c:45,m:65,e:99,country:"China",books:["JS","JAVA","C++","MONGODB"]},
{name:"zhaoliu",age:27,email:"214521457@qq.com",c:99,m:96,e:97,country:"China",books:["JS","JAVA","EXTJS","PHP"]},
{name:"piaoyingjun",age:26,email:"piaoyingjun@uspcat.com",c:39,m:54,e:53,country:"Korea",books:["JS","C#","EXTJS","MONGODB"]},
{name:"lizhenxian",age:27,email:"lizhenxian@uspcat.com",c:35,m:56,e:47,country:"Korea",books:["JS","JAVA","EXTJS","MONGODB"]},
{name:"lixiaoli",age:21,email:"lixiaoli@uspcat.com",c:36,m:86,e:32,country:"Korea",books:["JS","JAVA","PHP","MONGODB"]},
{name:"zhangsuying",age:22,email:"zhangsuying@uspcat.com",c:45,m:63,e:77,country:"Korea",books:["JS","JAVA","C#","MONGODB"]}])
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 11,
"nUpserted" : 0,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [ ]
})
题目:
1.查询年龄大于25小于27的name,age
> db.persons.find({age:{$gt:25,$lt:27}},{name:1,age:1})
{ "_id" : ObjectId("6052b5fbb4d165f89a64a79e"), "name" : "lili", "age" : 26 }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a0"), "name" : "lisi", "age" : 26 }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a3"), "name" : "piaoyingjun", "age" : 26 }
2.查询出不是美国的name,country
> db.persons.find({country:{$ne:'USA'}},{name:1})
3.查询国籍是中国或者美国的学生信息
> db.persons.find({country:{$ne:'USA'}},{name:1,country:1})
{ "_id" : ObjectId("6052b5fbb4d165f89a64a79f"), "name" : "zhangsan", "country" : "China" }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a0"), "name" : "lisi", "country" : "China" }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a1"), "name" : "wangwu", "country" : "China" }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a2"), "name" : "zhaoliu", "country" : "China" }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a3"), "name" : "piaoyingjun", "country" : "Korea" }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a4"), "name" : "lizhenxian", "country" : "Korea" }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a5"), "name" : "lixiaoli", "country" : "Korea" }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a6"), "name" : "zhangsuying", "country" : "Korea" }
4.查询语文成绩大于85或者英语成绩大于90的学生信息
> db.persons.find({$or:[{c:{$gt:85}},{e:{$gt:90}}]},{name:1,c:1,e:1})
{ "_id" : ObjectId("6052b5fbb4d165f89a64a79c"), "name" : "jim", "c" : 89, "e" : 87 }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a79d"), "name" : "tom", "c" : 75, "e" : 97 }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a79e"), "name" : "lili", "c" : 75, "e" : 97 }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a79f"), "name" : "zhangsan", "c" : 89, "e" : 67 }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a1"), "name" : "wangwu", "c" : 45, "e" : 99 }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a2"), "name" : "zhaoliu", "c" : 99, "e" : 97 }
5.查询出名字中存在"li"的学生信息(模糊查询)
> db.persons.find({name:/li/},{name:1})
{ "_id" : ObjectId("6052b5fbb4d165f89a64a79e"), "name" : "lili" }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a0"), "name" : "lisi" }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a2"), "name" : "zhaoliu" }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a4"), "name" : "lizhenxian" }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a5"), "name" : "lixiaoli" }
6.查询喜欢看MONGODB和PHP的学生(与条件all)
> db.persons.find({books:{$all:['MONGODB','PHP']}},{name:1,books:1})
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a0"), "name" : "lisi", "books" : [ "JS", "C#", "PHP", "MONGODB" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a5"), "name" : "lixiaoli", "books" : [ "JS", "JAVA", "PHP", "MONGODB" ] }
7.查询第二本书是JAVA的学生信息
> db.persons.find({'books.1':'JAVA'},{name:1,books:1})
{ "_id" : ObjectId("6052b5fbb4d165f89a64a79d"), "name" : "tom", "books" : [ "PHP", "JAVA", "EXTJS", "C++" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a79e"), "name" : "lili", "books" : [ "JS", "JAVA", "C#", "MONGODB" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a79f"), "name" : "zhangsan", "books" : [ "JS", "JAVA", "EXTJS", "MONGODB" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a1"), "name" : "wangwu", "books" : [ "JS", "JAVA", "C++", "MONGODB" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a2"), "name" : "zhaoliu", "books" : [ "JS", "JAVA", "EXTJS", "PHP" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a4"), "name" : "lizhenxian", "books" : [ "JS", "JAVA", "EXTJS", "MONGODB" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a5"), "name" : "lixiaoli", "books" : [ "JS", "JAVA", "PHP", "MONGODB" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a6"), "name" : "zhangsuying", "books" : [ "JS", "JAVA", "C#", "MONGODB" ] }
8.查询喜欢的书数量是4本的学生
> db.persons.find({books:{$size:4}},{name:1,books:1})
{ "_id" : ObjectId("6052b5fbb4d165f89a64a79c"), "name" : "jim", "books" : [ "JS", "C++", "EXTJS", "MONGODB" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a79d"), "name" : "tom", "books" : [ "PHP", "JAVA", "EXTJS", "C++" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a79e"), "name" : "lili", "books" : [ "JS", "JAVA", "C#", "MONGODB" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a79f"), "name" : "zhangsan", "books" : [ "JS", "JAVA", "EXTJS", "MONGODB" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a0"), "name" : "lisi", "books" : [ "JS", "C#", "PHP", "MONGODB" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a1"), "name" : "wangwu", "books" : [ "JS", "JAVA", "C++", "MONGODB" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a2"), "name" : "zhaoliu", "books" : [ "JS", "JAVA", "EXTJS", "PHP" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a3"), "name" : "piaoyingjun", "books" : [ "JS", "C#", "EXTJS", "MONGODB" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a4"), "name" : "lizhenxian", "books" : [ "JS", "JAVA", "EXTJS", "MONGODB" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a5"), "name" : "lixiaoli", "books" : [ "JS", "JAVA", "PHP", "MONGODB" ] }
{ "_id" : ObjectId("6052b5fbb4d165f89a64a7a6"), "name" : "zhangsuying", "books" : [ "JS", "JAVA", "C#", "MONGODB" ] }
9.查询出persons中的国家分别是什么
> db.persons.distinct('country')
[ "China", "Korea", "USA" ]
2. MongoDB的聚合命令
2.1 基本概念
聚合是基于数据处理的聚合管道,每个文档通过⼀个由多个阶段组成的管道,可以对每个阶段的管道进行分组、过滤等功能,然后经过⼀系列的处理,输出相应的结果。
说明:首先,匹配status='A’的数据,然后根据cust_id来进行分组,并将各自分组内的amount进行求和,结果返回给total。
常用管道:
$group:将集合中的文档分组,可用于统计结果;
$match:过滤数据,只输出符合条件的文档;
$sort:将输入文档排序后输出;
$limit:限制聚合管道返回的文档数;
$skip:跳过指定数量的文档,并返回余下的文档。
表达式:处理输入文档并输出。语法:’$列名’。
常用表达式:
$sum:计算总和,$sum:1,1表示以一倍计数;
$avg:计算平均值;
$min:获取最小值;
$max:获取最⼤值;
$push:在结果文档中插入值到一个数组中;
$first:根据资源文档的排序获取第一个文档数据;
$last:根据资源文档的排序获取最后一个文档数据。
2.2 案例演示
测试数据:
> db.stu.find()
{ "_id" : ObjectId("6051cd147de174714a04c4b1"), "name" : "张三", "hometown" : "⻓沙", "age" : 20, "gender" : true }
{ "_id" : ObjectId("6051cd147de174714a04c4b2"), "name" : "⽼李", "hometown" : "⼴州", "age" : 18, "gender" : false }
{ "_id" : ObjectId("6051cd147de174714a04c4b3"), "name" : "王麻⼦", "hometown" : "北京", "age" : 18, "gender" : false }
{ "_id" : ObjectId("6051cd147de174714a04c4b4"), "name" : "刘六", "hometown" : "深圳", "age" : 40, "gender" : true }
{ "_id" : ObjectId("6051cd147de174714a04c4b5"), "name" : "jerry", "hometown" : "⻓沙", "age" : 16, "gender" : true }
{ "_id" : ObjectId("6051cd147de174714a04c4b6"), "name" : "小永", "hometown" : "⼴州", "age" : 45, "gender" : true }
{ "_id" : ObjectId("6051cd147de174714a04c4b7"), "name" : "⽼amy", "hometown" : "衡阳", "age" : 18, "gender" : true }
$group:将集合中的文档分组,可用于统计结果。
_id表示分组的依据,使用某个字段的格式为’$字段’。
# 按照gender分组,统计各个分组的数量,返回给count
> db.stu.aggregate({$group:{_id:'$gender',count:{$sum:1}}})
{ "_id" : true, "count" : 5 }
{ "_id" : false, "count" : 2 }
# 按照gender分组,分组后统计结果乘以2,计算每个分组的平均年龄
> db.stu.aggregate({$group:{_id:'$gender',count:{$sum:2},avg_age:{$avg:'$age'}}})
{ "_id" : true, "count" : 10, "avg_age" : 27.8 }
{ "_id" : false, "count" : 4, "avg_age" : 18 }
# 按照性别分组后,分别统计各自分组的姓名
> db.stu.aggregate({$group:{_id:'$gender',count:{$sum:1},name:{$push:'$name'}}})
{ "_id" : true, "count" : 5, "name" : [ "张三", "刘六", "jerry", "小永", "⽼amy" ] }
{ "_id" : false, "count" : 2, "name" : [ "⽼李", "王麻⼦" ] }
$match:match是管道命令,能将结果交给后⼀个管道。
# 查询年龄大于20的学生
> db.stu.aggregate({$match:{age:{$gt:20}}})
{ "_id" : ObjectId("6051cd147de174714a04c4b4"), "name" : "刘六", "hometown" : "深圳", "age" : 40, "gender" : true }
{ "_id" : ObjectId("6051cd147de174714a04c4b6"), "name" : "小永", "hometown" : "⼴州", "age" : 45, "gender" : true }
# 查询年龄小于20的男生,女生人数
> db.stu.aggregate({$match:{age:{$lt:20}}},{$group:{_id:'$gender',count:{$sum:1}}})
{ "_id" : false, "count" : 2 }
{ "_id" : true, "count" : 2 }
3. MongoDB的索引操作
为什么操作索引:
加快查询速度、进行数据去重。
MongoDB创建简单的索引方法
语法:db.集合名.ensureIndex({属性:1})
,1表示升序, -1表示降序。
测试:插入10万条数据到数据库中。
插⼊数据:
> for(i=0;i<100000;i++){db.test.insert({name:'test'+i,age:i})}
> db.test.find()
{ "_id" : ObjectId("6052c809b4d165f89a64a7a7"), "name" : "test0", "age" : 0 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7a8"), "name" : "test1", "age" : 1 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7a9"), "name" : "test2", "age" : 2 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7aa"), "name" : "test3", "age" : 3 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7ab"), "name" : "test4", "age" : 4 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7ac"), "name" : "test5", "age" : 5 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7ad"), "name" : "test6", "age" : 6 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7ae"), "name" : "test7", "age" : 7 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7af"), "name" : "test8", "age" : 8 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7b0"), "name" : "test9", "age" : 9 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7b1"), "name" : "test10", "age" : 10 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7b2"), "name" : "test11", "age" : 11 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7b3"), "name" : "test12", "age" : 12 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7b4"), "name" : "test13", "age" : 13 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7b5"), "name" : "test14", "age" : 14 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7b6"), "name" : "test15", "age" : 15 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7b7"), "name" : "test16", "age" : 16 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7b8"), "name" : "test17", "age" : 17 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7b9"), "name" : "test18", "age" : 18 }
{ "_id" : ObjectId("6052c809b4d165f89a64a7ba"), "name" : "test19", "age" : 19 }
Type "it" for more
创建索引前:
> db.test.find({name:'test9999'})
{ "_id" : ObjectId("6052c80db4d165f89a64ceb6"), "name" : "test9999", "age" : 9999 }
# 显示查询操作的详细信息
> db.test.find({name:'test9999'}).explain('executionStats')
{
...
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 54,
"totalKeysExamined" : 0,
"totalDocsExamined" : 100000,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"name" : {
"$eq" : "test9999"
}
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 100002,
"advanced" : 1,
"needTime" : 100000,
"needYield" : 0,
"saveState" : 100,
"restoreState" : 100,
"isEOF" : 1,
"direction" : "forward",
"docsExamined" : 100000
}
},
"serverInfo" : {
"host" : "DESKTOP-F9A7J71",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
创建索引
> db.test.ensureIndex({name:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
创建索引后
> db.test.find({name:'test9999'}).explain('executionStats')
{
...
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 10,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"docsExamined" : 1,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"keyPattern" : {
"name" : 1
},
...
}
}
},
"serverInfo" : {
"host" : "DESKTOP-F9A7J71",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
索引的查看
默认情况下_id是集合的索引。查看方式:db.集合名.getIndexes()
> db.test.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_"
},
{
"v" : 2,
"key" : {
"name" : 1
},
"name" : "name_1"
}
]
如何删除索引
语法:db.集合名.dropIndex({'索引名称':1})
> db.test.dropIndex({name:1})
{ "nIndexesWas" : 2, "ok" : 1 }
> db.test.getIndexes()
[ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_" } ]