How to use mysql to complete the data generation in excel

How to use mysql to complete the data generation in excel

Excel is the most commonly used tool for data analysis. This article compares the functions of MySQL and Excel to introduce how to use MySQL to complete data generation, data cleaning, preprocessing, and the most common data classification, data filtering, classification summary, and data pivot operations in Excel. In this article, we introduce the contents of parts 5, 6, and 7, data extraction, data screening, and data aggregation and perspective.

5. Data Extraction

The fifth part is data extraction, which is also the most common task in data analysis. The following describes how to use each function.

Extract data by column

#按列提取SELECT city FROM data1;

Extract data by row

#Extract by rowSELECT * FROM data1 WHERE city='beijing';

Extract data by location

#Extract by position SELECT * FROM data1 LIMIT 2,5;

Extract data by condition

#Extract and calculate by condition SELECT AVG(price) FROM data1 WHERE city='beijing' AND age<25;

6. Data screening

The sixth part is data filtering, which uses the three conditions of AND, OR, NOT and greater than, less than and equal to filter the data, and count and sum them. Similar to the filtering function and countifs and sumifs functions in Excel.

Filter by condition (and, or, not)

The Excel data directory provides a "Filter" function, which is used to filter the data table according to different conditions. In MySQL, WHERE is used to complete the filtering operation, and the sum and count functions can also realize the functions of sumif and countif functions in Excel.

#Data filtering AND
SELECT * FROM data1 WHERE city='shanghai' AND age>30;

#Data filtering IN
SELECT * FROM data1 WHERE city IN ('shanghai','beijing');

#Data filtering OR
SELECT * FROM data1 WHERE city='shanghai' OR age>30;

#Data filtering (not equal)
SELECT * FROM data1 WHERE city !='beijing';

#Data filtering like (fuzzy filtering)
SELECT * FROM data1 WHERE city LIKE 'bei%';

#Count after filtering countif
SELECT COUNT(id) AS id_count FROM data1 WHERE city='shanghai'AND age>30;

#Sum after filtering sumtif
SELECT SUM(price) AS price FROM data1 WHERE city='beijing' AND age<30;

#Find the average after filtering averageif
SELECT AVG(price) AS avg_price FROM data1 WHERE city !='beijing';

7. Data classification, summary and perspective

The seventh part is to classify and summarize the data. In Excel, you can use classification and summary and pivot data to summarize data according to specific dimensions. The main functions used in MySQL are GROUP BY and CASE WHEN. The following describes how to use these two functions.

Classification

Excel provides a "Subtotal" function under the data directory, which can summarize the data table according to the specified fields and summary methods. In MySQL, the corresponding operations are completed through GROUP BY, and multi-level classification aggregation can be supported.

GROUP BY is a function for classification and aggregation. It is very easy to use. You can specify the column name to be grouped, or you can specify multiple column names at the same time. GROUP BY groups in the order in which the column names appear. At the same time, a summary method should be formulated after grouping. The most common ones are counting and summing.

#Single column classification summary SELECT city,COUNT(id) AS id_count FROM data1 GROUP BY city ORDER BY id_count;

#Multiple columns SELECT city,colour,ROUND(SUM(price),2) AS id_count FROM data1 GROUP BY city,colour;

Pivot

The "Pivot Table" function is provided under the Insert directory in Excel to summarize the data table according to specific dimensions. There is no direct pivot table functionality in MySQL. But the same effect can be achieved through the CASE WHEN function.

Pivot table is also a commonly used way to classify and summarize data, and its function is more powerful than GROUP BY. In the following code, city is set as the row field, colour is set as the column field, price is set as the value field, and the price amount is calculated.

#View the original data table SELECT * FROM data1;

#Use CASE WHEN to pivot data CREATE VIEW data_Items AS (
 SELECT
 data1.city,
 CASE WHEN colour = "A" THEN price END AS A,
 CASE WHEN colour = "B" THEN price END AS B,
 CASE WHEN colour = "C" THEN price END AS C,
 CASE WHEN colour = "F" THEN price END AS F
 FROM data1
);

#View the results SELECT * FROM data_Items;

#Summarize the fields CREATE VIEW data1_Extended_Pivot AS (
 SELECT
 city,
 SUM(A) AS A,
 SUM(B) AS B,
 SUM(C) AS C,
 SUM(F) AS F
 FROM data_Items
 GROUP BY city
);

#View the results SELECT * FROM data1_Extended_Pivot;

#Handle null values ​​CREATE VIEW data1_Extended_Pivot_Pretty AS (
 SELECT 
 city, 
 COALESCE(A, 0) AS A, 
 COALESCE(B, 0) AS B, 
 COALESCE(C, 0) AS C,
 COALESCE(F, 0) AS F
 FROM data1_Extended_Pivot
);

#View the pivot data results SELECT * FROM data1_Extended_Pivot_Pretty;

You may also be interested in:
  • How to import Excel to MySQL using PHP
  • Implementation code of importing Excel data into Mysql database
  • Use PHPExcel class to implement Excel import MySQL database function (example code)
  • PHP exports MySQL data to Excel file (fputcsv)
  • php import excel into mysql
  • How to solve the problem of MySQL Chinese garbled characters and exporting to SQL statements and Excel [Pictures and text]

<<:  Detailed Linux installation tutorial

>>:  How to use JS to implement waterfall layout of web pages

Recommend

Discussion on more reasonable creation rules for MySQL string indexes

Preface Regarding the use of MySQL indexes, we ha...

Why should MySQL fields use NOT NULL?

I recently joined a new company and found some mi...

About MySQL innodb_autoinc_lock_mode

The innodb_autoinc_lock_mode parameter controls t...

Creative About Us Web Page Design

Unique “About”-Pages A great way to distinguish yo...

Detailed analysis of MySQL instance crash cases

[Problem description] Our production environment ...

Docker-compose creates a bridge, adds a subnet, and deletes a network card

1. Create a docker network card [root@i ~]# brctl...

Detailed graphic tutorial on how to enable remote secure access with Docker

1. Edit the docker.service file vi /usr/lib/syste...

How to build a complete samba server in Linux (centos version)

Preface smb is the name of a protocol that can be...

js to realize the mouse following game

This article shares the specific code of js to im...

MySQL single table query example detailed explanation

1. Prepare data The following operations will be ...