Oracle 查询、更新基本操作

oracle 默认隔离等级是：读已提交。

查询锁定，防止另外用户更新：
select * from books for update;
当前用户更新之后，另外用户可以更改。


01、表连接
假定from子句中从左到右两个表分别为A，B表。

内连接：选取A、B表的完全匹配的集合，两表交集：
select empno,ename,emp.deptno A,dept.deptno B,dname from emp inner join dept on emp.deptno=dept.deptno;

左外连：选取A表全部以及A、B表交集
select dname,dept.deptno A,emp.deptno B,empno,ename from dept left join emp on dept.deptno=emp.deptno;

右外连：选取B表全部以及A、B表交集
select empno,ename,emp.deptno A,dept.deptno B,dname from emp right join dept on emp.deptno=dept.deptno;

02、表联合
整合结果集并消除重复行：
select empno,ename from emp
union
select deptno,dname from dept;

03、空值
select empno,ename,nvl(comm,0) from emp;      --正确，comm与0类型相同
select empno,ename,nvl(comm,'空值') from emp; --错误，comm与'空值'类型不同
select empno,ename,comm from emp where comm is null;
select empno,ename,comm from emp where comm is not null;

04、排序
select * from emp order by empno,asc;
select * from dept order by deptno desc;

05、消除重复数据
select distinct deptno from emp;

06、模糊查询
select ename from emp where ename like '%K';
_表示一个字符，%表示一个或多个字符

07、分组
select deptno, avg(sal) from emp group by deptno;       --分组求出平均薪水
select deptno, avg(sal) from emp group by deptno, job;  --组合分组
select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;
group by 后面的字段，可以不出现在select后面；
select 后面的字段，如果没有出现在聚集函数里，就必须出现在group by后面。
group by 分组过滤要使用having。

08、聚集函数
select ename, max(sal) from emp;
这条语句错误：max只能有一个值，只能选取一行，但是等于max值的记录可能有好几个，无法正确匹配，所以应该使用子查询：
select ename from emp where sal = (select max(sal) from emp);

聚集函数不能做为条件出现在where子句的后边：
select deptno,sum(sal) from emp where sum(sal)>3000 group by deptno;
这条语句是错误的。要记住：聚集函数做条件，一定要想到having子句。
having经常与group by联用，一般建议group by在having的前面：
select deptno,sum(sal) from emp group by deptno having sum(sal) >9000;

 

09、子查询
无关子查询：子查询中使用的条件与外部父查询没有关系。
select empno,ename,mgr,deptno from emp where exists (select deptno from dept where deptno>10);
判断子查询是否有结果集，如果存在，就显示员工表所有数据，否则不显示。

相关子查询：子查询中使用的条件与外部父查询有紧密的关系。
select empno,ename,mgr,deptno from emp where deptno in (select deptno from dept where deptno>20);

select empno,ename,deptno from emp where not exists (select * from dept where deptno<emp.deptno);
判断子查询是否有结果集，如果不存在，就显示员工表所有数据，否则不显示。

IN：子查询中不能在select中使用*。
EXISTS：子查询可以在select中使用*。
如果查询既能用表连接实现，又能用子查询实现，就使用表连接。

子查询就是在select语句中套select语句，把子查询的结果当成一张表：
select ename, sal from emp where sal > (select avg(sal) from emp);

10、修改数据
根据指定表创建新表(oracle专用)
create table t_dump as select empno,ename from emp where empno>7900;
每次插入多条记录(oracle专用)
insert into t_dump(empno,ename) select deptno,dname from dept where deptno<30;
用SMITH的信息更新SCOTT的信息
update emp set(job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';

 

11、插入多行
create table abc(a varchar2(10),b varchar2(10),c varchar2(10));
insert into abc
select 'a1','b1','c1' from dual
union all
select 'a2','b2','c2' from dual
union all
select 'a3','b3','c3' from dual;

12、数据表备份
create table dept2 as select * from dept;

13、简单业务查询
入职超过300天的员工
select rownum,empno,ename,hiredate from emp where sysdate>add_months(hiredate,300);
员工的入职天数
select empno,ename,trunc(sysdate-hiredate) 入职天数 from emp;
在月底前一天入职的员工
select empno,ename,hiredate,last_day(hiredate) "入职月底日期" from emp where last_day(hiredate)-2=hiredate;
别名不能使用单引号。

 

set transaction readonly;
只读设置后，在该点之后更新的数据不会进入读取范围，不会统计到。