Environmental Science:

CentOS 7,mysql5.7

Summary description:

This article mainly describes how to use mysql common functions, such as string correlation function, date format function, calculation function, etc

Steps:

1. Mathematical function

Functions for mathematical calculation:

-- ABS(X):Return X Absolute value
select ABS(-32);-- 32

-- MOD(N,M)or%:Return N cover M Remainder
select MOD(15,7);-- 1
select 15 % 7; -- 1

-- FLOOR(X):Return not greater than X Maximum integer value of
select FLOOR(1.23); -- 2
select FLOOR(-1.23); -- -2

-- CEILING(X)/ceil(x):Return no less than X Minimum integer value of
select CEILING(1.23);-- 2 
select CEILING(-1.23); -- -1

ROUND(X) :Return parameter X A rounded integer. 
select ROUND(1.58); -- 2
select ROUND(-1.58); -- -2

round(x,y):Return value x Have y Is the number of decimal results (rounded)
SELECT round(3.1415926,2);-- 3.14

rand():Return random number
select rand();-- 0.5911854436538978

truncate(x,y):Return value x Intercept y Decimal results (not rounded)
select truncate(3.1415926,4);-- 3.1415

2. Date time function

Function to process date time:

-- curdate()/current_date():Get current date
SELECT curdate();-- 2018-08-09
SELECT current_date();-- 2018-08-09

-- curtime()/current_time(): Get current time
SELECT curtime();-- 15:38:54
SELECT current_time();-- 15:38:54

-- now():Get current date time
select now();-- 2018-08-09 15:40:09

-- month(date),monthname(date):Get date month
SELECT MONTH (now());-- 8
SELECT monthname(now());-- August

-- week(date): Get date weeks
select week(now());-- 31

-- year(date):Get date years
select year(now());-- 2018

-- hour(time):Get time
select hour(now());-- 15

-- minute(time):Get time minutes
select minute(now());-- 47

-- DAYOFWEEK(date)/DAYNAME(date)/WEEKDAY(date):Get time weeks
select DAYOFWEEK(NOW());-- 5
select DAYNAME(now());-- Thursday
select WEEKDAY(now());-- 3

-- DATE_ADD(date,INTERVAL expr type)/DATE_SUB(date,INTERVAL expr type):Add date/Reduced operations, accurate to seconds
SELECT '2018-08-09'+INTERVAL 1 DAY;-- 2018-08-10
SELECT '2018-08-09'-INTERVAL 1 SECOND;-- 2018-08-08 23:59:59
SELECT DATE_ADD('2018-08-08 23:59:59',INTERVAL 1 SECOND);-- 2018-08-09 00:00:00
SELECT DATE_SUB('2018-08-09 00:01:01',INTERVAL '1:1' MINUTE_SECOND);-- 2018-08-09 00:00:00

-- date_format('time','format')/time_format(time,format):Date time to string
select date_format(now(), '%Y%m%d%H%i%s');-- 20180809160315
select time_format(now(),'%H:%i:%s');--16:03:15

-- str_to_date(str, format): String to date
select str_to_date('08.09.2018 16:06:30', '%m.%d.%Y %H:%i:%s');-- 2018-08-09 16:06:30

-- makdedate(year,dayofyear)/maketime(hour,minute,second): Pieced together date and time functions
select makedate(2001,31); -- '2001-01-31'
select makedate(2001,32); -- '2001-02-01'
select maketime(12,15,30); -- '12:15:30'

-- unix_timestamp()/unix_timestamp(date)/from_unixtime(unix_timestamp)/from_unixtime(unix_timestamp,format):Unix Time stamp, date conversion function
select unix_timestamp(); -- 1533802315
select unix_timestamp(now());-- 1533802315
select from_unixtime(1533802315); -- 2018-08-09 16:11:55
select from_unixtime(1533802315, '%Y%m%d%H%i%s'); -- 20180809161155

3. String function

Used to control string functions:

-- ASCII(str):Return string str Of the leftmost character ASCII Code value. If str Is an empty string, returning 0. If str yes NULL，Return NULL
SELECT ASCII("dd");-- 100
SELECT ASCII("dc");-- 100

-- CONCAT(str1,str2,...): Returns the string from the parameter link. If any parameter is NULL，Return NULL. Can have more than 2 parameters. A number parameter is transformed into an equivalent string form
select CONCAT('My', 'S', 'QL'); -- MySQL
select CONCAT('My', NULL, 'QL'); -- NULL
select CONCAT(14.3); -- 14.3

-- LENGTH(str):Return string str Byte length of
select LENGTH('text'); -- 4
select LENGTH('Character string');-- 9

-- CHAR_LENGTH(str):Used to get string length
select CHAR_LENGTH('text'); -- 4
select CHAR_LENGTH('Character string');-- 3

