MySQL講義第41講——select 查詢之函式（4）：流程控制函式
使用函式可在查詢時構造更加靈活的查詢條件，MySQL 提供了IF、IFNULL、CASE等流程控制函式，下面對流程控制函式進行詳細的介紹并舉例說明其用法，
一、資料準備
在當前資料庫中創建一個保存上市公司資訊的 listed_company 表，表結構及表中的資料如下：
CREATE TABLE listed_company(
company_id CHAR(6) PRIMARY KEY,
company_abbreviation CHAR(20),
company_fullname CHAR(200),
English_name CHAR(200),
registered_address CHAR(200),
listing_date DATETIME,
total_share_capital DECIMAL(16,2),
circulating_share_capital DECIMAL(16,2),
industry CHAR(100),
company_website CHAR(200)
);
mysql> DESC listed_company;
+---------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+---------------+------+-----+---------+-------+
| company_id | char(6) | NO | PRI | NULL | |
| company_abbreviation | char(20) | YES | | NULL | |
| company_fullname | char(200) | YES | | NULL | |
| English_name | char(200) | YES | | NULL | |
| registered_address | char(200) | YES | | NULL | |
| listing_date | datetime | YES | | NULL | |
| total_share_capital | decimal(16,2) | YES | | NULL | |
| circulating_share_capital | decimal(16,2) | YES | | NULL | |
| industry | char(100) | YES | | NULL | |
| company_website | char(200) | YES | | NULL | |
+---------------------------+---------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
SELECT *
FROM listed_company
WHERE company_id = '000004'\G
*************************** 1. row ***************************
company_id: 000004
company_abbreviation: 國農科技
company_fullname: 深圳中國農大科技股份有限公司
English_name: SHENZHEN CAU TECHNOLOGY CO., LTD
registered_address: 廣東省深圳市南山區中心路（深圳灣段）3333號中鐵南方總部大廈503室
listing_date: 1990-12-01 00:00:00
total_share_capital: 83976684.00
circulating_share_capital: 82905273.00
industry: C 制造業
company_website: www.sz000004.cn
1 row in set (0.00 sec)
二、MySQL 流程控制函式介紹
1、IF() 函式
IF() 函式可以根據邏輯運算式的不同取值回傳不同的運算式結果，語法結構如下：
IF(邏輯運算式,結果運算式1,結果運算式2);
--說明：
如果 邏輯運算式 結果為真，回傳 結果運算式1 的值，如果 邏輯運算式 結果為假，則回傳 結果運算式2 的值，
舉例：
（1）基本用法
SET @date = NOW();
SELECT
@date,
IF(MOD(YEAR(@date),4) = 0 AND
MOD(YEAR(@date),100) != 0 OR
MOD(YEAR(@date),400) = 0,'閏年','不是閏年') AS '是否如年';
+---------------------+--------------+
| @date | 是否如年 |
+---------------------+--------------+
| 2020-11-05 10:51:37 | 閏年 |
+---------------------+--------------+
1 row in set (0.00 sec)
（2）查詢股票資訊，總股本超過 5000000000 股的顯示為大盤股
SELECT
company_id,
company_abbreviation,
total_share_capital,
IF(total_share_capital >= 5000000000,'大盤股','') AS large_cap_stocks
FROM
listed_company
WHERE
company_id < '000020';
+------------+----------------------+---------------------+------------------+
| company_id | company_abbreviation | total_share_capital | large_cap_stocks |
+------------+----------------------+---------------------+------------------+
| 000001 | 平安銀行 | 17170411366.00 | 大盤股 |
| 000002 | 萬 科Ａ | 9724196533.00 | 大盤股 |
| 000004 | 國農科技 | 83976684.00 | |
| 000005 | 世紀星源 | 1058536842.00 | |
| 000006 | 深振業Ａ | 1349995046.00 | |
| 000007 | 全新好 | 230965363.00 | |
| 000008 | 神州高鐵 | 2757709279.00 | |
| 000009 | 中國寶安 | 2149344971.00 | |
| 000010 | 美麗生態 | 819854713.00 | |
| 000011 | 深物業A | 528373849.00 | |
| 000012 | 南 玻Ａ | 1312751568.00 | |
| 000014 | 沙河股份 | 201705187.00 | |
| 000016 | 深康佳Ａ | 1596593800.00 | |
| 000017 | 深中華A | 302984965.00 | |
| 000018 | 神州長城 | 1434441780.00 | |
| 000019 | 深深寶Ａ | 404575476.00 | |
+------------+----------------------+---------------------+------------------+
16 rows in set (0.00 sec)
2、IFNULL() 函式
IFNULL() 函式可以判斷一個運算式是否為空，如果不為空，回傳當前運算式的值，如果為空，就回傳另一個運算式的值，語法格式如下：
IFNULL(運算式1,運算式2);
--說明：
（1）判斷 運算式1 的值是否為空，如果不為空，就回傳【運算式1】的值，否則回傳【運算式2】的值，
舉例：
（1）執行查詢，把 t1 表中為空的電話號碼顯示為 UNKOWN
CREATE TABLE t2(
id int primary key,
name char(20),
phone char(11)
);
INSERT INTO t2
VALUES(1101,'張薇','15937320588'),
(1102,'王濤',NULL),
(1103,'張剛','13703732088'),
(1104,'劉杰',NULL);
SELECT
ID,
NAME,
IFNULL(PHONE,'UNKOWN')
FROM
t2;
+------+--------+------------------------+
| ID | NAME | IFNULL(PHONE,'UNKOWN') |
+------+--------+------------------------+
| 1101 | 張薇 | 15937320588 |
| 1102 | 王濤 | UNKOWN |
| 1103 | 張剛 | 13703732088 |
| 1104 | 劉杰 | UNKOWN |
+------+--------+------------------------+
4 rows in set (0.02 sec)
（2）查詢各行業的上市公司數量并進行匯總
SELECT
industry,
COUNT(*) AS cnt_company
FROM
listed_company
GROUP BY
industry
WITH ROLLUP;
+----------------+-------------+
| industry | cnt_company |
+----------------+-------------+
| - | 60 |
| A 農林牧漁 | 30 |
| B 采礦業 | 29 |
| C 制造業 | 1315 |
| D 水電煤氣 | 39 |
| E 建筑業 | 49 |
| F 批發零售 | 65 |
| G 運輸倉儲 | 28 |
| H 住宿餐飲 | 8 |
| I 資訊技術 | 168 |
| J 金融業 | 18 |
| K 房地產 | 61 |
| L 商務服務 | 27 |
| M 科研服務 | 15 |
| N 公共環保 | 21 |
| P 教育 | 1 |
| Q 衛生 | 5 |
| R 文化傳播 | 25 |
| S 綜合 | 8 |
| 公用事業 | 101 |
| 商業 | 96 |
| 地產 | 33 |
| 工業 | 735 |
| 綜合 | 191 |
| NULL | 3128 |
+----------------+-------------+
25 rows in set (0.01 sec)
（3）把上例中的匯總行的標題 NULL修改為 TOTAL
SELECT
IFNULL(industry,'TOTAL'),
COUNT(*) AS cnt_company
FROM
listed_company
GROUP BY
industry
WITH ROLLUP;
+--------------------------+-------------+
| IFNULL(industry,'TOTAL') | cnt_company |
+--------------------------+-------------+
| - | 60 |
| A 農林牧漁 | 30 |
| B 采礦業 | 29 |
| C 制造業 | 1315 |
| D 水電煤氣 | 39 |
| E 建筑業 | 49 |
| F 批發零售 | 65 |
| G 運輸倉儲 | 28 |
| H 住宿餐飲 | 8 |
| I 資訊技術 | 168 |
| J 金融業 | 18 |
| K 房地產 | 61 |
| L 商務服務 | 27 |
| M 科研服務 | 15 |
| N 公共環保 | 21 |
| P 教育 | 1 |
| Q 衛生 | 5 |
| R 文化傳播 | 25 |
| S 綜合 | 8 |
| 公用事業 | 101 |
| 商業 | 96 |
| 地產 | 33 |
| 工業 | 735 |
| 綜合 | 191 |
| TOTAL | 3128 |
+--------------------------+-------------+
25 rows in set (0.01 sec)
5、CASE() 函式
CASE() 函式用于多分支判斷，有兩種用法，個人認為，第二種用法較為靈活，完全可以代替第一種用法，語法格式如下：
第一種用法：
CASE 運算式0
WHEN [value1] THEN 運算式1
[WHEN [value2] THEN 運算式2 ...]
[ELSE 運算式n]
END;
--說明：
（1）判斷 CASE 后面的 "運算式0" 和 WHEN 后面的 "valuei" 是否相等，
（2）如果 "value1" 的值和 "運算式0" 的值相等，就回傳 "運算式1" 的值，
（3）如果 "value2" 的值和 "運算式0" 的值相等，就回傳 "運算式2" 的值，以此類推，
（4）如果所有的 "value" 都不等于 "運算式0" 的值，就回傳 ELSE 后面的 "運算式n" 的值，
（5）如果所有的 "value" 都不等于 "運算式0" 的值，并且沒有 ELSE 就回傳 NULL 值，
第二種用法：
CASE
WHEN [邏輯運算式1] THEN 運算式1
[WHEN [邏輯運算式2] THEN 運算式2 ...]
[ELSE 運算式n]
END;
--說明：
（1）直接判斷 "邏輯運算式i" 的值，哪個為真就回傳對應的 "運算式i" 的值，
（2）如果所有的 "邏輯運算式i" 的值均為假，回傳 ELSE 后面的 "運算式n" 的值，
（3）如果所有的 "邏輯運算式i" 的值均為假，并且沒有 ELSE，則回傳 NULL 值，
舉例：
（1）查詢股票是大盤股、中盤股還是小盤股，判斷標準為：大于等于 50 億股為大盤股，1 億股和 50 億股之間為中盤股，小于 1 億股為小盤股，
SELECT
company_id,
company_abbreviation,
total_share_capital,
CASE
WHEN total_share_capital >= 5000000000 THEN '大盤股'
WHEN total_share_capital >= 100000000 THEN '中盤股'
ELSE '小盤股'
END AS large_cap_stocks
FROM
listed_company
WHERE
company_id < '000020';
+------------+----------------------+---------------------+------------------+
| company_id | company_abbreviation | total_share_capital | large_cap_stocks |
+------------+----------------------+---------------------+------------------+
| 000001 | 平安銀行 | 17170411366.00 | 大盤股 |
| 000002 | 萬 科Ａ | 9724196533.00 | 大盤股 |
| 000004 | 國農科技 | 83976684.00 | 小盤股 |
| 000005 | 世紀星源 | 1058536842.00 | 中盤股 |
| 000006 | 深振業Ａ | 1349995046.00 | 中盤股 |
| 000007 | 全新好 | 230965363.00 | 中盤股 |
| 000008 | 神州高鐵 | 2757709279.00 | 中盤股 |
| 000009 | 中國寶安 | 2149344971.00 | 中盤股 |
| 000010 | 美麗生態 | 819854713.00 | 中盤股 |
| 000011 | 深物業A | 528373849.00 | 中盤股 |
| 000012 | 南 玻Ａ | 1312751568.00 | 中盤股 |
| 000014 | 沙河股份 | 201705187.00 | 中盤股 |
| 000016 | 深康佳Ａ | 1596593800.00 | 中盤股 |
| 000017 | 深中華A | 302984965.00 | 中盤股 |
| 000018 | 神州長城 | 1434441780.00 | 中盤股 |
| 000019 | 深深寶Ａ | 404575476.00 | 中盤股 |
+------------+----------------------+---------------------+------------------+
16 rows in set (0.01 sec)
（2）創建一張表 t3，表結構及表中的資料如下：
CREATE TABLE score(
stu_id int primary key,
stu_name char(20),
score int
);
INSERT INTO score
VALUES(2018001,'張華',89),(2018002,'李明',89),
(2018003,'趙蕓蕓',52),(2018004,'張靜',49),
(2018005,'劉倩',68),(2018006,'張菲',95),
(2018007,'王兵',93),(2018008,'李冰冰',77),
(2018009,'王艷艷',82),(2018010,'劉艷',65);
（3）查詢學生成績，并顯示成績的等級：優秀（>= 90）、良好（80 - 90）、中等（70 - 80）、及格（60 - 70）和不及格（< 60），
使用第一種格式查詢：
SELECT
*,
CASE FLOOR(score / 10)
WHEN 9 THEN '優秀'
WHEN 8 THEN '良好'
WHEN 7 THEN '中等'
WHEN 6 THEN '及格'
ELSE '不及格'
END AS grade
FROM
score;
+---------+-----------+-------+-----------+
| stu_id | stu_name | score | grade |
+---------+-----------+-------+-----------+
| 2018001 | 張華 | 89 | 良好 |
| 2018002 | 李明 | 89 | 良好 |
| 2018003 | 趙蕓蕓 | 52 | 不及格 |
| 2018004 | 張靜 | 49 | 不及格 |
| 2018005 | 劉倩 | 68 | 及格 |
| 2018006 | 張菲 | 95 | 優秀 |
| 2018007 | 王兵 | 93 | 優秀 |
| 2018008 | 李冰冰 | 77 | 中等 |
| 2018009 | 王艷艷 | 82 | 良好 |
| 2018010 | 劉艷 | 65 | 及格 |
+---------+-----------+-------+-----------+
10 rows in set (0.01 sec)
使用第二種格式查詢：
SELECT
*,
CASE
WHEN score >= 90 THEN '優秀'
WHEN score >= 80 THEN '良好'
WHEN score >= 70 THEN '中等'
WHEN score >= 60 THEN '及格'
ELSE '不及格'
END AS grade
FROM
score;
+---------+-----------+-------+-----------+
| stu_id | stu_name | score | grade |
+---------+-----------+-------+-----------+
| 2018001 | 張華 | 89 | 良好 |
| 2018002 | 李明 | 89 | 良好 |
| 2018003 | 趙蕓蕓 | 52 | 不及格 |
| 2018004 | 張靜 | 49 | 不及格 |
| 2018005 | 劉倩 | 68 | 及格 |
| 2018006 | 張菲 | 95 | 優秀 |
| 2018007 | 王兵 | 93 | 優秀 |
| 2018008 | 李冰冰 | 77 | 中等 |
| 2018009 | 王艷艷 | 82 | 良好 |
| 2018010 | 劉艷 | 65 | 及格 |
+---------+-----------+-------+-----------+
10 rows in set (0.00 sec)
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/205953.html
標籤：其他
上一篇：Oracle中常用函式
下一篇：進入資料結構中樹的世界