MySQL > select * from ‘MySQL’;

Mathematical function

• ABS(x) : Returns the absolute value of x

• BIN(x) : Returns the binary of x

• PI() : Returns the value of PI, which displays six decimal digits by default

• SQRT(x) : Returns a non-negative quadratic root of x

• MOD(x,y) : Returns the remainder of x divided by y

• CEIL(x), CEILING(x) : returns the smallest integer not less than x

• FLOOR(x) : Returns the largest integer not greater than x

• GREATEST(x1,x2,… ,xn) : Returns the largest value in the collection

• LEAST(x1,x2,… ,xn) : Returns the smallest value in the set

• ROUND(x) and ROUND(x,y) : the former returns the integer closest to x, that is, x is rounded. The latter returns the number closest to x, reserved to the y place behind the decimal point, or to the y place to the left of the decimal point if y is negative

• SIGN(x) : Returns the symbol of the argument x, -1 for negative and 0 for 0, 1 for positive

• POW(x,y), POWER(x,y) : returns the value of the y-power of x

• EXP(x) : returns the value of e to the x power

• LN(x) : Returns the natural log of x

• LOG(x,y) : Returns the base y LOG of x

• LOG10(x) : Returns the logarithm of base 10 of x

• RADIANS(x) : Returns the value of x converted from Angle to radian

• DEGREES(x) : Returns the value of x from radians to DEGREES

• Sine (x), ASIN(x) : the former returns the sine of x, where x is the given radian value; The latter returns the arcsine of x, which is sine

• COS(x), ACOS(x) : the former returns the cosine of x, where x is the given radian value; The latter returns the inverse cosine of x, which is cosine

• TAN(x), ATAN(x) : the former returns the tangent of x, where x is the given radian value; The latter returns the arctangent of x, which is the tangent

• COT(x) : Returns the cotangent of the given radian value x

• RAND() : Returns a random value between 0 and 1. You can cause the RAND() random number generator to generate a specified value by providing a parameter (seed)

• TRUNCATE(x,y) : Returns the truncation of the number x to y decimal places

• CONV(N,from_base,to_base) : Conversion between different base numbers, returning a string representation of the value N,from from_base to to_base

Aggregation function

• AVG(COL) : Returns the average value of the specified column

• COUNT(col) : Returns the number of non-null values in the specified column

• MIN(col) : Returns the minimum value for the specified column

• MAX(col) : Returns the maximum value of the specified column

• SUM(col) : Returns the SUM of all values of the specified column

• GROUP_CONCAT(COL) : Returns the result of concatenation of column values belonging to a group

String function

• STR_TO_DATE(STR, FMT) : indicates the conversion time from a string. The FMT date format is shown below

• LENGTH(STR) : calculates the number of characters in a string

• CONCAT (s1, s2,…). : Returns the string produced by the concatenation argument, one or more of the contents to be concatenated, NULL if any of them is NULL

• CONCAT_WS(x,s1,s2,…) : Returns a concatenated string with an x between each string

• INSERT(s1,x,len,s2) : returns the string s1, whose substring starts at position X and is replaced by len characters of the string s2

• LOWER(STR) and LCASE(STR), UPPER(STR) and UCASE(STR) : The first two convert all letters in STR to lowercase, and the second two convert all letters in the string to uppercase

• LEFT(s,n) and RIGHT(s,n) : The former returns n characters starting from the LEFT of string S, and the latter returns n characters starting from the RIGHT of string s

• LPAD(s1,len,s2), RPAD(s1,len,s2) : the former returns s1, and the left side of the string is filled with the length of len characters. If s1 is larger than len, the return value is shortened to len characters. The former returns s1, whose right-hand side is padded up to len characters by the string s2. If s1 is larger than len, the return value is shortened to len characters

• LTRIM(s), RTRIM(s) : the former returns the string S with all Spaces removed; The latter returns the string S with all Spaces to its right removed

• TRIM(s) : Returns string s with whitespace removed

• TRIM(s1 FROM s) : Deletes all substrings S1 at both ends of string S. If s1 is not specified, Spaces are deleted by default

• REPEAT(s,n) : Returns a string consisting of repeated strings s whose number is equal to n

• SPACE(n) : Returns a string of n Spaces

• REPLACE(s,s1,s2) : Returns a string that replaces all strings s1 in string s with the string s2

• STRCMP(s1,s2) : returns 0 if all strings in s1 and s2 are the same. Based on the current sorting order, -1 is returned if the first parameter is less than the second, and 1 is returned otherwise

