MariaDB – 流程控制函數 ( Control Flow Functions )

測試環境為 CentOS 8 x86_64 (虛擬機)

  1. 字串函數 ( String Functions ) 與 型別轉換http://benjr.tw/102953
  2. 數字函數 (Numeric Functions)http://benjr.tw/101970
  3. 日期與時間函數 (Date & Time Functions)http://benjr.tw/102964
  4. 資訊函數 (Information Functions)http://benjr.tw/102961
  5. 搭配 GROUP BY 函數 (Aggregate Functions)http://benjr.tw/102967
  6. 流程控制函數 (Control Flow Functions) – 這邊介紹.

在 SQL SELECT 時可以用 IF THEN ELSE (Flow Control Statements) 來做判斷嗎? 不行只能用下面幾種 Control Flow Functions 來完成.

MariaDB [(none)]> HELP Control flow functions;
You asked for help about help category: "Control flow functions"
For more information, type 'help <item>', where <item> is one of the following
topics:
   CASE OPERATOR
   IF FUNCTION
   IFNULL
   NULLIF
  1. CASE OPERATOR
  2. IF FUNCTION
  3. IFNULL
  4. NULLIF

先建立一個資料庫 (testdb) , 與 tables (employee) 格式為 Name VARCHAR(20), Dept VARCHAR(20), jobTitle VARCHAR(20) 各 20 個字元以及 數字欄位 Age INT(3) , Seniority INT(3) , Salary INT(11).

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE employee (Name VARCHAR(20) , Dept VARCHAR(20) , JobTitle VARCHAR(20) ,Age INT(3) , Seniority INT(3) , Salary INT(11));
Query OK, 0 rows affected (0.009 sec)

MariaDB [testdb]> DESCRIBE employee; 
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Name      | varchar(20) | YES  |     | NULL    |       |
| Dept      | varchar(20) | YES  |     | NULL    |       |
| JobTitle  | varchar(20) | YES  |     | NULL    |       |
| Age       | int(3)      | YES  |     | NULL    |       |
| Seniority | int(3)      | YES  |     | NULL    |       |
| Salary    | int(11)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.005 sec)

透過 INSERT 新增加了五筆資料.

