Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

The structure of the data table used is as follows:

The contents of the data table are as follows:

1. Insert a list of fixed values

Sometimes, we just want to get the existing data in the original table. I also want to insert a set of fixed values according to the selected data characteristics in the query results. Its implementation method is as follows:

SELECT id, `name`,age<20 as label
FROM chapter5 WHERE age<20
Copy the code

result:

2. Json sequence parsing

You can perform the following operations to parse the data stored in JSON format.

2.1 Obtaining the VALUE of JSON Data

SELECT id, `name`,JSON_EXTRACT(score, "$. Math ") as "Math" FROM Chapter5Copy the code

result:

2.2 Obtaining the KEY of JSON Data

SELECT id, 'name',JSON_KEYS(score) FROM chapter5Copy the code

result:

3. The missing value

The structure of the data table used from section 3 onwards is as follows:

The contents of the data table are as follows:

3.1. Missing value filtering

Missing values can be expressed as Spaces, null, and null values (“” represents null values). The first two forms also indicate missing values, but have values in the corresponding cell, while the last null value has no value, indicating that the cell has nothing.

Null filter:

SELECT * FROM chapter6 WHERE profession ! = ""Copy the code

result:

Whitespace filtering:

SELECT * FROM chapter6 WHERE profession ! = ""Copy the code

Null filtering

SELECT * FROM chapter6 WHERE profession is not NULL
Copy the code

3.2 Missing value filling

SELECT *, COALESCE(profession," other ") FROM chapter6Copy the code

result:

4. Repeat value processing

4.1 the distinct

SELECT DISTINCT order_id, memberid FROM chapter6
Copy the code

result:

4.2 group by

SELECT order_id, memberid FROM chapter6 GROUP BY order_id, memberid
Copy the code

result:

5. Data type conversion

There are two main methods for converting data types: cast(value as type)

convert(value, type)

SELECT age, CAST(age as DECIMAL) decimal_age, CONVERT(age, CHAR) char_age FROM chapter6
Copy the code

result: