mongodb 增删改查及索引

一  创建测试数据

mongo --port 27021 

for (var i = 1; i <= 50; i++) db.student.save({"uid":i,"name":"sname"+i,"age":20,"address":"zhongsan park","classid":1,"dese":"person"});

for (var i = 1; i <= 20000; i++) db.student.save({
uid:i,
"name":"user"+i,
"age":Math.floor(Math.random()*120),
"classid":Math.round(Math.random()*10),
"address":"zhongsan park",
"createdate":new Date()
});

 

二 增删改查

1.插入新数据

db.student.insert({"name":"s01","age":23})

 

2.删除数据 remove

remove中不带参数将删除所有数据,很危险,在mongodb中是一个不可撤回的操作

testrs:PRIMARY> db.student.remove({"name":"sname50"})

WriteResult({ "nRemoved" : 1 })

remove不带条件全部删除,需要加上{}

testrs:PRIMARY> db.student.remove({})
WriteResult({ "nRemoved" : 50 })

 

3.修改数据 update

 update方法的第一个参数为“查找的条件”，第二个参数为“更新的值”.默认情况下，MongoDB只会更新一个文档。要更新多个文档，需要将参数’multi‘设置为true。

testrs:PRIMARY> db.student.find({"name":"sname50"}).pretty()
{
        "_id" : ObjectId("5cb825ba3ea09f93517e6937"),
        "uid" : 50,
        "name" : "sname50",
        "age" : 20,
        "address" : "zhongsan park",
        "classid" : 1,
        "dese" : "person"
}

3.1. 修改一列的值同时增加新列
等效的SQL where子句:
update student set age=30 where name='sname50' 同时增加新列"email" : "hang@163.com"

