Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "what are the functions commonly used in mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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 minimum integer value greater than x

EXP (x) returns the x power of the value e (the base of the natural logarithm)

FLOOR (x) returns a maximum integer value less than x

GREATEST (x1 and 2) returns the largest value in the collection

LEAST (x1 and 2) returns the smallest value in the set

LN (x) returns the natural logarithm of x

LOG (xPowery) returns the y-based logarithm of x

MOD (xPowery) returns the module (remainder) of xCompy.

PI () returns the value of pi (pi)

RAND () returns a random value from 0 to 1, and you can cause the RAND () random number generator to generate a specified value by providing a parameter (seed).

ROUND (xQuery y) returns the value of the parameter x rounded with y decimal places

SIGN (x) returns the value of the symbol that represents the number x

SQRT (x) returns the square root of a number

TRUNCATE (xQuery y) returns the result of the number x truncated to y decimal places

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 composed of concatenations of column values that belong to a group

"list the results of the highest salary in each department", the sql statement is as follows:

SELECT DEPT, MAX (SALARY) AS MAXIMUM FROM STAFF GROUP BY DEPT

"query the total salary of each department."

SELECT DEPT, sum (SALARY) AS total FROM STAFF GROUP BY DEPT

For more information on usage, please see MySQL group by where having usage details

Third, string function

ASCII (char) returns the ASCII code value of the character

BIT_LENGTH (str) returns the bit length of the string

CONCAT (s1MageS2... reparationsn) concatenates s1Magins2.. Personsn into a string.

CONCAT_WS (sep,s1,s2...,sn) concatenates s1direction s2.. CONCAT_WS sn into strings and spaced them with sep characters.

INSERT (str,x,y,instr) starts the string str at position x, replaces the y-character-long substring with the string instr, and returns

Return the result

FIND_IN_SET (str,list) parses a comma-separated list of list and, if str is found, returns the location 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 x characters in the string str

LENGTH (s) returns the number of characters in the string str

LTRIM (str) cuts off the opening space from the string str

POSITION (substr,str) returns the position where the substring substr first appears in the string str

QUOTE (str) escapes single quotation marks in str with a backslash

REPEAT (str,srchstr,rplcstr) returns 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 in the string str

RTRIM (str) returns the space at the end of the string str

STRCMP (S1 and S2) compare strings S1 and S2

TRIM (str) removes all spaces at the beginning and end of a 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 int (int must follow the key

Words are formatted), such as SELECT

DATE_ADD (CURRENT_DATE,INTERVAL 6 MONTH)

DATE_FORMAT (date,fmt) formats the date value according to the specified fmt format

DATE_SUB (date,INTERVAL int keyword) returns the result of the date date plus interval int (int must follow the key

Words are formatted), such as SELECT

DATE_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 in which date is returned (1-31)

DAYOFYEAR (date) returns the day of the year in which date is returned (1x366)

DAYNAME (date) returns the week name of date, such as: SELECT DAYNAME (CURRENT_DATE)

FROM_UNIXTIME (ts,fmt) formats the UNIX timestamp ts according to the specified fmt format

HOUR (time) returns the hourly value of time (0,23)

MINUTE (time) returns the minute value of time (0,59)

MONTH (date) returns the month value of date (1-12)

MONTHNAME (date) returns the month name of the date, such as: SELECT MONTHNAME (CURRENT_DATE)

NOW () returns the current date and time

QUARTER (date) returns the quarter of the year (1x4) of date, such as SELECT QUARTER (CURRENT_DATE)

WEEK (date) returns the date date is the week ordinal of the year (0,53)

YEAR (date) returns the date date's year (100009999)

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 ())

'00muri% mMUE% d')

