[Cicada Hall Learning note]_mysql function

For advanced enquiries, see:

http://www.w3school.com.cn/sql/
Http://www.cnblogs.com/kissdodog/p/4168721.html

--------------------string Function-------------------
--Capitalization conversion ucase (), Upper ()
SELECT UCASE (ename) from EMP;
SELECT UPPER (ename) from EMP;

--Lowercase conversion lcase (), lower
SELECT LCASE (ename) from EMP;
SELECT LOWER (ename) from EMP;
--String Stitching concat ()
SELECT CONCAT (ename, '. com ') ' Domain name ' from emp;

--MySQL string intercept function: Left (), right (), substring (), Substring_index ().
-and mid (), substr (). where mid (), substr () is equivalent to the substring () function,
--intercept substr--http://www.cnblogs.com/zdz8207/p/3765073.html
SELECT SUBSTR (ename,1,1) from EMP;
SELECT SUBSTRING (ename,2) from EMP;

--Display all employee names in uppercase letters
SELECT CONCAT (UPPER (SUBSTR (ename,1,1)), LOWER (SUBSTR (ename,2))) as ' Employee name ' from EMP;
--To display employee information in lowercase letters, in the form of an uppercase letter
SELECT CONCAT (LCASE (SUBSTR (ename,1,1)), UCASE (SUBSTR (ename,2))) as ' Employee name ' from EMP;

------------------------Mathematical Functions------------------------------
--The round () function is used to round a numeric field to a specified number of decimal digits.
SELECT ROUND (19.3445,2); --19.34
SELECT ROUND (19.3495,2); --19.35

--MoD () function for modulus value
SELECT MOD (2,3); --2%3 = 2

--SQRT (x) returns the square root of X
SELECT SQRT (25); --5

--ceil () rounding up
SELECT Ceil (2.3); --3

--Floor (x) rounding down
SELECT Floor (2.9)--2

--ABS (x) returns the absolute value of X
SELECT ABS (-1)--return 1

--RAND () returns the random number of 0->1
SELECT RAND (); --0.93099315644334

--RAND (x) returns the random number of 0->1 with the same number of random numbers returned at the same X value

SELECT RAND (2); --1.5865798029924

-Displays the daily wage of all employees in one months and 30 days
SELECT ename,sal, hiredate from EMP WHERE Day (Last_day (hiredate)) = ' 30 '
--Select Hiredate,day (Last_day (hiredate)) from EMP

------------------------Time function-----------------------
--Curdate (), current_date () returns the current date
SELECT curdate (); -2017-09-28
SELECT current_date ();

--Now (), Current_timestamp (), localtime (), Sysdate (), Localtimestamp ()
--Returns the current date and time
SELECT now (); --2017-09-28 18:58:50
SELECT sysdate ();
-year () returns years
SELECT year (now ()); --2017
-month () returns months
SELECT MONTH (now ()); --9

-Day () return date
SELECT Day (now ())--28
--DATEDIFF () returns the number of days between two dates
SELECT DATEDIFF (' 2008-12-30 ', ' 2008-12-29 ') as Diffdate-1

--Adddate (D,n) calculates the date d plus n days in fact
SELECT Adddate (Now (), 1); --2017-09-29 19:13:47 today is 2017-09-28 19:13:47

--Last_day () Get the last day of the month
SELECT Last_day (now ()); -2017-09-30

--WEEKDAY () when in for a few days
SELECT WEEKDAY (now ())--3 Today Thursday, the first day is 0

--DayOfMonth () Day of the month
SELECT DayOfMonth (now ()); --28

--Subdate (D,interval expr type) Date D minus the date after one time period
SELECT subdate (' 2011-11-11 11:11:11 ', INTERVAL 5 MINUTE)--2011-11-11 11:06:11 (the value of type is similar to the one listed above)

--Time format--http://www.w3school.com.cn/sql/func_date_format.asp
SELECT Date_format (now (), '%Y year%m month%d day%h%i minute%s seconds ');

--Adddate (D,interval expr type calculates the start date D plus the date after a time period
SELECT adddate (' 2011-11-11 11:11:11 ', 1)--2011-11-12 11:11:11 (default is days)
SELECT adddate (' 2011-11-11 11:11:11 ', INTERVAL 5 MINUTE); --plus five minutes

--Return to employee information for 8 months in the job
SELECT * from emp WHERE adddate (hiredate, INTERVAL 8 MONTH) = Now ();
--Find out all employees employed on the third day of the month
SELECT ename,hiredate from EMP WHERE Day (hiredate) = Day (Last_day (HireDate)-2);
--Show Employee entry days
SELECT Ename,datediff (now (), HireDate) ' Entry days ' from EMP;

--Displays the employee name and number of days of employment for the service over 10 years
SELECT Ename,datediff (now (), HireDate) ' Hire days ' from EMP WHERE now () > Adddate (hiredate,interval);
-Get the Week/month/The first day of the year, the last day
SELECT Subdate (now (), INTERVAL WEEKDAY (today ()) day)--when the first days of the week
SELECT Subdate (now (), INTERVAL DayOfMonth (Now ()) – 1 day)--the first of the month
SELECT Subdate (now (), INTERVAL DayOfYear (Now ()) – 1 day)--the first days of the year
SELECT Adddate (now (), INTERVAL 6-weekday (today ()) day); -When the last day of the week
SELECT Last_day (now ());--Last day of the month
SELECT Adddate (now (), INTERVAL DayOfYear (now ()), INTERVAL 1 years) ' Last day of the year '--the last day of the year

------------------------conversion function-----------------------------------------------
--To_days (d) Calculate Date D days from January 1, 00
SELECT To_days (now ());

--Str_to_date (d, ' format '); String Turn time
SELECT str_to_date (hiredate, '%y-%m-%d%h:%i:%s ') from EMP;

--Show employees who entered the workforce in 1980

SELECT * from EMP WHERE year (hiredate) = 1980;
--Show all employees who have been in employment in December
SELECT * from emp WHERE MONTH (hiredate) = 12;
-------------------------------Encryption-------------------------------

SELECT MD5 (DEPTNO) from EMP;

[Cicada Hall Learning note]_mysql function