View (視圖、檢視) 是一種虛擬表單，使用時就像是一個資料表單 (Table)，可以用來保存一段你指定的查詢敘述。因為有些表單是結合許多資料表單而成，如果經常用到都要用複雜的語法再叫出資料，會顯得很麻煩，因此View就是一個方便的方式。
View的建立
建立View的語法如下 :
CREATE [or REPLACE] VIEW view_name [(column_list)]
AS SELECT_statement
例如有前面文章提到的選課結構，我們就可以把幾個資料表單結合起來如下 :
select c.id as 課程編號,c.cname as 課程,d.dname as 開課科系 from courses c
join departments d on d.id=c.department_id;
以上結合了課程 (courses) 與科系 (departments) 兩個資料表，執行結果如下 :
使用View來做，就可以建立檢視表單如下 :
CREATE VIEW course_department (course_id, course_name, department_name)
AS
select c.id as 課程編號,c.cname as 課程,d.dname as 開課科系 from courses c
join departments d on d.id=c.department_id;
然後我們就可以把course_department當成資料表單來，做以下的執行 :
select * from course_department;
執行結果如下 :
select * from course_department where course_name like "%數學%";
執行結果如下 :
如果我們結合更多資料表單如下 :
select c.cname as 課程,c.id as 課程編號, oc.id as 開課編號,d.dname as 開課科系,t.tname as 開課老師, cs.slot as 開課時段 from courses c
join departments d on c.department_id=d.id
join open_courses oc on oc.course_id=c.id
join teachers t on t.id=oc.teacher_id
join courses_schedule2 cs on cs.oc_id=oc.id;
執行結果如下 :
我們使用View來做的話
CREATE VIEW all_course (course_name, course_id, open_course_id, department_name, teacher_name, course_slot)
AS
select c.cname as 課程,c.id as 課程編號, oc.id as 開課編號,d.dname as 開課科系,t.tname as 開課老師, cs.slot as 開課時段 from courses c
join departments d on c.department_id=d.id
join open_courses oc on oc.course_id=c.id
join teachers t on t.id=oc.teacher_id
join courses_schedule2 cs on cs.oc_id=oc.id;
就可以用來執行
select * from all_course;
執行結果如下 :
select * from all_course where course_name like "%數學%";
執行結果如下 :
以View的方式，是不是把擷取資料的語法簡化了許多呢?
原本是這樣的語法
select c.cname as 課程,c.id as 課程編號, oc.id as 開課編號,d.dname as 開課科系,t.tname as 開課老師, cs.slot as 開課時段 from courses c
join departments d on c.department_id=d.id
join open_courses oc on oc.course_id=c.id
join teachers t on t.id=oc.teacher_id
join courses_schedule2 cs on cs.oc_id=oc.id;
使用View的方式直接變成
select * from all_course;
並且當原始結合的資料表有變更時，View (檢視資料表)當然的就會跟著變更。
例如我們再插入一個教師，並讓他開某門課程 :
insert into teachers (department_id, tname) values (2, "孫五");
insert into open_courses (teacher_id, course_id, classroom) values (5, 8, "Room010");
insert into courses_schedule2 (oc_id, slot) values (10, 5);
insert into courses_schedule2 (oc_id, slot) values (10, 6);
再來執行
select * from all_course;
執行結果如下 :
刪除View
Drop view view_name;
Drop view if exists view_name;
更新View的結構
假如我要變動View的結構，可以執行如下
CREATE or REPLACE VIEW course_department (cid, cname, dname)
AS
select c.id as 課程編號,c.cname as 課程,d.dname as 開課科系 from courses c
join departments d on d.id=c.department_id;
這個View的欄位就變成了 cid, cname, dname 執行結果如下 :
ALTER VIEW course_department (c_id, c_name, d_name)
AS
select c.id as 課程編號,c.cname as 課程,d.dname as 開課科系 from courses c
join departments d on d.id=c.department_id;
這個View的欄位就變成了 c_id, c_name, d_name 執行結果如下 :
View是否可以使用INSERT、UPDATE、DELETE指令
至於View能否使用INSERT、UPDATE、DELETE指令呢? 要看View的定義，如上面的View，大都不能使用INSERT、UPDATE、DELETE指令。
那什麼樣子的View可以使用INSERT、UPDATE、DELETE指令?
假設有一個資料表 quot，結構如下 :
CREATE TABLE quot (
product varchar(20),
quantity INT,
unit_price INT);
現在建立一個View，結構如下 :
CREATE VIEW quot_view
AS
select product, quantity, unit_price, quantity*unit_price as total from quot;
先插入幾筆資料到quot
insert into quot values ("產品1", 10, 50), ("產品2", 20, 10), ("產品3", 5, 5);
如果抓出quot_view
select * from quot_view;
執行結果如下 :
update quot_view set quantity=100 where product="產品1";
我再 select * from quot_view 看看
執行結果如下 :
顯然View也是可以更新的，但是有一些條件。
如果我執行指令去更新course_department
update course_department set d_name="資訊管理系" where d_name="資管系";
select * from course_department;
執行結果如下 :
但是如果你要插入資料
insert into course_department values (11,'邏輯工程學','電機系');
執行結果如下 :
為何同樣針對course_department ，可以update卻不能insert呢? 主要是因為這個View的情況下
update不會破壞資料的一致性，但是insert會破壞資料的一致性。
至於什麼是資料的一致性? 何時會破壞? 何時不會破壞? 應該如何判斷呢? 你自己思考看看吧。
思考看看以下的指令 :
(1)
CREATE VIEW course_department (course_id, course_name, department_name)
AS
select c.id as 課程編號,c.cname as 課程,d.dname as 開課科系 from courses c
join departments d on d.id=c.department_id;
是否可以使用以下的指令 :
(X) INSERT INTO course_department (course_id, course_name, department_name)
VALUES (11, "資管概論", "資管系");
(O) UPDATE course_department SET course_name="測試" WHERE course_id=1;
(X) DELETE FROM course_department WHERE course_id=1;
(2)
CREATE VIEW all_course (course_name, course_id, open_course_id, department_name, teacher_name, course_slot)
AS
select c.cname as 課程,c.id as 課程編號, oc.id as 開課編號,d.dname as 開課科系,t.tname as 開課老師, cs.slot as 開課時段 from courses c
join departments d on c.department_id=d.id
join open_courses oc on oc.course_id=c.id
join teachers t on t.id=oc.teacher_id
join courses_schedule2 cs on cs.oc_id=oc.id;
是否可以使用以下的指令 :
(X) INSERT INTO all_course (course_name, course_id, open_course_id, department_name, teacher_name, course_slot)
values ("測試", 20, 20, "電機系", "李三", 30);
(O) UPDATE all_course SET course_name="更改的課程名稱" WHERE course_id=10;
(X) DELETE FROM all_course WHERE course_id=5;
(3)
我們可以對表單建立index，例如
CREATE INDEX cname ON courses (cname);
移除index使用drop index cname on courses;
CREATE UNIQUE INDEX dname ON departments (dname);
移除index使用drop index dname on departments;
那麼你可以這樣做嗎?
(X) CREATE INDEX open_course_id ON all_course (open_course_id);
有沒有準則可以遵循? 那些View的操作是不允許的呢?
準則1 : 通常去DELETE 或 INSERT View中的資料是沒有意義，而且大多會破壞資料一致性。需要刪除或插入資料應該從表單去處理比較妥當。
準則2 : 具有JOIN的View有些可以UPDATE，但是有些不行，原因也是因為資料一致性。
判斷View是否可以UPDATE，有以下條件 :
(1) 僅涉及單個基本表的更改：如果 UPDATE 操作僅涉及 view 中的一個基本表，則應該可以進行 UPDATE。如果 UPDATE 操作涉及多個基本表，則可能無法執行。
(2) 無聚合函數：view 中不應包含聚合函數（如 COUNT、SUM、AVG 等），因為聚合函數會阻止對 view 進行 UPDATE。
(3) 無 DISTINCT、GROUP BY 和 HAVING 子句：view 中不應包含 DISTINCT、GROUP BY 或 HAVING 子句，因為這些子句會影響更新操作。
(4) 無 UNION 或 UNION ALL 子句：view 中不應包含 UNION 或 UNION ALL 子句，因為這些子句會將多個 SELECT 查詢的結果組合在一起，使更新操作變得不確定。
(5) 無子查詢：view 的 SELECT 子句中不應包含子查詢，因為子查詢可能使 view 變得不可更新。
? ){kind=link}
0 留言