< DATE_FORMAT (birthday, '00-%m-%d')) AS age from employee 五、加密函数 AES_ENCRYPT(str,key)　 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用 AES_ENCRYPT的结果是一个二进制 字符串，以BLOB类型存储 AES_DECRYPT(str,key)　 返回用密钥key对字符串str利用高级加密标准算法解密后的结果 DECODE(str,key)　 使用key作为密钥解密加密字符串str ENCRYPT(str,salt)　 使用UNIX crypt()函数，用关键词salt(一个可以惟一确定口令的字符串， 就像钥匙一样)加密字符串str ENCODE(str,key)　 使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串 ，它以BLOB类型存储 MD5()　　 计算字符串str的MD5校验和 PASSWORD(str)　 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过 程使用不同的算法。 SHA()　　 计算字符串str的安全散列算法(SHA)校验和 示例： SELECT ENCRYPT('root','salt'); SELECT ENCODE('xufeng','key'); SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起 SELECT AES_ENCRYPT('root','key'); SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key'); SELECT MD5('123456'); SELECT SHA('123456'); 六、控制流函数 MySQL有4个函数是用来进行条件操作的，这些函数可以实现SQL的条件逻辑，允许开发者将一些应 用程序业务逻辑转换到数据库后台 。 MySQL控制流函数： CASE WHEN[test1] THEN [result1]...ELSE [default] END 如果testN是真，则返回resultN，否 则返回default CASE [test] WHEN[val1] THEN [result]...ELSE [default] END　 如果test和valN相等，则返 回resultN，否则返回default IF(test,t,f)　 如果test是真，返回t；否则返回 f IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2 NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1 这些函数的第一个是IFNULL()，它有两个参数，并且对第一个参数进行判断。如果第一个参数不 是NULL，函数就会向调用者返回第 一个参数；如果是NULL,将返回第二个参数。 如：SELECT IFNULL(1,2), IFNULL(NULL,10), IFNULL(4*NULL,'false'); NULLIF()函数将会检验提供的两个参数是否相等，如果相等，则返回NULL，如果不相等，就返回 第一个参数。 如：SELECT NULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,4+1); 和许多脚本语言提供的IF()函数一样，MySQL的IF()函数也可以建立一个简单的条件测试，这个函 数有三个参数，第一个是要被判断 的表达式，如果表达式为真，IF()将会返回第二个参数，如果为假，IF()将会返回第三个参数。 如：SELECT IF(1100,'true','false'); IF()函数在只有两种可能结果时才适合使用。然而，在现实世界中，我们可能发现在条件测试中 会需要多个分支。在这种情况下， MySQL提供了CASE函数，它和PHP及Perl语言的switch-case条件例程一样。 CASE函数的格式有些复杂，通常如下所示： CASE [expression to be evaluated] WHEN [val 1] THEN [result 1] WHEN [val 2] THEN [result 2] WHEN [val 3] THEN [result 3] ...... WHEN [val n] THEN [result n] ELSE [default result] END 这里，第一个参数是要被判断的值或表达式，接下来的是一系列的WHEN-THEN块，每一块的第一个 参数指定要比较的值，如果为真， 就返回结果。所有的 WHEN-THEN块将以ELSE块结束，当END结束了所有外部的CASE块时，如果前面 的每一个块都不匹配就会返回ELSE 块指定的默认结果。如果没有指定ELSE块，而且所有的WHEN-THEN比较都不是真，MySQL将会返回 NULL。 CASE函数还有另外一种句法，有时使用起来非常方便，如下： CASE WHEN [conditional test 1] THEN [result 1] WHEN [conditional test 2] THEN [result 2] ELSE [default result] END 这种条件下，返回的结果取决于相应的条件测试是否为真。 示例： mysql>

SELECT CASE 'green'

WHEN 'red' THEN' stop'

WHEN 'green' THEN' Go' END

SELECT CASE 9 WHEN 1 THEN 'a'WHEN 2 THEN 'b'ELSE 'N 'A'END

SELECT CASE WHEN (2 ~ 2) = 4 THEN 'OK' WHEN (2 ~ 2) $amp

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.