1 文本處理函數(shù)
1.1 顯示表格
SELECT * FROM student_info;
Screenshot 2020-02-29 at 9.00.25 PM.png
1.2 LEFT() 返回從左邊取指定長度的子串
SELECT LEFT(name, 1) FROM student_info;
Screenshot 2020-02-29 at 9.02.44 PM.png
1.3 RIGHT() 返回從右邊取指定長度的子串
SELECT RIGHT(name, 1) FROM student_info;
Screenshot 2020-02-29 at 9.03.44 PM.png
1.4 LENGTH() 返回字符串的長度
SELECT LENGTH(name) FROM student_info;
Screenshot 2020-02-29 at 9.04.43 PM.png
1.5 UPPER() 返回大寫格式字符串
先新建一個示例表格：
CREATE TABLE example(
id INT NOT NULL,
info VARCHAR(64) NOT NULL
);
INSERT example VALUE
(1, 'abc'),
(2, 'ABC'),
(3, ' ABC'),
(4, 'ABC '),
(5, ' ABC '),
(6, 'abcdefg');
SELECT * FROM example;
Screenshot 2020-02-29 at 9.07.23 PM.png
SELECT UPPER(info) FROM example WHERE id = 1;
Screenshot 2020-02-29 at 9.10.49 PM.png
1.6 LOWER() 返回小寫格式字符串
SELECT LOWER(info) FROM example WHERE id = 2;
Screenshot 2020-02-29 at 9.11.19 PM.png
1.7 LTRIM() 將字符串左邊空格去除后返回
SELECT LTRIM(LOWER(INFO)) FROM example WHERE id = 3;
Screenshot 2020-02-29 at 9.12.51 PM.png
1.8 RTRIM() 將字符串右邊空格去除后返回
SELECT RTRIM(LOWER(INFO)) FROM example WHERE ID = 4;
Screenshot 2020-02-29 at 9.13.51 PM.png
1.9 TRIM() 將字符串左右兩邊的空格去除后返回
SELECT TRIM(LOWER(INFO)) FROM example WHERE ID = 5;
Screenshot 2020-02-29 at 9.14.45 PM.png
1.10 SUBSTRING() 從字符串第X位開始截取X位字符
SELECT SUBSTRING(INFO, 2, 2) FROM example WHERE id = 6;
Screenshot 2020-02-29 at 9.15.39 PM.png
2 時間日期處理函數(shù)
2.1 NOW() 返回當前的日期和時間
SELECT NOW();
Screenshot 2020-02-29 at 9.16.57 PM.png
2.2 CURDATE() 返回當前的日期
SELECT CURDATE();
Screenshot 2020-02-29 at 9.18.00 PM.png
2.3 CURTIME() 返回當前的時間
SELECT CURTIME();
Screenshot 2020-02-29 at 9.18.59 PM.png
2.4 EXTRACT() 返回日期/時間按的單獨部分
SELECT EXTRACT(YEAR FROM NOW());
Screenshot 2020-02-29 at 9.20.01 PM.png
SELECT EXTRACT(MONTH FROM NOW());
Screenshot 2020-02-29 at 9.20.33 PM.png
2.5 DATE_ADD() 給日期添加指定的時間間隔
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
Screenshot 2020-02-29 at 9.21.21 PM.png
2.6 DATE_SUB() 從日期減去指定的時間間隔
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR);
Screenshot 2020-02-29 at 9.22.25 PM.png
2.7 DATEDIFF() 返回兩個日期之間的天數(shù)
SELECT DATEDIFF('2019-02-28', '2020-02-29');
Screenshot 2020-02-29 at 9.23.03 PM.png
2.8 DATE_FORMAT() 用不同的格式顯示日期/時間
SELECT DATE_FORMAT('2020-02-29', '%Y年%m月%d日');
Screenshot 2020-02-29 at 9.23.48 PM.png
