mongo执行逻辑表达式_Mongodb操作详解

Mongodb操作

零 用户管理

要管理哪个库就要在哪个库下创建账号，只能登录这个库

show databases

use admin #切换到admin库

db.createUser(

{

user: "root",

pwd: "123",

roles: [ { role: "root", db: "admin" } ]

}

)

use test #有就使用，没有就创建，空不显示，只有有数据才显示

一 数据库操作

增

​

use db1 #有则切换，无则新增

查

show dbs #查看所有

db #当前

​

删

db.dropDatabase()

二集合操作(表)

增

​

db.user

db.user.info

db.user.auth

查看

show collections

show tables

​

删

db.user.info.drop()

三 文档操作(数据)

3.1增加文档

db.user.insert({"_id":1,"name":"egon"})

user0={

"name":"zhangsan",

"age":10,

'hobbies':['music','read','dancing'],

'addr':{

'country':'China',

'city':'BJ'

}

}

db.user.insert(user0)

​

#3、插入多条

user1={

"_id":1,

"name":"lisi",

"age":10,

'hobbies':['music','read','dancing'],

'addr':{

'country':'China',

'city':'weifang'

}

}

​

user2={

"_id":2,

"name":"wangwu",

"age":20,

'hobbies':['music','read','run'],

'addr':{

'country':'China',

'city':'山东'

}

}

​

​

user3={

"_id":3,

"name":"zhaoliu",

"age":30,

'hobbies':['music','drink'],

'addr':{

'country':'China',

'city':'上海'

}

}

​

user4={

"_id":4,

"name":"wangqi",

"age":40,

'hobbies':['music','read','dancing','tea'],

'addr':{

'country':'China',

'city':'BJ'

}

}

​

user5={

"_id":5,

"name":"yueba",

"age":50,

'hobbies':['music','read',],

'addr':{

'country':'China',

'city':'henan'

}

}

db.user.insertMany([user1,user2,user3,user4,user5])

​

db.t1.insert({"_id":1,"a":1,"b":2,"c":3})

#有相同的_id则覆盖，无相同的_id则新增，必须指定_id

db.t1.save({"_id":1,"z":6})

db.t1.save({"_id":2,"z":6})

db.t1.save({"z":6})

​

​

3.2 查询文档

3.2.1比较运算

=，!=,>,=,<

1、select * from db1.user where id = 3

​

db.user.find({"_id":3})

​

2、select * from db1.user where id != 3

​

db.user.find({"_id":{"$ne":3}})

​

3、select * from db1.user where id > 3

​

db.user.find({"_id":{"$gt":3}})

​

4、select * from db1.user where age < 3

​

db.user.find({"age":{"$lt":3}})

​

5、select * from db1.user where id >= 3

​

db.user.find({"_id":{"$gte":3}})

​

6、select * from db1.user where id <= 3

​

db.user.find({"_id":{"$lte":3}})

3.2.2逻辑运算

$and,$or,$not

1 select * from db1.user where id >=3 and id <=4;

​

db.user.find({"_id":{"$gte":3,"$lte":4}})

​

2 select * from db1.user where id >=3 and id <=4 and age >=40;

​

db.user.find({

"_id":{"$gte":3,"$lte":4},

"age":{"$gte":40}

})

​

db.user.find({"$and":[

{"_id":{"$gte":3,"$lte":4}},

{"age":{"$gte":40}}

]})

​

3 select * from db1.user where id >=0 and id <=1 or id >=4 or name = "zhangsan";

​

db.user.find({"$or":[

{"_id":{"$lte":1,"$gte":0}},

{"_id":{"$gte":4}},

{"name":"zhangsan"}

]})

​

4 select * from db1.user where id % 2 = 1;

​

db.user.find({"_id":{"$mod":[2,1]}})

​

db.user.find({

"_id":{"$not":{"$mod":[2,1]}}

})

3.2.3成员运算

$in,$nin

db.user.find({"name":{"$in":["zhangsan","lisi"]}})

