MySQL-07.單行函數_ZenDei技術網路在線

C-07.單行函數 1.函數的理解 1.1 什麼是函數函數在電腦語言中的使用貫穿始終，函數的作用是什麼呢？它可以把我們經常使用的代碼封裝起來，需要的時候直接調用即可。這樣即提高了代碼效率，又提高了可維護性。在SQL中我們也可以使用函數對檢索出的數據進行函數操作。使用這函數，可以極大地提高用戶對數 ...

C-07.單行函數

1.函數的理解

1.1 什麼是函數

函數在電腦語言中的使用貫穿始終，函數的作用是什麼呢？它可以把我們經常使用的代碼封裝起來，需要的時候直接調用即可。這樣即提高了代碼效率，又提高了可維護性。在SQL中我們也可以使用函數對檢索出的數據進行函數操作。使用這函數，可以極大地提高用戶對資料庫的管理效率。

從函數的定義角度出發，我們可以將函數分成內置函數和自定義函數。在SQL語言中，同樣也包括了內置函數的自定義函數。內置函數是系統內置的通用函數，而自定義函數是我們根據自己的需要編寫的，本章和下一章講解的是SQL的內置函數。

1.2 不同DBMS的函數的差異

我們在使用SQL語言時，是和各種不同的DBMS打交道。DBMS之間的差異性很大，遠大於同一個語言不同版本之間的差異。實際上，只是很少的函數是被DBMS同時支持的。比如，大多數DBMS使用(||)或(+)來做拼接符，而在MySQL中的字元串拼接函數為concat()。大部分DBMS會有自己特定的函數，這就意味著採用SQL函數的代碼可移植性是很差的，因此在使用函數的時候需要特別註意。

1.3 MySQL的內置函數分類

MySQL提供了豐富的內置函數，這些函數使得數據的維護與管理更加方便，能夠更好的提供數據的分析和統計能力，在一定程度上提高了開發人員進行數據分析與統計的效率。
MySQL提供的內置函數從實現的功能角度可以分為數值函數，字元串函數，日期與時間函數，流程式控制制函數，加密與解密函數，獲取MySQL信息函數，聚合函數等。也可以分為單行函數，聚合函數(或分組函數)。

兩種SQL函數

單行函數

操作數據對象
接收參數返回一個結果
只對一行進行變換
每行返回一個結果
可以嵌套
參數可以一列或一個值

2.數值函數

2.1 基本函數

舉例:

SELECT ABS(-10.1),SIGN(-23),SIGN(20),PI(),CEIL(-41.2),CEILING(20.8),FLOOR(20.8),FLOOR(-41.2),MOD(12,5)
FROM dual;

SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-2),RAND(-2) FROM dual;

mysql> SELECT ROUND(10.5),ROUND(10.224,2),ROUND(13.325,-1),TRUNCATE(10.66,1),TRUNCATE(12.66,-1) FROM dual;
+-------------+-----------------+------------------+-------------------+--------------------+
| ROUND(10.5) | ROUND(10.224,2) | ROUND(13.325,-1) | TRUNCATE(10.66,1) | TRUNCATE(12.66,-1) |
+-------------+-----------------+------------------+-------------------+--------------------+
|          11 |           10.22 |               10 |              10.6 |                 10 |
+-------------+-----------------+------------------+-------------------+--------------------+
1 row in set (0.00 sec)

2.2 角度與弧度互換函數

mysql> SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90)) FROM dual;
+--------------------+--------------------+--------------------+-----------------+----------------------+
| RADIANS(30)        | RADIANS(60)        | RADIANS(90)        | DEGREES(2*PI()) | DEGREES(RADIANS(90)) |
+--------------------+--------------------+--------------------+-----------------+----------------------+
| 0.5235987755982988 | 1.0471975511965976 | 1.5707963267948966 |             360 |                   90 |
+--------------------+--------------------+--------------------+-----------------+----------------------+
1 row in set (0.00 sec)

2.3 三角函數