• SUBSTRING(s,n,len), MID(s,n,len) : The two functions have the same function, return a string of len length starting with the NTH character from string s

• LOCATE(str1, STR), POSITION(str1 INSTR), and INSTR(str1, str1) : The three functions have the same function. They return the starting POSITION of the substring str1 IN the string STR.

• REVERSE(s) : Reverses the string s

• ELT(N,str1,str2,str3,str4,…) : Returns the NTH string

• CONVERT(STR USING charset) : use charset to represent string STR

Date time function

• CURDATE(), CURRENT_DATE() : Returns the current date in either “YYYY-MM-DD” or “YYYYMMDD” format, depending on the string or number context in which the function is used

• CURRENT_TIMESTAMP(), LOCALTIME(), NOW(), SYSDATE() : These four functions have the same function. They return the current date and time in the format of “YYYY_MM-DD HH:MM:SS” or “YYYYMMDDHHMMSS”, depending on the string or number context in which the function is used

• UNIX_TIMESTAMP() and UNIX_TIMESTAMP(date) : the former returns the number of seconds from 1970-01-01 00:00:00 to the present, while the latter returns the number of seconds from 1970-01-01 00:00:00 to the specified time

• FROM_UNIXTIME(date) : The inverse function of UNIX_TIMESTAMP converts the UNIX timestamp to a normal format

• UTC_DATE() and UTC_TIME() : the former returns the current UTC date in the format of “YYYY-MM-DD” or “YYYYMMDD”, the latter returns the current UTC time in the format of “YYYY-MM-DD” or “YYYYMMDD”. Depending on whether the function is used in a string or a number context

• MONTH(date), MONTHNAME(date) : The former returns the MONTH in the specified date, and the latter returns the name of the MONTH in the specified date

• DAYNAME(d), DAYOFWEEK(d), WEEKDAY(d) : DAYNAME(d) Returns the English name of the working day corresponding to D, such as Sunday and Monday. DAYOFWEEK(d) Indicates the index of a week. 1 indicates Sunday and 2 indicates Monday. WEEKDAY(d) indicates the index of working days corresponding to D. 0 indicates Monday and 1 indicates Tuesday

• WEEK(d) and WEEKOFYEAD(d) : the former calculates the WEEK in a year. The latter calculates the WEEK in a year

• DAYOFYEAR(d) and DAYOFMONTH(d) : the former returns D as the day of the year, while the latter returns D as the day of the month

• YEAR(date), QUARTER(date), MINUTE(time), SECOND(time) : YEAR(date) Returns the YEAR corresponding to the specified date. The range is 1970 2069. QUARTER(date) Returns date corresponding to the QUARTER of a year. The range is 1 and 4. MINUTE(time) Returns the number of minutes corresponding to time. The value ranges from 0 to 59. SECOND(time) Returns the SECOND value of the specified time

• EXTRACE(type FROM date) : Extracts part of the date. Type can be YEAR, YEAR_MONTH, DAY_HOUR, DAY_MICROSECOND, DAY_MINUTE, or DAY_SECOND

• TIME_TO_SEC(time) : Returns the time parameter converted to seconds as “3600* hours + 60* minutes + seconds “.

• SEC_TO_TIME() : And TIME_TO_SEC(time) are inverse functions of each other, converting the second value to the time format

• DATE_ADD(date,INTERVAL expr type), ADD_DATE(date,INTERVAL expr type) : DATE_ADD(‘2010-12-31 23:59:59’, INTERVAL 1 SECOND)

• DATE_SUB(date,INTERVAL expr type), SUBDATE(date,INTERVAL expr type) : Returns the time after subtracting expr type from the start time

• ADDTIME(date,expr) and SUBTIME(date,expr) : the former adds the date and the latter subtracts the date

Formatting function

• DATE_FORMAT(date, FMT) : Formats the date value according to the string FMT

• FORMAT(x,y) : FORMAT the number x with y decimal places rounded off. The result is returned as a string

• INET_ATON(IP) : Returns the numeric representation of the IP address

• INET_NTOA(num) : Returns the IP address represented by a number

