cover

南大通用数据库-Gbase-8a-学习-29-常用函数介绍

介绍的函数有:HEX,ROUND,CEILING,FLOOR,TRUNCATE,返回当前时间函数,WEEK,WEEKDAY,WEEKOFYEAR,LAST_DAY,DATE_FORMAT,CHARSET,ADD_MONTHS,DATE_ADD,DATE_SUB,DATEDIFF,TIMESTAMPDIFF.

阳光九叶草LXGZXJ

2631人浏览 · 2022-12-30 17:05:09

阳光九叶草LXGZXJ · 2022-12-30 17:05:09 发布

一、测试环境

名称	值
cpu	12th Gen Intel® Core™ i7-12700H
操作系统	CentOS Linux release 7.9.2009 (Core)
内存	3G
逻辑核数	2
Gbase-8a数据库版本	9.5.3.27

二、函数介绍

1、HEX

（1）说明

将数字或字符串转换成十六进制形式。

（2）例子

gbase> SELECT HEX(15);
+---------+
| HEX(15) |
+---------+
| F       |
+---------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT HEX('SUN');
+------------+
| HEX('SUN') |
+------------+
| 53554E     |
+------------+
1 row in set (Elapsed: 00:00:00.00)

2、ROUND

（1）说明

ROUND(参数1,参数2)

参数1：为需要四舍五入的数字。

参数2：为需要保留的位数。

（2）例子

gbase> SELECT ROUND(123);
+------------+
| ROUND(123) |
+------------+
|        123 |
+------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT ROUND(123.123456);
+-------------------+
| ROUND(123.123456) |
+-------------------+
|               123 |
+-------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT ROUND(123.123456,-4);
+----------------------+
| ROUND(123.123456,-4) |
+----------------------+
|                    0 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT ROUND(123.123456,-2);
+----------------------+
| ROUND(123.123456,-2) |
+----------------------+
|                  100 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT ROUND(123.123456,4); 
+---------------------+
| ROUND(123.123456,4) |
+---------------------+
|            123.1235 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

3、CEILING

（1）说明

向上取整。

（2）例子

gbase> SELECT CEILING(123.1);
+----------------+
| CEILING(123.1) |
+----------------+
|            124 |
+----------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT CEILING(123.12);
+-----------------+
| CEILING(123.12) |
+-----------------+
|             124 |
+-----------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT CEILING(123.9); 
+----------------+
| CEILING(123.9) |
+----------------+
|            124 |
+----------------+
1 row in set (Elapsed: 00:00:00.00)

4、FLOOR

（1）说明

向下取整。

（2）例子

gbase> SELECT FLOOR(123);
+------------+
| FLOOR(123) |
+------------+
|        123 |
+------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT FLOOR(123.1);
+--------------+
| FLOOR(123.1) |
+--------------+
|          123 |
+--------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT FLOOR(123.9);
+--------------+
| FLOOR(123.9) |
+--------------+
|          123 |
+--------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT FLOOR(123.92);
+---------------+
| FLOOR(123.92) |
+---------------+
|           123 |
+---------------+
1 row in set (Elapsed: 00:00:00.00)

5、TRUNCATE

（1）说明

截断字符串的作用。

TRUNCATE(X,Y)

X:为需要截断的数字。

Y:为需要截断的位数，如果为正数，截断小数点之后的Y位，如果为负数，截断小数点前的Y位，不四舍五入。

（2）例子

gbase> SELECT TRUNCATE(123);  
+---------------+
| TRUNCATE(123) |
+---------------+
|           123 |
+---------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT TRUNCATE(123.123);
+-------------------+
| TRUNCATE(123.123) |
+-------------------+
|               123 |
+-------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT TRUNCATE(123.123,1);
+---------------------+
| TRUNCATE(123.123,1) |
+---------------------+
|               123.1 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT TRUNCATE(123.123,-1);
+----------------------+
| TRUNCATE(123.123,-1) |
+----------------------+
|                  120 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT TRUNCATE(123.123,2); 
+---------------------+
| TRUNCATE(123.123,2) |
+---------------------+
|              123.12 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

6、返回当前时间函数

（1）说明

NOW(),SYSDATE(),CURRENT_DATE(),CURDATE(),CURRENT_TIME(),CURTIME()都是返回当前时间函数，但返回的时间类型不尽相同。

SYSDATE和NOW区别：
SYSDATE:返回该函数执行时的时间。
NOW:返回的是语句开始执行的时间，在语句执行结束前不会变化。

（2）例子

gbase> select NOW(), SYSDATE(),CURRENT_DATE(), CURDATE(),CURRENT_TIME(),CURTIME() ;
+---------------------+---------------------+----------------+------------+----------------+-----------+
| NOW()               | SYSDATE()           | CURRENT_DATE() | CURDATE()  | CURRENT_TIME() | CURTIME() |
+---------------------+---------------------+----------------+------------+----------------+-----------+
| 2022-12-15 15:58:32 | 2022-12-15 15:58:32 | 2022-12-15     | 2022-12-15 | 15:58:32       | 15:58:32  |
+---------------------+---------------------+----------------+------------+----------------+-----------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT NOW(), SYSDATE(),SLEEP(6),NOW(), SYSDATE();
+---------------------+---------------------+----------+---------------------+---------------------+
| NOW()               | SYSDATE()           | SLEEP(6) | NOW()               | SYSDATE()           |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2022-12-15 15:59:17 | 2022-12-15 15:59:17 |        0 | 2022-12-15 15:59:17 | 2022-12-15 15:59:23 |
+---------------------+---------------------+----------+---------------------+---------------------+
1 row in set (Elapsed: 00:00:06.00)

7、WEEK

（1）说明

返回当前日期是对应年份的第几周。

WEEK(date[,mode])

date：需要转换成周数的日期时间。

mode：0-9

如果不写mode参数，取参数default_week_format的值。
在这里插入图片描述

（2）例子

gbase> SELECT WEEK(NOW());
+-------------+
| WEEK(NOW()) |
+-------------+
|          50 |
+-------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEK('2022-01-01');
+--------------------+
| WEEK('2022-01-01') |
+--------------------+
|                  0 |
+--------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEK('2022-01-01',5);
+----------------------+
| WEEK('2022-01-01',5) |
+----------------------+
|                    0 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEK('2022-01-01',9);
+----------------------+
| WEEK('2022-01-01',9) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEK('2022-01-08',9);
+----------------------+
| WEEK('2022-01-08',9) |
+----------------------+
|                    2 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEK('2022-01-07',9);
+----------------------+
| WEEK('2022-01-07',9) |
+----------------------+
|                    2 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SHOW VARIABLES LIKE '%default_week_format%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| default_week_format | 0     |
+---------------------+-------+
1 row in set (Elapsed: 00:00:00.01)

8、WEEKDAY

（1）说明

得到当前日期是星期几。

WEEKDAY(date)

返回0-6，表示星期一到星期日。

（2）例子

gbase> SELECT WEEKDAY('2022-12-16');
+-----------------------+
| WEEKDAY('2022-12-16') |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEKDAY('2022-12-10');
+-----------------------+
| WEEKDAY('2022-12-10') |
+-----------------------+
|                     5 |
+-----------------------+
1 row in set (Elapsed: 00:00:00.00)

9、WEEKOFYEAR

（1）说明

返回DATE是对应年份的第几周。

WEEKOFYEAR(date)

WEEKOFYEAR(date)等价于 WEEK(date,3)。

（2）例子

gbase> SELECT WEEKOFYEAR('2022-12-16');
+--------------------------+
| WEEKOFYEAR('2022-12-16') |
+--------------------------+
|                       50 |
+--------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEKOFYEAR('2022-01-01');
+--------------------------+
| WEEKOFYEAR('2022-01-01') |
+--------------------------+
|                       52 |
+--------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEKOFYEAR('2022-01-3');
+-------------------------+
| WEEKOFYEAR('2022-01-3') |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (Elapsed: 00:00:00.00)

10、LAST_DAY

（1）说明

查询日期月份最后一天；

错误日期返回NULL；

（2）例子

gbase> SELECT LAST_DAY(NOW());        
+-----------------+
| LAST_DAY(NOW()) |
+-----------------+
| 2022-12-31      |
+-----------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT LAST_DAY('2022-01-01');
+------------------------+
| LAST_DAY('2022-01-01') |
+------------------------+
| 2022-01-31             |
+------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT LAST_DAY('2022-01-32');
+------------------------+
| LAST_DAY('2022-01-32') |
+------------------------+
| NULL                   |
+------------------------+
1 row in set, 1 warning (Elapsed: 00:00:00.00)

gbase> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2022-01-32' |
+---------+------+----------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

11、DATE_FORMAT

（1）说明

将字符串格式化为日期类型。

DATE_FORMAT(date,FORMAT)

在这里插入图片描述

（2）例子

gbase> SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s.%f');                
+--------------------------------------------+
| DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s.%f') |
+--------------------------------------------+
| 2022-12-16 10:56:42.000000                 |
+--------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT NOW(), DATE_FORMAT(NOW(), '%W %M %Y');
+---------------------+--------------------------------+
| NOW()               | DATE_FORMAT(NOW(), '%W %M %Y') |
+---------------------+--------------------------------+
| 2022-12-16 10:52:57 | Friday December 2022           |
+---------------------+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT DATE_FORMAT('2021-03-19 10:38:59', '%H:%i:%s');
+------------------------------------------------+
| DATE_FORMAT('2021-03-19 10:38:59', '%H:%i:%s') |
+------------------------------------------------+
| 10:38:59                                       |
+------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

12、CHARSET

（1）说明

返回数据对应的字符集。

（2）例子

gbase> select * from test1;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (Elapsed: 00:00:00.52)

gbase> SELECT CHARSET(A) FROM TEST1;
+------------+
| CHARSET(A) |
+------------+
| binary     |
| binary     |
+------------+
2 rows in set (Elapsed: 00:00:00.52)

