当前位置：首页 > news >正文

[数据库]内置函数

news 2025/6/26 2:39:56

●🧑个人主页:你帅你先说.
●📃欢迎点赞👍关注💡收藏💖
●📖既选择了远方，便只顾风雨兼程。
●🤟欢迎大家有问题随时私信我！
●🧐版权：本文由[你帅你先说.]原创，CSDN首发，侵权必究。

📌📌📌为您导航📌📌📌

1.日期函数
- 1.1 current_xxx()
- 1.2 date_xxx()
- 1.3留言表
2.字符串函数
3.数学函数
4.其它函数

1.日期函数

1.1 current_xxx()

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2023-03-07     |
+----------------+
1 row in set (0.00 sec)mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 23:24:17       |
+----------------+
1 row in set (0.00 sec)mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2023-03-07 23:24:27 |
+---------------------+
1 row in set (0.00 sec)

1.2 date_xxx()

mysql> select date_add('1949-10-1',interval -10 day);
+----------------------------------------+
| date_add('1949-10-1',interval -10 day) |
+----------------------------------------+
| 1949-09-21                             |
+----------------------------------------+
1 row in set (0.00 sec)mysql> select date_sub('1949-10-1',interval 10 day);
+---------------------------------------+
| date_sub('1949-10-1',interval 10 day) |
+---------------------------------------+
| 1949-09-21                            |
+---------------------------------------+
1 row in set (0.00 sec)mysql> select datediff('1949-10-1','2022-08-12');
+------------------------------------+
| datediff('1949-10-1','2022-08-12') |
+------------------------------------+
|                             -26613 |
+------------------------------------+
1 row in set (0.00 sec)

1.3留言表

mysql> create table msg (
-> id int primary key auto_increment,
-> content varchar(30) not null,
-> sendtime datetime
-> );
--插入操作省略
>select * from msg;
+----+---------+---------------------+
| id | content | sendtime |
+----+---------+---------------------+
| 1 | hello1 | 2023-03-08 14:12:20 |
| 2 | hello2 | 2023-03-08 14:13:21 |
+----+---------+---------------------+

显示所有留言信息，发布日期只显示日期，不用显示时间

select content,date(sendtime) from msg;

请查询在2分钟内发布的帖子

select * from msg where date_add(sendtime, interval 2 minute) > now();

2.字符串函数

--假设有一张成绩表sg,分别有姓名，语文，数学，英语属性列
--获取emp表的ename列的字符集
select charset(name) from sg;--要求显示student表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”
select concat(name, '的语文是',chinese,'分，数学是',math,'分') as '分数' from student;--求学生表中学生姓名占用的字节数
select length(name), name from student;--将表中名字有S的替换成A
select replace(name, 'S', 'A') ,name from student;--截取表中name字段的第二个到第三个字符
select substring(name, 2, 2), name from student;--以首字母小写的方式显示所有员工的姓名
select concat(lcase(substring(name, 1, 1)),substring(name,2)) from student;--从'abcdef'从查找'cde',返回对应的下标(从1开始)
select instr('abcdef','cde');

3.数学函数

--绝对值函数
mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)
--转为二进制
mysql> select bin(20);
+---------+
| bin(20) |
+---------+
| 10100   |
+---------+
1 row in set (0.00 sec)
--转为十进制
mysql> select hex(20);
+---------+
| hex(20) |
+---------+
| 14      |
+---------+
1 row in set (0.00 sec)
--从n进制转为m进制
mysql> select conv(20,10,8);
+---------------+
| conv(20,10,8) |
+---------------+
| 24            |
+---------------+
1 row in set (0.00 sec)
--向上取整
mysql> select ceiling(3.9);
+--------------+
| ceiling(3.9) |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)
--向下取整
mysql> select floor(3.8);
+------------+
| floor(3.8) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)
--保留n位小数
mysql> select format(3.1415926535,3);
+------------------------+
| format(3.1415926535,3) |
+------------------------+
| 3.142                  |
+------------------------+
1 row in set (0.00 sec)
--产生[0.0,1.0)范围的小数
mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.6639702633619031 |
+--------------------+
1 row in set (0.00 sec)
--取模
mysql> select mod(10,8);
+-----------+
| mod(10,8) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

4.其它函数

--查询用户
mysql> select user();
+---------------+
| user()        |
+---------------+
| ljt@localhost |
+---------------+
1 row in set (0.00 sec)--database()显示当前正在使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)--password()函数，MySQL数据库使用该函数对用户加密
mysql> select password('root');
+-------------------------------------------+
| password('root')                          |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+
1 row in set (0.00 sec)--ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值
mysql> select ifnull(null,'af');
+-------------------+
| ifnull(null,'af') |
+-------------------+
| af                |
+-------------------+
1 row in set (0.00 sec)mysql> select ifnull('af',null);
+-------------------+
| ifnull('af',null) |
+-------------------+
| af                |
+-------------------+
1 row in set (0.00 sec)mysql> select ifnull('af','ae');
+-------------------+
| ifnull('af','ae') |
+-------------------+
| af                |
+-------------------+
1 row in set (0.00 sec)mysql> select ifnull(null,null);
+-------------------+
| ifnull(null,null) |
+-------------------+
|              NULL |
+-------------------+
1 row in set (0.00 sec)