Development tools:

• mysql-8.0
• DataGrip

Data source: Chapter7 csv

id,name,sales_A,sales_B,price_A,price_B
E001,Zhang Tong,18,15,10,5
E002,Li Gu,12,17,10,5
E003,Sun Feng,19,20,10,5
E004,Zhao Heng,12,14,10,5
E005,Wang Na,13,11,10,5
E006,Li Wei,16,16,10,5
E007,Liu Jie,11,13,10,5
E008,Xue Li,14,18,10,5
E009,Pei Jun,11,18,10,5

(1) Arithmetic operation

Arithmetic operation is the familiar operation of addition, subtraction, multiplication and division. It is a common and simple operation.

In SQL, when we want to perform arithmetic operations on two or more columns, we can directly connect the corresponding column names with the corresponding operators. The current sales volume of each product + b; The sales volume of products a and b is poor; Total sales of each product sold, i.e. sales of a product × A product price + b product sales × b. product price; Price multiples of product a and product b; A twice the sales volume of products. The specific implementation code is as follows:

-- Arithmetic operation
-- Now you need to get all the sales of each sold product,
-- Namely a Product sales+b Product sales volume;
-- a Products and b Poor sales of products;
-- Total sales of each product sold, i.e a Product sales×a product price+b Product sales×b Product price;
-- a Products and b Price multiples of products; a Twice the product sales. The specific implementation code is as follows:
select id,(sales_A + sales_B) as all_sales ,
       (sales_A - sales_B) as sales_a_b ,
       (sales_A * price_A + sales_B * price_B) as gmv ,
       (price_A / price_B) as price_a_b ,
       (sales_A * 2) as 2_sales_a from chapter7;

(2) Comparison operation

Comparison operation is mainly used for the comparison between two columns or the same specific value of a column. There are several operations shown in the following table.

In SQL, if you want to realize the comparison operation, similar to that in Excel, you need to specify the specific columns to be compared, and then use the comparison operator to connect different columns. The specific implementation code is as follows:

-- Comparison operation
select id ,sales_A ,sales_B ,
       sales_A > sales_B as "greater than" ,
       sales_A < sales_B as "less than" ,
       sales_A = sales_B as "be equal to" ,
       sales_A != sales_B as "Not equal to" ,
       sales_A is null as "Null value" ,
       sales_A is not null as "Non null value" from demo.chapter7;

-- We need to get a The sales volume of the product is 15～20 In scope id Column sum sales_a Column, which can be realized by the following code:
select id,sales_A from demo.chapter7 where sales_A between 15 and 20;
-- If you want to get a Products with sales volume greater than 15 id Column sum sales_a Column, you can implement it through the following code:
select id,sales_A from demo.chapter7 where sales_A > 15;

(3) Logical operation

Logical operators are mainly used to connect multiple conditions, including and, or and not, as shown in the following table.

For example, we need to add two labels to each id: double excellent and single excellent. The standard of double excellence is sales_ Column A and sales_ Column B is greater than 15, and the single optimal standard is as long as sales_ Column A and sales_ One column in column B is greater than 15. The specific implementation code is as follows:

select id,sales_A,sales_B, ((sales_A > 15) and (sales_B > 15))  as 'Shuangyou',
       ((sales_A > 15) or (sales_B > 15))  as 'Shan you' from demo.chapter7;

(4) Mathematical operation

Mathematical operation is some operations related to mathematics, such as trigonometric function, logarithmic operation and so on.

(4.1) calculate the absolute value

For example, we want to require sales corresponding to each id_ Column A and sales_ For the absolute difference of column B, if we directly make a difference between these two columns, the results will be positive and negative, but what we want to ask is the absolute difference, so we need to calculate the absolute value of the results after direct difference. The specific implementation code is as follows:

-- 1 Find the absolute value
select id,sales_A,sales_B,(sales_A - sales_B) as 'Difference' ,
       ABS(sales_A - sales_B) as 'absolute value' from chapter7;

(4.2) find the minimum integer value

For example, generate a minimum integer value not less than x. In SQL, we use the ceil() function. The specific implementation code is as follows:

select ceil(2.9);

The final result is 3, which is the minimum integer value of no less than 2.9.

(4.3) find the maximum integer value

For example, generate a maximum integer value not greater than x. In SQL, we use the floor() function. The specific implementation code is as follows:

select floor(2.1);

Run the above code, and the final result is 2, which is the maximum integer value not greater than 2.1.

(4.4) random number generation

In SQL, we use the rand() function to generate random numbers, and the rand() function returns a random floating-point number in the range of 0 ~ 1.

-- generation of random number
select id ,rand() as 'random number' from demo.chapter7;
select id ,rand() as 'random number' from demo.chapter7 order by rand() desc limit 3 ;

(4.5) adjustment of decimal places

In SQL, we use the round() function to adjust the number of decimal places. The specific implementation code is as follows:

select round(1.1111,2)

Run the above code and the final result is 1.11.

(4.6) positive and negative judgment

In SQL, we use the sign() function to judge the positive and negative. For example, we need to judge the sales corresponding to each id_ Column A and sales_ The positive and negative of the difference in column B can be realized by the following code:

select id,sales_A , sales_B,
       (sales_A - sales_B) as sales_a_b,
       sign(sales_a - sales_b) as "Positive and negative" from chapter7;

(5) String operation

String operation is also a common operation. The string operation function is shown in the table below.

(5.1) string substitution

In SQL, we use the replace() function. The specific implementation code is as follows:

select replace("AaAaAa","A","a")

The final result is AAAA.
For example, replace the character E in the id column in the chapter7 table with E, and the specific implementation code is as follows:

select id,replace(id,'E','e') as  replace_id from chapter7;

(5.2) string merging

String merging is to merge multiple strings into one string. In SQL, we use concat() function.

In some tables, the last name and first name are stored in two columns, so we need to combine the last name and first name to form a name. The specific implementation code is as follows:

-- String merge
select concat('cai','zhengjie');

If you merge two or more columns in a table, you can directly indicate the name of the column to be merged in the brackets of the concat() function. For example, merge the id column and name column in the chapter7 table. The specific implementation code is as follows:

-- take chapter7 In table id Column sum name Column merging. The specific implementation code is as follows:
select id,name,concat(id,name) as  concat_id from chapter7;

Sometimes, when we want to merge different strings or columns with fixed symbols, we need to use another function concat_ws():

-- If we want to merge different strings or columns with fixed symbols, we need another function concat_ws(): 
select id,name,concat_ws('-',id,name) as  concat_id from chapter7;

(5.3) string interception

String interception is to intercept some characters we need from a string. There are three main interception methods: left, middle and right.

-- String interception
select left('2019-10-01 12:30:21',10);
select right('2019-10-01 12:30:21',8);
select substring('2019-10-01 12:30:21',6,2);

(5.4) string matching

In SQL, like is used for string matching. Like has two matching symbols:% and% Used to match characters of any length, which can be 0, and_ Characters used to match a single length.

-- string matching 
-- For example, we should extract all the students surnamed Zhang, assuming that their names are listed as name
select * from chapter7 where name like 'Zhang%';
-- Another example is that we need to name All names contained in the column are extracted, and only the middle character is Kai,
-- The front and back can be several characters. The specific implementation code is as follows:
select * from chapter7 where name like '%through%';
-- Another example is that we want to obtain name Students whose surname is Zhang and whose name is two characters in the column can be realized through the following code:
select * from chapter7 where name like 'Zhang_';
-- For example, we can obtain the information of non Zhang students through the following code:
select * from chapter7 where name not like 'Zhang%';

(5.5) string count

String counting is to count how many characters a string contains. In SQL, we use char_length() function, and char_ A function similar to the length () function is length ()

-- String count
select char_length('sql'); -- 3
 select length('I love learning.'); -- 12

(5.6) remove string spaces

There are three ways to remove the space on the left of the string, the space on the right of the string, and the space on both sides of the string. The specific implementation code is as follows:

-- Remove string spaces
-- There are three ways to remove the space on the left of the string, the space on the right of the string, and the space on both sides of the string.
select length(' abcdefg ') as srt_length,
       length(ltrim(' abcdefg ')) as ltrim_length,
       length(rtrim(' abcdefg ')) as rtrim_length,
       length(trim(' abcdefg ')) as trim_length;

(5.7) string repetition

String repetition is to combine the same string several times into one string. The repeat() function is used in SQL. The specific implementation forms are as follows:

-- Duplicate string
-- repeat(str,x) -- Return string str repeat x Results of the second test
select repeat('Sql',3);

