精通Oracle10编程SQL(4)使用SQL语句

--工资级别表
create table SALGRADE
(
  GRADE    NUMBER(10),
  LOSAL    NUMBER(10,2),
  HISAL    NUMBER(10,2)
)

insert into SALGRADE values(1,0,100);
insert into SALGRADE values(2,100,200);
insert into SALGRADE values(3,200,500);
insert into SALGRADE values(4,500,1000);
insert into SALGRADE values(5,1000,2000);
insert into SALGRADE values(6,2000,3000);
insert into SALGRADE values(6,3000,5000);
insert into SALGRADE values(6,5000,8000);
insert into SALGRADE values(6,8000,10000);

--给emp添加MGR（管理者号）列
alter table emp add MGR NUMBER(10);

select * from emp for update;



select ename,sal*12 from emp;

select ename as 姓名,sal*12 as 年收入 from emp;

select ename as "姓名",sal*12 as "年收入" from emp;


select ename,sal,comm,sal+comm from emp;

--使用NVL函数处理NULL值
select ename,sal,comm,sal+nvl(comm,0) as "月收入" from emp;

--使用NVL2函数处理NULL值
select ename,nvl2(comm,sal+comm,sal) from emp;

select * from emp;

--连接字符串
select ename||' is a '||job as "Employee Detail" from emp;

--使用where子句
select ename,sal from emp where sal>100;

select job,sal from emp where ename='SCOTT';

select job,sal from emp where lower(ename) = 'scott';

select ename,sal,hiredate from emp where hiredate>'01-1月-82';

select ename,sal,hiredate from emp where hiredate>to_date('1982-01-01','YYYY-MM-DD');

select ename,sal,hiredate,job from emp where sal between 100 and 200;

--显示首字符为S的所有雇员名及其工资
select ename,sal from emp where ename like 'S%';

--显示第三个字符为大写A的所有雇员名及其工资
select ename,sal from emp where ename like '__A%';

--显示雇员名包含"_"的雇员信息（其中ESCAPE后的字符a为转义符
select ename,sal from emp where ename like '%a_%' ESCAPE 'a';


select * from emp for update;


select ename,sal from emp where sal in(800,1250);

select ename,sal from emp where job is null;

--显示补助非空的雇员信息
select ename,sal,comm from emp where comm is not null;

--当执行升序排序时，如果被排序列包含NULL值，那么NULL会显示在最后面。
select ename,sal,comm from emp where deptno=3 order by comm;

--当执行降序排序时，如果被排序列存在NULL值，那么NULL会显示在最前面。
select ename,sal,comm from emp where deptno=3 order by comm desc;

--使用非选择列表列进行排序
select ename from emp order by sal desc;

--使用别名排序
select ename,sal*12 as "全年工资" from emp where deptno=3 order by "全年工资" desc;

--使用列位置编号排序
--另外当使用UNION,UNION ALL,INTERSECT,MINUS等集合操作符合并查询结查时，如果选择列表中的列名不同，并且希望进行排序，那么必须使用列位置。
select ename,sal*12 "全年工资" from emp where deptno=3 order by 2 desc;


select * from emp;

--使用特定格式插入日期值
insert into emp(empno,ename,sal,hiredate,comm,job,deptno)
values(1356,'MARY',30.2,to_date('1983-10-20','YYYY-MM-DD'),10.2,'CLERK',5);

--使用DEFAULT提供数据
select * from dept;

insert into dept values(60,'MARKET',DEFAULT);

SELECT * FROM dept WHERE deptno=60;


--使用子查询插入数据
select * from employee;
select * from emp;

create table employee as select * from emp where 1=2;

insert into employee(empno,ename,sal,deptno)
select empno,ename,sal,deptno from emp
where deptno=3;

--使用子查询执行直接装载
--当要装载大批量数据时，采用第二种方法装载数据的速度远远优于第一种方法。
insert /*++APPEND*/ into employee(empno,ename,sal,deptno)
select empno,ename,sal,deptno from emp
where deptno=3;


--使用多表插入数据
create table dept10 as select * from emp where 1=2;
create table dept20 as select * from emp where 1=2;
create table dept30 as select * from emp where 1=2;
create table clerk as select * from emp where 1=2;
create table other as select * from emp where 1=2;

