--视图
create view v_myview
as
select * from emp where sal>2000;
select * from v_myview;
select * from user_views;
create view v_emp20
as
select * from emp where deptno=20;
select view_name,text_length,text from user_views;
select * from v_emp20;
create or replace view v_myview
as
select d.deptno,d.dname,d.loc,
count(e.empno) count,nvl(round(avg(sal),2),0) avg,nvl(sum(sal),0) sum,
nvl(max(sal),0) max,nvl(min(sal),0) min
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;
select * from v_myview;
create or replace view v_myview
(部门编号,部门名称,位置,人数,平均工资,总工资,最高工资,最低工资)
as
select d.deptno,d.dname,d.loc,
count(e.empno) count,nvl(round(avg(sal),2),0) avg,nvl(sum(sal),0) sum,
nvl(max(sal),0) max,nvl(min(sal),0) min
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;
select * from v_myview;
create or replace view v_emp20
as
select empno,ename,job,sal,deptno from emp where deptno=20;
select * from v_emp20;
insert into v_emp20(empno,ename,job,sal,deptno)values(6688,'工大','CLERK',1900,20);
SELECT * FROM V_EMP20;
select * from emp;
update v_emp20 set ename='MLDNJAVA',JOB='MANAGE',SAL=2300 where empno=6688;
select * from v_emp20;
select * from emp where deptno=20;
delete from v_emp20 where empno=6688;
commit;
select * from v_emp20;
select * from emp where deptno=20;
create or replace view v_myview
as
select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno and d.deptno=20;
select * from v_myview;
insert into v_myview(empno,ename,job,sal,deptno,dname,loc)
values(6688,'工大','CLERK',2000,50,'教学','北京');
update v_myview set ename='史密斯',sal=5000,dname='教学' where empno=7369;
delete from v_myview where empno=7369;
select * from v_myview;
select * from emp;
select * from dept;
delete from v_myview where deptno=20;
select * from emp;
select * from dept;
select * from v_myview;
create or replace view v_emp20
as
select * from emp where deptno=20;
select view_name,text_length,text from user_views;
select * from v_emp20;
select * from emp;
SELECT * FROM DEPT;
desc emp;
insert into emp(empno,ename,job,sal,deptno,mgr,hiredate)
values(7369,'SMITH','CLERK',800,20,7902,to_date('1980-12-17','yyyy-mm-dd'));
insert into emp(empno,ename,job,sal,deptno,mgr,hiredate)
values(7566,'JONES','MANAGER',2975,20,7839,to_date('1981-04-02','yyyy-mm-dd'));
insert into emp(empno,ename,job,sal,deptno,mgr,hiredate)
values(7788,'SCOTT','ANALYST',3000,20,7566,to_date('1987-04-19','yyyy-mm-dd'));
insert into emp(empno,ename,job,sal,deptno,mgr,hiredate)
values(7876,'ADAMS','CLERK',1100,20,7788,to_date('1987-05-23','yyyy-mm-dd'));
insert into emp(empno,ename,job,sal,deptno,mgr,hiredate)
values(7902,'FORD','ANALYST',3000,20,7566,to_date('1981-12-03','yyyy-mm-dd'));
COMMIT;
rollback;
create or replace view v_emp20
as
select * from emp where deptno=20;
select view_name,text_length,text from user_views;
select * from v_emp20;
update v_emp20 set deptno=40 where empno=7369;
select * from v_emp20;
select * from emp where empno=7369;
rollback;
create or replace view v_emp20
as
select * from emp where deptno=20
with check option constraint v_emp20_ck;
update v_emp20 set ename='史密斯',comm=300 where empno=7369;
select * from V_emp20;
create or replace view v_emp20
as
select * from emp where deptno=20
with read only;
select view_name,text_length,text,read_only from user_views;
update v_emp20 set ename='SMITH',comm=null where empno=7369;
drop view v_myview;
drop view emp20;
select view_name,text_length,text,read_only from user_views;
select * from emp;
--序列
create sequence myseq;
select * from user_sequences;
select myseq.nextval from dual;
select myseq.currval from dual;
drop table member purge;
create table member(
mid number,
name varchar2(50) not null,
constraint pk_mid primary key(mid)
);
select * from tab;
insert into member(mid,name) values(myseq.nextval,'软件1102');
select * from member;
drop sequence myseq;
select * from user_sequences;
drop sequence myseq;
create sequence myseq increment by 3;
select * from user_sequences;
select myseq.nextval from dual;
drop sequence myseq;
create sequence myseq
increment by 3
start with 30;
select myseq.nextval from dual;
drop sequence myseq;
create sequence myseq
cache 100;
select * from user_sequences;
drop sequence myseq;
create sequence myseq
nocache;
drop sequence myseq;
create sequence myseq
start with 1
increment by 2
maxvalue 10
minvalue 1
cycle
cache 3;
select myseq.nextval from dual;
drop sequence myseq;
create sequence myseq;
select * from user_sequences;
alter sequence myseq
increment by 10
maxvalue 98765
cache 100;
select myseq.nextval from dual;
--同义词
--Oracle伪劣
select rowid,deptno,dname,loc from dept;
select rowid,
dbms_rowid.rowid_object(rowid) 数据对象号,
dbms_rowid.rowid_relative_fno(rowid) 相对文件号,
dbms_rowid.rowid_block_number(rowid) 数据块号,
dbms_rowid.rowid_row_number(rowid) 数据行号,
deptno,dname,loc
from dept;
drop table mydept purge;
create table mydept as select * from DEPT;
insert into mydept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');
insert into mydept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');
insert into mydept(deptno,dname,loc) values(20,'RESEARCH','DALLAS');
insert into mydept(deptno,dname,loc) values(20,'RESEARCH','DALLAS');
insert into mydept(deptno,dname,loc) values(20,'RESEARCH','DALLAS');
COMMIT;
select rowid,deptno,dname,loc from mydept;
select deptno,dname,loc,min(rowid)
from mydept
group by deptno,dname,loc;
delete from mydept
where rowid not in(
select min(rowid)
from mydept
group by deptno
);
select * from mydept;
select rownum,empno,ename,job,sal,hiredate from emp;
select rownum,empno,ename,job,sal,hiredate from emp where deptno=30;
select avg(sal) from emp;
select e.empno,e.ename,e.sal,e.job,e.hiredate
from emp e
where e.sal>(
select avg(sal) from emp);
select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc
from emp e,dept d
where e.sal>(
select avg(sal) from emp)
and e.deptno=d.deptno;
select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,s.grade
from emp e,dept d,salgrade s
where e.sal>(
select avg(sal) from emp)
and e.deptno=d.deptno
and e.sal between s.losal and s.hisal;
select rownum rn,e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,s.grade
from emp e,dept d,salgrade s
where e.sal>(
select avg(sal) from emp)
and e.deptno=d.deptno
and e.sal between s.losal and s.hisal;
select * from emp where rownum=1;
select * from(
select empno,ename,job,hiredate,sal,mgr,deptno,rownum rn
from emp where rownum<=5) temp
where temp.rn>0;
select * from(
select empno,ename,job,hiredate,sal,mgr,deptno,rownum rn
from emp where rownum<=10) temp
where temp.rn>5;
--索引
select * from emp where sal>1500;
create index emp_sal_ind on emp(sal);
select index_name,index_type,table_owner,table_name,uniqueness,status from user_indexes;
select * from user_ind_columns where index_name='EMP_SAL_IND';
create index emp_hiredate_ind_desc on emp(hiredate);
select * from scott.emp
where hiredate between to_date('1981-01-01','yyyy-mm-dd') and to_date('1981-12-21','yyyy-mm-dd')
order by hiredate desc;
create index emp_ename_ind on emp(lower(ename));
select * from emp where lower(ename)='smith';
create bitmap index emp_deptno_ind on emp(deptno);
select * from emp where deptno=10;
select * from emp where deptno=10 or deptno=20;
select index_name,index_type,table_owner,table_name,uniqueness,status from user_indexes;
drop index emp_sal_ind;
drop index emp_deptno_ind;
drop index emp_ename_ind;
create view v_myview
as
select * from emp where sal>2000;
select * from v_myview;
select * from user_views;
create view v_emp20
as
select * from emp where deptno=20;
select view_name,text_length,text from user_views;
select * from v_emp20;
create or replace view v_myview
as
select d.deptno,d.dname,d.loc,
count(e.empno) count,nvl(round(avg(sal),2),0) avg,nvl(sum(sal),0) sum,
nvl(max(sal),0) max,nvl(min(sal),0) min
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;
select * from v_myview;
create or replace view v_myview
(部门编号,部门名称,位置,人数,平均工资,总工资,最高工资,最低工资)
as
select d.deptno,d.dname,d.loc,
count(e.empno) count,nvl(round(avg(sal),2),0) avg,nvl(sum(sal),0) sum,
nvl(max(sal),0) max,nvl(min(sal),0) min
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;
select * from v_myview;
create or replace view v_emp20
as
select empno,ename,job,sal,deptno from emp where deptno=20;
select * from v_emp20;
insert into v_emp20(empno,ename,job,sal,deptno)values(6688,'工大','CLERK',1900,20);
SELECT * FROM V_EMP20;
select * from emp;
update v_emp20 set ename='MLDNJAVA',JOB='MANAGE',SAL=2300 where empno=6688;
select * from v_emp20;
select * from emp where deptno=20;
delete from v_emp20 where empno=6688;
commit;
select * from v_emp20;
select * from emp where deptno=20;
create or replace view v_myview
as
select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno and d.deptno=20;
select * from v_myview;
insert into v_myview(empno,ename,job,sal,deptno,dname,loc)
values(6688,'工大','CLERK',2000,50,'教学','北京');
update v_myview set ename='史密斯',sal=5000,dname='教学' where empno=7369;
delete from v_myview where empno=7369;
select * from v_myview;
select * from emp;
select * from dept;
delete from v_myview where deptno=20;
select * from emp;
select * from dept;
select * from v_myview;
create or replace view v_emp20
as
select * from emp where deptno=20;
select view_name,text_length,text from user_views;
select * from v_emp20;
select * from emp;
SELECT * FROM DEPT;
desc emp;
insert into emp(empno,ename,job,sal,deptno,mgr,hiredate)
values(7369,'SMITH','CLERK',800,20,7902,to_date('1980-12-17','yyyy-mm-dd'));
insert into emp(empno,ename,job,sal,deptno,mgr,hiredate)
values(7566,'JONES','MANAGER',2975,20,7839,to_date('1981-04-02','yyyy-mm-dd'));
insert into emp(empno,ename,job,sal,deptno,mgr,hiredate)
values(7788,'SCOTT','ANALYST',3000,20,7566,to_date('1987-04-19','yyyy-mm-dd'));
insert into emp(empno,ename,job,sal,deptno,mgr,hiredate)
values(7876,'ADAMS','CLERK',1100,20,7788,to_date('1987-05-23','yyyy-mm-dd'));
insert into emp(empno,ename,job,sal,deptno,mgr,hiredate)
values(7902,'FORD','ANALYST',3000,20,7566,to_date('1981-12-03','yyyy-mm-dd'));
COMMIT;
rollback;
create or replace view v_emp20
as
select * from emp where deptno=20;
select view_name,text_length,text from user_views;
select * from v_emp20;
update v_emp20 set deptno=40 where empno=7369;
select * from v_emp20;
select * from emp where empno=7369;
rollback;
create or replace view v_emp20
as
select * from emp where deptno=20
with check option constraint v_emp20_ck;
update v_emp20 set ename='史密斯',comm=300 where empno=7369;
select * from V_emp20;
create or replace view v_emp20
as
select * from emp where deptno=20
with read only;
select view_name,text_length,text,read_only from user_views;
update v_emp20 set ename='SMITH',comm=null where empno=7369;
drop view v_myview;
drop view emp20;
select view_name,text_length,text,read_only from user_views;
select * from emp;
--序列
create sequence myseq;
select * from user_sequences;
select myseq.nextval from dual;
select myseq.currval from dual;
drop table member purge;
create table member(
mid number,
name varchar2(50) not null,
constraint pk_mid primary key(mid)
);
select * from tab;
insert into member(mid,name) values(myseq.nextval,'软件1102');
select * from member;
drop sequence myseq;
select * from user_sequences;
drop sequence myseq;
create sequence myseq increment by 3;
select * from user_sequences;
select myseq.nextval from dual;
drop sequence myseq;
create sequence myseq
increment by 3
start with 30;
select myseq.nextval from dual;
drop sequence myseq;
create sequence myseq
cache 100;
select * from user_sequences;
drop sequence myseq;
create sequence myseq
nocache;
drop sequence myseq;
create sequence myseq
start with 1
increment by 2
maxvalue 10
minvalue 1
cycle
cache 3;
select myseq.nextval from dual;
drop sequence myseq;
create sequence myseq;
select * from user_sequences;
alter sequence myseq
increment by 10
maxvalue 98765
cache 100;
select myseq.nextval from dual;
--同义词
--Oracle伪劣
select rowid,deptno,dname,loc from dept;
select rowid,
dbms_rowid.rowid_object(rowid) 数据对象号,
dbms_rowid.rowid_relative_fno(rowid) 相对文件号,
dbms_rowid.rowid_block_number(rowid) 数据块号,
dbms_rowid.rowid_row_number(rowid) 数据行号,
deptno,dname,loc
from dept;
drop table mydept purge;
create table mydept as select * from DEPT;
insert into mydept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');
insert into mydept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');
insert into mydept(deptno,dname,loc) values(20,'RESEARCH','DALLAS');
insert into mydept(deptno,dname,loc) values(20,'RESEARCH','DALLAS');
insert into mydept(deptno,dname,loc) values(20,'RESEARCH','DALLAS');
COMMIT;
select rowid,deptno,dname,loc from mydept;
select deptno,dname,loc,min(rowid)
from mydept
group by deptno,dname,loc;
delete from mydept
where rowid not in(
select min(rowid)
from mydept
group by deptno
);
select * from mydept;
select rownum,empno,ename,job,sal,hiredate from emp;
select rownum,empno,ename,job,sal,hiredate from emp where deptno=30;
select avg(sal) from emp;
select e.empno,e.ename,e.sal,e.job,e.hiredate
from emp e
where e.sal>(
select avg(sal) from emp);
select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc
from emp e,dept d
where e.sal>(
select avg(sal) from emp)
and e.deptno=d.deptno;
select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,s.grade
from emp e,dept d,salgrade s
where e.sal>(
select avg(sal) from emp)
and e.deptno=d.deptno
and e.sal between s.losal and s.hisal;
select rownum rn,e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,s.grade
from emp e,dept d,salgrade s
where e.sal>(
select avg(sal) from emp)
and e.deptno=d.deptno
and e.sal between s.losal and s.hisal;
select * from emp where rownum=1;
select * from(
select empno,ename,job,hiredate,sal,mgr,deptno,rownum rn
from emp where rownum<=5) temp
where temp.rn>0;
select * from(
select empno,ename,job,hiredate,sal,mgr,deptno,rownum rn
from emp where rownum<=10) temp
where temp.rn>5;
--索引
select * from emp where sal>1500;
create index emp_sal_ind on emp(sal);
select index_name,index_type,table_owner,table_name,uniqueness,status from user_indexes;
select * from user_ind_columns where index_name='EMP_SAL_IND';
create index emp_hiredate_ind_desc on emp(hiredate);
select * from scott.emp
where hiredate between to_date('1981-01-01','yyyy-mm-dd') and to_date('1981-12-21','yyyy-mm-dd')
order by hiredate desc;
create index emp_ename_ind on emp(lower(ename));
select * from emp where lower(ename)='smith';
create bitmap index emp_deptno_ind on emp(deptno);
select * from emp where deptno=10;
select * from emp where deptno=10 or deptno=20;
select index_name,index_type,table_owner,table_name,uniqueness,status from user_indexes;
drop index emp_sal_ind;
drop index emp_deptno_ind;
drop index emp_ename_ind;