Mysql and MONGO simple query instance code _mysql

First of all, I don't say the difference between relational and non relational databases (there are a lot of Baidu) directly into the theme

I think the query is like this: The score is greater than 0 and the person's name is Bob or Jake's total. Average score minimum fraction max score count

Give this example to try MySQL and MongoDB to write a query separately

First, we're going to do some prep work.

MySQL's database structure is as follows

CREATE TABLE ' New_schema '. ' Demo ' (
' id ' INT NOT NULL,
' person ' VARCHAR (=) NOT null,
' score ' VARCHAR (=) Not N ull,
PRIMARY KEY (' id '));

We'll insert some data after we've finished building the table.

INSERT into ' New_schema '-' demo ' (' id ', ' person ', ' score ') VALUES (' 1 ', ' Bob ', ') ";
INSERT into ' New_schema '. ' Demo ' (' IDs ', ' person ', ' score ') VALUES (' 2 ', ' Jake ', ') ';
INSERT into ' New_schema '-' demo ' (' id ', ' person ', ' score ') VALUES (' 3 ', ' Bob ', ') ";
INSERT into ' New_schema '. ' Demo ' (' IDs ', ' person ', ' score ') VALUES (' 6 ', ' Jake ', ') ';
INSERT into ' New_schema '-' demo ' (' id ', ' person ', ' score ') VALUES (' 8 ', ' Li ', ' 100 ');

I'll take a picture to see the structure.

Okay, so let's go into MongoDB's prep work. Look at the structure of the document in the set of MongoDB (basically the same as MySQL) Here I will not write the specific process of inserting the document (in order to see the MongoDB display I have two formats display: One is cousin module display One is the text module display)

This is the table module display

This is the text module display

/* 1
/{"_id": ObjectId ("58043fa8e9a7804c05031e17"),
"person": "Bob",
"Sorce":
/
* 2/*
{
"_id": ObjectId ("58043fa8e9a7804c05031e18"),
"person": "Bob",
"Sorce": +
}/
* 3 */< c13/>{
"_id": ObjectId ("58043fa8e9a7804c05031e19"),
"person": "Jake",
"Sorce":/
* 4 * /
{
"_id": ObjectId ("58043fa8e9a7804c05031e1a"),
"person": "Jake",
"Sorce":
* 5 *
*
"_id": ObjectId ("58043fa8e9a7804c05031e1b"),
"person": "Li",
"Sorce":

Start to get to the point

Now, the MySQL statement I'm looking for is like this (score is greater than 0 and the name is Bob or Jake's score.) Average score min score max fraction count)

SELECT person, SUM (score), AVG (score), MIN (score), MAX (score), COUNT (*) from the 
demo 
WHERE score > 0 and person in (' Bob ', ' Jake ') 
GROUP by person;

The following starts with MONGO to write this query

The first thing to think about is the aggregation framework

First, the $match filter 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 the result

This is the result of the Cousin module display:

This is the result of the text module display:

/* 1
/{"_id": ObjectId ("58043fa8e9a7804c05031e17"),
"person": "Bob",
"Sorce":
/
* 2/*
{
"_id": ObjectId ("58043fa8e9a7804c05031e18"),
"person": "Bob",
"Sorce": +
}/
* 3 */< c13/>{
"_id": ObjectId ("58043fa8e9a7804c05031e19"),
"person": "Jake",
"Sorce":/
* 4 * /
{
"_id": ObjectId ("58043fa8e9a7804c05031e1a"),
"person": "Jake",
"Sorce":

Then you want to group and display the maximum minimum total mean and count value

So $group came 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 that the score is greater than 0 and the person's name is Bob or Jake's. Average score minimum fraction maximum fraction count

The resulting table module shows:

The text module of the result shows:

* 1 * * "
_id": "Bob",
"Sumsorce": ","
avgsorce ": 75.0,
" Lowsetsorce ":"
Highestsorce " : MB,
"Count": 2.0
}/
* 2/
{
"_id": "Jake",
"Sumsorce": 160,
"Avgsorce": 80.0,
    "Lowsetsorce": "
Highestsorce": $,
"Count": 2.0
}

The above is a small set to introduce the MySQL and MONGO simple query instance code, hope to help everyone, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!