[Logo]
[开发公司]MongoDB .Inc(前身为10gen)
[开发语言] c++
[OS] Linux,Mac,Linux,Solaris
[名字由来] “Humongous” (海量) amount of data
MongoDB特性
面向文档
动态查询
文件存储格式为BSON(一种JSON的扩展)
自由模式
复制及自动故障转移(replication)
水平扩展(sharding)
MapReduce
高效的传统存储方式(GridFS):支持二进制数据及大型对象(如照片或图片)
支持Python,PHP,Ruby,Java,C,C#,Javascript,Perl及C++语言的驱动程序,社区中也提供了对Erlang 及.NET等平台的驱动程序
MongoDB常用语法
查询
MySQL:
SELECT * FROM user
Mongo:
db.user.find()
MySQL:
SELECT * FROM user WHERE name = ‘aylen' ORDER BY name DESC LIMIT 10 OFFSET 10
Mongo:
db.user.find({‘name’ : ‘aylen’}).sort({‘name’: -1}).skip(10).limit(10)
插入
MySQL:
INSERT INOT user (`name`, `age`) values (‘aylen',10)
Mongo:
db.user.insert({'name' : ‘aylen', 'age' : 10})
删除:
MySQL:
DELETE * FROM user
Mongo:
db.user.remove({})
db.user.drop()
MySQL:
DELETE FROM user WHERE age < 30
Mongo:
db.user.remove({'age' : {$lt : 30}})
说明:
$eq:=;$gt : > ;$gte : >= ;$lt : < ;$lte : <= ;$ne : !=;$in:in;$nin:not in
更新:
MySQL:
UPDATE user SET `age` = 20 WHERE `name` = ‘aylen'
Mongo:
db.user.update({'name' : ‘aylen'}, {$set : {'age' : 20}})
MySQL:
UPDATE user SET `age` = `age` + 3 WHERE `name` = ‘aylen'
Mongo:
db.user.update({'name' : ‘aylen'}, {$inc : {'age' : 3}})
SQL的例子中涉及orders和order_lineitem这两张表,通过order_lineitem.order_id和 orders.id关联
MongoDB的例子是关于orders的,其结构如下:
{
cust_id: "abc123",
ord_date: ISODate("2016-08-21T17:04:11.102Z"),
status: 'A',
price: 50,
items: [ { sku: "xxx", qty: 25, price: 1 },
{ sku: "yyy", qty: 25, price: 1 } ]
}
计算订单数
SQL语法:
SELECT COUNT(*) AS count FROM orders
MongoDB语法:
db.orders.aggregate( [
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] )
计算订单中的总金额
SQL语法:
SELECT SUM(price) AS total FROM orders
MongoDB语法:
db.orders.aggregate( [
{
$group: {
_id: null,
total: { $sum: "$price" }
}
}
] )
计算每个用户的总金额
SQL语法:
SELECT cust_id, SUM(price) AS total
FROM orders
GROUP BY cust_id
MongoDB语法:
db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
}
] )
计算每个用户的总价并排序
SQL语法:
SELECT cust_id,SUM(price) AS total
FROM orders
GROUP BY cust_id
ORDER BY total
MongoDB语法:
db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
},
{ $sort: { total: 1 } }
] )
计算每个用户的每天的总金额
假设日期中不涉及时间
SQL语法:
SELECT cust_id, ord_date, SUM(price) AS total
FROM orders
GROUP BY cust_id,
ord_date
MongoDB语法:
db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: {
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
},
total: { $sum: "$price" }
}
}
] )
统计下单多于一个的用户
SQL语法:
SELECT cust_id,count(*)
FROM orders
GROUP BY cust_id
HAVING count(*) > 1
MongoDB语法:
db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
count: { $sum: 1 }
}
},
{ $match: { count: { $gt: 1 } } }
] )
统计每天消费金额超过250的用户
SQL语法:
SELECT cust_id,
ord_date,
SUM(price) AS total
FROM orders
GROUP BY cust_id,
ord_date
HAVING total > 250
MongoDB语法:
db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: {
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
},
total: { $sum: "$price" }
}
},
{ $match: { total: { $gt: 250 } } }
] )
统计用户状态为A且每天消费超过250
SQL语法:
SELECT cust_id,
SUM(price) as total
FROM orders
WHERE status = 'A'
GROUP BY cust_id
HAVING total > 250
MongoDB语法:
db.orders.aggregate( [
{ $match: { status: 'A' } },
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
},
{ $match: { total: { $gt: 250 } } }
] )