视图、索引、序列

select * from emp_shixuefu;
--其他数据库对象
--数据库对象包含：表，视图，索引，序列

--视图
--视图在SQL语句中体现的角色与表一致，但其并非一张真实存在的表，他的数据仅来自一条select语句的查询结果集。
create view v_emp_shixuefu_10
as
select empno,ename,sal,deptno
from emp_shixuefu
where deptno=10;

desc v_emp_shixuefu_10
select * from v_emp_shixuefu_10;

--视图对应的查询语句中的字段若使用别名，那么视图的该字段名就为这个别名。
--对应子查询的字段若含有函数或表达式，那么该字段必须给别名。
--or replace 如果视图名存在就覆盖，如果不存在就直接创建。
create or replace view v_emp_shixuefu_10
as
select empno id,ename,sal salary,deptno
from emp_shixuefu
where deptno=10;

select id,salary,deptno from v_emp_shixuefu_10;

--对视图进行DML操作
--对视图进行DML就是对视图数据来源的基础表进行的。并且仅能对简单视图进行，复杂视图不能进行DML操作。
insert into v_emp_shixuefu_10
(id,ename,salary,deptno)
values
(1001,'jjjj',6000,10);

select * from emp_shixuefu;
update v_emp_shixuefu_10
set salary=4000
where ename='jjjj';
delete from v_emp_shixuefu_10
where ename='jjjj';

--对视图进行DML操作不当会“污染”基表数据。
--所谓污染指的是：
--对视图进行的DML操作就是对基表对应数据进行DML操作，但是当对视图
--进行DML操作并修改基表数据后，视图对该记录不可见。
--唯一不会污染的是delete
select * from v_emp_shixuefu_10;
select * from emp_shixuefu;

insert into v_emp_shixuefu_10
(id,ename,salary,deptno)
values
(1008,'pppp',5000,20);

update v_emp_shixuefu_10
set deptno=20
where deptno=10;

delete from v_emp_shixuefu_10
where deptno=20;

--避免由于对视图进行DML污染基表，可以对视图添加检查首选“with check option”
create or replace view v_emp_shixuefu_10
as
select empno id,ename,sal salary,deptno
from emp_shixuefu
where deptno=10
with check option;

--with read only  只读选项
--当视图添加了只读选项后，该视图不可以进行DML操作
create or replace view v_emp_shixuefu_10
as
select empno id,ename,sal salary,deptno
from emp_shixuefu
where deptno=10
with read only;

--
select object_name,object_type
from user_objects;
select object_name,object_type
from user_objects
where object_name like '%SHIXUEFU%';

select text from user_views
where view_name = 'V_EMP_SHIXUEFU_10';

--查看创建过的所有表
select table_name from user_tables;

--复杂视图
--视图对应的子查询中含有函数，表达式，分组或多表关联查询，去重等操作时，该视图就是一个复杂视图。
--复杂视图不能进行DML操作。

--创建一个部门工资情况的视图？最高，最低，平均，总和工资，部门名以及部门号？

create view v_dept_shixuefu_sal
as
select max(e.sal) max_sal,
       min(e.sal) min_sal,
       avg(e.sal) avg_sal,
       d.deptno,
       d.dname
from emp_shixuefu e,dept_shixuefu d
where e.deptno=d.deptno
group by d.deptno,d.dname;
select * from v_dept_shixuefu_sal;
--查看谁的工资高于部门平均工资？
select e.ename,e.sal,v.deptno
from emp_shixuefu e,v_dept_shixuefu_sal v
where e.deptno=v.deptno
and e.sal>v.avg_sal;

--删除视图
--删除视图中的数据会对应的将基表数据删除，但是删除视图本身，并不会影响基表数据。
drop view v_emp_shixuefu_10;

--序列
--序列也是数据库对象之一，作用是生成一组数字。
--序列常用于为标的主键字段生成值使用。
create sequence seq_emp_sxf_id
start with 100
increment by 10;

--序列支持两个伪列：
--nextval：获取序列下一个数字
--currval：获取序列当前数字
--序列是不能回退的，当使用nextval获取下一个数字就会导致序列发生步进。
--currval是获取序列最后一次生成的数字，不会导致步进，但是新创建的序列
--必须至少调用一次nextval后才可以使用。

select seq_emp_sxf_id.nextval
from dual;

insert into emp_shixuefu
(empno,ename,sal,job,deptno)
values
(seq_emp_sxf_id.nextval,'rose',3000,'CLERK',10);

select * from emp_shixuefu;

--UUID:32位不重复字符串，也是主键生成方式的一种。
select sys_guid() from dual;
--删除序列
drop sequence seq_emp_sxf_id;

--创建索引
--单一字段加索引
create index idx_emp_shixuefu_ename on emp_shixuefu(ename);
--复合索引
--注意：排序有优先级，创建索引时列的顺序要和排序时列的顺序一致
create index idx_emp_shixuefu_job_sal on emp_shixuefu(job,sal);
select empno,ename,sal,job from emp_shixuefu
order by job,sal;
--对函数加索引
create index emp_shixuefu_ename_upper_idx
on emp_shixuefu(upper(ename));
select * from emp_shixuefu
where upper(ename)='KING';
--修改和删除索引
--如果经常在索引列上执行DML操作，需要定期重建索引。
alter index idx_emp_shixuefu_ename rebuild;
--删除索引
drop index idx_emp_shixuefu_ename;

--约束
--非空约束：是一个列级约束,建表(定义列)或修改列时加非空约束。
create table employees_shixuefu(
eid number(6),
name varchar2(30) not null,
salary number(7,2),
hiredate date
   constraint employees_shixuefu_hiredate_nn not null
);
desc employees_shixuefu
--修改表时添加非空约束
alter table employees_shixuefu
modify(eid number(6) not null);
--取消非空约束
alter table employees_shixuefu
modify(eid number(6) null);

--唯一性约束：既是列级约束，又是表级约束
create table emp2_sxf(
eid number(6) ,
name varchar2(30) not null,
email varchar2(50),
salary number(7,2),
hiredate date,
constraint emp2_sxf_email_uk unique(email)
);
desc emp1_sxf

insert into emp2_sxf
(eid,name,email)
values
(111,'ggg','akakk1.com');
select * from emp2_sxf;
--在建表之后增加唯一性约束
alter table emp2_sxf
add constraint emp2_sxf_name_uk unique(name);

delete from emp2_sxf;

--主键约束,非空且唯一
create table emp3_sxf(
eid number(6) primary key,
name varchar2(30),
email varchar2(50),
salary number(7,2),
hiredate date
);
desc emp3_sxf

--检查约束
alter table emp3_sxf
add constraint emp3_sxf_salary_check
check(salary>2000);
insert into emp3_sxf
(eid,salary)
values
(112,2090);


create table myemployee_shixuefu(
id number(4) primary key,
name varchar2(20) not null,
birthday date,
telephone varchar2(11) unique,
score number(9,2) check(score>=0)
);