preface

MongoDB is a very mainstream non-relational database at present. Its loose data structure (JSON and BSON) facilitates the storage of complex data types, powerful query syntax (with most functions of single table query in relational database), supports the creation of indexes, and has excellent query performance. For MongoDB data stores and MySQL database stores, a single piece of data can be regarded as the difference between a Map<String,Obect> and an entity object. MongoDB can store various fields and data types at will. MySQL needs to insert data fields and types defined in table design. Where applicable, this is an advantage of non-relational databases like MongoDB.

knowledge

  • Simple queries (conditional queries, paging queries, sorting, operator usage)
  • Group aggregation (single field group, multi-field group)
  • Update, delete

The query

Presentation data

user_info

Name (name) Age (age) Gender (sex) Address (address) Weight (weight)
Kim kardashian 29 female Los Angeles, 60
George 30 male Los Angeles, 80
Mr Booker 23 male phoenix 80
East qi qi 23 male Dallas 80

Note: name is the MySQL keyword. Do not use the keyword as the field name in actual development

A simple query

1 Query details

selectName, age, sex, addressfrom user_info where name = 'book' limit 1;
Copy the code
Db. User_info. Find ({" name ":" booker "}, {" name ": 1," age ": 1," sex ": 1," address ": 1}}). Limit (1);Copy the code

1.1 Multi-field Matching

selectName, age, sex, addressfrom user_info where name = 'book' and address = 'Phoenix' limit 1;
Copy the code
Db. User_info. Find ({" name ":" booker ", "address", "phoenix"}, {" name ": 1," age ": 1," sex ": 1," address ": 1}}). Limit (1);Copy the code