select * from dept10;
select * from dept20;
select * from dept30;
select * from clerk;
select * from other;

delete dept10;
delete dept20;
delete dept30;
delete clerk;
delete other;

select * from emp for update;

--使用ALL操作符执行多表插入
insert all
when deptno=1 then into dept10
when deptno=2 then into dept20
when deptno=3 then into dept30
when job='CLERK' then into clerk
else into other
select * from emp;

--使用FIRST操作符执行多表插入
--当使用FIRST操作符执行多表插入时，如果数据已经满足了先前条件，并且已经被插入到某表，那么该行数据在后续插入中将不会被再次使用。
insert first
when deptno=1 then into dept10
when deptno=2 then into dept20
when deptno=3 then into dept30
when job='CLERK' then into clerk
else into other
select * from emp;


select * from emp;

--更新日期列数据
update emp set hiredate=to_date('1984/01/01','YYYY/MM/DD') where empno=7788;

--使用DEFAULT选项更新数据
select job from emp where ename='SCOTT';

update emp set job=DEFAULT where ename='SCOTT';

--修改表字段，设置默认值
alter table emp modify (job NVARCHAR2(255) default 'JOB'); 

--使用子查询更新数据
--更新关联数据
update emp set(job,sal,comm) = (
select job,sal,comm from emp where ename='FAT')
where ename='SCOTT';

--使用子查询更新数据
--复制表数据
select * from employee;

update employee set deptno = 
   (select deptno from emp where empno=7788)
where job = (select job from emp where empno=7788);

--删除数据
delete from emp where ename='SMITH';

select * from emp;

--使用TRUNCATE TABLE截断表
--使用TRUNCATE TABLE语句不仅会删除表的所有数据，而且还会释放表段所占用的空间。
--注意，DELETE语句的操作可以回退，但TRUNCATE TABLE语句的操作不能回退。
truncate table employee;

--使用子查询删除数据
delete from emp where deptno = (select deptno from dept where dname='SALES');

select * from dept;

select * from emp;

--删除主表数据的注意事项
delete from dept where deptno=10;


--设置保存点
savepint a;
exec dbms_transaction.savepoint('a');

--取消部分事务
rollback to a;
exec dbms_transaction.rollback_savepoint('a');

--取消全部事务
rollback;
exec dbms_transaction.rollback;


--只读事务
--设置只读事务
--当使用只读事务时，可以确保用户取得特定时间点的数据。
--只读事务是指只允许执行查询操作，而不允许执行任何DML操作的事务。
set TRANSACTION READ ONLY;
exec dbms_transaction.read_only;

--顺序事务
--设置顺序事务
--顺序事务使得用户可以取得特定时间点的数据，并且允许DML操作，可以使用顺序事务。
set TRANSACTION ISOLATION LEVEL SERIALIZABLE;


--取得总计行数
select count(*) from emp;

--在count函数中还可以引用表达式，因为分组函数会忽略NULL行，所以使用count(表达式)会显示NOT NULL的总计行数。
select count(comm) from emp;

--取得方差和标准偏差
select variance(sal),stddev(sal) from emp;

--取消重得值
select count(distinct deptno) AS distinct_dept from emp;

select count(deptno) AS dept from emp;

select * from emp;

select distinct deptno AS distinct_dept from emp;

select deptno AS distinct_dept from emp;


--使用GROUP BY进行单列分组
select deptno,avg(sal),max(sal) from emp group by deptno;

--使用GROUP BY进行多列分组
select deptno,job,avg(sal),max(sal) from emp
group by deptno,job;

--使用HAVING子句限制分组显示结果
select deptno,avg(sal),max(sal) from emp
group by deptno
having avg(sal)<100;


--使用ROLLUP操作符
--下面显示每部门每岗位的平均工资、每部门的平均工资、所有雇员平均工资
select deptno,job,avg(sal) from emp
group by rollup(deptno,job);

--使用CUBE操作符
--下面以显示每部门每岗位平均工资、部门平均工资、岗位平均工资、所有雇员平均工资为例
select deptno,job,avg(sal) from emp
group by cube(deptno,job);