舉例：
ATAN2(m,n)函數返回兩個參數的反正切值。與ATAN(x)函數相比，ATAN2(m,n)需要兩個參數。例如有兩個點point(x1,y1)和point(x2,y2)，使用ATAN(x)函數計算反正切值為ATAN((y2-y1)/(x2-x1))，使用ATAN2(m,n)計算反正切值為ATAN2(x2-x1,y2-y1)。

註意分母不能為0的情況。

mysql> SELECT  SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1))
    -> FROM dual;
+---------------------+------------------+--------------------+------------------+---------------------+
| SIN(RADIANS(30))    | DEGREES(ASIN(1)) | TAN(RADIANS(45))   | DEGREES(ATAN(1)) | DEGREES(ATAN2(1,1)) |
+---------------------+------------------+--------------------+------------------+---------------------+
| 0.49999999999999994 |               90 | 0.9999999999999999 |               45 |                  45 |
+---------------------+------------------+--------------------+------------------+---------------------+
1 row in set (0.00 sec)

2.4 指數與對數

mysql> SELECT POW(2,3),POWER(2,3),EXP(3),LN(EXP(2)),LOG10(100),LOG2(64) FROM dual;
+----------+------------+--------------------+------------+------------+----------+
| POW(2,3) | POWER(2,3) | EXP(3)             | LN(EXP(2)) | LOG10(100) | LOG2(64) |
+----------+------------+--------------------+------------+------------+----------+
|        8 |          8 | 20.085536923187668 |          2 |          2 |        6 |
+----------+------------+--------------------+------------+------------+----------+
1 row in set (0.00 sec)

2.5 進位間的轉換

mysql> SELECT BIN(12),HEX(12),OCT(12),CONV(10,2,8) FROM dual;
+---------+---------+---------+--------------+
| BIN(12) | HEX(12) | OCT(12) | CONV(10,2,8) |
+---------+---------+---------+--------------+
| 1100    | C       | 14      | 2            |
+---------+---------+---------+--------------+
1 row in set (0.00 sec)

3.字元串函數

舉例

mysql> SELECT FIELD('mm','hello','mm','asmr'),FIND_IN_SET('mm','hello,mm,asmr')
    -> FROM dual;
+---------------------------------+-----------------------------------+
| FIELD('mm','hello','mm','asmr') | FIND_IN_SET('mm','hello,mm,asmr') |
+---------------------------------+-----------------------------------+
|                               2 |                                 2 |
+---------------------------------+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REVERSE('asmr'),NULLIF('mysql','mysql'),NULLIF('mysql','mysqld')
    -> FROM dual;
+-----------------+-------------------------+--------------------------+
| REVERSE('asmr') | NULLIF('mysql','mysql') | NULLIF('mysql','mysqld') |
+-----------------+-------------------------+--------------------------+
| rmsa            | NULL                    | mysql                    |
+-----------------+-------------------------+--------------------------+
1 row in set (0.00 sec)

4.日期和時間函數

4.1 獲取日期，時間

舉例：

4.2 日期與時間戳的轉換

mysql> SELECT UNIX_TIMESTAMP(),FROM_UNIXTIME(now()),FROM_UNIXTIME(UNIX_TIMESTAMP()) FROM dual;
+------------------+----------------------+---------------------------------+
| UNIX_TIMESTAMP() | FROM_UNIXTIME(now()) | FROM_UNIXTIME(UNIX_TIMESTAMP()) |
+------------------+----------------------+---------------------------------+
|       1702562470 | NULL                 | 2023-12-14 22:01:10             |
+------------------+----------------------+---------------------------------+
1 row in set (0.00 sec)

4.3 獲取月份，天數，星期數等函數

mysql> SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE()) FROM dual;
+-----------------+------------------+----------------+-----------------+---------------+-------------------+
| YEAR(CURDATE()) | MONTH(CURDATE()) | DAY(CURDATE()) | HOUR(CURTIME()) | MINUTE(NOW()) | SECOND(SYSDATE()) |
+-----------------+------------------+----------------+-----------------+---------------+-------------------+
|            2023 |               12 |             14 |              22 |             5 |                 8 |
+-----------------+------------------+----------------+-----------------+---------------+-------------------+
1 row in set (0.00 sec)

mysql> SELECT MONTHNAME(now()),DAYNAME(now()),WEEKDAY(CURDATE()),QUARTER(SYSDATE()),WEEK(now()),DAYOFYEAR(now()),DAYOFMONTH(now()),DAYOFWEEK(now()) FROM dual;
+------------------+----------------+--------------------+--------------------+-------------+------------------+-------------------+------------------+
| MONTHNAME(now()) | DAYNAME(now()) | WEEKDAY(CURDATE()) | QUARTER(SYSDATE()) | WEEK(now()) | DAYOFYEAR(now()) | DAYOFMONTH(now()) | DAYOFWEEK(now()) |
+------------------+----------------+--------------------+--------------------+-------------+------------------+-------------------+------------------+
| December         | Thursday       |                  3 |                  4 |          50 |              348 |                14 |                5 |
+------------------+----------------+--------------------+--------------------+-------------+------------------+-------------------+------------------+
1 row in set (0.00 sec)

4.4 日期的操作函數

mysql> SELECT EXTRACT(MINUTE FROM now()),EXTRACT(WEEK FROM NOW()),EXTRACT(QUARTER FROM NOW()),EXTRACT(MINUTE_SECOND FROM now()) FROM dual;
+----------------------------+--------------------------+-----------------------------+-----------------------------------+
| EXTRACT(MINUTE FROM now()) | EXTRACT(WEEK FROM NOW()) | EXTRACT(QUARTER FROM NOW()) | EXTRACT(MINUTE_SECOND FROM now()) |
+----------------------------+--------------------------+-----------------------------+-----------------------------------+
|                         13 |                       50 |                           4 |                              1311 |
+----------------------------+--------------------------+-----------------------------+-----------------------------------+
1 row in set (0.00 sec)

4.5 時間和秒鐘轉換的函數

mysql> SELECT TIME_TO_SEC(NOW());
+--------------------+
| TIME_TO_SEC(NOW()) |
+--------------------+
|              85290 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT SEC_TO_TIME(85290);
+--------------------+
| SEC_TO_TIME(85290) |
+--------------------+
| 23:41:30           |
+--------------------+
1 row in set (0.00 sec)

4.6 計算日期和時間的函數

第一組

# now() 也可以是代表日期的常量，如'2023-12-14 23:46:20'
mysql> SELECT DATE_ADD(NOW(),INTERVAL 1 DAY) AS col1,ADDDATE(NOW(),INTERVAL 1 SECOND) AS col2 FROM dual;
+---------------------+---------------------+
| col1                | col2                |
+---------------------+---------------------+
| 2023-12-15 23:46:38 | 2023-12-14 23:46:39 |
+---------------------+---------------------+
1 row in set (0.00 sec) 

mysql> SELECT DATE_SUB(NOW(),INTERVAL 1 DAY) AS col1,SUBDATE(NOW(),INTERVAL 1 SECOND) AS col2 FROM dual;
+---------------------+---------------------+
| col1                | col2                |
+---------------------+---------------------+
| 2023-12-13 23:47:14 | 2023-12-14 23:47:13 |
+---------------------+---------------------+
1 row in set (0.00 sec)

第二組

mysql> SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3') FROM dual;
+---------------------+---------------------+------------------------+
| ADDTIME(NOW(),20)   | SUBTIME(NOW(),30)   | SUBTIME(NOW(),'1:1:3') |
+---------------------+---------------------+------------------------+
| 2023-12-14 23:52:38 | 2023-12-14 23:51:48 | 2023-12-14 22:51:15    |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF(NOW(),'2022-10-01'),TIMEDIFF(NOW(),'2023-10-01 00:00:00'),FROM_DAYS(366) FROM dual;
+------------------------------+---------------------------------------+----------------+
| DATEDIFF(NOW(),'2022-10-01') | TIMEDIFF(NOW(),'2023-10-01 00:00:00') | FROM_DAYS(366) |
+------------------------------+---------------------------------------+----------------+
|                          439 | 838:59:59                             | 0001-01-01     |
+------------------------------+---------------------------------------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT TO_DAYS('0000-12-14'),LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),122) FROM dual;
+-----------------------+-----------------+---------------------------+
| TO_DAYS('0000-12-14') | LAST_DAY(NOW()) | MAKEDATE(YEAR(NOW()),122) |
+-----------------------+-----------------+---------------------------+
|                   348 | 2023-12-31      | 2023-05-02                |
+-----------------------+-----------------+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT PERIOD_ADD(202011,10),MAKETIME(10,25,56) FROM dual;
+-----------------------+--------------------+
| PERIOD_ADD(202011,10) | MAKETIME(10,25,56) |
+-----------------------+--------------------+
|                202109 | 10:25:56           |
+-----------------------+--------------------+
1 row in set (0.00 sec)

註意，圖中關於PERIOD_AD輸入不同的參數可能有問題，不推薦使用

查詢7天內新增用戶有多少？

SELECT COUNT(*) AS num FROM user WHERE TO_DAYS(NOW()) - TO_DAYS(regist_time) <= 7;

4.7 日期的格式化與解析

非GET_FORMAT函數中fmt參數常用的格式符

GET_FORMAT函數參數的格式符

