🍭 MongoDB Query Language，是MongoDB数据库的查询语言，类似于SQL，是针对于文档数据库的设计，支持丰富的查询操作。

1 MQL

MQL
- MongoDB数据库下载：https://www.mongodb.com/try/download/community。
- MongoDB数据库，自带UI界面可操作，或使用NoSQLBooster for MongoDB。
- 提供图形化和命令行界面，使用户能轻松连接、管理和操作MongoDB数据库。
- 表达式
  - 等于(:·····=)、不等于($ne········!=)。
  - 小于($lt···<)、小于等于($lte·····<=)。
  - 大于($gt···>)、大于等于($gte·····>=)。
  - 包含($in···in())、不包含($nin····not in())。

SQL	MongoDB	解释/说明
database	database	数据库
table	collection	数据表/集合
row	document	数据记录行/文档
column	field	数据字段(列)/域
index	index	索引
primary key	primary key	主键，MongoDB自动将_id字段设置为主键

2 数据创建

// 创建数据表
create table people1(
  id mediumint not null
  auto_increment,
  user_id varchar(30),
  age int,
  status char(1),
  primary key (id)
);

// 插入数据
insert into people1(user_id, age, status) values ("sql001", 20, "A");
insert into people1(user_id, age, status) values ("sql002", 21, "B");
insert into 
  people1(user_id, age, status) 
values 
  ("sql003", 20, "C"),
  ("sql004", 20, "D"),
  ("sql005", 22, "B"),
  ("sql116", 19, "B"),
  ("sql117", 23, "B");

// 使用数据库dbtest
use dbtest

// mongodb不用创建表，如果没有插入数据的指定文档
// 则数据库会自动创建，也可以先创建文档再插入数据
db.people1.insertOne(
  {user_id: "mql001", age: 20, status: "A"}
);
db.people1.insertOne(
  {user_id: "mql002", age: 21, status: "B"}
);
db.people1.insertMany([
  {user_id: "mql003", age: 20, status: "C"},
  {user_id: "mql004", age: 20, status: "D"},
  {user_id: "mql005", age: 22, status: "B"},
  {user_id: "mql116", age: 19, status: "B"},
  {user_id: "mql117", age: 23, status: "B"}
]);

3 数据查询

1	sql001	20	A
2	sql002	21	B
3	sql003	20	C
4	sql004	20	D
5	sql005	22	B
6	sql116	19	B
7	sql117	23	B

MySQL数据表people1如上，MongoDB数据表people1如下。

[
  {
    "_id": {
      "$oid": "6745827854fbcbc1330904be"
    },
    "user_id": "mql001",
    "age": 20,
    "status": "A"
  },
  {
    "_id": {
      "$oid": "6745828954fbcbc1330904bf"
    },
    "user_id": "mql002",
    "age": 21,
    "status": "B"
  },
  {
    "_id": {
      "$oid": "6745828e54fbcbc1330904c0"
    },
    "user_id": "mql003",
    "age": 20,
    "status": "C"
  },
  {
    "_id": {
      "$oid": "6745828e54fbcbc1330904c1"
    },
    "user_id": "mql004",
    "age": 20,
    "status": "D"
  },
  {
    "_id": {
      "$oid": "6745828e54fbcbc1330904c2"
    },
    "user_id": "mql005",
    "age": 22,
    "status": "B"
  },
  {
    "_id": {
      "$oid": "6745828e54fbcbc1330904c3"
    },
    "user_id": "mql116",
    "age": 19,
    "status": "B"
  },
  {
    "_id": {
      "$oid": "6745828e54fbcbc1330904c4"
    },
    "user_id": "mql117",
    "age": 23,
    "status": "B"
  }
]

3-1 简单查询

select * from people1;
db.people1.find();
db.people1.find({});

select id, user_id, status from people1;
// 第一个{}不能去掉，表示筛选条件
// 后一个{}中，1代表显示，0不显示，_id默认显示
db.people1.find(
  {}, 
  {user_id: 1, status: 1}
);

select user_id, status from people1;
db.people1.find(
  {}, 
  {user_id: 1, status: 1, _id: 0}
);

3-2 条件查询

select * from people1 where status = "A";
db.people1.find(
  {status: "A"}
);

