【MySQL】内置函数详解

• 日期函数
• 字符串函数
• 数学函数
• 加密函数与其它

日期函数

--获取当前日期mysql> select current_date();+----------------+| current_date() |+----------------+| 2023-07-09     |+----------------+--获取当前时间mysql> select current_time();+----------------+| current_time() |+----------------+| 09:42:21       |+----------------+--获取当前时间戳mysql> select current_timestamp();+---------------------+| current_timestamp() |+---------------------+| 2023-07-09 09:42:27 |+---------------------+--获取当前日期时间mysql> select now();+---------------------+| now()               |+---------------------+| 2023-07-09 09:43:07 |+---------------------+--从日期时间中提取日期mysql> select date(now());+-------------+| date(now()) |+-------------+| 2023-07-09  |+-------------+--在当前日期基础上增加10天mysql> select date_add(now(),interval 10 day);+---------------------------------+| date_add(now(),interval 10 day) |+---------------------------------+| 2023-07-19 09:52:56             |+---------------------------------+--在当前日期基础上减少10天mysql> select date_sub(now(),interval 10 day);+---------------------------------+| date_sub(now(),interval 10 day) |+---------------------------------+| 2023-06-29 09:53:04             |+---------------------------------+--获取时间差mysql> select datediff(date(now()),'2002-05-01');+------------------------------------+| datediff(date(now()),'2002-05-01') |+------------------------------------+|                               7739 |+------------------------------------+

综合案列

--创建msg表mysql> create table msg (id int primary key auto_increment,content varchar(30) not null,sendtime datetime);--插入两条数据mysql> insert into msg(content,sendtime) values('天街小雨润如酥', now());mysql> insert into msg(content,sendtime) values('草色遥看近却无', now());--显示所有留言信息，发布日期只显示日期，不用显示时间mysql> select * from msg;+----+-----------------------+---------------------+| id | content               | sendtime            |+----+-----------------------+---------------------+|  1 | 天街小雨润如酥        | 2023-07-09 10:20:25 ||  2 | 草色遥看近却无        | 2023-07-09 10:20:36 |+----+-----------------------+---------------------+--查询在10分钟内发布的帖子mysql> select * from msg where date_add(sendtime,interval 10  minute)>now();+----+-----------------------+---------------------+| id | content               | sendtime            |+----+-----------------------+---------------------+|  1 | 天街小雨润如酥        | 2023-07-09 10:20:25 ||  2 | 草色遥看近却无        | 2023-07-09 10:20:36 |+----+-----------------------+---------------------+mysql> select * from msg where date_sub(now(),interval 10 minute)<sendtime;+----+-----------------------+---------------------+| id | content               | sendtime            |+----+-----------------------+---------------------+|  1 | 天街小雨润如酥        | 2023-07-09 10:20:25 ||  2 | 草色遥看近却无        | 2023-07-09 10:20:36 |+----+-----------------------+---------------------+

字符串函数

1. 获取msg表的content列的字符集。 2. 拼接显示stu表中的信息。 3. 求学生表中学生姓名占用的字节数。长度函数返回字符串长度，以字节为单位。如果是多字节字符，则计算多个字节数；如果是单字节字符，则算作一个字节。例如，字母、数字算作一个字节，中文表示多个字节数（与字符集编码有关，utf-8是三个字节）。 4. 将stu表中所有数学成绩中有8的替换成'6'。 5. 截取stu表中name字段的第二个到第三个字符。 6. 以首字母小写的方式显示所有员工的姓名。

