Oracle视图

--视图
create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5)
);

create table teacher(
tno varchar2(10) primary key,
tname varchar2(20)
);

create table course(
cno varchar2(10),
cname varchar2(20),
tno varchar2(20),
constraint pk_course primary key (cno,tno)
);

create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2), 
constraint pk_sc primary key (sno,cno)
);




/*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');

/******************初始化教师表***********************/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');

/***************初始化课程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS ','t001');
insert into course values ('c009','PHP ','t003');
insert into course values ('c010','EJB3.0','t002');

/***************初始化成绩表***********************/
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');


/*
假如说： 1 知道你的名字  2 我知道你的课程名称 3 我知道你的成绩
scs--> 成绩视图（虚拟的表）
*/
insert into sc values (
    select sno from student where sname ='%',
    select cno from course where cname='%'
    ,78.9);

insert into scs values( '张三','J2SE','99')

/*
Question:

0.   查询所有表中的信息
*/
select * from student;
select * from teacher;
select * from course;
select * from sc;


/*
问题：  假如说  如果万恶的教务处 要给你们的家长发送成绩单？
怎么做？
1 查询 
2 我直接用一张表存成绩单（占用了空间，同时还有重复的字段）
3 如果有一张虚拟的表 存储对应的字段 并且把实体的数据映射过来

1  什么是视图
由基表映射来的一张虚拟表
这个表可以看作是一个查询结果
1.1 单张表的查询
1.2 多张表查询
1.3 视图的查询

*/

/*

创建单表视图
创建多表视图
创建视图的视图
创建没有源表的视图（查询结果）
对视图的操作（更删改查）

*/

CREATE OR REPLACE   VIEW  VIEW_NAME

AS SELECT ----
   FROM --
   WHERE ---

--最简单的视图
--创建表的语句
create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5)
);

--student 视图
CREATE VIEW students
as select * from student;

CREATE OR REPLACE VIEW students
as select sno ,sname from student;

select sno ,sname  from student;
--OR REPLACE : 强制的覆盖视图
-- 总结： 视图是根据查询的结果来创建的
--创建多表视图
--查找成绩良好（分数>80）的学员名称和课程名称


--1 写出多个表的查询语句
select 
(select sname from student where student.sno=sc.sno) ,
(select cname from course where course.cno=sc.cno)  
from sc where score>80;
-- 2 根据查询语句来创建视图 
create OR REPLACE VIEW goodstudent 
as select 
(select sname from student where student.sno=sc.sno) 姓名,
(select cname from course where course.cno=sc.cno) 课程名,
score 成绩
from sc where score>80;



create OR REPLACE VIEW goodstudents 
as select 
(select sname from student where student.sno=sc.sno),
(select cname from course where course.cno=sc.cno),
score
from sc where score>80;

--2.1 创建不及格需要补考的学生的试题
create VIEW badstudent
as select 
(select sname from student where student.sno=sc.sno) 姓名,
(select cname from course where course.cno=sc.cno)  课程名,
score 成绩
from sc where score<60;
--3 根据视图来创建视图
--students goodstudent

create VIEW id_goodstudent
as
select sno 学号,姓名,成绩 
from goodstudent g,students s
where g.姓名=s.sname;

/*
刚才创建的时候为什么一直出错？？
注意表头！！
*/

/*
对视图更删改查
*/
select

insert into students values('s011','二柱子');

--insert into goodstudent values('二柱子'，'J2SE','90');
-- 视图的插入受到表结构的限制  注意

update students set sname='小王' where sno='s011';

update goodstudent set 成绩=20 where 姓名 ='李四';


delete from students where sname='小王';

--视图的修改
  alter VIEW goodstudent rename column 姓名 to sname;
 --删除视图
 drop VIEW id_goodstudent;
 drop VIEW goodstudent;
 drop VIEW badstudent;
 drop VIEW students;