Question

Oracle数据库sql语句模糊查询问题

用户表是scott用户下的emp表:
问题时这样的:查询在1981年入职的所有员工
我用模糊查询做的
select * from emp where hiredate like '%81%';
但是如果我改成
select * from emp where hiredate like '%981%';或者
select * from emp where hiredate like '1981%';或者
select * from emp where hiredate like '_981%';
就差不出任何记录
为什么呢？

SQL> select * from emp where hiredate like '%981%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------

SQL> select * from emp where hiredate like '%81%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20

10 rows selected

Answer 1

因为日期存储在数据库的样子跟我们看到日期的样子并不一样。你可以在网上查查Oracle日期类型存储格式，便可知其原因了。以下是我简单的捞了一下重点，说明了一下日期类型在数据库中的存储原理。

日期类型长度是7，7个字节分别表示世纪、年、月、日、时、分和秒。
　　由于不会出现0的情况，月和日都是按照原值存储的，月的范围是1～12，日的范围是1～31。
　　由于时、分、秒都会出现0的情况，因此存储时采用原值加1的方式。0时保存为1，13时保存为14，23时保存为24。分和秒的情况与小时类似。小时的范围是0～23，在数据库中以1～24保存。分和秒的范围都是0～59，在数据库中以1～60保存。
　　年和世纪的情况相对比较复杂，可分为公元前和公元后两种情况。由于最小的世纪的值是-47（公元前4712年），最大值是99（公元9999年）。为了避免负数的产生，oracle把世纪加100保存在数据库中。公元2000年，世纪保存为120，公元9999年，世纪保存为199，公元前101年，世纪保存为99（100+(-1)），公元前4712年，世纪保存为53（100+(-47)）。
　　注意，对于公元前1年，虽然已经是公元前了，但是表示世纪的前两位的值仍然是0，因此，这时的保存的世纪的值仍然是100。世纪的范围是-47～99，保存的值是53～199。
　　年的保存与世纪的保存方式类似，也把年的值加上100进行保存。对于公元2000年，年保持为100，公元1年保存为101，公元2004年保存为104，公元9999年保存为199，公元前1年，保存为99（100+(-1)），公元前101年，保存为99（100+(-1)），公元前4712年保存为88（100+(-12)）。对于公元前的年，保存的值总是小于等于100，对于公元后的年，保存的值总是大于等于100。年的范围是0～99，保存的值是1～199。

其实你那样查并不准确，建议改成：select * from emp where to_char(hiredate,'yyyy')='1982'

如果还有疑问，可以Hi我。

Answer 2

可以通过to_date方式统一时间样式，之后通过做差的形式来进行值获取时间差，。
sql：select
to_date(to_char(sysdate,'yyyy-mm-dd')||'
23:59:59','yyyy-mm-dd
hh24:mi:ss')-
to_date(to_char(sysdate,'yyyy-mm-dd')||'
22:22:22','yyyy-mm-dd
hh24:mi:ss')
from
dual;
备注：时间比较是距离1970年越远的那么时间就越大。

Answer 3

Oracle的日期类型进行like，还真没有用过。

用楼上的to_char()，转成年份，直接匹配

Answer 4

你查询的是日期字段 , 如果想查寻 可以转换成 varchar2来使用like 查询 如'%81-1%'

Answer 5

用 to_char(hiredate,'yyyy')='1981'