MySQL built-in functions

1 String functions
To view the ASCII code of a string: ASCII (str) STR returns 0 when empty
Select ASCII ("a");

To view the ASCII code corresponding characters: char (num)
Select char (num);

Stitching string concat (str1, str2 ...)
Select Concat (12,34, "AB");

Contains the number of characters: Length (str)
Select Length ("abc");

To intercept a string:
Left (Str,len) intercepts Str on the right side of Len
Right (Str,len) intercepts the STR-R-byte characters
SUBSTRING (str,pos,len) intercepts str and intercepts Len characters from Pos

Select substring ("ABCD", 2,3)

Remove spaces:
LTrim (str) returns a string that removes the left white space of Str
RTrim (str) returns a string that removes the right-hand space of Str
Trim (direction Str_del from str) returns the string after Str_del is removed in str by direction
Direction: Both sides, leading left, trailing right
Select Trim (Trailing "x" from "Xxaaaaxx")

A string that returns N spaces: space (n)
Select Space (10);

Replacement string: replace (str, from, to) returns the string from the from in Str replaced with to
Select ("AAVVCC", "VV", "BB");

Case conversion: Lower (str) upper (str)
Select Lower ("ABcDE");
Select Upper ("AbcDef");


2 Numeric functions
Absolute: ABS (NUM)
Select ABS (-5);

Find remainder: MoD (m,n) m%n
Select mod (m,n);
Select m% n;

Take integer Down: Floor (n)
Select Floor (4.9);

Rounding up: Ceiling (n)
Select Ceiling (2.1);

Rounded: Round (n, D) n represents number, d means reserved decimal place, default is 0
Select round (1.6);

Y power of x: Pow (x, y)
Select POW (5,3);

Get pi: Pi ()
Select PI ();

Random number 1 to 0 floating-point numbers: rand ()
Select rand ();

Trigonometric functions: reference documentation.


3rd Period and Time
To get an integer value:
Year (date) returns the numeric value of a date
Month (date) returns the months value of date
Day (date) returns the date value
Hour (time) returns the hour value of time
Minute (time) returns the number of minutes of time
Second (time) returns the number of seconds in time

Select year ("2008-7-15");

Date Calculation: Date + interval num type;
Select "2017-6-12" + interval 1 day;
--One day after query

Select "2017-6-12" + interval 1 month;
--One months after enquiry

Date formatting: Date_format (Date,format)
%Y Full year
%y abbreviated year
%m Month
%d days
24-hour system at%H
12-hour system at%h
%i min
%s seconds

Select Date_format ("2015-11-12", "%Y%m%d")

Current date: Current_date ()
Select Current_date ();

Current time: Current_time ()
Select Current_time ();

Current date Time: Now ()
Select Now ();

4 Type conversions
Cast and Convert:cast (value as type), convert (value, type)
Types of type:
Binary binary binary
Char character type can specify length char (10)
Date Dates
Time period
DateTime Date Time
Decimal (n, m) floating-point numbers total m-bit decimal m bits
Signed signed integer
unsigned unsigned integer

Select CONVERT ("123.78", signed);
Select cast ("125.83" as signed);

5 Process Control:
Case Syntax:
Case value when condition 1 then result 1 when condition 2 then result 2 ... when condition n then result n else result
Key =case When condition 1 then result 1 when condition 2 then result 2 ... when condition n then result n end result

Select Case 5 If 3 then "three" while 5 then "five" else "haha" end as result;

If statement: if (condition, result 1, result 2) returns result 1 if condition is true 2
Select if (1>2,3,4) as result;

Ifnull statement: ifnull (expression 1, expression 2) returns an expression of 2 if expression 1 is empty, otherwise returns an expression of 1
Select Ifnull (Gender, "none") as result;

Nullif statement: nullif (expression 1, expression 2) returns null if expression 1 and expression 2 are equal, otherwise returns the result of expression 1
Select Nullif (1,0);



6 Cryptographic functions
MD5 encryption means char (32)
SHA1 encryption means char (40)
SHA1 encryption Method: password (word)
SELELCT password ("abc")

MySQL built-in functions