--使用GROUPING函数
--GROUPING函数用于确定统计结果是否用到了特定列。
--如果函数返回0，则表示统计结果使用了该列，如果函数返回1，则表示统计结果未使用该列。
select deptno,job,avg(sal),grouping(deptno),grouping(job)
from emp group by cube(deptno,job);

--GROUPING SETS
--使用GROUPING SETS操作符可以合并多个分组的结果。

--显示部门平均工资
select deptno,avg(sal) from emp group by deptno;

--显示岗位平均工资
select job,avg(sal) from emp group by job;

--显示部门平均工资和岗位平均工资
select deptno,job,avg(sal) from emp group by grouping sets(deptno,job);

--使用相等连接执行主从查询
--下面显示所有雇员的名称、工资及其所在的部门名称为例，说明使用相等连接的方法。
select e.ename,e.sal,d.dname from emp e,dept d
where e.deptno = d.deptno;

--使用AND指定其他条件
select d.dname,e.ename,e.sal from emp e,dept d
where e.deptno=d.deptno and d.deptno=1;


--不等连接
select * from salgrade;

--显示所有雇员的名称、工资及其工资级别
select a.ename,a.sal,b.grade from emp a,salgrade b
where a.sal between b.losal and b.hisal;

--连接查询-自连接
select * from emp for update;
--显示EMPP雇员的上级领导
select manager.ename from emp manager,emp worker
where worker.mgr = manager.empno
and worker.ename = 'EMPP';

select * from dept;
--内连接
select a.dname,b.ename from dept a,emp b
where a.deptno=b.deptno and a.deptno=1;

select a.dname,b.ename from dept a
inner join emp b
on a.deptno = b.deptno
and a.deptno=1;

--如果主表的主键列和从表的外部键列名称相同，那么还可以使用NATURAL JOIN关键字自动执行内连接操作
select dname,ename from dept natural join emp;

--左外连接
select a.dname,b.ename from dept a left join emp b
on a.deptno = b.deptno and a.deptno = 1;

select a.dname,b.ename from dept a right join emp b
on a.deptno = b.deptno and a.deptno=1;

--完全外连接
select a.dname,b.ename from dept a full join emp b
on a.deptno = b.deptno and a.deptno=1;

--使用（+）操作符
--左外连接
select a.dname,b.ename from dept a,emp b
where a.deptno = b.deptno(+) and b.deptno(+) = 1;

--右外连接
select a.dname,b.ename from dept a,emp b
where a.deptno(+) = b.deptno and a.deptno(+) = 1;


--单行子查询
select ename,sal,deptno from emp where deptno=
(select deptno from emp where ename = 'SCOTT');

select ename,sal,deptno from emp where deptno in
(select deptno from emp);

--在多行子查询中使用IN操作符
select ename,job,sal,deptno from emp where job in
(select distinct job from emp where deptno = 1);

--在多行子查询中使用ALL操作符
select ename,sal,deptno from emp where sal>all
(select sal from emp where deptno = 5);

--在多行子查询中使用ANY操作符
select ename,sal,deptno from emp where sal>any
(select sal from emp where deptno = 5);


select * from emp;

--多列子查询
select ename,job,sal,deptno from emp where (deptno,job)=
(select deptno,job from emp where ename='EMPP');

--成对比较
select ename,sal,comm,deptno from emp
where (sal,nvl(comm,-1)) in (select sal,nvl(comm,-1)
from emp where deptno=3);

--非成对比较示例
select ename,sal,comm,deptno from emp
where sal in(select sal from emp where deptno=3)
and nvl(comm,-1) in (select nvl(comm,-1) from emp where deptno=3);

--相关子查询
--相关子查询是指需要引用主查询表列的子查询语句，相关子查询是通过EXISTS谓词来实现的
select ename,job,sal,deptno from emp where exists
(select 1 from dept where dept.deptno = emp.deptno
and dept.loc = 'NEW YORK');

select * from dept for update;

--在FROM子句中使用子查询
--当在FROM子句中使用子查询时，该子查询会被作为视图对待，因此也被称为内嵌视图
--显示高于部门平均工资的雇员为例
select ename,job,sal from emp,
(select deptno,avg(sal) avgsal from emp group by deptno) dept
where emp.deptno = dept.deptno and sal > dept.avgsal;


