catalogue
1.Mysql function classification
2. Common mathematical functions
1.Mysql function classification
(1) Single line function
characteristic:
- A single line function operates on a single line
- Each row returns a result. The returned value may be inconsistent with the original parameter data type
- Single line functions can be written in SELECT, WHERE, ORDER BY clauses
- Some functions have no parameters, and some functions include one or more parameters
- Functions can be nested
(2) Multiline function
Features: multiple lines return one result
2. Common mathematical functions
ABS(x): returns the absolute value of X;
SQRT(x): returns the square root of a nonnegative number x;
PI(): return PI;
MOD(x,y) or (x% y): returns the remainder of X divided by Y;
CEIL(x), CEILING(x): returns the minimum integer value greater than or equal to X;
FLOOR(x): returns the maximum integer value less than or equal to X;
ROUND(x,y): returns an integer rounded to the nearest y digits after the decimal point;
TRUNCATE(x,y): returns the number x in Y digits after the discarded decimal point;
RAND(): generate different random numbers each time;
SIGN(x): returns the symbol of the parameter;
POW(x,y) and POWER(x,y): return the result value of the Y power of X;
EXP(x): returns the value after the power of X with e as the base;
LOG(x): returns the natural logarithm of X, the logarithm of X relative to cardinality e;
LOG10(x): returns the logarithm of base 10 of X;
RADIANS(x): convert parameter x from angle to radian;
DEGREES(x): converts the parameter x from radians to degrees.
SIN(x): returns the sine of X, where x is the radian value;
ASIN(x) returns the inverse sine of X, that is, the sine is the value of X;
COS(x): returns the cosine of X;
ACOS(x): returns the X arccosine
TAN(x): returns the tangent of X;
ATAN(x): returns the arctangent of X;
example:
select sqrt(4) from dual;--2 select PI() from dual;--3.141593 select mod(10, 3) , (10 % 3) from dual;--All 1 select ceil(12.8), floor(12.8), round(12.8) from dual;--13 12 13 select pow(2, 3) from dual;--8 select sign(-8), sign(8), sign(0) from dual;-- (-1 1 0)
3. String function (key)
CHAR_LENGTH(str): returns the number of characters contained in the string str;
LENGTH(str): returns the length of the string str;
CONCAT(s1,s2,...): String connection;
CONCAT_WS(x,s1,s2,...): string connection, where x is the separator of other parameters;
INSERT(s1,x,len,s2): returns the string s1 and inserts the string s2 into s1;
LOWER (str)|LCASE (str): convert all strings to lowercase letters;
UPPER(str)|UCASE(str): convert all strings to uppercase letters;
LEFT(s,n): returns the leftmost character of the specified length;
RIGHT(s,n): returns the rightmost character of the specified length;
LPAD(s1,len,s2)| RPAD(s1,len,s2): fill string function;
TRIM(s1 FROM s)|LTRIM(s) | RTRIM(s): delete the space function;
REPEAT(s,n): repeatedly generate string function;
SPACE(n): returns a string consisting of N spaces;
REPLACE (s, s1, s2): string replacement function;
STRCMP(s1,s2): compare string size function;
SUBSTRING(s,n,len): get substring function;
LOCATE(str1,str)|POSITION(str1 IN str)|INSTR(str, str1): function of matching substring start position;
REVERSE(s): reverse the string s;
ELT(N, string 1, string 2, string 3,...): returns the specified position function;
example:
select char_length('my name is 123') from dual;-- 14
select length('tom') from dual;-- 3
select concat('I ', 'love ', 'mysql') from dual;-- The result is'I love mysql'
select concat_ws(' ', 'I', 'am', 'student!') from dual;-- The result is'I am student!'
select insert('Iam', 2, 1, '\'') from dual;-- In the second position, insert the of length 1'，The result is I'm
select left('LQH', 2), substring('LQH', 1, 2) from dual;-- The first two characters of the string are extracted, and the result is“ LQ"
select locate('am', 'I am student, I am stupid boy'), position('am' in 'I am student, I am stupid boy'), instr('I am student, I am stupid boy', 'am') from dual;-- Are in the query string“ am"For the first position, the results are 3
select repeat('.', '6'), space(6) from dual;-- Here, repeat Repeat the output 6 times“."，and space Return 6 spaces
select strcmp('a', 'B'), strcmp('a', 'A'), strcmp('z', 'G') from dual;-- Compare the string size because the former is smaller than the latter-1，Equal returns 0, otherwise 1;The result is -1 0 1
select reverse('I miss you') from dual;-- String reversal with result“ uoy ssim I"
select elt(2, '123', 'abc', 'hhh', 'ddd') from dual;-- Returns the second string with the result abc"
select lpad(123, 6, '*'), rpad(123, 6, '*') from dual;-- The left and right fills are not enough. Fill with the specified character and the result is“***123" And "123"***"
select upper('i love mysql'), lower('I loVE MysQL') from dual;-- Turn large and small, and the result is“ I LOVE MYSQL","i love mysql"
4. Date and time functions
Current() and CURRENT_DATE(): get the current date function;
NOW(): returns the current date and time of the server;
CURTIME(): returns the current time, including only hours, minutes and seconds;
UTC_DATE(): function that returns the world standard time and date;
UTC_TIME(): returns the world standard time function;
TIMEDIFF(expr1, expr2): returns the time difference between two dates;
DATEDIFF(expr1, expr2): returns the number of days after subtracting two dates;
DATE_ADD(date,INTERVAL expr type): date plus a time interval value;
DATE_SUB(date,INTERVAL expr type): subtract a time interval value from the date;
DATE(date), TIME(date), YEAR (date): select each part of date and time:
EXTRACT(unit FROM date): extract an individual part or combination FROM the date;
DAYOFWEEK(date), DAYOFMONTH(date), DAYOFYEAR(date): returns the day of the week, month, or year
DAYNAME, MONTHNAME: returns the name of the week and month of the date;
DATE_FORMAT(date,format): format the date;
TIME_ Format (time, format): format the time;
Timestamp diff (unit, datetime_expr1, datetime_expr2): calculate the date time difference
(1) Where, DATE_ADD(date,INTERVAL expr type) function and date_ The following values can be selected for the type parameter of the sub (date, interval expr type) function:
| type | explain |
|---|---|
| HOUR | hour |
| MINUTE | minute |
| SECOND | second |
| MICROSECOND | millisecond |
| YEAR | year |
| MONTH | month |
| DAY | day |
| WEEK | week |
| QUARTER | season |
| YEAR_MONTH | Year and month |
| DAY_HOUR | Date and hour |
| DAY_MINUTE | Days and minutes |
| DAY_SECOND | Days and seconds |
| HOUR_MINUTE | Hours and minutes |
| HOUR_SECOND | Hours and seconds |
| MINUTE_SECOND | Minutes and seconds |
(2) Where, DATE_FORMAT(date,format) and time_ For the parameter format of format (time, format) function, you can select the following parameters:
| format | describe |
|---|---|
| %a | Sunday name |
| %b | Month name |
| %c | Month, value |
| %D | Days of the month with English prefix |
| %d | Day of the month, value (00-31) |
| %e | Day of the month, value (0-31) |
| %f | subtle |
| %H | Hours (00-23) |
| %h | Hours (01-12) |
| %I | Hours (01-12) |
| %i | Minutes, value (00-59) |
| %j | Days of the year (001-366) |
| %k | Hours (0-23) |
| %M | Month name |
| %m | Month, value (00-12) |
| %p | AM or PM |
| %r | Time, 12 hours (hh:mm:ss AM or PM) |
| %S | Seconds (00-59) |
| %s | Seconds (00-59) |
| %T | Time, 24 hours (hh:mm:ss) |
| %U | Week (0-53), Sunday is the first day of the week |
| %u | Week (00-53), Monday is the first day of the week |
| %V | Week (01-53), Sunday is the first day of the week, used with% X |
| %v | Week (01-53), Monday is the first day of the week, used with% x |
| %W | Sunday name |
| %w | Days of the week (0 = Sunday, 6 = Saturday) |
| %X | Year, where Sunday is the first day of the week, 4 digits, used with% V |
| %x | In, Monday is the first day of the state, 4 digits, used with% v |
| %Y | Year, 4 |
| %y | Year, 2 |
(3) Examples
SELECT now(),date(now()); -- date
SELECT now(),time(now()); -- time
SELECT now(),year(now()); -- year
SELECT now(),quarter(now()); -- quarter
SELECT now(),month(now()); -- month
SELECT now(),week(now()); -- week
SELECT now(),day(now()); -- day
SELECT now(),hour(now()); -- hour
SELECT now(),minute(now()); -- minute
SELECT now(),second(now()); -- second
SELECT now(),microsecond(now()); -- Microsecond
-- EXTRACT(unit FROM date): Extract a single part or combination from a date
SELECT now(),extract(YEAR FROM now()); -- year
SELECT now(),extract(QUARTER FROM now()); -- quarter
SELECT now(),extract(MONTH FROM now()); -- month
SELECT now(),extract(WEEK FROM now()); -- week
SELECT now(),extract(DAY FROM now()); -- day
SELECT now(),extract(HOUR FROM now()); -- hour
SELECT now(),extract(MINUTE FROM now()); -- minute
SELECT now(),extract(SECOND FROM now()); -- second
SELECT now(),extract(YEAR_MONTH FROM now()); -- years
SELECT now(),extract(HOUR_MINUTE FROM now()); -- time
SELECT dayname('2022-01-01') from dual;-- Saturday
SELECT timestampdiff(month, '2020-05-22' ,'2021-12-01') from dual;-- Subtract the previous date from the later date and the result is 18
SELECT date_add('2022-01-01', interval 1 month) from dual;-- Plus January, the result is 2022-02-01
SELECT date_sub('2022-01-01', interval 1 month) from dual;-- Minus January, the result is 2021-12-01
SELECT date_format('2022-01-31', '%Y/%m/%d') from dual;-- Format the output and the result is 2022/01/315. Process control function
(1)CASE
① Syntax:
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
perhaps
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
② example:
select case 'C' when 'A' then 'Very Good!' when 'B' then 'Good!' when 'C' then 'Normal!' when 'D' then 'Bad!' else 'Worse!' end from dual;-- The result is Normal! select case when sal < 1000 then 'D' when sal < 3000 then 'C' when sal < 5000 then 'B' when sal < 6000 then 'A' end from t_emp;-- Output when employee salary is less than 1000 D，Less than 3000 output C，Less than 5000 output B，Less than 6000 output A
(2)IF
① Syntax:
IF(expr1,expr2,expr3)
② Example:
select if(1 < 0, 'true', 'false') from dual; -- similar java When the first parameter is true, the second value is displayed; Displays the third value when false
(3)IFNULL
① Syntax:
IFNULL(expr1,expr2)
② Example:
select ifnull(null, 'Not empty') from dual; -- If the first parameter is empty, the second parameter is returned; otherwise, the first parameter is returned -- amount to coalesce Function, the result is "not empty" select coalesce(null, 'Not empty') from dual;
(4)NULLIF
① Syntax:
NULLIF(expr1,expr2)
② Example:
select nullif(10, 10) from dual;-- The result is null -- Returns if the first parameter is equal to the second parameter null；Otherwise, the first parameter is returned
6. Other functions
Database(): returns the default (current) database name using the utf8 character set
Version(): returns a string indicating the MySQL server version.
User(): returns the current MySQL user name and host name
Inet_aton(): give a point address representation of a network address as a string, and return an integer representing the address value.
Inet_ntoa(): given a digital network address, returns the point address representation of the address as a string.
Password(str): calculates and returns the password string from the original password str, and returns NULL when the parameter is NULL.
Md5(str): calculate an MD5 128 bit check sum for the string.
example:
select database() from dual;-- myshop
select version() from dual;-- 8.0.27
select user() from dual;-- root@localhost
select Inet_aton('192.168.254.3'), inet_ntoa('3232300547') from dual;-- 3232300547 192.168.254.3
select md5('123456') from dual; -- e10adc3949ba59abbe56e057f20f883e