Oracle基础之单表查询

平时用惯了PostgreSQL和MS SQL Server，因目前要做数据库兼容，因此主流Oracle便成了首个要克服的坎，以下是初用Oracle时掉入的一些坑，借此记录一下：

1. where条件中不允许直接拿类似 ** = '2020-3-17 17:18:25' ，Oracle里要用日期赋值或进行比较时，必须要用 to_date函数：

to_date('2020-03-17 10:40:10.345', 'yyyy-MM-dd HH24:MI:ss.ff')

2. where条件中不允许出现 字段名 not in ('')，这在PostgreSQL和MS SQL Server里完全没问题，但如果你用在Oracle中，则查不出数据，也不报错。。

另外判断字段值是否为空时也不能直接用 字段名=''，也是查不出数据，判断为空时可用以下方式：

字符串名 is null or 字符串名 = ' '

判断非空时用以下方式：

字段名 is not null and 字段名 <> ' '

另外Null不支持加、减、乘、除、大小比较、相等比较 ，所以对空值进行操作时需要把空值改为有意义的值，可以使用NVL，如 nvl(字段名, 0) ，但NVL只能处理单位参数，如果要处理多个参数，可以使用COALESCE，比如要C1~C6各列不为空的值，如下：

SELECT nvl(nvl(nvl(nvl(nvl(c1,c2),c3),c4),c5),c6) AS c FROMtable1table1;
 
SELECT COALESCE(C1,C2,C3,C4,C5,C6) AS c FROMtable1;

另外不同的函数对空值的处理方式也不一样，有些会返回空值：

select greatest(1,null) from dual;
 
--结果：
GREATEST(1,NULL)
------------------
NULL

有些会返回期望的结果：

select replace('abcde', 'a', null) as str from dual;
 
--结果：
STR
-----
bcde

而在DECOE中还可以比较空值：

select deptno,ename,comm,decode(comm, null, 0) from scott.emp where deptno=20;
 
--结果：
DEPTNO ENAME   COMM  DECODE(COMM,NULL,0)
------ -----   ----- -------------------
  20   SMITH           0
  20   JONES           0

因此遇到Null时最好测试一下结果会受什么影响，不要仅凭相像。

3. 使用rownum来限制返回行数

select * from emp where rownum<=2

rownum是伪列，会依次对返回的第一条数据做一个标识，不能直接用rownum=2来取数据。