字符串函数
Concat() 字符串连接
mysql> select * fromname;+------+-------+-------+
| id | name | major |
+------+-------+-------+
| 1 | Jack | CS |
| 2 | Paul | MS |
| 3 | Linda | SE |
| 7 | Robin | CS |
+------+-------+-------+
4 rows in set (0.00sec)
mysql> select concat(id,name) fromname;+-----------------+
| concat(id,name) |
+-----------------+
| 1Jack |
| 2Paul |
| 3Linda |
| 7Robin |
+-----------------+
4 rows in set (0.05sec)
mysql>
Lcase() 转小写
mysql> select * fromname;+------+-------+-------+
| id | name | major |
+------+-------+-------+
| 1 | Jack | CS |
| 2 | Paul | MS |
| 3 | Linda | SE |
| 7 | Robin | CS |
+------+-------+-------+
4 rows in set (0.00sec)
mysql> select lcase(name) from name where id =1;+-------------+
| lcase(name) |
+-------------+
| jack |
+-------------+
1 row in set (0.01sec)
mysql>
Ucase() 转大写
mysql> select * fromname;+------+-------+-------+
| id | name | major |
+------+-------+-------+
| 1 | Jack | CS |
| 2 | Paul | MS |
| 3 | Linda | SE |
| 7 | Robin | CS |
+------+-------+-------+
4 rows in set (0.00sec)
mysql> select ucase(name) from name where id =1;+-------------+
| ucase(name) |
+-------------+
| JACK |
+-------------+
1 row in set (0.00sec)
mysql>
Length() 字符串长度
mysql> select * fromname;+------+-------+-------+
| id | name | major |
+------+-------+-------+
| 1 | Jack | CS |
| 2 | Paul | MS |
| 3 | Linda | SE |
| 7 | Robin | CS |
+------+-------+-------+
4 rows in set (0.00sec)
mysql> select length(name) fromname;+--------------+
| length(name) |
+--------------+
| 4 |
| 4 |
| 5 |
| 5 |
+--------------+
4 rows in set (0.02 sec)
Ltrim() 去除左边空格
mysql> select * fromname;+------+-------+-------+
| id | name | major |
+------+-------+-------+
| 1 | Jack | CS |
| 2 | Paul | MS |
| 3 | Linda | SE |
| 7 | Robin | CS |
+------+-------+-------+
4 rows in set (0.00sec)
mysql> select ltrim(name) fromname;+-------------+
| ltrim(name) |
+-------------+
| Jack |
| Paul |
| Linda |
| Robin |
+-------------+
4 rows in set (0.02 sec)
Rtrim() 去除右边空格
mysql> select * fromname;+------+-------+-------+
| id | name | major |
+------+-------+-------+
| 1 | Jack | CS |
| 2 | Paul | MS |
| 3 | Linda | SE |
| 7 | Robin | CS |
+------+-------+-------+
4 rows in set (0.00sec)
mysql> select rtrim(name) fromname;+-------------+
| rtrim(name) |
+-------------+
| Jack |
| Paul |
| Linda |
| Robin |
+-------------+
4 rows in set (0.00 sec)
Repeat(string,count) 重复count次 (没有贴原表 同上)
mysql> select repeat(name,2) fromname;+----------------+
| repeat(name,2) |
+----------------+
| JackJack |
| PaulPaul |
| LindaLinda |
| RobinRobin |
+----------------+
4 rows in set (0.00sec)
mysql>
Replace(str,search_str,replace_str) 在str中用replace_str替换searche_str 区分大小写
mysql> select replace(name,"Jack","Tom") fromname;+----------------------------+
| replace(name,"Jack","Tom") |
+----------------------------+
| Tom |
| Paul |
| Linda |
| Robin |
+----------------------------+
4 rows in set (0.00 sec)
Substring(str,position,length) 从position开始,截取length个字符 (第一位从1开始)
mysql> select substring(name,1,3) fromname;+---------------------+
| substring(name,1,3) |
+---------------------+
| Jac |
| Pau |
| Lin |
| Rob |
+---------------------+
4 rows in set (0.00sec)
mysql>
Space(count) 生成count个空格
mysql> select space(2) ;+----------+
| space(2) |
+----------+
| |
+----------+
1 row in set (0.00sec)
mysql> select space(20) ;+----------------------+
| space(20) |
+----------------------+
| |
+----------------------+
1 row in set (0.00 sec)
数学函数
BIN(decimal_number) 十进制转二进制
mysql> select bin(255);+----------+
| bin(255) |
+----------+
| 11111111 |
+----------+
1 row in set (0.00 sec)
CEILING(number) 向上取整 ceiling(天花板)
mysql> select CEILING(2.34);+---------------+
| CEILING(2.34) |
+---------------+
| 3 |
+---------------+
1 row in set (0.01 sec)
FLOOR(number) 向下取整
mysql> select FLOOR(2.34);+-------------+
| FLOOR(2.34) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
MAX(num1,num2) 取最大值
mysql> select max(id) fromname;+---------+
| max(id) |
+---------+
| 7 |
+---------+
1 row in set (0.00 sec)
MIN(num1,num2) 取最小值
mysql> select min(id) fromname;+---------+
| min(id) |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
SQRT(number) 开平方
mysql> select sqrt(2);+--------------------+
| sqrt(2) |
+--------------------+
| 1.4142135623730951 |
+--------------------+
1 row in set (0.02 sec)
RAND() 返回0-1内的随机数
mysql> select rand();+-------------------+
| rand() |
+-------------------+
| 0.979456949028612 |
+-------------------+
1 row in set (0.00 sec)
日期函数
Curdate() 返回当前日期
mysql> selectCurdate();+------------+
| Curdate() |
+------------+
| 2018-09-01 |
+------------+
1 row in set (0.01 sec)
Curtime() 返回当前时间
mysql> selectCurtime();+-----------+
| Curtime() |
+-----------+
| 11:48:13 |
+-----------+
1 row in set (0.00 sec)
Now() 返回当前日期时间
mysql> selectnow();+---------------------+
| now() |
+---------------------+
| 2018-09-01 11:48:37 |
+---------------------+
1 row in set (0.00 sec)
Unix_timestamp(date) 返回当前date的时间戳
mysql> selectUnix_timestamp();+------------------+
| Unix_timestamp() |
+------------------+
| 1535773773 |
+------------------+
1 row in set (0.00 sec)
mysql> select Unix_timestamp("2018-03-04 03:40:20");+---------------------------------------+
| Unix_timestamp("2018-03-04 03:40:20") |
+---------------------------------------+
| 1520106020 |
+---------------------------------------+
1 row in set (0.00 sec)
From_unixtime() 返回时间戳的日期值
mysql> select From_unixtime(1520106020);+---------------------------+
| From_unixtime(1520106020) |
+---------------------------+
| 2018-03-04 03:40:20 |
+---------------------------+
1 row in set (0.00 sec)
Week(date) 返回日期date为一年中的第几周
mysql> select week("2018-03-04 03:40:20");+-----------------------------+
| week("2018-03-04 03:40:20") |
+-----------------------------+
| 9 |
+-----------------------------+
1 row in set (0.00 sec)
Year(date) 返回日期date的年份
mysql> select year("2018-03-04 03:40:20");+-----------------------------+
| year("2018-03-04 03:40:20") |
+-----------------------------+
| 2018 |
+-----------------------------+
1 row in set (0.00 sec)
Datediff(expr,expr2) 返回起始时间expr和结束时间expr2间天数
mysql> select Datediff("2018-03-04 03:40:20","2018-03-05 03:40:20");+-------------------------------------------------------+
| Datediff("2018-03-04 03:40:20","2018-03-05 03:40:20") |
+-------------------------------------------------------+
| -1 |
+-------------------------------------------------------+
1 row in set (0.00 sec)