Mysql and Mongo simple query instance code
First of all, I won’t talk about the difference between relational databases and non-relational databases here (there are many on Baidu) and I will directly cut into the topic
The content I want to query is this: the total score of the score greater than 0 and the person’s name is bob or jake average score minimum score maximum score count
Take this example to try to write a query with MySQL and mongodb respectively
First, let’s do some preparatory work.
The MySQL database structure is as follows
CREATE TABLE `new_schema`.`demo` ( `id` INT NOT NULL, `person` VARCHAR(45) NOT NULL, `score` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`));
After building the table, let’s insert some data
INSERT INTO `new_schema`.`demo` (`id`, `person`, `score`) VALUES ('1','bob', '50'); INSERT INTO `new_schema`.`demo` (`id`, `person`, `score`) VALUES ('2','jake', '60'); INSERT INTO `new_schema`.`demo` (`id`, `person`, `score`) VALUES ('3','bob', '100'); INSERT INTO `new_schema`.`demo` (`id`, `person`, `score`) VALUES ('6','jake', '100'); INSERT INTO `new_schema`.`demo` (`id`, `person`, `score`) VALUES ('8','li', '100');
I cut a picture for easy viewing of the structure
Okay.Next, let’s prepare for mongodb to take a look at the document structure in the mongodb collection (basically the same as MySQL).Here I will not write the specific process of inserting documents (for convenience Looking at the display of mongodb, I use two formats to display: one is the cousin module display and the other is the text module display)
This is the form module display
This is a text module display
/* 1 */ { "_id": ObjectId("58043fa8e9a7804c05031e17"), "person": "bob", "sorce": 50 } /* 2 */ { "_id": ObjectId("58043fa8e9a7804c05031e18"), "person": "bob", "sorce": 100 } /* 3 */ { "_id": ObjectId("58043fa8e9a7804c05031e19"), "person": "jake", "sorce": 60 } /* 4 */ { "_id": ObjectId("58043fa8e9a7804c05031e1a"), "person": "jake", "sorce": 100 } /* 5 */ { "_id": ObjectId("58043fa8e9a7804c05031e1b"), "person": "li", "sorce": 100 }
Start to enter the topic
Now the MySQL statement I want to check is like this (the total score of the score is greater than 0 and the person’s name is bob or jake, the average score, the minimum score, the maximum score, and the count)
SELECT person, SUM(score), AVG(score), MIN(score), MAX(score), COUNT(*) FROM demo WHERE score > 0 AND person IN('bob','jake') GROUP BY person;
Let’s start writing this query with Mongo
The first thing that comes to mind is the aggregation framework
First use $match to filter the score is greater than 0 and the name is bob or jake
db.demo.aggregate( { "$match":{ "$and":[ {"sorce":{"$gt":0}}, {"person":{"$in":["bob","jake"]}} ] } }
Get this result
This is the result displayed by the cousin module:
This is the result displayed by the text module:
/* 1 */ { "_id": ObjectId("58043fa8e9a7804c05031e17"), "person": "bob", "sorce": 50 } /* 2 */ { "_id": ObjectId("58043fa8e9a7804c05031e18"), "person": "bob", "sorce": 100 } /* 3 */ { "_id": ObjectId("58043fa8e9a7804c05031e19"), "person": "jake", "sorce": 60 } /* 4 */ { "_id": ObjectId("58043fa8e9a7804c05031e1a"), "person": "jake", "sorce": 100 }
Then I want to group and display the maximum, minimum, total, average, and count value
Then $group comes in handy:
db.demo.aggregate( { "$match":{ "$and":[ {"sorce":{"$gt":0}}, {"person":{"$in":["bob","jake"]}} ] } }, { "$group":{"_id":"$person", "sumSorce":{"$sum":"$sorce"}, "avgSorce":{"$avg":"$sorce"}, "lowsetSorce":{"$min":"$sorce"}, "highestSorce":{"$max":"$sorce"}, "count":{"$sum":1}} } )
The result is the total score of the score greater than 0 and the person’s name is bob or jake, average score, minimum score, maximum score, count
Result table module display:
The text module display of the result:
/* 1 */ { "_id": "bob", "sumSorce": 150, "avgSorce": 75.0, "lowsetSorce": 50, "highestSorce": 100, "count": 2.0 } /* 2 */ { "_id": "jake", "sumSorce": 160, "avgSorce": 80.0, "lowsetSorce": 60, "highestSorce": 100, "count": 2.0 }
The above is the simple query example code for MySQL and Mongo introduced by the editor.I hope it will be helpful to you.If you have any questions, please leave me a message.The editor will reply to you in time.Thank you very much for your support to the website!
buy atorvastatin 80mg & lt;a href="https://lipiws.top/"& gt;lipitor usa& lt;/a& gt; buy generic lipitor for sale
Ojqtmz
2024-03-08