MySQL中資料檢視操作詳解

2022-08-05 14:05:30

1.檢視概述

檢視是從一個或多個表（或檢視）匯出的表。檢視與表（有時為與檢視區別，也稱表為基本表）不同，檢視是一個虛表，即檢視所對應的資料不進行實際儲存，資料庫中只儲存檢視的定義，對檢視的資料進行操作時，系統根據檢視的定義去操作與檢視相關聯的基本表。

檢視一經定義，就可以像表一樣被查詢、修改、刪除和更新。使用檢視有下列優點：

1、為使用者集中資料，簡化使用者的資料查詢和處理。有時使用者所需要的資料分散在多個表中，定義檢視可將它們集中在一起，從而方便使用者的資料查詢和處理。

2、遮蔽資料庫的複雜性。使用者不必瞭解複雜的資料庫中的表結構，並且資料庫表的更改也不影響使用者對資料庫的使用。

3、簡化使用者許可權的管理。只須授予使用者使用檢視的許可權，而不必指定使用者只能使用表的特定列，也增加了安全性。

4、便於資料共用。各使用者不必都定義和儲存自己所需的資料，可共用資料庫的資料，這樣同樣的資料只需儲存一次。

5、可以重新組織資料以便輸出到其他應用程式中。

使用檢視時，要注意下列事項：

1、在預設情況下，將在當前資料庫建立新檢視。要想在給定資料庫中明確建立檢視，建立時應將名稱指定為db_name.view_name

2、檢視的命名必須遵循標誌符命名規則，不能與表同名，且對每個使用者檢視名必須是唯一的，即對不同使用者，即使是定義相同的檢視，也必須使用不同的名字。

3、不能把規則、預設值或觸發器與檢視相關聯

4、不能在檢視上建立任何索引，包括全文索引

5、檢視中使用SELECT語句有以下的限制：

5.1、定義檢視的使用者必須對所參照的表或檢視有查詢（即可執行SELECT語句）的許可權，在定義中參照的表或檢視必須存在

5.2、不能包含FROM子句中的子查詢，不能參照系統或使用者變數，不能參照預處理語句引數

5.3、在檢視定義中允許使用ORDER BY子句，但是，如果從特定檢視進行了選擇，而該檢視使用了具有自己ORDER BY的語句，則檢視定義中的ORDER BY將被忽略。

1.1建立檢視

檢視的建立語法格式：

CREATE [ OR REPLACE ] VIEW 檢視名 [ ( 列名列表 ) ]
AS SELECT 語句
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

例：建立Bookstore資料庫上的jsj_sell檢視，包括計算機類圖書銷售的訂單號、圖書編號、書名等情況。其中要保證對該檢視的訂單修改都要符合計算機類這個條件

CREATE OR REPLACE VIEW jsj_sell
AS
SELECT 訂單號,Sell.圖書編號,書名
FROM Book,Sell
WHERE Book.圖書編號=Sell.圖書編號
AND Book.圖書類別='計算機'
WITH CHECK OPTION;

例：建立Bookstore資料庫中計算機類圖書銷售檢視sale_avg，包括書名（在檢視中列名為name）和該圖書的平均訂購冊數（在檢視中列名為sale_avg）

CREATE VIEW sale_avg(name,sale_avg)
AS 
SELECT 書名,avg(訂購冊數)
FROM jsj_sell
GROUP BY 書名;

上面建立了計算機類圖書銷售檢視jsj_sell，可以直接從jsj_sell檢視中查詢資訊生成新檢視。

1.2檢視的查詢

檢視定義後，就可以像查詢基本表那樣對檢視進行查詢

例：在檢視jsj_sell中查詢計算機類圖書的訂單號和訂購冊數

SELECT 訂單號,訂購冊數
FROM jsj_sell;

例：查詢平均訂購冊數大於5本的訂購客戶的身份證號和平均訂購冊數

1、建立客戶平均訂購檢視kh_avg，包括客戶身份證號和平均訂購冊數

CREATE VIEW kh_avg(sfz,order_avg)
AS
SELECT 身份證號,AVG(訂購冊數)
FROM Sell
GROUP BY 身份證號;

2、對kh_avg檢視進行查詢

SELECT *
FROM kh_avg
WHERE order_avg>5;

注意：使用檢視查詢時，若其關聯的基本表中新增了新欄位，則該檢視將不包含新欄位。如果與檢視相關聯的表或檢視被刪除，則該檢視將不能再使用。

2.操作檢視

2.1通過檢視運算元據

2.1.1可更新檢視

要通過檢視更新基本表資料，必須保證檢視是可更新檢視，即可以在INSET、UPDATE或DELETE等語句中使用它們。對於可更新的檢視，在檢視中的行和基表中的行之間必須具有一對一的關係。有一些特定的其他結構，這類結構會使得檢視不可更新。若檢視包含以下結構中的任何一種，其就是不可更新的：

1、聚合函數

2、DISTINCT關鍵字

3、GROUP BY子句

4、ORDER BY子句

5、HAVING子句

6、UNION運運算元

7、位於選擇列表中的子查詢

8、FROM子句中包含多個表

9、SELECT語句中參照了不可更新檢視

10、WHERE子句中的子查詢，參照FROM子句中的表

MySQL在多表上建立檢視方法

2.1.2插入資料

當使用檢視插入資料時，如果在建立檢視時加上WITH CHECK OPTION子句，WITH CHECK OPTION子句會在更新資料時檢查新資料是否符合檢視定義中WHERE子句的條件。

WITH CHECK OPTION子句只能和可更新檢視一起使用。

例：建立檢視jsj_book，檢視中包含計算機類圖書的資訊，並向jsj_book檢視中插入一條記錄：（'IS-01'，'計算機'，'計算機基礎'）

1、建立檢視jsj_book

CREATE OR REPLACE VIEW jsj_book
AS
SELECT *
FROM Book
WHERE 圖書類別='計算機'
WITH CHECK OPTION;

2、插入記錄

INSERT INTO jsj_book
VALUES('IS-01','計算機','計算機基礎');

當檢視所依賴的基本表有多個時，不能向該檢視插入資料，因為這將會影響多個基本表。

對INSERT語句還有一個限制：SELECT語句中必須包含FROM子句中指定表的所有不能為空的列。例如，若jsj_book檢視定義時不加上“書名”欄位，則插入資料時會出錯。

2.1.3修改資料

使用UPDATE語句可以實現通過檢視修改基本表資料。

例：將jsj_sell檢視中的圖書編號為IS-01的書名改為“MySQL基礎”

UPDATE jsj_sell
SET 書名='MySQL基礎'
WHERE 圖書編號='IS-01';

2.1.4刪除資料

如果檢視來源於單個基本表，可以使用DELETE語句通過檢視來刪除基本表資料，對於依賴多個基本表的檢視，不能使用DELETE語句。

例：刪除jsj_book中“中國青年出版社”的記錄

DELETE FROM jsj_book
WHERE 出版社='中國青年出版社';

2.2修改檢視定義

可以使用ALTER VIEW語句對已有檢視的定義進行修改

語法格式：

ALTER VIEW 檢視名[ ( 列名列表 ) ]
AS select語句
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

例：將jsj_book檢視修改為只包含計算機類圖書的圖書編號、書名和單價

ALTER VIEW jsj_book
AS
SELECT 圖書編號,書名,單價
FROM Book
WHERE 圖書類別='計算機';

2.3刪除檢視

語法格式：DROP VIEW [ IF EXISTS ] 檢視名1 [, 檢視名2 ]...

若宣告了IF EXISTS，則檢視不存在的話也不會報錯。使用DROP VIEW 一次可以刪除多個檢視。例：一次性刪除jsj_book和jsj_sell

DROP VIEW jsj_book,jsj_sell;

到此這篇關於MySQL中資料檢視操作詳解的文章就介紹到這了,更多相關MySQL資料檢視內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！