mysql集成函数_mysql的内置函数

字符串函数

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)