testrs:PRIMARY> db.student.update({"name":"sname50"},{"$set":{"age":30,"email":"hang@163.com"}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
testrs:PRIMARY> db.student.find({"name":"sname50"}).pretty()
{
        "_id" : ObjectId("5cb825ba3ea09f93517e6937"),
        "uid" : 50,
        "name" : "sname50",
        "age" : 30,
        "address" : "zhongsan park",
        "classid" : 1,
        "dese" : "person",
        "email" : "hang@163.com"
}

3.2.同时更新多个文档 需要使用multi:true

testrs:PRIMARY> db.student.update({"age":20},{"$set":{"address":"new 123"}},{multi:true})
WriteResult({ "nMatched" : 49, "nUpserted" : 0, "nModified" : 49 })

3.3 使列自增

等效的SQL where子句:
update student set age=age+1 where uname='user10'

db.student.update({"name":"sname50"},{"$inc":{"age":1}})

3.4.upsert操作
如果我没有查到，我就在数据库里面新增一条，其实这样也有好处，就是避免了我在数据库里面判断是update还是add操作，使用起来很简单将update的第三个参数设为true即可。
如下语句作用为:
判断jackson用户是否存在,存在就更新,不存在就插入新记录
如果去掉true,不存在就,什么也不做

testrs:PRIMARY> db.student.update({"name":"jackson"},{$inc:{"age":1}},true)
WriteResult({
        "nMatched" : 0,
        "nUpserted" : 1,
        "nModified" : 0,
        "_id" : ObjectId("5cb82b4ddb2a13e68e747928")
})
testrs:PRIMARY> db.student.find({'name':'jackson'})
{ "_id" : ObjectId("5cb82b4ddb2a13e68e747928"), "name" : "jackson", "age" : 1 }

 

4.查询

参考:http://www.cnblogs.com/libingql/archive/2011/06/09/2076440.html

数据量大时( db.student.find() ),设置每页显示数据的大小

testrs:PRIMARY> DBQuery.shellBatchSize = 10
10

4.1 查询一条记录

同 SELECT TOP 1 * FROM student

db.student.findOne()

4.2 distinct 用法

SELECT DISTINCT("age")  FROM student

testrs:PRIMARY> db.student.distinct('age')
[ 20, 31, 1 ]

4.3 查询聚集中UserName包含“keyword”关键字的记录   /相当于%

同 SELECT * FROM Account WHERE UserName LIKE '%keyword%'

testrs:PRIMARY> db.student.find({"name":/jeson/}).pretty()
{
        "_id" : ObjectId("5cb825ba3ea09f93517e6937"),
        "uid" : 50,
        "name" : "jeson.hang",
        "age" : 31,
        "address" : "zhongsan park",
        "classid" : 1,
        "dese" : "person",
        "email" : "hang@163.com",
        "sname" : "jeson.hang"
}

查询聚集中UserName以"jeson" 开头的记录

同 SELECT * FROM Account WHERE UserName LIKE 'keyword%'

testrs:PRIMARY> db.student.find({"name":/^jeson/}).pretty()
{
        "_id" : ObjectId("5cb825ba3ea09f93517e6937"),
        "uid" : 50,
        "name" : "jeson.hang",
        "age" : 31,
        "address" : "zhongsan park",
        "classid" : 1,
        "dese" : "person",
        "email" : "hang@163.com",
        "sname" : "jeson.hang"
}

 查询聚集中UserName以“jeson”结尾的记录

同 SELECT * FROM Account WHERE UserName LIKE '%keyword' 

testrs:PRIMARY> db.student.find({"name":/jeson$/}).pretty()

查询聚集中指定列

同 SELECT UserName,Email FROM Account

testrs:PRIMARY> db.student.find({},{"name":1,"age":1})  1表示true
{ "_id" : ObjectId("5cb825ba3ea09f93517e6906"), "name" : "sname1", "age" : 20 }
{ "_id" : ObjectId("5cb825ba3ea09f93517e6907"), "name" : "sname2", "age" : 20 }
{ "_id" : ObjectId("5cb825ba3ea09f93517e6908"), "name" : "sname3", "age" : 20 }
{ "_id" : ObjectId("5cb825ba3ea09f93517e6909"), "name" : "sname4", "age" : 20 }

 查询聚集中排除指定列

testrs:PRIMARY> db.student.find({},{"name":0}) 0表示false
{ "_id" : ObjectId("5cb825ba3ea09f93517e6906"), "uid" : 1, "age" : 20, "address" : "new 123", "classid" : 1, "dese" : "person" }
{ "_id" : ObjectId("5cb825ba3ea09f93517e6907"), "uid" : 2, "age" : 20, "address" : "new 123", "classid" : 1, "dese" : "person" }
{ "_id" : ObjectId("5cb825ba3ea09f93517e6908"), "uid" : 3, "age" : 20, "address" : "new 123", "classid" : 1, "dese" : "person" }
{ "_id" : ObjectId("5cb825ba3ea09f93517e6909"), "uid" : 4, "age" : 20, "address" : "new 123", "classid" : 1, "dese" : "person" }

查询聚集中指定列，且Age > 20

同 SELECT UserName,Email FROM Account WHERE Age > 20 

db.student.find({"Age":{"$gt":20}},{"name":1,"Email":1})

 

聚集中字段排序 

--SELECT * FROM Account ORDER BY UserName ASC

--SELECT * FROM Account ORDER BY UserName DESC 

> db.Account.find().sort({"UserName":1}) -- 升序
> db.Account.find().sort({"UserName":-1}) --降序

 

统计聚集中记录条数

--SELECT COUNT(*) FROM Account 

> db.Account.find().count()

 

统计聚集中符合条件的记录条数

-- SELECT COUNT(*) FROM Account WHERE Age > 20

> db.Account.find({"Age":{"$gt":20}}).count()

 

统计聚集中字段符合条件的记录条数

--SELECT COUNT(UserName) FROM Account 

> db.Account.find({"UserName":{"$exists":true}}).count()

 

查询聚集中前5条记录 

--SELECT TOP 5 * FROM Account 

> db.Account.find().limit(5)

 

查询聚集中第10条以后的记录

--SELECT * FROM Account WHERE AccountID NOT IN (SELECT TOP 10 AccountID FROM Account) 

> db.Account.find().skip(10)

 

or查询

--SELECT UserName,Email FROM Account WHERE UserName LIKE '%keyword%' OR Email LIKE '%keyword%'

> db.Account.find({"$or":[{"UserName":/keyword/},{"Email":/keyword/}]},{"UserName":true,"Email":true})

 三 添加索引

1.添加索引,默认索引名 (注意添加索引,要使用background方式,否则会锁表)

db.student.ensureIndex({"name":1},{background:true})
db.student.ensureIndex({"age":1},{background:true})

2.添加索引,指定索引名

testrs:PRIMARY>  db.student.ensureIndex({"name":1,"age":1},{"name":"idx_name_age"} ,{background:true})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 3,
        "numIndexesAfter" : 4,
        "ok" : 1
}

3.添加唯一性索引

添加唯一性索引
db.student.ensureIndex({"name":1},{"unique":true})
创建唯一索引，并消除重复数据
db.student.ensureIndex({"uid":1},{"unique":true,"dropDups":true})    
查询结果确认，重复的键确实在创建索引时已经被删除。
db.student.find()
{ "_id" : ObjectId("4fe823c180144abd15acd52e"), "userid" : 5 }    
注意:在不存在的列上建索引也会成功,不会报错

4.查看有哪些索引

testrs:PRIMARY> db.student.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "school.student"
        },
        {
                "v" : 1,
                "key" : {
                        "name" : 1
                },
                "name" : "name_1",
                "ns" : "school.student",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "age" : 1
                },
                "name" : "age_1",
                "ns" : "school.student",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "name" : 1,
                        "age" : 1
                },
                "name" : "idx_name_age",
                "ns" : "school.student"
        }
]

5.删除索引(注意: name 是列名,代表的是删除这个列上所有的索引)

testrs:PRIMARY> db.student.dropIndex({"name":1})
{ "nIndexesWas" : 4, "ok" : 1 }

6.查看执行计划

testrs:PRIMARY> db.student.find({"name":"user101"}).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "school.student",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "name" : {
                                "$eq" : "user101"
                        }
                },
                "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "name" : 1,
                                        "age" : 1
                                },
                                "indexName" : "idx_name_age",
                                "isMultiKey" : false,
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 1,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "name" : [
                                                "[\"user101\", \"user101\"]"
                                        ],
                                        "age" : [
                                                "[MinKey, MaxKey]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "izuf60bp6kd88idp0no1urz",
                "port" : 27091,
                "version" : "3.2.20-16-g4c2d1a88c1",
                "gitVersion" : "4c2d1a88c19c316c7cfc895a6e0f7d1a81e85bd7"
        },
        "ok" : 1
}