The article directories

  • Step 4: Flow control functions of common functions
  • 5. Process control function
    • 1. If function: if else effect
  • 2. Case function use a: switch case effect
      • Case 1: Query employee’s salary and request
  • 3. Use of case function 2: similar to multiple if
      • Case 1: Query the salary of an employee

Step 4: Flow control functions of common functions

  • The following figure database as an example to write a case

5. Process control function

1. If function: if else effect

SELECT IF(10<5.'big'.'small');
Copy the code

SELECT last_name,commission_pct,IF(commission_pct IS NULL.'No bonus, hehe'.'Bonus, hee hee') noteFROM employees;
Copy the code

2. Case function use a: switch case effect

Switch (variable or expression){caseconstant1Statements:1; break; .defaultN: statements; break; }Copy the code
In the mysqlcaseThe field or expression to judgewhenconstant1 thenThe value to display1Or statements1;
whenconstant2 thenThe value to display2Or statements2; .elseThe value n or statement n to display;end
Copy the code

Case 1: Query employee’s salary and request

If the department number is 30, the salary displayed is 1.1 times. If the department number is 40, the salary displayed is 1.2 times. If the department number is 50, the salary displayed is 1.3 times

SELECTSalary original salary,department_id,CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END ASThe new salaryFROM employees;
Copy the code

3. Use of case function 2: similar to multiple if

In Java: if(condition1) {statement1; }elseIf (condition2) {statement2; }...else{statements n; }Copy the code
Mysql:case 
whenconditions1 thenThe value to display1Or statements1
whenconditions2 thenThe value to display2Or statements2.elseThe value n or statement n to displayend
Copy the code

Case 1: Query the salary of an employee

If salary >20000, level A is displayed; if salary >15000, level B is displayed; if salary >10000, level C is displayed; otherwise, level D is displayed

SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END ASWage levelFROM employees;
Copy the code