Very practical MySQL function comprehensive summary detailed example analysis tutorial

1. Description of functions in MySQL

"Concept": Similar to the methods in Java and Python, a set of logical statements are encapsulated in the method body and the method name is exposed externally;

"Benefits": ⅠHide implementation details; ⅡImprove code reusability;

"Call": select function name (actual parameter list) [from table];

"Features": Ⅰ What is it called (function name); Ⅱ What does it do (function function);

"Classification": Ⅰ single-row function; Ⅱ grouping function;

"What is a single-row function:": It acts on each row of records in the table, and one result is obtained for each record;

"What is an aggregate function:": Acts on one or more rows and ultimately returns a result;

2. Single-line function classification

Character functions;

Mathematical functions;

Date functions;

Other functions:

Process control functions;

3. Character functions

1) length(str) : Get the number of bytes of the parameter value;

For the utf8 character set, one English character occupies 1 byte; one Chinese character occupies 3 bytes;

For gbk character set, one English character occupies 1 byte; one Chinese character occupies 2 bytes;

The operation is as follows:

2) concat(str1,str2,…) : concatenate strings;

The operation is as follows:

3) upper(str) : converts all letters in a string to uppercase;

The operation is as follows:

4) lower(str) : changes all letters in a string to lowercase;

The operation is as follows:

5) substr(str,start,len) : Extract the string from the start position, len indicates the length to be extracted; if len is not specified, it means extracting from the start position to the end of the string. The specified length is len: it means starting from start, the length of len is intercepted.

The operation is as follows:

6) instr(str,要查找的子串) : Returns the index of the first occurrence of the substring. If not found, returns 0. When the substring to be searched exists in the string: Returns the index of the [first] occurrence of the substring in the string. When the substring being searched does not exist in the string: 0 is returned.

The operation is as follows:

7) trim(str) : removes spaces before and after a string; this function can only remove spaces before and after a string, but cannot remove spaces in the middle of a string.

The operation is as follows:

8) lpad(str,len,填充字符) : Use the specified character to fill the string to the specified length on the left;

The operation is as follows:

9) rpad(str,len,填充字符) : Use the specified character to right-fill the string to the specified length;

The operation is as follows:

10) replace(str,子串,另一個字符串) : replace the string in the string str with another string;

The operation is as follows:

4. Mathematical functions

1) round(x,[保留的位數]) : round to the nearest integer. When rounding a positive number: just round to the nearest integer according to the normal calculation method. When rounding a negative number: first throw the sign aside, round the positive number after removing the negative sign, and then add the negative sign back after completing the rounding.

The operation is as follows:

2) ceil(x) : Rounds up and returns the smallest integer that is greater than or equal to the argument. (Ceiling function) Ceiling function, this function exists in both Excel and Python. Just imagine the ceiling of your house and throw this number onto the ceiling. The goal is to find the smallest integer greater than or equal to this number.

The operation is as follows:

3) floor(x) : Rounds down and returns the largest integer that is less than or equal to the argument. (Floor function) Floor function: This function exists in both Excel and Python. Just imagine the floor of your house, throw this number on the floor, and find the largest integer that is less than or equal to this number.

The operation is as follows:

4) truncate(x,D) : truncation; truncate函數also exists in Excel and Python, and the meaning is basically the same. But this function is a bit difficult to understand. Let me show you how to learn it.

The understanding is as follows:

"Refer to the example picture below and understand the following text"
1) D is a positive number, and the operation is on the decimal part to the right of the decimal point.
D=1, cut off the following part directly from the first position.
D=2, cut off the back part directly from the second position.
......

2) If D is 0, the decimal part is discarded directly.

3) D is a negative number, and the operation is on the integer part to the left of the decimal point.
D=-1, directly cut off the decimal part at the -1 position,
And "starting from the current position (including the current position), the following integer part is replaced by 0".
D=-2, directly cut off the decimal part at the -2 position,
And "starting from the current position (including the current position), the following integer part is replaced by 0".

Example image:

The operation is as follows:

