DDL 数据库视图定义
2020-03-11 本文已影响0人
风中小酌
创建视图
根据表的查询结果创建视图
- 创建简单视图，由一张表的查询生成的视图
SQL> create view dept50 as select e.employee_id id_number, e.last_name name, 12*salary ann_salary from employees e;
View created
SQL> desc dept50;
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------
ID_NUMBER NUMBER(6)
NAME VARCHAR2(25)
ANN_SALARY NUMBER Y
- 创建复杂视图，由多张表的查询或有函数时生成的视图
有分组函数时，列名必须使用别名
SQL> create view emp_dep as select d.department_name, max(e.salary), min(e.salary), avg(e.salary) from employees e, departments d where d.department_id=e.department_id group by d.department_name;
create view emp_dep as select d.department_name, max(e.salary), min(e.salary), avg(e.salary) from employees e, departments d where d.department_id=e.department_id group by d.department_name
ORA-00998: 必须使用列别名命名此表达式
在查询时指定组函数别名
SQL> create view emp_dep as select d.department_name, max(e.salary) max, min(e.salary)min, avg(e.salary)avg from employees e, departments d where d.department_id=e.department_id group by d.department_name;
View created
在创建视图时定义视图的列名
SQL> create view emp_dep(name, maxSal, minSal, avgSal) as select d.department_name, max(e.salary), min(e.salary), avg(e.salary) from employees e, departments d where d.department_id=e.department_id group by d.department_name;
View created
- 创建只读视图，不允许做DML操作
语句末尾使用 WITH READ ONLY
SQL> create view dept50 as select e.employee_id id_number, e.last_name name, 12*salary ann_salary from employees e where e.department_id=50 with read only;
View created
SQL> delete from dept50 where id_number=190;
delete from dept50 where id_number=190
ORA-42399: 无法对只读视图执行 DML 操作
对视图做DML操作
限制：
不能包含组函数、Group by、distinct、用表达式定义的列
SQL> create view dept50 as select e.employee_id id_number, e.last_name name, 12*salary ann_salary from employees e where e.department_id=80;
View created
SQL> select * from dept50;
ID_NUMBER NAME ANN_SALARY
--------- ------------------------- ----------
174 Abel 132000
175 Hutton 105600
对视图中记录的操作，将会导致相应表中原数据的改变
SQL> delete from dept50 where name='Abel';
1 row deleted
SQL> update dept50 set name='Tommy' where id_number=175;
1 row updated
删除视图
SQL> drop view dept50;
View dropped
内建视图
内建视图是一个带有别名，可以在SQL语句中使用的子查询；
一个主查询在FROM子句中指定的子查询就是一个内建视图。
括号中的子查询是内建视图
SQL> select e.last_name, e.salary, e.department_id, ed.maxsal from employees e, (select emp.department_id, max(emp.salary) maxsal from employees emp group by emp.department_id) ed where e.department_id=ed.department_id and e.salary < maxsal;
LAST_NAME SALARY DEPARTMENT_ID MAXSAL
------------------------- ---------- ------------- ----------
Faviet 9000.00 100 12008
Chen 8200.00 100 12008
Sciarra 7700.00 100 12008
Urman 7800.00 100 12008
Popp 6900.00 100 12008
使用内建视图的场景
- TOP-N
查询表中前N条记录，
ROWNUM 伪列在主查询中
在主查询的列投影中加入 rownum 伪列，并在内建视图中对数据进行排序，主查询的过滤条件使用 rownum 过滤。
SQL> select rownum, last_name, salary from (select e.last_name, e.salary from employees e order by e.salary desc) emp where rownum < 6 ;
- 查询表中第N到M条记录
查询第6到10条记录
select * from (select rownum rn, em.* from (select e.* from employees e order by e.SALARY desc) em where rownum <=10) where rn>=6;
分页查询
ROWNUM在内建视图中，
在主查询的where子名中对内建视图的rownum进行过滤，达到分页的目的
SQL> select em.* from (select rownum rn, e.* from employees e) em where em.rn between 100 and 110;
RN EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
100 200 Jennifer Whalen JWHALEN 515.123.4444 2003/9/17 星 AD_ASST 4400.00 101 10
101 201 Michael Hartstein MHARTSTE 515.123.5555 2004/2/17 星 MK_MAN 13000.00 100 20
102 202 Pat Fay PFAY 603.123.6666 2005/8/17 星 MK_REP 6000.00 201 20
103 203 Susan Mavris SMAVRIS 515.123.7777 2002/6/7 星期 HR_REP 6500.00 101 40
104 204 Hermann Baer HBAER 515.123.8888 2002/6/7 星期 PR_REP 10000.00 101 70
105 205 Shelley Higgins SHIGGINS 515.123.8080 2002/6/7 星期 AC_MGR 12008.00 101 110
106 206 William Gietz WGIETZ 515.123.8181 2002/6/7 星期 AC_ACCOUNT 8300.00 205 110
107 2500 Tommy se@sfe.com 2007/11/1 星 IT_PROG 500078.00
8 rows selected
