mongodb常用文法及指令

mongodb常用文法及指令

建立使用者：db.createUser({user:"xu",pwd:"123456",roles:[{role:"root",db:"admin"}]});
登入使用者：db.auth('xu','123456')
建立資料庫：
切換資料庫：use databases;
           

1 術語和概念
SQL MongoDB
table   collection
row document或BSON document
column  field
index   index
table joins $lookup, 綁定文檔
標明任一列作為主鍵   自定指定`_id`為主鍵
aggregation (e.g. group by) aggregation pipeline
2 建立與改變
SQL語句===========================MongoDB語句
CREATE TABLE people ( id MEDIUMINT NOT NULL AUTO_INCREMENT, user_id Varchar(), age Number, status char(), PRIMARY KEY (id) )==============   db.people.insertOne( { user_id: "abc123", age: , status: "A" } )
ALTER TABLE people ADD join_date DATETIME============== db.people.updateMany( { }, { $set: { join_date: new Date() } } )
ALTER TABLE people DROP COLUMN join_date ============== db.people.updateMany( { }, { $unset: { "join_date": "" } } )
CREATE INDEX idx_user_id_asc ON people(user_id)==============   db.people.createIndex( { user_id:  } )
CREATE INDEX idx_user_id_asc_age_desc ON people(user_id, age DESC)==============    db.people.createIndex( { user_id: , age: - } )
CREATE INDEX idx_user_id_asc_age_desc ON people(user_id, age DESC)==============    db.people.createIndex( { user_id: , age: - } )
DROP TABLE people============== db.people.drop()
 插入
SQL INSERT==============    MongoDB insertOne()
INSERT INTO people(user_id, age, status) VALUES ("bcd001", , "A")
==============db.people.insertOne( { user_id: "bcd001", age: , status: "A" } )
 選擇查詢
SQL SELECT  MongoDB find()
SELECT * FROM people==============  db.people.find()
SELECT id, user_id, status FROM people==============    db.people.find( { }, { user_id: , status:  } )
SELECT user_id, status FROM people==============    db.people.find( { }, { user_id: , status: , _id:  } )
SELECT * FROM people WHERE status = "A"==============   db.people.find( { status: "A" } )
SELECT user_id, status FROM people WHERE status = "A"============== db.people.find( { status: "A" }, { user_id: , status: , _id:  } )
SELECT * FROM people WHERE status != "A"==============  db.people.find( { status: { $ne: "A" } } )
SELECT * FROM people WHERE status = "A" AND age = ==============  db.people.find( { status: "A", age:  } )
SELECT * FROM people WHERE status = "A" OR age = ==============   db.people.find( { $or: [ { status: "A" } , { age:  } ] } )
SELECT * FROM people WHERE age > ==============   db.people.find( { age: { $gt:  } } )
SELECT * FROM people WHERE age < ==============   db.people.find( { age: { $lt:  } } )
SELECT * FROM people WHERE age >  AND age <= ============== db.people.find( { age: { $gt: , $lte:  } } )
SELECT * FROM people WHERE user_id like "%bc%"==============    db.people.find( { user_id: /bc/ } ) -or- db.people.find( { user_id: { $regex: /bc/ } } )
SELECT * FROM people WHERE user_id like "bc%"============== db.people.find( { user_id: /^bc/ } ) -or- db.people.find( { user_id: { $regex: /^bc/ } } )
SELECT * FROM people WHERE status = "A" ORDER BY user_id ASC==============  db.people.find( { status: "A" } ).sort( { user_id:  } )
SELECT * FROM people WHERE status = "A" ORDER BY user_id DESC============== db.people.find( { status: "A" } ).sort( { user_id: - } )
SELECT * FROM people WHERE status = "A" ORDER BY user_id DESC============== db.people.find( { status: "A" } ).sort( { user_id: - } )
SELECT COUNT(*) FROM people==============   db.people.count() or db.people.find().count()
SELECT COUNT(user_id) FROM people============== db.people.count( { user_id: { $exists: true } } ) or db.people.find( { user_id: { $exists: true } } ).count()
SELECT COUNT(*) FROM people WHERE age >   ==============db.people.count( { age: { $gt:  } } ) or db.people.find( { age: { $gt:  } } ).count()
SELECT DISTINCT(status) FROM people==============   db.people.distinct( "status" )
SELECT * FROM people LIMIT ==============  db.people.findOne() or db.people.find().limit()
SELECT * FROM people LIMIT  SKIP ==============  db.people.find().limit().skip()
EXPLAIN SELECT * FROM people WHERE status = "A"==============   db.people.find( { status: "A" } ).explain()
 修改資料
