MongoDB基本操作

#1、插入单条数据
db.student1.insertOne({_id:"stu001","name":"Tom","age":25,grade:{"chinese":80,"math":90,"english":88}})

#2、插入多条数据
db.student1.insertMany([
{_id:"stu002","name":"Mary","age":23,grade:{"chinese":80,"math":90}},
{_id:"stu003","name":"Mike","age":23,grade:{"chinese":81,"math":90,"english":88}}
]);

#1、更新_id=7839的薪水 ---> 8000
db.emp.updateOne({"_id":7839},{$set:{"sal":8000}})
db.emp.find({"_id":7839})  # 查询id=7893的文档

#2、更新多条数据：更新10号部门的员工薪水，加100块钱
错误：db.emp.updateMany({"deptno":{$eq:10}},{$set:{"sal":"sal"+100}})  ---> 不对
正确：db.emp.updateMany({"deptno":{$eq:10}},{$inc:{"sal",100}})

db.emp.deleteOne({"_id":7839})

db.mystudents.bulkWrite([
{insertOne:{"document":{"_id":100,"name":"Tom","age":25}}},
{insertOne:{"document":{"_id":101,"name":"Mary","age":24}}},
{updateOne:{"filter":{"_id":100},"update":{$set:{"name":"Tom123"}}}}
]);

#1、查询所有的员工信息
db.emp.find()

#2、查询职位值经理的员工
db.emp.find({"job":"MANAGER"})

#3、操作符：$in和$or
#查询职位是MANAGER或者是CLERK员工信息
db.emp.find({"job":{$in:["MANAGER","CLERK"]}})
db.emp.find({$or:[{"job":"MANAGER"},{"job":"CLERK"}]})

#4、查询10号部门工资大于2000的员工
db.emp.find({"sal":{$gt:2000},"deptno":10})

#1、查询语文是81分，英语成绩是88分的文档
db.student2.find({grade:{chinese:81,english:88}})   ---> 得不到结果

#2、查询语文是81分，数学90分，英语成绩是88分的文档
db.student2.find({grade:{chinese:81,math:90,english:88}})  ---> 得到结果
# { "_id" : "stu0003", "name" : "Mike", "age" : 25, "grade" : { "chinese" : 81, "math" : 90, "english" : 88 } }
小结：如果是相等查询，保证匹配所有的field，顺序也要一样
    
#3、查询嵌套文档中的一个列：查询数学成绩是82分的文档
db.student2.find({"grade.math":82})
    
#4、使用比较运算符：查询英语成绩大于88分文档
db.student2.find({"grade.english":{$gt:88}})
    
#5、使用AND运算符：查询英语成绩大于88分，语文成绩大于85分的文档
db.student2.find({"grade.english":{$gt:88},"grade.chinese":{$gt:85}})

#1、查询Java有4本的文档
db.studentbook1.find({books:{"bookname":"Java","quantity":4}})
'''
{
    "_id": "stu003",
    "name": "Mike",
    "books": [{
        "bookname": "Java",
        "quantity": 4
    }, {
        "bookname": "MySQL",
        "quantity": 1
    }, {
        "bookname": "PHP",
        "quantity": 1
    }]
}
'''

#2、指定查询的条件：查询数组中第一个元素大于3本的文档
db.studentbook1.find({"books.0.quantity":{$gt:3}})
'''
{
    "_id": "stu002",
    "name": "Mary",
    "books": [{
        "bookname": "C++",
        "quantity": 4
    }, {
        "bookname": "Java",
        "quantity": 3
    }, {
        "bookname": "Oracle",
        "quantity": 5
    }]
}
'''
                
#3、如果不知道field的位置: 查询文档中至少有一个quantity的值大于3
db.studentbook1.find({"books.quantity":{$gt:3}})

#4、查询Java等于4本的文档
db.studentbook1.find({"books":{$elemMatch:{"bookname":"Java","quantity":4}}})
'''
{
    "_id": "stu003",
    "name": "Mike",
    "books": [{
        "bookname": "Java",
        "quantity": 4
    }, {
        "bookname": "MySQL",
        "quantity": 1
    }, {
        "bookname": "PHP",
        "quantity": 1
    }]
}
'''

db.student3.insertMany([
   { _id: 1,name:"Tom",age:null },
   { _id: 2,name:"Mary"}
])

#1、查询值为null的文档
db.student3.find({age:null})  ---> 返回两条记录
'''
{ "_id" : 1, "name" : "Tom", "age" : null }
{ "_id" : 2, "name" : "Mary" }
'''

#2、只返回null的记录：BSON表示null：10
db.student3.find({"age":{$type:10}})
'''
{ "_id" : 1, "name" : "Tom", "age" : null }
'''
                
#3、检查是否缺失某个列
db.student3.find({age:{$exists:false}})
db.student3.find({age:{$exists:true}})

#1、定义游标
var mycursor = db.emp.find()
mycursor

#2、使用游标访问文档（打印json格式数据）
var mycursor = db.emp.find()
while(mycursor.hasNext()){
printjson(mycursor.next())
}    

#3、游标和数组
var mycursor = db.emp.find()                       # 定义一个游标
var myarray = mycursor.toArray()                   # 将查询结果转换成数组
var mydoc = myarray[3]                             # 取出数组中第3条数据

#4、分页操作
第一页： limit表示查询过滤出前5条数据
var mycursor = db.emp.find().limit(5)
第二页： skip(5)表示跳过多少条数据
var mycursor = db.emp.find().limit(5).skip(5)

$match: 过滤进入PipeLine的数据
$project:指定提取的列，其中： 1表示提取列  0不提取
#查询部门id=10，只显示ename、sal、deptno
db.emp.aggregate(
{$match:{"deptno":{$eq:10}}},
{$project:{"ename":1,"sal":1,"deptno":1}}
);    
'''
{ "_id" : 7782, "ename" : "CLARK", "sal" : 2450, "deptno" : 10 }
{ "_id" : 7839, "ename" : "KING", "sal" : 8000, "deptno" : 10 }
{ "_id" : 7934, "ename" : "MILLER", "sal" : 1300, "deptno" : 10 }
'''

db.emp.aggregate(
{$project:{"sal":1,"deptno":1}},
{$group:{"_id":"$deptno",salTotal:{$sum:"$sal"}}}
);

'''
{ "_id" : 10, "salTotal" : 11750 }
{ "_id" : 30, "salTotal" : 9400 }
{ "_id" : 20, "salTotal" : 10875 }
'''

#select deptno,job,sum(sal) from emp group by deptno,job;
db.emp.aggregate(
{$project:{"job":1,"sal":1,"deptno":1}},
{$group:{"_id":{"deptno":"$deptno","job":"$job"},salTotal:{$sum:"$sal"}}}
);    
'''
{ "_id" : { "deptno" : 20, "job" : "ANALYST" }, "salTotal" : 6000 }
{ "_id" : { "deptno" : 30, "job" : "SALESMAN" }, "salTotal" : 5600 }
{ "_id" : { "deptno" : 20, "job" : "CLERK" }, "salTotal" : 1900 }
'''