cookbook学习笔记一
select * from scott.dept;
#检索所有行
select * from scott.emp;
#检索部分行
select * from scott.emp where deptno=10;
#查找满足条件的行
select * from scott.emp where( deptno=10 or comm is not null or sal<=2000) and deptno=20;
#检索部分列
select ename,deptno,sal from scott.emp;
#为列取有意义的名称
select sal as salary,comm as commission from scott.emp;
#在where子句中引用别名的列
select sal as salary,comm as commission from scott.emp where salary<5000;
select * from (select sal as salary,comm as commission from scott.emp) x where salary<5000;
#连接列值
select ename,job from scott.emp where deptno=10;
select ename || 'WORKS AS A '||job as msg from scott.emp where deptno=10;
#在select中使用条件逻辑
select ename,sal,case
when sal<=2000 then 'UNDRERPAID'
when sal>=400 then 'OVERPAIN'
else 'OK'
end as status
from scott.emp;
#限制返回的行数
select * from scott.emp where rownum<=5;
#从表中随机返回n条记录
select * from ( select ename,job from scott.emp order by dbms_random.value) where rownum<=5;
#查找空值
select * from scott.emp where comm is null;
#将空值转换为实际值
select coalesce(comm,0) from scott.emp;
#按模式搜索
select ename,job,deptno from scott.emp where deptno in(10,20);
select ename,job,deptno from scott.emp where deptno in(10,20) and (ename like '%I%' or job like '%ER');
#查询结果排序
#以指定的次序返回查询结果
select ename,job,sal from scott.emp where deptno=10 order by sal asc;
select ename,job,sal from scott.emp where deptno=10 order by sal desc;
#按多个字段排序
select * from scott.emp;
select empno,deptno,sal,ename,job from scott.emp order by deptno,sal desc;
#按子串排序
select ename,job from scott.emp order by substr(job,length(job)-2);
#对字母数字混合的数据排序(视图)
create view v as select ename||' in '||deptno as data from scott.emp;
select * from v;
#按deptno排序
select data from v order by replace(data,replace(translate(data,'0123456789','##########'),'#',''),'');
#按ename排序
select data from v order by replace(translate(data,'0123456789','##########'),'#','');
#处理排序空值
select ename,sal,comm from scott.emp order by 3;
select ename,sal,comm from scott.emp order by 3 desc;
#根据数据项的键排序
select ename,sal,job,comm from scott.emp order by case
when job='SALESMAN' then comm
else sal end;
select * from scott.emp;
select * from scott.dept;
#记录集的叠加(多个表的行组合在一起)
select ename as ename_and_dname, deptno from scott.emp
where deptno=10
union all
select '-------------------',null
from scott.dept where rownum=1
union all
select dname,deptno
from scott.dept;
#组合相关的行
select e.ename,d.loc
from scott.emp e,scott.dept d
where e.deptno=d.deptno and e.deptno=10;
#两个表中查找共同行
create view v1
as select ename,job,sal from scott.emp
where job='CLERK'
#并没有返回所有列
select * from v1;
#返回所有列
select empno,ename,job,sal,deptno from scott.emp
where (ename,job,sal)
in (select ename,job,sal from scott.emp
intersect select ename,job,sal from v1);
#从一个表中查找另一个表中没有的值
select deptno from scott.dept minus select deptno from scott.emp;
#在一个表中查找与其他表不匹配的记录
select d.* from scott.dept d,scott.emp e
where d.deptno=e.deptno(+)
and e.deptno is not null;
#向查询中增加联接而不影响其他联接
select e.ename,d.loc,b.received from scott.enp e,scott.dept d,scott.bonus b
where e.deptno=d.deptno
and e.empno=b.empno(+)
order by 2;
#检测两个表中是否有相同的数据
create view v2
as select * from scott.emp where deptno!=10
union all
select * from scott.emp where ename='WARD'
select * from v2;
(select empno,ename,job,mgr,hiredate,sal,comm,deptno,count(*) as cnt
from v2
group by empno,ename,job,mgr,hiredate,sal,comm,deptno
minus
select empno,ename,job,mgr,hiredate,sal,comm,deptno,count(*) as cnt
from scott.emp
group by empno,ename,job,mgr,hiredate,sal,comm,deptno)
union all
(select empno,ename,job,mgr,hiredate,sal,comm,deptno,count(*) as cnt
from scott.emp
group by empno,ename,job,mgr,hiredate,sal,comm,deptno
minus
select empno,ename,job,mgr,hiredate,sal,comm,deptno,count(*) as cnt
from v2
group by empno,ename,job,mgr,hiredate,sal,comm,deptno
)
#识别和消除笛卡儿积
select e.ename,d.loc,e.deptno from scott.emp e,scott.dept d
where e.deptno=10;
select e.ename,d.loc,e.deptno from scott.emp e,scott.dept d
where e.deptno=10 and e.deptno=d.deptno;
#新建奖金表
create table scott.emp_bonus(empno varchar(4),
receiver date,
type int);
#聚集和联接
select distinct deptno,total_sal,total_bonus
from (
select e.empno,
e.ename,
sum(distinct e.sal) over
(partition by e.deptno) as total_sal,
e.deptno,
sum(e.sal*case when eb.type=1 then .1
when eb.type=2 then .2
else .3 end) over
(partition by deptno) as total_bonus
from scott.emp e,scott.emp_bonus eb
where e.empno=eb.empno
and e.deptno=10)x
#聚集与外联接
select deptno,
sum(distinct sal) as total_sal,
sum(bonus) as total_bonus
from(
select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal* case when eb.type is null then 0
when eb.type=1 then .1
when eb.type=2 then .2
else .3 end as bonus
from scott.emp e , scott.emp_bonus eb
where e.empno=eb.empno(+)
and e.deptno=10)
group by deptno;
#多从个表中返回丢失的数据
select d.deptno,d.dname,e.ename from scott.dept d,scott.emp e
where d.deptno=e.deptno(+)
union
select d.deptno,d.dname,e.ename from scott.dept d,scott.emp e
where d.deptno(+)=e.deptno
#在运算比较时使用null值
select ename,comm from scott.emp where coalesce(comm,0) < (select comm from scott.emp where ename='WARD');
#检索所有行
select * from scott.emp;
#检索部分行
select * from scott.emp where deptno=10;
#查找满足条件的行
select * from scott.emp where( deptno=10 or comm is not null or sal<=2000) and deptno=20;
#检索部分列
select ename,deptno,sal from scott.emp;
#为列取有意义的名称
select sal as salary,comm as commission from scott.emp;
#在where子句中引用别名的列
select sal as salary,comm as commission from scott.emp where salary<5000;
select * from (select sal as salary,comm as commission from scott.emp) x where salary<5000;
#连接列值
select ename,job from scott.emp where deptno=10;
select ename || 'WORKS AS A '||job as msg from scott.emp where deptno=10;
#在select中使用条件逻辑
select ename,sal,case
when sal<=2000 then 'UNDRERPAID'
when sal>=400 then 'OVERPAIN'
else 'OK'
end as status
from scott.emp;
#限制返回的行数
select * from scott.emp where rownum<=5;
#从表中随机返回n条记录
select * from ( select ename,job from scott.emp order by dbms_random.value) where rownum<=5;
#查找空值
select * from scott.emp where comm is null;
#将空值转换为实际值
select coalesce(comm,0) from scott.emp;
#按模式搜索
select ename,job,deptno from scott.emp where deptno in(10,20);
select ename,job,deptno from scott.emp where deptno in(10,20) and (ename like '%I%' or job like '%ER');
#查询结果排序
#以指定的次序返回查询结果
select ename,job,sal from scott.emp where deptno=10 order by sal asc;
select ename,job,sal from scott.emp where deptno=10 order by sal desc;
#按多个字段排序
select * from scott.emp;
select empno,deptno,sal,ename,job from scott.emp order by deptno,sal desc;
#按子串排序
select ename,job from scott.emp order by substr(job,length(job)-2);
#对字母数字混合的数据排序(视图)
create view v as select ename||' in '||deptno as data from scott.emp;
select * from v;
#按deptno排序
select data from v order by replace(data,replace(translate(data,'0123456789','##########'),'#',''),'');
#按ename排序
select data from v order by replace(translate(data,'0123456789','##########'),'#','');
#处理排序空值
select ename,sal,comm from scott.emp order by 3;
select ename,sal,comm from scott.emp order by 3 desc;
#根据数据项的键排序
select ename,sal,job,comm from scott.emp order by case
when job='SALESMAN' then comm
else sal end;
select * from scott.emp;
select * from scott.dept;
#记录集的叠加(多个表的行组合在一起)
select ename as ename_and_dname, deptno from scott.emp
where deptno=10
union all
select '-------------------',null
from scott.dept where rownum=1
union all
select dname,deptno
from scott.dept;
#组合相关的行
select e.ename,d.loc
from scott.emp e,scott.dept d
where e.deptno=d.deptno and e.deptno=10;
#两个表中查找共同行
create view v1
as select ename,job,sal from scott.emp
where job='CLERK'
#并没有返回所有列
select * from v1;
#返回所有列
select empno,ename,job,sal,deptno from scott.emp
where (ename,job,sal)
in (select ename,job,sal from scott.emp
intersect select ename,job,sal from v1);
#从一个表中查找另一个表中没有的值
select deptno from scott.dept minus select deptno from scott.emp;
#在一个表中查找与其他表不匹配的记录
select d.* from scott.dept d,scott.emp e
where d.deptno=e.deptno(+)
and e.deptno is not null;
#向查询中增加联接而不影响其他联接
select e.ename,d.loc,b.received from scott.enp e,scott.dept d,scott.bonus b
where e.deptno=d.deptno
and e.empno=b.empno(+)
order by 2;
#检测两个表中是否有相同的数据
create view v2
as select * from scott.emp where deptno!=10
union all
select * from scott.emp where ename='WARD'
select * from v2;
(select empno,ename,job,mgr,hiredate,sal,comm,deptno,count(*) as cnt
from v2
group by empno,ename,job,mgr,hiredate,sal,comm,deptno
minus
select empno,ename,job,mgr,hiredate,sal,comm,deptno,count(*) as cnt
from scott.emp
group by empno,ename,job,mgr,hiredate,sal,comm,deptno)
union all
(select empno,ename,job,mgr,hiredate,sal,comm,deptno,count(*) as cnt
from scott.emp
group by empno,ename,job,mgr,hiredate,sal,comm,deptno
minus
select empno,ename,job,mgr,hiredate,sal,comm,deptno,count(*) as cnt
from v2
group by empno,ename,job,mgr,hiredate,sal,comm,deptno
)
#识别和消除笛卡儿积
select e.ename,d.loc,e.deptno from scott.emp e,scott.dept d
where e.deptno=10;
select e.ename,d.loc,e.deptno from scott.emp e,scott.dept d
where e.deptno=10 and e.deptno=d.deptno;
#新建奖金表
create table scott.emp_bonus(empno varchar(4),
receiver date,
type int);
#聚集和联接
select distinct deptno,total_sal,total_bonus
from (
select e.empno,
e.ename,
sum(distinct e.sal) over
(partition by e.deptno) as total_sal,
e.deptno,
sum(e.sal*case when eb.type=1 then .1
when eb.type=2 then .2
else .3 end) over
(partition by deptno) as total_bonus
from scott.emp e,scott.emp_bonus eb
where e.empno=eb.empno
and e.deptno=10)x
#聚集与外联接
select deptno,
sum(distinct sal) as total_sal,
sum(bonus) as total_bonus
from(
select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal* case when eb.type is null then 0
when eb.type=1 then .1
when eb.type=2 then .2
else .3 end as bonus
from scott.emp e , scott.emp_bonus eb
where e.empno=eb.empno(+)
and e.deptno=10)
group by deptno;
#多从个表中返回丢失的数据
select d.deptno,d.dname,e.ename from scott.dept d,scott.emp e
where d.deptno=e.deptno(+)
union
select d.deptno,d.dname,e.ename from scott.dept d,scott.emp e
where d.deptno(+)=e.deptno
#在运算比较时使用null值
select ename,comm from scott.emp where coalesce(comm,0) < (select comm from scott.emp where ename='WARD');