--在DML语句中使用子查询
--在INSERT语句中使用子查询

create table EMPL
(
  id       NUMBER(10),
  name     varchar2(20),
  title    varchar2(100),
  salary   number(10,2),
  EMPNO    NUMBER(10),
  HIREDATE TIMESTAMP(6),
  COMM     NUMBER(10,2),
  JOB      NVARCHAR2(255),
  DEPTNO   NUMBER(10)
)

insert into empl(id,name,title,salary) 
select empno,ename,job,sal from emp;

select * from empl;

--在UPDATE语句中使用子查询
--当在UPDATE语句中使用子查询时，既可以在WHERE子句中引用子查询（返回未知条件值），也可以在SET子句中使用子查询（修改列数据）
--将SCOTT同岗位的雇员工资和补助更新为与SCOTT的工资和补助完全相同为例
update emp set(sal,comm) =
(select sal,comm from emp where ename='SCOTT')
where job=(select job from emp where ename='SCOTT');

select * from emp for update;

--在DELETE语句中使用子查询
delete from emp where deptno=
(select deptno from dept where dname='SALES');

select * from dept;

--在DDL语句中使用子查询
--在CREATE TABLE语句中使用子查询
create table new_emp(id,name,sal,job,deptno) as 
select empno,ename,sal,job,deptno from emp;

select * from new_emp;

--在CREATE VIEW语句中使用子查询
create or replace view dept_10 as 
select empno,ename,job,sal,deptno from emp
where deptno=1 order by empno;

select * from dept_10;

--在CREATE MATERIALIZED VIEW语句中使用子查询
create MATERIALIZED VIEW summary_emp as 
select deptno,job,avg(sal) avgsal,sum(sal) sumsal
from emp group by cube(deptno,job);


--UNION
--用于获取两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行，并且会以第一列的结果进行排序。
select ename,sal,job from emp where sal>100
union
select ename,sal,job from emp where job='JOB';

select * from emp;

--UNION ALL
--UNION ALL操作符用于获取两个结果集的并集。
--但与UNION操作符不同，该操作符不会取消重复值，而且也不会以任何列进行排序。
select ename,sal,job from emp where sal>100
union all
select ename,sal,job from emp where job='JOB';

--INTERSECT
--INTERSECT操作符用于获取两个结果集的交集。
--当使用该操作符时，只会显示同时存在于两个结果集中的数据，并且会以第一列进行排序。
select ename,sal,job from emp where sal>100
intersect
select ename,sal,job from emp where job='JOB';

--MINUS
--MINUS操作符用于获取两个结果集的差集。
--当使用该操作符时，只会显示在第一个结果集中存在，在第二个结果集中不存在的数据，并且会以第一列进行排序。
select ename,sal,job from emp where sal>100
minus
select ename,sal,job from emp where job='JOB';

select * from emp for update;

--其他复杂查询
--层次查询
--下面以显示除"CLERK"外所有其他雇员的上下级关系为例，说明使用层次查询的方法。
select LPAD(' ',3*(LEVEL-1))||ename ename,
LPAD(' ',3*(LEVEL-1))||job job from emp
where job<>'CLERK' start with mgr is NULL
connect by mgr=PRIOR empno;

--使用CASE表达式
select ename,sal,
CASE when sal>100 then 3
when sal>20 then 2
else 1 end grade
from emp where deptno=10;

update emp set sal=1000 where empno=8;
--查看当前数据
select ename,sal from emp where ename='FAT';

--查看历史数据
select ename,sal from emp as of timestamp to_timestamp('2010-10-26 10:48:00','YYYY-MM-DD HH24:MI:SS')
where ename='FAT'


--使用WITH子句重用子查询
--显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和（两次使用相同子查询）
select dname,sum(sal) as dept_total from emp,dept
where emp.deptno=dept.deptno group by dname
having sum(sal) >
(select sum(sal)*1/3 from emp,dept
where emp.deptno = dept.deptno
);

--显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和（使用WITH子句重用子查询）
with summary as (
select dname,sum(sal) as dept_total from emp,dept
where emp.deptno=dept.deptno group by dname
)
select dname,dept_total from summary where dept_total>
(select sum(dept_total)*1/3 from summary);