Oracle_限制和排序语句

in

sal in (890,980) <=> sal =890 or sal = 980,顺序也是相关的;

sal not in (1,2)  <=> sal !=1 and sal !=2;

not sal in (1,2) <=> 逻辑取反,先计算in,然后再取反;

空值

is null/is not null

模糊查询

like '%S';

like '__A%',第3个字符为A;

like  '%/_%' escape '/',/后第一个字符是转义的字符;

逻辑运算

and 与

or 或

not 非

顺序 not and or;

where id = 1 or id =2 and sal > 1500

<=>where or id = 1 or id =2  (and sal > 1500)

注意:

字符串区分大小写,

日期查询注意日期格式 

alter session set nls_date_format = 'yyyy-mm-dd'; 

排序

null是最大值

select sal+nvl(comm,0) tsal from emp order by tsal;

<=>select sal+nvl(comm,0) from emp order by 1;

定义变量 define/undefine 

仅当前当次登录用户有效

define e = 7902;

select ename , sal from emp where empno = &e;

临时变量 &

select ename , sal from emp where empno = &e;

Enter value for e :7900

变量调变量 &&,

先写&&e,只需要输入一次,执行顺序右到左

select &&e, ename , sal from emp where &e = 7900;

Enter value for e :empno

多个变量顺序:右到左,上到下

声明变量

var v1 number;

exec :v1 :=7900;

select ename , sal from emp where empno = :v1;

查看新旧值 set verify off