Monogo uses the find() method, which contains two json parameters. The first parameter is the query condition, and the second parameter specifies the key to be queried. A value of 0 means that the field is not queried, and 1 means that the field is queried. Find (query, projection) and limit(size) are used in the mongo query, which is similar to mysql select and limit. Find ({}) {find ({}) {find ({}) {find ({}) {find ({})) {find ({}) {find ({}) {find ({})) {find ({}) {find ({})) {find ({}) {find ({})) {find ({})

Db.user_info. find({},{}) or db.user_info.find() = select * from user_info

2 Paging query

selectName, age, sex, address,weightfrom user_info limit 1.1;
Copy the code
db.user_info.find({},{"name":1,"age":1,"sex":1, address":1,"weight":1}).limit(1).skip(1)
Copy the code

MongoDB needs to use limit method and skip method in combination to realize the number of queries from the number of items. Limit in MySQL is more flexible

3 Sort query

3.1 Single-field sort

SELECT name,age,sex,address,weight FROM `user_info` ORDER BY age desc
Copy the code
db.user_info.find({},{"name":1,"age":1,"sex":1, "address":1}).sort({"age":-1})
Copy the code

Sort ({“key”: -1 or 1}) sort({“key”: -1 or 1}) sort({“key”: -1 or 1}

3.2 Multi-field sorting

SELECT name,age,sex,address,weight FROM `user_info` ORDER BY age desc,weight asc
Copy the code
db.user_info.find({},{"name":1,"age":1,"sex":1, "address":1,"weight":1}).sort({"age":-1,"weight":1})
Copy the code

4 Fuzzy Query

SELECT name,age,sex,address,weight FROM `user_info` where address like 'los % %';
Copy the code
The getCollection (' user_info). Find ({" address ": {' $regex ':', '}, {" name" : 1, "age" : 1, "sex" : 1, "address" : 1, the "weight" : 1})Copy the code

$regex is a simplified regex judgment. Mongo query rules mainly start or end with xx, including XX, and are case sensitive

5 Common conditional operators

Conditional operator describe The sample
$ne Is not equal to db.getCollection(‘user_info’).find({“age”:{‘$ne’:23}})
$lt Less than Same as above
$gt Less than or equal to Same as above
$gt Is greater than Same as above
$gte Greater than or equal to Same as above
$or or The getCollection (‘ user_info). Find ({$” or “: {‘ name ‘:’ Brooks’, ‘age: 29}}
$nor Or take the Same as above
$in In the query Db. GetCollection (‘ user_info). Find ({” name “: {‘ $’ in: [‘ book ‘, ‘Kim kardashian’]}})

5.1 Combination Examples

More than 23 years old less than or equal to 30 db. GetCollection (' user_info). Find ({" age ": {' $gt: 23, '$lte: 30}}; Ranging in age from 23 to 30, the people living in Los Angeles, Dallas the getCollection (' user_info). Find ({" age ": {' $gt: 23, '$lte: 30},' address: {' $' in: [' Los Angeles', 'Dallas']}};Copy the code

Simple query summary

The simple query part introduces our common condition query, paging query, the application of the condition operator, master the command above can be in the development of the data for conventional query operation, the following introduction to the application of Mongo aggregation group query.

Group aggregation

The difficulties in

Mongo group aggregation syntax is not difficult to take out alone, but in combination with complex conditions, alias will be more hierarchy, it is recommended to master simple grouping, use JSON editor for editing, can clearly see the hierarchy, easy to understand

Single field grouping

Total weight over 18 years old by Address grouping (single field grouping)

SELECT address, SUM(weight) weightSum FROM `user_info` where age > =18 GROUP BY address
Copy the code
db.user_info.aggregate([{"$match":{"age":{"$gte":18}}},{"$group":{"_id":"$address","weightSum":{"$sum":"$weight"}}}])
Copy the code

Mongo group aggregation uses the aggregate method. Each entry of monGO data has a unique _ID, which is automatically generated during insertion or manually set during insertion

  • The aggregate method takes an array [{}, {}]
  • {“$match”:{}} where address = “Los Angeles “}} Where address =” Los Angeles”
  • {“$group”:{}} $group = {“$group”:{}} $group = {_id:”$address”; The value is also a JSON, worth json key is the function, value is $+ statistics field

Multi-field grouping

Average body weight by Address and sex (multi-field grouping)

db.user_info.aggregate([{"$match":{"age":{"$gte":18}}},{"$group":{"_id":["$address","$sex"],"weightSum":{"$avg":"$weight "}}}])Copy the code

The difference is that the value of _id in the group is changed to an array of fields, and the value of _id in the return value is changed to an array

delete

delete from user_info where name = 'book' limit 1;
Copy the code
Db.collection. remove({'name':' booker '}, ture)Copy the code

Mongo delete using remove method, the first parameter is delete condition, and find query condition syntax, the second parameter is whether to delete a, true will only delete the condition matched to the first data

update

The mongo update method is very strong. The first parameter is the condition (format {}), the second parameter is the updated value (format {‘$set’:{‘ field ‘:’ value ‘}), and the third parameter is the upsert(if the condition exists, it is updated; if the condition does not exist, it is inserted, default false). By default, fasLE updates only the first one that meets the criteria.

Update kardashian’s address

update user_info set address="New York"where name ="Kardashian" Limit1;
Copy the code

Ps: There are many update insert methods in mysql. For example, replace into checks whether the data exists based on the primary key or unique index in the data. If the data exists, the self-added ID will change

Db. User_info. Update ({" name ":" Kim kardashian "}, {" $set ": {" address" : "New York", "telephone" : 10086}}, true, false)Copy the code

It can be seen that WHEN I updated Kardashian, I also added a field called telephone that did not exist before, so that kardashian’s personal information would have an additional telephone, which is also a loose embodiment of MongoDB

The last

This paper involves the development of the common simple query and group aggregation, covering the vast majority of scenarios, through the comparison with SQL to quickly learn mongo operation, I hope to help people in need

Click learn MongoDB breakdown

Learn how to build file services with MongoDB GridFs in minutes

Learn about Redis cache and local cache design practices