mysql> SELECT TIME_FORMAT(CURTIME(),'%H:%i:%s'),DATE_FORMAT(CURDATE(),'%Y-%m-%d') FROM dual;
+-----------------------------------+-----------------------------------+
| TIME_FORMAT(CURTIME(),'%H:%i:%s') | DATE_FORMAT(CURDATE(),'%Y-%m-%d') |
+-----------------------------------+-----------------------------------+
| 23:02:36                          | 2023-12-15                        |
+-----------------------------------+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT GET_FORMAT(DATE,'USA'),GET_FORMAT(TIME,'USA') FROM dual;
+------------------------+------------------------+
| GET_FORMAT(DATE,'USA') | GET_FORMAT(TIME,'USA') |
+------------------------+------------------------+
| %m.%d.%Y               | %h:%i:%s %p            |
+------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SELECT STR_TO_DATE('2021-01-05 00:00:00','%Y-%m-%d') FROM dual;
+-----------------------------------------------+
| STR_TO_DATE('2021-01-05 00:00:00','%Y-%m-%d') |
+-----------------------------------------------+
| 2021-01-05                                    |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT STR_TO_DATE('2021-01-05 00:00:00','%Y-%m-%d %H:%i:%s') FROM dual;
+--------------------------------------------------------+
| STR_TO_DATE('2021-01-05 00:00:00','%Y-%m-%d %H:%i:%s') |
+--------------------------------------------------------+
| 2021-01-05 00:00:00                                    |
+--------------------------------------------------------+
1 row in set (0.00 sec

註意，沒有STR_TO_TIME函數。

5.流程式控制制函數

流程式控制制函數可以根據不同的條件，執行不同的處理流程，可以在SQL語句中，實現不同的條件選擇。MySQL中的流程處理函數主要包括IF()，IFNULL()和CASE()函數。

mysql> SELECT IF(1 > 0,'正確','錯誤'),IFNULL(null,'Hello,World') FROM dual;
+-------------------------+----------------------------+
| IF(1 > 0,'正確','錯誤') | IFNULL(null,'Hello,World') |
+-------------------------+----------------------------+
| 正確                    | Hello,World                |
+-------------------------+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE WHEN 1 > 0 THEN '第一個成立' WHEN 2 > 0 THEN '第二個成立' ELSE '前兩個都不成立' END
    -> ;
+------------------------------------------------------------------------------------------+
| CASE WHEN 1 > 0 THEN '第一個成立' WHEN 2 > 0 THEN '第二個成立' ELSE '前兩個都不成立' END |
+------------------------------------------------------------------------------------------+
| 第一個成立                                                                               |
+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE 1
    -> WHEN 1 THEN '值是1'
    -> WHEN 2 THEN '值是2'
    -> ELSE '值是N'
    -> END;
+-----------------------------------------------------------------+
| CASE 1
WHEN 1 THEN '值是1'
WHEN 2 THEN '值是2'
ELSE '值是N'
END |
+-----------------------------------------------------------------+
| 值是1                                                           |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT last_name,salary,department_id,CASE department_id WHEN 10 THEN 1.1 * salary
    -> WHEN 20 THEN (1.2 * salary) WHEN 30 THEN (1.3 * salary) ELSE salary END '薪水'
    -> FROM employees;
+-------------+----------+---------------+----------+
| last_name   | salary   | department_id | 薪水     |
+-------------+----------+---------------+----------+
| King        | 24000.00 |            90 | 24000.00 |
| Kochhar     | 17000.00 |            90 | 17000.00 |
| De Haan     | 17000.00 |            90 | 17000.00 |
...
| Mavris      |  6500.00 |            40 |  6500.00 |
| Baer        | 10000.00 |            70 | 10000.00 |
| Higgins     | 12000.00 |           110 | 12000.00 |
| Gietz       |  8300.00 |           110 |  8300.00 |
+-------------+----------+---------------+----------+
107 rows in set (0.00 sec)

6.加密與解密函數

加密與解密函數主要用於對資料庫中的數據進行加密和解密處理，以防止數據被他人竊取，這些函數在保證資料庫安全時非常有用。

mysql> SELECT PASSWORD('wind');
+-------------------------------------------+
| PASSWORD('wind')                          |
+-------------------------------------------+
| *E04A13B4BCD1DCBE73B56F656D27615E2C2BFE55 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

註意password函數在MySQL8.0後已經取消支持了。上述時在5.7版本下執行的。

mysql> SELECT PASSWORD('wind');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('wind')' at line 1

8.0環境下的執行會報錯。

mysql> SELECT MD5('wind');
+----------------------------------+
| MD5('wind')                      |
+----------------------------------+
| 7e25b972e192b01004b62346ee9975a5 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT SHA('WIND');
+------------------------------------------+
| SHA('WIND')                              |
+------------------------------------------+
| c514a747e8f0bc9e5d9beba8fe62416cc7b2a66e |
+------------------------------------------+
1 row in set (0.00 sec)

MD5()和SHA()，5.7和8.0都支持。
ENCODE()和DECODE()也都是在8.0中不支持。

7.MySQL信息函數

MySQL內置了一些可以查詢MySQL信息的函數，這些函數主要用於幫助資料庫開發或運維人員更好地維護資料庫。

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| atguigudb  |
+------------+
1 row in set (0.00 sec)

mysql> SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER();
+----------------+----------------+----------------+----------------+
| USER()         | CURRENT_USER() | SYSTEM_USER()  | SESSION_USER() |
+----------------+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT CHARSET('abc'),COLLATION('abc');
+----------------+------------------+
| CHARSET('abc') | COLLATION('abc') |
+----------------+------------------+
| gbk            | gbk_chinese_ci   |
+----------------+------------------+
1 row in set (0.00 sec)

8.其他函數

MySQL有些函數，無法進行具體的分類，但是使用較為頻繁。

# 如果n的值小於或等於0，只保留整數
mysql> SELECT FORMAT(123.12,2),FORMAT(123.354,2),FORMAT(12.546,-1);
+------------------+-------------------+-------------------+
| FORMAT(123.12,2) | FORMAT(123.354,2) | FORMAT(12.546,-1) |
+------------------+-------------------+-------------------+
| 123.12           | 123.35            | 13                |
+------------------+-------------------+-------------------+
1 row in set (0.00 sec)

mysql> SELECT CONV(10,10,2),CONV(10,10,16),CONV(NULL,10,2);
+---------------+----------------+-----------------+
| CONV(10,10,2) | CONV(10,10,16) | CONV(NULL,10,2) |
+---------------+----------------+-----------------+
| 1010          | A              | NULL            |
+---------------+----------------+-----------------+
1 row in set (0.00 sec)

#計算公式192 * 256^3 + 168 * 256^2 + 0 * 256 + 130 = 3232235650
mysql> SELECT INET_ATON('192.168.0.130');
+----------------------------+
| INET_ATON('192.168.0.130') |
+----------------------------+
|                 3232235650 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT INET_NTOA(3232235650);
+-----------------------+
| INET_NTOA(3232235650) |
+-----------------------+
| 192.168.0.130         |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT BENCHMARK(10000,MD5('WIND'));
+------------------------------+
| BENCHMARK(10000,MD5('WIND')) |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CHARSET('mysql'),CHARSET(CONVERT('mysql' USING 'utf8'));
+------------------+----------------------------------------+
| CHARSET('mysql') | CHARSET(CONVERT('mysql' USING 'utf8')) |
+------------------+----------------------------------------+
| gbk              | utf8mb3                                |
+------------------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

練習

使用的表結構

# Chapter07
-- 1.顯示系統時間
SELECT NOW() FROM DUAL;

-- 2.查詢員工號，姓名，工資，以及工資提高20%後的結果
SELECT employee_id,last_name,salary,1.2 * salary AS new_salary FROM employees;

-- 3.將員工的姓名按首字母排序，並寫出姓名的長度
SELECT last_name,LENGTH(last_name) AS 'name_length'
FROM employees
ORDER BY last_name;

-- 4.查詢員工id,last_name,salary,並作為一個列輸出，別名為OUT_PUT
SELECT CONCAT(employee_id,,'',last_name,',',salary) AS 'OUT_PUt'
FROM employees;
 
DESC employees;
-- hire_date記錄的是各個員工入職的時間
-- 年限 = year(now()) - year(date)
-- 工作天數 = to_days(year) - TO_days(hire_date)
-- 5.查詢公司各員工工作的年限，工作的天數，並按工作年數的降序排序
SELECT
    last_name,
    hire_date,
    (TO_DAYS(NOW()) - TO_DAYS(hire_date)) / 365 AS 'work_years',
    TO_DAYS(NOW()) - TO_DAYS(hire_date) AS 'word_days'
FROM
    employees
ORDER BY work_years DESC;

-- 6.查詢員工姓名，hire_date,department_id,滿足以下條件，入職時間在1997年之後，department_id為80
-- or 90 or 110,commission_pct不為空
SELECT hire_date,department_id,commission_pct
FROM employees
WHERE YEAR(hire_date) >= 1997 AND department_id IN (80,90,110) AND NOT ISNULL(commission_pct);

-- 7.查詢公司中入職超過10000天的員工姓名，入職時間
SELECT hire_date,last_name,TO_DAYS(NOW()) - TO_DAYS(hire_date) AS 'work_days'
FROM employees 
WHERE TO_DAYS(NOW()) - TO_DAYS(hire_date) > 10000;

-- 8.做一個查詢，產生下麵的結果
-- Kingearns24000.00monthly but wants72000 別名是DREAM SALARY
SELECT CONCAT(last_name,'earns',salary,'monthly but wants',FLOOR(salary * 3)) AS 'DREAM SALARY'
FROM employees;


DESC employees;
-- 9.使用case-when ,按照下麵條件
-- job grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E
SELECT 
    last_name,
    job_id,
    CASE job_id 
        WHEN 'AD_PRES' THEN 'A'
        WHEN 'ST_MAN' THEN 'B'
        WHEN 'IT_PROG' THEN 'C'
        WHEN 'SA_REP' THEN 'D'
        WHEN 'ST_CLERK' THEN 'E'
        ELSE '其他'
    END 'Grade'
FROM employees;

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。