db.user.find({"name":{"$nin":["zhangsan","lisi"]}})

3.2.4正则匹配

select * from db1.user where name regexp "^z.*?$";

db.user.find({

"name":/^z.*?$/i

})

3.2.5查看指定字段

select name,age from db1.user where name regexp "^z.*?(g|n)$";

db.user.find({

"name":/^z.*?(g|n)$/i

},

{

"_id":0,

"name":1,

"age":1

}

)

3.2.5查询数组相关

db.user.find({

"hobbies":"dancing"

})

​

db.user.find({

"hobbies":{"$all":["dancing","tea"]}

})

​

db.user.find({

"hobbies.2":"dancing"

})

​

db.user.find(

{},

{

"_id":0,

"name":0,

"age":0,

"addr":0,

"hobbies":{"$slice":[1,2]},

}

)

​

db.user.find(

{},

{

"_id":0,

"name":0,

"age":0,

"addr":0,

"hobbies":{"$slice":2},

}

)

​

db.user.find(

{

"addr.country":"China"

}

)

​

db.user.find().sort({"_id":1,"age":-1})

​

db.user.find().limit(2).skip(0)

db.user.find().limit(2).skip(2)

db.user.find().limit(2).skip(4)

db.user.find().distinct()

3.3修改文档

一 语法：

db.table.update(

条件,

修改字段,

其他参数

)

update db1.t1 set id=10 where name="yuefei";

​

db.table.update(

{},

{"age":11},

{

"multi":true,

"upsert":true

}

)

​

1、update db1.user set age=23,name="武大郎" where name="zhagnsan";

#覆盖式

db.user.update(

{"name":"zhangsan"},

{"age":23,"name":"武大郎"}

)

#局部修改:$set

db.user.update(

{"name":"alex"},

{"$set":{"age":73,"name":"潘金莲"}}

)

​

#改多条

db.user.update(

{"_id":{"$gte":1,"$lte":2}},

{"$set":{"age":53,}},

{"multi":true}

)

#有则修改，无则添加

db.user.update(

{"name":"EGON"},

{"$set":{"name":"lisi","age":28,}},

{"multi":true,"upsert":true}

)

​

#修改嵌套文档

db.user.update(

{"name":"潘金莲"},

{"$set":{"addr.country":"Japan"}}

)

​

#修改数组

db.user.update(

{"name":"潘金莲"},

{"$set":{"hobbies.1":"Piao"}}

)

​

#删除字段

db.user.update(

{"name":"潘金莲"},

{"$unset":{"hobbies":""}}

)

​

2、$inc

db.user.update(

{},

{"$inc":{"age":1}},

{"multi":true}

)

​

db.user.update(

{},

{"$inc":{"age":-10}},

{"multi":true}

)

​

3、$push, $pop $pull

db.user.update(

{"name":"yuefei"},

{"$push":{"hobbies":"tangtou"}},

{"multi":true}

)

​

db.user.update(

{"name":"yuanhao"},

{"$push":{"hobbies":{"$each":["纹身","抽烟"]}}},

{"multi":true}

)

​

#从头删-1，从尾删1

db.user.update(

{"name":"wangwu"},

{"$pop":{"hobbies":-1}},

{"multi":true}

)

​

db.user.update(

{"name":"zhaoliu"},

{"$pop":{"hobbies":1}},

{"multi":true}

)

​

#按条件删

db.user.update(

{"name":"wangwu"},

{"$pull":{"hobbies":"纹身"}},

{"multi":true}

)

​

#3、$addToSet

db.t3.insert({"urls":[]})

db.t3.update(

{},

{"$addToSet":{"urls":{"$each":[

"http://www.baidu.com",

"http://www.baidu.com",

"http://www.baidu.com",

"http://www.baidu.com",

"http://www.baidu.com"

]}}},

{"multi":true}

)

3.4删除文档

db.user.deleteOne({"_id":{"$gte":3}})

db.user.deleteMany({"_id":{"$gte":3}})

