Examples of using if in MySQL statements

Source: Internet
Author: User

This article mainly describes the example of using if in MySQL statements and the specific operation steps, this document demonstrates how to use the if statement in MySQL. The following describes the specific solution. I hope it will be helpful in your future study.

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

Control Flow Functions

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 returned results of the first scheme, value = compare-value. The returned results of the second solution are the real results of the first case. If no matching result value exists, the result after ELSE is returned. If no ELSE part exists, the return value is NULL.

 
 
  1. MySQL (best combination with PHP)> select case 1 WHEN 1 THEN 'one'
  2. -> WHEN 2 THEN 'two' ELSE 'more' END;
  3. -> 'One'
  4. MySQL (best combination with PHP)> select case when 1> 0 THEN 'true' ELSE 'false' END;
  5. -> 'True'
  6. MySQL (best combination with PHP)> select case binary 'B'
  7. -> WHEN 'a 'then 1 WHEN 'B' THEN 2 END;
  8. -> NULL

The default Return Value Type of a CASE expression is the compatible set type of any return value, but the specific situation depends on the context. If it is used in a string context, a result flavor string is returned. If it is used in the numeric context, the returned result is a decimal value, a real value, or an integer.

 
 
  1. 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 a numeric or string value, depending on the context.

 
 
  1. MySQL (best combination with PHP)> select if (1> 2, 2, 3 );
  2. -> 3
  3. MySQL (best combination with PHP)> select if (1 <2, 'yes', 'no ');
  4. -> 'Yes'
  5. MySQL (best combination with PHP)> select if (STRCMP ('test', 'test1'), 'No', 'yes ');
  6. -> 'No'

IF only one expr2 or expr3 is NULL, The result type of the IF () function is not the result type of the NULL expression.

Expr1 is calculated as an integer. That is to say, if you are verifying a floating point or string value, you should use a comparison operation for testing.

 
 
  1. MySQL (the best combination with PHP)> select if (0.1 );
  2. -> 0
  3. MySQL (best combination with PHP)> select if (0.1 <> 0, 0 );
  4. -> 1

In the first example, IF (0.1) returns 0 because 0.1 is converted to an integer, resulting in a test of IF (0. This may not be what you want. In the second example, a comparison checks the original floating point value to see if it is a non-zero value. The comparison result uses an integer.

The example of if () in a MySQL statement is used (this is important when it is stored in a temporary table). The default return value type is calculated as follows:

Expression

Return Value

The return value of expr2 or expr3 is a string.

String

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

Floating Point

The return value of expr2 or expr3 is an integer.

Integer

If both expr2 and expr3 are strings, and any one of them is case sensitive, the returned result is case sensitive.

IFNULL (expr1, expr2)

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

MySQL (the best combination with PHP)> select ifnull (1, 0 );

-> 1

MySQL (the best combination with PHP)> select ifnull (NULL, 10 );

-> 10

MySQL (the best combination with PHP)> select ifnull (1/0, 10 );

-> 10

MySQL (the best combination with PHP)> select ifnull (1/0, 'yes ');

-> 'Yes'

The default result value of IFNULL (expr1, expr2) is one of the two expressions that is more "common" in the order of STRING, REAL, or INTEGER. Assume that an expression-based table or MySQL (the best combination with PHP) must store the returned value of IFNULL () in a temporary table in internal memory:

Create table tmp select ifnull (1, 'test') AS test;

Example of using if in MySQL statements,In this example, the test column type is CHAR (4 ).

NULLIF (expr1, expr2)

If expr1 = expr2 is true, 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 (best combination with PHP)> select nullif );

-> NULL

MySQL (the best combination with PHP)> select nullif (1, 2 );

-> 1

Note: If the parameters are not equal, the value obtained for MySQL (the best combination with PHP) is expr1.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.