数据库开发基础-Oracle-SQL基础-008

--视图
视图是数据库对象之一
视图在sql语句中体现的角色与表相同。
但是视图不是真实的表，而是一个查询语句
对应的结果集。


create view v_emp_10
as
select empno,ename,sal,deptno
from emp 
where deptno  =10;

select * from v_emp_10;

desc v_emp_10

当视图对应的子查询的字段含有函数或
表达式时，必须对该字段定义别名，那么
视图该字段使用别名作为字段名。
--错误
create or replace view v_emp_10
as
select empno id, ename name, sal * 12 , deptno
  from emp
 where deptno = 10;

--正确
create or replace view v_emp_10
as
select empno id, ename name, sal * 12 n_salary, deptno
  from emp
 where deptno = 10;

create or replace view v_emp_10
as
select empno id, ename name, sal  salary, deptno
  from emp
 where deptno = 10;

--对视图进行DML操作
对简单视图可以进行DML操作，但是对复杂视图不可以。
对视图进行dml操作就是对视图数据来源的基础表进行操作。

insert into v_emp_10
(id,name,salary,deptno)
values
(1003,'JACK2',5000,10);

select * from v_emp_10;
select * from emp;


当插入一条视图看不见的数据时，会对
基础表造成“污染”

insert into v_emp_10
  (id, name, salary, deptno)
values
  (1002, 'ROSE', 5000, 20);


select * from v_emp_10;
select * from emp;

更新数据同样会对基础表污染
更新后的数据对视图而言不可见不可控

update v_emp_10 set deptno = 20;--所以记录数据

select * from v_emp_10;
select * from emp;


删除不存在该问题
delete from v_emp_10
where deptno =20;

为视图添加检查选项【约束】，可以避免对视图
操作后视图对数据不可控的情况。
当添加检查选项后，对视图进行：
insert :插入的数据必须视图可见
update:更新后视图必须对应数据可见

create or replace view v_emp_10
as
select empno id,ename name,
sal salary,deptno 
from emp
where deptno =10
with check option

update v_emp_10 set deptno = 20 --【全表更新不受制约】
--where id = '7369';

也可以为视图添加只读选项，添加后
该视图不允许执行dml操作。


create or replace view v_emp_10
as
select empno id,ename name,sal salary ,deptno
from emp where deptno =10
with read only;


--复杂视图
视图对应的select语句中含有函数，表达式，分组等操作时
该视图就是一个复杂视图。
对于多表关联查询的视图，叫做连接视图，也算是复杂视图的一种。


复杂视图不能进行DML操作。
定义一个可以查看各个部门薪资情况的视图：[视图的数据会随基础表数据同步]

create view v_emp_salary
as
select d.dname,d.deptno,
       avg(e.sal) avg_sal,
       sum(sal) sum_sal,
       min(sal) min_sal,
       max(sal) max_sal 

from 
emp e,dept d
where e.deptno =d.deptno
group by d.dname,d.deptno

select * from v_emp_salary;

查看谁的工资比自己所在部门平均工资高？
select e.ename, e.sal, e.deptno
  from emp e, v_emp_salary v
 where e.deptno = v.deptno
   and e.sal > v.avg_sal

select e.ename, e.sal, e.deptno
  from emp e
  join v_emp_salary v
    on e.deptno = v.deptno
 where e.sal > v.avg_sal

--删除视图
删除视图不会对视图的数据来源的基础表做任何操作。

drop view v_emp_10;



--表的数据字典
select * from user_tables;

select table_name from user_tables

--序列
数据库对象之一
序列可以按照指定的规则生成一系列的数字。
序列生成的数字通常是为了某张表的主键提供值的。


create sequence seq_emp_id
start with 1
increment by 1;


序列支持两个伪列：
nextval：让序列生成一个数字，是用序列最后生成的数字加上步长得到，
新创建的序列调用时返回 start with 指定的值。


currval :获取序列当前的数字（即最后一次通过nextval生成的数字）
新创建的序列必须先至少执行一次nextval以后才可以使用currval。
nextval 会导致序列增长。而且增长后不能获取原来生成过的值。


select seq_emp_id.nextval from dual;

select seq_emp_id.currval from dual;

insert into emp
  (empno, ename, job, sal, deptno)
values
  (seq_emp_id.nextval, 'CheriJim', 'CLERK', 6000, 30);

select * from EMP;

删除序列
drop sequence seq_emp_id;




--索引
数据库对象之一
为某张表的某些字段添加索引可以提高表的查询，排序，去重等操作效率

索引的算法和维护是数据库自行维护的，我们不能干涉，我们只需要告诉数据库
是否创建索引即可。


当执行某些sql时数据库会自行使用可用的索引来提高执行效率

create index idx_emp_ename on emp(ename);

--创建表约束

create table employees(
       eid number(6),
       name varchar(30) not null,--
       salary number(7,2),
       hiredate date constraint employees_hiredate_nn not null--
);

insert into employees (eid, name, salary) values (null, 'JACK', 5000);

create table employees1(
       eid number(6) unique,--
       name varchar2(30),
       email varchar2(50),
       salary number(7,2),
       hiredate date,
       constraint employees1_email_uk unique(email)--
);



insert into employees1
  (eid, name, email, salary)
values
  (101, 'JACK', '@11', 5000);

--null 值 在唯一性约束中不认为相同
INSERT INTO employees1
(eid,name,email,salary)
VALUES
(NULL,'JACK',NULL,5000)
  
select * from employees1;
drop table employees1;--表中存在数据照样可用删除表及数据。
--在实际的数据编程中需要设置 表中存在数据不能删除表，我们可以使用触发器实现这个需求，有关触发器后面会讲到。

create table employees2(
       eid number(6) primary key,--非空且唯一
       name varchar2(30),
       email varchar(50),
       salary number(7,2),
       hiredate date
);


insert into employees2 (eid, name) values (null, 'JACK');

前面有关Oracle-基础sql的所有脚本：

点击打开链接