今日内容概要

聚合查询
Python操作MongoDB
第三方可视化视图工具

今日内容详细

聚合查询
Python操作MongoDB

数据准备

from pymongo import MongoClient # 载入pymongo模块

import datetime # 载入datetime模块

client = MongoClient('mongodb://root:666@localhost:27017')# 连接MongoDB数据库(mongodb://用户名:密码@localhost(ip地址):port端口)

table = client['huangduoduo']['emp'] # client['数据库']['所形成的的表名']

l=[

('egon','male','男',18,'20170301','teacher',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语句为例

# 统计每个部门年龄大于20岁以上的员工的平均工资大于10000的部门和平均工资

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

'''在MongoDB中 设计到分组的 查询关键字需要使用aggregate（）'''

# 1.$match 筛选  等价于where和having

# select *from emp where age>20

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

# 2.$group

# select post,avg(salary) from emp where age>20 group by post

db.emp.aggregate({'$match':{"age":{"$gt":20}}},{'$group':{"_id":"$post","avg_salary":{"$avg":"$salary"}}})

# _id是$group固定的用法不是主键_id，$post表示获取键post对应的值

# 按什么分组就在键_id后面写什么

'''

如果你想拿到键对应的值 很简单

只需要在键的前面加上一个$符号就表示获取键对应的值

$avg 等价于sql语句里面的聚合函数 avg()

'''

# 3.select post from 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}})

                                                                     '''

MySQL里面的关键字                                                           select

		from

		where

		group by

		having

		order by

		distinct

		limit

		regexp

MongoDB里面的关键字

		$each

		$match

		$group

		……

'''                                      

                                                                     # 投射(映射) $project

{"$project":{"要保留的字段名":1,"要去掉的字段名":0,"新增的字段名":"表达式"}} # 单纯的映射意义不大

 # select salary*12 from emp

# project还可以对数据做二次操作

db.emp.aggregate(

	{"$project":{"_id":0,"name":1,"post":1,"salary":1}}

)

# 只能拿固定的数据 无法直接操作

db.emp.find({},{"_id":0,"name":1,"post":1,"salary":1})

# 关键字$multiply对应的值放一个列表 会自动将列表中的多个元素相乘

  # 再分组取别的

      # 再筛选

          # 再次映射修改字段名

db.emp.aggregate(

	{"$project":{"_id":0,

               "name":1,

               "post":1,

               "annual_salary":{"$multiply":[12,"$salary"]}}},

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

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

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

)

#1、select name,post,(age+1) as new_age from db1.emp;

db.emp.aggregate(

    {"$project":{

        "name":1,

        "post":1,

        "年龄":{"$add":["$age",1]}

        }

})

"""

#2、表达式之数学表达式

{"$add":[expr1,expr2,...,exprN]} #相加

{"$subtract":[expr1,expr2]} #第一个减第二个

{"$multiply":[expr1,expr2,...,exprN]} #相乘

{"$divide":[expr1,expr2]} #第一个表达式除以第二个表达式的商作为结果

{"$mod":[expr1,expr2]} #第一个表达式除以第二个表达式得到的余数作为结果

"""

#例如：select name,date_format("%Y") as hire_year from emp

#3、表达式之日期表达式:$year,$month,$week,$dayOfMonth,$dayOfWeek,$dayOfYear,$hour,$minute,$second

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"}

        ]

    }}}

)

$group补充

#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":{"$sum":"$salary"}}})

#例5：求每个部门的人数

db.emp.aggregate({"$group":{"_id":"$post","count":{"$sum":1}}})

#3、数组操作符

{"$addToSet":expr}：不重复

{"$push":expr}：重复

#例：查询岗位名以及各岗位内的员工姓名:select post,group_concat(name) from db1.emp group by post;

db.emp.aggregate({"$group":{"_id":"$post","names":{"$push":"$name"}}})

db.emp.aggregate({"$group":{"_id":"$post","names":{"$addToSet":"$name"}}})

随机获取

#集合users包含的文档如下

{ "_id" : 1, "name" : "dave123", "q1" : true, "q2" : true }

{ "_id" : 2, "name" : "dave2", "q1" : false, "q2" : false  }