13、ADD_MONTHS

（1）说明

ADD_MONTHS(date,n)
在时间date的基础上添加n个月。

（2）例子

gbase> SELECT ADD_MONTHS(DATE(NOW()),1);          
+---------------------------+
| ADD_MONTHS(DATE(NOW()),1) |
+---------------------------+
| 2023-01-30                |
+---------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT ADD_MONTHS(NOW(),1);       
+---------------------+
| ADD_MONTHS(NOW(),1) |
+---------------------+
| 2023-01-30 16:16:42 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

14、DATE_ADD

（1）说明

DATE_ADD(date,INTERVAL expr type)
向日期添加指定的时间间隔。

type 参数
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

（2）例子

gbase> SELECT DATE_ADD(now(), INTERVAL 1 DAY);
+---------------------------------+
| DATE_ADD(now(), INTERVAL 1 DAY) |
+---------------------------------+
| 2022-12-31 16:23:19             |
+---------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT DATE_ADD(now(), INTERVAL 30 DAY);
+----------------------------------+
| DATE_ADD(now(), INTERVAL 30 DAY) |
+----------------------------------+
| 2023-01-29 16:23:28              |
+----------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT DATE_ADD(now(), INTERVAL -30 DAY);
+-----------------------------------+
| DATE_ADD(now(), INTERVAL -30 DAY) |
+-----------------------------------+
| 2022-11-30 16:23:36               |
+-----------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT DATE_ADD(now(), INTERVAL -1 DAY); 
+----------------------------------+
| DATE_ADD(now(), INTERVAL -1 DAY) |
+----------------------------------+
| 2022-12-29 16:23:48              |
+----------------------------------+
1 row in set (Elapsed: 00:00:00.00)

15、DATE_SUB

（1）说明

DATE_SUB(date,INTERVAL expr type)
向日期减去指定的时间间隔。

type 参数
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

（2）例子

gbase> SELECT DATE_SUB(now(), INTERVAL 1 DAY); 
+---------------------------------+
| DATE_SUB(now(), INTERVAL 1 DAY) |
+---------------------------------+
| 2022-12-29 16:26:43             |
+---------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT DATE_SUB(now(), INTERVAL -1 DAY);
+----------------------------------+
| DATE_SUB(now(), INTERVAL -1 DAY) |
+----------------------------------+
| 2022-12-31 16:26:47              |
+----------------------------------+
1 row in set (Elapsed: 00:00:00.00)

16、DATEDIFF

（1）说明

DATEDIFF(起始时间，结束时间)

返回起始时间减去结束时间的差值。

（2）例子

gbase> SELECT DATEDIFF(NOW(),NOW());
+-----------------------+
| DATEDIFF(NOW(),NOW()) |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT NOW()+1;
+---------------------+
| NOW()+1             |
+---------------------+
| 2022-12-31 16:35:55 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT DATEDIFF(NOW(),NOW()+1);
+-------------------------+
| DATEDIFF(NOW(),NOW()+1) |
+-------------------------+
|                      -1 |
+-------------------------+
1 row in set (Elapsed: 00:00:00.00)

17、TIMESTAMPDIFF

（1）说明

TIMESTAMPDIFF(TYPE, START_TIME,END_TIME);
返回END_TIME减去START_TIME的差值。

序号	type参数
1	YEAR
2	MONTH
3	DAY
4	WEEK
5	QUARTER

（2）例子

gbase> SELECT TIMESTAMPDIFF(MONTH,now(), now()+31);
+--------------------------------------+
| TIMESTAMPDIFF(MONTH,now(), now()+31) |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT TIMESTAMPDIFF(DAY,now(), now()+31);  
+------------------------------------+
| TIMESTAMPDIFF(DAY,now(), now()+31) |
+------------------------------------+
|                                 31 |
+------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT TIMESTAMPDIFF(QUARTER,now(), now()+100);
+-----------------------------------------+
| TIMESTAMPDIFF(QUARTER,now(), now()+100) |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

Springboot+MySQL 实现从数据库获取数据展示到前端

1.运行效果效果图如下：2.创建数据库表数据库名为"springboot",数据库表名为"user"，数据库表里面放了5条数据，字段分别为：“id”，“name”，“age”，“email”。3.新建SpringBoot项目1、选择Spring Initial...

腾讯云开发者社区

cover

腾讯云建官网：一个完整的自助建站的过程，不用代码，2小时就可以制作自己的网站

腾讯云开发者社区

数据库实验二数据库的简单查询和连接查询实验

– l）用Transact-SQL语句表示下列操作．在学生选课库中实现其数据查询操作．– ①求数学系学生的学号和姓名。select sno,sname from student where sdept='MA';– ②求选修了课程的学生学号。select distinct sno from sc;– ③求选修C1课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。s

腾讯云开发者社区

所有评论(0)

查看更多评论

阳光九叶草LXGZXJ

已为社区贡献7条内容