数据分析-mongodb语句5.28

昨日内容回顾

• mongodb安装

  细心的一步步点击即可
  
  # 搜索网上安装教程即可
  

• 文件准备

  data文件夹
  	db文件夹
  log文件
  	mongod.log日志文件
  mongod.cfg配置文件
  

• 基本NoSQL语句

  mongo
  show dbs
  exit /quit()
  

• 用户管理

  针对不同的用户可以创建不同的账号以及不同的权限
  用户的账号信息可以保存在任意的库下没有规定固定
  db.createUser({})
  db.auth({})
  

• 库、集合、文档增删改查

  show dbs
  use db1
  db.dropDatabase()
  
  db.createCollection()
  use userinfo
  show tables
  db.userinfo.drop()
  
  db.userinfo.insert()
  db.userinfo.find()
  db.userinfo.update()
  db.userinfo.delete()
  

• 运算符

  比较运算符
  	$gt $lt $gte $lte $ne $not
  逻辑运算符
  	, (and)  $or
      
      db.userinfo.find({'name':'jason','pwd':123})
      db.userinfo.find({
          '$or':[
              {'name':'jason'},
              {'pwd':123}
          ]
      })
  

今日内容概要

• 聚合查询

• 分组查询

• 查询关键字补充

• 多表查询练习题

• mongodb可视化软件(类似于MySQL的Navicat)

• python操作mongodb(类似于pymysql模块操作MySQL)

  pymongo模块
  

今日内容详细

成员运算

# 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'}}]})

正则匹配

用一些覅好符号的组合去文本中筛选出符合条件的数据

# 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 } } ]
				)

前期代码运行可能会报错

1.py:41报错
    有可能是因为你已经提前创建了emp集合并且有一些数据导致
    解决方案:将emp集合删除再次运行即可
    
2.authentication failed
	就是由于账户管理和权限没有配置好导致
    解决方案:设置对应的账户与权限
   
3.No module named pymongo
	缺少操作mongodb的python模块pymongo
    解决方案:pip3 install pymongo

课堂练习

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

pymongo模块

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

1.下载
	pip3 install pymongo
2.导入
	import pymongo
    from pymongo import MongoClient
    
3.基本使用

可视化软件

网址:https://robomongo.org/
我们之前学习使用的Navicat其实也可以连接mongodb并操作

下载一步步点击安装即可

点击右上角连接按钮
	输入对应的库和账户信息连接即可

作业

1.自己独立完成数据库的安装并创建一个管理员账户三个普通用户账户
	三个普通账户各自权限都不一致(自定义)
2.将所有的查询操作自己手动来一遍
	最好是MySQL与MongoDB都自己写(不要看笔记)
3.将今日笔记抄写一遍
4.将分组相关的练习题独立完成
	每个部门最大薪资与最低薪资
    求每个部门的总工资
    求每个部门的人数
    查询岗位名以及各岗位内的员工姓名
    
5.将上述11道练习题脱稿用MySQL和MongoDB分别实现
# 上述两道题每个人操作完成后截图放到一个文档里面打包发送到群里  
6.远程控制电脑软件
	向日葵

可视化软件

网址:https://robomongo.org/
我们之前学习使用的Navicat其实也可以连接mongodb并操作

下载一步步点击安装即可

点击右上角连接按钮
	输入对应的库和账户信息连接即可

作业

1.自己独立完成数据库的安装并创建一个管理员账户三个普通用户账户
	三个普通账户各自权限都不一致(自定义)
2.将所有的查询操作自己手动来一遍
	最好是MySQL与MongoDB都自己写(不要看笔记)
3.将今日笔记抄写一遍
4.将分组相关的练习题独立完成
	每个部门最大薪资与最低薪资
    求每个部门的总工资
    求每个部门的人数
    查询岗位名以及各岗位内的员工姓名
    
5.将上述11道练习题脱稿用MySQL和MongoDB分别实现
# 上述两道题每个人操作完成后截图放到一个文档里面打包发送到群里  
6.远程控制电脑软件
	向日葵