05.复合查询
# 01.基本查询
# 1.1 创建emp表并插入14条数据
db.emp.insert(
[
{_id:7369,ename:'SMITH' ,job:'CLERK' ,mgr:7902,hiredate:'17-12-80',sal:800,comm:0,deptno:20},
{_id:7499,ename:'ALLEN' ,job:'SALESMAN' ,mgr:7698,hiredate:'20-02-81',sal:1600,comm:300 ,deptno:30},
{_id:7521,ename:'WARD' ,job:'SALESMAN' ,mgr:7698,hiredate:'22-02-81',sal:1250,comm:500 ,deptno:30},
{_id:7566,ename:'JONES' ,job:'MANAGER' ,mgr:7839,hiredate:'02-04-81',sal:2975,comm:0,deptno:20},
{_id:7654,ename:'MARTIN',job:'SALESMAN' ,mgr:7698,hiredate:'28-09-81',sal:1250,comm:1400,deptno:30},
{_id:7698,ename:'BLAKE' ,job:'MANAGER' ,mgr:7839,hiredate:'01-05-81',sal:2850,comm:0,deptno:30},
{_id:7782,ename:'CLARK' ,job:'MANAGER' ,mgr:7839,hiredate:'09-06-81',sal:2450,comm:0,deptno:10},
{_id:7788,ename:'SCOTT' ,job:'ANALYST' ,mgr:7566,hiredate:'19-04-87',sal:3000,comm:0,deptno:20},
{_id:7839,ename:'KING' ,job:'PRESIDENT',mgr:0,hiredate:'17-11-81',sal:5000,comm:0,deptno:10},
{_id:7844,ename:'TURNER',job:'SALESMAN' ,mgr:7698,hiredate:'08-09-81',sal:1500,comm:0,deptno:30},
{_id:7876,ename:'ADAMS' ,job:'CLERK' ,mgr:7788,hiredate:'23-05-87',sal:1100,comm:0,deptno:20},
{_id:7900,ename:'JAMES' ,job:'CLERK' ,mgr:7698,hiredate:'03-12-81',sal:950,comm:0,deptno:30},
{_id:7902,ename:'FORD' ,job:'ANALYST' ,mgr:7566,hiredate:'03-12-81',sal:3000,comm:0,deptno:20},
{_id:7934,ename:'MILLER',job:'CLERK' ,mgr:7782,hiredate:'23-01-82',sal:1300,comm:0,deptno:10}
]
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 1.2 基本查询
#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
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
# 02.查询嵌套文档
# 2.1 插入多条数据
db.student2.insertMany([
{_id:"stu0001",name:"Mary",age:25,grade:{chinese:80,math:85,english:90}},
{_id:"stu0002",name:"Tom",age:25,grade:{chinese:86,math:82,english:95}},
{_id:"stu0003",name:"Mike",age:25,grade:{chinese:81,math:90,english:88}},
{_id:"stu0004",name:"Jerry",age:25,grade:{chinese:95,math:87,english:89}}
])
1
2
3
4
5
6
2
3
4
5
6
# 2.2 查询嵌套文档
#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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 03.查询数组文档
# 3.1 插入数据
db.studentbook.insert([
{_id:"stu001",name:"Tom",books:["Hadoop","Java","NoSQL"]},
{_id:"stu002",name:"Mary",books:["C++","Java","Oracle"]},
{_id:"stu003",name:"Mike",books:["Java","MySQL","PHP"]},
{_id:"stu004",name:"Jerry",books:["Hadoop","Spark","Java"]},
{_id:"stu005",name:"Jone",books:["C","Python"]}
])
1
2
3
4
5
6
7
2
3
4
5
6
7
# 3.2 查询数组文档
#1、查询所有有Hadoop和Java的文档
错误：db.studentbook.find({books:["Hadoop","Java"]}) ---> 没有结果
正确：db.studentbook.find({books:{$all:["Hadoop","Java"]}})
'''
{ "_id" : "stu001", "name" : "Tom", "books" : [ "Hadoop", "Java", "NoSQL" ] }
{ "_id" : "stu004", "name" : "Jerry", "books" : [ "Hadoop", "Spark", "Java" ] }
'''
#2、根查询嵌套的文档一样，匹配每个元素，顺序也要一致
db.studentbook.find({books:["Hadoop","Java","NoSQL"]})
'''
{ "_id" : "stu001", "name" : "Tom", "books" : [ "Hadoop", "Java", "NoSQL" ] }
'''
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
# 04.查询数组中嵌套的文档
# 4.1 插入数据
db.studentbook1.insertMany([
{_id:"stu001",name:"Tome",books:[{"bookname":"Hadoop", quantity:2},{"bookname":"Java", quantity:3},{"bookname":"NoSQL", quantity:4}]},
{_id:"stu002",name:"Mary",books:[{"bookname":"C++", quantity:4}, {"bookname":"Java", quantity:3},{"bookname":"Oracle", quantity:5}]},
{_id:"stu003",name:"Mike",books:[{"bookname":"Java", quantity:4}, {"bookname":"MySQL", quantity:1},{"bookname":"PHP", quantity:1}]},
{_id:"stu004",name:"Jone",books:[{"bookname":"Hadoop", quantity:3},{"bookname":"Spark", quantity:2},{"bookname":"Java", quantity:4}]},
{_id:"stu005",name:"Jane",books:[{"bookname":"C", quantity:1}, {"bookname":"Python", quantity:5}]}
])
1
2
3
4
5
6
7
2
3
4
5
6
7
# 4.2 查询数组中嵌套文档
#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
}]
}
'''
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
# 05.查询空值null或者缺失的列
# 5.1 插入数据
db.student3.insertMany([
{ _id: 1,name:"Tom",age:null },
{ _id: 2,name:"Mary"}
])
1
2
3
4
2
3
4
# 5.2 查询空值null或者缺失的列
#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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 06.使用游标
#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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
上次更新: 2024/10/15 16:27:13