How to use mysql to generate excel data and mysql to complete excel Data

How to use mysql to generate excel data and mysql to complete excel Data

Excel is the most commonly used tool in data analysis. This article compares the functions of mysql and excel to show you how to use mysql to generate data in excel, clean data, pre-process data, and classify the most common data, data filtering, classification and summary, and data pivoting. This article describes Part 5, 6, and 7, including data extraction, data filtering, data summarization, and pivoting.

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

# Extract SELECT city FROM data1 by column;

Extract data by row

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

Extract data by location

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

Extract data by conditions

# Extract AND calculate select avg (price) FROM data1 WHERE city = 'beijing' AND age by conditions <25;

6. Data Filtering

The sixth part is data filtering. The use and, or, if not three conditions are used together, the values smaller than and equal to are used to filter the data and count and sum the data. Similar to the filter functions in excel, countifs and sumifs functions.

Filter by conditions (and, or, not)

The Excel Data Directory provides the "filter" function to filter data tables according to different conditions. Mysql uses the WHERE clause to filter data, and works with the sum and count functions to implement the sumif and countif functions in excel.

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

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

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

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

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

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

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

# Average averageifSELECT AVG (price) AS avg_price FROM data1 WHERE city after filtering! = 'Beijing ';

7. Data Classification summary and Perspective

The seventh part is to classify and summarize data. In Excel, data can be summarized BY specific dimensions using classification and data pivoting. The main functions used in mysql are group by and case when. The following describes how to use these two functions.

Category summary

The "Classification and summarization" function is provided in the Excel Data Directory to summarize data tables based on specified fields and summary methods. Mysql performs operations by group by and supports multi-level classification and summarization.

Group by is a function for classification and summarization. It is easy to use. You can specify the name of the column to be grouped. You can also specify multiple column names at the same time, group by is grouped BY column name. At the same time, we need to develop a summary Method After grouping. Common examples are count and sum.

# SELECT city, COUNT (id) AS id_count FROM data1 group by city order by id_count;

# SELECT city, color, ROUND (SUM (price), 2) AS id_count FROM data1 group by city, color;

Data pivoting

The "pivot table" function is provided under the insert directory in Excel to summarize data tables by specific dimensions. Mysql does not directly provide the pivot table function. But the same effect is achieved through the case when function.

Pivot tables are also commonly used in data classification and summarization, and are more powerful than group. In the following code, set "city" as the row field, "color" as the column field, and "price" as the value field to calculate the price amount.

# View the original data table SELECT * FROM data1;

# Use case when for data pivoting create view data_Items AS (SELECT data1.city, case when color = "A" THEN price end as, case when color = "B" THEN price end as B, CASE WHEN color = "C" THEN price END AS C, case when color = "F" THEN price end as f from data1 );

# View the result SELECT * FROM data_Items;

# SUM and summary of fields create view dataworks extended_just 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 result SELECT * FROM dataworks extended_logs;

# Process null values. create view data‑extended_‑t_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 data=extended_pivot );

# View the data pivot result SELECT * FROM data‑extended_effect_pretty;