password ifnull 【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 daydate_sub(date,interval d_value_type)在date中减去日期或时间，interval后的数值单位可以是year minute second daydatediff(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中出现的位置，没有返回0ucase(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_strstrcmp(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)

参考阅读

 您阅读本篇文章共花了：