1. 数据库
- 选中数据库,不存在则创建
use mydb;
- 删除数据库
db.dropDatabase();
- 查看当前数据库
db;
- 查看全部数据库
show dbs;
- 查看当前数据库下全部集合
show collections;
- 查看版本
db.version();
2. 集合
- 创建集合
db.createCollection("mycol2");
- 删除集合
db.myco2.drop();
- 删除集合下全部文档
db.mycol2.remove();
- 查询集合下全部文档
db.mycol2.find();
3. 索引
- 创建索引(1 升序 -1 降序)(name 索引名称,unique 唯一,background 后台创建)
db.mycol2.createIndex({
"name": 1,
"age": - 1
}, {
"name": "uk_name_age",
"unique": 1,
"background": true
});
- 删除全部索引
db.mycol2.dropIndexes()
- 删除指定索引
db.mycol2.dropIndex("uk_name_age")
- 查看全部索引
db.mycol2.getIndexes()
4. 文档
4.1 添加(如果集合不存在则创建集合)
insert 已不再建议使用,使用 insertOne 或 insertMany
区别是:https://stackoverflow.com/questions/36792649/whats-the-difference-between-insert-insertone-and-insertmany-method
insert 单个插入返回 WriteResult 对象,批量插入返回一个 BulkWriteResult 对象,insertOne 和 insertMany 返回插入对象的 ObjectId
insert 命令在成功和错误情况下都返回一个文档,insertOne 和 insertMany 命令会引发异常
- 添加单条文档
db.mycol2.insertOne({
"name": "chen",
"age": 18,
"email": "chen@163.com"
});
- 添加多条文档
db.mycol2.insertMany([
{
"name": "张三",
"age": 18
},
{
"name": "李四",
"age": 20
}
]);
4.2 删除
- 指定条件删除文档(remove,deleteOne,deleteMany)
db.mycol2.deleteMany({
"name": "张三"
});
4.3 查询
- in,and,or使用
db.mycol2.find(
{
"isDel": 0,
"role": {
$in: ['管理员1号', '管理员2号']
},
"$or": [
{
"name": "陈1"
},
{
"age": {
"$gte": 20
}
}
]
}
)
- 比较(gt >,lt <,gte >=,lte <=,ne !=)
db.mycol2.find({
"age": {
"$gt": 15,
"$lt": 30,
"$ne": 20
}
})
- limit 和 skip 使用
db.mycol2.find().limit(2).skip(1);
- 排序(1 升序,-1 降序)
db.mycol2.find().sort({
"age": - 1
})
- 统计数量
db.mycol2.find().count()
- 正则
db.mycol2.find({
"name": /陈/
});
- 模糊查询(左、右、全)
db.mycol2.find({
"name": {
"$regex": "^chen.*$"
}
})
db.mycol2.find({
"name": {
"$regex": "^.*kaixin$"
}
})
db.mycol2.find({
"name": {
"$regex": "^.*chenkaixin.*$"
}
})
- 查询单个字段并去重
db.mycol2.distinct("name");
- 时间
db.mycol2.find({
"createTime": {
$gt: new Date("2020-02-03T06:24:40.110Z"),
$lt: new Date("2020-02-05T06:14:40.089Z")
}
});
- 指定字段(1 查询,0 排除)
db.mycol2.find({}, {
"name": 1,
"_id": 0
});
4.4 更新
- 带_id是添加,不带_id则更新
db.mycol2.save({
"_id": ObjectId("613a1c32864100008c006de9"),
"name": "麻子",
"age": 3
});
- 根据条件更新一条
db.mycol2.updateOne(
{
"age": 18
},
{
$set: {
"name": "18真好"
}
}
);
- 更新多条
db.mycol2.updateMany({
"age": 30
}, {
$set: {
"name": '30真好啊222',
"age": 30
}
}, {
upsert: true
});
4.5 嵌套字段
db.inventory.insertMany( [
{ item: "journal", qty: 25, size: { h: 14, w: 21, uom: "cm" }, status: "A" },
{ item: "notebook", qty: 50, size: { h: 8.5, w: 11, uom: "in" }, status: "A" },
{ item: "paper", qty: 100, size: { h: 8.5, w: 11, uom: "in" }, status: "D" },
{ item: "planner", qty: 75, size: { h: 22.85, w: 30, uom: "cm" }, status: "D" },
{ item: "postcard", qty: 45, size: { h: 10, w: 15.25, uom: "cm" }, status: "A" }
]);
- 精确匹配
db.inventory.find({
size: {
h: 14,
w: 21,
uom: "cm"
}
})
- 相等匹配
db.inventory.find({
"size.uom": "in"
})
- 指定匹配
db.inventory.find({
"size.h": {
$lt: 15
},
"size.uom": "in",
status: "D"
})
4.6 数组
db.inventory.insertMany([
{ item: "journal", qty: 25, tags: ["blank", "red"], dim_cm: [ 14, 21 ] },
{ item: "notebook", qty: 50, tags: ["red", "blank"], dim_cm: [ 14, 21 ] },
{ item: "paper", qty: 100, tags: ["red", "blank", "plain"], dim_cm: [ 14, 21 ] },
{ item: "planner", qty: 75, tags: ["blank", "red"], dim_cm: [ 22.85, 30 ] },
{ item: "postcard", qty: 45, tags: ["blue"], dim_cm: [ 10, 15.25 ] }
]);
- 指定顺序和大小查询
db.inventory.find({
tags: ["red", "blank"]
})
- 不指定
db.inventory.find({
tags: {
$all: ["red", "blank"]
}
})
- 包含指定元素
db.inventory.find({
tags: "red"
});
- 组合满足条件
db.inventory.find({
dim_cm: {
$gt: 15,
$lt: 20
}
});
- 满足全部条件
db.inventory.find({
dim_cm: {
$elemMatch: {
$gt: 22,
$lt: 30
}
}
});
- 使用长度查询
db.inventory.find({
"tags": {
$size: 3
}
});
4.7 数组文档
db.inventory.insertMany( [
{ item: "journal", instock: [ { warehouse: "A", qty: 5 }, { warehouse: "C", qty: 15 } ] },
{ item: "notebook", instock: [ { warehouse: "C", qty: 5 } ] },
{ item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 15 } ] },
{ item: "planner", instock: [ { warehouse: "A", qty: 40 }, { warehouse: "B", qty: 5 } ] },
{ item: "postcard", instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35 } ] }
]);
- 对字段进行查询
db.inventory.find({
'instock.qty': {
$lte: 20
}
})
- 使用数组的索引查询
db.inventory.find({
'instock.0.qty': {
$lte: 20
}
})
- 组合满足条件
db.inventory.find({
"instock.qty": {
$gt: 10,
$lte: 20
}
})
- 满足全部条件
db.inventory.find({
"instock": {
$elemMatch: {
qty: {
$gt: 10,
$lte: 20
}
}
}
})
5. 聚合
5.1 常用操作
sql | mongo |
---|---|
select | $project |
where | $match |
join | $lookup |
group by | $group |
having | $match |
order by | $sort |
limit | $limit |
sum() | $sum |
count() | $sum |
age() | $age |
min() | $min |
max() | $max |
5.2 示例
db.mycol2.aggregate([
{
$group: {
_id: "$name",
user_count: {
$sum: 1
}
}
},
{
$match: {
"user_count": {
$gt: 10
}
}
},
{
$skip: 3
},
{
$limit: 10
},
{
$sort: {
user_count: - 1
}
}
])
db.mycol2.aggregate([
{
$match: {
isDel: 0
}
},
{
$group: {
_id: "$name",
age_sum: {
$sum: "$age"
}
}
}
])
Q.E.D.