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=