4.Oracle_4

1. where 条件

1.1 not ( is not null, not in , not exists )

1.2 Null值判断(不可以=)

• is null
• is not null

-- not  非  
-- 非空判断 is not null   || is null
select *
from emp
where comm is not null; --  null不可以=的
select *
from emp
where comm is null;

1.3 字符拼接

• ||
• concat(str1, str2);

select job || '@' || ename, concat(concat(job, '@'),ename)
from emp;

1.4 between … and … 区间【】左闭，右闭区间

-- 入职时间 81年的
select *
from emp
where hiredate > to_date('1980-12-31', 'yyyy-mm-dd')
 and hiredate < to_date('1982-1-1', 'yyyy-mm-dd');
-- between .. and .. 【,】 左闭右闭区间
select *
from emp
where hiredate between  to_date('1981-1-1', 'yyyy-mm-dd') and to_date('1981-12-31', 'yyyy-mm-dd')

1.5 like:

• _任意一个字符

• %任意个任意字符

  -- _ 任意一个字符 %任意个任意字符
  -- ename_M开头的人
  select *
  from emp
  where ename like 'M%';
  -- ename 第二个字母是A的
  select *
  from emp
  where ename like '_A%';
  
  

1.6 in ()； 在数组范围

-- in 在氛围
where deptno = 20 or deptno = 30
select * from emp
where deptno in (20, 30);
-- 20号的经理和30号的salesman
select *
from emp
where deptno = 20 and job = 'MANAGER' or deptno = 30 and job = 'SALESMAN'
-------------------------------------
select *
from emp
where (deptno, job) not in ((20, 'MANAGER'), (30, 'SALESMAN'))

select * from emp
where deptno in (20, 30);

1.7 exists； 存在； where子查询中存在数据就是true

• 理论来说 exists比 in快 （哪个表数据多作为主表）

select *
from emp t1
where exists (select 1 from emp t2 where t1.deptno = t2.deptno and (t2.deptno = 20 or t2.deptno = 30))
-- 
delete from emp t1 where not exists (select 1 from dept t2 where t1.deptno = t2.deptno);
rollback;
delete from emp t1 where deptno not in (select deptno from dept t2 where t1.deptno = t2.deptno);

2 上下关系的集合

union: 并集去重

union all: 并集不去重

intersect: 交集

minus: 差集

select ename, job, deptno
from emp
where deptno =20
union
select ename, job, deptno
from emp
where job = 'MANAGER'

select ename, job, deptno
from emp
where deptno =20
union all
select ename, job, deptno
from emp
where job = 'MANAGER';

select ename, job, deptno
from emp
where deptno =20
intersect
select ename, job, deptno
from emp
where job = 'MANAGER';

select ename, job, deptno
from emp
where deptno =20
minus
select ename, job, deptno
from emp
where job = 'MANAGER';

ANAGER’;
select ename, job, deptno
from emp
where deptno =20
minus
select ename, job, deptno
from emp
where job = ‘MANAGER’;
``