【MongoDB】3.MongoDB基本操作（總結），mongodb3.mongodb

【MongoDB】3.MongoDB基本操作（總結），mongodb3.mongodb

官方參考文檔：https://docs.mongodb.com/manual/reference/method/db.collection.find/

總結：

//左邊是mongodb查詢語句，右邊是sql語句。
db.users.find({})   //select * from users
db.users.find({"age" : 27})    //select * from users where age = 27
db.users.find({"name" : "Linda"}, {"age" : 27})  //select * from users where "name" = "Linda" and age = 27
db.users.find({}, {"name" : 1, "email" : 1})   //select name, email from users
db.users.find({}, {"name" : 1, "_id" : 0})  // 即時加上了列篩選，_id也會返回；必須顯式的阻止_id返回
db.users.find({"age" : {"$gte" : 18, "$lte" : 30}})   //select * from users where age>=18 and age<= 30 
db.users.find({"username" : {"$ne" : "Linda"}})   //select * from users where username!='Linda'

db.food.find({"ticket_no" : {"$in" : [725, 542, 390]}})  //select * from food where ticket_no in (725, 542, 390)
db.food.find({"ticket_no" : {"$nin" : [725, 542, 390]}})   //select * from food where ticket_no not in (725, 542, 390)
db.food.find({"$or" : [{"ticket_no" : 725}, {"winner" : true}]}) select * form food where ticket_no = 725 or winner = true
db.food.find({"id_num" : {"$mod" : [5, 1]}}) select * from food where (id_num mod 5) = 1
db.food.find({"$not": {"age" : 27}}) select * from food where not (age = 27)
db.food.find({"username" : {"$in" : [null], "$exists" : true}}) select * from food where username is null // 如果直接通過find({"username" : null})進行查詢，那麼連帶沒有"username"的紀錄一併篩選出來
db.food.find({"name" : /joey/}) // 正則查詢，value是符合PCRE的運算式
db.food.find({"fruit" : {$all : ["apple", "banana"]}}) // 對數組的查詢, 欄位fruit中，既包含"apple",又包含"banana"的紀錄
db.food.find({"fruit.2" : "peach"}) // 對數組的查詢, 欄位fruit中，第3個(從0開始)元素是peach的紀錄
db.food.find({"fruit" : {"$size" : 3}}) // 對數組的查詢, 查詢數組元素個數是3的記錄，$size前面無法和其他的操作符複合使用
db.food.find({}, {"comments" : {"$slice" : 10}}) // 對數組的查詢，只返回數組comments中的前十條，還可以{"$slice" : -10}， {"$slice" : [23, 10]}; 分別返回最後10條，和中間10條
db.food.find({"name.first" : "Joe", "name.last" : "Schmoe"})  // 巢狀查詢
db.food.find({"comments" : {"$elemMatch" : {"author" : "joe", "score" : {"$gte" : 5}}}}) // 巢狀查詢，僅當嵌套的元素是數組時使用,
db.foo.find({"$where" : "this.x + this.y == 10"}) // 複雜的查詢，$where當然是非常方便的，但效率低下。對於複雜查詢，考慮的順序應當是 正則 -&gt; MapReduce -&gt; $where
db.foo.find({"$where" : "function() { return this.x + this.y == 10; }"}) // $where可以支援javascript函數作為查詢條件
db.foo.find().sort({"x" : 1}).limit(1).skip(10); // 返回第(10, 11]條，按"x"進行排序; 三個limit的順序是任意的，應該盡量避免skip中使用large-number

一、資料庫操作：
    //建立test資料庫，建立users集合：use test; db.createCollection("users")
    //刪除test資料庫：use test; db.dropDatabase()
    //刪除users集合：use test; db.users.drop()

二、集合操作：

1.增刪查改

1.1、新增：
   //插入資料
   db.users.insert([{"_id" : ObjectId("4e17ce13c39f1afe0ba78ce5"),"a" : 1.0,"b" : "測試1","c" : true}])
   db.users.insert([{"_id" : ObjectId("59f2d11a970f79131465068f"),"a" : 2.0,"b" : "測試2","c" : false}])
   db.users.insert([{"a" : 3.0,"b" : "測試3","c" : false},{"a" : 4.0,"b" : "測試4","c" : false}])

  //插入10000條資料
   for(var i=0;i<10000;i++)
  {
     db.CollectionName.insert([{},{}])
   }

1.2、查詢（find）：

（1）根據id搜尋：{"_id" : ObjectId("550a40cc77c805e7d59f07cc")}
（2）根據時間搜尋：{"ct":{$gt:ISODate("2015-05-08T12:18:51.303Z")}}
（3）查空：db.table.find({"name":{$in:[null]}});
（4）查非空：db.table.find({"name":{$ne:null}});
（5）and：{"name":"xb12369","name":"test"}
（6）or：{$or:[{"name":"xb12369"},{"name":"test"}]}
（7）升降序：{ct:-1}  {ct:1}  -1降序  1升序
     排序：sort