5) mod(被除數,除數) : remainder; when the dividend is a positive number, the result is a positive number. When the dividend is a negative number, the result is a negative number.

The operation is as follows:

5. Date and time functions

The meaning of date: refers to the year, month and day we often say.

The meaning of time: refers to the hours, minutes and seconds we often say.

Before explaining the following functions, let us first add a piece of knowledge: what do different time format characters mean?

1) now() : Returns the current date and time of the system;

The operation is as follows:

2) curdate() : returns only the current date of the system, excluding time;

The operation is as follows:

3) curtime() : returns only the current system time, excluding the date;

The operation is as follows:

4) Get the year, month, day, hour, minute and second of the date and time;

Get the year: year();

Get the month: month();

Get the day: day();

Get the hour: hour();

Get the minute: minute();

Get the seconds: second();

5) weekofyear() : Get the week number of the current time;

The operation is as follows:

6) quarter() : Get the quarter to which the current moment belongs;

The operation is as follows:

7) str_to_date() : Convert a string in date format to a date in a specified format;

The operation is as follows:

8) date_format() : Convert the date into a date string; the month returned %Y-%m-%d is in the format of 01,02... The month returned %Y-%c-%d is in the format of 1,2...

The operation is as follows:

9) date_add() + interval : offset date and time forward or backward;

The operation is as follows:

10) last_day() : Extract the date of the last day of a month;

The operation is as follows:

11) datediff(end_date,start_date) : Calculates the number of days between two dates;

The operation is as follows:

12) timestampdiff(unit,start_date,end_date) : Calculates the year/month/day returned by two times;

The unit parameter is the unit in which the result of (start_date, end_date) is determined, expressed as an integer. The following are valid units:

year: year

month: month

day: day

hour: hour

minute

second: seconds

microsecond: microsecond

week: week number

quarter: quarter

YEAR: year

The operation is as follows:

6. Other commonly used system functions

7. Flow control function

1) if function: realizes the effect of if-else;

2) ifnull function: determines whether the value is null, and fills it with the specified value if it is null;

3) Three usages of case…when function;

There are three usages of case … when. I believe my summary is quite comprehensive. I hope everyone will study these uses carefully, they are all very useful.

Equivalence judgment: similar to the effect of switch case in Java;

Interval judgment: similar to the effect of if-elif-else in Python;

case ... when is used in conjunction with aggregate functions;

① case ... when is used as the syntax format for equal value judgment;

case The field or expression to be judged
when constant 1 then value 1 or statement 1 to be displayed
when constant 2 then value 2 or statement 2 to be displayed
...
else value n or statement n to be displayed
end

The operation is as follows:

② case … when is used as the syntax format for interval judgment;

case
when condition 1 then value 1 or statement 1 to be displayed
when condition 2 then value 2 or statement 2 to be displayed
...
else value n or statement n to be displayed
end

The operation is as follows:

③ Combination of case…when and aggregate functions

Using the original table above, complete the following questions:

-- 18. Check the highest score, lowest score and average score of each subject and display them in the following format:
-- Course ID, course name, highest score, lowest score, average score, pass rate, average rate, good rate, excellent rate
-- Passing score: >=60, Average score: 70-80, Good score: 80-90, Excellent score: >=90

The operation is as follows:

select sc.c,cname,
max(score) is the highest score, min(score) is the lowest score, avg(score) is the average score,
sum(case when score>60 then 1 else 0 end)/count(*) pass rate,
sum(case when score>=70 and score<80 then 1 else 0 end)/count(*) medium rate,
sum(case when score>=80 and score<90 then 1 else 0 end)/count(*) Good rate,
sum(case when score>=90 then 1 else 0 end)/count(*) excellent rate from sc left join course 
on sc.c = course.c
group by sc.c;

The results are as follows:

8. Aggregate Functions

1) Functions and classification of aggregation functions;

① The function of aggregate function;

Used for statistical purposes, also known as aggregate function, statistical function or group function.

② Classification of aggregation functions; sum for summation avg for average value max for maximum value min for minimum value count for calculation number

