『SQL』内置函数

最新推荐文章于 2023-04-22 19:08:04 发布

爱喝可乐的炸鸡

最新推荐文章于 2023-04-22 19:08:04 发布

阅读量468

点赞数 1

分类专栏：『SQL』文章标签：内置函数 SQL

本文链接：https://blog.csdn.net/sss_0916/article/details/85252791

版权

『SQL』专栏收录该内容

8 篇文章 0 订阅

订阅专栏

日期函数

函数名称	描述
current_date()	当前日期
current_time()	当前时间
current_timestamp()	当前时间戳
date(datetime)	返回datetime参数的日期部分
date_add(date, interval d_value_type)	在date中添加日期或时间，interval后的数值单位可以是：year minuer second day
date_sub(date, interval d_value_type)	在date中减去日期或时间，interval后的数值单位可以是：year minute second day
datedif(date1, date2)	两个日期的差，单位是天
now()	当前日期时间

current_date()		当前日期
MariaDB [prictice]> select current_date();
+----------------+
| current_date() |
+----------------+
| 2018-12-25     |
+----------------+

current_time()		当前时间
MariaDB [prictice]> select current_time();
+----------------+
| current_time() |
+----------------+
| 19:05:42       |
+----------------+

current_timestamp()		当前时间戳
MariaDB [prictice]> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2018-12-25 19:06:55 |
+---------------------+

