基本操作
命令行连接指令:
mongo --host localhost --port 27017 --username admin --password 123456 --authenticationDatabase dbTest
权限操作:
- 创建超级管理员:
use admin; db.createUser( { user:"root", pwd:"123456", roles:[{role:"root",db:"admin"}] } ); db.auth("root","123456");
- 创建普通用户:
use my-database; db.createUser( { user:"test", pwd:"123456", roles:[{role:"dbOwner",db:"my-database"}] } );
- 创建超级管理员:
数据库操作:
- 创建数据库/集合(表):
use dbTest db.createCollection("coTest")
- 删除数据库:
db.dropDatabase()
- 查看当前数据库:
db
- 查看所有数据库:
show dbs
- 创建数据库/集合(表):
集合(表)操作:
- 创建集合(表):
db.createCollection("coTest")
- 删除集合(表):
db.coTest.drop()
- 查看集合(表):
show collections
- 创建集合(表):
CRUD 操作:
- 新增:
db.coTest.insertOne({"name": "lee","age": 30}) db.coTest.insertMany([{"name": "lee","age": 30},{"name": "zhangsan","age": 18}])
- 删除:
db.coTest.deleteOne({"name": "lee"}) db.coTest.deleteMany({"name": "lee"})
- 更新:
db.coTest.updateOne({"name": "zhangsan"},{$set: {"name": "lee"}}) db.coTest.updateMany({"name": "zhangsan"},{$inc: {"age": 1}})
- 查询:
- 查询全部记录:
db.coTest.find()
- 美化输出:
db.coTest.find().pretty()
- 查询指定字段:
db.coTest.find({},{"name": 1,_id: 0})
- 条件查询:
db.coTest.find({ "name": "zhangsan", // name = "zhangsan" "createAt": { $gte: new Date("2023-08-24T05:30:00Z") // createAt >= 2023-08-24 05:30:00 }, $or: [ { "age": { $lt: 20 } }, // age < 20 { "name": { $regex: /^zhang/ } } // name LIKE "zhang%" ] })
- 根据字段的数据类型进行查询:
db.coTest.find({"age": {$type: "string"}}) // typeof(age) == "string"
- 分页查询:
db.coTest.find().limit(1).skip(1)
- 排序(1:升序,-1:降序):
db.coTest.find().sort({"age": 1})
- 统计条数:
db.coTest.find({age: {$gt: 30}}).count()
- 分组查询并统计条数:
db.coTest.aggregate([ { $group: { _id: "$name", name_count: { $sum: 1 } } } ]) // 相当于:SELECT name, COUNT(*) AS name_count FROM coTest GROUP BY name
- 聚合查询:
db.coTest.aggregate([ { $match: { "createAt": { $gte: new Date("2023-08-24T06:30:00Z") }, "name": "lee" } }, { $group: { "_id": "$name", "total": { $sum: 1 }, "age": { "$first": "$age" } } }, { $sort: { "age": - 1 } } ]) // 相当于 SELECT name AS _id,COUNT(1) AS total,MIN(age) AS age FROM `coTest` WHERE createAt >= new Date("2023-08-24T06:30:00Z") AND name = "lee" GROUP BY name ORDER BY age DESC
- 查询全部记录:
- 新增:
索引相关操作:
- 创建索引:
db.coTest.createIndex({"age": 1}, {background: true, unique: true})
- 查看索引:
db.coTest.getIndexes()
- 查看索引大小:
db.coTest.totalIndexSize()
- 删除索引:
- 删除所有索引:
db.coTest.dropIndexes()
- 删除指定索引:
db.coTest.dropIndex("索引名称")
- 删除所有索引:
- 创建索引:
退出 mongodb 命令行:
quit()
或exit
其它命令:
- 备份:
mongodump --host localhost --port 27017 --db dbTest --out ./
- 恢复:
mongorestore --host localhost --port 27017 --db dbTest1 --dir ./dbTest
- 监控:
mongostat
和mongotop
- 备份:
联表查询
创建测试数据:
# student - 学生表 db.student.insertMany([ {"_id": 1,"name": "小明"}, {"_id": 2,"name": "小红"}, {"_id": 3,"name": "小强"} ]) # course - 课程表 db.course.insertMany([ {"_id": 1, "name": "语文"}, {"_id": 2, "name": "数学"}, {"_id": 3, "name": "英语"} ]) # student_course - 学生-课程表 db.student_course.insertMany([ {"_id": 1, "student_id": 1, "course_id": 2}, {"_id": 2, "student_id": 1, "course_id": 3}, {"_id": 3, "student_id": 2, "course_id": 1}, {"_id": 4, "student_id": 2, "course_id": 3}, {"_id": 5, "student_id": 3, "course_id": 2}, {"_id": 6, "student_id": 3, "course_id": 3} ])
预期结果:
SELECT s.name AS student_name,c.name AS course_name FROM student_course AS sc LEFT JOIN student AS s ON sc.student_id = s._id LEFT JOIN course AS c ON sc.course_id = c._id WHERE student_name = "小明"
mongodb 联表查询命令:
db.student_course.aggregate([ { $lookup: { from: "student", localField: "student_id", foreignField: "_id", as: "student_info" } }, { $lookup: { from: "course", localField: "course_id", foreignField: "_id", as: "course_info" } }, { $unwind: { path: "$student_info", preserveNullAndEmptyArrays: true, } }, { $unwind: { path: "$course_info", preserveNullAndEmptyArrays: true, } }, { $project: { "_id": 0, "student_name": "$student_info.name", "course_name": "$course_info.name", } }, { $match: { "student_name": "小明" } } ])
复合索引
创建测试数据:
db.article.insertMany([ {"_id": 1,"title": "part one","content": "description one"}, {"_id": 2,"title": "part two","content": "description two"}, {"_id": 3,"title": "part three","content": "description three"}, ])
以
title
升序、content
升序的方式创建索引:db.article.createIndex({ title: 1, content: 1 })
根据
title
和content
字段查询,排序方式为title
升序、content
升序:db.article.find({ "title": "part two", "content": "description two" }).sort({"title": 1,"content": 1})
地理位置索引
2dsphere 索引(适用于现实场景):
- 创建测试数据:
db.shop.insertMany([ {"location": { "type": "Point", "coordinates": [10,10] }}, {"location": { "type": "Point", "coordinates": [11,11] }}, {"location": { "type": "Point", "coordinates": [12,12] }}, {"location": { "type": "Point", "coordinates": [13,13] }}, {"location": { "type": "Point", "coordinates": [14,14] }}, {"location": { "type": "Point", "coordinates": [15,15] }}, ])
- 创建索引:
db.shop.createIndex({"location": "2dsphere"})
- geoWithin: 查询在指定多边形范围内的店铺:
db.shop.find({"location": {"$geoWithin": {"$geometry": { "type": "Polygon", "coordinates":[[ [10,10], [12,10], [12,12], [10,12], [10,10], ]] }}}})
- near: 查询在指定圆范围内的店铺:
db.shop.find({"location": {"$near": {"$geometry": { "type": "Point", "coordinates": [11, 11] }, "$maxDistance": 200000 // 单位:米 }}})
- 创建测试数据:
2d 索引(适用于虚拟场景):
- 创建测试数据:
db.shop.insertMany([ {"location": [10, 10]}, {"location": [11, 11]}, {"location": [12, 12]}, {"location": [13, 13]}, {"location": [14, 14]}, {"location": [15, 15]}, ])
- 创建索引:
db.shop.createIndex({"location": "2d"})
- geoWithin: 查询在指定长方形范围内的店铺:
db.shop.find({"location": {"$geoWithin": {"$box": [[9, 9], [11, 11]]}}})
- geoWithin: 查询在指定圆范围内的店铺:
db.shop.find({"location": {"$geoWithin": {"$center": [[10, 10], 2]}}})
- geoWithin: 查询在指定多边形范围内的店铺:
db.shop.find({"location": {"$geoWithin": {"$polygon": [ [10,10], [12,10], [12,12], [10,12], [10,10], ]}}})
- near: 查询在指定圆范围内的店铺:
db.shop.find({"location": {"$near": [11, 11], "$maxDistance": 5}})
- 创建测试数据:
全文索引
创建测试数据:
db.article.insertMany([ {"_id": 1,"title": "part one","content": "description one"}, {"_id": 2,"title": "part two","content": "description two"}, {"_id": 3,"title": "part three","content": "description three"}, ])
创建索引:
db.article.createIndex({ title: "text", content: "text" })
查询匹配文档(行),并根据索引分数进行排序:
db.article.find({ $text: { $search: "part three" } },{ score: { $meta: "textScore" } }).sort( { score: { $meta: "textScore" } } )
TTL索引
说明:
mongodb 每隔60秒才会清除过期数据,所以过期数据会有所廷迟
创建方式:
- 在创建索引的时候设置过期时间:
- 创建测试数据:
db.log.insertMany([ {"_id": 1,"event": "error","create_at": new Date()}, {"_id": 2,"event": "warn","create_at": new Date()}, {"_id": 3,"event": "info","create_at": new Date()}, ])
- 创建索引,根据
create_at
字段进行判断,3秒后过期:db.log.createIndex( { "create_at": 1 }, { expireAfterSeconds: 3 } )
- 验证:
db.log.find()
- 创建测试数据:
- 根据指定字段设置过期时间(灵活度高):
- 创建测试数据:
db.log.insertMany([ {"_id": 1,"event": "error","expire_at": new Date(new Date().getTime() + 3000)}, // 3秒后过期 {"_id": 2,"event": "warn","expire_at": new Date(new Date().getTime() + 3000)}, {"_id": 3,"event": "info","expire_at": new Date(new Date().getTime() + 3000)}, ])
- 创建索引,根据
expire_at
字段确定过期时间:db.log.createIndex( { "expire_at": 1 }, { expireAfterSeconds: 0 } )
- 验证:
db.log.find()
- 创建测试数据:
- 在创建索引的时候设置过期时间:
map/reduce 用法
创建测试数据:
db.article.insertMany([ {"_id": 1,"author": "小明","likes": 15,"status": "active"}, {"_id": 2,"author": "小红","likes": 13,"status": "active"}, {"_id": 3,"author": "小张","likes": 18,"status": "active"}, {"_id": 4,"author": "小明","likes": 17,"status": "disabled"}, {"_id": 5,"author": "小红","likes": 16,"status": "active"}, ])
根据 map/reduce 函数生成集合(表):
db.article.mapReduce( function() { emit(this.author, this.likes); }, // map[this.author] = this.likes function(keys, values) {return Array.sum(values)}, // keys == this.author ; values = this.likes(数组类型) { query: {"status": "active"}, // 筛选条件 sort: {"likes": 1}, // 排序 limit: 3, out: "article_likes_number" // 构造出的 collection(表) } )
查询生成的集合(表)中的数据:
db.article_likes_number.find()
事务操作(需要在副本集环境中操作)
创建测试集合(表):
db.createCollection("trans")
创建 session(会话):
session = db.getMongo().startSession() trans = session.getDatabase("test").trans
回滚:
session.startTransaction() trans.insert({"a": 3}) trans.find({"a": 3}) session.abortTransaction() trans.find({"a": 3})
提交:
session.startTransaction() trans.insert({"a": 3}) db.trans.find({"a": 3}) session.commitTransaction() db.trans.find({"a": 3})