The use of case in SQL

The case in the SQL statement is a bit like the multi-branch of a switch in a high-level programming language such as Java, but a little bit different is when the post is followed by when, in addition, when the subsequent branches are a bit like if followed by else. This is my personal acquaintance, case. When: Then.. [Else]..

Below, looking at MySQL's Help information, you can see the basic syntax structure of the case:

1 Topics: #CASE的两种基本用法2 Case OPERATOR3 Case STATEMENT4 5MariaDB [mysql]> Help Casestatement; #case的第一种用法6Name: ' Case STATEMENT '7 Description:8 Syntax:9 Case Case_valueTen When When_value then statement_list One [when When_value then Statement_list] ... A [ELSE statement_list] - END Case -  the Or: -  -  Case - When search_condition then statement_list + [when Search_condition then Statement_list] ... - [ELSE statement_list] + END Case A  atMariaDB [mysql]> Help Caseoperator; #case的第二种基本用法 -Name: ' Case OPERATOR ' - Description: - Syntax: - Case value is [compare_value] then result [when [Compare_value] Then - result ...] [ELSE result] END in  - case is [condition] then result [when [condition] then result ...] to[ELSE result] END

Here, I also need to explain that the case can be used in conjunction with select,check,update and so on, equivalent to the addition of SQL operations conditional analysis, yes, SQL can be more powerful.

1. Mate Usage for Select

1 Examples: 2 mariadb> SELECT Case 1 if 1 then ' one '3--When     2 Then ' both     ' ELSE ' more ' END; 4         -One '5 mariadb> SELECT case when 1>0 then ' true ' ELSE ' false ' END; 6         ' True '7 mariadb> SELECT case BINARY ' B '8--When     ' a ' then 1 when ' B ' then 2
   
     END; 
    9         , NULL
   

1MariaDB [test]> SELECT *From Casetbl;2+----+------+---------+---------+--------+3| ID | Age | name | Country | Salary |4+----+------+---------+---------+--------+5|   1 | 33 | Shihuc |   China | 1000 |6|   2 | 34 | Water |   China | 2000 |7|   3 | 20 | Taikang |   America | 3000 |8|   4 | 50 | Amazon |   America | 5000 |9+----+------+---------+---------+--------+Ten4 rows in Set (0.00sec) One  AMariaDB [test]>SELECT -Case when salary <= "1" -When salary > Salary <= 4000 then ' 2 ' the-ELSE NULL END salary_class, -COUNT (*) from Casetbl --GROUP by -Case when salary <= "1" +When salary > Salary <= 4000 then ' 2 ' --ELSE NULL END; ++--------------+----------+ A| Salary_class | COUNT (*) | at+--------------+----------+ -|        NULL | 1 | -|        1 | 2 | -|        2 | 1 | -+--------------+----------+ -3 Rows in Set (0.00 sec)

2. Mate usage of check

For example, some companies have an age limit for male recruitment and must be less than 40 years old, and this age is automatically checked when data is inserted into the table.

1MariaDB [test]>CREATE TABLE Casechktbl (2Idintprimary Key auto_increment,3-AgeintNotNULL,4Name varchar (30),5Salaryint(5)Check (case-age > 1 Else 0 end));6Query OK, 0 rows affected (0.25sec)7 8MariaDB [test]>desc CASECHKTBL;9+--------+-------------+------+-----+---------+----------------+Ten| Field | Type | Null | Key | Default | Extra | One+--------+-------------+------+-----+---------+----------------+ A| ID |int(11) | NO | PRI | NULL | auto_increment | -| Age |int(11) |     NO | |                NULL | | -| name | varchar (30) |     YES | |                NULL | | the| Salary |int(5) |     YES | |                NULL | | -+--------+-------------+------+-----+---------+----------------+ -4 rows in Set (0.03sec) -  +MariaDB [test]> INSERT INTO CASECHKTBL (name, age, salary) VALUES ("Hailang", 36, 10); -Query OK, 1 row affected (0.04sec) +  AMariaDB [test]> SELECT *From Casechktbl; at+----+-----+---------+--------+ -| ID | Age | name | Salary | -+----+-----+---------+--------+ -|  1 | 36 |     Hailang | 10 | -+----+-----+---------+--------+ -1 row in Set (0.00 sec)

It is important to note that this test is done under MySQL, which may not be a problem because the check in MySQL is actually a device that does not matter (when used for constraints).

3. Update with Use

There are the following update conditions 1. Employees with a salary of over 5000 are reduced by 10%. 2. Salaries between 2000 and 4600 employees, increase in wages by 15% it is easy to consider the option to execute two UPDATE statements as follows--Condition 1 UPDATE Personnel SETSalary = salary * 0.9WHERESalary >= 5000;--Condition 2UPDATE Personnel SETSalary = salary * 1.15WHERE salary >= andSalary < 4600;

This example, in fact, is very easy to see the problem, because the execution of these two conditional statements is separate operation, so there will be jitter, that is, after the first condition is executed, his salary becomes the condition that meets the second condition, it will be modified again.

For example, the wages of passers-by IS 5000, after the first modification, it becomes 4500, it is clear that the level meets the second condition, that is, to increase 10%, then the final salary is 5175. He has made such a adjustment that the wages have risen.

Therefore, the case condition needs to be dealt with.

1MariaDB [test]> SELECT *From Casetbl;2+----+------+---------+---------+--------+3| ID | Age | name | Country | Salary |4+----+------+---------+---------+--------+5|   1 | 33 | Shihuc |   China | 1000 |6|   2 | 34 | Water |   China | 2000 |7|   3 | 20 | Taikang |   America | 3000 |8|   4 | 50 | Amazon |   America | 5000 |9|   5 | 37 | Hailang |   Germany | 5000 |Ten+----+------+---------+---------+--------+ One5 rows in Set (0.00sec) A  -  -MariaDB [test]> Update casetbl Set salary = the- CaseWhen salary >= and salary * 0.9 --When salary >=2000 and salary <4600 then salary * 1.15 --Elsesalary end; -Query OK, 4 rows affected (0.05sec) +Rows Matched:5 Changed:4 warnings:0 -  +MariaDB [test]> AMariaDB [test]> SELECT *From Casetbl; at+----+------+---------+---------+--------+ -| ID | Age | name | Country | Salary | -+----+------+---------+---------+--------+ -|   1 | 33 | Shihuc |   China | 1000 | -|   2 | 34 | Water |   China | 2300 | -|   3 | 20 | Taikang |   America | 3450 | in|   4 | 50 | Amazon |   America | 4500 | -|   5 | 37 | Hailang |   Germany | 4500 | to+----+------+---------+---------+--------+ +5 rows in Set (0.00 sec)

From the above results, it is not easy to see that the results are logical.

The use of case in SQL