2) Simple use of aggregate functions

3) What data types are supported by the parameters passed into the five aggregate functions?

MySQL is not a strongly typed programming language. In other words, some statements may not report errors when they are executed, but the execution results are meaningless, so we also think they are incorrect.

① Test data;

"Create table statement"
create table test(
	id int primary key auto_increment,
	name varchar(20) not null,
	sal int,
	birth date)charset=utf8;

"Insert Data"
 insert into test(name,sal,birth) values 
 ("Zoo",6500,'1993.3.20'),
 ("Hobby",4000,'1997.6.10'),
 ("Aline",5500,'2000.5.1'),
 ("Bob",10000,'2008.10.1');

② sum() function and avg() function: it makes sense to pass in integer/decimal types;

The conclusion is as follows:

The sum() function and avg() function do not make much sense for calculations of string type and date/time type. Therefore, we only use the sum() function and avg() function to sum decimal types and integer types.

③ max() function and min() function: It is more meaningful to pass in integer/decimal type, date/time type;

The conclusion is as follows:

What is passed into max() and min() is "integer/decimal type", and最大值and最小值​​​​of the numbers are calculated. The "date type" is passed into max() and min(). The maximum value calculated by max() is最近的那個日期to us, and the minimum value calculated by min() is最遠的那個日期from us. You can remember this. The values ​​passed into max() and min() are字符串類型. The maximum value calculated by max() is displayed in alphabetical order, and the minimum value calculated by min() is also displayed in alphabetical order, which is not very meaningful.

④ count() function: You can pass in any data type, but be careful when encountering null;

The conclusion is as follows:

The count() function can pass in any data type to count rows.
"But the following knowledge points require special attention"
First, let’s take a look at what count(sal) and count(birth) mean? These two sentences respectively represent the statistics of the number of rows in the sal column and the birth column. Since one of the records is null, count() is used.
When the function counts, null rows are ignored.
Secondly, count(*) means counting the number of rows in the entire table. This is definitely a correct count of the number of rows in the original data. As long as a column field in a row of the entire table has a value that is not null, count(*) will consider that row to be 1 row. Of course, if the entire row is null, you don't need to insert this record.

Summarize:

When there is no null value in a field column, "count(column field) = count(*)"
When a field column has a null value, "count(column field) < count(*)"
Therefore, if you want to count the number of rows in the entire table, please use count(*).

In fact, all grouping functions ignore null values, but the count() function above needs special attention when encountering null values.

⑤ Pay special attention to the count() function when it encounters a null value;

The conclusion is as follows:

For the average value of avg(sal), (6500+4000+5500+10000)/4=6500.

For the following sum()/count(*) to calculate the average, (6500+4000+5500+10000)/5=5200.

Take the above example carefully. Sometimes, although someone's score is recorded as null, you still have 5 people, so you have to consider how to use the appropriate function to achieve the result you want.

⑥ What do count(1) and count(0) mean?

Whether it is sum(1), sum(0), count(1), count(0), avg(1), avg(0), the principle is the same, which is equivalent to adding a new column to the original table.

Secondly, we know that where is followed by [logical value]. When using where 1 and where 0, the principle is still the same, which is equivalent to adding a new column to the original table.

We just need to remember that in MySQL: "non-0 is true, 0 is false". That is to say, you can replace all the 1s below with any non-zero numbers.

The schematic diagram is as follows:

Test it out:

⑦ The efficiency of count(*) counting;

Under the MYISAM storage engine, count(*) is highly efficient. Under the INNODB storage engine, count(*) and count(1) are almost as efficient, but slightly more efficient than count(field). To sum up: count(*) is preferred.

4) The use of aggregate functions and group by is "most important";

We will discuss this knowledge point in the following knowledge points. Here we only need to remember one sentence: when the group by function is used in the SQL statement, the field after the select must be the field after the group by + the use of the aggregate function.

The above is the details of a very practical MySQL function comprehensive summary with detailed example analysis. For more information about MySQL functions, please pay attention to other related articles on 123WORDPRESS.COM!