db.user.deleteMany({})

3.5聚合文档

3.5.1 $match

例：

select post from db1.emp where age > 20 group by post having avg(salary) > 10000;

#$match

#1、select post from db1.emp where age > 20

db.emp.aggregate({"$match":{"age":{"$gt":20}}})

​

#$group

#2、select post from db1.emp where age > 20 group by post;

db.emp.aggregate(

{"$match":{"age":{"$gt":20}}},

{"$group":{"_id":"$post"}}

)

​

#3、select post,avg(salary) as avg_salary from db1.emp where age > 20 group by post;

db.emp.aggregate(

{"$match":{"age":{"$gt":20}}},

{"$group":{"_id":"$post","avg_salary":{"$avg":"$salary"}}}

)

​

#select post from db1.emp where age > 20 group by post having avg(salary) > 10000;

db.emp.aggregate(

{"$match":{"age":{"$gt":20}}},

{"$group":{"_id":"$post","avg_salary":{"$avg":"$salary"}}},

{"$match":{"avg_salary":{"$gt":10000}}}

)

二：

{"$project":{"要保留的字段名":1,"要去掉的字段名":0,"新增的字段名":"表达式"}}

例1：

#查询每组平均年薪大于1000，显示部门名，平均年薪，不显示id

db.emp.aggregate(

{"$project":{"_id":0,"name":1,"post":1,"annual_salary":{"$multiply":[12,"$salary"]}}},

{"$group":{"_id":"$post","平均年薪":{"avg":"annual_salary"}}},

{"$match":{"平均年薪":{"$gt":1000000}}},

{"$project":{"部门名":"$_id","平均年薪":1,"_id":0}}

)

例2：

#入职时间

db.emp.aggregate(

{"$project":{"_id":0,"name":1,"hire_period":{"$subtract":[new Date(),"$hire_date"]}}}

)

#哪一年入职的

db.emp.aggregate(

{"$project":{"_id":0,"name":1,"hire_year":{"$year":"$hire_date"}}}

)

#人工作几年

db.emp.aggregate(

{"$project":{"_id":0,"name":1,"hire_period":{"$subtract":[{"$year":new Date()},{"$year":"$hire_date"}]}}}

)

#所有人名字变大写

db.emp.aggregate(

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

)

#除egon外，所有名字加sb

db.emp.aggregate(

{"$match":{"name":{"$ne":"egon"}}},

{"$project":{"_id":0,"new_name":{"$concat":["$name","_SB"]},}}

)

#从名字字符串中截出3个字节(utf-8编码，汉字三个字节)

db.emp.aggregate(

{"$match":{"name":{"$ne":"egon"}}},

{"$project":{"_id":0,"new_name":{"$substr":["$name",0,3]},}}

)

三分组：

{"$group":{"_id":分组字段,"新的字段名":聚合操作符}}

#select post,max,min,sum,avg,count,group_concat from db1.emp group by post;

#聚合函数，最大年龄，最小id，平均工资，工资之和，总人数，所有人的名字放到数组中

db.emp.aggregate(

{"$group":{

"_id":"$post",

"max_age":{"$max":"$age"},

"min_id":{"$min":"$_id"},

"avg_salary":{"$avg":"$salary"},

"sum_salary":{"$sum":"$salary"},

"count":{"$sum":1},

"names":{"$push":"$name"}

}

}

)

四：

排序：$sort、限制：$limit、跳过：$skip

#匹配名字不为egon，名字截取三个字符按照年龄升序，id降序排列，跳过显示5条

db.emp.aggregate(

{"$match":{"name":{"$ne":"egon"}}},

{"$project":{"_id":1,"new_name":{"$substr":["$name",0,3]},"age":1}},

{"$sort":{"age":1,"_id":-1}},

{"$skip":5},

{"$limit":5}

)

补充

#随机取三个值

db.emp.aggregate({"$sample":{"size":3}})

更多资料，查看小猿取经博客小猿取经 - 博客园​www.cnblogs.com