1 文本处理函数
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 时间日期处理函数
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() 返回两个日期之间的天数
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
