--工资级别表
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);