Mongodb 基础 查询表达式

时间:2022-09-09 21:06:59

数据库操作

查看:show dbs;

创建:use dbname; // db.createCollection('collection_name');    隐式创建,需要创建的数据库中有表才表示创建成功

删除:use dbname; db.dropDatabase();   // 选择好对应的数据库,然后删除

如下实例:

查看数据库:

> show dbs;
admin 0.000GB
local 0.000GB
test_1 0.000GB
>

 创建数据库:隐式创建,直接use不存在的数据库

> use student;
switched to db student

查看没有:因为没有在这个数据库中创建表,只有创建表后才创建成功

> use student;
switched to db student
> show dbs;
admin 0.000GB
local 0.000GB
test_1 0.000GB

下面表示创建成功:

> use student;
switched to db student
> show dbs;
admin 0.000GB
local 0.000GB
test_1 0.000GB
>
> show collections;
>
> show tables;
> db.createCollection('minstudent');
{ "ok" : 1 }
> show tables;
minstudent
> show dbs;
admin 0.000GB
local 0.000GB
student 0.000GB
test_1 0.000GB

 删除数据库:

> db.dropDatabase();
{ "dropped" : "student", "ok" : 1 }
>

数据文档操作

这里的文档类似与MySQL中的表,因为Mongodb是文档数据库,所以可以说是对文档的操作;

创建:db.createCollection('collection_name');

删除:db.collection_name.drop();

查看:show collections;

如下实例:

创建

> use school;
switched to db school
>
> db.createCollection('student');
{ "ok" : 1 }

 删除

> db.student.drop();
true

 查看

> db.teacher.insert({'name':'teacher1'});
WriteResult({ "nInserted" : 1 })
> show collections;
teacher

文档内容操作

插入数据:db.collection_name.insert({key:value})  单个插入

db.collection_name.insert({_id:value, key:value}) 有_id的插入

db.collection_name.insert([{key:value},{key:value}])   多个插入

查看:db.collection_name.find();    // 这里只例举简单的查询方法,会单独写一篇文档详细说明查询的方法

删除:db.collection_name.remove(查询表达式,选项)

修改:db.collection_name.update(查询表达式,新值,选项)

如下实例:

插入

> db.student.insert({'name': 'wallace'});
WriteResult({ "nInserted" : 1 })
> db.student.find();
{ "_id" : ObjectId("5c810428cdad789abb91899a"), "name" : "wallace" }

 修改_id

> db.student.insert({'name': 'bbbb', '_id':111});
WriteResult({ "nInserted" : 1 })
> db.student.find();
{ "_id" : ObjectId("5c810428cdad789abb91899a"), "name" : "wallace" }
{ "_id" : ObjectId("5c810441cdad789abb91899b"), "name" : "bbbb" }
{ "_id" : 111, "name" : "bbbb" }

 插入多条

> db.student.insert([{'age': 'bbbb', 'age':111, 'home': 'beijin'},{'age':'laowang', 'age': 1, 'home':'江西'}]);
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 2,
"nUpserted" : 0,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [ ]
})
> db.student.find();
{ "_id" : ObjectId("5c810428cdad789abb91899a"), "name" : "wallace" }
{ "_id" : ObjectId("5c810441cdad789abb91899b"), "name" : "bbbb" }
{ "_id" : 111, "name" : "bbbb" }
{ "_id" : ObjectId("5c810567cdad789abb91899c"), "age" : 111, "home" : "beijin" }
{ "_id" : ObjectId("5c810567cdad789abb91899d"), "age" : 1, "home" : "江西"}

删除数据

