Ex26.1
一、什么是视图。(知识点:视图定义,P111-112)
二、导入school_DB数据库,做以下操作:
- 创建视图
– 2.创建视图VW_DEPART_MAJOR:查询院部专业信息。(知识点:CREATE VIEW语句,P112-113)
– 3.创建STUDENTS表的视图vw_student_1,显示学号、姓名、班级名称、专业名称。
– 4.创建STUDENTS表的视图vw_student_2,显示学号、姓名、班级名称、专业名称、院部名称。 - 查看视图详细信息
– 5.查看vw_student_1、vw_student_2视图信息。(知识点:DESCRIBE/SHOW CREATE VIEW语句,P113-114) - 修改视图
– 6.给vw_student_1视图添加STUDENTS表的ID字段。(知识点:ALTER VIEW语句,P115)
– 删除视图
– 7.删除vw_student_1、vw_student_2视图。(知识点:DROP VIEW语句,P116)
一、.什么是视图。(知识点:视图定义,P111-112)
/MySQL 视图(View),是一种虚拟存在的表,同真实表一样,视图也由列和行构成/
二、
# 创建视图
# 2.创建视图VW_DEPART_MAJOR:查询院部专业信息。(知识点:CREATE VIEW语句,P112-113)
CREATE VIEW VW_DEPART_MAJOR
AS
SELECT majors.id m_id,majors.mname m_name,majors.type m_type,departs.id departid,departs.dname d_name,departs.type d_type
FROM majors, departs
WHERE majors.departid=departs.id;
SELECT * FROM VW_DEPART_MAJOR;
# 3.创建STUDENTS表的视图vw_student_1,显示学号、姓名、班级名称、专业名称。
CREATE VIEW vw_student_1
AS
SELECT students.sno sno,students.sname sname,mclasss.mname class_name, majors.mname major_name
FROM students,mclasss,majors
WHERE students.mclassid=mclasss.id AND mclasss.majorid=majors.id;
SELECT * FROM vw_student_1;
# 4.创建STUDENTS表的视图vw_student_2,显示学号、姓名、班级名称、专业名称、院部名称。
CREATE VIEW vw_student_2
AS
SELECT students.sno,students.sname,mclasss.mname cla_name,majors.mname maj_name,departs.dname
FROM students,mclasss,majors,departs
WHERE students.mclassid=mclasss.id AND mclasss.majorid=majors.id AND majors.departid=departs.id;
SELECT * FROM vw_student_2;
# 查看视图详细信息
# 5.查看vw_student_1、vw_student_2视图信息。(知识点:DESCRIBE/SHOW CREATE VIEW语句,P113-114)
DESC vw_student_1;
DESC vw_student_2;
SHOW CREATE VIEW vw_student_1;
SHOW CREATE VIEW vw_student_2;
# 修改视图
-- 6.给vw_student_1视图添加STUDENTS表的ID字段。(知识点:ALTER VIEW语句,P115)
ALTER VIEW vw_student_1
AS
SELECT students.id,students.sno sno,students.sname sname,mclasss.mname class_name, majors.mname major_name
FROM students,mclasss,majors
WHERE students.mclassid=mclasss.id AND mclasss.majorid=majors.id;
-- 删除视图
-- 7.删除vw_student_1、vw_student_2视图。(知识点:DROP VIEW语句,P116)
DROP VIEW vw_student_1,vw_student_2;