朋友们、伙计们，我们又见面了，本期来给大家带来数据库内置函数知识点，如果看完之后对你有一定的启发，那么请留下你的三连，祝大家心想事成！
C 语 言 专 栏：C语言：从入门到精通
数据结构专栏：数据结构
个 人 主 页 ：stackY、
C + + 专 栏 ：C++
Linux 专 栏 ：Linux
目录
1. 日期函数
示例：
① 获得年月日、时分秒、时间戳
-- 注意：这些内置函数都要搭配select来使用 -- 获取年月日 mysql> select current_date(); +----------------+ | current_date() | +----------------+ | 2024-11-21 | +----------------+ 1 row in set (0.00 sec) -- 获取时分秒 mysql> select current_time; +--------------+ | current_time | +--------------+ | 09:24:48 | +--------------+ 1 row in set (0.00 sec) -- 获取时间戳 mysql> select current_timestamp; +---------------------+ | current_timestamp | +---------------------+ | 2024-11-21 09:25:12 | +---------------------+ 1 row in set (0.00 sec) -- 获取当前日期时间 mysql> select now(); +---------------------+ | now() | +---------------------+ | 2024-11-21 10:10:19 | +---------------------+ 1 row in set (0.00 sec)
② 日期/时间的加减
-- 日期 + 天数 mysql> select date_add('1949-10-1',interval 20 day); +---------------------------------------+ | date_add('1949-10-1',interval 20 day) | +---------------------------------------+ | 1949-10-21 | +---------------------------------------+ 1 row in set (0.00 sec) -- 日期 + 年数 mysql> select date_add('1949-10-1',interval 95 year); +----------------------------------------+ | date_add('1949-10-1',interval 95 year) | +----------------------------------------+ | 2044-10-01 | +----------------------------------------+ 1 row in set (0.00 sec) -- 日期 - 分钟数 mysql> select date_sub(now(), interval 20 minute); +-------------------------------------+ | date_sub(now(), interval 20 minute) | +-------------------------------------+ | 2024-11-21 09:46:33 | +-------------------------------------+ 1 row in set (0.00 sec)
③ 计算两个日期之间相差多少天
-- date1 - date2 mysql> select datediff('2024-11-21', '1949-10-1'); +-------------------------------------+ | datediff('2024-11-21', '1949-10-1') | +-------------------------------------+ | 27445 | +-------------------------------------+ 1 row in set (0.00 sec)
案例：
创建一个留言表，包含留言条目数、留言、留言时间；
-- 创建表 mysql> create table msg( -> id int primary key auto_increment, -> content varchar(100) not null, -> sendtime datetime -> ); Query OK, 0 rows affected (0.03 sec) -- 查看表结构 mysql> desc msg; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | content | varchar(100) | NO | | NULL | | | sendtime | datetime | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) -- 插入数据 mysql> insert into msg(content, sendtime) values('博主的文章真不错',now()); Query OK, 1 row affected (0.00 sec) mysql> insert into msg(content, sendtime) values('三连+关注',now()); Query OK, 1 row affected (0.00 sec) mysql> select * from msg; +----+--------------------------+---------------------+ | id | content | sendtime | +----+--------------------------+---------------------+ | 1 | 博主的文章真不错 | 2024-11-21 10:19:09 | | 2 | 三连+关注 | 2024-11-21 10:21:29 | +----+--------------------------+---------------------+ 2 rows in set (0.00 sec)① 显示所有留言信息，发布日期只显示日期，不用显示时间
mysql> select content,date(sendtime) from msg; +--------------------------+----------------+ | content | date(sendtime) | +--------------------------+----------------+ | 博主的文章真不错 | 2024-11-21 | | 三连+关注 | 2024-11-21 | +--------------------------+----------------+ 2 rows in set (0.00 sec)② 请查询在2分钟内发布的帖子
-- 2分钟之内发布的，就是现在的时间now()减去发送帖子的时间sendtime()小于2即可 mysql> select * from msg where date_add(sendtime,interval 2 minute) > now(); Empty set (0.00 sec) mysql> insert into msg(content, sendtime) values('你关注我了吗？',now()); Query OK, 1 row affected (0.00 sec) mysql> select * from msg where date_add(sendtime,interval 2 minute) > now(); +----+-----------------------+---------------------+ | id | content | sendtime | +----+-----------------------+---------------------+ | 3 | 你关注我了吗？ | 2024-11-21 10:31:23 | +----+-----------------------+---------------------+ 1 row in set (0.00 sec)
2. 字符串函数
示例：继续使用上一章节的雇员表信息
-- 数据展示 mysql> select * from emp; +--------+--------+-----------+------+---------------------+---------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +--------+--------+-----------+------+---------------------+---------+---------+--------+ | 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | | 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | | 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | | 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | +--------+--------+-----------+------+---------------------+---------+---------+--------+ 14 rows in set (0.00 sec)① 获取emp表中ename列的编码格式
mysql> select charset(ename) from emp; +----------------+ | charset(ename) | +----------------+ | utf8 | | utf8 | | utf8 | | utf8 | | utf8 | | utf8 | | utf8 | | utf8 | | utf8 | | utf8 | | utf8 | | utf8 | | utf8 | | utf8 | +----------------+ 14 rows in set (0.00 sec)
② 要求显示exam_result表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”
-- 查看原始数据 mysql> select * from exam_result; +----+-----------+---------+------+---------+ | id | name | chinese | math | english | +----+-----------+---------+------+---------+ | 1 | 唐三藏 | 134 | 98 | 56 | | 3 | 猪悟能 | 176 | 98 | 90 | | 4 | 曹孟德 | 140 | 90 | 67 | | 5 | 刘玄德 | 110 | 115 | 45 | | 6 | 孙权 | 140 | 73 | 78 | | 7 | 宋公明 | 150 | 95 | 30 | +----+-----------+---------+------+---------+ 6 rows in set (0.00 sec) -- 进行格式化拼接 mysql> select concat(name, '的语文是',chinese,'分，数学是',math,'分') as '分数' from exam_result; +-----------------------------------------------+ | 分数 | +-----------------------------------------------+ | 唐三藏的语文是134分，数学是98分 | | 猪悟能的语文是176分，数学是98分 | | 曹孟德的语文是140分，数学是90分 | | 刘玄德的语文是110分，数学是115分 | | 孙权的语文是140分，数学是73分 | | 宋公明的语文是150分，数学是95分 | +-----------------------------------------------+ 6 rows in set (0.00 sec)
③ 将emp表中所有名字中有S的替换成 *
mysql> select ename, replace(ename,'S','*') from emp; +--------+------------------------+ | ename | replace(ename,'S','*') | +--------+------------------------+ | SMITH | *MITH | | ALLEN | ALLEN | | WARD | WARD | | JONES | JONE* | | MARTIN | MARTIN | | BLAKE | BLAKE | | CLARK | CLARK | | SCOTT | *COTT | | KING | KING | | TURNER | TURNER | | ADAMS | ADAM* | | JAMES | JAME* | | FORD | FORD | | MILLER | MILLER | +--------+------------------------+ 14 rows in set (0.00 sec)
④ 截取emp表中ename字段的第二个到第三个字符
-- mysql里面字符串的下标从1开始的 mysql> select ename, substring(ename,2,2) from emp; +--------+----------------------+ | ename | substring(ename,2,2) | +--------+----------------------+ | SMITH | MI | | ALLEN | LL | | WARD | AR | | JONES | ON | | MARTIN | AR | | BLAKE | LA | | CLARK | LA | | SCOTT | CO | | KING | IN | | TURNER | UR | | ADAMS | DA | | JAMES | AM | | FORD | OR | | MILLER | IL | +--------+----------------------+ 14 rows in set (0.00 sec)
⑤ 以首字母小写的方式显示所有员工的姓名
-- 通过截取字符串转化小写并拼接实现 mysql> select ename,concat(lcase(substring(ename,1,1)),substring(ename,2)) from emp; +--------+--------------------------------------------------------+ | ename | concat(lcase(substring(ename,1,1)),substring(ename,2)) | +--------+--------------------------------------------------------+ | SMITH | sMITH | | ALLEN | aLLEN | | WARD | wARD | | JONES | jONES | | MARTIN | mARTIN | | BLAKE | bLAKE | | CLARK | cLARK | | SCOTT | sCOTT | | KING | kING | | TURNER | tURNER | | ADAMS | aDAMS | | JAMES | jAMES | | FORD | fORD | | MILLER | mILLER | +--------+--------------------------------------------------------+ 14 rows in set (0.00 sec)
3. 数学函数
示例：
① 进制转化
-- 十进制转化为二进制 mysql> select bin(10); +---------+ | bin(10) | +---------+ | 1010 | +---------+ 1 row in set (0.00 sec) -- 十进制转化为十六进制 mysql> select hex(16); +---------+ | hex(16) | +---------+ | 10 | +---------+ 1 row in set (0.00 sec) -- 指定的进制转化 -- 将十进制的12转化为二进制 mysql> select conv(12,10,2); +---------------+ | conv(12,10,2) | +---------------+ | 1100 | +---------------+ 1 row in set (0.00 sec)
② 取整转化
-- 向上取整 mysql> select ceiling(4.1); +--------------+ | ceiling(4.1) | +--------------+ | 5 | +--------------+ 1 row in set (0.00 sec) mysql> select ceiling(-4.9); +---------------+ | ceiling(-4.9) | +---------------+ | -4 | +---------------+ 1 row in set (0.00 sec) -- 向下取整 mysql> select floor(4.9); +------------+ | floor(4.9) | +------------+ | 4 | +------------+ 1 row in set (0.00 sec) mysql> select floor(-4.1); +-------------+ | floor(-4.1) | +-------------+ | -5 | +-------------+ 1 row in set (0.00 sec) -- 保留2位小数（四舍五入） mysql> select format(12.3456,2); +-------------------+ | format(12.3456,2) | +-------------------+ | 12.35 | +-------------------+ 1 row in set (0.00 sec)
③ 随机数、取模
-- 随机数 mysql> select rand(); +-------------------+ | rand() | +-------------------+ | 0.807517722069768 | +-------------------+ 1 row in set (0.00 sec) -- 通过取整获得1000以内的随机数 mysql> select ceiling(rand()*1000); +----------------------+ | ceiling(rand()*1000) | +----------------------+ | 268 | +----------------------+ 1 row in set (0.00 sec)-- 取模、求余 mysql> select mod(10,3); +-----------+ | mod(10,3) | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec)
4. 其他函数
① 查询当前用户
select user();② 对密码加密
mysql> select md5('hello!'); +----------------------------------+ | md5('hello!') | +----------------------------------+ | 5a8dd3ad0756a93ded72b823b19dd877 | +----------------------------------+ 1 row in set (0.00 sec) -- 主要用于对数据库中输入密码的保存③ 对用户加密
mysql> select password('root'); +-------------------------------------------+ | password('root') | +-------------------------------------------+ | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | +-------------------------------------------+ 1 row in set, 1 warning (0.00 sec)④ ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值
mysql> select ifnull('abc', '123'); +----------------------+ | ifnull('abc', '123') | +----------------------+ | abc | +----------------------+ 1 row in set (0.00 sec) mysql> select ifnull(null, '123'); +---------------------+ | ifnull(null, '123') | +---------------------+ | 123 | +---------------------+ 1 row in set (0.00 sec)
朋友们、伙计们，美好的时光总是短暂的，我们本期的的分享就到此结束，欲知后事如何，请听下回分解~，最后看完别忘了留下你们弥足珍贵的三连喔，感谢大家的支持！