四个对象
SQL> -- 视图、索引、序列、同义词
SQL> -- 视图
SQL> -- 视图的创建
SQL> create view myview
2 as
3 select * from emp;
create view myview
*
第 1 行出现错误:
ORA-01031: 权限不足
SQL> -- 默认情况下scott是没有创建视图的权限的(view)
SQL> -- 给soctt用户授权，让他可以创建视图 grant create view to scott;
SQL> -- 登录dba的命令 cmd 》》 sqlplus / as sysdba;
SQL> create view myview
2 as
3 select * from emp;
视图已创建。
SQL> desc myview;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select * from view;
select * from view
*
第 1 行出现错误:
ORA-00903: 表名无效
SQL> select * from myview;
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
2016 SAL
8000
2015 zhangsan MANAGER 2016 18-3月 -16
5000 3000 20
7369 SMITH CLERK 7902 17-12月-80
800 20
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81
1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81
1250 500 30
7566 JONES MANAGER 7839 02-4月 -81
2975 20
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-9月 -81
1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81
2850 30
7782 CLARK MANAGER 7839 09-6月 -81
2450 10
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月 -87
3000 20
7839 ZS PRESIDENT 17-11月-81
10000 10
7844 TURNER SALESMAN 7698 08-9月 -81
1500 0 30
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7876 ADAMS CLERK 7788 23-5月 -87
1100 20
7900 JAMES CLERK 7698 03-12月-81
950 30
7902 FORD ANALYST 7566 03-12月-81
3000 20
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7934 MILLER CLERK 7782 23-1月 -82
1300 10
已选择16行。
SQL> set linesize 200;
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
2016 SAL 8000
2015 zhangsan MANAGER 2016 18-3月 -16 5000 3000 20
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 ZS PRESIDENT 17-11月-81 10000 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择16行。
SQL> -- 什么是视图: 视图是一个逻辑表,用来简化sql查询语句,视图本身不存放数据，视图不能提高效率
SQL> drop view myview;
视图已删除。
SQL> create view myview
2 as
3 select e.ename,d.dname,e.job,e.sal,e.comm
4 from emp e,dept d
5 where e.detpno = d.deptno;
where e.detpno = d.deptno
*
第 5 行出现错误:
ORA-00904: "E"."DETPNO": 标识符无效
SQL> create view myview
2 as
3
SQL> select e.ename,d.dname,e.job,e.sal,e.comm
2 from emp e,dept d
3 where e.deptno = d.deptno;
ENAME DNAME JOB SAL COMM
-------------------- ---------------------------- ------------------ ---------- ----------
zhangsan RESEARCH MANAGER 5000 3000
SMITH RESEARCH CLERK 800
ALLEN SALES SALESMAN 1600 300
WARD SALES SALESMAN 1250 500
JONES RESEARCH MANAGER 2975
MARTIN SALES SALESMAN 1250 1400
BLAKE SALES MANAGER 2850
CLARK ACCOUNTING MANAGER 2450
SCOTT RESEARCH ANALYST 3000
ZS ACCOUNTING PRESIDENT 10000
TURNER SALES SALESMAN 1500 0
ENAME DNAME JOB SAL COMM
-------------------- ---------------------------- ------------------ ---------- ----------
ADAMS RESEARCH CLERK 1100
JAMES SALES CLERK 950
FORD RESEARCH ANALYST 3000
MILLER ACCOUNTING CLERK 1300
已选择15行。
SQL> create view myview
2 as
3 select e.ename,d.dname,e.job,e.sal,e.comm
4 from emp e,dept d
5 where e.deptno = d.deptno;
视图已创建。
SQL> select * from myview;
ENAME DNAME JOB SAL COMM
-------------------- ---------------------------- ------------------ ---------- ----------
zhangsan RESEARCH MANAGER 5000 3000
SMITH RESEARCH CLERK 800
ALLEN SALES SALESMAN 1600 300
WARD SALES SALESMAN 1250 500
JONES RESEARCH MANAGER 2975
MARTIN SALES SALESMAN 1250 1400
BLAKE SALES MANAGER 2850
CLARK ACCOUNTING MANAGER 2450
SCOTT RESEARCH ANALYST 3000
ZS ACCOUNTING PRESIDENT 10000
TURNER SALES SALESMAN 1500 0
ENAME DNAME JOB SAL COMM
-------------------- ---------------------------- ------------------ ---------- ----------
ADAMS RESEARCH CLERK 1100
JAMES SALES CLERK 950
FORD RESEARCH ANALYST 3000
MILLER ACCOUNTING CLERK 1300
已选择15行。
SQL> -- 视图的第二个用途: 对于一些敏感的数据可以用来隐藏
SQL> create view empview
2 as
3 select empno,ename,job from emp;
视图已创建。
SQL> desc empview;
名称 是否为空? 类型
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SQL> select * from empview;
EMPNO ENAME JOB
---------- -------------------- ------------------
2016 SAL
2015 zhangsan MANAGER
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 ZS PRESIDENT
EMPNO ENAME JOB
---------- -------------------- ------------------
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
已选择16行。
SQL> select sal from empview;
select sal from empview
*
第 1 行出现错误:
ORA-00904: "SAL": 标识符无效
SQL> insert into empview values(1001,'ls','SAL');
已创建 1 行。
SQL> select * from empview;
EMPNO ENAME JOB
---------- -------------------- ------------------
2016 SAL
2015 zhangsan MANAGER
1001 ls SAL
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
EMPNO ENAME JOB
---------- -------------------- ------------------
7839 ZS PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
已选择17行。
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
2016 SAL 8000
2015 zhangsan MANAGER 2016 18-3月 -16 5000 3000 20
1001 ls SAL
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7839 ZS PRESIDENT 17-11月-81 10000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择17行。
SQL> delete from empview where empno=1001;
已删除 1 行。
SQL> select * from empview;
EMPNO ENAME JOB
---------- -------------------- ------------------
2016 SAL
2015 zhangsan MANAGER
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 ZS PRESIDENT
EMPNO ENAME JOB
---------- -------------------- ------------------
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
已选择16行。
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
2016 SAL 8000
2015 zhangsan MANAGER 2016 18-3月 -16 5000 3000 20
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 ZS PRESIDENT 17-11月-81 10000 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择16行。
SQL> -- 一般创建视图的时候都不希望你用视图去修改真实的表
SQL> drop view myview;
视图已删除。
SQL> create view myview
2 as
3 select empno,ename,job
4 from emp
5 width readonly;
width readonly
*
第 5 行出现错误:
ORA-00933: SQL 命令未正确结束
SQL> create view myview
2 as
3 select empno,ename,job
4 from emp
5 width read only;
width read only
*
第 5 行出现错误:
ORA-00933: SQL 命令未正确结束
SQL> create view myview
2 as
3 select empno,ename,job
4 from emp
5 with readonly;
with readonly
*
第 5 行出现错误:
ORA-00905: 缺失关键字
SQL> create view myview
2 as
3 select empno,ename,job
4 from emp
5 with read only;
视图已创建。
SQL> insert into myview values(1001,'ddd','SAL');
insert into myview values(1001,'ddd','SAL')
*
第 1 行出现错误:
ORA-01733: 此处不允许虚拟列
SQL> selct * from myview;
SP2-0734: 未知的命令开头 "selct * fr..." - 忽略了剩余的行。
SQL> select * from myview;
EMPNO ENAME JOB
---------- -------------------- ------------------
2016 SAL
2015 zhangsan MANAGER
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 ZS PRESIDENT
EMPNO ENAME JOB
---------- -------------------- ------------------
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
已选择16行。
SQL> delete from myview where empno=2016;
delete from myview where empno=2016
*
第 1 行出现错误:
ORA-01752: 不能从没有一个键值保存表的视图中删除
SQL> -- 索引(index)
SQL> -- 索引的用途: 可以用来提高查询的效率
SQL> -- 索引可以在一定程度上提高我们的查询效率，但是他要根据数据量来决定，数据量越多提高的效果越明显，数据越低效果越不明显
SQL> create index myindex on emp(empno);
create index myindex on emp(empno)
*
第 1 行出现错误:
ORA-01408: 此列列表已索引
SQL> create index myjob on emp(job);
索引已创建。
SQL> -- 可以通过查询sql执行的日志，来查看是否调用了我们的索引
SQL> -- 经常变动的列不适合建立索引
SQL> spool off;
浙公网安备 33010602011771号