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;
每个人都是在努力的路上,别因为别人的误解而放弃,,术业有专攻,如是而已。