文本处理
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)