一、substring函數
此函數可以用在HQL語句的columns部分和where後面,主要目的可以對數據庫字段中的某數據進行按條件截取,並動態組合成自已想要的數據格式,具體使用方法如:
example: oldSid:812108M00003 -> newSid:08-M00003
hql = "select substring(oldSid,5, 2)+'-'+substring(oldSid,7,6) as newSid from Table";
substring()函數一般帶有三個參數,第一個為要格式化的源字段,第二個為開始執行的起始位置,(下標從臺開始),第三個參數為從開始位置起要截取幾位數據,所有上面語句的結果為:
newSid:08-M00003
另substring函數也可以用在where語句後面如:
hql = "from Table a where substring(a.description, 1, 3) = 'cat'";
二、子查詢的用法
假設現在有兩張表Company(公司表),Employee(員工表),關係一個公司有多個員工,所以一個company記錄應該對應多條employee紀錄.即在employee有一個 FK 指向Company的 PK.
condition:找出每一個公司中員工工資(pay)最高的那一位.
hql:
select (select max(e.pay) from c.Employee e) from Company c
note:select後面的子查詢必須寫在()中,
其運行後對應的sql語句為:
select
(select
max(e.pay)
from
Employee e
where
e.HSUID=c.HSUID) as col_0_0_
from
Company c;
hql中的子查詢也可以帶查詢條件如下面也可以寫成:
select (select max(e.pay) from a.employee e where e.name=:name) from company a
select
(select
max(e.pay)
from
Employee e
where
e.HSUID=c.HSUID and e.name = '01') as col_0_0_
from
Company c;
三、coalesce函数的用法
COALESCE
返回其参数中第一个非空表达式。
语法
COALESCE (expr
参数
expr
任何类型的表达式。
n
表示可以指定多个表达式的占位符。所有表达式必须是相同类型,或者可以隐性转换为相同的类型。
返回类型
将相同的值作为 expr
注释
如果所有自变量均为 NULL,则 COALESCE 返回 NULL 值。
COALESCE(expr
CASE
WHEN (expr
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
示例:表score代表某同学本次考试各科成绩的得分.结构如下:
語文 數學 英語
58 NULL 95
95 NULL 0
NULL NULL 34
NULL NULL NULL
0 95 NULL
0 0 0
NULL 95 95
95 NULL NULL
hql:
select a from score a where coalesce(a.chinese,a.math,a.english) = 95;
執行後結果為:
95 NULL 0
NULL 95 95
95 NULL NULL
(3 rows affected)
第一行因為a.chinese(語文)不空,所以where a.chinese = 95;return false;
第三行因為a.english = 34,所以return false;
第五行因為a.chinese = 0;標誌也不為空,所以return false;
四. ISNULL 函数和 NULLIF 函数
SQL Server里的 ISNULL 与 ASP 中的 IsNull不同,SQL Server 中有两个参数,语法:
ISNULL(check_expr
- check_expr
ession 与 replacement_value 数据类型必须一致 - 如果 check_expr
ession 为 NULL,则返回 replacement_value - 如果 check_expr
ession 不为 NULL,则返回 check_expr ession
NULLIF 用于检查两个表达式,语法:
NULLIF(expr
- 如果两个 expr
ession 相等,则返回 NULL,该 NULL 为第一个 expr ession 的数据类型 - 如果两个 expr
ession 不相等,则返回第一个 expr ession
示例程式:
函数表score代表某学生某此考試的數學和中文成績.數據如下:
中文 數學
25 36
15 59
68 68
85 45
96 95
現要查詢某同學的兩門成績,要求分數不相等,如相等,則要求中文成績要超過85分以上.
select a from Score a where nullif(a.chinese,a.math) >= 85;
返回結果:
85 45
最後一條雖然第門功課成績都是well,但由於a.chinese == a.math,所以函數nullif會返回NULL,
五. 于日期有关的数据函数(EXTRACT)
EXTRACT
ANSI标准中EXTRACT函数允许选取日期和时间中任意段或任意间隔的值,Teradata中EXTRACT函数支持日期数据中选取年.月.日.从时间数据中选取小时.分钟和秒,同时Hibernate也支持相应的函数运算.
示例:
SELECT DATE; 96/11/07
SELECT EXTRACT(YEAR FROM DATE); 1996
SELECT EXTRACT(MONTH FROM DATE+30); 12
SLEECT EXTRACT(DAY FROM DATE+2); 09
SELECT TIME; 14:52:32
SELECT EXTRACT(HOUR FROM TIME); 14
SELECT EXTRACT(SECOND FROM TIME+30); INVALID TIME
hql:
select extract(second from a.orderTime) from ApOrderRequest a;
六.LOCATE函数的用法
locate函数返回第一个string在第二个string中的起始位置,如果第一个string或第二个string为空,则结果反回空(NULL),如果第一个字串中第二个中没有找见,则返回零(0),语法介绍:
LOCATE( string1, string2 [, start] )
string1:
任何字串表达式,此字串作为一个字串对象去字串2中进行批配.
string2:
任何字串表达式,此字串包括第一个字串.
start:
从字串string2中搜寻string1是的起始位置,假如没有设置,或为NULL,或所赋值小于壹,则统一从1开始搜寻.Examples:
DE<SELECT LOCATE( 'Peter', 'Peter Steve Tom' ) DE<
七.CAST函数的用法
CAST 函数 [数据类型转换]功能:
返回转换为提供的数据类型的表达式的值。
语法:
CAST(expression AS da ta type)
参数:
expression 要转换的表达式
data type 目标数据类型
用法:
如果未指定字符串类型的长度,数据库服务器将选择适当的长度。如果没有为十进制转换指定精度和小数位数,则数据库服务器将选择适当的值。示例
下面的函数确保字符串被用作日期:
SELECT CAST( '2000-10-31' AS DATE )
计算表达式DE<1 + 2 DE< 的值,并将结果转换为单字符字符串。
SELECT CAST( 1 + 2 AS CHAR )
可以使用 CAST 函数缩短字符串:
SELECT CAST( 'Surname' AS CHAR(5) )