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