• notice
  • Congratulations on the launch of the Sought Tech site

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!

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

buy atorvastatin 80mg & lt;a href="https://lipiws.top/"& gt;lipitor usa& lt;/a& gt; buy generic lipitor for sale

Ojqtmz

2024-03-08

Leave a Reply

+