mongodb 学习笔记及常用命令

曾经终败给现在 2022-08-07 05:52 192阅读 0赞

**windows****下安装**

**1、下载**

地址：http://www.mongodb.org/downloads（32位还是64位自行选择）。

我下载的是：mongodb-win32-x86\_64-2.4.5.zip

**2、解压**

把mongodb-win32-x86\_64-2.4.5.zip放到某个位置，解压。我放的是D:\\dev目录。

解压后的路径为D:\\dev\\mongodb-win32-x86\_64-2.4.5

**3. 安装准备**

MongoDB默认的数据目录为：C:\\data\\db。如果不用默认目录，则需要在在mongod.exe命令后加--dbpath参数。

创建数据目录。我创建的是D:\\dev\\mongodb-win32-x86\_64-2.4.5\\data\\db

创建日志目录及其文件。我创建的是D:\\dev\\mongodb-win32-x86\_64-2.4.5\\log及D:\\dev\\mongodb-win32-x86\_64-2.4.5\\log\\log.txt。

**4、启动MongoDB**

打开cmd窗口(cmd.exe)，进入D:\\dev\\mongodb-win32-x86\_64-2.4.5\\bin，执行mongod.exe命令，见下图。

mongod.exe--logpath=D:\\dev\\mongodb-win32-x86\_64-2.4.5\\log\\log.txt--dbpath=D:\\dev\\mongodb-win32-x86\_64-2.4.5\\data\\db

![Image 1][]

\--logpath参数是设定日志文件的路径。

\--dbpath参数是设定数据库文件的存放路径。

mongod.exe命令的所有参数选项可通过mongod.exe--help查看。

5、作为服务进行安装

使用管理员权限打开windows的cmd窗口，进入D:\\dev\\mongodb-win32-x86\_64-2.4.5\\bin目录。

mongod.exe --install--logpath=D:\\dev\\mongodb-win32-x86\_64-2.4.5\\log\\log.txt--dbpath=D:\\dev\\mongodb-win32-x86\_64-2.4.5\\data\\db

\--install 参数是设定安装为服务器

![Image 1][]

设置为服务后，即可在cmd(管理员权限打开的windows cmd窗口)窗口用服务的方式启动或停止MongoDB。

net start mongodb启动mongodb服务

net stop mongodb启动mongodb服务

6、进入shell环境界面

进入sheelMongoDB后，在cmd窗口进入D:\\dev\\mongodb-win32-x86\_64-2.4.5\\bin目录，输入mongo.exe，则可以进入shell环境界面。

![Image 1][]

**mongodb常用命令**

查询数据库列表

>showdbs

创建数据库(如果存在则改变数据库，否则创建并改变数据库)

>usemydb

显示当前数据库

>db

帮助信息

>help

创建两个文档

>j = \{ name :"mongo" \}  
>k = \{ x : 3 \}

插入两个文档到testData集合中

>db.testData.insert(j )

>db.testData.insert(k )

显示集合

>showcollections

查找指定集合数据

>db.testData.find()

可以定义查询结果变量，显示查询结果

>var c =db.testData.find()  
>while ( c.hasNext() ) printjson( c.next())

查询某个记录

>var c =db.testData.find()

>printjson( c\[ 1 \] )

查询指定集合的指定数据

>db.testData.find(\{ x : 3 \} )

返回一个文档

>db.testData.findOne()

查询制定文档书

>db.testData.find().limit(2)

删除集合

>db.testData()

插叙测试数据通过循环

>for(vari=0;i<25;i++) \{db.testData.insert(\{x:i\})\}

可以插入测试数据通过函数

查询条件，限制条数，投影操作，为find的第二个参数，第一个参数为查询条件

>db.users.find(\{age:\{$gt:18\}\},\{name:1,address:1\}).limit(3)

排序函数sort()

>db.users.find(\{age:\{$gt:18\}\},\{name:1,address:1\}).sort(\{age:1\})

创建索引

>db.inventory.ensureIndex(\{ name: 1 \} )

保证操作原子性的函数

$isolated()

批量操作bulk()

>var bulk =db.items.initializeUnorderedBulkOp();  \#\#无序

>var bulk =db.items.initializeOrderedBulkOp();  \#\#有序

