Eight functions in the database (aggregation, number, string, date and time, condition, system information, encryption, formatting)

A. function
1. Aggregation Functions
SUM () Avg () Count () min () max ()
2. Mathematical Functions
Mainly used for processing numbers including integral type floating point type
/* Math function */
SELECT Floor(1.5)/* Returns the largest integer less than or equal to the parameter */floor down rounding
SELECTRAND()/* Returns a random number of 0-1 */
SELECTROUND(RAND () *10)/* Returns the nearest integer from round (x) x rounded after the decimal point */
SELECTROUND(1.23456,3);/*round (x, y) retains the y digit after the decimal point but is rounded when it is intercepted */
3. String Functions
The most commonly used type of function is primarily for working with strings in tables
/* String function */
/*lower Lowercase Upper Capital * /
SELECT LOWER (ename) from EMP;
SELECT UPPER (ename) from EMP;
/* Returns the length of the stringLength (x)
Get all employees with a name length of 5 in the EMP table */
SELECT * from emp WHERE LENGTH (ename) =5
/* Combine multiple strings into a single string */
SELECTCONCAT("abc", "123456")
/* Replace replace (STR,SEARCH_STR,REPLACE_STR) in Str to replace SEARCH_STR with REPLACE_STR */
Select REPLACE ("Hello Java 1801 ok", "Java", "Thousand Front");
/* intercept substring (str,position,length) from the STR string position start with length characters The default index starts at 1 */
Select SUBSTRING ("Hello java", +);
/* Go to Space LTrim (str) RTRIM (str) */
Select LTRIM ("3018")//Go left space
Select RTRIM ("3018")//Go with spaces
Select LTRIM (RTRIM ("3018"))
4. Date and Time functions
/* Date and time function */
SELECTcurdate()/* Returns the current date */
SELECTCurtime()/* return current time */
SELECT Now()/* Returns the current date and time */
SELECTMONTH("2011-11-2")
SELECT Year(Now ())
/* Returns the English name of the month in the date */
SELECT MONTHNAME (now ())
/* Return day of the week in English * *
SELECTDayname(Now ())
/* Return the day of the week Sunday start */
SELECTDAYOFWEEK(Now ())
/* Date is the first few weeks of the year */
SELECT WEEK (now ())
/* Return hours */
SELECT HOUR (now ())
/* Return minutes */
SELECT MINUTE (now ())
/* return seconds */
SELECT SECOND (now ())
/* Get employee information For more than 30 years of entry time * *
SELECT * from EMP WHERE (Year (now ())-year (hiredate)) >=30
/* Get age of 7369 */
SELECTDATEDIFF("1981-1-17", "1980-12-17");
SELECT DATEDIFF (now (), emp.hiredate)/365 from emp WHERE empno=7369
5. Conditional Functions
ifnull (x, y)
If the value of x is null, replace with the value of Y

if (expr,x,y)
Returns the result if the expression expr is true x otherwise returns y
Select IF (1>0, "correct", "error")
6. System Information Functions
IP Address Conversion/* Convert the given IP address to a number */

QQ is a remote login, it will record the address you often log in, if you change the computer (IP different) will prompt
SELECTInet_aton("127.0.0.1")
/* Turn the given number into an IP address */
SELECT Inet_ntoa (2130706433)
7. Cryptographic functions
MySQL functions for encrypting data
password (str)
The string str can be encrypted in general to encrypt the user's password
MD5 (str)
You can hash a string
Can be used for some data that does not need to be decrypted

Select PASSWORD ("123")//encryption
Select MD5 ("123")//decryption
8. Formatting functions
The Format function formats the data content in MySQL
SELECT FORMAT (100000,2);
SELECT FORMAT (100.31111,2);
SELECT FORMAT (100.31111,0);
SELECT FORMAT (432432234.65534453,2);
/* You can format the data as an integer or a floating-point number with rounding function */

Eight functions in the database (aggregation, number, string, date and time, condition, system information, encryption, formatting)