mongodb python 大于_MongoDB数据库 333

内容回顾

• 数据的查询

# 结合MySQL的SQL语句对比学习

1.比较运算符

db.user.find({'id':{'$gt':1}})

2.逻辑运算符

db.user.find({"_id":{"$gte":2},"age":{"$lt":40}})

3.成员运算

db.user.find({"age":{"$in":[20,30,31]}})

4.正则匹配(不要加引号)

db.user.find({'name':/^j.*?(g|n)$/i})

5.数组查询

db.user.find({"hobbies.3":'tea'})

6.排序

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

7.分页

db.user.find().sort({'age':1}).limit(1).skip(2)

• 数据的编辑

db.user.update({'_id':3},{'$set':{'addr.country':'USA'}})

db.user.update({},

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

{"multi":true})

• 聚合与分组

db.emp.aggregate(

{"$match":{"_id":{"$gt":3}}}, # where

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

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

)

# 把MongoDB语句尝试着用SQL语句模拟实现

select post,avg(salary) as avg_salary from db.emp where _id > 3 group by post having avg(salary) > 10000;

今日内容概要

• MongoDB语句补充
• MongoDB练习题
• python模块操作MongoDB
• Robo 3T工具使用
• 其他知识点补充

今日内容详细

MongoDB语句补充

# SQL语句

select post,avg(salary) as avg_salary from db.emp group by post order by avg_salary desc limit 2;

# MongoDB语句 (注意涉及到分组用aggregate）

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.emp.aggregate(

[ { $sample: { size: 3 } } ]

)

前期准备数据代码运行可能会报错

1.py:41报错

有可能是因为你已经提前创建了emp集合并且有一些数据导致

解决方案:将emp集合删除再次运行即可

2.authentication failed

就是由于账户管理和权限没有配置好导致

解决方案:设置对应的账户与权限

3.No module named pymongo

缺少操作mongodb的python模块pymongo

解决方案:pip3 install pymongo

数据准备

from pymongo import MongoClient

import datetime

client=MongoClient('mongodb://root:123@localhost:27017')

table=client['db1']['emp']

# table.drop()

l=[

('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部

('alex','male',78,'20150302','teacher',1000000.31,401,1),

('wupeiqi','male',81,'20130305','teacher',8300,401,1),

('yuanhao','male',73,'20140701','teacher',3500,401,1),

('liwenzhou','male',28,'20121101','teacher',2100,401,1),

('jingliyang','female',18,'20110211','teacher',9000,401,1),

('jinxin','male',18,'19000301','teacher',30000,401,1),

('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门

('丫丫','female',38,'20101101','sale',2000.35,402,2),

('丁丁','female',18,'20110312','sale',1000.37,402,2),

('星星','female',18,'20160513','sale',3000.29,402,2),

('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门

('程咬金','male',18,'19970312','operation',20000,403,3),

('程咬银','female',18,'20130311','operation',19000,403,3),

('程咬铜','male',18,'20150411','operation',18000,403,3),

('程咬铁','female',18,'20140512','operation',17000,403,3)

]

for n,item in enumerate(l):

d={

"_id":n,

'name':item[0],

'sex':item[1],

'age':item[2],

'hire_date':datetime.datetime.strptime(item[3],'%Y%m%d'),

'post':item[4],

'salary':item[5]

}

table.save(d)

课堂练习

'''先尝试用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}

)

pymongo模块

能够支持使用python代码操作mongodb数据库

1.下载

pip3 install pymongo

2.导入

from pymongo import MongoClient

3.基本使用

from pymongo import MongoClient
# 'mongodb://用户名:密码@localhost:27017/'
client = MongoClient('mongodb://root:123@localhost:27017/')
# use db1
db1 = client['db1']
# show tables
tables = db1.collection_names()
# print(tables) # ['t3', 'emp', 'user', 't1']
# db.createCollection('') db.tt.insert({})
# t222 = db1.create_collection('t222') # 直接刷到硬盘
# db1['t123'] # 先创建在内存中
# 先定义出集合数据
# import datetime
# user0={
# "_id":1,
# "name":"jason",
# "birth":datetime.datetime.now(),
# "age":18,
# 'hobbies':['music','read','dancing'],
# 'addr':{
# 'country':'China',
# 'city':'SH'
# }
# }
# user1={
# "_id":2,
# "name":"tony",
# "birth":datetime.datetime.now(),
# "age":28,
# 'hobbies':['music','read','dancing'],
# 'addr':{
# 'country':'China',
# 'city':'SZ'
# }
# }
# t222.insert({}) t222.insertMany([{},{}])
# t222.insert_many([user0,user1])
# 查询操作
# 指定表
"""在所有的方法中都完全支持mongodb查询关键字"""
t222 = db1['t222']
# for data in t222.find({}):
# print(data)
# 编辑操作
t222.update({'name':'tony'},{'$set':{'name':'tonyDSB'}})

可视化软件

网址:Free, open-source MongoDB GUI (formerly Robomongo)

我们之前学习使用的Navicat其实也可以连接mongodb并操作

官网下载download studio 3T only 一步步点击安装即可

点击右上角连接按钮

输入对应的库和账户信息连接即可

作业

1.将分组相关的练习题独立完成

每个部门最大薪资与最低薪资

求每个部门的总工资

求每个部门的人数

查询岗位名以及各岗位内的员工姓名

远程控制电脑软件

向日葵