mysql的内置函数
字符串函数
Concat() 字符串连接
mysql> select * from name; +------+-------+-------+ | id | name | major | +------+-------+-------+ | 1 | Jack | CS | | 2 | Paul | MS | | 3 | Linda | SE | | 7 | Robin | CS | +------+-------+-------+ 4 rows in set (0.00 sec) mysql> select concat(id,name) from name; +-----------------+ | concat(id,name) | +-----------------+ | 1Jack | | 2Paul | | 3Linda | | 7Robin | +-----------------+ 4 rows in set (0.05 sec) mysql>
Lcase() 转小写
mysql> select * from name; +------+-------+-------+ | id | name | major | +------+-------+-------+ | 1 | Jack | CS | | 2 | Paul | MS | | 3 | Linda | SE | | 7 | Robin | CS | +------+-------+-------+ 4 rows in set (0.00 sec) mysql> select lcase(name) from name where id =1; +-------------+ | lcase(name) | +-------------+ | jack | +-------------+ 1 row in set (0.01 sec) mysql>
Ucase() 转大写
mysql> select * from name; +------+-------+-------+ | id | name | major | +------+-------+-------+ | 1 | Jack | CS | | 2 | Paul | MS | | 3 | Linda | SE | | 7 | Robin | CS | +------+-------+-------+ 4 rows in set (0.00 sec) mysql> select ucase(name) from name where id =1; +-------------+ | ucase(name) | +-------------+ | JACK | +-------------+ 1 row in set (0.00 sec) mysql>
Length() 字符串长度
mysql> select * from name; +------+-------+-------+ | id | name | major | +------+-------+-------+ | 1 | Jack | CS | | 2 | Paul | MS | | 3 | Linda | SE | | 7 | Robin | CS | +------+-------+-------+ 4 rows in set (0.00 sec) mysql> select length(name) from name; +--------------+ | length(name) | +--------------+ | 4 | | 4 | | 5 | | 5 | +--------------+ 4 rows in set (0.02 sec)
Ltrim() 去除左边空格
mysql> select * from name; +------+-------+-------+ | id | name | major | +------+-------+-------+ | 1 | Jack | CS | | 2 | Paul | MS | | 3 | Linda | SE | | 7 | Robin | CS | +------+-------+-------+ 4 rows in set (0.00 sec) mysql> select ltrim(name) from name; +-------------+ | ltrim(name) | +-------------+ | Jack | | Paul | | Linda | | Robin | +-------------+ 4 rows in set (0.02 sec)
Rtrim() 去除右边空格
mysql> select * from name; +------+-------+-------+ | id | name | major | +------+-------+-------+ | 1 | Jack | CS | | 2 | Paul | MS | | 3 | Linda | SE | | 7 | Robin | CS | +------+-------+-------+ 4 rows in set (0.00 sec) mysql> select rtrim(name) from name; +-------------+ | rtrim(name) | +-------------+ | Jack | | Paul | | Linda | | Robin | +-------------+ 4 rows in set (0.00 sec)
Repeat(string,count) 重复count次 （没有贴原表 同上）
mysql> select repeat(name,2) from name; +----------------+ | repeat(name,2) | +----------------+ | JackJack | | PaulPaul | | LindaLinda | | RobinRobin | +----------------+ 4 rows in set (0.00 sec) mysql>
Replace(str,search_str,replace_str) 在str中用replace_str替换searche_str 区分大小写
mysql> select replace(name,"Jack","Tom") from name; +----------------------------+ | 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) from name; +---------------------+ | substring(name,1,3) | +---------------------+ | Jac | | Pau | | Lin | | Rob | +---------------------+ 4 rows in set (0.00 sec) mysql>
Space(count) 生成count个空格
mysql> select space(2) ; +----------+ | space(2) | +----------+ | | +----------+ 1 row in set (0.00 sec) 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) from name; +---------+ | max(id) | +---------+ | 7 | +---------+ 1 row in set (0.00 sec)
MIN(num1,num2) 取最小值
mysql> select min(id) from name; +---------+ | 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> select Curdate(); +------------+ | Curdate() | +------------+ | 2018-09-01 | +------------+ 1 row in set (0.01 sec)
Curtime（） 返回当前时间
mysql> select Curtime(); +-----------+ | Curtime() | +-----------+ | 11:48:13 | +-----------+ 1 row in set (0.00 sec)
Now（） 返回当前日期时间
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2018-09-01 11:48:37 | +---------------------+ 1 row in set (0.00 sec)
Unix_timestamp（date） 返回当前date的时间戳
mysql> select Unix_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)