>bulk.insert(\{ \_id: 1, item: "abc123", status: "A", soldQty: 5000 \});  
>bulk.insert( \{ \_id: 2, item:"abc456", status: "A", soldQty: 150 \} );  
>bulk.insert( \{ \_id: 3, item:"abc789", status: "P", soldQty: 0 \} );  
>bulk.execute( \{ w: "majority",wtimeout: 5000 \} );

查询操作

in的使用

>db.inventory.find(\{ type: \{ $in: \[ 'food', 'snacks' \] \} \} )

or的使用

>db.inventory.find(\{$or: \[ \{ qty: \{ $gt: 100 \} \}, \{ price: \{ $lt: 9.95 \} \} \] \})

and和or组合用法(类似sql语句(type='food' and (qty>100 or price<9.95)))

>db.inventory.find(\{type:'food', $or: \[ \{ qty: \{ $gt: 100 \} \}, \{ price: \{ $lt: 9.95 \} \} \]\})

嵌套文档查询方法

db.inventory.find(  
    \{  
      producer:  
        \{  
          company: 'ABC123',  
          address: '123Street'  
        \}  
    \}  
)

或者

>db.inventory.find(\{ 'producer.company': 'ABC123' \} )

文档包含属组，如inventory集合内容如下

\{ \_id: 5, type:"food", item: "aaa", ratings: \[ 5, 8, 9 \] \}  
\{ \_id: 6, type: "food", item: "bbb", ratings: \[ 5, 9 \]\}  
\{ \_id: 7, type: "food", item: "ccc", ratings: \[ 9, 5, 8 \] \}

匹配整个数组

>db.inventory.find(\{ ratings: \[ 5, 8, 9 \] \} )

数组包含某个值

>db.inventory.find(\{ ratings: 5 \} )

查询数组某个位置的值的查询

>db.inventory.find(\{ 'ratings.0': 5 \} )

查询至少一个元素包含在下面的多个条件中(多个条件用$elemMatch)，范围(5,9)

>db.inventory.find(\{ ratings: \{ $elemMatch: \{ $gt: 5, $lt: 9 \} \} \} )

>查询只要满足有元素大于5，并且有元素小于9即可(\[ 5, 9 \])

>db.inventory.find(\{ ratings: \{ $gt: 5, $lt: 9 \} \} )

假设inventory（包括数组，及嵌套文档）集合内容如下

\_id: 100, type: "food", item: "xyz", qty: 25, price: 2.5,

ratings: \[ 5, 8, 9 \],

memos: \[ \{ memo: "on time", by:"shipping" \}, \{ memo: "approved", by: "billing" \}\]

\_id: 101, type: "fruit", item:"jkl", qty: 10, price: 4.25,

ratings: \[ 5, 9 \],

memos: \[ \{ memo: "on time", by:"payment" \}, \{ memo: "delayed", by: "shipping" \}\]

使用嵌套文档的索引查询集合

>db.inventory.find( \{ 'memos.0.by': 'shipping' \} )

通过制定名字查找，嵌套文档中至少一个文档包含就符合记录

>db.inventory.find( \{ 'memos.by': 'shipping' \} )

查询对于嵌套文档中的其中一个文档必同时满足下面的条件

>db.inventory.find(

memos:

$elemMatch:

memo: 'on time',

by: 'shipping'

)

查询满足嵌套文档中的所有文档满足下面条件即可

>db.inventory.find(

'memos.memo': 'on time',

'memos.by': 'shipping'

)

更新操作

根据条件更新集合($currentDate更新指定域用当前时间)

>db.inventory.update(

\{ item: "MNO2" \},

$set: \{

category: "apparel",

details: \{ model: "14Q3",manufacturer: "XYZ Company" \}

\},

$currentDate: \{ lastModified: true \}

)

更新单个嵌套文档

>db.inventory.update(

\{ item: "ABC1" \},

\{ $set: \{ "details.model":"14Q2" \} \}

)

更新多个文档(指定multi: true)

>db.inventory.update(

\{ category: "clothing" \},

$set: \{ category: "apparel" \},

$currentDate: \{ lastModified: true \}

\},

\{ multi: true \}

)

更新文档如果存在，否则插入一个文档(指定upsert: true)

