【MySQL】MySQL内置函数--日期函数/字符串函数/数学函数/其他相关函数

文章目录

• • 1.日期函数
  • 2.字符串函数
  • 3.数学函数
  • 4.其它���数

    1.日期函数

    MySQL中内置了一下函数：

    （）

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

    获得年月日

    mysql> select current_date();+----------------+| current_date() |+----------------+| 2023-11-11     |+----------------+1 row in set (0.00 sec)

    获得时分秒

    （）

    mysql> select current_time();+----------------+| current_time() |+----------------+| 15:46:47       |+----------------+1 row in set (0.00 sec)

    获得时间戳

    mysql> select current_timestamp();+---------------------+| current_timestamp() |+---------------------+| 2023-11-11 15:47:19 |+---------------------+1 row in set (0.00 sec)

    在日期的基础上加日期

    mysql> select date_add('2023-11-11',interval 10 day);+----------------------------------------+| date_add('2023-11-11',interval 10 day) |+----------------------------------------+| 2023-11-21                             |+----------------------------------------+1 row in set (0.01 sec)

    在日期的基础上减去时间

    mysql> select date_sub('2023-11-11',interval 10 day);+----------------------------------------+| date_sub('2023-11-11',interval 10 day) |+----------------------------------------+| 2023-11-01                             |+----------------------------------------+1 row in set (0.00 sec)

    计算两个日期之间相差多少天

    mysql> select datediff('2023-11-11','2023-10-21');+-------------------------------------+| datediff('2023-11-11','2023-10-21') |+-------------------------------------+|                                  21 |+-------------------------------------+1 row in set (0.01 sec)

    创建一个留言表

    mysql> create table messsage(    -> id int primary key auto_increment,    -> content varchar(32) not null,    -> sendtime datetime    -> );Query OK, 0 rows affected (0.03 sec)

    插入数据

    mysql> insert into message(content,sendtime) values('hello world',now());mysql> insert into message(content,sendtime) values('hello world',now());mysql> select * from message;+----+-------------+---------------------+| id | content     | sendtime            |+----+-------------+---------------------+|  3 | hello world | 2023-11-11 16:03:38 ||  4 | hello world | 2023-11-11 16:03:48 |+----+-------------+---------------------+2 rows in set (0.00 sec)

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

    mysql> select * from message where date_add(sendtime ,interval 2 minute) > now();+----+-------------+---------------------+| id | content     | sendtime            |+----+-------------+---------------------+|  3 | hello world | 2023-11-11 16:03:38 ||  4 | hello world | 2023-11-11 16:03:48 |+----+-------------+---------------------+2 rows in set (0.00 sec)------------------------------|-----------|-------------|------------------                              初始时间     now()       初始时间+2min

    2.字符串函数

    函数名称	描述
    charset(str)	返回字符串字符集
    concat(string2 [,…])	连接字符串
    instr(string,substring)	返回substring在string中出现的位置，没有返回0
    ucase(string2)	转换成大写
    lcase(string2)	转换成小写
    left(string2,length)	从string2中的左边取length个字符
    right(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的postion开始，取length个字符
    ltrim(string) rtrim(string) trim(string)	去除前空格或后空格

    获取emp表的ename列的字符集

    select charset(ename) from emp;

    要求显示exam_result表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”

    select concat(name, '的语文是',chinese,'分，数学是',math,'分') as '分数' from student;

    求学生表中学生姓名占用的字节数

    select length(name), name from student;

    注意：length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数；如果是单字节字符则算作一个字节。比如：字母，数字算作一个字节，中文表示多个字节数（与字符集编码有关）

    将emp表中所有名字中有S的替换成’上海’

    select replace(ename, 'S', '上海') ,ename from emp;

    截取emp表中ename字段的第二个到第三个字符

    select substring(ename, 2, 2), ename from emp;

    以首字母小写的方式显示所有员工的姓名

    select concat(lcase(substring(ename,1,1)),substring(ename,2)) from emp;

    3.数学函数

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

    绝对值

    select abs(-100.2);mysql> select abs(-100.2);+-------------+| abs(-100.2) |+-------------+|       100.2 |+-------------+1 row in set (0.00 sec)

    向上取整

    mysql> select ceiling(3.1);+--------------+| ceiling(3.1) |+--------------+|            4 |+--------------+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 ceiling(-3.1);+---------------+| ceiling(-3.1) |+---------------+|            -3 |+---------------+1 row in set (0.00 sec)mysql> select ceiling(-3.9);+---------------+| ceiling(-3.9) |+---------------+|            -3 |+---------------+1 row in set (0.00 sec

    向下取整

    mysql> select floor(3.1);+------------+| floor(3.1) |+------------+|          3 |+------------+1 row in set (0.00 sec)mysql> select floor(3.9);+------------+| floor(3.9) |+------------+|          3 |+------------+1 row in set (0.00 sec)mysql> select floor(-3.1);+-------------+| floor(-3.1) |+-------------+|          -4 |+-------------+1 row in set (0.00 sec)mysql> select floor(-3.9);+-------------+| floor(-3.9) |+-------------+|          -4 |+-------------+1 row in set (0.00 sec)

    保留2位小数位数（小数四舍五入)

    mysql> select format(12.345,2);+------------------+| format(12.345,2) |+------------------+| 12.35            |+------------------+1 row in set (0.00 sec)mysql> select format(99.99,2);+-----------------+| format(99.99,2) |+-----------------+| 99.99           |+-----------------+1 row in set (0.00 sec)mysql> select format(99.999,2);+------------------+| format(99.999,2) |+------------------+| 100.00           |+------------------+1 row in set (0.00 sec)mysql> select format(-12.223,2);+-------------------+| format(-12.223,2) |+-------------------+| -12.22            |+-------------------+1 row in set (0.00 sec)mysql> select format(-12.227,2);+-------------------+| format(-12.227,2) |+-------------------+| -12.23            |+-------------------+1 row in set (0.00 sec)

    产生随机数

    mysql> select rand();+--------------------+| rand()             |+--------------------+| 0.5963675860281732 |+--------------------+1 row in set (0.00 sec)mysql> select rand()*10;+--------------------+| rand()*10          |+--------------------+| 3.1565381243420187 |+--------------------+1 row in set (0.00 sec)

    4.其它函数

    • user() 查询当前用户

      select user();mysql> select user();+----------------+| user()         |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)

      • database()显示当前正在使用的数据库

        select database();mysql> select database();+------------+| database() |+------------+| scott      |+------------+1 row in set (0.00 sec)

        • md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串

          select md5('admin');mysql> select md5('admin');+----------------------------------+| md5('admin')                     |+----------------------------------+| 21232f297a57a5a743894a0e4a801fc3 |+----------------------------------+1 row in set (0.00 sec)

          • password()函数，MySQL数据库使用该函数对用户加密

            select password('root');mysql> select password('root');+-------------------------------------------+| password('root')                          |+-------------------------------------------+| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |+-------------------------------------------+1 row in set, 1 warning (0.01 sec)

            • ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值

              mysql> select ifnull('abc',null);+--------------------+| ifnull('abc',null) |+--------------------+| abc                |+--------------------+1 row in set (0.00 sec)mysql> select ifnull(null, '123');+---------------------+| ifnull(null, '123') |+---------------------+| 123                 |+---------------------+1 row in set (0.00 sec)mysql> select ifnull('abc', '123');+----------------------+| ifnull('abc', '123') |+----------------------+| abc                  |+----------------------+1 row in set (0.00 sec)