Self-study of MySql built-in functions knowledge points summary

String functions

Check the ascii code value of the character ascii(str), and return 0 if str is an empty string

select ascii('a');

View the character char (number) corresponding to the ascii code value

select char(97);

Concatenate strings concat(str1,str2...)

select concat(12,34,'ab');

Contains the number of characters length(str)

select length('abc');

Intercepting a string

• left(str,len) returns the leftmost len ​​characters of the string str
• right(str,len) returns the right end of the string str.
• substring(str,pos,len) returns len characters starting from position pos of the string str

select substring('abc123',2,3);

Remove spaces

ltrim(str) returns the string str with left spaces removed
rtrim(str) returns the string str with right spaces removed
trim([direction remstr from str) returns the string str after deleting remstr from one side. Direction words include both, leading, and trailing, which means both sides, left, and right.

select trim(' bar ');
select trim(leading 'x' FROM 'xxxbarxxx');
select trim(both 'x' FROM 'xxxbarxxx');
select trim(trailing 'x' FROM 'xxxbarxxx');
SELECT TRIM(LEADING ' ' FROM ' my ');

Returns a string consisting of n space characters space(n)

select space(10);

Replace string replace(str,from_str,to_str)

select replace('abc123','123','def');

Case conversion, the function is as follows

• lower(str)
• upper(str)

select lower('aBcD');

Mathematical functions

Find the absolute value abs(n)

select abs(-32);

Find the remainder when m is divided by n, mod(m,n), same as operator %

select mod(10,3);
select 10%3;

Floor floor(n), which represents the largest integer not greater than n

select floor(2.3);

Ceiling(n), which means the largest integer not less than n

select ceiling(2.3);

Find the rounded value round(n,d), where n represents the original number and d represents the decimal place, the default value is 0

select round(1.6);

Find x raised to the power of y pow(x,y)

select pow(2,3);

Get PI()

select PI();

Random number rand(), a floating point number with a value between 0 and 1.0

select rand();

There are many other trigonometric functions. You can consult the documentation when using them.

Date and time functions

Get the sub-value, the syntax is as follows

• year(date) returns the year of date (in the range 1000 to 9999)
• month(date) returns the month value in date
• day(date) returns the date value in date
• hour(time) returns the hour of time (range 0 to 23)
• minute(time) returns the minute of time (range 0 to 59)
• second(time) returns the seconds of time (range 0 to 59)

select year('2016-12-21');

Date calculation, use the +- operator, the keyword after the number is year, month, day, hour, minute, second

select '2016-12-21'+interval 1 day;

Date format date_format(date,format), the available values ​​of format parameter are as follows

Get the year %Y and return a 4-digit integer

* Get year%y, return a 2-digit integer

* Get month%m, the value is an integer from 1 to 12

Get day %d, return integer

* When obtaining %H, the value is an integer from 0 to 23

* When getting %h, the value is an integer from 1 to 12

* Get the score %i, the value is an integer from 0 to 59

* Get the second %s, the value is an integer from 0 to 59

select date_format('2016-12-21','%Y %m %d');

Current date current_date()

select current_date();

Current time current_time()

select current_time();

Current date and time now()

select now();

The above are all the relevant knowledge points introduced this time. If you have any additional needs, please contact the editor of 123WORDPRESS.COM.