IT评测·应用市场-qidao123.com技术社区
标题:
【MySQL】MySQL内置函数--日期函数/字符串函数/数学函数/其他相干函数
[打印本页]
作者:
花瓣小跑
时间:
2024-7-20 21:02
标题:
【MySQL】MySQL内置函数--日期函数/字符串函数/数学函数/其他相干函数
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)
复制代码
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。
欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/)
Powered by Discuz! X3.4