Shulou(Shulou.com)06/01 Report--

1. Mathematical function

ABS (x) returns the absolute value of x BIN (x) returns the binary of x (OCT returns octal HEX returns hexadecimal) CEILING (x) returns the smallest integer value greater than x EXP (x) returns the x-th power of the value e (the base of the natural logarithm) FLOOR (x) returns the maximum integer value GREATEST less than x (x1J x2m.) returns the largest value in the set LEAST (x1m x2m...) returns the smallest value in the set LN (x) returns the natural logarithm LOG (x) of x. Y) returns the logarithm of x based on y (x MOD) returns the module (remainder) of x PI () returns the value of pi (pi) RAND () returns a random value within 0 to 1, and you can cause the RAND () random number generator to generate a specified value by providing a parameter (seed). ROUND (x) returns the rounded value of parameter x with y decimal places SIGN (x) returns the value of the symbol representing the number x SQRT (x) returns the square root of a number TRUNCATE (x) returns the result that the number x is truncated to a y decimal place

Aggregate function (commonly used in SELECT queries of GROUP BY clauses)

AVG (col) returns the average of the specified column COUNT (col) returns the number of non-null values in the specified column MIN (col) returns the minimum value of the specified column MAX (col) returns the maximum value of the specified column SUM (col) returns the sum of all the values of the specified column GROUP_CONCAT (col) returns the result formed by the combination of column values belonging to a group

Third, string function

ASCII (char) returns the ASCII code value of the character BIT_LENGTH (str) returns the bit length of the string CONCAT (s1scores2. Reverend sn) concatenates s1elivers2.. reparatesn into a string CONCAT_WS (sep,s1,s2...,sn) concatenates s1heros2.. Lines sn into a string, and uses the sep character interval INSERT (str,x,y,instr) to replace the string str from position x, and the substring of y characters to the string instr. Returns the result FIND_IN_SET (str,list) analyzes a comma-separated list list, if str is found Returns the position of str in list LCASE (str) or LOWER (str) returns the result of changing all characters in the string str to lowercase LEFT (str,x) returns the leftmost character LENGTH (s) in the string str returns the number of characters in the string str LTRIM (str) cuts off the opening space POSITION (substr) from the string str Str) returns the position where the substring substr first appears in the string str QUOTE (str) escapes the single quotation mark REPEAT (str,srchstr,rplcstr) in str with a backslash to return the result of the string str repeated x times REVERSE (str) returns the result of reversing the string str RIGHT (str,x) returns the rightmost x characters RTRIM (str) in the string str returns the space STRCMP (S1) at the end of the string str S2) compare the string S1 and s2TRIM (str) to remove all spaces at the beginning and end of the string UCASE (str) or UPPER (str) returns the result of converting all characters in the string str to uppercase

IV. Date and time functions

CURDATE () or CURRENT_DATE () returns the current date CURTIME () or CURRENT_TIME () returns the current time DATE_ADD (date,INTERVAL int keyword) returns the result of the date date plus interval time int (int must be formatted according to keywords), such as SELECTDATE_ADD (CURRENT_DATE,INTERVAL 6 MONTH) DATE_FORMAT (date,fmt) formats the date value date _ SUB (date,INTERVAL int keyword) according to the specified fmt format. Returns the result of date plus interval int (int must be formatted according to keywords), such as: SELECTDATE_SUB (CURRENT_DATE,INTERVAL 6 MONTH) DAYOFWEEK (date) returns the day of the week represented by date (1-7) DAYOFMONTH (date) returns the day of the month (1-31) DAYOFYEAR (date) returns the day of the year (1-366) DAYNAME (date) returns the name of the week of date, such as: SELECT DAYNAME (CURRENT_DATE) FROM_UNIXTIME (ts,fmt) according to the specified fmt format, the UNIX timestamp tsHOUR (time) returns the hour value of time (0,23) MINUTE (time) returns the minute value of time (0,59) MONTH (date) returns the month value of date (1x12) MONTHNAME (date) returns the month name of date, such as SELECT MONTHNAME (CURRENT_DATE) NOW () returns the current date and time QUARTER (date) returns the quarter of the year (1-4) of the date, such as SELECT QUARTER (CURRENT_DATE); WEEK (date) returns the date date is the week ordinal (0,53) YEAR (date) returns the year of the date date (1000-9999)