// 不返回_id
select user_id, status from people1 where status = "A";
db.people1.find(
  {status: "A"},
  {user_id: 1, status: 1, _id: 0}
);

select * from people1 where status != "A";
db.people1.find(
  {status: {$ne: "A"}}
);

select * from people1 where status in ("A", "C");
db.people1.find(
  {status: {$in:["A", "D"]}}
);

select * from people1 where status = "A" and age < 21;
db.people1.find(
  {status: "B", age: {$lt: 21}}
);

select * from people1 where status = "A" and age = 20;
db.people1.find(
  {status: "A", age: 20}
);

select * from people1 where status = "A" or age = 20;
// MongoDB使用的是Bson格式，Bson是Json的一个升级版
db.people1.find(
  {$or: [{status: "A"}, {age: 20}]}
);

select * from people1 where age > 20;
db.people1.find(
  {age: {$gt: 20}}
);

select * from people1 where age <= 20;
db.people1.find(
  {age: {$lte: 20}}
);

select * from people1 where age > 20 and age <= 22;
db.people1.find(
  {age: {$gt: 20, $lte: 22}}
);

3-3 模糊查询

select * from people1 where user_id like "%11%";
db.people1.find(
  {user_id: /11/}
);
db.people1.find(
  {user_id: {$regex: /11/}} 
);

select * from people1 where user_id like "sql1%";
// “/”指表达式的开始或结束，“^”表示字符串的开头
db.people1.find(
  {user_id: /^mql1/}
);
db.people1.find(
  {user_id: {$regex: /^mql1/}} 
);

// 去重
select distinct(status) from people1;
db.people1.aggregate(
  [{$group: {_id: "$status"}}]
);
db.people1.distinct("status");

3-4 排序查询

// asc升序排序
select * from 
  people1 
where 
  status = "B" 
order by 
  user_id asc;

// “1”升序，“-1”降序
db.people1.find(
  {status: "B"}
).sort({user_id: 1});

3-5 聚合函数

select count(*) from people1;
db.people1.count();
db.people1.find().count();

select count(user_id) from people1;
db.people1.count(
  {user_id: {$exists: true}}
);
db.people1.find(
  {user_id: {$exists: true}}
).count();

select count(*) from people1 where age > 20;
db.people1.count(
  {age: {$gt: 20}}
);
db.people1.find(
  {age: {$gt: 20}}
).count();

3-6 分页查询

// 获取指定条数
select * from people1 limit 1;
db.people1.findOne();
db.people1.find().limit(1);

// 从索引值3开始，获取2条数据
select * from people1 limit 3, 2;
db.people1.find().limit(2).skip(3);

3-7 其他子句

// 多条件查询
select * from 
  people1 
where 
  status = "A" 
and 
  (age < 21 or user_id like "%00%");

db.people1.find({
  status: "A", 
  $or:[{age: {$lt: 21}}, {user_id: "%00%"}]
});

// 查看sql执行情况
explain select * from people1 where status = "A";
db.people1.find({status: "A"}).explain();

4 数据更新

// 更改全部status为D
update people1 set status = "D";
db.people1.updateMany(
  {},
  {$set: {status: "D"}}
);

// age为20的status改为C
update people1 set status = "C" where age = 20;
db.people1.updateMany(
   {age: 20},
   {$set: {status: "C"}}
);

5 Json查询

create table people2(
  id mediumint not null
  auto_increment,
  user_id varchar(30),
  age int,
  status char(1),
  size json,
  primary key (id)
);

insert into 
	people2(user_id, age, status, size) 
values 
	("sql001", 20, "A", '{"h": 172, "w": 121, "address": "cn"}');
  
insert into 
	people2(user_id, age, status, size) 
values 
	("sql002", 21, "B", '{"h": 178, "w": 115, "address": "cn"}');
  
insert into 
  people2(user_id, age, status, size)
values 
  ("sql003", 20, "C", '{"h": 172, "w": 121, "address": "en"}'),
  ("sql004", 20, "D", '{"h": 155, "w": 86, "address": "cn"}'),
  ("sql005", 22, "B", '{"h": 168, "w": 103, "address": "en"}'),
  ("sql116", 19, "B", '{"h": 188, "w": 137, "address": "en"}'),
  ("sql117", 23, "B", '{"h": 158, "w": 92, "address": "cn"}');

