Some uses of the IF for Select Case

Overview:
The case statement in the SQL statement and the switch statement in the high-level language are standard SQL syntax, and are suitable for a condition that has multiple values to perform different operations, respectively.

First, let's look at the syntax of the case. In the general Select, the syntax is as follows:

Case < single-Value expressions >
When < expression value > then <sql statement or return value >
When < expression value > then <sql statement or return value >
...
When < expression value > then <sql statement or return value >
ELSE <sql statement or return value >
END

★ The first part

# Create a user table

CREATE TABLE ' user ' (
' id ' int (ten) unsigned not NULL auto_increment,
' Sex ' tinyint (1) Default 1 COMMENT ' sex: 0 female, 1 male, 2 confidential ',
' Age ' int (3) Default 1 COMMENT ' ages ',
' Province ' char (254) Default NULL COMMENT ' province ',
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT Charset=utf8

# Insert test data into the table

INSERT into User (sex,age,province) VALUES
(' 1 ', ' 22 ', ' Beijing '),
(' 0 ', ' 25 ', ' Guangdong '),
(' 0 ', ' 56 ', ' Tianjin '),
(' 1 ', ' 14 ', ' Beijing '),
(' 0 ', ' 36 ', ' Guangdong '),
(' 1 ', ' 68 ', ' Hunan '),
(' 1 ', ' 45 ', ' Beijing '),
(' 1 ', ' 17 ', ' Hebei '),
(' 2 ', ' 33 ', ' Tianjin '),
(' 1 ', ' 27 ', ' Hunan '),
(' 1 ', ' 29 ', ' Beijing '),
(' 2 ', ' 70 ', ' Guangdong '),
(' 0 ', ' 24 ', ' Beijing ')

Data sheets such as:

------------------------------------------------------------------------------------------

Experiment 1.1: Visually display the user's gender in text

1. Simple Case function notation (note the location of sex)

SELECT *, (Case sex if ' 1 ' then ' Male ' when ' 0 ' then ' women ' ELSE ' secret ' END) as Sex_text
From user

2.Case search function notation (note the location of the sex "recommended")

SELECT *, (case if sex= ' 1 ' then ' Male ' when sex= ' 0 ' Then ' women ' ELSE ' confidential ' END) as Sex_text
From user

Summary: The simple case function is only suitable for equal condition judgment and cannot be used for judgments that are greater than, less than, and not equal.
The case search function is suitable for complex condition judgments: it can be used for judgments that are greater than, less than, and not equal.
------------------------------------------------------------------------------------------

Experiment 1.2: Visually display the user's gender in text: 0 female, 1 male, 2 confidential, and sorted by gender

SELECT *, (case if sex= ' 1 ' then ' Male ' when sex= ' 0 ' Then ' women ' ELSE ' confidential ' END) as Sex_text
From user
ORDER BY Sex_text DESC

Summary: With the temporary generated fields can be sorted, MySQL query process: The table data query first, the data after the sorting display.
------------------------------------------------------------------------------------------

Experiment 1.3: The user's age is visually displayed (involving a range of values and can only be written using the "Case search function"), as follows:

SELECT *, (case if age>=60 then ' old ' when age<60 and age>=30 then ' middle age ' when age<30 and age>=18 then ' Youth ' E LSE ' underage ' END as Age_text
From user

------------------------------------------------------------------------------------------
Lab 1.4: Combining the above two experiments
SELECT *,
(Case if sex= ' 1 ' then ' Male ' when sex= ' 0 ' Then ' women ' ELSE ' secret ' END) as Sex_text,
(case is age>=60 then ' old ' when age<60 and age>=30 then ' middle age ' when age<30 and age>=18 then ' youth ' ELSE ' underage ' END) as Age_text
From user

------------------------------------------------------------------------------------------

Experiment 1.5: Grouping the regions to count the number of registrations in North and South China respectively
Select COUNT (*), (case province when ' Beijing ' then ' North China ' when ' Tianjin ' then ' North ' when ' Hebei ' then ' north ' when ' Guangdong ' then ' South China ' when ' Hunan ' T HEN ' South China ' END) as area
From user
Group BY area

Summary: Similarly, a temporary generated field area can be used for sorting or grouping after the query has ended.

==========================================================================================

★ Part II

# Create a data table

CREATE TABLE ' Dj_zt ' (
' id ' int (ten) unsigned not NULL auto_increment,
' ZT ' char (254) Default NULL,
' BS ' char (254) Default NULL,
' Qylx_dm ' char (254) Default NULL,
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT Charset=utf8

# Insert test Data

INSERT into Dj_zt (ZT, BS, QYLX_DM) VALUES
(' A ', ' a ', ' 01 '),
(' In ', ' B ', ' 02 '),
(' A ', ' C ', ' 03 '),
(' One ', ' d ', ' 03 '),
(' Up ', null, ' 04 '),
(' A ', ' f ', ' 03 '),
(' A ', ' g ', ' 02 '),
(' A ', ' h ', ' 03 '),
(' One ', ' I ', ' 03 '),
(' A ', ' j ', ' 03 '),
(' One ', ' k ', ' 04 ')

Experiment 2.1: Query the DJ_ZT table state value (ZT) for ' 07 ' or ' 11 ', when the condition is qylx_dm = ' 03 ' for all records.

A: Using Case statements
Select count (case a.zt "then A.bs end" + count (case a.zt when ' one ' then A.bs end ') as Num
From Dj_zt A
where a.qylx_dm = ' 03 '

B: No case statement
Select COUNT (*)
From Dj_zt A
where a.qylx_dm = ' a.zt ' in (' 07 ', ' 11 ')

Results: A, b two groups cost the same price, compared to the wording of B concise, draw.

------------------------------------------------------------------------------------------

Experiment 2.2: Query the DJ_ZT table with the status of ' 07 ' and ' 11 ' and qylx_dm = ' 03 ' For each record count.

A: Using Case statements
Select count (case a.zt "then A.bs end" as Num1,count (case a.zt when ' one ' then A.bs END ') as num2
From Dj_zt A
where a.qylx_dm = ' 03 '

B: No case statement (write two statements, scan the table two times, the efficiency is significantly lower)
Select COUNT (*)
From Dj_zt A
where a.qylx_dm = ' a.zt= ' and ' 07 '

Select COUNT (*)
From Dj_zt A
where a.qylx_dm = ' a.zt= ' and ' 11 '

Results: The cost of Group B was significantly higher than that of Group A, and the efficiency of the implementation was lower.

Summary: The difference between case and if:
• In high-level languages, case can be replaced with if, but not in SQL.
· The case is defined by the SQL standard, and if is an extension of the database system.
· Case can be used for SQL statements and SQL stored procedures, triggers, if only for stored procedures and triggers.
• In SQL procedures and triggers, using if instead of case costs are quite high, rather cumbersome and difficult to implement.

As can be seen from the above set of examples, the application of case statements can make SQL more concise and efficient, thus greatly improving the efficiency of execution. Also, case usage generally does not cause performance (compared to statements that do not use case), but increases the flexibility of the operation

★ The use of the third part if statement

? IF (EXPR1,EXPR2,EXPR3)

Rule: Returns EXPR2 if EXPR1 is true, otherwise returns EXPR3.

The return value of IF () is a numeric value or a string value, depending on the context in which it is located.

Experiment 3.1: The following:

Select *,if (sex= ' 1 ', ' Male ', ' non-male ') as Sex_text
From user

Summary: If not as the case can be multi-conditional judgment, if can only judge "true", "false";

Mysql> SELECT IF (1>2,2,3);
3

Mysql> SELECT IF (1<2, ' yes ', ' no ');
' 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 the non-NULL expression.
EXPR1 is calculated as an integer value, that is, if you are validating floating-point values or string values, you should use a comparison operation for validation.

Mysql> SELECT IF (0.1,1,0);
0

Mysql> SELECT IF (0.1<>0,1,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, resulting in a test of if (0). This may not be the case you want. In the second example, the comparison examines the original floating-point value to see if it is a value other than 0. The comparison results use integers.

------------------------------------------------------------------------------------------

? Ifnull (EXPR1,EXPR2)

Rule: If Expr1 is not NULL, it returns EXPR1, otherwise returns EXPR2.
The return value of Ifnull () is either a number or a string, depending on the context in which it is used.

Experiment 3.2: If the field BS is empty, return the value of the field ZT

Select *,ifnull (BS,ZT)
From Dj_zt
where ID in (5,6,12)

Lab 3.3: Return ' ZZX ' If the field BS is empty
Select *,ifnull (BS, ' ZZX ')
From Dj_zt
where ID in (5,6,12)

Mysql> SELECT ifnull (1,0);
1

Mysql> SELECT ifnull (null,10);
10

Mysql> SELECT ifnull (1/0,10);
10

Mysql> SELECT ifnull (1/0, ' yes ');
' Yes '

The default result value for Ifnull (EXPR1,EXPR2) is one of the more "common" in two expressions, in the order of string, real, or INTEGER. Suppose a case of an expression-based table, or MySQL must store the return value of Ifnull () in a temporary table in the internal memory:

------------------------------------------------------------------------------------------

? Nullif (EXPR1,EXPR2)

Rule: If Expr1 = EXPR2 is established, then the return value is NULL, otherwise the return value is EXPR1.
This is the same as when the case is Expr1 = Expr2 then NULL ELSE expr1 end.

Experiment 3.4: If the field ZT has an equal value to the field QYLX_DM, return null, otherwise return ZT

Select *,nullif (ZT,QYLX_DM)
From Dj_zt

Mysql> SELECT Nullif ();
, NULL

Mysql> SELECT Nullif;
1

Note that if the arguments are not equal, the MySQL two-time value is EXPR1

Some uses of the IF for Select Case