-- LOCATE(substr,str):Return substring substr In string str The first place to appear, if substr Not in str Inside, back to 0
select LOCATE('bar', 'foobarbar'); -- 4
select LOCATE('xbar', 'foobar'); -- 0

-- INSTR(str,substr):Return substring substr In string str The first place in
select INSTR('foobarbar', 'bar'); -- 4
select INSTR('xbar', 'foobar'); -- 0

-- LEFT(str,len)/RIGHT(str,len):Return string str The most left/Right side len Character
select LEFT('foobarbar', 5); -- fooba
select RIGHT('foobarbar', 4); -- rbar

-- SUBSTRING(str,pos):From string str From pos Return a substring
select SUBSTRING('foobarbar',5);-- arbar

-- TRIM(str):Return string str，All prefixes or suffixes have been removed
select TRIM(' xbar ');-- xbar

-- LTRIM(str)/RTRIM(str): Back to before deleting/String with space character after str. 
select LTRIM(' xbar');-- xbar
select RTRIM('xbar ');--xbar

-- REPLACE(str,from_str,to_str):Return string str，Its string from_str All occurrences of by string to_str replace
select REPLACE('xbar', 'x', 'bar');-- barbar

-- REPEAT(str,count):Return to be repeated by countTimes Secondary string str A string of. If count <= 0，Returns an empty string. If str or count yes NULL，Return NULL
select REPEAT('bar', 3);-- barbarbar

-- REVERSE(str):Returns a string that reverses the character order str. 
select REVERSE('bar');-- rab

-- INSERT(str,pos,len,newstr):Return string str，In position pos Starting substring and len Character long substring by string newstr Instead. 
select INSERT('whatareyou', 5, 3, 'is');-- whatisyou

-- strcmp(str1,str2):Used to compare the size of two strings. Returns 1 when left is greater than right, 0 when left is equal to right, and returns when left is less than right-1
SELECT strcmp('ab','ac');-- -1

-- Capitalization: upper(x),ucase(x)；A lowercase letter lower(x),lcase(x):Letter case conversion function;
SELECT UPPER("abc");-- ABC
SELECT UCASE("abc");-- ABC
SELECT LOWER("ABC");-- abc
SELECT LCASE("ABC");-- abc

-- find_in_set(str1,str2):Return string str1 stay str2 Location in, str2 Contains a number of comma separated strings str2 See a list. The element is multiple strings. The search result is str1 stay str2 Index position in this list, starting from 1)
SELECT FIND_IN_SET('abc','123,456,abc');-- 3

-- field(str,str1,str2,str3...):And find_in_set Similar, but str2 From a list like string to multiple strings, return str stay str1,str2,str3...Location in.
SELECT FIELD('abc','123','456','abc');-- 3

-- elt(index,str1,str2,str3...):Gets the string of the specified location
SELECT elt(3,'123','456','abc');-- abc

4. Logic function

Functions for logic control:

-- CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END: In the return result of the first scenario, value=compare-value. The return result of the second scenario is the real result of the first scenario. If there is no matching result value, the returned result is ELSE If not ELSE Part, the return value is NULL. 
SELECT
	CASE 1
WHEN 1 THEN
	2
ELSE
	3
END;-- 2

-- IF(expr1,expr2,expr3): If expr1 yes TRUE (expr1 <> 0 and expr1 <> NULL)，be IF()The return value of is expr2; Otherwise, the return value is expr3. IF() The return value of is a numeric value or a string value, depending on the context
SELECT IF(1>2,2,3);-- 3

-- STRCMP(expr1,expr2): If the strings are the same, STRCMP()Returns 0 if the first parameter is less than the second according to the current sorting order-1，Otherwise return 1
select STRCMP('text', 'text2');-- -1

-- SELECT IFNULL(expr1,expr2): If expr1 If it is blank, return expr2 Otherwise return expr1
SELECT IFNULL(1,2);-- 2

5. Encryption function

Functions for encryption:

-- MD5(str): Function can be used to str Encryption. MD5(str)Function mainly encrypts ordinary data. Use below MD5(str)Function is a string“ abcd"encryption
SELECT MD5('abcd');-- e2fc714c4727ee9395f324cd2e7f331f

-- ENCODE(str,pswd_str): Functions can use strings pswd_str To encrypt a string str. The result of encryption is a binary number, which must be used BLOB Type to save it.
SELECT ENCODE("abcd","evan");

-- DECODE(crypt_str,pswd_str)Functions can use strings pswd_str Come for crypt_str Decrypt. crypt_str It is through ENCODE(str,pswd_str)Encrypted binary data. Character string pswd_str Should match the string at encryption time pswd_str It's the same. Use below DECODE(crypt_str,pswd_str)by ENCODE(str,pswd_str)Encrypted data decryption.
SELECT DECODE(ENCODE("abcd","evan"),"evan");