MySQL Common functions

-----------------------------String Function---------------------------------------

----stitching strings, any string with null concatenation is null
Select Concat (' AAA ', ' BBB ', ' CCC '), concat (null, ' AAA ');

----Replace three characters from the 12th character to me
Select Insert (' Beijing2008you ', 12,3, ' me ');

----Subscript starting from 1
Select Insert (' Beijing ', +, ' oo ');

---case conversion
Select lower (' Beijing '), UPPER (' Beijing ');


---repeat
Select repeat (' MySQL ', 3);

---Remove the spaces on both sides, remove the left space, and remove the right repetition
Select Trim ("Simba"), LTrim ("Simba"), RTrim ("Simba");

---n characters to the left of the output, n characters to the right of the output, and NULL if n is null
Select Left (' beijing2008 ', 7), left (' Beijing ', null), right ("beijing2008", 4);

-----The leftmost character with the rightmost padding, knowing that the length is n
Select Lpad (' + ', ' ten ', ' Beijing '), Rpad (' n ', ', ' Beijing '), Lpad (' 4 ', ' Beijing ');

----Replacement string
Select replace (' Beijing ', ' I ', ' o ');


----Compare the size of the first string and the second one
Select strcmp (' BC ', ' BS '), strcmp (' B ', ' B '), strcmp (' C ', ' B ');

-----Returns a substring of Y-character length from the X-position of the string.

Select substring (' beijing2008 ', 8,4), substring (' beijing2008 ', 1,7);


------------------------------Numeric function----------------------------------


-----absolute value, the string is 0
Select ABS (4), ABS (4-5), ABS (' A '-' B ');


-----Returns the smallest integer greater than X
Select Ceil ( -0.8), Ceil (0.8), ceil (2.0);


----Returns the largest integer less than x, which is the exact opposite of Ceil's usage
Select Floor ( -0.8), floor (0.8);


-----NULL for any one parameter result is null
Select mod (15,10), mod (1,11), mod (null,10);

------produces a random number between 0 and 1.
Select rand (), Rand () *10;

Select Ceil (rand () *100), Floor (rand () *100);




---Returns the first number rounded after the decimal point, several
Select Round (1.1), round (1.567,2), round (on);


-----truncation x equals decimal place only one
Select truncate (123.284,1);




-------Date and Time functions--------------------------------------


----Current date Time
Select Curdate ();


Select Curtime ();


Select Now ();


-----Returns the UNIX timestamp for date

Select Unix_timestamp (now ());

----Returns the time at which a timestamp corresponds
Select From_unixtime (132454);


-----The week of the year, the first few years, hours and minutes

Select Week (now ()), Year (now ()), Hour (now ()), Minute (now ());

----Return Month in English
Select MonthName (now ());


-----Formatting Date
Select Date_format (now (), '%m,%d%y ');


----days of difference between two days

Select DateDiff ("2014-05-10", now ());

------The current date, the current date plus 31 days the next two months after one year of the current date interval= spacing
Select Now (), Date_add (today (), Interval Day), Date_add (now (), Interval ' 1_2 ' year_month);



-------Other----

Select Version ()

Select Database (), User ()

------Convenient IP or network segment comparison
-----The number that returns the IP address

Select Inet_aton (' 192.168.8.11 ')

---Returns the IP address of the number
Select Inet_ntoa (3452233445);



--Returns the form after the digital encryption
Select Password (123456);

Select MD5 (123456);

Copyright NOTICE: This article is the original blogger articles, reproduced please indicate the source.

MySQL Common functions