> db.student.find();
{ "_id" : ObjectId("5c810428cdad789abb91899a"), "name" : "wallace" }
{ "_id" : ObjectId("5c810441cdad789abb91899b"), "name" : "bbbb" }
{ "_id" : 111, "name" : "bbbb" }
{ "_id" : ObjectId("5c810567cdad789abb91899c"), "age" : 111, "home" : "beijin" }
{ "_id" : ObjectId("5c810567cdad789abb91899d"), "age" : 1, "home" : "江西" }
{ "_id" : ObjectId("5c810575cdad789abb91899e"), "age" : 111, "home" : "beijin" }
{ "_id" : ObjectId("5c810575cdad789abb91899f"), "age" : 1, "home" : "江西" }
> db.student.remove({home:'beijin'});
WriteResult({ "nRemoved" : 2 })
> db.student.find();
{ "_id" : ObjectId("5c810428cdad789abb91899a"), "name" : "wallace" }
{ "_id" : ObjectId("5c810441cdad789abb91899b"), "name" : "bbbb" }
{ "_id" : 111, "name" : "bbbb" }
{ "_id" : ObjectId("5c810567cdad789abb91899d"), "age" : 1, "home" : "江西" }
{ "_id" : ObjectId("5c810575cdad789abb91899f"), "age" : 1, "home" : "江西" }
> db.student.remove({home:'江西'}, true);
WriteResult({ "nRemoved" : 1 })
> db.student.find();
{ "_id" : ObjectId("5c810428cdad789abb91899a"), "name" : "wallace" }
{ "_id" : ObjectId("5c810441cdad789abb91899b"), "name" : "bbbb" }
{ "_id" : 111, "name" : "bbbb" }
{ "_id" : ObjectId("5c810575cdad789abb91899f"), "age" : 1, "home" : "江西" }
>

修改

> db.student.find();
{ "_id" : ObjectId("5c810428cdad789abb91899a"), "name" : "wallace" }
{ "_id" : ObjectId("5c810441cdad789abb91899b"), "name" : "bbbb" }
{ "_id" : 111, "name" : "bbbb" }
{ "_id" : ObjectId("5c810575cdad789abb91899f"), "age" : 1, "home" : "江西" }
> db.student.update({home:'江西'},{home:北京});
2019-03-08T09:00:31.981-0500 E QUERY [thread1] ReferenceError: \u5317\u4EAC is not defined :
@(shell):1:32
> db.student.update({home:'江西'},{home:'北京'});
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.student.find();
{ "_id" : ObjectId("5c810428cdad789abb91899a"), "name" : "wallace" }
{ "_id" : ObjectId("5c810441cdad789abb91899b"), "name" : "bbbb" }
{ "_id" : 111, "name" : "bbbb" }
{ "_id" : ObjectId("5c810575cdad789abb91899f"), "home" : "北京" }

修改二:$set 具体到某一个键值对的值

> db.student.insert({'name':'bbb',age:18, classmate:4});
WriteResult({ "nInserted" : 1 })
> db.student.update({name:'bbb'}, {$set:{'age':19}});
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.student.find();
{ "_id" : ObjectId("5c810428cdad789abb91899a"), "name" : "wallace" }
{ "_id" : ObjectId("5c810441cdad789abb91899b"), "name" : "1111" }
{ "_id" : 111, "name" : "bbbb" }
{ "_id" : ObjectId("5c810575cdad789abb91899f"), "home" : "北京" }
{ "_id" : ObjectId("5c835969043334857a7dbe85"), "name" : "aaa", "age" : 18, "classmate" : 3 }
{ "_id" : ObjectId("5c835973043334857a7dbe86"), "name" : "bbb", "age" : 19, "classmate" : 4 }

修改三:$unset 删除某一对象的键值对

> db.student.update({name:'bbb'}, {$unset:{'age':19}});
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.student.find();
{ "_id" : ObjectId("5c810428cdad789abb91899a"), "name" : "wallace" }
{ "_id" : ObjectId("5c810441cdad789abb91899b"), "name" : "1111" }
{ "_id" : 111, "name" : "bbbb" }
{ "_id" : ObjectId("5c810575cdad789abb91899f"), "home" : "北京" }
{ "_id" : ObjectId("5c835969043334857a7dbe85"), "name" : "aaa", "age" : 18, "classmate" : 3 }
{ "_id" : ObjectId("5c835973043334857a7dbe86"), "name" : "bbb", "classmate" : 4 }
>