Some examples:

Get the current system time:

SELECT FROM_UNIXTIME (UNIX_TIMESTAMP ()); SELECT EXTRACT (YEAR_MONTH FROM CURRENT_DATE); SELECT EXTRACT (DAY_SECOND FROM CURRENT_DATE); SELECT EXTRACT (HOUR_MINUTE FROM CURRENT_DATE)

Returns the difference between two date values (months):

SELECT PERIOD_DIFF (200302, 199802)

Calculate the age in Mysql:

SELECT DATE_FORMAT (FROM_DAYS (TO_DAYS (NOW ())-TO_DAYS (birthday)),'% Y') + 0 AS age FROM employee

In this way, if Brithday is the future year, month and day, the calculated result is 0.

The following SQL statement calculates the absolute age of the employee, that is, when Birthday is a future date, it will get a negative value.

SELECT DATE_FORMAT (NOW (),'% Y')-DATE_FORMAT (birthday,'% Y')-(DATE_FORMAT (NOW (), '00Murray% mmurf% d') SELECT CASE' green' WHEN 'red' THEN' stop' WHEN 'green' THEN' go' END;SELECT CASE 9 WHEN 1 THEN 'WHEN 2 THEN' b'ELSE 'NZA 'END;SELECT CASE WHEN (2' 2) = 4 THEN 'OK' WHEN (2' 2) 4 THEN 'not OK' END ASSTATUS SELECT Name,IF ((IsActive = 1), 'activated', 'inactivated') AS RESULT FROMUserLoginInfo;SELECT fname,lname, (math+sci+lit) AS total,CASE WHEN (math+sci+lit) < 50 THEN 'D'WHEN (math+sci+lit) BETWEEN 50 AND 150 THEN' C'WHEN (math+sci+lit) BETWEEN 151AND 250 THEN 'B'ELSE' A 'ENDAS grade FROM marks;SELECT IF (ENCRYPT (' sue','ts') = upass,'allow','deny') AS LoginResultFROM users WHERE uname = 'sue';# a login verification

Formatting function

DATE_FORMAT (date,fmt) formats the date value FORMAT (XMague y) according to the string fmt to format x into a sequence of numbers separated by commas, y is the number of decimal places of the result INET_ATON (ip) returns the number of IP address represents INET_NTOA (num) returns the IP address represented by the number TIME_FORMAT (time,fmt) formats the time value according to the string fmt

The simplest of these is the FORMAT () function, which formats large numbers into easy-to-read sequences separated by commas.

Example:

SELECT FORMAT (34234.34323432); SELECT DATE_FORMAT (NOW (),'% WMague% D% M% Y% r'); SELECT DATE_FORMAT (NOW (),'% Ymuri% mmi% d'); SELECT DATE_FORMAT (19990330); SELECT DATE_FORMAT (NOW (),'% hpurl% I% p'); SELECT INET_ATON ('10.122.89.47'); SELECT INET_NTOA (175790383)

Type conversion function

For data type conversion, MySQL provides the CAST () function, which converts a value to a specified data type. The types are: BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED

Example:

SELECT CAST (NOW () AS SIGNED INTEGER), CURDATE () + 0tSELECT 'f'=BINARY' Fractogramma frenzy cast ('F'AS BINARY)

IX. System information function

DATABASE () returns the current database name BENCHMARK (count,expr) returns the expression expr run count times CONNECTION_ID () returns the connection of the current customer IDFOUND_ROWS () returns the total number of rows retrieved by the last SELECT query USER () or SYSTEM_USER () returns the current login user name VERSION () returns the version of the MySQL server

Example:

SELECT DATABASE (), VERSION (), USER (); SELECTBENCHMARK (99999999) log (RAND () * PI ()); # in this case, MySQL evaluates the LOG (RAND () * PI ()) expression 9999999 times.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.