Common function

Concept: Encapsulate a set of logical statements in a method body, exposing the method name

Advantages: Hiding implementation details

Improved code reuse

Select function name () from table;

Characteristic: The name of the method

Function function

Common functions:

  • 1. Single-line functions

    Oncat, length, ifNULL, etc

  • 2. Grouping functions

    Do statistics, also known as statistical function

A single function

  1. Character function
  2. Mathematical function
  3. Date function
  4. Other functions
  5. Flow control function

1. Character functions

Length Gets the number of bytes of the parameter value

select length('str'); Select length(' zhang3 ') #Copy the code

Concat Concates a string

Select concat (str1, str2...).Copy the code

upper lower

select upper(str); Select lower(STR); # string lowercaseCopy the code

Substr intercepts a string

select substr('srting',7); Select substr('string',1,3) from 1; The second value indicates the length to be truncated, not the indexCopy the code

Instr displays the actual index of the string

select instr('string','str'); Note: If no corresponding string is found, return 0Copy the code

Trim Trim ‘before and after string

select trim(' str '); Select trim('a' from 'aaaaaaaa ') #Copy the code

Lpad implements the left padding of the specified length with the specified character

Select lpad('string',10,'*'Copy the code

Replace Replace string

Select replace(' Zhang Wuji falls in love with Zhou Zhiru ',' Zhou Zhiru ',' Zhao Min ') output: Zhang Wuji falls in love with Zhao MinCopy the code

2. Mathematical functions

Select round(1.65) from round; Select round(1.567,2) from ceil select ceil (1.002); Output: 2 #floor rounded down, returns <= the minimum certificate for this parameter select floor(); #truncate truncate SELECT TRUNCate (1.65,1); A -a/b*b select mod(10,3); Output :1 Note: If the dividend is positive, the result is positive, and vice versaCopy the code

3. Date function

Select now(); #curdate select curdate(); Select * from year(now()); select * from year(now()); select month(now()); select monthname(now()); # return month in EnglishCopy the code

#str_to_date converts date-formatted characters to dates in the specified format

Str_to_date ('9-13-1999','% M -% D -%Y') date_format Converts dates to charactersCopy the code

4. Process control function

If the function

Select if (10>5,'t','f');Copy the code

Case function

The use of a

Case Field or expression to be judged when On 1 THEN Value to be displayed When on 1 then value to be displayed... Else Want the real value or statement endCopy the code

Use two

Cese when 1 then display value 1 or statement 1 cese when 2 then display value 2 or statement 2... Else Displays the value n or statement n endCopy the code