5-1 字符串

// 以字符串形式存储
db.people2.insertOne({
  user_id: "mql001", 
  age: 20, 
  status: "A", 
  size: '{"h": 172, "w": 121, "address": "cn"}'
});
db.people2.insertOne({
  user_id: "mql002",
  age: 21,
  status: "B",
  size: '{"h": 178, "w": 115, "address": "cn"}'
});
db.people2.insertMany([
  {
    user_id: "mql003", age: 20, status: "C", 
    size: '{"h": 172, "w": 121, "address": "en"}'
  },
  {
    user_id: "mql004", age: 20, status: "D",
    size: '{"h": 155, "w": 86, "address": "cn"}'
  },
  {
    user_id: "mql005", age: 22, status: "B",
    size: '{"h": 168, "w": 103, "address": "en"}'
  },
  {
    user_id: "mql116", age: 19, status: "B",
    size: '{"h": 188, "w": 137, "address": "en"}'
  },
  {
    user_id: "mql117", age: 23, status: "B",
    size: '{"h": 158, "w": 92, "address": "cn"}'
  }
]);

5-2 Json形式

// 以Json形式存储
db.people3.insertOne({
  user_id: "mql001", 
  age: 20, 
  status: "A", 
  size: {h: 172, w: 121, address: "cn"}
});
db.people3.insertOne({
  user_id: "mql002",
  age: 21,
  status: "B",
  size: {h: 178, w: 115, address: "cn"}
});
db.people3.insertMany([
  {
    user_id: "mql003", age: 20, status: "C", 
    size: {h: 172, w: 121, address: "en"}
  },
  {
    user_id: "mql004", age: 20, status: "D",
    size: {h: 155, w: 86, address: "cn"}
  },
  {
    user_id: "mql005", age: 22, status: "B",
    size: {h: 168, w: 103, address: "en"}
  },
  {
    user_id: "mql116", age: 19, status: "B",
    size: {h: 188, w: 137, address: "en"}
  },
  {
    user_id: "mql117", age: 23, status: "B",
    size: {h: 158, w: 92, address: "cn"}
  }
]);

5-3 数据查询

// MySQL使用JSON_CONTAINS函数查询Json数据
select * from 
	people2
where
	json_contains(size, '{"address": "cn"}');
  
// 字符串形式存储，使用正则表达式来匹配部分内容
// 注意：单双引号的书写，可能也影响查询到的结果
db.people2.find({size: /"address": "cn"/});

// 以Json形式存储
db.people3.find({"size.address": "cn"});

(1) 点连接

select * from 
	people2
where 
	json_contains(size, '{"h": 158, "w": 92, "address": "cn"}');
  
// 以字符串形式存储
db.people2.find(
  {size: /"h": 158, "w": 92, "address": "cn"/}
);

// 以Json形式存储
db.people3.find({
  "size.h": 158,
  "size.w": 92,
  "size.address": "cn"
});

// MongoDB中，单引号用于分隔字符串
// 字段名、字符串值、嵌入式文档的键（如：size.address）必须使用双引号括起来
db.people3.find(
  {size: {h: 158, w: 92, address: "cn"}}
);

// 顺序乱掉，就查不到数据了
db.people3.find(
  {size: {w: 92, h: 158, address: "cn"}}
);

// 少属性值，也查询不到数据
db.people3.find(
  {size: {h: 158, w: 92}}
);

(2) 表达式

// 使用JSON_EXTRACT函数查询Json数据
select * from
  people2
where json_extract(size, "$.h") < 170;

// 以字符串形式存储，不推荐使用，效率不高
db.people2.find({
  "size": {$regex: /"h":\s*(\d+)/, $options: "i"},
  $where: function() { 
    var match = this.size.match(/"h":\s*(\d+)/);
    return match && parseInt(match[1]) < 170;
  }
});

// 以Json形式存储，size中的h属性小于170
db.people3.find(
  {"size.h": {$lt: 170}}
);

select * from
  people2
where
  size regexp '"h":\\s*\\d+'
and 
  cast(json_extract(size, "$.h") as unsigned) < 170
and 
  json_contains(size, '"cn"', "$.address")
and status = "D";

