oracle创建视图
发布网友
共4个回答
热心网友
1段
create view V_StudInfo
as
select a.学号,a.姓名,b.课程号,b.课程名,
case when c.成绩 between 90 and 100 then '优'
when c.成绩 between 80 and then '良'
when c.成绩 between 70 and 79 then '中'
when c.成绩 between 60 and 69 then '及格'
else '不及格' end 成绩等级
from 学生表 a,课程表 b, 成绩表 c where a.学号=c.学号
and b.课程号=c.课程号2段
create view V_Stud
as
select a.学号,a.姓名,count(*) 所修科目数,avg(成绩) 平均成绩
from 学生表 a,课程表 b, 成绩表 c where a.学号=c.学号
and b.课程号=c.课程号
and a.学号 in
(select a.学号
from 学生表 a,课程表 b, 成绩表 c where a.学号=c.学号
and b.课程号=c.课程号 and b.课程名='英语' and c.成绩>75)
扩展资料：
oracle视图总结
创建视图时在子查询中给列定义别名：在选择视图中的列时应使用别名
<span style="font-size:14px;">CREATE VIEW salvu50
AS
SELECT employee_id ID_NUMBER,
last_name NAME,
salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;
View created.</span>
查询视图：SELECT * FROM salvu50;
修改视图：使用CREATE OR REPLACE VIEW 子句修改视图
<span style="font-size:14px;">CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' ' || last_name,
salary, department_id
FROM employees
WHERE department_id = 80;
View created.</span>
CREATE VIEW 子句中各列的别名应和子查询中各列相对应
创建复杂视图举例：
<span style="font-size:14px;">CREATE VIEWdept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
View created.</span>
删除视图：删除视图只是删除视图的定义，并不会删除基表的数据
<span style="font-size:14px;">DROP VIEW empvu80;
View dropped.</span>
热心网友
score.chinese+score.math+score.english+score.complex
这个是number吗，最好改下
sum(to_number(score.chinese)+to_number(score.math)+to_number(score.english)+to_number(score.complex))
类型匹配
后面也是，我就不咧了
热心网友
create or replace view view_will as
select college.collegeid,mat.stuid,sum(score.chinese+score.math+score.english+score.complex)
from college,mat,score
where college.collegeid=mat.first_will and mat.stuid=score.stuid
group by score.stuid,mat.stuid having sum(score.chinese+score.math+score.english+score.complex)>500;
热心网友
CREATE or REPLACE view view_will as
select college.collegeid,mat.stuid,sum(score.chinese+score.math+score.english+score.complex)
from college,mat,score
where college.collegeid=mat.first_will and mat.stuid=score.stuid
group by SCORE.STUID,COLLEGE.COLLEGEID,MAT.STUID having
sum(score.chinese+score.math+score.english+score.complex)>500
