nosql练习

MongoDB 基本操作作业

1. 创建一个数据库 名字grade 
> use grade
switched to db grade

2. 数据库中创建一个集合名字 class
> db.createCollection("class")
{ "ok" : 1 }

3. 集合中插入若干数据 文档格式如下
｛name:'zhang',age；10,sex:'m',hobby:['a','b','c']｝
hobby: draw  sing  dance  basketball football  pingpong
       computer 

db.class.insert([
{name:"zhang",age:10,sex:'m',hobby:['a','b','c']},
.....
])

> db.class.insert([
... {name:"zhang",age:10,sex:'m',hobby:['a','b','c']},
... {name:"liu",age:20,sex:'F',hobby:['draw','sing']},
... {name:"王五",age:20,sex:'F',hobby:['dance','sing']},
... {name:"小红",age:30,sex:'F',hobby:['sing']},
... {name:"小明",age:20,sex:'F',hobby:['football','pingpang','baskerball']},
... {name:"小李",age:20,sex:'F',hobby:['computer']},
... {name:"小黑子",age:2.5,sex:'M',hobby:['sing','dance','rap','basketball']},
... ])
BulkWriteResult({
    "writeErrors" : [ ],
    "writeConcernErrors" : [ ],
    "nInserted" : 7,
    "nUpserted" : 0,
    "nMatched" : 0,
    "nModified" : 0,
    "nRemoved" : 0,
    "upserted" : [ ]
})

