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

This article mainly explains "the specific use of if in mysql sentences". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn the specific usage of if in the mysql sentence.

Select *, if (sva=1, "male", "female") as ssva from taname where sva ""

12.2. Control flow function

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result...] [ELSE result] END

In the return result of the first scenario, value=compare-value. The return result of the second scheme is the real result of the first case. If there is no matching result value, the result is returned if the result is ELSE, and if there is no ELSE section, the return value is NULL.

MySQL > SELECT CASE 1 WHEN 1 THEN one

-> WHEN 2 THEN two ELSE more END

-> one

> SELECT CASE WHEN 1 > 0 THEN true ELSE false END

-> true

Mysql > SELECT CASE BINARY B

-> WHEN a THEN 1 WHEN b THEN 2 END

-> NULL

The default return type of an CASE expression is the compatible set type of any return value, depending on its context. If used in a string context, the resulting flavor string is returned. If used in a numeric context, the returned result is a decimal, real, or integer value.

IF (exPR1,expr2,expr3)

If expr1 is TRUE (expr1 0 and expr1 NULL), the return value of IF () is expr2;, otherwise the return value is expr3. The return value of IF () is either a numeric value or a string value, depending on its context.

Mysql > SELECT IF (1 > 2pm 2pm 3)

-> 3

Mysql > SELECT IF (1 yes

Mysql > SELECT IF (STRCMP (test,test1), no,yes)

-> no

If only one of the expr2 or expr3 is explicitly NULL, the result type of the IF () function is the result type of a non-NULL expression.

Expr1 is evaluated as an integer value, that is, if you are validating a floating-point or string value, you should use a comparison operation to verify it.

Mysql > SELECT IF (0. 1, 1, and 0)

-> 0

Mysql > SELECT IF (0.10, 1, 10, 0)

-> 1

In the first example shown, the return value of IF (0. 1) is 0 because 0. 1 is converted to an integer value, causing a test of IF (0). This may not be what you want. In the second example, the original floating-point value is tested by comparison to see if it is a non-zero value. The comparison results use integers.

The default return value type for IF (), which is important when it is stored in a temporary table, is calculated as follows:

Expression.

Return value

The expr2 or expr3 return value is a string.

String

The expr2 or expr3 return value is a floating point value.

Floating point

The expr2 or expr3 return value is an integer.

Integer number

If both expr2 and expr3 are strings, and either string is case-sensitive, the return result is case-sensitive.

IFNULL (expr1,expr2)

If expr1 is not NULL, the return value of IFNULL () is expr1; otherwise its return value is expr2. The return value of IFNULL () is a number or a string, depending on the context in which it is used.

Mysql > SELECT IFNULL (1J0)

-> 1

Mysql > SELECT IFNULL (NULL,10)

-> 10

Mysql > SELECT IFNULL (1gamma 0pr 10)

-> 10

Mysql > SELECT IFNULL (1bat 0m yes)

-> yes

The default result value of IFNULL (expr1,expr2) is the more "generic" of the two expressions, in the order STRING, REAL, or INTEGER. Suppose the case of an expression-based table, or that MySQL must store the return value of IFNULL () in a temporary table in memory:

CREATE TABLE tmp SELECT IFNULL (1m test) AS test

In this example, the type of the test column is CHAR (4).

NULLIF (expr1,expr2)

If expr1 = expr2 is established, the return value is NULL, otherwise the return value is expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

Mysql > SELECT NULLIF (1Pol 1)

-> NULL

Mysql > SELECT NULLIF (1Jue 2)

-> 1

Note that if the parameters are not equal, the value evaluated twice by MySQL is expr1.

At this point, I believe you have a deeper understanding of "the specific use of if in mysql sentences". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.