{ "_id" : 3, "name" : "ahn", "q1" : true, "q2" : true  }

{ "_id" : 4, "name" : "li", "q1" : true, "q2" : false  }

{ "_id" : 5, "name" : "annT", "q1" : false, "q2" : true  }

{ "_id" : 6, "name" : "li", "q1" : true, "q2" : true  }

{ "_id" : 7, "name" : "ty", "q1" : false, "q2" : true  }

#下述操作时从users集合中随机选取3个文档

db.users.aggregate(

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

)

可视化软件

1.navicat里面也可以连接MongoDB

2.pycharm里面也可以连接

	jetbrains公司也专门开发了一款可以连接很多数据库的软件

3.robo 3.0T

	直接百度搜索下载即可

python代码操作MongoDB

#用python操作mongodb

from pymongo import MongoClient

#1、链接

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

client = MongoClient('localhost', 27017)

#2、use 数据库

# db=client['db2'] #等同于：client.db1

db=client.test

#3、查看库下所有的集合

# print(db.collection_names(include_system_collections=False))

#4、创建集合

table_user=db['table1'] #等同于：db.user

# table1=db.table1

# #5、插入文档

# import datetime

# user0={

#     "_id":1,

#     "name":"egon",

#     "birth":datetime.datetime.now(),

#     "age":10,

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

#     'addr':{

#         'country':'China',

#         'city':'BJ'

#     }

# }

#

# user1={

#     "_id":2,

#     "name":"alex",

#     "birth":datetime.datetime.now(),

#     "age":10,

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

#     'addr':{

#         'country':'China',

#         'city':'weifang'

#     }

# }

# res=table_user.insert_many([user0,user1])

# print(res)

# print(table_user.count())

#6、查找

# from pprint import pprint#格式化细

# pprint(table_user.find_one())

# for item in table_user.find():

#     pprint(item)

print(table_user.find_one({"_id":{"$gte":1},"name":'egon'}))

table_user.find()

#7、更新

table_user.update({'_id':1},{'name':'EGON'})

#8、传入新的文档替换旧的文档

table_user.save(

    {

        "_id":2,

        "name":'egon_xxx'

    }

)

作业

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

   db.emp.aggregate({"$group":{"_id":"$post","names":{"$addToSet":"$name"}}})

2. 查询岗位名以及各岗位内包含的员工个数

   db.emp.aggregate({"$group":{"_id":"$post","count":{"$sum":1}}})

3. 查询公司内男员工和女员工的个数

   db.emp.aggregat({"$group":{"_id":"$sex","count":{"$sum":1}}})

4. 查询岗位名以及各岗位的平均薪资、最高薪资、最低薪资

   db.emp.aggregate({"$group":{"_id":"$post","max_salary":{"$max":"$salary"},"min_salary":{"$min":"$salary"}}})

5. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

  db.emp.aggregat({"$group":{"_id":"$sex","avg_salary":{"$avg":"$salary"}}})

6. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数

db.emp.aggregate({"$group":{"_id":"$post","names":{"$addToSet":"$name"},"count":{"$sum":1}},

                      "$match":{"count":{"$lt":2}},

                      "$project":{"_id":0,"names":1,"count":1}})

7. 查询各岗位平均薪资大于10000的岗位名、平均工资

 db.emp.aggregate({"$group":{"_id":"$post","avg_salary":{"$avg":"$salary"}},

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

8. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

 db.emp.aggregate({"$group":{"_id":"$post","avg_salary":{"$avg":"$salary"}},

                       "$match":{"avg_salary":{"$gt":10000,"$lt":20000}}})

9. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序

db.emp.aggregate({"$sort":{"age":1,"hire_date":-1}})

10. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列

db.emp.aggregate({"$group":{"_id":"$post","avg_salary":{"$avg":"$salary"}},

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

                      "$sort":{"avg_salary":1}})

11. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列,取前1个

db.emp.aggregate({"$group":{"_id":"$post","avg_salary":{"$avg":"$salary"}},

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

                      "$sort":{"avg_salary":-1},"$limit":1})

巴特西

MongoDB聚合查询及Python连接MongoDB操作

今日内容概要

今日内容详细

$group补充

随机获取

可视化软件

python代码操作MongoDB

最新文章

热门文章