python mongodb操作大全

標籤：lex   group   names   其他   auth   www   size   主鍵   修改   

資料庫
增
use db1 #有則切換，無則新增

查
show dbs #查看所有
db #當前

刪
db.dropDatabase()

集合：
增:
db.user
db.user.info
db.user.auth

查看
show collections
show tables

刪
db.user.info.drop()

文檔：
增
db.user.insert({"_id":1,"name":"egon"})
user0={
"name":"egon",
"age":10,
‘hobbies‘:[‘music‘,‘read‘,‘dancing‘],
‘addr‘:{
‘country‘:‘China‘,
‘city‘:‘BJ‘
}
}

db.user.insert(user0)
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})

　　　　　save與insert的區別:
　　 若新增的資料中存在主鍵 ，insert() 會提示錯誤，而save() 則更改原來的內容為新內容。
　　 如：
　　　　 已存在資料： {_id : 1, " name " : " n1 " }，再次進行插入操作時，
　　　　 insert({_id : 1, " name " : " n2 " }) 會報主鍵重複的錯誤提示
　　　　 save({ _id : 1, " name " : " n2 " }) 會把 n1 修改為 n2 。
　　　　 相同點：
　　　　 若新增的資料中沒有主鍵時，會增加一條記錄。
　　　　 已存在資料： { _id : 1, " name " : " n1 " }，再次進行插入操作時，
　　　　 insert({ " name " : " n2 " }) 插入的資料因為沒有主鍵，所以會增加一條資料
　　　　 save({ " name " : " n2 " }) 增加一條資料。
查

比較運算：=，!=,>,<,>=,<=
#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}})

#邏輯運算:$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 = "yuanhao";
db.user.find({"$or":[
{"_id":{"$lte":1,"$gte":0}},
{"_id":{"$gte":4}},
{"name":"yuanhao"}
]})

#4 select * from db1.user where id % 2 = 1;
db.user.find({"_id":{"$mod":[2,1]}})

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

#成員運算:$in,$nin
db.user.find({"name":{"$in":["alex","egon"]}})
db.user.find({"name":{"$nin":["alex","egon"]}})

#正則匹配
select * from db1.user where name regexp "^jin.*?(g|n)$";
db.user.find({
"name":/^jin.*?(g|n)$/i
})

#查看指定欄位
select name,age from db1.user where name regexp "^jin.*?(g|n)$";
db.user.find({
"name":/^jin.*?(g|n)$/i
},
{
"_id":0,
"name":1,
"age":1
}
)

#查詢數組相關
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()

改
一 文法：
db.table.update(
條件,
修改欄位,
其他參數
)

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

db.table.update(
{},
{"age":11},
{
"multi":true,
"upsert":true
}
)

1、update db1.user set age=23,name="武大郎" where name="wupeiqi";
#覆蓋式
db.user.update(
{"name":"wupeiqi"},
{"age":23,"name":"武大郎"}
)
#局部修改:$set
db.user.update(
{"name":"alex"},
{"$set":{"age":73,"name":"潘金蓮-alex"}}
)

#改多條
db.user.update(
{"_id":{"$gte":1,"$lte":2}},
{"$set":{"age":53,}},
{"multi":true}
)
#有則修改，無則添加
db.user.update(
{"name":"EGON"},
{"$set":{"name":"EGON","age":28,}},
{"multi":true,"upsert":true}
)

#修改嵌套文檔
db.user.update(
{"name":"潘金蓮-alex"},
{"$set":{"addr.country":"Japan"}}
)

#修改數組
db.user.update(
{"name":"潘金蓮-alex"},
{"$set":{"hobbies.1":"Piao"}}
)

#刪除欄位
db.user.update(
{"name":"潘金蓮-alex"},
{"$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":"yuanhao"},
{"$push":{"hobbies":"tangtou"}},
{"multi":true}
)

db.user.update(
{"name":"yuanhao"},
{"$push":{"hobbies":{"$each":["紋身","抽煙"]}}},
{"multi":true}
)

#從頭刪-1，從尾刪1
db.user.update(
{"name":"yuanhao"},
{"$pop":{"hobbies":-1}},
{"multi":true}
)

db.user.update(
{"name":"yuanhao"},
{"$pop":{"hobbies":1}},
{"multi":true}
)

#按條件刪
db.user.update(
{"name":"yuanhao"},
{"$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}
)

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

彙總
一：$match
例：
select post from db1.emp where age > 20 group by post having avg(salary) > 10000;

#$match
#1、select * 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：
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"}]}}}
)

例3：
db.emp.aggregate(
{"$project":{"_id":0,"new_name":{"$toUpper":"$name"},}}
)

db.emp.aggregate(
{"$match":{"name":{"$ne":"egon"}}},
{"$project":{"_id":0,"new_name":{"$concat":["$name","_SB"]},}}
)

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;

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
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}}) #隨機選取3個文檔

python mongodb操作大全