MySQL has 4 functions for conditional operation, which can implement the conditional logic of SQL, allowing the developer to transform some application business logic into the database background.
MySQL Control flow function:Case
When[test1] then [RESULT1] ... else [default] END If TESTN is true, returns RESULTN, otherwise returns the default Case [Test] when[val1) Then [result] ... else [Default]end returns RESULTN if test and valn are equal, otherwise returns the defaultif
(test,t,f) if test is true, returns T;
ifnull(ARG1,ARG2) If arg1 is not empty, return arg1, otherwise return arg2
nullif(ARG1,ARG2) If ARG1=ARG2 returns NULL, otherwise returns ARG1 The first of these functions is ifnull (), which has two parameters and is judged on the first parameter. If the first argument is not NULL, the function returns the first argument to the caller, and if it is null, the second argument is returned. such as: SELECT Ifnull, Ifnull (null,10), ifnull (4*null, ' false ');The nullif () function verifies that the supplied two parameters are equal, returns null if they are equal, and returns the first argument if not equal. such as: SELECT Nullif, Nullif (' A ', ' B '), Nullif (2+3,4+1);like the IF () function provided by many scripting languages, MySQL's if () function can also establish a simple conditional test, which has three parameters, the first is the expression to be judged, if the expression is true, if () will return the second argument, if False, if () A third parameter is returned. such as: Selectif (1<10,2,3), if (56>100, ' true ', ' false ');the IF () function is suitable for use only if there are only two possible outcomes. In the real world, however, we may find that multiple branches are needed in a conditional test. In this case, MySQL provides the cases function, which is the same as the PHP and Perl language switch-case conditional routines. the format of the case function is somewhat complex and usually resembles the following:Copy the code code as follows:
Case [expression to being 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]
ENDhere, the first parameter is the value or expression to be judged, followed by a series of when-then blocks, the first parameter of each block specifies the value to compare, and if true, returns the result. All When-then blocks will end with the else block, and when end ends all external case blocks, if each of the preceding blocks does not match, the default result specified by the else block is returned. If you do not specify an else block and all when-then comparisons are not true, MySQL will return null. The case function has another syntax, which is sometimes very convenient to use, as follows: CaseCopy the code code as follows:
When [conditional test 1] Then [result 1]
When [conditional test 2] Then [result 2]
ELSE [Default result]
END In this condition,
the result of the return depends on whether the appropriate condition test is true. Example:Copy the code code as follows:
mysql>select case ' green '
When the ' red ' then ' Stop '
When the ' green ' Then ' Go ' END;SELECT Case 9 If 1 then ' a ' is 2 Then ' B ' ELSE ' n/a ' END;SELECT case When (=4) "OK" when (+/-) <>4 then ' not OK ' END asstatus;SELECT name,if ((IsActive = 1), ' activated ', ' inactive ') as RESULT fromuserlogininfo;SELECT Fname,lname, (math+sci+lit) as Total,Case when (Math+sci+lit) < ' D 'When (Math+sci+lit) between and "C"When (Math+sci+lit) between 151 and "B"ELSE ' A ' ENDAs grade from marks;SELECT IF (ENCRYPT (' Sue ', ' ts ') =upass, ' Allow ', ' deny ') as Loginresultfrom users WHERE uname = ' Sue '; #一个登陆验证vii. Formatting Functions
date_format (DATE,FMT) formatting date dates values according to the string FMTformat (x, y) formats x as a comma-separated sequence of numbers, and Y is the number of decimal digits of the resultInet_aton (IP) Returns the number representation of an IP addressinet_ntoa (num) returns the IP address represented by the numberTime_format (TIME,FMT) format time value according to the string FMTThe simplest of these is the format () function, which formats large numeric values into a readable sequence of comma intervals. Example:Copy the code code as follows:SELECT 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 ');SELECT Inet_aton (' 10.122.89.47 ');SELECT Inet_ntoa (175790383);A) when the case and then function
Syntax:
Case value is [compare-value] then result [when [compare-value] then result ...] [ELSE result] END
Case is [condition] then result [when[condition] then result ...] [ELSE result] END;
Function Usage Description: In the return result of the first scenario, value =compare-value. The return result of the second scenario is the real result of the first case. If there is no matching result value, the result is returned with the else result, and if there is no else part, the return value is NULL
b) IF function usage
Syntax: IF (EXPR1,EXPR2,EXPR3)
Function Usage Description: If EXPR1 is TRUE (expr1 <> 0 and Expr1 <> NULL), then the return value of if () is expr2; Otherwise the return value is EXPR3. The return value of IF () is a numeric value or a string value, depending on the context in which it is located
SELECT IF (1>2,2,3) c) ifnull function
Syntax: ifnull (EXPR1,EXPR2)
Function Usage Description: If EXPR1 is not NULL, the return value of Ifnull () is EXPR1, otherwise its return value is EXPR2. The return value of Ifnull () is either a number or a string, depending on the context in which it is used
SELECT ifnull, Ifnull (null,10)
Control flow functions commonly used in MySQL
