“
点击蓝字 / 关注我们
”
成员运算
# SQL:in,not in
# MongoDB:"$in","$nin"
# 1.select * from db1.user where age in (20,30,31);
db.user.find({"age":{"$in":[20,30,31]}})
# 2.select * from db1.user where name not in ('ax','yo');
db.user.find({"name":{"$nin":['ax','yo']}})
# 3.select * from db1.user where age in (20,30,31) or name != 'jason';
db.user.find({"$or":[{"age":{"$in":[20,30,31]}},{"name":{"$ne":'jason'}}]})
正则匹配
用一些T特殊符号的组合去文本中筛选出符合条件的数据
# SQL:regexp 正则
# MongoDB:/正则表达式/i
# select * from db1.user where name regexp '^j.*?(g|n)$';
db.user.find({"name":/^j.*?(g|n)$/i})
范围/模糊查询
db.user.find({查询条件}，{筛选字段})
"""
MySQL:
关键字 like
关键符号
%匹配任意个数的任意字符
_匹配单个个数的任意字符
MongoDB:
通过句点符
$all
"""
# 1.查看有dancing爱好的人
select * from db1.user where hobbies='dancing';
db.user.find({"hobbies":'dancing'})
# 2.查看既有dancing爱好又有tea爱好的人
select * from db1.user where hobbies='dancing' and hobbies='tea';
db.user.find({'hobbies':{"$all":['dancing','tea']}})
# 3.查看第4个爱好为tea的人(点索引)
select * from db1.user where hobbies.3='tea';
db.user.find({'hobbies.3':'tea'})
# 4.查看所有人最后两个爱好
db.user.find({},{'_id':0,'name':1,'hobbies':{'$slice':-2}})
# 5.查看所有人前面两个爱好
db.user.find({},{'_id':0,'name':1,'hobbies':{"$slice":2}})
# 6.查看所有人中间的第2个到第3个爱好
db.user.find({},{'_id':0,'name':1,'hobbies':{"$slice":[1,2]}})
# 7.查询国家是China的数据(点键)
db.user.find({'addr.country':'China'})
数据排序
"""
MySQL:
关键字order by
升序 降序asc desc
MongoDB:
关键字sort
升序 降序1 -1
"""
# 排序:1代表升序,-1代表降序
# select * from db.user order by age asc;
db.user.find().sort({"age":1})
# select * from db.user order by age desc,_id asc;
db.user.find().sort({"age":-1,"_id":1})
分页(限制查询条数)
"""
MySQL:
关键字limit
分页5,5
MongoDB:
关键字limit
分页skip
"""
# 分页:limit代表取多少个document,skip代表跳过前多少个document
# select * from db.user limit 2,1 # 从第二个开始，选择一个
db.user.find().sort({"age":1}).limit(1).skip(2)
· 内容补充 ·
# 获取数量
# 统计年龄大于30岁的数据条数
db.user.find({"age":{"$gt":30}}).count()
或者db.user.count({"age":{"$gt":30}})
# 1.{'key':null} 匹配key的值为null或者没有这个key的数据
db.t2.insert({'a':10,'b':111})
db.t2.insert({'a':20})
db.t2.insert({'b':null})
db.t2.find({"b":null})
{ "_id" : ObjectId("5a5cc2a7c1b4645aad959e5a"), "a" : 20 }
{ "_id" : ObjectId("5a5cc2a8c1b4645aad959e5b"), "b" : null }
# 2.查找所有
db.user.find() #等同于db.user.find({})
# 3.查找一个,与find用法一致,只是只取匹配成功的第一个
db.user.findOne({"_id":{"$gt":3}})
MongoDB语句补充
# 查询平均工资最高的前两个部门
# SQL语句
select post,avg(salary) as avg_salary from db.emp group by post order by avg_salary desc limit 2;
# MongoDB语句
db.emp.aggregate(
{'$group':{'_id':'$post','avg_salary':{'$avg':'$salary'}}},
{'$sort':{'avg_salary':-1}},
{'$limit':2}
)
# 随机抽取
'''
python模块random
random.choice([11,22,33,44,55,66])
'''
db.user.aggregate(
[ { $sample: { size: 3 } } ]
)
课堂练习
'''先尝试用sql(SQL)语句完成再用mongodb(NoSQL)语句完成'''
1. 查询岗位名以及各岗位内的员工姓名
select post,group_concat(name) as names from db.emp group by post;
db.emp.aggregate(
{"$group":{"_id":"$post","names":{"$push":"$name"}}})
2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from db.emp group by post;
db.emp.aggregate(
{"$group":{"_id":"$post","员工个数":{"$sum":1}}})
3. 查询公司内男员工和女员工的个数
select sex,count(id) from db.emp group by sex;
db.emp.aggregate(
{"$group":{"_id":"$sex","员工个数":{"$sum":1}}})
4. 查询岗位名以及各岗位的平均薪资、最高薪资、最低薪资
select post,avg(salary),max(salary),min(salary) from db.emp group by post;
db.emp.aggregate(
{"$group":{
"_id":"$post",
"平均薪资":{"$avg":"$salary"},
"最高薪资":{"$max":"$salary"},
"最低薪资":{"$min":'$salary'}
}
})
5. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from db.emp group by sex;
db.emp.aggregate(
{"$group":{"_id":"$sex","平均薪资":{"$avg":'$salary'}}})
6. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(name),count(id) from db.emp group by post having count(id) < 2;
db.emp.aggregate(
{"$group":{"_id":"$post",
"员工姓名":{"$push":"$name"},
'员工个数':{"$sum":1}
}},
{'$match':{'员工个数':{"$lt":2}}}
)
7. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from db.emp group by post haing avg(salary) > 10000;
db.emp.aggregate(
{"$group":{"_id":"$post","平均薪资":{"$avg":"$salary"}}},
{"$match":{"平均薪资":{"$gt":10000}}}
)
8. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from db.emp group by post haing avg(salary) > 10000 and avg(salary) < 20000;
db.emp.aggregate(
{"$group":{"_id":"$post","平均薪资":{"$avg":"$salary"}}},
{"$match":{"平均薪资":{"$gt":10000,"$lt":20000}}}
)
9. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select * from db.emp order by age asc,hire_date desc;
db.emp.find({}).sort({"age":1,"hire_date":-1})
10. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) from db.emp group by post having avg(salary) > 10000 order by avg(salary) asc;
db.emp.aggregate(
{"$group":{"_id":"$post","平均薪资":{"$avg":"$salary"}}},
{"$match":{"平均薪资":{"$gt":10000}}},
{"$sort":{'平均薪资':1}}
)
11. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列,取前1个
select post,avg(salary) from db.emp group by post having avg(salary) > 10000 order by avg(salary) desc limit 1;
db.emp.aggregate(
{"$group":{"_id":"$post","平均薪资":{"$avg":"$salary"}}},
{"$match":{"平均薪资":{"$gt":10000}}},
{"$sort":{'平均薪资':-1}},
{"$limit":1}
)
聚合与分组
# 分组
# select post from db.emp group by post;
db.emp.aggregate({'$group':{'_id':'$post'}})
# select avg(salary) as '平均薪资' from db.emp group by post;
db.emp.aggregate({'$group':{'_id':'$post','平均薪资':{'$avg':'$salary'}}})
# select * from db.emp where id >3 group by post having avg(salary) > 10000;
db.emp.aggregate({'$match':{'_id':{"$gt":3}}},
{'$group':{'_id':'$post','avg_salary':{'$avg':'$salary'}}},
{'$match':{'avg_salary':{'$gt':10000}}}
)
{"$group":{"_id":'$分组字段',"新的字段名":聚合操作符}}
# 1.将分组字段传给$group函数的_id字段即可
{"$group":{"_id":'$sex'}} # 按照性别分组
{"$group":{"_id":'$post'}} # 按照职位分组
{"$group":{"_id":{"state":"$state",'city':"$city"}}}
# 按照多个字段分组
# 2.分组后聚合得结果,类似于sql中聚合函数的聚合操作符：$sum、$avg、$max、$min、$first、$last
# 例1. select post,max(salary) from db1.emp group by post;
db.emp.aggregate({"$group":{'_id':'$post','max_salary':{"$max":"$salary"}}})
# 例2. 去每个部门最大薪资与最低薪资
db.emp.aggregate({"$group":
{
"_id":"$post",
"max_salary":{"$max":"$salary"},
"min_salary":{"$min":"$salary"}
}
})
# 例3.如果字段是排序后的，那么$first,$last会很有用,比用$max和$min效率高
db.emp.aggregate({"$group":{"_id":"$post","first_id":{"$first":"$_id"}}})
# 例4：求每个部门的总工资
db.emp.aggregate({"$group":{"_id":"$post","count":
{"$first":"$_id"}}})
# 例5:求每个部门的人数
db.emp.aggregate({"$group":{"_id":"$post","count":{"$sum":1}}})
{"$project":{"要保留的字段名":1,'要去掉的字段名':0,"新增的字段名":"表达式"}}
# 1.select name,post,(age+1) as nem_age from db1.emp;
db.emp.aggregate(
{"$project":{
"name":1,
"post":1,
"new_age":{"$add":["$age":1]}
}
})
# 2.表达式之数学表达式
{"$add":[expr1,expr2,expr3,...,exprN]} # 相加
{"$subtract":[expr1,expr2]} # 第一个减第二个
{"$multiply":[expr1,expr2,...,exprN]} # 相乘
{"$divide":[expr1,expr2]} # 第一个表达式除第二个表达式的商作为结果
{"$mod":[expr1,expr2]} # 第一个表达式除第二个表达式的余数作为结果
# 3.表达式之日期表达式：$year,$month,$week,$dayOfMonth,$dayOfWeek,$dayOfYear,$hour,$minute,$second
# 例如 select name,date_format("%Y") as hire_year from db1.emp;
db.emp.aggregate(
{"$project":{
"name":1,
"hire_year":
{"$year":"$hire_date"}
}}
)
# 例如查看每个员工工作多长时间
db.emp.aggregate(
{"$project":{
"name":1,
"hire_period":{
"$subtract":[
{"$year":new Date()},
{"$year":"$hire_date"}
]
}}}
)
# 4.字符串表达式
{"$substr":[字符串/$值为字符串的字段名,起始位置,截取几个字符]}
{"$concat":[expr1,expr2,...,exprN]} # 指定的表达式或字符串连接在一起返回，只支持字符串拼接
{"$toLower":expr}
{"$toUpper":expr}
db.emp.aggregate({"$project":{"NAME":{"$toUpper":"$name"}}})