date(datetime()		返回datetime参数中的日期部分
MariaDB [prictice]> select datetime from example;
+---------------------+
| datetime            |
+---------------------+
| 2018-12-25 19:11:09 |
+---------------------+

MariaDB [prictice]> select date(datetime) from example;
+----------------+
| date(datetime) |
+----------------+
| 2018-12-25     |
+----------------+

date_add(date, interval d_value_type)		日期的基础上加日期
MariaDB [prictice]> select date_add('2018-12-25', interval 100 day);
+------------------------------------------+
| date_add('2018-12-25', interval 100 day) |
+------------------------------------------+
| 2019-04-04                               |
+------------------------------------------+

date_sub(date, interval d_value_type)		日期的基础上减去日期
MariaDB [prictice]> select date_sub('2018-12-25', interval 100 day);
+------------------------------------------+
| date_sub('2018-12-25', interval 100 day) |
+------------------------------------------+
| 2018-09-16                               |
+------------------------------------------+
注: 减出来刚好是我生日, 嘻嘻(#^.^#)

datediff(date1, date2)		两个日期的差
MariaDB [prictice]> select datediff('2018-12-25', '2018-9-16');
+-------------------------------------+
| datediff('2018-12-25', '2018-9-16') |
+-------------------------------------+
|                                 100 |
+-------------------------------------+

now()		当前日期时间
MariaDB [prictice]> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-12-25 19:24:23 |
+---------------------+

字符串函数

charset(str)	返回字符串字符集
concat(string2 [, ...])	连接字符串
instr(string, substring)	返回substring在string中出现的位置
ucase(string2)	转换成大写
lcase(string2)	转换成小写
left(string2, length)	从string2中的左边起取length个字符
length(string)	string的长度
replace(str, search_str, replace_str)	在str中用replace_str替换search_str
strcmp(string1, string2)	逐字符比较两个字符串的大小
substring(str, position [, length])	从str的position开始，取length个字符
ltrim(string) rtrim(string) trim(string)	去除前空格或后空格

charset(str)		查看字符串字符集
MariaDB [prictice]> select charset(name) from student;
+---------------+
| charset(name) |
+---------------+
| utf8          |
| utf8          |
| utf8          |
| utf8          |
| utf8          |
| utf8          |
+---------------+

concat(string1, string2...)		连接字符串
MariaDB [prictice]> select concat(name, '的语文成绩为: ', chinese, '分!')
    -> as chinese
    -> from student;
+-------------------------------------+
| chinese                             |
+-------------------------------------+
| 大明的语文成绩为: 100.0分!             |
| 小红的语文成绩为: 99.5分!              |
| 小华的语文成绩为: 72.5分!              |
| 小芳的语文成绩为: 92.0分!              |
| 小白的语文成绩为: 100.0分!             |
| 小哈的语文成绩为: 100.0分!             |
+-------------------------------------+

instr(string, substring)		返回substring在string中的位置, 没有返回0
MariaDB [prictice]> select instr('xixihelloworldxixi', 'helloworld');
+-------------------------------------------+
| instr('xixihelloworldxixi', 'helloworld') |
+-------------------------------------------+
|                                         5 |
+-------------------------------------------+

ucase(string)		转换成大写
MariaDB [prictice]> select ucase('xixi');
+---------------+
| ucase('xixi') |
+---------------+
| XIXI          |
+---------------+

lcase(string)		转换成小写
MariaDB [prictice]> select lcase('XIXI');
+---------------+
| lcase('XIXI') |
+---------------+
| xixi          |
+---------------+

left(string, length)		从string左起取length个字符
MariaDB [prictice]> select left('xixisss_0916', 4);
+-------------------------+
| left('xixisss_0916', 4) |
+-------------------------+
| xixi                    |
+-------------------------+

length(string)		string的长度
MariaDB [prictice]> select length('xixi');
+----------------+
| length('xixi') |
+----------------+
|              4 |
+----------------+

replace(str, search_str, replace_str)		str中用replace_str替换search_str
MariaDB [prictice]> select replace('hahahelloworldhaha', 'haha', '(#^.^#)');
+--------------------------------------------------+
| replace('hahahelloworldhaha', 'haha', '(#^.^#)') |
+--------------------------------------------------+
| (#^.^#)helloworld(#^.^#)                         |
+--------------------------------------------------+

strcmp(string1, string2)		逐字符比较两字符串的大小
MariaDB [prictice]> select strcmp('xixi', 'xixi');
+------------------------+
| strcmp('xixi', 'xixi') |
+------------------------+
|                      0 |
+------------------------+
MariaDB [prictice]> select strcmp('xixi', 'haha');
+------------------------+
| strcmp('xixi', 'haha') |
+------------------------+
|                      1 |
+------------------------+
MariaDB [prictice]> select strcmp('haha', 'xixi');
+------------------------+
| strcmp('haha', 'xixi') |
+------------------------+
|                     -1 |
+------------------------+

substring(str, position, length)		从position开始取length个字符
MariaDB [prictice]> select substring('sssxixi', 4, 4);
+----------------------------+
| substring('sssxixi', 4, 4) |
+----------------------------+
| xixi                       |
+----------------------------+

ltrim(string)		去掉前空格
rtrim(string)		去掉后空格
trim(string)		去掉前后空格
MariaDB [prictice]> select ltrim('    xixi    ') as ltrim;
+----------+
| ltrim    |
+----------+
| xixi     |
+----------+
MariaDB [prictice]> select rtrim('    xixi    ') as rtrim;
+----------+
| rtrim    |
+----------+
|     xixi |
+----------+
MariaDB [prictice]> select trim('    xixi    ') as trim;
+------+
| trim |
+------+
| xixi |
+------+

数学函数

函数名称	描述
abs(number)	绝对值函数
bin(decimal_number)	十进制转二进制
hex(decimal_number)	十进制转十六进制
conv(number, from_base, to_base)	进制转换
ceiling(number)	向上去整
floor(number)	向下去整
format(number, decimal_places)	格式化，保留小数位数
rand()	返回随机浮点数，范围[0.0, 1.0)
mod(number, denominator)	取模，求余

abs(number)		求绝对值
MariaDB [prictice]> select abs(-3);
+---------+
| abs(-3) |
+---------+
|       3 |
+---------+

bin(decimal_number)		十进制转二进制
MariaDB [prictice]> select bin(11);
+---------+
| bin(11) |
+---------+
| 1011    |
+---------+

hex(decimal_number)		十进制转十六进制
MariaDB [prictice]> select hex(1021);
+-----------+
| hex(1021) |
+-----------+
| 3FD       |
+-----------+

conv(number, from_base, to_base)		进制转换
MariaDB [prictice]> select conv(11, 10, 2);
+-----------------+
| conv(11, 10, 2) |
+-----------------+
| 1011            |
+-----------------+

ceiling(number)		向上取整
MariaDB [prictice]> select ceiling(10.001);
+-----------------+
| ceiling(10.001) |
+-----------------+
|              11 |
+-----------------+

floor(number)		向下取整
MariaDB [prictice]> select floor(11.99);
+--------------+
| floor(11.99) |
+--------------+
|           11 |
+--------------+

format(number, decimal_places)		格式化输出, 保留小数位数
MariaDB [prictice]> select format(11.11234, 2);
+---------------------+
| format(11.11234, 2) |
+---------------------+
| 11.11               |
+---------------------+

rand()		返回随机数, 范围[0.0, 1.0]
MariaDB [prictice]> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.6307388568583306 |
+--------------------+

mod(number, denominator)		取模, 求余
MariaDB [prictice]> select mod(11, 5);
+------------+
| mod(11, 5) |
+------------+
|          1 |
+------------+

聚合函数

count(expr)		返回查询到的数据的数量
MariaDB [prictice]> select count(name) from student;
+-------------+
| count(name) |
+-------------+
|           6 |
+-------------+
注: 使用count(*)不受null的影响; count(name), name中有为null的, 不计入总数

sum(expr)		返回查询到的数据的总和, 不是数字无意义
MariaDB [prictice]> select sum(chinese) from student;
+--------------+
| sum(chinese) |
+--------------+
|        564.0 |
+--------------+

avg(expr)		返回查询到的数据的平均值, 不是数字无意义
MariaDB [prictice]> select avg(chinese) from student;
+--------------+
| avg(chinese) |
+--------------+
|     94.00000 |
+--------------+

max(expr)		返回查询到的数据的最大值, 不是数字无意义
MariaDB [prictice]> select max(chinese) from student;
+--------------+
| max(chinese) |
+--------------+
|        100.0 |
+--------------+

min(expr)		返回查询到的数据的最小值, 不是数字无意义
MariaDB [prictice]> select min(chinese) from student;
+--------------+
| min(chinese) |
+--------------+
|         72.5 |
+--------------+

其他函数

user()		查询当前用户
MariaDB [prictice]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

md5(str)		MD5消息摘要算法
MariaDB [prictice]> select md5('xixi');
+----------------------------------+
| md5('xixi')                      |
+----------------------------------+
| de156e39c8481df78050021b1ffcd425 |
+----------------------------------+

database()		显示当前正在使用的数据库
MariaDB [prictice]> select database();
+------------+
| database() |
+------------+
| prictice   |
+------------+

password()		加密函数
MariaDB [prictice]> select password('xixi');
+-------------------------------------------+
| password('xixi')                          |
+-------------------------------------------+
| *F13AA038C6D4B48040F842128FE6CEA90CEE3A9E |
+-------------------------------------------+

ifnull(val1, val2)		如果val1为null, 返回val2, 否则返回val1
MariaDB [prictice]> select ifnull(null, 3);
+-----------------+
| ifnull(null, 3) |
+-----------------+
|               3 |
+-----------------+
MariaDB [prictice]> select ifnull(1, 3);
+--------------+
| ifnull(1, 3) |
+--------------+
|            1 |
+--------------+

nullif(val1, val2)		如果val1 = val2, 返回null, 否则返回val1
MariaDB [prictice]> select nullif(1, 1);
+--------------+
| nullif(1, 1) |
+--------------+
|         NULL |
+--------------+
MariaDB [prictice]> select nullif(1, 2);
+--------------+
| nullif(1, 2) |
+--------------+
|            1 |
+--------------+