4. 查找练习
查看班级所有人信息
> db.class.find()
{ "_id" : ObjectId("64b26387994c887337fa1bfa"), "name" : "zhang", "age" : 10, "sex" : "m", "hobby" : [ "a", "b", "c" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bfb"), "name" : "liu", "age" : 20, "sex" : "F", "hobby" : [ "draw", "sing" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bfc"), "name" : "王五", "age" : 20, "sex" : "F", "hobby" : [ "dance", "sing" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bfd"), "name" : "小红", "age" : 30, "sex" : "F", "hobby" : [ "sing" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bfe"), "name" : "小明", "age" : 20, "sex" : "F", "hobby" : [ "football", "pingpang", "baskerball" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bff"), "name" : "小李", "age" : 20, "sex" : "F", "hobby" : [ "computer" ] }
{ "_id" : ObjectId("64b26387994c887337fa1c00"), "name" : "小黑子", "age" : 2.5, "sex" : "M", "hobby" : [ "sing", "dance", "rap", "basketball" ] }
> 

查看班级中年龄为2.5岁的学生信息
> db.class.find({age:2.5})
{ "_id" : ObjectId("64b26387994c887337fa1c00"), "name" : "小黑子", "age" : 2.5, "sex" : "M", "hobby" : [ "sing", "dance", "rap", "basketball" ] }

查看年龄大于10岁的学生信息
> db.class.find({age:{$gt:10}})
{ "_id" : ObjectId("64b26387994c887337fa1bfb"), "name" : "liu", "age" : 20, "sex" : "F", "hobby" : [ "draw", "sing" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bfc"), "name" : "王五", "age" : 20, "sex" : "F", "hobby" : [ "dance", "sing" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bfd"), "name" : "小红", "age" : 30, "sex" : "F", "hobby" : [ "sing" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bfe"), "name" : "小明", "age" : 20, "sex" : "F", "hobby" : [ "football", "pingpang", "baskerball" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bff"), "name" : "小李", "age" : 20, "sex" : "F", "hobby" : [ "computer" ] }

查看年龄在 2.5---8岁之间的学生信息 
> db.class.find({age:{$gte:2.5,$lte:8}}).pretty()
{
    "_id" : ObjectId("64b26387994c887337fa1c00"),
    "name" : "小黑子",
    "age" : 2.5,
    "sex" : "M",
    "hobby" : [
        "sing",
        "dance",
        "rap",
        "basketball"
    ]
}

找到年龄为2.5岁且为男生的学生
> db.class.find({age:2.5,sex:"M"}).pretty()
{
    "_id" : ObjectId("64b26387994c887337fa1c00"),
    "name" : "小黑子",
    "age" : 2.5,
    "sex" : "M",
    "hobby" : [
        "sing",
        "dance",
        "rap",
        "basketball"
    ]
}

找到年龄小于7岁或者大于10岁的学生
> db.class.find({ $or:[{age:{$lt:7}},{age:{$gt:10}}]})
{ "_id" : ObjectId("64b26387994c887337fa1bfb"), "name" : "liu", "age" : 20, "sex" : "F", "hobby" : [ "draw", "sing" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bfc"), "name" : "王五", "age" : 20, "sex" : "F", "hobby" : [ "dance", "sing" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bfd"), "name" : "小红", "age" : 30, "sex" : "F", "hobby" : [ "sing" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bfe"), "name" : "小明", "age" : 20, "sex" : "F", "hobby" : [ "football", "pingpang", "baskerball" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bff"), "name" : "小李", "age" : 20, "sex" : "F", "hobby" : [ "computer" ] }
{ "_id" : ObjectId("64b26387994c887337fa1c00"), "name" : "小黑子", "age" : 2.5, "sex" : "M", "hobby" : [ "sing", "dance", "rap", "basketball" ] }
> 

找到年龄是2.5岁或者10岁的学生
> db.class.find({ $or:[{age:2.5},{age:10}]})
{ "_id" : ObjectId("64b26387994c887337fa1bfa"), "name" : "zhang", "age" : 10, "sex" : "m", "hobby" : [ "a", "b", "c" ] }
{ "_id" : ObjectId("64b26387994c887337fa1c00"), "name" : "小黑子", "age" : 2.5, "sex" : "M", "hobby" : [ "sing", "dance", "rap", "basketball" ] }

找到兴趣爱好有两项的学生
> db.class.find({hobby:{$size:2}})
{ "_id" : ObjectId("64b26387994c887337fa1bfb"), "name" : "liu", "age" : 20, "sex" : "F", "hobby" : [ "draw", "sing" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bfc"), "name" : "王五", "age" : 20, "sex" : "F", "hobby" : [ "dance", "sing" ] }

找到兴趣爱好有draw的学生
> db.class.find({hobby:{$in:["draw"]}})
{ "_id" : ObjectId("64b26387994c887337fa1bfb"), "name" : "liu", "age" : 20, "sex" : "F", "hobby" : [ "draw", "sing" ] }

找到既喜欢唱歌又喜欢跳舞的学生
> db.class.find({hobby:{$all:["sing","dance"]}})
{ "_id" : ObjectId("64b26387994c887337fa1bfc"), "name" : "王五", "age" : 20, "sex" : "F", "hobby" : [ "dance", "sing" ] }
{ "_id" : ObjectId("64b26387994c887337fa1c00"), "name" : "小黑子", "age" : 2.5, "sex" : "M", "hobby" : [ "sing", "dance", "rap", "basketball" ] }

统计爱好有三项的学生人数
> db.class.find({hobby:{$size:3}})
{ "_id" : ObjectId("64b26387994c887337fa1bfa"), "name" : "zhang", "age" : 10, "sex" : "m", "hobby" : [ "a", "b", "c" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bfe"), "name" : "小明", "age" : 20, "sex" : "F", "hobby" : [ "football", "pingpang", "baskerball" ] }

找出本班年龄第二大的学生
> db.class.find().sort({age:-1}).skip(1).limit(1)
{ "_id" : ObjectId("64b26387994c887337fa1bfb"), "name" : "liu", "age" : 20, "sex" : "F", "hobby" : [ "draw", "sing" ] }

查看学生的兴趣范围
db.class.distinct('hobby')
[
    "a",
    "b",
    "baskerball",
    "basketball",
    "c",
    "computer",
    "dance",
    "draw",
    "football",
    "pingpang",
    "rap",
    "sing"
]

将学生按年龄排序找到年龄最大的三个
> db.class.find().sort({age:-1}).limit(3)
{ "_id" : ObjectId("64b26387994c887337fa1bfd"), "name" : "小红", "age" : 30, "sex" : "F", "hobby" : [ "sing" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bfb"), "name" : "liu", "age" : 20, "sex" : "F", "hobby" : [ "draw", "sing" ] }
{ "_id" : ObjectId("64b26387994c887337fa1bfc"), "name" : "王五", "age" : 20, "sex" : "F", "hobby" : [ "dance", "sing" ] }

删除所有 年级大于20或者小于4岁的学生
> db.class.remove({$or:[{age:{$lt:4}},{age:{$gt:20}}]})
WriteResult({ "nRemoved" : 2 })

增加、更新、删除作业 ： 
使用之前的grade数据库
1. 将小李的年龄变为8岁 兴趣爱好变为 跳舞 画画
> db.class.find({name:"小李"})
{ "_id" : ObjectId("64b26387994c887337fa1bff"), "name" : "小李", "age" : 20, "sex" : "F", "hobby" : [ "computer" ] }
> db.class.update({name:"小李"},{$set:{age:8,hobby:["dance","draw"]}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.class.find({name:"小李"})
{ "_id" : ObjectId("64b26387994c887337fa1bff"), "name" : "小李", "age" : 8, "sex" : "F", "hobby" : [ "dance", "draw" ] }
> 

2. 追加小明兴趣爱好  唱歌
{$push:{hobby:'sing'}}
3. 小王兴趣爱好增加  吹牛 打篮球
{$pushAll:{hobby:['吹牛','basketball']}}
4. 小李增加爱好，跑步和唱歌，但是不要和以前的重复
{$addToSet:{hobby:{$each:['running','sing']}}}
5. 该班所有同学年龄加1
update({},{$inc:{age:1}},false,true)
6. 删除小明的sex属性
{$unset:{sex:0}}
7. 删除小李兴趣中的第一项
{$pop:{hobby:-1}}
8. 将小红兴趣中的画画爱好删除
{$pull:{hobby:'draw'}}

使用之前的grade数据库
增加分数域 score:{'chinese':88,'english':78,'math':98}
1. 按照性别分组统计每组人数
aggregate({$group:{_id:'$sex',num:{$sum:1}}})

2. 按照姓名分组，过滤出有重名的同学
aggregate([{$group:{_id:'$name',num:{$sum:1}}},{$match:{num:{$gt:1}}}])

3. 统计每名男生的语文成绩
aggregate([{$match:{sex:'m'}},{$project:{_id:0,name:1,'score.chinese':1}}])

4. 将女生按照英语分数降序排列
aggregate([{$match:{sex:'w'}},{$sort:{'score.english':-1}}])