A brief introduction to MySQL functions

-- abs() absolute value SELECT ABS (-100);

-- pi() returns the regularity of a circle SELECT PI();

-- SQRT() returns the square of a non-negative number x SELECT SQRT(2);

-- POW returns x raised to the power of y. SELECT POW(2,10);
SELECT POWER(2,10)

-- CELL() or 
SELECT CEIL(3.14); -- round up to the nearest integer -- floor()
SELECT FLOOR(3.14); -- Round down to an integer -- round() can round to an integer and also keep decimals SELECT ROUND(3.4);
SELECT ROUND(3.5);
SELECT ROUND(3.7,2)


-- pow() x to the power of y SELECT POW(2,3);


-- random Random 0 to 1 
SELECT RAND();

-- truncate() retains decimals SELECT TRUNCATE(3.14159265758,3);
SELECT TRUNCATE(RAND()* 1000 ,3);

-- String function -- RIGHT

SELECT LENGTH('fdsajfadslksdafk');

SELECT CHAR_LENGTH('fdsajfadslksdafk');

-- lower upper --Change all to uppercase and lowercase SELECT UPPER('fdsajfadslksdafk');
SELECT LOWER('AFASDF');

-- trim() removes spaces at both ends SELECT TRIM(' abc ' )
SELECT TRIM('afdjkadsfjkll asd;fasf ');

-- reverse() reverse SELECT REVERSE('abcdefg');
SELECT REVERSE(TRIM('afdjkadsfjkll asd;fasf '));、


-- replace(str,len1,len2) len1 replaces len2
SELECT REPLACE('Everyone in the QQ group is talented and speaks well', 'Talent', 'Genius');

SELECT REPLACE('路展其是班草', '阿呆呆', '曹某某');

-- Intercept y Intercept z 
SELECT SUBSTRING('Everyone in prison is talented and speaks well',4,5);

-- Date function -- System current date sysdate()
SELECT SYSDATE() System current date; -- Hours, minutes, seconds -- (date and time)
SELECT CURDATE() System current date; -- Current date -- (date)
SELECT CURTIME() system current time;
-- (time)

-- dayofweek() Today is week - month - year
SELECT DAYOFYEAR(SYSDATE()) Today is the day of the year;
SELECT DAYOFMONTH(SYSDATE()) Today is the first day of this month; 
SELECT DAYOFWEEK('2021-9-19') What day of the week is today? -- 1-7

--weekday 
SELECT WEEKDAY(SYSDATE()) + 1 What day is today? -- 0-6

-- dayname day of the week SELECT DAYNAME('2001-9-18') 

-- year month day hour minute second
SELECT YEAR(SYSDATE()) YEAR;
SELECT MONTH(SYSDATE()) month;
SELECT DAY(SYSDATE()) DAY;

SELECT HOUR(SYSDATE());
SELECT MINUTE(SYSDATE()) MINUTE;
SELECT SECOND(SYSDATE()) seconds; 
-- Week SELECT WEEK(SYSDATE()) week; 


-- adddate(date,interval expr type) adds time to the date -- Assuming you have 50 years left to live, what will the time be in 50 years? SELECT ADDDATE(NOW(),INTERVAL 50 YEAR);
SELECT ADDDATE(NOW(), INTERVAL 50 SECOND);

-- datediff(date1, date2) The interval between two dates SELECT ABS(DATEDIFF('2005-8-26','2021-9-23')); -- 1984 - 3 - 24


-- Assuming that he was born on July 15, 2005, how many minutes did he live? -- Assuming that he has 80 years to live, how long will it be after 80 years? SELECT ABS(DATEDIFF('2005-7-15',SYSDATE()) ) * 1444;

SELECT ADDDATE(NOW(), INTERVAL 80 YEAR);

-- Encryption function SELECT PASSWORD('123456') cannot be reversed after encryption;
SELECT PASSWORD('666666') Encryption cannot be reversed;

--MD5
SELECT PASSWORD('123456') cannot be reversed after encryption MD5('123456') can be reversed after encryption -- View the version SELECT VERSION();
SELECT CHARSET('123456');


-- md5 encryption -- Snowflake Twitter algorithm