修改列名

> db.student.update({name:'bbb'}, {$rename:{'name':'sc'}});
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.student.find();
{ "_id" : ObjectId("5c810428cdad789abb91899a"), "name" : "wallace" }
{ "_id" : ObjectId("5c810441cdad789abb91899b"), "name" : "1111" }
{ "_id" : 111, "name" : "bbbb" }
{ "_id" : ObjectId("5c810575cdad789abb91899f"), "home" : "北京" }
{ "_id" : ObjectId("5c835969043334857a7dbe85"), "name" : "aaa", "age" : 18, "classmate" : 3 }
{ "_id" : ObjectId("5c835973043334857a7dbe86"), "classmate" : 4, "sc" : "bbb" }
>

增加值

> db.student.find();
{ "_id" : ObjectId("5c810428cdad789abb91899a"), "name" : "wallace" }
{ "_id" : ObjectId("5c810441cdad789abb91899b"), "name" : "1111" }
{ "_id" : 111, "name" : "bbbb" }
{ "_id" : ObjectId("5c810575cdad789abb91899f"), "home" : "北京" }
{ "_id" : ObjectId("5c835969043334857a7dbe85"), "name" : "aaa", "age" : 37, "classmate" : 3 }
{ "_id" : ObjectId("5c835973043334857a7dbe86"), "classmate" : 4, "sc" : "bbb" }
> db.student.update({name:'aaa'},{$inc:{age:10}});
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.student.find();
{ "_id" : ObjectId("5c810428cdad789abb91899a"), "name" : "wallace" }
{ "_id" : ObjectId("5c810441cdad789abb91899b"), "name" : "1111" }
{ "_id" : 111, "name" : "bbbb" }
{ "_id" : ObjectId("5c810575cdad789abb91899f"), "home" : "北京" }
{ "_id" : ObjectId("5c835969043334857a7dbe85"), "name" : "aaa", "age" : 47, "classmate" : 3 }
{ "_id" : ObjectId("5c835973043334857a7dbe86"), "classmate" : 4, "sc" : "bbb" }
>

条件关键字

mongodb的条件查询与普通的SQL查询不一样,使用特定的符号来代替,下面表格的常用的的条件代替。

操作 格式 范例 RDBMS中的类似语句
等于 {<key>:<value>} db.col.find({"by":"aaa"}).pretty() where by = 'aaa'
小于 {<key>:{$lt:<value>}} db.col.find({"likes":{$lt:50}}).pretty() where likes < 50
小于或等于 {<key>:{$lte:<value>}} db.col.find({"likes":{$lte:50}}).pretty() where likes <= 50
大于 {<key>:{$gt:<value>}} db.col.find({"likes":{$gt:50}}).pretty() where likes > 50
大于或等于 {<key>:{$gte:<value>}} db.col.find({"likes":{$gte:50}}).pretty() where likes >= 50
不等于 {<key>:{$ne:<value>}} db.col.find({"likes":{$ne:50}}).pretty() where likes != 50

条件查询实例

1: 基础查询 where的练习:

查出满足以下条件的商品
1.1:主键为32的商品
db.goods.find({goods_id:32}); 1.2:不属第3栏目的所有商品($ne)
db.goods.find({cat_id:{$ne:3}},{goods_id:1,cat_id:1,goods_name:1}); 1.3:本店价格高于3000元的商品{$gt}
db.goods.find({shop_price:{$gt:3000}},{goods_name:1,shop_price:1}); 1.4:本店价格低于或等于100元的商品($lte)
db.goods.find({shop_price:{$lte:100}},{goods_name:1,shop_price:1}); 1.5:取出第4栏目或第11栏目的商品($in)
db.goods.find({cat_id:{$in:[4,11]}},{goods_name:1,shop_price:1}); 1.6:取出100<=价格<=500的商品($and)
db.goods.find({$and:[{price:{$gt:100},{$price:{$lt:500}}}]);
和下面方式结果一样:
db.goods.find({shop_price:{$gt:100}, shop_price:{$lt:5000}}, {goods_name:1, _id:0, goods_number:1, shop_price:1}) 1.7:取出不属于第3栏目且不属于第11栏目的商品($and $nin和$nor分别实现)
db.goods.find({$and:[{cat_id:{$ne:3}},{cat_id:{$ne:11}}]},{goods_name:1,cat_id:1})
db.goods.find({cat_id:{$nin:[3,11]}},{goods_name:1,cat_id:1});
db.goods.find({$nor:[{cat_id:3},{cat_id:11}]},{goods_name:1,cat_id:1}); 1.8:取出价格大于100且小于300,或者大于4000且小于5000的商品()
db.goods.find({$or:[{$and:[{shop_price:{$gt:100}},{shop_price:{$lt:300}}]},{$and:[{shop_price:{$gt:4000}},{shop_price:{$lt:5000}}]}]},{goods_name:1,shop_price:1}); 1.9:取出goods_id%5 == 1, 即,1,6,11,..这样的商品
db.goods.find({goods_id:{$mod:[5,1]}}); 1.10:取出有age属性的文档
db.stu.find({age:{$exists:1}});
含有age属性的文档将会被查出 1.11:使用where进行查询:
db.stu.find({$where:'this.goods_id>=3'}); // value直接接过滤条件

游标 cursor

游标不是查询结果,而是查询的返回资源,或者接口。类似与python中的生成器

通过调用可以逐条读取。直接将查询结果赋值给一个变量就是游标

var cursor = db.colllections_name.find(query, projection)
cursor.hasNext() //判断是否已经取完
cursor.Next() // 取出下一个元素

通常情况下我们用循环来获取游标中的值:

while循环

> var cursor=db.goods.find();
> while(cursor.hasNext()){
... printjson(cursor.next());}
{

for循环

> var cursor=db.goods.find();
> for(var doc=true;cursor.hasNext();){printjson(cursor.next())}

> for(var cursor=db.goods.find(); doc=true;cursor.hasNext()){printjson(cursor.next())}

forEach循环:可以自己添加回调函数

> var fun = function(obj){print(obj.goods_name)}; // 定义函数
> var cursor = db.goods.find(); // 创建游标
> cursor.forEach(fun); // 调用函数

游标分页:这个默认形式,也可以使用游标的形式

db.collection_name.find().skip(10); //表示跳过10开始取
db.collection_name.find().limit(10); //表示取10个

索引

索引提高查询速度,但会降低写入速度,所以再使用的时候需要权衡查询字段,不必在太多列上创建索引。在Mongodb中,索引可以按字段升序/降序来创建索引,便于排序。默认情况下mongodb是用btree来组织索引文件,2.4后也有通过hash来创建索引。

查看当前文档有那些索引

>db.collection_name.getIndexes()

创建普通索引(1表示升序,-1表示降序)

> db.collection_name.ensureIndex({field:})

创建多列索引

> db.collection_name.ensureIndex({field1:, field2:});

创建唯一索引

> db.collection_name.ensureIndex({field:},{unique:true});

创建hash索引

> db.collection_name.ensureIndex({field:'hashed'});

创建稀疏索引:如果针对field做索引,针对不含field列的文档将不做索引。而普通索引是会把该列作为null表示,并建立索引。

> db.collection_name.ensureIndex({name:}, {sparse:true});

重建索引

一个表经过很多次修改后,导致表的文件产生空洞,索引文件也是如此,可以通过索引重建,减少索引文件碎片,并提高索引的效率。类似mysql中的 optimize table

> db.collection_name.reIndex();