--
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);