MySQL如何使用View(視圖)?

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的結構 :

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;

執行結果如下 :


如果我去更新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 變得不可更新。

張貼留言

0 留言