（8）模糊查詢：{"name":{$regex:".*小新.*"}}
    eg: collection.find({name: new RegExp("^.*"+name+".*$")})
          //db.users.find({"SubInfo":{"$elemMatch":{ "ContactPhone2" : "22222222222", "ContactName2":{$regex:".*克雋.*"}}}})

          db.Users.find({"username":{$type:2,$regex:/^.{100,}$/}}) //欄位類型為2，表示有此欄位，或者用$exists:true  //$regex:/^.{100,}$/   正則匹配 欄位長度大於100

（9）//指定返回哪些列（鍵）：
        //補充說明：第一個{} 放where條件，第二個{} 指定哪些列顯示和不顯示（0表示不顯示 1表示顯示)。
        db.users.find({}, {"a" : 1, "b" : 1});

1.3、更新（update）：
 （1）更新內容：db.users.update({},{$set:{"b" : "測試3"}})

 （2）增加欄位：db.users.update({}, {$set: {"name":""}}, {multi: 1})

             db.users.update({}, {$set: {IsShare: true}}, {multi: 1}) //更新所有行增加子段IsShare //等價於db.users.updateMany({},{$set:{"IsShare":true}})
             //db.users.update({},{$set:{"SubInfo":[{"ContactName1":"s1","ContactPhone1":"11111111111"},{"ContactName2":"s2","ContactPhone2":"22222222222"}]}}, {multi: 1})

               刪除欄位：db.users.update({},{$unset:{"name":""}},false, true)

  （3）更改欄位名稱：db.users.update({},{$rename:{"a":"f"}}) //將欄位"abc"改為"def"

1.4、刪除：
  （1）刪除test資料庫users集合中資料：use test; db.users.remove({})

1.5、其他：
   （1）where條件：
        1）等於
           db.users.find({"a": 1.0},{"a": 1, "b" : 1});
        2）使用and
         db.users.find({$and:[{ "a": 2.0 }, { "c" : false }]})
        3）使用or
           db.users.find({ $or : [{"name": "Bob"}, {"age": 18}] });
        4）<, <=, >, >= ($lt, $lte, $gt, $gte )
           db.users.find({"age": {$gte: 20}},{"age": {$lte: 30}});
        5）使用in, not in ($in, $nin)
           db.users.find({"age" : {$in: [10, 22, 26]}});
        6）匹配null
           db.users.find({"age" : null});
        7）like (mongoDB 支援Regex)
           db.users.find({"name":"/hurry/"});
           db.users.find({"name":"/^hurry/"});
        8）使用distinct
           db.users.distinct("b");
        9）使用count
           db.users.count();
   （2）數組查詢：

   （3）$exists:
$exists用來判斷一個元素是否存在：
db.users.find( { "a": { $exists : true } } ); // 如果存在元素a,就返回
db.users.find( { "a" : { $exists : false } } ); // 如果不存在元素a，就返回
   （4）Regex:
mongo支援Regex：
//db.users.find( { name : /acme.*corp/i } ); // 後面的i的意思是區分大小寫

//查詢欄位是否存在：
db.FollowRecord.find({"FollowContent.I131": { $exists: true } }) 

//正則
//db.Record.find({ $and: [ { "Content.I131": { $regex: ".*計劃.*"  } }, { "FollowContent.StartDate": { $exists: true } } ] }) 
//db.Record.find({ $and: [ { "Content.I131": { $ne: ""  } }, { "Content.StartDate": { $exists: true } } ] }) 

2.跨集合查詢：
2.1跨集合統計資料條數：

//localhost--本地（IP）//DatabaseName--資料庫名//CollectionName--集合名//RelationID--關聯IDlet srcConnection = "localhost";let srcDb = "DatabaseName";let Arr=[];use(srcDb); db.CollectionName1.find({$and:[{"FieldName1" : "FieldValue1"},{"FieldName2" : "FieldValue2"}]}).forEach(function(x){let tt=x["_id"]+"";    Arr.push(tt);   });    print(Arr.length);let count=db.CollectionName2.find({"RelationID":{$in:Arr}}).count();print(count); 

//排序取第一條

db.CollectionName2.find({$and:[{"RelationID":{$in:Arr}},{"Isvalid":true},{"Date":{$ne:""}}]}).sort({"Date":-1}).limit(1);

3.複製集合：

3.1複製集合資料