(6) Aggregation operation

Aggregation operation refers to the aggregation of multiple values for some operation, such as summation, average, etc.

(6.1) count

The count() function is used to count multiple non missing values. It is often used to check the number of non null values in a column in the table

-- Aggregation operation
-- count()count,We want to see chapter7 In table id How many non null values are there in the column
select count(id) from demo.chapter7;
-- If we want to see chapter7 How many rows are there in the table? You only need to put the in parentheses id change into*Just.
select count(*) from demo.chapter7;
select count(' ');
-- For example, we want to see chapter7 Products in the table a There are several sales levels, i.e sales_a Column count after deleting duplicate values. The specific implementation code is as follows:
select count(distinct sales_A) from demo.chapter7;

(6.2) sum

The sum() function is mainly used to sum and summarize all the values of a column in the table

-- sum()Sum
-- For example, we need to get it separately chapter7 Products in the table a And products b Total sales of
select sum(sales_A),sum(sales_B) from demo.chapter7;

(6.3) avg() average

avg() function is mainly used to calculate the average value of all values in a column in the table

-- avg()Average
-- For example, we need to get it separately chapter7 Products in the table a And products b The average sales volume can be used avg()Function, the specific implementation code is as follows:
select avg(sales_A),avg(sales_B) from demo.chapter7;

(6.4) max() to find the maximum value

The max() function is mainly used to obtain the maximum value of a column in the table

-- max()Find the maximum value
-- For example, we need to get it separately chapter7 Products in the table a And products b The highest sales volume can be used max()Function, the specific implementation code is as follows:
select max(sales_A),max(sales_B) from demo.chapter7;

(6.5) min() to find the minimum value

The min() function corresponds to the max() function and is used to obtain the minimum value of a column in the table

-- min()Find the minimum value
-- For example, we need to get it separately chapter7 Products in the table a And products b The lowest sales volume can be used min()Function, the specific implementation code is as follows:
select min(sales_A),min(sales_B) from demo.chapter7;

(6.6) variance calculation

Variance is used to reflect the dispersion degree of a group of data, that is, the degree of fluctuation. The greater the variance, the more severe the data fluctuation. The calculation formula of variance is as follows:

Where X is each value in a set of data, μ Is the overall average value, and N is the number of overall values. This formula represents the square sum of the difference between each value in a group of data and the average value of the whole group of data, and then divided by the number of values in the whole group of data.
In SQL, VaR is used to calculate the overall variance_ Pop() function; Find the sample variance, using var_samp() function. The specific implementation code is as follows:

-- Find variance
-- stay SQL To find the population variance, we use var_pop()Function; Find the sample variance, using var_samp()Function. The specific implementation code is as follows:
select var_pop(sales_A),var_samp(sales_A) from demo.chapter7;

(6.7) standard deviation

The standard deviation is the square of variance and is also used to reflect the dispersion degree of data. Readers may think that the variance has been used to reflect the dispersion degree of data. Why use the standard deviation? That's because although variance can reflect the dispersion of data, it has no practical business significance. Because the standard deviation is consistent with the unit of the actual data. For example, the unit of the standard deviation of the height of middle school students is cm, and the variance is the square of CM, which is difficult to understand.

Because the standard deviation is the square of variance, and the variance includes population variance and sample variance, the standard deviation also includes population standard deviation and sample standard deviation.

In SQL, the std() function is used to calculate the overall standard deviation; StdDev is used to calculate the standard deviation of samples_ Samp() function. The specific implementation code is as follows:

-- Standard deviation
select  std(sales_A),stddev_samp(sales_A) from demo.chapter7;

(6.8) operation between aggregate functions

For example, if we want to obtain the total sales volume of product a and product b, we need to analyze sales first_ A column is summed and aggregated, and then sales_ Column b performs summation and aggregation operation. Finally, sum and aggregate the two values after aggregation operation, which is the total sales volume of product a and product b. the specific implementation code is as follows:

-- Operations between aggregate functions
select sum(sales_a) as a_group ,
       sum(sales_b) as b_group ,
       sum(sales_a) + sum(sales_b) as a_b_group from demo.chapter7;

The above content is for reference only. If there is infringement, please contact me to delete it!
If this article is helpful to you, the thumb in the lower left corner is the biggest encouragement to bloggers.
Your encouragement is the biggest motivation of bloggers!