// 字符串形式存储
db.people2.find({
  "size": {$regex: /"h"\s*:\s*(\d+)/, $options: "i"},
  $where: function() {
    var hMatch = this.size.match(/"h"\s*:\s*(\d+)/);
    var addressMatch = this.size.match(/"address"\s*:\s*"([^"]+)"/);
    return hMatch && parseInt(hMatch[1]) < 170 
      && addressMatch && addressMatch[1].toLowerCase() === "cn" 
      && this.status === "D";
  }
});

// 以Json形式存储
db.people3.find(
  {
  "size.h": {$lt: 170},
  "size.address": "cn", status: "D" 
  }
);

6 数组查询

数组查询
- 使用SQL查询语句的传统关系数据库，均不支持直接存储数组。
- 关系数据库中的字段只能存储单个标量值，如字符串、日期等。
- 但可使用变通方法存储，如：序列化数组、创建多表、使用NoSQL数据库等。

6-1 数据定义

db.people4.insertMany([
  {
    user_id: "mql001", age: 20, status: "A", 
    size: {h: 172, w: 121, address: "cn"},
    score: [97, 91, 93, 95, 100, 99, 99]
  },
  {
    user_id: "mql002", age: 21, status: "B",
    size: {h: 178, w: 115, address: "cn"},
    score: [89, 88, 93, 90, 84, 85, 95]
  },
  {
    user_id: "mql003", age: 20, status: "C", 
    size: {h: 172, w: 121, address: "en"},
    score: [97, 91, 93, 95, 100, 99, 99]
  },
  {
    user_id: "mql004", age: 20, status: "D",
    size: {h: 155, w: 86, address: "cn"},
    score: [67, 78, 75, 77, 83, 76, 71]
  },
  {
    user_id: "mql005", age: 22, status: "B",
    size: {h: 168, w: 103, address: "en"},
    score: [88, 91, 85, 89, 83, 90, 80]
  },
  {
    user_id: "mql116", age: 19, status: "B",
    size: {h: 188, w: 137, address: "en"},
    score: [86, 87, 83, 80, 81, 85, 88]
  },
  {
    user_id: "mql117", age: 23, status: "B",
    size: {h: 158, w: 92, address: "cn"},
    score: [90, 91, 83, 81, 87, 79, 81]
  }
]);

6-2 查询操作*

// 详细查询：必须指定相等条件，包括数组顺序、元素个数
db.people4.find(
  {score: [86, 87, 83, 80, 81, 85, 88]}
);

// 包含查询：查询score域中包含[88, 89]元素的数据
db.people4.find(
  {score: {$all: [88, 89]}}
);

// 查询score域中大于90的所有数据
// 为什么会获取到小于90的值？？？
db.people4.find(
  {score: {$gt: 90}}
);

7 聚合管道

聚合管道
- MongoDB提供三种执行聚合的方法：Pipeline、Reference、Map-Reduce。
- 聚合管道，即Aggregation Pipeline，则使用的是aggregate()方法。
- 其余表达式参看“MongoDB 官方文档 Aggregation Pipeline 说明”。

7-1 筛选查询

// 查询status="B"的所有数据
db.people4.aggregate(
    [{$match: {status: "B"}}]
);

// age大于20，并且小于23，或status="A"的所有数据
db.people4.aggregate(
    [{$match: {$or: [{age: {$gt: 20, $lt: 23}}, {status: "A"}]}}]
);

7-2 分组查询

select count(*) as count from people2;

// _id: null没有指定具体的字段
// count: {$sum: 1}查询的条数
db.people4.aggregate([
  {$group: {_id: null, count: {$sum: 1}}}
]);

// 根据age字段分组
db.people4.aggregate([
  {$group: {_id: "$age"}}
]);

7-3 组合查询

db.people4.aggregate([
  // 先筛选
  {$match: {age: {$gt: 19, $lt: 23}}},
  // 再分组
  {$group: {_id: "$status"}},
  // 最后升序排序
  {$sort: {"_id": 1}}
]);

数据库

#MongoDB #数据库 #MongoDBCompass #MQL

MQL

https://stitch-top.github.io/2024/11/28/shu-ju-ku/db02-mql/

作者

Dr.626

发布于

2024年11月28日 00:25:30

许可协议

Python Tkinter(一) 上一篇

SQL 下一篇