MongoDB Compass&Shell SQL语句[2]
一、字符串匹配
在表tableName 中查询列(timestamp_string)：2022-11-09 这一天的所有数据
SQL语句：
select * from tableName where timestamp_string like '2022-11-09%'
Mongodb Compass语句：
{timestamp_string:{$regex: /^2022-11-09/}}
扩展语句:
{timestamp_string:{$regex: /^2022-11-09/,"$options" : "i"}}
二、排序
在表tableName 中查询列(timestamp_string)：2022-11-09 这一天的所有数据，并且按照timestamp_string排序
SQL语句：
select * from tableName where timestamp_string like '2022-11-09%' order by timestamp_string
Mongodb语句：
{timestamp_string:{$regex: /^2022-11-09/}}
{timestamp_string:1}
三、检索指定列
检索学生的姓名、年龄、性别，条件：学生表中数学小于90 且 英语小于等于81
SQL语句：
select name,age,sex from student where Math < 90 and English <= 81
Mongodb语句:
{Math:{$lt:90},English:{$lte:81}}
{name:1,age:1,sex:1}
四、索引
关于MongoDB Index请查询官网：https://www.mongodb.com/docs/manual/indexes/
Indexes support the efficient execution of queries in MongoDB. Without indexes, MongoDB must perform a collection scan, i.e. scan every document in a collection, to select those documents that match the query statement. If an appropriate index exists for a query, MongoDB can use the index to limit the number of documents it must inspect.
在MongoDB中，索引支持查询语句的高效执行。在没有索引的情况下，MongoDB必须执行整张表的扫描，例如，为了查找那些符合匹配查询语句的记录，需要在一张表中扫描每一条记录，
Indexes are special data structures [1] that store a small portion of the collection's data set in an easy to traverse form. The index stores the value of a specific field or set of fields, ordered by the value of the field. The ordering of the index entries supports efficient equality matches and range-based query operations. In addition, MongoDB can return sorted results by using the ordering in the index.
索引是特殊的数据结构，这种数据结构存储为便于表容易遍历的很小一部分。索引存储一个特定的域或设置的域的值，这个索引更具域的值进行排序。这个经过排序的索引条目高效的支持相等匹配和基于范围的查询操作。另外，MongoDB能通过索引中的排序来返回存储的结果。
[1] MongoDB 索引是基于B树数据结构实现的
五、结果集去重
新建Collection：orders，并批量插入数据
db.orders.insertMany( [
{ _id: 0, name: "Pepperoni", size: "small", price: 19,
quantity: 10, date: ISODate( "2021-03-13T08:14:30Z" ) },
{ _id: 1, name: "Pepperoni", size: "medium", price: 20,
quantity: 20, date : ISODate( "2021-03-13T09:13:24Z" ) },
{ _id: 2, name: "Pepperoni", size: "large", price: 21,
quantity: 30, date : ISODate( "2021-03-17T09:22:12Z" ) },
{ _id: 3, name: "Cheese", size: "small", price: 12,
quantity: 15, date : ISODate( "2021-03-13T11:21:39.736Z" ) },
{ _id: 4, name: "Cheese", size: "medium", price: 13,
quantity:50, date : ISODate( "2022-01-12T21:23:13.331Z" ) },
{ _id: 5, name: "Cheese", size: "large", price: 14,
quantity: 10, date : ISODate( "2022-01-12T05:08:13Z" ) },
{ _id: 6, name: "Vegan", size: "small", price: 17,
quantity: 10, date : ISODate( "2021-01-13T05:08:13Z" ) },
{ _id: 7, name: "Vegan", size: "medium", price: 18,
quantity: 10, date : ISODate( "2021-01-13T05:10:13Z" ) },
{ _id: 8, name: "Vegan", size: "medium", price: 18,
quantity: 10, date : ISODate( "2021-01-13T05:10:13Z" ) }
] )
🔸 查询要求：过滤出{size="medium"}的数据
下图查询结果，显示有重复数据，如果只让 name 中同样的只出现一次，如果解决？
🔸 查询要求：过滤出{size="medium"}的数据，并根据 name 去重
MongoCompass中查询:
MongoShell中查询命令：
db.orders.aggregate( [
// Stage 1: Filter pizza order documents by pizza size
{
$match: { size: "medium" }
},
// Stage 2: Group remaining documents by pizza name
{
$group: { _id: "$name" }
}
] )