const BATCH_SIZE = 100;let srcConnection = "localhost";let srcDb = "SDatabaseName";let dstConnection = "localhost";let dstDb = "TDatabaseName";let Arr=[];use(srcDb); db.CollectionName1.find({$and:[{"FieldName1" : "FieldValue1"},{"FieldName2" : "FieldValue2"}]}).forEach(function(x){   let tt=x["_id"]+"";      Arr.push(tt);      //print(tt);       //use(dstDb)   //db.CollectionName1.insert(x);    });  use(srcDb); let Count=db.CollectionName2.find({"RelationID" :{ $in:Arr}}).count();//print(Arr);print(Count);use(dstDb);//idPolicy: overwrite_with_same_id|always_insert_with_new_id|insert_with_new_id_if_id_exists|skip_documents_with_existing_id|abort_if_id_already_existslet toCopyCollections = [    { srcCollection: "CollectionName2", query: {"RelationID" :{ $in:Arr}}, projection: {}, dstCollection: "CollectionName2", idPolicy: "overwrite_with_same_id", dropDstCollection: false }];// if have more indexes than default index (_id), create index after copy data.let shouldCreateIndex = true;let totalCopyResult = {    result: {},    fails: [],}function copyCollection(params) {    let { srcCollection, dstCollection, query, projection, idPolicy, dropDstCollection } = params;    let continueRead = true;    console.log(`copy docs from ${srcConnection}:${srcDb}:${srcCollection} to ${dstConnection}:${dstDb}:${dstCollection} start...`);    let isSameCollection = srcConnection === dstConnection && srcDb === dstDb && srcCollection === dstCollection;    if (isSameCollection) {        if (toCopyCollections.length === 1)            shouldCreateIndex = false;        else            params.shouldCreateIndex = false;    }    if (dropDstCollection) {        // srcCollection is same to dstCollection, can not drop dstCollection (equal to drop srcCollection)        // drop dst collection and copy from same collection, means nothing to do.        if (isSameCollection) return;        mb.dropCollection({ connection: dstConnection, db: dstDb, collection: dstCollection });    }    totalCopyResult.result[dstCollection] = {        nInserted: 0,        nModified: 0,        nSkipped: 0,        failed: 0,    };    let collectionRst = totalCopyResult.result[dstCollection];    let limitReadCount = Number.MAX_SAFE_INTEGER;    if (isSameCollection)        limitReadCount = mb.runScript({ connection: srcConnection, db: srcDb, script: `db.getCollection("${srcCollection}").find(${tojson(query)}).count()` })    let skip = 0;    while (continueRead) {        let limit = limitReadCount > BATCH_SIZE ? BATCH_SIZE : limitReadCount;        let docs = mb.readFromDb({ connection: srcConnection, db: srcDb, collection: srcCollection, query, projection, skip, limit });        let readLength = docs.length;        skip += readLength;        limitReadCount -= readLength;        if (readLength < BATCH_SIZE)            continueRead = false;        if (readLength) {            let copyResult = mb.writeToDb({ connection: dstConnection, db: dstDb, collection: dstCollection, docs, idPolicy });            let failed = copyResult.errors.length;            let success = copyResult.nInserted + copyResult.nModified;            collectionRst.nInserted += copyResult.nInserted;            collectionRst.nModified += copyResult.nModified;            collectionRst.nSkipped += copyResult.nSkipped;            collectionRst.failed += failed;            console.log(`${dstCollection}: ${collectionRst.nInserted + collectionRst.nModified} docs successfully imported, ${collectionRst.failed} docs failed.`);            if (failed) {                console.log("Failed objects", copyResult.errors);            }            totalCopyResult.fails = [...totalCopyResult.fails, ...copyResult.errors];        }        sleep(10)    }    console.log(`copy docs from ${srcConnection}:${srcDb}:${srcCollection} to ${dstConnection}:${dstDb}:${dstCollection} finished.`);}toCopyCollections.forEach(it => copyCollection(it));if (shouldCreateIndex) {    let indexCreationPrompted = false;    function indexCreationPrompt() {        if (indexCreationPrompted) return;        let waitTime = 3;        console.log(`Index creating will start in ${waitTime} seconds...`)        sleep(1000 * waitTime);        indexCreationPrompted = true;    }    let srcCollections = toCopyCollections.filter(it => it["shouldCreateIndex"] !== false).map(it => it.srcCollection)    srcCollections.forEach(it => {        let indexes = mb.runScript({ connection: srcConnection, db: srcDb, script: `db.getCollection("${it}").getIndexes();` });        if (indexes.length > 1) {            let toCopyCollection = _.find(toCopyCollections, { srcCollection: it });            if (!toCopyCollection) return;            let dstCollection = toCopyCollection.dstCollection;            indexes.forEach(index => {                if (index.name === "_id_") return;                indexCreationPrompt();                let newIndex = _.cloneDeep(index);                // remove index version and engine info, these info may fail createIndexes operator.                delete newIndex.v;                delete newIndex.textIndexVersion;                delete newIndex["2dsphereIndexVersion"];                delete newIndex.storageEngine;                newIndex.ns = `${dstDb}.${dstCollection}`;                console.log(`create index ${newIndex.name} for ${dstDb}.${dstCollection}`);                console.log(db.runCommand({                    createIndexes: dstCollection,                    indexes: [newIndex]                }));            })        }    });    if (indexCreationPrompted)        console.log("create index complete.")}if (totalCopyResult.result) {    let successed = 0;    let failed = 0;    let collections = _.keys(totalCopyResult.result);    collections.forEach((key) => {        let obj = totalCopyResult.result[key];        successed += obj.nInserted + obj.nModified;        failed += obj.failed;    });    console.log(`${successed} document(s) of ${collections.length} collection(s) have been copied.`, totalCopyResult.result);    if (failed) {        console.log(`${failed} document(s) haven't been copied, please check failed list below.`);    } else {        console.log("All documents copied successfully.");    }}if (totalCopyResult.fails.length) {    console.log("All failed objects", totalCopyResult.fails);}

   

頂

0

踩

0

查看評論