MariaDB [testdb]> INSERT INTO employee VALUES ('Ben' , 'Testing' , 'Engineer' , '32' , '5' , '45000') , ('Afa' , 'Power' , 'Engineer' , '28' , '2' , '48000') , ('Boss' , 'Testing' , 'Manager' , '42' , '8' , '75000') , ('Justin' , 'Testing' , 'Senior Engineer' , ' 26' , '2' , '42000') , ('Jasmine' , 'Power' , 'Manager' , '41' , '9' ,'80000') ;
Query OK, 5 rows affected (0.002 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM employee;
+---------+---------+-----------------+------+-----------+--------+
| Name    | Dept    | JobTitle        | Age  | Seniority | Salary |
+---------+---------+-----------------+------+-----------+--------+
| Ben     | Testing | Engineer        |   32 |         5 |  45000 |
| Afa     | Power   | Engineer        |   28 |         2 |  48000 |
| Boss    | Testing | Manager         |   42 |         8 |  75000 |
| Justin  | Testing | Senior Engineer |   26 |         2 |  42000 |
| Jasmine | Power   | Manager         |   41 |         9 |  80000 |
+---------+---------+-----------------+------+-----------+--------+
5 rows in set (0.001 sec)
  • CASE – Case operator
    雖然在 SELECT 時不可以使用 IF THEN ELSE ( Flow Control Statements ) ,但是可以使用 CASE ,使用上跟在 Flow Control Statements – http://benjr.tw/101629 的 CASE 一樣.

    MariaDB [astl]> HELP CASE OPERATOR;
    Name: 'CASE OPERATOR'
    Description:
    Syntax:
    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
    
    The first version returns the result where value=compare_value. The
    second version returns the result for the first condition that is true.
    If there was no matching result value, the result after ELSE is
    returned, or NULL if there is no ELSE part.
    
    URL: https://mariadb.com/kb/en/case-operator/
    

    我們來算看看每一個人的薪水是否高於平均值,是則回傳 Above Average 否則回傳 Under Average .

    MariaDB [astl]> SELECT Name , JobTitle , Salary,  CASE WHEN Salary >= (SELECT SUM(Salary) DIV COUNT(*) FROM employee) THEN "Above Average" ELSE "Under Average" END AS Average FROM employee;
    +---------+-----------------+--------+---------------+
    | Name    | JobTitle        | Salary | Average       |
    +---------+-----------------+--------+---------------+
    | Ben     | Engineer        |  45000 | Under Average |
    | Afa     | Engineer        |  48000 | Under Average |
    | Boss    | Manager         |  75000 | Above Average |
    | Justin  | Senior Engineer |  42000 | Under Average |
    | Jasmine | Manager         |  80000 | Above Average |
    +---------+-----------------+--------+---------------+
    5 rows in set (0.001 sec)
    

    主要是這一段 (SELECT SUM(Salary) DIV COUNT(*) FROM employee) ,先算出總薪水的平均值再來判斷,看是否高於或是低於平均值.

    CASE 
    WHEN Salary >= (SELECT SUM(Salary) DIV COUNT(*) FROM employee) THEN "Above Average" 
    ELSE "Under Average" 
    END 
    

    函數說明:

    • SUM
      計算數字加總.
    • COUNT
      回傳 SELECT 的筆數.
  • IF() – If/else construct
    雖然在 SELECT 時不可以使用 IF THEN ELSE ( Flow Control Statements ),但是可以使用 IF 函數.

    MariaDB [astl]> HELP IF FUNCTION; 
    Name: 'IF FUNCTION'
    Description:
    Syntax:
    IF(expr1,expr2,expr3)
    
    If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns
    expr2; otherwise it returns expr3. IF() returns a numeric or string
    value, depending on the context in which it is used.
    
    URL: https://mariadb.com/kb/en/if-function/
    

    IF(expr1,expr2,expr3) , 當 expr1 成立時回傳 expr2 否則回傳 expr3

    只有 NULL 才是 NULL.

    MariaDB [testdb]> SELECT IF( NULL IS NULL,"NULL","Not NULL");
    +-------------------------------------+
    | IF( NULL IS NULL,"NULL","Not NULL") |
    +-------------------------------------+
    | NULL                                |
    +-------------------------------------+
    1 row in set (0.002 sec)
    

    “NULL” (代表字串 ),0(數字) 也不是 NULL.

    MariaDB [testdb]> SELECT IF( "NULL" IS NULL,"NULL","Not NULL");
    +---------------------------------------+
    | IF( "NULL" IS NULL,"NULL","Not NULL") |
    +---------------------------------------+
    | Not NULL                              |
    +---------------------------------------+
    1 row in set (0.000 sec)
    
    MariaDB [testdb]> SELECT IF( 0 IS NULL,"NULL","Not NULL");
    +----------------------------------+
    | IF( 0 IS NULL,"NULL","Not NULL") |
    +----------------------------------+
    | Not NULL                         |
    +----------------------------------+
    1 row in set (0.001 sec)
    
    MariaDB [testdb]> SELECT IF( 1 IS NULL,"NULL","Not NULL");
    +----------------------------------+
    | IF( 1 IS NULL,"NULL","Not NULL") |
    +----------------------------------+
    | Not NULL                         |
    +----------------------------------+
    1 row in set (0.000 sec)
    
    MariaDB [astl]> SELECT IF(1>2,2,3);
    +-------------+
    | IF(1>2,2,3) |
    +-------------+
    |           3 |
    +-------------+
    1 row in set (0.000 sec)
    

    一樣我們來算看看每一個人的薪水是否高於平均值 (但使用 IF 函數),是則回傳 Above Average 否則回傳 Under Average .

    MariaDB [astl]> SELECT Name , JobTitle , Salary,  IF (Salary >= (SELECT SUM(Salary) DIV COUNT(*) FROM employee) , "Above Average" , "Under Average") AS Average FROM employee;
    +---------+-----------------+--------+---------------+
    | Name    | JobTitle        | Salary | Average       |
    +---------+-----------------+--------+---------------+
    | Ben     | Engineer        |  45000 | Under Average |
    | Afa     | Engineer        |  48000 | Under Average |
    | Boss    | Manager         |  75000 | Above Average |
    | Justin  | Senior Engineer |  42000 | Under Average |
    | Jasmine | Manager         |  80000 | Above Average |
    +---------+-----------------+--------+---------------+
    5 rows in set (0.001 sec)
    
  • IFNULL() – Null if/else construct
    MariaDB [astl]> HELP IFNULL;
    Name: 'IFNULL'
    Description:
    Syntax:
    IFNULL(expr1,expr2)
    
    If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns
    expr2. IFNULL() returns a numeric or string value, depending on the
    context in which it is used.
    
    URL: https://mariadb.com/kb/en/ifnull/
    

    用來判斷 Expression 字串是否為 NULL ,是就以 ALTER_Value 來取代.

    IFNULL(Expression, ALTER_Value)
    
    MariaDB [testdb]> SELECT IFNULL(NULL,'Ben') AS 'IFNULL Result';
    +---------------+
    | IFNULL Result |
    +---------------+
    | Ben           |
    +---------------+
    1 row in set (0.002 sec)
    
    MariaDB [testdb]> SELECT IFNULL(1,'Ben') 'IFNULL Result';
    +---------------+
    | IFNULL Result |
    +---------------+
    | 1             |
    +---------------+
    1 row in set (0.000 sec)
    

    下面範例使用 GROUP BY (依據不同單位做分類,再加總各單位的薪水 )加上 WITH ROLLUP (可以把分類的資料做個總結).

    MariaDB [astl]> SELECT Dept, SUM(salary) AS Total FROM employee GROUP BY Dept WITH ROLLUP;
    +---------+--------+
    | Dept    | Total  |
    +---------+--------+
    | Power   | 128000 |
    | Testing | 162000 |
    | NULL    | 290000 |
    +---------+--------+
    3 rows in set (0.001 sec)
    

    但很討厭的是在總結資料中會顯示為 NULL ,這時候可以利用 IFNULL 來取代 NULL 字串.

    MariaDB [astl]> SELECT IFNULL(Dept,'SUM') AS Dept, SUM(salary) AS Total FROM employee GROUP BY Dept WITH ROLLUP;
    +---------+--------+
    | Dept    | Total  |
    +---------+--------+
    | Power   | 128000 |
    | Testing | 162000 |
    | SUM     | 290000 |
    +---------+--------+
    3 rows in set, 1 warning (0.002 sec)
    
  • NULLIF() – Return NULL if expr1 = expr2
    MariaDB [astl]> HELP NULLIF;
    Name: 'NULLIF'
    Description:
    Syntax:
    NULLIF(expr1,expr2)
    
    Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is
    the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
    
    URL: https://mariadb.com/kb/en/nullif/
    

    當兩個值相同時回傳 NULL ,不同時則回傳第一個值.

    MariaDB [astl]> SELECT NULLIF(1,1);
    +-------------+
    | NULLIF(1,1) |
    +-------------+
    |        NULL |
    +-------------+
    1 row in set (0.001 sec)
    
    MariaDB [astl]> SELECT NULLIF(1,2);
    +-------------+
    | NULLIF(1,2) |
    +-------------+
    |           1 |
    +-------------+
    1 row in set (0.001 sec)
    
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料