column_flags 字段取值如下 值 含义 1 NOT_NULL_FLAG,Field can't be NULL 2 PRI_KEY_FLAG,Field is part of a primary key 4 UNIQUE_KEY_FLAG,Field is part of a unique key 8 MULTIPLE_KEY_FLAG,Field is part of a key 16 BLOB_FLAG,Field is a blob 32 UNSIGNED_FLAG,Field is unsigned 64 ZEROFILL_FLAG,Field is zerofill 128 BINARY_FLAG,Field is binary 256 ENUM_FLAG,field is an enum 512 AUTO_INCREMENT_FLAG,field is a autoincrement field 1024 TIMESTAMP_FLAG,Field is a timestamp 2048 SET_FLAG,field is a set 4096 NO_DEFAULT_VALUE_FLAG,Field doesn't have default value 8192 ON_UPDATE_NOW_FLAG,Field is set to NOW on UPDATE 32768 NUM_FLAG,Field is num (for clients) 16384 PART_KEY_FLAG,Intern; Part of some key
GBase 8c V5 开发者手册 南大通用数据技术股份有限公司 39
可以在查询的 Projection 子句或 WHERE 子句中使用时间函数 DAY 、MONTH 、 WEEKDAY 和 YEAR 。这些函数返回与用来调用函数的表达式或参数对应的值。还可以使 用 CURRENT 或 SYSDATE 函数返回具有当前日期和时间的值, 或者使用 EXTEND 函数调 整 DATE 或 DATETIME 值。
DAY 和 CURRENT 函数 下列查询在两个 expression 列中对 call_dtime 和 res_dtime 列返回日期(一个月中的某一 天)。 图: 查询 SELECT customer_num, DAY (call_dtime), DAY (res_dtime) FROM cust_calls; 图: 查询结果 customer_num (expression) (expression)
106 12 12 110 7 7 119 1 2 121 10 10 127 31 116 28 28 116 21 27 下列查询使用 DAY 和 CURRENT 函数来将列值与当前日期(月中某日)进行比较。它只 选择值比当前日期早的那些行。在此示例中,CURRENT 日是 15。 图: 查询 SELECT customer_num, DAY (call_dtime), DAY (res_dtime) FROM cust_calls WHERE DAY (call_dtime) < DAY (CURRENT); 图: 查询结果 customer_num (expression) (expression) 106 12 12 110 7 7
GBase 8s SQL 指南:教程 南大通用数据技术股份有限公司 - 88 -
119 1 2 121 10 10 下列查询使用 CURRENT 函数来选择除今天打的电话之外的所有来电。 图: 查询 SELECT customer_num, call_code, call_descr FROM cust_calls WHERE call_dtime < CURRENT YEAR TO DAY; 图: 查询结果 customer_num 106 call_code D call_descr Order was received, but two of the cans of ANZ tennis balls within the case were empty
customer_num 110 call_code L call_descr Order placed one month ago (6/7) not received. ⋮ customer_num 116 call_code I call_descr Second complaint from this customer! Received two cases right-handed outfielder gloves (1 HRO) instead of one case lefties. SYSDATE 函数与 CURRENT 函数及其类似,但当未指定 DATETIME 限定符时,其返回 值的缺省精度是 DATETIME YEAR TO FRACTION(5),而不是 CURRENT 的缺省精度 DATETIME YEAR TO FRACTION(3)。
YEAR 函数 下列查询检索 call_dtime 比当前年份的开始早的行。 图: 程序 SELECT customer_num, call_code, YEAR (call_dtime) call_year, YEAR (res_dtime) res_year FROM cust_calls WHERE YEAR (call_dtime) < YEAR (TODAY); 图: 查询结果 customer_num call_code call_year res_year
116 I 1997 1997 116 I 1997 1997
GBase 8s SQL 指南:教程 南大通用数据技术股份有限公司 - 91 -
格式化 DATETIME 值 在下列查询中,EXTEND 函数仅显示指定的子字段以限制两个 DATETIME 值。 图: 程序 SELECT customer_num, EXTEND (call_dtime, month to minute) call_time, EXTEND (res_dtime, month to minute) res_time FROM cust_calls ORDER BY res_time; 该查询为标签为 call_time 和 res_time 的列返回月份至分钟范围,提供工作量的指示。 图: 查询结果 customer_num call_time res_time