>db.inventory.update(

\{ item: "TBD1" \},

item: "TBD1",

details: \{ "model" :"14Q4", "manufacturer" : "ABC Company" \},

stock: \[ \{ "size" :"S", "qty" : 25 \} \],

category: "houseware"

\},

\{ upsert: true \}

)

删除操作

删除指定集合的所有文档

>db.inventory.remove(\{\})

删除指定条件的文档

>db.inventory.remove(\{ type : "food" \} )

删除当个文档(指定remove()第二个参数为1)

>db.inventory.remove( \{ type : "food" \}, 1 )

指定返回的查询域

返回制定域的集合

>db.inventory.find( \{ type: 'food' \}, \{ item: 1, qty: 1, \_id:0 \} )

返回除了type的所有域

>db.inventory.find( \{ type: 'food' \}, \{ type:0 \} )

假设inventory（包括数组）集合内容如下

\{ "\_id" :5, "type" : "food", "item" : "aaa","ratings" : \[ 5, 8, 9 \] \}

返回指定数组的前两个元素

>db.inventory.find( \{ \_id: 5 \}, \{ ratings: \{ $slice: 2 \} \} )

分析执行计划

通过explain()

>db.inventory.find(\{ quantity: \{ $gte: 100, $lte: 200 \} \} ).explain()

创建索引

>db.inventory.ensureIndex( \{ quantity: 1 \} )

>db.inventory.ensureIndex( \{ quantity: 1, type: 1 \} )

>db.inventory.ensureIndex( \{ type: 1, quantity: 1 \} )

使用hint

>db.inventory.find(\{ quantity: \{ $gte: 100, $lte: 300 \}, type: "food" \} ).hint(\{quantity: 1, type: 1 \}).explain()

>db.inventory.find( \{ quantity: \{ $gte: 100, $lte: 300 \}, type:"food" \} ).hint(\{ type: 1, quantity: 1 \}).explain()

two-phase commit 两段式提交

两个集合

db.accounts.insert(

\{ \_id: "A", balance: 1000,pendingTransactions: \[\] \},

\{ \_id: "B", balance: 1000,pendingTransactions: \[\] \}

)

db.transactions.insert(

\{ \_id: 1, source: "A",destination: "B", value: 100, state: "initial",lastModified: new Date() \}

)

转账从A到B转100块，插入transactions集合中一个文档，存储转账信息，用于回滚

更新交易状态，在进行中

var t =db.transactions.findOne( \{ state: "initial" \} )

db.transactions.update(

\{ \_id: t.\_id, state: "initial" \},

$set: \{ state: "pending" \},

$currentDate: \{ lastModified: true \}

)

更新账户A

db.accounts.update(

\{ \_id: t.source, pendingTransactions: \{ $ne:t.\_id \} \},

\{ $inc: \{ balance: -t.value \}, $push: \{pendingTransactions: t.\_id \} \}

)

更新账户B

db.accounts.update(

\{ \_id: t.destination, pendingTransactions: \{$ne: t.\_id \} \},

\{ $inc: \{ balance: t.value \}, $push: \{pendingTransactions: t.\_id \} \}

)

更新事务状态，已经应用

db.transactions.update(

\{ \_id: t.\_id, state: "pending" \},

$set: \{ state: "applied" \},

$currentDate: \{ lastModified: true \}

)

删除账户的事务\_id

db.accounts.update(

\{ \_id: t.source, pendingTransactions: t.\_id\},

\{ $pull: \{ pendingTransactions: t.\_id \} \}

)

db.accounts.update(

\{ \_id: t.destination, pendingTransactions:t.\_id \},

\{ $pull: \{ pendingTransactions: t.\_id \} \}

)

更新事务状态为done

db.transactions.update(

\{ \_id: t.\_id, state: "applied" \},

$set: \{ state: "done" \},

$currentDate: \{ lastModified: true \}

)

从事务失败状态恢复

事务在pending状态

var dateThreshold =new Date();

dateThreshold.setMinutes(dateThreshold.getMinutes()- 30);

var t =db.transactions.findOne( \{ state: "pending", lastModified: \{ $lt:dateThreshold \} \} );

1)更新事务状态取消了

db.transactions.update(

\{ \_id: t.\_id, state: "pending" \},

$set: \{ state: "canceling" \},

$currentDate: \{ lastModified: true \}

)