SQL Update==============    MongoDB updateMany()
UPDATE people SET status = "C" WHERE age > ============== db.people.updateMany( { age: { $gt:  } }, { $set: { status: "C" } } )
UPDATE people SET age = age +  WHERE status = "A"  db.people.updateMany( { status: "A" } , { $inc: { age:  } } )
 删除資料
SQL Delete==============    MongoDB deleteMany()
DELETE FROM people WHERE status = "D"============== db.people.deleteMany( { status: "D" } )
DELETE FROM people==============    db.people.deleteMany({})
           

1.分片
在Mongodb裡面存在另一種叢集，就是分片技術,可以滿足MongoDB資料量大量增長的需求。
當MongoDB存儲海量的資料時，一台機器可能不足以存儲資料，也可能不足以提供可接受的讀寫吞吐量。這時，我們就可以通過在多台機器上分割資料，使得資料庫系統能存儲和處理更多的資料。
2.為什麼使用分片
複制所有的寫入操作到主節點
延遲的敏感資料會在主節點查詢
單個副本集限制在12個節點
當請求量巨大時會出現記憶體不足。
本地磁盤不足
垂直擴充價格昂貴
           

mongodb中的表關聯
A表:
db.product.insert({"_id":,"productname":"商品1","price":})
db.product.insert({"_id":,"productname":"商品2","price":})
B表：
db.orders.insert({"_id":,"pid":,"ordername":"訂單1"})
db.orders.insert({"_id":,"pid":,"ordername":"訂單2"})
db.orders.insert({"_id":,"pid":,"ordername":"訂單3"})
db.orders.insert({"_id":,"pid":,"ordername":"訂單4"})
查詢
db.product.find()
db.orders.find()
$lookup表關聯用法：
------------------------------------
db.product.aggregate([
    {
      $lookup:
        {
          from: "orders",
          localField: "_id",
          foreignField: "pid",
          as: "inventory_docs"
        }
   }
])
------------------------------------
$lookup中的參數：
from：需要關聯的表【orders】
localField: 【product】表需要關聯的鍵。
foreignField：【orders】的matching key。
as：對應的外鍵集合的資料，【因為可能是一對多的】
$match篩選：
----------------------------------------
db.product.aggregate([
    {
      $lookup:
        {
          from: "orders",
          localField: "_id",
          foreignField: "pid",
          as: "inventory_docs"
        }
   },
   { $match : { price : {$gt:} } }
])
-----------------------------------------
$project 挑選字段：
-----------------------------------------
db.product.aggregate([
    {
      $lookup:
        {
          from: "orders",
          localField: "_id",
          foreignField: "pid",
          as: "inventory_docs"
        }
   },
   { $match : { price : {$gt:} } },
   {$project: { "inventory_docs": ,"_id": } }

])
-----------------------------------------
           

mongodb.jdbc配置：

mongo.host=.xxx.xxx
mongo.port=
mongo.defaultDbName=mylearndb
#mongo.user=joyven
#mongo.pwd=123456
mongo.connectionsPerHost=
mongo.threadsAllowedToBlockForConnectionMultiplier=
mongo.minConnectionsPerHost=
#\u8FDE\u63A5\u8D85\u65F6\u65F6\u95F4
mongo.connectTimeout=
#\u7B49\u5F85\u65F6\u95F4
mongo.maxWaitTime=
#Socket\u8D85\u65F6\u65F6\u95F4
mongo.socketTimeout=
mongo.socketKeepAlive=true
mongo.description=joyven test mongodb database
mongo.maxConnectionIdleTime=
mongo.maxConnectionLifeTime=
#mongo slave
mongo.heartbeatSocketTimeout=
mongo.heartbeatConnectTimeout=
mongo.minHeartbeatFrequency=
mongo.heartbeatFrequency=