mysql> select charset(content) from msg;+------------------+| charset(content) |+------------------+| utf8             || utf8             |+------------------+mysql> select concat('姓名：',name,'总分:',chinese+math+english,'语文',chinese,'数学',math,'英语',english) msg from stu;+--------------------------------------------------------+| msg                                                    |+--------------------------------------------------------+| 姓名：唐三藏总分:288语文134数学98英语56                || 姓名：猪悟能总分:364语文176数学98英语90                || 姓名：曹孟德总分:297语文140数学90英语67                || 姓名：刘玄德总分:270语文110数学115英语45               || 姓名：孙权总分:291语文140数学73英语78                  || 姓名：宋公明总分:275语文150数学95英语30                |+--------------------------------------------------------+mysql> select name,length(name) from stu;+-----------+--------------+| name      | length(name) |+-----------+--------------+| 唐三藏    |            9 || 猪悟能    |            9 || 曹孟德    |            9 || 刘玄德    |            9 || 孙权      |            6 || 宋公明    |            9 |+-----------+--------------+mysql> select math,replace(math,'8','6') from stu;+------+-----------------------+| math | replace(math,'8','6') |+------+-----------------------+|   98 | 96                    ||   98 | 96                    ||   90 | 90                    ||  115 | 115                   ||   73 | 73                    ||   95 | 95                    |+------+-----------------------+6 rows in set (0.00 sec)--截取stu表中name字段的第二个到第三个字符mysql> select name,substring(name,2,2) from stu;+-----------+---------------------+| name      | substring(name,2,2) |+-----------+---------------------+| 唐三藏    | 三藏                || 猪悟能    | 悟能                || 曹孟德    | 孟德                || 刘玄德    | 玄德                || 孙权      | 权                  || 宋公明    | 公明                |+-----------+---------------------+--以首字母小写的方式显示所有员工的姓名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                                                 |+--------+--------------------------------------------------------+

数学函数

--取绝对值mysql> select abs(10.2);+-----------+| abs(10.2) |+-----------+|      10.2 |+-----------+--取绝对值mysql> select abs(-10.5);+------------+| abs(-10.5) |+------------+|       10.5 |+------------+--转为二进制mysql> select bin(5);+--------+| bin(5) |+--------+| 101    |+--------+--对小数转二进制会自动取整mysql> select bin(5.2);+----------+| bin(5.2) |+----------+| 101      |+----------+--转化为16进制mysql> select hex(10);+---------+| hex(10) |+---------+| A       |+---------+--将十进制16转为2进制mysql> select conv(16,10,2);+---------------+| conv(16,10,2) |+---------------+| 10000         |+---------------+--向上取整mysql> select ceiling(5.2);+--------------+| ceiling(5.2) |+--------------+|            6 |+--------------+--向上取整mysql> select ceiling(-5.2);+---------------+| ceiling(-5.2) |+---------------+|            -5 |+---------------+----向下取整mysql> select floor(-5.2);+-------------+| floor(-5.2) |+-------------+|          -6 |+-------------+----向下取整mysql> select floor(5.2);+------------+| floor(5.2) |+------------+|          5 |+------------+--保留2位小数位数（小数四舍五入)mysql> select format(12.3569,2)    -> ;+-------------------+| format(12.3569,2) |+-------------------+| 12.36             |+-------------------+--生成[0.0,10.0)之间的随机数mysql> select rand()*10;+-------------------+| rand()*10         |+-------------------+| 3.120866942332002 |+-------------------+mysql> select format(rand()*10,2);+---------------------+| format(rand()*10,2) |+---------------------+| 8.73                |+---------------------+--取模mysql> select mod(5,2);+----------+| mod(5,2) |+----------+|        1 |+----------+

加密函数与其它

user() 查询当前用户
md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串
对于一个加密后的数据，想要查询它，也需要配套使用

insert into user (name,password) values ('wmh',md5('123'));select * from user where password=md5('123');

mysql> select user();+----------------+| user()         |+----------------+| root@localhost |+----------------+mysql> select md5('root');+----------------------------------+| md5('root')                      |+----------------------------------+| 63a9f0ea7bb98050796b649e85481845 |+----------------------------------+--database()显示当前正在使用的数据库mysql> select database();+------------+| database() |+------------+| scott      |+------------+--password()函数，MySQL数据库使用该函数对用户加密mysql> select password('root');+-------------------------------------------+| password('root')                          |+-------------------------------------------+| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |+-------------------------------------------+--ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值mysql> select ifnull(null,10);+-----------------+| ifnull(null,10) |+-----------------+|              10 |+-----------------+mysql> select ifnull(20,null);+-----------------+| ifnull(20,null) |+-----------------+|              20 |+-----------------+

解题思路: length(string) 统计出所有字符串的长度；减去没有逗号的长度，就是逗号的长度，这里需要用 '‘替换’,'了

select id,length(string)-length(replace(string,',','')) from strings;

标签： 函数 字节 总分 姓名