文本处理
Left(column, n)
返回字段的左边 N 个字符。Right 同理。
比如:
mysql> select * from staff;
+----+--------+-----------+---------+---------+------------+
| id | name | dept | salary | edlevel | hiredate |
+----+--------+-----------+---------+---------+------------+
| 1 | 张三 | 开发部 | 2000.00 | 3 | 2009-10-11 |
| 2 | 李四 | 开发部 | 2500.00 | 3 | 2009-10-01 |
| 3 | 王五 | 设计部 | 2600.00 | 5 | 2010-10-02 |
| 4 | 王六 | 设计部 | 2300.00 | 4 | 2010-10-03 |
| 5 | 马七 | 设计部 | 2100.00 | 4 | 2010-10-06 |
| 6 | 赵八 | 销售部 | 3000.00 | 5 | 2010-10-05 |
| 7 | 钱九 | 销售部 | 3100.00 | 7 | 2010-10-07 |
| 8 | 孙十 | 销售部 | 3500.00 | 7 | 2010-10-06 |
+----+--------+-----------+---------+---------+------------+
8 rows in set (0.00 sec)
mysql> select Left(name, 1) from staff;
+---------------+
| Left(name, 1) |
+---------------+
| 张 |
| 李 |
| 王 |
| 王 |
| 马 |
| 赵 |
| 钱 |
| 孙 |
+---------------+
8 rows in set (0.00 sec)
Length(column)
一个汉字是三个字符,数字和英文是一个字符。
mysql> select Length(name) from staff;
+--------------+
| Length(name) |
+--------------+
| 6 |
| 6 |
| 6 |
| 6 |
| 6 |
| 6 |
| 6 |
| 6 |
+--------------+
8 rows in set (0.00 sec)
Locate(substr, str)
返回子串在目标中的起始位置,位置从1开始计算。
mysql> select * from customer;
+----+-------+
| id | name |
+----+-------+
| 1 | alice |
+----+-------+
1 row in set (0.00 sec)
mysql> select Locate('ce',name) from customer where id=1;
+-------------------+
| Locate('ce',name) |
+-------------------+
| 4 |
+-------------------+
1 row in set (0.00 sec)
Lower(column)
转小写。Upper 转大写。
mysql> select Lower(name) from customer where id=1;
+-------------+
| Lower(name) |
+-------------+
| alice |
+-------------+
1 row in set (0.00 sec)
LTrim(column)
去掉串左边的空格。
SubString(column, from, to)
位置从1开始计算,取子串。
mysql> select SubString(name, 4,5) from customer where id=1;
+----------------------+
| SubString(name, 4,5) |
+----------------------+
| ce |
+----------------------+
1 row in set (0.00 sec)
日期和时间处理
Now()
返回当前日期和时间。
mysql> select Now();
+---------------------+
| Now() |
+---------------------+
| 2018-05-05 23:31:46 |
+---------------------+
1 row in set (0.00 sec)
CurDate()
返回当前日期。
mysql> select CurDate();
+------------+
| CurDate() |
+------------+
| 2018-05-05 |
+------------+
1 row in set (0.00 sec)
CurTime()
返回当前时间。
mysql> select CurTime();
+-----------+
| CurTime() |
+-----------+
| 23:23:13 |
+-----------+
1 row in set (0.00 sec)
Date_Add()
日期运算函数,比如给当前日期加上 31 天,这样:
mysql> select Date_Add(CurDate(), INTERVAL 31 DAY);
+--------------------------------------+
| Date_Add(CurDate(), INTERVAL 31 DAY) |
+--------------------------------------+
| 2018-06-05 |
+--------------------------------------+
1 row in set (0.00 sec)
Date_Format()
mysql> select Date_Format(CurDate(), '%Y%M%D');
+----------------------------------+
| Date_Format(CurDate(), '%Y%M%D') |
+----------------------------------+
| 2018May5th |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select Date_Format(CurDate(), '%y%m%d');
+----------------------------------+
| Date_Format(CurDate(), '%y%m%d') |
+----------------------------------+
| 180505 |
+----------------------------------+
1 row in set (0.00 sec)
Date()
返回一个日期时间的日期部分。
Time()
返回一个日期时间的时间部分。
数值处理
常用的有:
Mod()
mysql> select * from staff where Mod(id, 2)=1;
+----+--------+-----------+---------+---------+------------+
| id | name | dept | salary | edlevel | hiredate |
+----+--------+-----------+---------+---------+------------+
| 1 | 张三 | 开发部 | 2000.00 | 3 | 2009-10-11 |
| 3 | 王五 | 设计部 | 2600.00 | 5 | 2010-10-02 |
| 5 | 马七 | 设计部 | 2100.00 | 4 | 2010-10-06 |
| 7 | 钱九 | 销售部 | 3100.00 | 7 | 2010-10-07 |
+----+--------+-----------+---------+---------+------------+
4 rows in set (0.00 sec)
Round()
四舍五入的整数值。
Floor()和Ceil()
mysql> select Floor(4.6);
+------------+
| Floor(4.6) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
mysql> select Ceil(4.6);
+-----------+
| Ceil(4.6) |
+-----------+
| 5 |
+-----------+
1 row in set (0.00 sec)