Suppose a data table has a status field designed to be a varchar type with the following values: Null,pending,pending refund,refund,cancel.
We know the order that the query status is cancel, which can be written by the SQL statement: SELECT o.oid,o.moneyreceipt,o.moneyget,o.thecurrency,o.status from Qorder o WHERE O.status = ' Cancel '
SQL statements can query for the correct data, but when we want to query for orders with a status other than cancel, it can be troublesome because the status field does not set not NULL, so the status value for most orders is null, so use ' <> ' Query out of the data is incorrect, only the status in addition to cancel the Non-null data query out, and null query out. SELECT o.oid,o.moneyreceipt,o.moneyget,o.thecurrency,o.status from Qorder o WHERE o.status <> ' Cancel ' reason: null value operation
A null value can be surprising until you get used to it. Conceptually, null means "no value" or "Unknown value", and it is considered a distinct value. In order to test NULL, you cannot use arithmetic comparison operators such as =, < or!=. To illustrate it, try the following query:mysql> SELECT 1 = null, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
Obviously you can't get meaningful results from these comparisons. Instead, use the IS null and is not NULL operator:mysql> SELECT 1 is null, and 1 is not null;
+-----------+---------------+
| 1 is NULL | 1 is not NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
Please note that in MySQL, 0 or null means false, while other values mean true. The default truth value of a Boolean operation is 1. Based on the above null value operation results, the final use of this method is resolved: SELECT o.oid,o.moneyreceipt,o.moneyget,o.thecurrency,o.status from Qorder o WHERE ifnull ( O.status, ' pending ') <> ' Cancel ' study: Ifnull (EXPR1,EXPR2)
If EXPR1 is not Null,ifnull () returns EXPR1, it returns EXPR2. Ifnull () returns a numeric or string value, depending on the context in which it is used.
Copy Code code as follows:
Mysql> Select Ifnull (1,0); ->1
Mysql> Select Ifnull (0,10); ->0
Mysql> Select Ifnull (1/0,10); ->10.0000
Mysql> Select Ifnull (1/0, ' yes '); -> ' yes ' if (EXPR1,EXPR2,EXPR3) if Expr1 is true (expr1<>0 and Expr1<>null), then if () returns EXPR2, otherwise it returns EXPR3. IF () returns a number or string value, depending on the context in which it is used.
Mysql> Select IF (1>2,2,3); -> 3
Mysql> Select IF (1<2, ' yes ', ' no '); -> ' yes '
(1) Case value when [compare-value] THEN result [when [compare-value] THEN result ...] [ELSE result] End//This I have not thought how to use, know can message exchange,
(2) When [condition] THEN result [when [condition] THEN result ...] [ELSE result] End//This interview was asked, can be used as a condition to judge
The first (1) Returns the result, in which value=compare-value. subclause (2) If the first condition is true, return result. If there is no matching result value, then results after else are returned. If there is no else part, then NULL is returned.
Copy Code code as follows:
mysql> SELECT Case 1 if 1 THEN "one" when 2 THEN "two" ELSE "more" end; -> "One"
mysql> SELECT Case If 1>0 THEN "true" ELSE "false" end; -> "true"
mysql> SELECT case BINARY ' B ' when ' a ' then 1 when ' B ' then 2 end; -> NULL
The above example I tested on the mysql5.2.6 by =====================================================================================ps : The above is reproduced content, feel very good turned over, which case ... When ... THEN, IF, ifnull are commonly used functions eg:select id,username,case Gender when 1 THEN "Men" when 2 THEN "female" end from T_people; SELECT b.id,b.book_name,case B.type when 1 THEN "Computer" when 2 THEN "accountant" Else "other" end of book B;