2)撤销两个账户的事务

db.accounts.update(

\{ \_id: t.destination, pendingTransactions:t.\_id \},

$inc: \{ balance: -t.value \},

$pull: \{ pendingTransactions: t.\_id \}

)

db.accounts.update(

\{ \_id: t.source, pendingTransactions: t.\_id\},

$inc: \{ balance: t.value\},

$pull: \{ pendingTransactions: t.\_id \}

)

3)更新事务状态为关闭

db.transactions.update(

\{ \_id: t.\_id, state: "canceling"\},

$set: \{ state: "cancelled" \},

$currentDate: \{ lastModified: true \}

)

事务在applied状态

var dateThreshold =new Date();

dateThreshold.setMinutes(dateThreshold.getMinutes()- 30);

var t =db.transactions.findOne( \{ state: "applied", lastModified: \{ $lt:dateThreshold \}

不应该回滚事务而应该创建一个新事物，源和目标对调

多个应用的情况

t =db.transactions.findAndModify(

query: \{ state: "initial",application: \{ $exists: false \} \},

update:

$set: \{ state:"pending", application: "App1" \},

$currentDate: \{ lastModified: true\}

\},

new: true

)

var dateThreshold =new Date();

dateThreshold.setMinutes(dateThreshold.getMinutes()- 30);

db.transactions.find(

application: "App1",

state: "pending",

lastModified: \{ $lt: dateThreshold \}

)

创建自增主键的两个方法

1.使用序列

1）创建结合

db.counters.insert(

\_id: "userid",

seq: 0

)

2）创建返回下一个序列的函数

functiongetNextSequence(name) \{

var ret = db.counters.findAndModify(

query: \{ \_id: name \},

update: \{ $inc: \{ seq: 1 \} \},

new: true

);

return ret.seq;

3）使用序列

db.users.insert(

\_id: getNextSequence("userid"),

)

db.users.insert(

\_id: getNextSequence("userid"),

)

2.Optimistic Loop

创建一个函数

functioninsertDocument(doc, targetCollection) \{

while (1) \{

var cursor = targetCollection.find( \{\},\{ \_id: 1 \} ).sort( \{ \_id: -1 \} ).limit(1);

var seq = cursor.hasNext() ?cursor.next().\_id + 1 : 1;

doc.\_id = seq;

var results =targetCollection.insert(doc);

if( results.hasWriteError() ) \{

if( results.writeError.code ==11000 /\* dup key \*/ )

continue;

else

print( "unexpected errorinserting data: " + tojson( results ) );

break;

插入数据

var myCollection =db.users2;

insertDocument(

\},

myCollection

);

insertDocument(

\},

myCollection

)

聚合操作

求和count()

>db.records.count()

>db.records.count( \{ a: 1 \} )

去重操作distinct()

>db.records.distinct( "b" )

分组操作group()

>db.records.group( \{

key: \{ a: 1 \},

cond: \{ a: \{ $lt: 3 \} \},

reduce: function(cur, result) \{ result.count+= cur.count \},

initial: \{ count: 0 \}

\} )

使用聚合函数aggregate()，求和功能

db.zipcodes.aggregate(\{ $group :

\{ \_id :"$state",

totalPop : \{ $sum :"$pop" \} \} \},

\{ $match : \{totalPop : \{$gte : 10\*1000\*1000 \} \} \} )

类似一下sql语句

SELECT state,SUM(pop) AS totalPop

FROM zipcodes

GROUP BY state

HAVING totalPop >= (10\*1000\*1000)

求平均值

db.zipcodes.aggregate(\[

\{ $group : \{ \_id : \{ state :"$state", city : "$city" \}, pop : \{ $sum : "$pop"\} \} \},

\{ $group : \{ \_id : "$\_id.state",avgCityPop : \{ $avg : "$pop" \} \} \}

\] )

求最大值最小值

db.zipcodes.aggregate(\{ $group:

\{ \_id: \{ state:"$state", city: "$city" \},

pop: \{ $sum:"$pop" \} \} \},

\{ $sort: \{ pop: 1 \} \},

\{ $group:

\{ \_id :"$\_id.state",

biggestCity:  \{ $last: "$\_id.city" \},

biggestPop:   \{ $last: "$pop" \},

smallestCity: \{$first: "$\_id.city" \},

smallestPop:  \{ $first: "$pop" \} \} \},

// the following$project is optional, and

// modifies the outputformat.

\{ $project:

\{ \_id: 0,

state:"$\_id",

biggestCity:  \{ name: "$biggestCity",  pop: "$biggestPop" \},

smallestCity: \{name: "$smallestCity", pop: "$smallestPop" \} \} \} )

重命名字段在排序

db.users.aggregate(

\{ $project : \{name:\{$toUpper:"$\_id"\} , \_id:0 \} \},

\{ $sort : \{ name : 1 \} \}

)

db.users.aggregate(

\{ $project :

month\_joined : \{ $month :"$joined" \},

name : "$\_id",

\_id : 0

\},

\{ $sort : \{ month\_joined : 1 \} \}

)

返回前5个

db.users.aggregate(

\{ $unwind : "$likes" \},

\{ $group : \{ \_id : "$likes" ,number : \{ $sum : 1 \} \} \},

\{ $sort : \{ number : -1 \} \},

\{ $limit : 5 \}

)

mapReduce()，方式求聚合值

例子文档orders

\_id:ObjectId("50a8240b927d5d8b5891743c"),

cust\_id: "abc123",

ord\_date: new Date("Oct 04,2012"),

status: 'A',

price: 25,

items: \[ \{ sku: "mmm", qty: 5,price: 2.5 \},

\{ sku: "nnn", qty: 5,price: 2.5 \} \]

以cust\_id分组，对price求和

map函数

var mapFunction1 =function() \{

emit(this.cust\_id,this.price);

\};

reduce函数

var reduceFunction1= function(keyCustId, valuesPrices) \{

returnArray.sum(valuesPrices);

\};

运行：

db.orders.mapReduce(

mapFunction1,

reduceFunction1,

\{ out:"map\_reduce\_example" \}

)

聚合命令与sql对照

例子文档

cust\_id: "abc123",

ord\_date:ISODate("2012-11-02T17:04:11.102Z"),

status: 'A',

price: 50,

items: \[ \{ sku: "xxx", qty: 25,price: 1 \},

\{ sku: "yyy", qty: 25,price: 1 \} \]

<table> 
 <tbody> 
 <tr> 
 <td> SQL Example </td> 
 <td> MongoDB Example </td> 
 <td> Description </td> 
 </tr> 
 <tr> 
 <td> SELECT COUNT(*) AS count FROM orders </td> 
 <td> db.orders.aggregate( [ &nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp; $group: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; _id: null, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; count: { $sum: 1 } &nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; } ] ) </td> 
 <td> Count all records from orders </td> 
 </tr> 
 <tr> 
 <td> SELECT SUM(price) AS total FROM orders </td> 
 <td> db.orders.aggregate( [ &nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp; $group: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; _id: null, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; total: { $sum: "$price" } &nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; } ] ) </td> 
 <td> Sum the price field from orders </td> 
 </tr> 
 <tr> 
 <td> SELECT cust_id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(price) AS total FROM orders GROUP BY cust_id </td> 
 <td> db.orders.aggregate( [ &nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp; $group: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; _id: "$cust_id", &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; total: { $sum: "$price" } &nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; } ] ) </td> 
 <td> For each unique cust_id, sum the price field. </td> 
 </tr> 
 <tr> 
 <td> SELECT cust_id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(price) AS total FROM orders GROUP BY cust_id ORDER BY total </td> 
 <td> db.orders.aggregate( [ &nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp; $group: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; _id: "$cust_id", &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; total: { $sum: "$price" } &nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; }, &nbsp;&nbsp; { $sort: { total: 1 } } ] ) </td> 
 <td> For each unique cust_id, sum the price field, results sorted by sum. </td> 
 </tr> 
 <tr> 
 <td> SELECT cust_id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ord_date, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(price) AS total FROM orders GROUP BY cust_id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ord_date </td> 
 <td> db.orders.aggregate( [ &nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp; $group: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; _id: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cust_id: "$cust_id", &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ord_date: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; month: { $month: "$ord_date" }, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; day: { $dayOfMonth: "$ord_date" }, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; year: { $year: "$ord_date"} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; total: { $sum: "$price" } &nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; } ] ) </td> 
 <td> For each unique cust_id, ord_date grouping, sum the price field. Excludes the time portion of the date. </td> 
 </tr> 
 <tr> 
 <td> SELECT cust_id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; count(*) FROM orders GROUP BY cust_id HAVING count(*) &gt; 1 </td> 
 <td> db.orders.aggregate( [ &nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp; $group: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; _id: "$cust_id", &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; count: { $sum: 1 } &nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; }, &nbsp;&nbsp; { $match: { count: { $gt: 1 } } } ] ) </td> 
 <td> For cust_id with multiple records, return the cust_id and the corresponding record count. </td> 
 </tr> 
 <tr> 
 <td> SELECT cust_id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ord_date, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(price) AS total FROM orders GROUP BY cust_id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ord_date HAVING total &gt; 250 </td> 
 <td> db.orders.aggregate( [ &nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp; $group: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; _id: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cust_id: "$cust_id", &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ord_date: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; month: { $month: "$ord_date" }, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; day: { $dayOfMonth: "$ord_date" }, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; year: { $year: "$ord_date"} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; total: { $sum: "$price" } &nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; }, &nbsp;&nbsp; { $match: { total: { $gt: 250 } } } ] ) </td> 
 <td> For each unique cust_id, ord_date grouping, sum the price field and return only where the sum is greater than 250. Excludes the time portion of the date. </td> 
 </tr> 
 <tr> 
 <td> SELECT cust_id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(price) as total FROM orders WHERE status = 'A' GROUP BY cust_id </td> 
 <td> db.orders.aggregate( [ &nbsp;&nbsp; { $match: { status: 'A' } }, &nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp; $group: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; _id: "$cust_id", &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; total: { $sum: "$price" } &nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; } ] ) </td> 
 <td> For each unique cust_id with status A, sum the price field. </td> 
 </tr> 
 <tr> 
 <td> SELECT cust_id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(price) as total FROM orders WHERE status = 'A' GROUP BY cust_id HAVING total &gt; 250 </td> 
 <td> db.orders.aggregate( [ &nbsp;&nbsp; { $match: { status: 'A' } }, &nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp; $group: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; _id: "$cust_id", &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; total: { $sum: "$price" } &nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; }, &nbsp;&nbsp; { $match: { total: { $gt: 250 } } } ] ) </td> 
 <td> For each unique cust_id with status A, sum the price field and return only where the sum is greater than 250. </td> 
 </tr> 
 <tr> 
 <td> SELECT cust_id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(li.qty) as qty FROM orders o, &nbsp;&nbsp;&nbsp;&nbsp; order_lineitem li WHERE li.order_id = o.id GROUP BY cust_id </td> 
 <td> db.orders.aggregate( [ &nbsp;&nbsp; { $unwind: "$items" }, &nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp; $group: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; _id: "$cust_id", &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; qty: { $sum: "$items.qty" } &nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; } ] ) </td> 
 <td> For each unique cust_id, sum the corresponding line item qty fields associated with the orders. </td> 
 </tr> 
 <tr> 
 <td> SELECT COUNT(*) FROM (SELECT cust_id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ord_date &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM orders &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; GROUP BY cust_id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ord_date) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as DerivedTable </td> 
 <td> db.orders.aggregate( [ &nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp; $group: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; _id: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cust_id: "$cust_id", &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ord_date: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; month: { $month: "$ord_date" }, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; day: { $dayOfMonth: "$ord_date" }, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; year: { $year: "$ord_date"} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; }, &nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp; $group: { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; _id: null, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; count: { $sum: 1 } &nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; } ] ) </td> 
 <td> Count the number of distinct cust_id, ord_date groupings. Excludes the time portion of the date. </td> 
 </tr> 
 </tbody> 
</table>

Spring mongodb

使用mongodb语法查询数据

BasicQuery query =new BasicQuery("\{ age : \{ $lt : 50 \}, accounts.balance : \{ $gt : 1000.00\}\}"); 
List<Person> result = mongoTemplate.find(query, Person.class);

查询数据

import staticorg.springframework.data.mongodb.core.query.Criteria.where; 
import staticorg.springframework.data.mongodb.core.query.Query.query; 
List<Person> result =mongoTemplate.find(query(where("age").lt(50) 
 .and("accounts.balance").gt(1000.00d)), Person.class);

[Image 1]: