SELECT UPPER('Hello');

SELECT LOWER('Hello');

SELECT LENGTH('葉綠體不忘呼吸aaaa');

SELECT CONCAT('Hello','世界') AS result;

#從第4個(gè)開始截取，截取后面全部
SELECT SUBSTR('我相信光',4);

#從第1個(gè)開始截取，截取3個(gè)
SELECT SUBSTR('我相信光',1,3);

SELECT INSTR('國(guó)足10月13日客戰(zhàn)沙特','沙特') AS result;

SELECT TRIM('a' FROM 'aaaaa葉aa綠體aaaaa') AS result;

#去空格
SELECT TRIM('    葉aa綠體a    ') AS result;

SELECT LPAD('葉綠體',9,'a') AS result;

SELECT REPLACE('a葉aaa綠體aaaa','a','B') AS result;

#默認(rèn)保留一位小數(shù)
SELECT ROUND(1.62) AS result;

#保留兩位小數(shù)
SELECT ROUND(1.627,2) AS result;

SELECT CEIL(1.002) AS result;

SELECT FLOOR(1.002) AS result;

SELECT TRUNCATE(1.699,1) AS result;

SELECT MOD(10,-3) AS result;

SELECT NOW();

SELECT CURDATE();

SELECT CURTIME();

SELECT YEAR(NOW());

SELECT YEAR('2021-09-30') AS result;

SELECT MONTH('2021-09-30') AS result;

SELECT MONTHNAME('2021-09-30') AS result;

#相當(dāng)于是解析：兩個(gè)參數(shù)格式要匹配
SELECT STR_TO_DATE('9-30 2021','%m-%d %Y') AS result;

#相當(dāng)于是格式化
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS result;

#查看版本
SELECT VERSION();
#查看數(shù)據(jù)庫(kù)
SELECT DATABASE();
#查看用戶
SELECT USER();

SELECT IF(10>5,'大','小') AS result;

CASE 要判斷的
WHEN 常量1 THEN 語(yǔ)句1;或者要顯示的值1
...
ELSE 語(yǔ)句;或者要顯示的值
END

#示例為要顯示的值，不加‘;'
SELECT `last_name`,`salary`,`department_id`,
CASE `department_id`
WHEN 100 THEN `salary`*(1+0.8)
WHEN 90 THEN `salary`*(1+0.6)
WHEN 80 THEN `salary`*(1+0.4)
WHEN 70 THEN `salary`*(1+0.2)
ELSE `salary`
END AS 最終工資
FROM employees;

CASE 
WHEN 條件1 THEN SELECT 語(yǔ)句1;或者要顯示的值1
...
ELSE 語(yǔ)句;或者要顯示的值
END

#示例為要顯示的值，不加‘;'
SELECT `last_name`,`salary`,
CASE
WHEN `salary`>20000 THEN 'A級(jí)別'
WHEN `salary`>15000 THEN 'B級(jí)別'
WHEN `salary`>10000 THEN 'C級(jí)別'
ELSE 'D級(jí)別'
END AS 等級(jí)
FROM employees;