1.安装MongoDB
https://www.mongodb.com/try/download/community
https://fastdl.mongodb.org/osx/mongodb-macos-x86_64-4.4.1.tgz
本例Macos4.4.1版，较于旧版本似乎有api删减。
下载解压。在目录内新建db、log文件夹。
在bin目录新建1个sh文件。
1.1.配置
1）mgdb_start.sh文件
此文件用于启动mongodb数据库服务。
|
1 2 3 4 5 6 7 8 |
mongod --dbpath=/Users/moonmen/appspace/mongodb-macos-x86_64-4.4.1/db --logpath=/Users/moonmen/appspace/mongodb-macos-x86_64-4.4.1/log/mongodb.log --port=27017 --logappend=true --fork |
参数说明，按需配置：
|
1 2 3 4 5 6 7 8 |
--dbpath 数据存放路径 --logpath 日志文件路径 --logappend 日志采用追加输出方式 --port 启用端口号 --fork 在后台运行 --auth 是否需要验证权限登录(用户名和密码) --bind_ip 限制访问的ip |
2）系统环境变量~/.bash_profile配置
|
1 2 3 |
export MONGODB_HOME=/Users/moonmen/appspace/mongodb-macos-x86_64-4.4.1 export PATH=$MONGODB_HOME/bin:$PATH:. |
刷新配置：
|
1 2 |
$ source ~/.bash_profile |
1.2.启动
1）启动服务：
|
1 2 3 4 5 6 |
$ mgdb_start.sh about to fork child process, waiting until server is ready for connections. forked process: 20259 child process started successfully, parent exiting $ |
2）进入mongodb的shell：
|
1 2 3 4 5 6 7 8 9 |
$ mongo MongoDB shell version v4.4.1 connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb Implicit session: session { "id" : UUID("f0eca395-6962-4a1a-b874-65b5168ddea4") } MongoDB server version: 4.4.1 Welcome to the MongoDB shell. --- > |
此时通过浏览器查看localhost:27017如下：
3）从shell直接退出并停止服务
|
1 2 3 4 5 6 7 8 9 10 |
> db.shutdownServer() shutdown command only works with the admin database; try 'use admin' > use admin # 首先进入admin库 switched to db admin > db.shutdownServer() # 然后终止数据库服务 server should be down... > exit bye $ |
如果不停止服务，也可以光退出shell环境：
如果服务还没有停止，再次启动服务会提示“ server is ready for connections”，使用“exit”指令光退出shell，在使用“top”指令可以查看还在运行的mongodb服务：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
$ mgdb_start.sh about to fork child process, waiting until server is ready for connections. forked process: 21476 ERROR: child process failed, exited with 48 To see additional information in this output, start without the "--fork" option. $ mongo MongoDB shell version v4.4.1 connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb Implicit session: session { "id" : UUID("f0fe024e-af54-4aa0-b784-4273de92eb27") } MongoDB server version: 4.4.1 --- > exit bye $ top Processes: 341 total, 2 running, 339 sleeping, 1476 threads 23:41:59 Load Avg: 1.57, 1.44, 1.36 CPU usage: 3.59% user, 3.25% sys, 93.15% idle SharedLibs: 160M resident, 46M data, 19M linkedit. MemRegions: 66818 total, 2012M resident, 83M private, 1036M shared. PhysMem: 7911M used (2233M wired), 279M unused. VM: 1677G vsize, 1297M framework vsize, 1035121(0) swapins, 1290555(0) swapouts. Networks: packets: 1134917/840M in, 1495034/187M out. Disks: 1896315/27G read, 1663140/25G written. PID COMMAND %CPU TIME #TH #WQ #PORTS MEM 21490 top 9.5 00:00.77 1/1 0 25 4200K+ 21465 CoreServices 0.0 00:00.09 4 2 165 4456K 21345 Google Chrom 0.0 00:00.09 14 1 103 14M 21476 mongod 0.4 00:02.47 32 0 42 20M 21177 QuickLookSat 0.0 00:02.15 4 1 79 16M 21175 quicklookd 0.0 00:00.49 5 2 88 4020K 20981 bash 0.0 00:00.05 1 0 21 980K …… $ ps aux | grep mongod moonmen 21476 0.1 0.3 5536224 27684 ?? S 10:18下午 0:03.88 mongod # 最后的0:03.88是存活时长 moonmen 23423 0.0 0.0 4268036 808 s000 S+ 10:18下午 0:00.00 grep mongo $ ps -ef |grep mongod 501 21476 1 0 10:18下午 ?? 0:03.94 mongod # 最后的0:03.94是存活时长 501 23428 21811 0 10:18下午 ttys000 0:00.00 grep mongo $ |
2.数据库常用操作
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
$ mongo MongoDB shell version v4.4.1 connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb Implicit session: session { "id" : UUID("4dcd05c2-8431-41a6-afb7-ffb403a129af") } MongoDB server version: 4.4.1 --- > show dbs # 查看全部的数据库。此时test是空白的，默认不显示 admin 0.000GB config 0.000GB local 0.000GB > db # db指令查看当前使用的数据库。 test # 进入shell，默认使用一个空的test数据库 > show databases # 查看全部的数据库。同show dbs admin 0.000GB config 0.000GB local 0.000GB # 虽然test为当前默认数据库，但其中并没有数据，所以默认不显示 > > db.getCollectionNames() # 查看数据库中存在的数据集-集合-表。同 show tables [ ] # 默认使用test库是空的，没有表 > db.test.insert({tip:'test是collection的名字'}) # 向当前数据库的“test表”插入一条记录。如果这个“表”不存在 则直接新建。 WriteResult({ "nInserted" : 1 }) > db.getCollectionNames() [ "test" ] # 直接创建了表 > show dbs admin 0.000GB config 0.000GB local 0.000GB test 0.000GB # test已经有了表，有了数据，此时显示 > db test > db.dropDatabase() { "dropped" : "test", "ok" : 1 } > show dbs admin 0.000GB config 0.000GB local 0.000GB > db test # 默认使用一个空白的‘test’数据库，所以删除了有数据的test，这里还是test > |
3.集合(表)常用操作
3.1.表的属性
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
> db test > db.test.stats() # 查看集合状态 { "ns" : "test.tb", "size" : 0, "count" : 0, # 0-无记录 "storageSize" : 12288, "freeStorageSize" : 4096, "capped" : false, "wiredTiger" : { 。。。。。。 }, "nindexes" : 1, "indexBuilds" : [ ], "totalIndexSize" : 12288, "totalSize" : 24576, "indexSizes" : { "_id_" : 12288 }, "scaleFactor" : 1, "ok" : 1 } > db.test.dataSize() # 集合中数据的原始大小 0 > db.test.totalIndexSize() # 集合中索引数据的原始大小 12288 > db.test.totalSize() # 集合中索引+数据压缩存储之后的大小 24576 > db.test.storageSize() # 集合中数据压缩存储的大小 12288 > db.test.insert({"tip":"重复key的记录999"}) WriteResult({ "nInserted" : 1 }) > db.test.dataSize() 53 > db.test.totalIndexSize() 12288 > db.test.totalSize() 24576 > db.test.storageSize() 12288 |
3.2.记录查询
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
> for(i=0;i<10;i++){ db.test.insert({"tip":"重复key的记录"+i}); } # 使用语句批量插入数据 WriteResult({ "nInserted" : 1 }) > db.getCollectionNames() [ "test" ] # “test”表，这个表没有的话就直接新建 > db.test.find() { "_id" : ObjectId("5f832d17c75d21c83c933ae1"), "tip" : "重复key的记录0" } { "_id" : ObjectId("5f832d17c75d21c83c933ae2"), "tip" : "重复key的记录1" } { "_id" : ObjectId("5f832d17c75d21c83c933ae3"), "tip" : "重复key的记录2" } { "_id" : ObjectId("5f832d17c75d21c83c933ae4"), "tip" : "重复key的记录3" } { "_id" : ObjectId("5f832d17c75d21c83c933ae5"), "tip" : "重复key的记录4" } { "_id" : ObjectId("5f832d17c75d21c83c933ae6"), "tip" : "重复key的记录5" } { "_id" : ObjectId("5f832d17c75d21c83c933ae7"), "tip" : "重复key的记录6" } { "_id" : ObjectId("5f832d17c75d21c83c933ae8"), "tip" : "重复key的记录7" } { "_id" : ObjectId("5f832d17c75d21c83c933ae9"), "tip" : "重复key的记录8" } { "_id" : ObjectId("5f832d17c75d21c83c933aea"), "tip" : "重复key的记录9" } > db.test.findOne() # 查询表的第一条记录 { "_id" : ObjectId("5f832d17c75d21c83c933ae1"), "tip" : "重复key的记录0" } > db.test.count() # 查询表的记录数量 10 > |
3.2.1.条件查询
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
> for(i=0;i<10;i++){ db.tb_a.insert({"name":"python"+1,"pswd":"pswd"+i}); } WriteResult({ "nInserted" : 1 }) > db.getCollection('tb_a').find({}) > db.tb_a.find() { "_id" : ObjectId("46696df41f85"), "name" : "python1", "pswd" : "pswd0" } { "_id" : ObjectId("46696df41f86"), "name" : "python1", "pswd" : "pswd1" } { "_id" : ObjectId("46696df41f87"), "name" : "python1", "pswd" : "pswd2" } { "_id" : ObjectId("46696df41f88"), "name" : "python1", "pswd" : "pswd3" } { "_id" : ObjectId("46696df41f89"), "name" : "python1", "pswd" : "pswd4" } { "_id" : ObjectId("46696df41f8a"), "name" : "python1", "pswd" : "pswd5" } { "_id" : ObjectId("46696df41f8b"), "name" : "python1", "pswd" : "pswd6" } { "_id" : ObjectId("46696df41f8c"), "name" : "python1", "pswd" : "pswd7" } { "_id" : ObjectId("46696df41f8d"), "name" : "python1", "pswd" : "pswd8" } { "_id" : ObjectId("46696df41f8e"), "name" : "python1", "pswd" : "pswd9" } > db.tb_a.find({pswd:"pswd8"}) # 有条件查询，select * from tb_a where 'pswd=pswd8'; { "_id" : ObjectId("46696df41f8d"), "name" : "python1", "pswd" : "pswd8" } > db.tb_a.find({pswd:"pswd8"}, {name:1})# 有条件查询指定列。也叫“投影”。select name from tb_a where pswd=pswd8; { "_id" : ObjectId("46696df41f8d"), "name" : "python1" } > db.tb_a.find({}, {_id:1, name:1}) # 查询指定列 select (_id, name) from tb_a; { "_id" : ObjectId("46696df41f85"), "name" : "python1" } { "_id" : ObjectId("46696df41f86"), "name" : "python1" } { "_id" : ObjectId("46696df41f87"), "name" : "python1" } { "_id" : ObjectId("46696df41f88"), "name" : "python1" } { "_id" : ObjectId("46696df41f89"), "name" : "python1" } { "_id" : ObjectId("46696df41f8a"), "name" : "python1" } { "_id" : ObjectId("46696df41f8b"), "name" : "python1" } { "_id" : ObjectId("46696df41f8c"), "name" : "python1" } { "_id" : ObjectId("46696df41f8d"), "name" : "python1" } { "_id" : ObjectId("46696df41f8e"), "name" : "python1" } > > db.tb_a.find({"name":{"$exists":true}}) # 只查询有‘name’列的条目。$exists见下文 > db.tb_a.find({"name":{"$exists":false}}) # 排除有'name'列的条目 > > var row="user" # 定义一个变量，作为查询条件的某个参数 > db.tb_a.find({[row]:{"$exists":false}}) # 传入参数 |
3.2.2.运算符条件查询
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
> for(i=0;i<10;i++){ db.numbers.insert({"number":Math.floor(Math.random()*10)+i}); # 使用Math随机数 } WriteResult({ "nInserted" : 1 }) > db.numbers.find() { "_id" : ObjectId("5f85e02aa707486f4b8042ac"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042ad"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042ae"), "number" : 3 } { "_id" : ObjectId("5f85e02aa707486f4b8042af"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042b0"), "number" : 10 } { "_id" : ObjectId("5f85e02aa707486f4b8042b1"), "number" : 5 } { "_id" : ObjectId("5f85e02aa707486f4b8042b2"), "number" : 12 } { "_id" : ObjectId("5f85e02aa707486f4b8042b3"), "number" : 16 } { "_id" : ObjectId("5f85e02aa707486f4b8042b4"), "number" : 13 } { "_id" : ObjectId("5f85e02aa707486f4b8042b5"), "number" : 11 } > db.numbers.find({number:4}) # 查询number的值等于4的记录 { "_id" : ObjectId("5f85e02aa707486f4b8042ac"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042ad"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042af"), "number" : 4 } > db.numbers.find({number:{$lt:10}}) # $lt，查询number的值小于10的记录 { "_id" : ObjectId("5f85e02aa707486f4b8042ac"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042ad"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042ae"), "number" : 3 } { "_id" : ObjectId("5f85e02aa707486f4b8042af"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042b1"), "number" : 5 } > db.numbers.find({number:{$lte:10}}) # $lte，查询number的值小于等于10的记录 { "_id" : ObjectId("5f85e02aa707486f4b8042ac"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042ad"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042ae"), "number" : 3 } { "_id" : ObjectId("5f85e02aa707486f4b8042af"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042b0"), "number" : 10 } { "_id" : ObjectId("5f85e02aa707486f4b8042b1"), "number" : 5 } > db.numbers.find({number:{$gt:10}}) # $gt，查询number的值大于10的记录 { "_id" : ObjectId("5f85e02aa707486f4b8042b2"), "number" : 12 } { "_id" : ObjectId("5f85e02aa707486f4b8042b3"), "number" : 16 } { "_id" : ObjectId("5f85e02aa707486f4b8042b4"), "number" : 13 } { "_id" : ObjectId("5f85e02aa707486f4b8042b5"), "number" : 11 } > db.numbers.find({number:{$gte:10}}) # $gte，查询number的值大于等于10的记录 { "_id" : ObjectId("5f85e02aa707486f4b8042b0"), "number" : 10 } { "_id" : ObjectId("5f85e02aa707486f4b8042b2"), "number" : 12 } { "_id" : ObjectId("5f85e02aa707486f4b8042b3"), "number" : 16 } { "_id" : ObjectId("5f85e02aa707486f4b8042b4"), "number" : 13 } { "_id" : ObjectId("5f85e02aa707486f4b8042b5"), "number" : 11 } > db.numbers.find({number:{$ne:10}}) # $ne，查询number的值不等于10的记录 { "_id" : ObjectId("5f85e02aa707486f4b8042ac"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042ad"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042ae"), "number" : 3 } { "_id" : ObjectId("5f85e02aa707486f4b8042af"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042b1"), "number" : 5 } { "_id" : ObjectId("5f85e02aa707486f4b8042b2"), "number" : 12 } { "_id" : ObjectId("5f85e02aa707486f4b8042b3"), "number" : 16 } { "_id" : ObjectId("5f85e02aa707486f4b8042b4"), "number" : 13 } { "_id" : ObjectId("5f85e02aa707486f4b8042b5"), "number" : 11 } > |
3.2.3.范围条件查询
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
> db.numbers.find({number:{$in:[10, 11, 12]}}) # 等于某些值的记录 { "_id" : ObjectId("5f85e02aa707486f4b8042b0"), "number" : 10 } { "_id" : ObjectId("5f85e02aa707486f4b8042b2"), "number" : 12 } { "_id" : ObjectId("5f85e02aa707486f4b8042b5"), "number" : 11 } > db.numbers.find({number:{$nin:[10, 11, 12]}}) # 不等于某些值的记录 { "_id" : ObjectId("5f85e02aa707486f4b8042ac"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042ad"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042ae"), "number" : 3 } { "_id" : ObjectId("5f85e02aa707486f4b8042af"), "number" : 4 } { "_id" : ObjectId("5f85e02aa707486f4b8042b1"), "number" : 5 } { "_id" : ObjectId("5f85e02aa707486f4b8042b3"), "number" : 16 } { "_id" : ObjectId("5f85e02aa707486f4b8042b4"), "number" : 13 } > |
3.2.4.逻辑条件查询
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
> db.numbers.find() { "_id" : ObjectId("5f85e448a707486f4b8042b6"), "number" : 5, "code" : 4 } { "_id" : ObjectId("5f85e448a707486f4b8042b8"), "number" : 6, "code" : 3 } { "_id" : ObjectId("5f85e448a707486f4b8042b9"), "number" : 12, "code" : 3 } { "_id" : ObjectId("5f85e448a707486f4b8042bd"), "number" : 16, "code" : 16 } { "_id" : ObjectId("5f85e448a707486f4b8042be"), "number" : 10, "code" : 8 } > db.numbers.find({code:3}) # code等于3的 { "_id" : ObjectId("5f85e448a707486f4b8042b8"), "number" : 6, "code" : 3 } { "_id" : ObjectId("5f85e448a707486f4b8042b9"), "number" : 12, "code" : 3 } > db.numbers.find({code:3, number:6}) # code等于3 并且(and) number等于6 { "_id" : ObjectId("5f85e448a707486f4b8042b8"), "number" : 6, "code" : 3 } > db.numbers.find({$or:[{number:6}, {number:16}]}) # number等于6 或者number等于16 { "_id" : ObjectId("5f85e448a707486f4b8042b8"), "number" : 6, "code" : 3 } { "_id" : ObjectId("5f85e448a707486f4b8042bd"), "number" : 16, "code" : 16 } > |
3.2.5.正则查询
|
1 2 3 4 |
> db.tb.find({"tip":/.*9/}) # 模糊查询：tip列带数字‘9’的。条件是正则 { "_id" : ObjectId("5f832d17c75d21c83c933aea"), "tip" : "重复key的记录9" } > |
3.2.6.分页查询
|
1 2 3 4 5 |
> db.tb.find().limit(3).pretty() # 返回前3条记录，并且更加美观的显示 > db.tb.find().skip(3) # 忽略前3条记录 > db.tb.find().skip(3).limit(2) # 忽略前3条记录后，返回剩余记录的前2条 > |
3.2.7.自定义条件查询
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
> db.numbers.find() { "_id" : ObjectId("5f85e448a707486f4b8042b6"), "number" : 5, "code" : 4 } { "_id" : ObjectId("5f85e448a707486f4b8042b7"), "number" : 10, "code" : 8 } { "_id" : ObjectId("5f85e448a707486f4b8042b8"), "number" : 6, "code" : 3 } { "_id" : ObjectId("5f85e448a707486f4b8042b9"), "number" : 12, "code" : 3 } { "_id" : ObjectId("5f85e448a707486f4b8042ba"), "number" : 7, "code" : 10 } { "_id" : ObjectId("5f85e448a707486f4b8042bb"), "number" : 13, "code" : 10 } { "_id" : ObjectId("5f85e448a707486f4b8042bc"), "number" : 11, "code" : 15 } { "_id" : ObjectId("5f85e448a707486f4b8042bd"), "number" : 16, "code" : 16 } { "_id" : ObjectId("5f85e448a707486f4b8042be"), "number" : 10, "code" : 8 } { "_id" : ObjectId("5f85e448a707486f4b8042bf"), "number" : 15, "code" : 17 } > db.numbers.find({ $where:function(){ # where关键字定义一个函数 return this.code%2 == 0; # 能被2整除的 } }) { "_id" : ObjectId("5f85e448a707486f4b8042b6"), "number" : 5, "code" : 4 } { "_id" : ObjectId("5f85e448a707486f4b8042b7"), "number" : 10, "code" : 8 } { "_id" : ObjectId("5f85e448a707486f4b8042ba"), "number" : 7, "code" : 10 } { "_id" : ObjectId("5f85e448a707486f4b8042bb"), "number" : 13, "code" : 10 } { "_id" : ObjectId("5f85e448a707486f4b8042bd"), "number" : 16, "code" : 16 } { "_id" : ObjectId("5f85e448a707486f4b8042be"), "number" : 10, "code" : 8 } > |
3.2.8.查询排序
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
> db.numbers.find() { "_id" : ObjectId("5f85e9e3a707486f4b8042c0"), "number" : 6, "code" : 8 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c1"), "number" : 1, "code" : 9 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c2"), "number" : 2, "code" : 5 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c3"), "number" : 6, "code" : 10 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c4"), "number" : 10, "code" : 6 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c5"), "number" : 10, "code" : 10 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c6"), "number" : 14, "code" : 11 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c7"), "number" : 8, "code" : 13 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c8"), "number" : 15, "code" : 9 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c9"), "number" : 15, "code" : 17 } > db.numbers.find().sort({number:1,code:-1}) # 1升序;-1降序。按number升序code降序 { "_id" : ObjectId("5f85e9e3a707486f4b8042c1"), "number" : 1, "code" : 9 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c2"), "number" : 2, "code" : 5 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c3"), "number" : 6, "code" : 10 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c0"), "number" : 6, "code" : 8 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c7"), "number" : 8, "code" : 13 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c5"), "number" : 10, "code" : 10 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c4"), "number" : 10, "code" : 6 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c6"), "number" : 14, "code" : 11 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c9"), "number" : 15, "code" : 17 } { "_id" : ObjectId("5f85e9e3a707486f4b8042c8"), "number" : 15, "code" : 9 } > |
3.2.9.查询统计
|
1 2 3 4 5 6 |
> db.numbers.find({number:10}).count() # 查询结果统计 2 > db.numbers.count({number:10}) # 有条件的统计 2 > |
3.2.10.查询去重
|
1 2 3 4 5 6 7 8 9 10 |
> db.numbers.distinct("number") # number列出现过的值-至少出现1次 [ 1, 2, 6, 8, 10, 14, 15 ] > db.numbers.distinct("code") # code列出现过的值 [ 5, 6, 8, 9, 10, 11, 13, 17 ] > db.numbers.distinct("number", {code:8}) # code值为8的全部记录中number出现过的值 [ 6 ] > db.numbers.distinct("number", {code:{$gt:8}}) # code值大于8的全部记录中number出现过的值 [ 1, 6, 8, 10, 14, 15 ] > |
3.3.记录删除
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
> db.tb.remove({tip:"重复key的记录0"}, {justOne:true}) # 仅删除第一条记录 > db.tb.remove({tip:"重复key的记录0"}) # 删除记录。delete from tb where tip='...0'; WriteResult({ "nRemoved" : 11 }) > db.tb.remove({}) # 清空表，表还存在 WriteResult({ "nRemoved" : 11 }) > db.tb.count() 0 > db.tb.renameCollection('col1') # 将tb表重命名为“col1” { "ok" : 1 } > db.getCollectionNames() [ "col1" ] > db.col1.drop() # 删除指定表，记录清空、表删除 true > db.getCollectionNames() [ ] > |
3.4.创建表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
> db test > db.getCollectionNames() [ ] > db.createCollection("tb_a") # 创建表 { "ok" : 1 } > db.createCollection("tb_b") { "ok" : 1 } > db.getCollectionNames() [ "tb_a", "tb_b" ] > show tables tb_a tb_b > |
有条件的创建表：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
$ mongo -u abc -p abc --authenticationDatabase 'abc' > db.createCollection("ghi", {capped:true, size:2, max:2}) # 限制表数据最大2M，记录最多2条 // capped:是否启用集合限制，默认为不启用。如果true，则须同时设置size // size:限制集合使用空间的大小，默认为没有限制。如果开启，则须同时capped:true // max:集合中最大条数限制，默认为没有限制。如果开启，则须同时capped:true // autoIndexId:是否使用_id作为索引，默认为true // size的优先级比max要高 { "ok" : 1 } > db.ghi.insert({'city':'北京','code':10010}) WriteResult({ "nInserted" : 1 }) > db.ghi.insert({'city':'上海','code':10012}) WriteResult({ "nInserted" : 1 }) > db.ghi.find() { "_id" : ObjectId("5f85ce78a707486f4b80426c"), "city" : "北京", "code" : 10010 } { "_id" : ObjectId("5f85ce82a707486f4b80426d"), "city" : "上海", "code" : 10012 } > db.ghi.insert({'city':'广州','code':22100}) WriteResult({ "nInserted" : 1 }) > db.ghi.find() { "_id" : ObjectId("5f85ce82a707486f4b80426d"), "city" : "上海", "code" : 10012 } { "_id" : ObjectId("5f85ce90a707486f4b80426e"), "city" : "广州", "code" : 22100 } > db.ghi.insert({'city':'深圳','code':12200}) WriteResult({ "nInserted" : 1 }) > db.ghi.find() { "_id" : ObjectId("5f85ce90a707486f4b80426e"), "city" : "广州", "code" : 22100 } { "_id" : ObjectId("5f85cea4a707486f4b80426f"), "city" : "深圳", "code" : 12200 } > |
3.5.记录更新
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
> db.tb_a.update({pswd:'pswd5'}, {pswd:'pswd5', name:'PythonOne'}) # 更新数据。update tb_a set name='PythonOne' where pswd='pswd5'。注意pswd:'pswd5'是否保留 WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 }) > db.tb_a.find() { "_id" : ObjectId("46696df41f85"), "name" : "python1", "pswd" : "pswd0" } { "_id" : ObjectId("46696df41f86"), "name" : "python1", "pswd" : "pswd1" } { "_id" : ObjectId("46696df41f87"), "name" : "python1", "pswd" : "pswd2" } { "_id" : ObjectId("46696df41f88"), "name" : "python1", "pswd" : "pswd3" } { "_id" : ObjectId("46696df41f89"), "name" : "python1", "pswd" : "pswd4" } { "_id" : ObjectId("46696df41f8a"), "name" : "PythonOne", "pswd" : "pswd5" } { "_id" : ObjectId("46696df41f8b"), "name" : "python1", "pswd" : "pswd6" } { "_id" : ObjectId("46696df41f8c"), "name" : "python1", "pswd" : "pswd7" } { "_id" : ObjectId("46696df41f8d"), "name" : "python1", "pswd" : "pswd8" } { "_id" : ObjectId("46696df41f8e"), "name" : "python1", "pswd" : "pswd9" } > |
有条件的更新：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
> db.tb_a.find() { "_id" : ObjectId("5f85d04270"), "name" : "python1", "pswd" : "pswd0" } { "_id" : ObjectId("5f85d04271"), "name" : "python1", "pswd" : "pswd1" } { "_id" : ObjectId("5f85d04272"), "name" : "python1", "pswd" : "pswd2" } { "_id" : ObjectId("5f85d04273"), "name" : "python1", "pswd" : "pswd3" } { "_id" : ObjectId("5f85d04274"), "name" : "python1", "pswd" : "pswd4" } { "_id" : ObjectId("5f85d04275"), "name" : "python1", "pswd" : "pswd5" } { "_id" : ObjectId("5f85d04276"), "name" : "python1", "pswd" : "pswd6" } { "_id" : ObjectId("5f85d04277"), "name" : "python1", "pswd" : "pswd7" } { "_id" : ObjectId("5f85d04278"), "name" : "python1", "pswd" : "pswd8" } { "_id" : ObjectId("5f85d04279"), "name" : "python1", "pswd" : "pswd9" } > db.tb_a.update({name:'python1'}, {name:'PythonOne'}) # 更新第1条name为‘python1’的记录 WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 }) > db.tb_a.find() { "_id" : ObjectId("5f85d04270"), "name" : "PythonOne" } { "_id" : ObjectId("5f85d04271"), "name" : "python1", "pswd" : "pswd1" } { "_id" : ObjectId("5f85d04272"), "name" : "python1", "pswd" : "pswd2" } { "_id" : ObjectId("5f85d04273"), "name" : "python1", "pswd" : "pswd3" } { "_id" : ObjectId("5f85d04274"), "name" : "python1", "pswd" : "pswd4" } { "_id" : ObjectId("5f85d04275"), "name" : "python1", "pswd" : "pswd5" } { "_id" : ObjectId("5f85d04276"), "name" : "python1", "pswd" : "pswd6" } { "_id" : ObjectId("5f85d04277"), "name" : "python1", "pswd" : "pswd7" } { "_id" : ObjectId("5f85d04278"), "name" : "python1", "pswd" : "pswd8" } { "_id" : ObjectId("5f85d04279"), "name" : "python1", "pswd" : "pswd9" } > db.tb_a.update({name:'python1'}, {$set:{name:'PythonOne'}}) # 更新第一条name为‘python1’的记录的name列。如果列是以前没有的则插入新的列 WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 }) > db.tb_a.find() { "_id" : ObjectId("5f85d04270"), "name" : "PythonOne" } { "_id" : ObjectId("5f85d04271"), "name" : "PythonOne", "pswd" : "pswd1" } { "_id" : ObjectId("5f85d04272"), "name" : "python1", "pswd" : "pswd2" } { "_id" : ObjectId("5f85d04273"), "name" : "python1", "pswd" : "pswd3" } { "_id" : ObjectId("5f85d04274"), "name" : "python1", "pswd" : "pswd4" } { "_id" : ObjectId("5f85d04275"), "name" : "python1", "pswd" : "pswd5" } { "_id" : ObjectId("5f85d04276"), "name" : "python1", "pswd" : "pswd6" } { "_id" : ObjectId("5f85d04277"), "name" : "python1", "pswd" : "pswd7" } { "_id" : ObjectId("5f85d04278"), "name" : "python1", "pswd" : "pswd8" } { "_id" : ObjectId("5f85d04279"), "name" : "python1", "pswd" : "pswd9" } > db.tb_a.update({name:'python1'}, {$set:{name:'PythonMulit'}}, {multi:true}) # 更新name为‘’的全部记录，只更新记录的name列 WriteResult({ "nMatched" : 8, "nUpserted" : 0, "nModified" : 8 }) > db.tb_a.find() { "_id" : ObjectId("5f85d04270"), "name" : "PythonOne" } { "_id" : ObjectId("5f85d04271"), "name" : "PythonOne", "pswd" : "pswd1" } { "_id" : ObjectId("5f85d04272"), "name" : "PythonMulit", "pswd" : "pswd2" } { "_id" : ObjectId("5f85d04273"), "name" : "PythonMulit", "pswd" : "pswd3" } { "_id" : ObjectId("5f85d04274"), "name" : "PythonMulit", "pswd" : "pswd4" } { "_id" : ObjectId("5f85d04275"), "name" : "PythonMulit", "pswd" : "pswd5" } { "_id" : ObjectId("5f85d04276"), "name" : "PythonMulit", "pswd" : "pswd6" } { "_id" : ObjectId("5f85d04277"), "name" : "PythonMulit", "pswd" : "pswd7" } { "_id" : ObjectId("5f85d04278"), "name" : "PythonMulit", "pswd" : "pswd8" } { "_id" : ObjectId("5f85d04279"), "name" : "PythonMulit", "pswd" : "pswd9" } > |
3.6.覆盖插入
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
> db.tb_a.findOne() { "_id" : ObjectId("56f4b804270"), "name" : "PythonOne" } > db.tb_a.insert({'_id':ObjectId('56f4b804270'), 'name':'PythonSave'}) # _id重复，insert插入失败 WriteResult({ "nInserted" : 0, "writeError" : { "code" : 11000, "errmsg" : "E11000 duplicate key error collection: abc.tb_a index: _id_ dup key: { _id: ObjectId('56f4b804270') }" } }) > db.tb_a.save({'_id':ObjectId('56f4b804270'), 'name':'PythonSave'}) # 无此_id则insert；有此_id(重复)则覆盖数据update WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 }) > db.tb_a.findOne() { "_id" : ObjectId("56f4b804270"), "name" : "PythonSave" } > > # 以下使用save模拟update > one = db.tb_a.findOne() # 首先，确定目标记录 { "_id" : ObjectId("56f4b804270"), "name" : "PythonSave" } > one.name='PythonUpdate' # 然后，更新数据 PythonUpdate > db.tb_a.save(one) # 最后，执行覆盖插入 WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 }) > db.tb_a.find() { "_id" : ObjectId("56f4b804270"), "name" : "PythonUpdate" } { "_id" : ObjectId("56f4b804271"), "name" : "PythonOne", "pswd" : "pswd1" } { "_id" : ObjectId("56f4b804272"), "name" : "PythonMulit", "pswd" : "pswd2" } { "_id" : ObjectId("56f4b804273"), "name" : "PythonMulit", "pswd" : "pswd3" } { "_id" : ObjectId("56f4b804274"), "name" : "PythonMulit", "pswd" : "pswd4" } { "_id" : ObjectId("56f4b804275"), "name" : "PythonMulit", "pswd" : "pswd5" } { "_id" : ObjectId("56f4b804276"), "name" : "PythonMulit", "pswd" : "pswd6" } { "_id" : ObjectId("56f4b804277"), "name" : "PythonMulit", "pswd" : "pswd7" } { "_id" : ObjectId("56f4b804278"), "name" : "PythonMulit", "pswd" : "pswd8" } { "_id" : ObjectId("56f4b804279"), "name" : "PythonMulit", "pswd" : "pswd9" } > |
4.用户管理
4.1.创建用户
创建语句格式：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
db.createUser({ user: "adm", # 用户名 pwd: "adm", # 密码 roles: [ { role: "root", # 角色权限： # --数据库用户 # read： 只允许用户读取指定数据库 # readWrite： 允许用户读写指定数据库 # # --数据库管理员 # dbAdmin： 允许用户在指定数据库中操作数据 # dbOwner： 允许用户在指定数据库任意操作 # userAdmin： 允许用户在指定数据库里创建、 删除和管理用户。用户管理员 # # --备份还原 # backup： 备份 # restore： 还原 # # --集群管理。创建这类角色，db须为admin # clusterAdmin： 授予管理集群的最高权限 # clusterManager：授予管理和监控集群的权限 # clusterMonitor： 授予监控集群的权限，对监控工具具有readonly的权限 # hostManager： 管理Server # # --跨库。创建这类角色，db须为admin # readAnyDatabase： 赋予用户所有数据库的读权限 # readWriteAnyDatabase： 赋予用户所有数据库的读写权限 # userAdminAnyDatabase： 赋予用户所有数据库的用户管理权限 # dbAdminAnyDatabase： 赋予用户所有数据库的数据管理权限。 # # --超级管理员。创建这类角色，db须为admin # root： 超级账号， 超级权限 db: "admin" # 目标数据库 }, { role: "readWrite", db: "db_a" } ] }) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
> use admin # 首先切换到admin库 switched to db admin > show tables # 查看存在的表，默认只有一个version表 system.version > db.createUser({ user: "adm", pwd: "adm", roles: [ { role: "root", db: "admin" }, { role: "readWrite", db: "db_a" } ] }) # 创建新用户，用户名adm，密码adm，有2条规则 Successfully added user: { "user" : "adm", "roles" : [ { "role" : "root", "db" : "admin" }, { "role" : "readWrite", "db" : "db_a" } ] } > show tables # 查看存在的表，多了 users system.users system.version > show users # 查看全部用户 { "_id" : "admin.adm", "userId" : UUID("e1f09a26-7fc6-485b-b896-a4f3b823299c"), "user" : "adm", "db" : "admin", "roles" : [ { "role" : "root", "db" : "admin" }, { "role" : "readWrite", "db" : "db_a" } ], "mechanisms" : [ "SCRAM-SHA-1", "SCRAM-SHA-256" ] } > db.auth("adm","adm") # 验证用户(用户名,密码)。返回1表示用户信息有效 1 > db.auth("adm","123") Error: Authentication failed. 0 > |
4.2.用户登录
mongo指令增加--auth后就启用了用户验证，匿名登录只能操作默认的test库。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
> db.shutdownServer() server should be down... > exit bye $ // 修改mgdb_start.sh文件，末尾增加参数--auth： // mongod // --dbpath=/Users/moonmen/appspace/mongodb-macos-x86_64-4.4.1/db // --logpath=/Users/moonmen/appspace/mongodb-macos-x86_64-4.4.1/log/mongodb.log // --port=27017 // --logappend // --fork // --auth $ mgdb_start.sh # 重新启动数据库服务 about to fork child process, waiting until server is ready for connections. forked process: 25650 child process started successfully, parent exiting $ // mongo -host 192.168.1.17 --port 27017 -u dba -p dba --authenticationDatabase "admin" $ mongo -uroot -proot # 登录方式1，登录上附加用户名和密码 MongoDB shell version v4.4.1 connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb Error: Authentication failed. : connect@src/mongo/shell/mongo.js:374:17 @(connect):2:6 exception: connect failed exiting with code 1 $ mongo -uadm -padm MongoDB shell version v4.4.1 connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb Implicit session: session { "id" : UUID("078d82fd-b9df-4e8a-8dc0-900495e7d231") } MongoDB server version: 4.4.1 --- > db test > exit bye $ mongo # 登录方式2，a> 先匿名进入shell MongoDB shell version v4.4.1 connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb Implicit session: session { "id" : UUID("e17b8a0b-27c6-40aa-bbce-59bf3735792a") } MongoDB server version: 4.4.1 > db test > use admin # b> 再进入admin库 switched to db admin > show tables Warning: unable to run listCollections, attempting to approximate collection names by parsing connectionStatus > db.auth('adm', 'adm') # c> 然后验证用户 1 > show tables system.users system.version > exit bye $ mongo -uadm -padm admin # 直接登录进入目标库 MongoDB shell version v4.4.1 connecting to: mongodb://127.0.0.1:27017/admin?compressors=disabled&gssapiServiceName=mongodb Implicit session: session { "id" : UUID("6ecefaa3-68bb-456a-a3bc-9547004ccdb5") } MongoDB server version: 4.4.1 --- > db admin > |
4.3.特定库的用户
在一个库abc，创建一个abc用户，其权限为dbOwner。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
> show dbs admin 0.000GB config 0.000GB local 0.000GB test 0.000GB > use abc # 切换到还没有数据的空白库abc switched to db abc > db.createUser({user:'abc',pwd:'123456', roles:[{role:'dbOwner',db:'abc'}]}) // 在空白库里创建一个用户 Successfully added user: { "user" : "abc", "roles" : [ { "role" : "dbOwner", "db" : "abc" } ] } > show users # 已经use abc，所以查看的是abc库的用户 { "_id" : "abc.abc", "userId" : UUID("f27b699c-c226-47e3-a5d6-4b772884b14f"), "user" : "abc", "db" : "abc", "roles" : [ { "role" : "dbOwner", "db" : "abc" } ], "mechanisms" : [ "SCRAM-SHA-1", "SCRAM-SHA-256" ] } > db.abc.insert({city:'北京'}); # 创建一个表并插入一条记录 WriteResult({ "nInserted" : 1 }) > show tables abc > db.abc.find() { "_id" : ObjectId("5f848e61bfc06ce41158a60d"), "city" : "北京" } > exit bye $ mongo -uabc -p123456 # 登录abc用户，但未指定其可操作的数据库。登录失败 MongoDB shell version v4.4.1 connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb Error: Authentication failed. : connect@src/mongo/shell/mongo.js:374:17 @(connect):2:6 exception: connect failed exiting with code 1 $ mongo -uabc -p123456 abc # 登录abc用户，并指明其可操作的数据库 MongoDB shell version v4.4.1 connecting to: mongodb://127.0.0.1:27017/abc?compressors=disabled&gssapiServiceName=mongodb Implicit session: session { "id" : UUID("e8ffdf8d-a7cd-4664-b6e4-781d028037a5") } MongoDB server version: 4.4.1 > show dbs abc 0.000GB # 只能看到有权限的数据库 > |
4.4.用户密码和权限修改
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
$ mongo -uabc -p123456 abc MongoDB shell version v4.4.1 connecting to: mongodb://127.0.0.1:27017/abc?compressors=disabled&gssapiServiceName=mongodb Implicit session: session { "id" : UUID("56ff5ba4-eca0-46c2-a6ed-e0f6b961d170") } MongoDB server version: 4.4.1 > show dbs abc 0.000GB > use abc switched to db abc > show tables abc > db.abc.find() { "_id" : ObjectId("5f848e61bfc06ce41158a60d"), "city" : "北京" } > db.changeUserPassword("abc", "123") # 修改用户的登录密码 > exit bye $ mongo -uabc -p123456 abc # 使用原密码登录失败 MongoDB shell version v4.4.1 connecting to: mongodb://127.0.0.1:27017/abc?compressors=disabled&gssapiServiceName=mongodb Error: Authentication failed. : connect@src/mongo/shell/mongo.js:374:17 @(connect):2:6 exception: connect failed exiting with code 1 $ mongo -uabc -p123 abc # 登录abc用户，并进入其默认abc库 MongoDB shell version v4.4.1 connecting to: mongodb://127.0.0.1:27017/abc?compressors=disabled&gssapiServiceName=mongodb Implicit session: session { "id" : UUID("015547ef-34e1-492e-9b8e-d7180a02da8c") } MongoDB server version: 4.4.1 > db.revokeRolesFromUser('abc', [{role:'dbOwner', db:'abc'}]) # 撤销dbOwner权限 > db.grantRolesToUser('abc', [{role:'read', db:'abc'}]) # 此时已经没有权限授权操作 uncaught exception: Error: not authorized on abc to execute command { grantRolesToUser: "abc", roles: [ { role: "read", db: "abc" } ], writeConcern: { w: "majority", wtimeout: 600000.0 }, lsid: { id: UUID("015547ef-34e1-492e-9b8e-d7180a02da8c") }, $db: "abc" } : _getErrorWithCode@src/mongo/shell/utils.js:25:13 DB.prototype.grantRolesToUser@src/mongo/shell/db.js:1588:15 @(shell):1:1 > exit bye $ mongo -uadm -p123 # 切换到root权限的用户 MongoDB shell version v4.4.1 connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb Implicit session: session { "id" : UUID("c0193ad1-6528-469b-abfa-7b97d13d02c0") } MongoDB server version: 4.4.1 --- > show dbs abc 0.000GB admin 0.000GB config 0.000GB local 0.000GB test 0.000GB > use abc # 切换到目标数据库 switched to db abc > db.grantRolesToUser('abc', [{role:'read', db:'abc'}]) # 对用户进行授权操作 > exit bye $ mongo -uabc -p123 abc # 登录read权限的用户abc，并进入其有权限的abc库 MongoDB shell version v4.4.1 connecting to: mongodb://127.0.0.1:27017/abc?compressors=disabled&gssapiServiceName=mongodb Implicit session: session { "id" : UUID("4f2d57f0-67d2-4df7-9c6a-05adc9d89095") } MongoDB server version: 4.4.1 > show tables abc > db.abc.insert({'city':'上海'}) # 只有read权限的用户不能进行写操作 WriteCommandError({ "ok" : 0, "errmsg" : "not authorized on abc to execute command { insert: \"abc\", ordered: true, lsid: { id: UUID(\"4f2d57f0-67d2-4df7-9c6a-05adc9d89095\") }, $db: \"abc\" }", "code" : 13, "codeName" : "Unauthorized" }) > |
4.5.删除用户
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
$ mongo -uabc -p123 abc # 登录read权限的用户abc > db.dropUser('abc') # read权限用户没有权限删除用户 uncaught exception: Error: not authorized on abc to execute command { dropUser: "abc", writeConcern: { w: "majority", wtimeout: 600000.0 }, lsid: { id: UUID("4f2d57f0-67d2-4df7-9c6a-05adc9d89095") }, $db: "abc" } : _getErrorWithCode@src/mongo/shell/utils.js:25:13 DB.prototype.dropUser@src/mongo/shell/db.js:1448:11 @(shell):1:1 > exit bye $ mongo -uadm -p123 # 登录root权限的用户 > use abc # 进入目标数据库 switched to db abc > show users { "_id" : "abc.abc", "userId" : UUID("f27b699c-c226-47e3-a5d6-4b772884b14f"), "user" : "abc", "db" : "abc", "roles" : [ { "role" : "read", "db" : "abc" } ], "mechanisms" : [ "SCRAM-SHA-1", "SCRAM-SHA-256" ] } > db.dropUser('abc') # 删除用户 true > show users > # 空 |
5.aggregate聚合查询
常用操作符介绍：
|
1 2 3 4 5 6 7 8 |
$group： 按照给定表达式组合结果 $limit： 限制结果数量 $match： 条件查询，需要同find()一样的参数 $project：包含、排除、重命名和显示字段 $skip： 忽略结果的数量 $sort： 按照给定的字段排序结果 $unwind：分割嵌入数组到自己顶层文件 |
示例数据记录：
|
1 2 3 4 5 6 7 8 9 10 11 |
db.stu.insert({"user":"张三", "age":23, "class":"大三", "math_score":122, "eng_score":122}) db.stu.insert({"user":"李四", "age":12, "class":"初一", "math_score":100, "eng_score":122}) db.stu.insert({"user":"王五", "age":20, "class":"大二", "math_score":119, "eng_score":122}) db.stu.insert({"user":"赵六", "age":34, "class":"成教", "math_score":90, "eng_score":122}) db.stu.insert({"user":"阮七", "age":19, "class":"初三", "math_score":120, "eng_score":122}) db.stu.insert({"user":"张八怪", "age":23, "class":"大三", "math_score":109, "eng_score":122}) db.stu.insert({"user":"李九九", "age":20, "class":"大三", "math_score":98, "eng_score":122}) db.stu.insert({"user":"张三丰", "age":27, "class":"博硕", "math_score":122, "eng_score":122}) db.stu.insert({"user":"葫芦娃", "age":48, "class":"成教", "math_score":100, "eng_score":122}) db.stu.insert({"user":"哪吒", "age":55, "class":"成教", "math_score":96, "eng_score":122}) |
5.1.match条件查询
|
1 2 3 4 5 6 7 8 9 |
> db.stu.aggregate([ {$match:{"class":"大三", "age":23}} # 同sql的where条件 ]) { "_id" : ObjectId("bb"), "user" : "张三", "age" : 23, "class" : "大三", "math_score" : 122, "eng_score" : 122 } { "_id" : ObjectId("b0"), "user" : "张八怪", "age" : 23, "class" : "大三", "math_score" : 109, "eng_score" : 122 } > |
5.2.limit分片
|
1 2 3 4 5 6 7 8 |
> db.stu.aggregate([ {$match:{"class":"大三","age":23}}, # 查询1，按条件查询 {$limit:1} # 查询2，在查询1结果的记录里，取前1条 ]) { "_id" : ObjectId("ab"), "user" : "张三", "age" : 23, "class" : "大三", "math_score" : 122, "eng_score" : 122 } > |
5.3.sort排序
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
> db.stu.aggregate([ {$match:{"class":"大三"}}, {$sort:{age:-1}} # 排序，按age列排序，-1降序 ]) { "_id" : ObjectId("ab"), "user" : "张三", "age" : 23, "class" : "大三", "math_score" : 122, "eng_score" : 122 } { "_id" : ObjectId("b0"), "user" : "张八怪", "age" : 23, "class" : "大三", "math_score" : 109, "eng_score" : 122 } { "_id" : ObjectId("b1"), "user" : "李九九", "age" : 20, "class" : "大三", "math_score" : 98, "eng_score" : 122 } > db.stu.aggregate([ {$match:{"class":"大三"}}, {$sort:{age:1}} # 按age列排序，1升序 ]) { "_id" : ObjectId("b1"), "user" : "李九九", "age" : 20, "class" : "大三", "math_score" : 98, "eng_score" : 122 } { "_id" : ObjectId("ab"), "user" : "张三", "age" : 23, "class" : "大三", "math_score" : 122, "eng_score" : 122 } { "_id" : ObjectId("b0"), "user" : "张八怪", "age" : 23, "class" : "大三", "math_score" : 109, "eng_score" : 122 } > |
5.4.skip忽略记录
|
1 2 3 4 5 6 7 8 9 10 11 |
> db.stu.aggregate([ {$match:{"class":"大三"}}, {$sort:{age:-1}}, {$skip:1} # 舍弃第1条记录 ]) { "_id" : ObjectId("b0"), "user" : "张八怪", "age" : 23, "class" : "大三", "math_score" : 109, "eng_score" : 122 } { "_id" : ObjectId("b1"), "user" : "李九九", "age" : 20, "class" : "大三", "math_score" : 98, "eng_score" : 122 } > |
5.5.group分组
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
> db.stu.aggregate([ { $group:{ _id:"$class", # 按照class列的值进行分组。固定关键字_id stu_count:{$sum:1} # 统计每一组的记录的数量。使用了表达式sum-详见下文 } } ]) { "_id" : "大三", "stu_count" : 3 } { "_id" : "大二", "stu_count" : 1 } { "_id" : "初三", "stu_count" : 1 } { "_id" : "初一", "stu_count" : 1 } { "_id" : "成教", "stu_count" : 3 } { "_id" : "博硕", "stu_count" : 1 } > db.stu.aggregate([ { $group:{ # 查询1 _id:"$class", stu_count:{$sum:1} }}, { $group:{ # 查询2。在查询1结果的基础上，再次统计组的数量 _id:null, # _id参照的列为null，则对全部记录进行处理 class_count:{$sum:1} # 每个记录都无差别统计在内 }} ]) { "_id" : null, "class_count" : 6 } > |
5.6.project指定列查询
|
1 2 3 4 5 6 7 8 9 10 11 |
> db.stu.aggregate([ {$project:{user:1, class:1}}, # 查询目标列。同sql的select(列1,列2,) from.. {$limit:5} # 保留前5条记录 ]) { "_id" : ObjectId("5f86b7f58bb01a4bbd5ea7ab"), "user" : "张三", "class" : "大三" } { "_id" : ObjectId("5f86b7f58bb01a4bbd5ea7ac"), "user" : "李四", "class" : "初一" } { "_id" : ObjectId("5f86b7f58bb01a4bbd5ea7ad"), "user" : "王五", "class" : "大二" } { "_id" : ObjectId("5f86b7f58bb01a4bbd5ea7ae"), "user" : "赵六", "class" : "成教" } { "_id" : ObjectId("5f86b7f58bb01a4bbd5ea7af"), "user" : "阮七", "class" : "初三" } > |
5.7.unwind拆分列查询
示例数据，likes为数组：
|
1 2 3 4 |
db.likes.insert({"user":"张三", "class":"大三", "likes":["绘画", "阅读", "唱歌", "徒步"]}) db.likes.insert({"user":"小明", "class":"大三", "likes":["击剑", "乒乓球", "Vlog"]}) db.likes.insert({"user":"大明", "class":"大三", "likes":null}) |
拆分数组数据属性的列：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
> db.likes.aggregate([ {$unwind:"$likes"} # 对likes列的数组进行拆分 ]) { "_id" : ObjectId("57b5"), "user" : "张三", "class" : "大三", "likes" : "绘画" } { "_id" : ObjectId("57b5"), "user" : "张三", "class" : "大三", "likes" : "阅读" } { "_id" : ObjectId("57b5"), "user" : "张三", "class" : "大三", "likes" : "唱歌" } { "_id" : ObjectId("57b5"), "user" : "张三", "class" : "大三", "likes" : "徒步" } { "_id" : ObjectId("57b6"), "user" : "小明", "class" : "大三", "likes" : "击剑" } { "_id" : ObjectId("57b6"), "user" : "小明", "class" : "大三", "likes" : "乒乓球"} { "_id" : ObjectId("57b6"), "user" : "小明", "class" : "大三", "likes" : "Vlog"} > |
查分数组，并设置一个新的列，用于给原数组元素生成的条目一个索引：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
> db.likes.aggregate([ { $unwind:{ path:"$likes", includeArrayIndex:"likeIndex" # 增加一个名为‘likeIndex’的列，用于标明原数组中元素的索引 } } ]) { "_id" : ObjectId("5ea7b5"), "user" : "张三", "class" : "大三", "likes" : "绘画", "likeIndex" : NumberLong(0) } { "_id" : ObjectId("5ea7b5"), "user" : "张三", "class" : "大三", "likes" : "阅读", "likeIndex" : NumberLong(1) } { "_id" : ObjectId("5ea7b5"), "user" : "张三", "class" : "大三", "likes" : "唱歌", "likeIndex" : NumberLong(2) } { "_id" : ObjectId("5ea7b5"), "user" : "张三", "class" : "大三", "likes" : "徒步", "likeIndex" : NumberLong(3) } { "_id" : ObjectId("5ea7b6"), "user" : "小明", "class" : "大三", "likes" : "击剑", "likeIndex" : NumberLong(0) } { "_id" : ObjectId("5ea7b6"), "user" : "小明", "class" : "大三", "likes" : "乒乓球", "likeIndex" : NumberLong(1) } { "_id" : ObjectId("5ea7b6"), "user" : "小明", "class" : "大三", "likes" : "Vlog", "likeIndex" : NumberLong(2) } > |
处理并显示列值无效的条目：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
> db.likes.aggregate([ { $unwind:{ path:"$likes", includeArrayIndex:"likeIndex", preserveNullAndEmptyArrays:true # 是否处理无目标列/目标列值为空或null的记录。默认false-此时可不设置此参数。 } } ]) { "_id" : ObjectId("5ea7b5"), "user" : "张三", "class" : "大三", "likes" : "绘画", "likeIndex" : NumberLong(0) } { "_id" : ObjectId("5ea7b5"), "user" : "张三", "class" : "大三", "likes" : "阅读", "likeIndex" : NumberLong(1) } { "_id" : ObjectId("5ea7b5"), "user" : "张三", "class" : "大三", "likes" : "唱歌", "likeIndex" : NumberLong(2) } { "_id" : ObjectId("5ea7b5"), "user" : "张三", "class" : "大三", "likes" : "徒步", "likeIndex" : NumberLong(3) } { "_id" : ObjectId("5ea7b6"), "user" : "小明", "class" : "大三", "likes" : "击剑", "likeIndex" : NumberLong(0) } { "_id" : ObjectId("5ea7b6"), "user" : "小明", "class" : "大三", "likes" : "乒乓球", "likeIndex" : NumberLong(1) } { "_id" : ObjectId("5ea7b6"), "user" : "小明", "class" : "大三", "likes" : "Vlog", "likeIndex" : NumberLong(2) } { "_id" : ObjectId("5ea7b7"), "user" : "大明", "class" : "大三", "likes" : null, "likeIndex" : null } > |
5.8.常用表达式
|
1 2 3 4 5 6 7 8 9 |
$addToSet 在结果记录中插入值到一个数组中，但不创建副本。 $avg 计算平均值 $first 获取第一个记录 $last 获取最后一个记录 $max 获取集合中所有文档对应值得最大值。 $min 获取集合中所有文档对应值得最小值。 $push 在结果文档中插入值到一个数组中。 $sum 计算总和。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
> db.stu.find() {"_id":ObjectId("7b8"),"user":"张三","age":23,"class":"大三","math_score":22,"eng_score":12} {"_id":ObjectId("7b9"),"user":"李四","age":12,"class":"初一","math_score":10,"eng_score":90} {"_id":ObjectId("7ba"),"user":"王五","age":20,"class":"大二","math_score":19,"eng_score":45} {"_id":ObjectId("7bb"),"user":"赵六","age":34,"class":"成教","math_score":90,"eng_score":55} {"_id":ObjectId("7bc"),"user":"阮七","age":19,"class":"初三","math_score":10,"eng_score":76} {"_id":ObjectId("7bd"),"user":"张怪","age":23,"class":"大三","math_score":19,"eng_score":12} {"_id":ObjectId("7be"),"user":"李九","age":20,"class":"大三","math_score":98,"eng_score":23} {"_id":ObjectId("7bf"),"user":"张丰","age":27,"class":"博硕","math_score":12,"eng_score":80} {"_id":ObjectId("7c0"),"user":"葫娃","age":48,"class":"成教","math_score":10,"eng_score":12} {"_id":ObjectId("7c1"),"user":"哪吒","age":55,"class":"成教","math_score":96,"eng_score":49} |
5.8.1.addToSet集合单列值
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
> db.stu.aggregate([ { $group:{ # 进行group分组 _id:"$class", user:{$addToSet:"$user"} # 集合到组内 } } ]) { "_id" : "博硕", "user" : [ "张三丰" ] } { "_id" : "大三", "user" : [ "李九", "张怪", "张三" ] } { "_id" : "初一", "user" : [ "李四" ] } { "_id" : "成教", "user" : [ "葫娃", "赵六", "哪吒" ] } { "_id" : "初三", "user" : [ "阮七" ] } { "_id" : "大二", "user" : [ "王五" ] } > |
5.8.2.avg平均值
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
> db.stu.aggregate([ { $group:{ # 分组 _id:"$class", user:{$addToSet:"$user"}, mathAvgScore:{$avg:"$math_score"} # 组内指定列的平均值 } } ]) { "_id" : "大三", "user" : [ "张怪", "张三", "李九" ], "mathAvgScore" : 46.333333333333336 } { "_id" : "初三", "user" : [ "阮七" ], "mathAvgScore" : 10 } { "_id" : "博硕", "user" : [ "张丰" ], "mathAvgScore" : 12 } { "_id" : "初一", "user" : [ "李四" ], "mathAvgScore" : 10 } { "_id" : "成教", "user" : [ "哪吒", "赵六", "葫娃" ], "mathAvgScore" : 65.33333333333333 } { "_id" : "大二", "user" : [ "王五" ], "mathAvgScore" : 19 } > |
5.8.3.first出现的第一条记录
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
> db.stu.aggregate([ { $group:{ # 分组 _id:"$class", firstGroup:{$first:"$user"} # 组内第一次出现的user } } ]) { "_id" : "大二", "firstGroup" : "王五" } { "_id" : "初三", "firstGroup" : "阮七" } { "_id" : "初一", "firstGroup" : "李四" } { "_id" : "成教", "firstGroup" : "赵六" } { "_id" : "博硕", "firstGroup" : "张丰" } { "_id" : "大三", "firstGroup" : "张三" } > |
5.8.4.last最后出现的记录
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
> db.stu.aggregate([ { $group:{ # 分组 _id:"$class", lastGroup:{$last:"$user"} # 每个组最后出现的user } } ]) { "_id" : "初一", "lastGroup" : "李四" } { "_id" : "成教", "lastGroup" : "哪吒" } { "_id" : "大三", "lastGroup" : "李九" } { "_id" : "博硕", "lastGroup" : "张丰" } { "_id" : "初三", "lastGroup" : "阮七" } { "_id" : "大二", "lastGroup" : "王五" } > |
5.8.5.max最大值
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
> db.stu.aggregate([ { $group:{ _id:"$class", maxMathScore:{$max:"$math_score"} # 组内出现的最大math_score } } ]) { "_id" : "博硕", "maxMathScore" : 12 } { "_id" : "大三", "maxMathScore" : 98 } { "_id" : "初一", "maxMathScore" : 10 } { "_id" : "成教", "maxMathScore" : 96 } { "_id" : "初三", "maxMathScore" : 10 } { "_id" : "大二", "maxMathScore" : 19 } > |
5.8.6.min最小值
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
> db.stu.aggregate([ {$group:{ _id:"$class", minMathScore:{$min:"$math_score"} }} ]) { "_id" : "大三", "minMathScore" : 19 } { "_id" : "大二", "minMathScore" : 19 } { "_id" : "初三", "minMathScore" : 10 } { "_id" : "初一", "minMathScore" : 10 } { "_id" : "成教", "minMathScore" : 10 } { "_id" : "博硕", "minMathScore" : 12 } > |
5.8.7.push集合多列值
比起addToSet，push可以抽取更多数据。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
> db.stu.aggregate([ { $group:{ _id:"$class", items:{$push:{user:"$user", age:"$age"}} } } ]) { "_id" : "博硕", "items" : [ { "user" : "张丰", "age" : 27 } ] } { "_id" : "初三", "items" : [ { "user" : "阮七", "age" : 19 } ] } { "_id" : "大二", "items" : [ { "user" : "王五", "age" : 20 } ] } { "_id" : "初一", "items" : [ { "user" : "李四", "age" : 12 } ] } { "_id" : "成教", "items" : [ { "user" : "赵六", "age" : 34 }, { "user" : "葫娃", "age" : 48 }, { "user" : "哪吒", "age" : 55 } ] } { "_id" : "大三", "items" : [ { "user" : "张三", "age" : 23 }, { "user" : "张怪", "age" : 23 }, { "user" : "李九", "age" : 20 } ] } > |
5.8.8.sum累计
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
> db.stu.aggregate([ { $group:{ _id:"$class", items:{$sum:1} # 累加1，可用于记录条目数量。可根据须要累加其它数字 } } ]) { "_id" : "初一", "items" : 1 } { "_id" : "成教", "items" : 3 } { "_id" : "博硕", "items" : 1 } { "_id" : "大三", "items" : 3 } { "_id" : "初三", "items" : 1 } { "_id" : "大二", "items" : 1 } > > db.stu.aggregate([ { $group:{ _id:"$class", mathScoresSum:{$sum:"$math_score"} # 累加某属性的值 } } ]) { "_id" : "博硕", "mathScoresSum" : 12 } { "_id" : "大三", "mathScoresSum" : 139 } { "_id" : "初一", "mathScoresSum" : 10 } { "_id" : "成教", "mathScoresSum" : 196 } { "_id" : "大二", "mathScoresSum" : 19 } { "_id" : "初三", "mathScoresSum" : 10 } > |
5.9.组合查询示例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
> db.stu.aggregate([ {$sort:{math_score:-1}}, # 首先进行排序，后续的分组以有序记录为基础 {$group:{ user: { "$first":"$$CURRENT.user" },# $$CURRENT即$first所指的组内第1行 _id:"$class", age: { "$first":"$$CURRENT.age" }, maxMathScore:{$max:"$math_score"} # 取组内最大的分数 }} ]) { "_id" : "初三", "user" : "阮七", "age" : 19, "maxMathScore" : 10 } { "_id" : "大二", "user" : "王五", "age" : 20, "maxMathScore" : 19 } { "_id" : "大三", "user" : "李九", "age" : 20, "maxMathScore" : 98 } { "_id" : "博硕", "user" : "张丰", "age" : 27, "maxMathScore" : 12 } { "_id" : "成教", "user" : "哪吒", "age" : 55, "maxMathScore" : 96 } { "_id" : "初一", "user" : "李四", "age" : 12, "maxMathScore" : 10 } > |
-end
声明
本文由崔维友 威格灵 cuiweiyou vigiles cuiweiyou 原创，转载请注明出处：http://www.gaohaiyan.com/2780.html
承接App定制、企业web站点、办公系统软件 设计开发，外包项目，毕设
