Common functions based on MySQL

Time:2021-9-14

1、 Common function classification

1.1 single line function:

  • Character function
  • Character control function (concat, substr, length…)
  • Case control function
  • Mathematical function
  • Date function
  • Process control function

1.2 grouping function:

Grouping function functions are biased towards statistics, such as AVG (), count (), max (), min (), sum ()

Single line function and grouping function:

(1) Grouping function: it is mainly used for statistics and aggregation. Grouping function receives multiple inputs and returns one output.

(2) Single line function: the character function, mathematical function and date function mentioned above belong to the category of single line function. Single line function transforms only one line and returns one result for each line.

2、 Single line function

Character function

Concat: character concatenation function

Substr: intercept string,

Select substr ('li Mochou falls in love with Lu Zhanyuan ', 7) out_ put; //  The index starts with 1, Lu Zhanyuan
Select substr ('li Mochou falls in love with Lu Zhanyuan ', 1,3) out_ put;//  Li Mochou

Instr: returns the index of the first occurrence of the substring. If it is not found, it returns 0

Select instr ('yang Bu regretted falling in love with Yin Liuxia ',' Yin Liuxia ') as out_ put;//  three

Trim: remove the blank elements on the left and right

Select length (trim ('zhang Cuishan ') as out_ put;
Select trim ('aa 'from' aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ') as out_ put;// The middle a cannot be removed

Upper: uppercase conversion string

Lower: lowercase conversion string

Lpad: fill the specified length with the specified character

Select lpad ('voxel ', 2,' * ') as out_ put;//  The number represents the specified length

Rpad: right fills the specified length with the specified character

Replace: replace

3、 Mathematical function

rounding
SELECT ROUND(-1.55);// -2
SELECT ROUND(1.567,2);//  Specified character precision 1.56

Round up
SELECT CEIL(-1.02);// -1

Round down
SELECT FLOOR(-9.99);// -10

truncation
SELECT TRUNCATE(1.69999,1);// 1.6

Surplus
SELECT MOD(-10,3); // -1
SELECT MOD(-10,-3);// -1
SELECT MOD(10,-3);// 1

4、 Date function

Return current system date + time
SELECT NOW();// 2021-03-16 09:00:35

Returns the current system date, excluding time
SELECT CURDATE();// 2021-03-16

Returns the current time, excluding the date
SELECT CURTIME();// 09:00:35

You can get the specified part, year, month, day, hour, minute and second
Select year (now ());
Select year ('1998-1-1 ');
Select month (now);
Select MonthName (now ());

Converts characters into dates in the specified format
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put; // 1998-03-02

Convert date to character
SELECT DATE_ Format (now(), '% y year% m month% d') as out_ put;

5、 Other functions

SELECT VERSION(); //  SQL version
SELECT DATABASE();//  Current database
SELECT USER();// [email protected]

6、 Control function

If function
Select if (10 < 5, 'large', 'small')// Small

Use of case function 1: case is followed by a parameter, indicating a certain situation

Case the field or expression to judge
When constant 1 then the value 1 or statement 1 to be displayed;
When constant 2 then the value 2 or statement 2 to be displayed;

Select salary, Department_ id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
End as new salary
FROM employees;

Use of case function 2: case does not have parameters, indicating a fuzzy interval

SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
End as salary level
FROM employees;

This is the end of this article about the common functions of MySQL foundation. For more information about common MySQL functions, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!

Recommended Today

Supervisor

Supervisor [note] Supervisor – H view supervisor command help Supervisorctl – H view supervisorctl command help Supervisorctl help view the action command of supervisorctl Supervisorctl help any action to view the use of this action 1. Introduction Supervisor is a process control system. Generally speaking, it can monitor your process. If the process exits abnormally, […]