Fast learning Mongo operation syntax combined with single table SQL

Hot butter beer 2021-09-15 09:21:25

Preface

MongoDB It is a very mainstream non relational database , Loose data structure (json and bson) Easy to store complex data types , Powerful query syntax ( It has most of the functions of single table query in relational database ), Support index creation , Excellent query performance . about MongoDB Data storage and MySQL Database storage , A single piece of data can be regarded as a Map<String,Obect> And an entity object ,MongoDB Various fields and data types can be stored at will ,MySQL You need to insert the data fields and types defined in the table design , In applicable scenarios , This is a MongoDB And other non relational databases .

Knowledge point

  • Simple query ( Conditions of the query 、 Paging query 、 Sort 、 Operator usage )
  • Group aggregation ( Single field grouping 、 Multi field grouping )
  • to update 、 Delete

Inquire about

Presentation data

user_info

full name (name) Age (age) Gender (sex) address (address) weight (weight)
Kim kardashian 29 Woman Los Angeles 60
George 30 male Los Angeles 80
Booker 23 male TRABTECH 80
East qi qi 23 male Dalas 80

Be careful :name yes MySQL keyword , Don't use keywords as field names in actual development

Simple query

1 Query details

select name, age, sex,address from user_info where name = ' Booker ' limit 1;
 Copy code 
db.user_info.find({"name":" Booker "},{"name":1,"age":1,"sex":1, "address":1}}).limit(1);
 Copy code 

1.1 Multi field matching

select name, age, sex,address from user_info where name = ' Booker ' and address = ' TRABTECH ' limit 1;
 Copy code 
db.user_info.find({"name":" Booker ", "address":" TRABTECH "},{"name":1,"age":1,"sex":1, "address":1}}).limit(1);
 Copy code 

Monogo Query usage find() Method ,find Method contains two parameters, both of which are json structure , The first parameter is the query condition , The second is to specify the key to query , The value is 0 Represents that this field is not queried ,1 Represents the query for this field , This article mongo The query involves two methods find(query, projection),limit(size), Corresponding mysql Medium select and limit, The difference lies in mysql in limit Can contain offset Parameters and limit Parameters , Let's show mongo How to realize mysql In paging limit Two parameter writing ,find The first parameter of the is filled in blank json({}) It means checking the whole table , Fill in the blank for the second parameter json({}) Represents getting all the fields

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

2 Paging query

select name, age, sex,address,weight from user_info limit 1,1;
 Copy code 
db.user_info.find({},{"name":1,"age":1,"sex":1, address":1,"weight":1}).limit(1).skip(1)
 Copy code 

MongoDB To query the number of items from item to item, you need to limit Methods and skip Method combination ,MySQL Among them are limit More flexible

3 Sort query

3.1 Single field sorting

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

mysql Use in order by Field Sort ( Can be placed at the top in ascending order asc、 Descending desc, If it is not specified, it will be in descending order by default ),mongo Use in sort({"key": -1 or 1}) Sort ,-1 For the descending order 、1 In ascending order

3.2 Multi field sorting

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

4 Fuzzy query

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

$regex It is equivalent to a simplified regular judgment ,Mongo Query rules are mainly based on xx Beginning or end , contain xx, Is it case sensitive

5 Common conditional operators

Conditional operators describe Example
$ne It's not equal to db.getCollection('user_info').find({"age":{'$ne':23}})
$lt Less than ditto
$gt Less than or equal to ditto
$gt Greater than ditto
$gte Greater than or equal to ditto
$or or db.getCollection('user_info').find({"$or":{'name':' Booker ','age':29}}
$nor Or reverse ditto
$in in Inquire about db.getCollection('user_info').find({'name':{'$in':[' Booker ',' Kim kardashian ']}})

5.1 Combined use example

 Greater than 23 Less than or equal to 30 year
db.getCollection('user_info').find({"age":{'$gt':23,'$lte':30}};
Age 23 To 30, Living in Los Angeles 、 Dallas people
db.getCollection('user_info').find({"age":{'$gt':23,'$lte':30}, 'address':{'$in':[' Los Angeles ',' Dalas ']}};
 Copy code 

Simple query summary

The simple query section introduces our commonly used conditional query , Paging query , Application of conditional operators , If you master the above commands, you can carry out routine query operation on data in development , So let's talk about that Mongo Application of aggregate grouping query .

Group aggregation

difficulty

mongo The syntax of grouping and aggregation is not difficult to take out alone , But in combination with complex conditions , After aliasing, there will be more levels , It is recommended to skillfully and simply group , Use json Editor to edit , Be able to clearly see the hierarchical relationship , Easy to understand

Single field grouping

Press address Grouping statistics is greater than 18 Total weight of years old ( Single field grouping )

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

image.png

mongo Group aggregation uses aggregate Method ,mongo Each piece of data will have a unique _id, It will be generated automatically at the time of insertion , It can also be set manually during insertion

image.png

  • aggregate The method parameter is an array [{},{}]
  • The format of the first element of the array is fixed to {"$match":{}},key Is the match operator , Values correspond to conditions in simple queries, such as {"$match":{"address":" Los Angeles "}} Namely sql group Before where address = " Los Angeles "
  • The format of the second parameter is {"$group":{}},$group yes mongo Grouping operators in , If the value is in accordance with address grouping , Namely _id:"$address", Then there is the aggregation operation to be done after grouping , It can be equivalent to an alias , Value is also a json, Worth json key That's the function. ,value Namely $+ Statistics fields

Multi field grouping

Press address The average body weight was calculated by sex ( Multi field grouping )

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

The difference is that in the group _id The corresponding value becomes an array of fields grouped by multiple fields , Return value _id It also becomes an array

image.png

Delete

delete from user_info where name = ' Booker ' limit 1;
 Copy code 
db.collection.remove(
{'name':' Booker '},
ture
)
 Copy code 

mongo Delete use remove Method , The first parameter of the method is delete condition , And find The syntax of the query criteria is the same , The second parameter is whether to delete a ,true Only the first data matched by the condition will be deleted

to update

mongo The update method is powerful ,update Method contains four parameters , The first is a condition ( The format is {}), The second is the updated value ( The format is {'$set':{' Field ':' value '}), The third is upsert( If there are qualified, i.e. update , Insert when not present , Default false), The fourth is whether to update multiple ( Default fasle Only the first one that meets the criteria is updated )

Update Kardashian's address

update user_info set address=" New York " where name = " Kim kardashian " limit 1;
 Copy code 

ps:mysql There are many ways to update insert in , such as replace into It will check whether the data exists according to the primary key or unique index in the data , The existence of conditions will lead to self increase id Change , You need to be careful with

db.user_info.update({"name":" Kim kardashian "},{"$set":{"address":" New York ","telephone":10086}},true, false)
 Copy code 

You can see that when I update Kardashian , Also added a field that didn't exist before telephone, So Kardashian's personal information will be one more telephone, This is also MongoDB Loose embodiment

Last

This paper deals with the simple query and grouping aggregation commonly used in development , Covering most scenes , Through and with sql Contrast fast learning mongo operation , Hope to help people in need

Click to learn MongoDB table

Learn in minutes MongoDB GridFs Set up document service

Get to know Redis Cache and local cache design practice

Please bring the original link to reprint ,thank
Similar articles

2021-09-15

2021-09-15

2021-09-15

2021-09-15