• TIME_FORMAT(time, FMT) : Formats the time value according to the string FMT

  Simple SELECT the FORMAT (34234.34323432, 3); SELECT DATE_FORMAT(NOW(),’%W,%D %M %Y %r’); SELECT DATE_FORMAT(NOW(),’%Y-%m-%d’); SELECT DATE_FORMAT(19990330,’%Y-%m-%d’); SELECT DATE_FORMAT(NOW(),’%h:%i %p’); The SELECT INET_ATON (‘ 10.122.89.47 ‘); SELECT INET_NTOA(175790383);

  Date Format %M Month name (January…… December) %W Sunday… Saturday) %D Dates of months with English prefixes (1st, 2nd, 3rd, etc.) %Y year, number, 4 digits %Y year, number, 2 digits %a abbreviated name of the week (Sun… Sat) %d number of days in month (00…… 31) %e Number of days in a month (0…… 31) %m month, number (01…… 12) % C month, number (1…… 12) %b Abbreviated month names (Jan… Dec) %j Number of days in a year (001…… 366) %H hours (00…… 23) %k hours (0…… 23) %h hours (01…… 12) %I hours (01…… 12) % L hours (1…… 12) % I minutes, numbers (00…… 59) % r time, 12 hours (hh: mm: ss (AP) M) % T time, 24 hours (hh: mm: ss) % S seconds (00… 59) % s seconds (00… 59) %p AM or PM %w Number of days in a week (0=Sunday… 6= 0 %U 52) here Sunday is the first day of the week. Monday is the first day of the week here

Flow control function

• IF(expr,v1,v2) : return v1 IF expr is TRUE, otherwise return v2

• IFNULL(v1,v2) : if v1 is not NULL, return v1, otherwise return v2

• NULLIF(v1,v2) : return NULL if v1=v2, otherwise return v1

• CASE WHEN[test1] THEN [result1]… ELSE [default] END: If testN is true, return resultN, otherwise return default

• CASE expr WHEN v1 THEN R1 [WHEN v2 THEN v2] [ELSE RN] END: if vn = v1 THEN r1 [WHEN v2 THEN v2] [ELSE RN] END: if vn = v1 THEN R1 [WHEN v2 THEN V2

Sorting function

• RANK() : RANK by jump. RANK is the same value, the same value retains the repeat RANK, and when the next different value is met, the jump to the total RANK

• DENSE_RANK is a sequential ranking of the same value. The same value is retained in the same ranking. When the next different value is encountered, the sequential number is still ranked

• ROW_NUMBER Consecutively ranks users by consecutive numbers even if they have the same value

  Select rank() OVER(order by E.salary desc) as row_num, e.salary from employee E; select dense_rank() OVER(order by e.salary desc) as row_num, e.salary from employee e; select row_number() OVER(order by e.salary desc) as row_num, e.salary from employee e;

Encryption function

• PASSWORD(STR) : Computes and returns the encrypted string PASSWORD from the original plaintext PASSWORD STR. Note that the encryption of this function is one-way (irreversible), so it should not be used in personal applications but only in the authentication system of the MySQL server

• MD5(STR) : Computes an MD5 128-bit checksum for a string. The value is returned as a binary string of 32-bit hexadecimal digits

• SHA(STR) : Evaluates the safe hash algorithm (SHA) checksum of the string STR

• ENCODE(STR, pswd_str) : encrypt STR using pswd_str as password

• DECODE(crypt_str,pswd_str) : Decrypts the encrypted string crypt_str, which is returned by the ENCODE function, using pswd_str as the password

• ENCRYPT(STR,salt) : Use the UNIXcrypt() function to ENCRYPT the string STR with the keyword salt(a string that can uniquely determine the password, like a key)

System information function

• VERSION() : View the MySQL VERSION number

• CONNECTION_ID() : Displays the number of connections of the current user

• USER(), CURRENT_USER(), SYSTEM_USER(), SESSION_USER() : Views the combination of USER names and hosts currently authenticated by the MySQL server. Generally, these functions return the same value

• CHARSET(STR) : Displays the character set used by the string STR

• COLLATION() : Displays the arrangement of strings

• DATABASE() : Returns the current DATABASE name

• BENCHMARK(count,expr) : Runs the expression expr count times. It is used to measure how fast MySQL processes expressions. The result is usually 0 (0 means fast, not no speed). Another use is to report the execution time of statements inside the MySQL client

Other functions

• INET_ATON(expR) : Gives a point address representation of a network address as a string. Returns an integer representing the value of the address. The address can be 4 or 8 bits
• INET_NTOA(expR) : Given a digital network address (4 or 8 bits), returns the point-address representation of that address as a string