MySQL與Oracle視圖：深入解析與全面對比

視圖概念

????????視圖在 MySQL 與Oracle中本質上是一種虛擬表，其數據并非實際存儲，而是基于一個或多個基礎表的查詢結果動態生成。它像是對復雜查詢的一種封裝，極大地簡化了數據的查詢操作。例如，當我們需要頻繁從多個關聯表中獲取特定數據時，如果每次都編寫復雜的 JOIN 查詢語句，不僅繁瑣，而且容易出錯。通過創建視圖，將這些復雜查詢邏輯封裝起來，后續只需像查詢普通表一樣查詢視圖，就能輕松獲取所需數據，大大提高了開發效率。?

同時，視圖在數據安全方面也發揮著重要作用。我們可以通過視圖只向用戶暴露部分數據，隱藏基礎表中的敏感信息，如用戶表中的密碼字段等。此外，視圖還提供了邏輯抽象，即使底層表結構發生變化，只要視圖定義不變，應用程序對數據的訪問方式就無需改變，增強了系統的穩定性和可維護性。

視圖作用

? ? ? ? 1.使操作簡單化

????????視圖需要達到的目的就是所見即所需。視圖不僅可以簡化用戶對數據的理解，也可以簡化他們的操作。那些被經常使用的查詢可以被定義為視圖，從而使得用戶不必為以后的操作每次指定全部條件。（視圖存放的是查詢語句，通過查詢視圖可以直接查看到該查詢語句查詢出的結果，不必再次輸入查詢語句，即操作簡單化。）

? ? ? ? 2.增加數據的安全性

? ? ? ? 通過視圖，用戶只能查詢和修改指定的數據。指定數據之外的信息，用戶接觸不到。這樣可以防止敏感信息被未授權的用戶查看，增強機密信息的安全性。

? ? ? ? 3.提高表的邏輯獨立性

? ? ? ? 視圖可以屏蔽原有表結構變化帶來的影響。例如：原有表增加列或刪除未被引用的列，對視圖不會造成影響。同樣，如果修改表中的某些列，可以使用修改視圖來解決這些列帶來的影響。

視圖基本操作?

（視圖操作前置代碼）

-- MySQL
-- 創建學生表，表名改為 stu
CREATE TABLE stu (sid INT AUTO_INCREMENT PRIMARY KEY,  -- 學生IDsname VARCHAR(100) NOT NULL,         -- 學生姓名gender ENUM('Male', 'Female') NOT NULL, -- 性別dob DATE                             -- 出生日期
);-- 插入示例數據到學生表
INSERT INTO stu (sname, gender, dob) VALUES
('Alice', 'Female', '2001-05-12'),
('Bob', 'Male', '2000-08-23'),
('Charlie', 'Male', '2002-01-30');-- 創建課程表，表名改為 co
CREATE TABLE co (cid INT AUTO_INCREMENT PRIMARY KEY,  -- 課程IDcname VARCHAR(100) NOT NULL,         -- 課程名稱credits INT NOT NULL                 -- 學分
);-- 插入示例數據到課程表
INSERT INTO co (cname, credits) VALUES
('Mathematics', 4),
('Physics', 3),
('Chemistry', 3);-- 創建成績表，表名改為 sc
CREATE TABLE sc (scid INT AUTO_INCREMENT PRIMARY KEY, -- 成績IDsid INT NOT NULL,                    -- 學生IDcid INT NOT NULL,                    -- 課程ID成績 DECIMAL(3,1),                   -- 成績（數字類型）FOREIGN KEY (sid) REFERENCES stu(sid),FOREIGN KEY (cid) REFERENCES co(cid)
);-- 插入示例數據到成績表
INSERT INTO sc (sid, cid, score) VALUES
(1, 1, 90.5),
(1, 2, 85.0),
(2, 1, 82.0),
(2, 3, 88.5),
(3, 2, 78.5),
(3, 3, 85.0);-- Oracle
-- 創建學生表，表名 stu
CREATE TABLE stu (sid NUMBER PRIMARY KEY,              -- 學生ID（序列生成）sname VARCHAR2(100) NOT NULL,        -- 學生姓名gender VARCHAR2(10) NOT NULL,        -- 性別（使用 VARCHAR2 替代 ENUM）dob DATE                             -- 出生日期
);-- 創建序列用于生成學生ID
CREATE SEQUENCE stu_seq START WITH 1 INCREMENT BY 1;-- 創建觸發器自動填充學生ID
CREATE OR REPLACE TRIGGER stu_before_insert
BEFORE INSERT ON stu
FOR EACH ROW
BEGINSELECT stu_seq.NEXTVAL INTO :NEW.sid FROM DUAL;
END;
/-- 插入示例數據到學生表
INSERT INTO stu (sname, gender, dob) VALUES
('Alice', 'Female', TO_DATE('2001-05-12', 'YYYY-MM-DD')),
('Bob', 'Male', TO_DATE('2000-08-23', 'YYYY-MM-DD')),
('Charlie', 'Male', TO_DATE('2002-01-30', 'YYYY-MM-DD'));-- 創建課程表，表名 co
CREATE TABLE co (cid NUMBER PRIMARY KEY,              -- 課程ID（序列生成）cname VARCHAR2(100) NOT NULL,        -- 課程名稱credits NUMBER NOT NULL              -- 學分
);-- 創建序列用于生成課程ID
CREATE SEQUENCE co_seq START WITH 1 INCREMENT BY 1;-- 創建觸發器自動填充課程ID
CREATE OR REPLACE TRIGGER co_before_insert
BEFORE INSERT ON co
FOR EACH ROW
BEGINSELECT co_seq.NEXTVAL INTO :NEW.cid FROM DUAL;
END;
/-- 插入示例數據到課程表
INSERT INTO co (cname, credits) VALUES
('Mathematics', 4),
('Physics', 3),
('Chemistry', 3);-- 創建成績表，表名 sc
CREATE TABLE sc (scid NUMBER PRIMARY KEY,             -- 成績ID（序列生成）sid NUMBER NOT NULL,                 -- 學生IDcid NUMBER NOT NULL,                 -- 課程IDscore NUMBER(3,1),                   -- 成績（修改列名并使用 NUMBER 類型）FOREIGN KEY (sid) REFERENCES stu(sid),FOREIGN KEY (cid) REFERENCES co(cid)
);-- 創建序列用于生成成績ID
CREATE SEQUENCE sc_seq START WITH 1 INCREMENT BY 1;-- 創建觸發器自動填充成績ID
CREATE OR REPLACE TRIGGER sc_before_insert
BEFORE INSERT ON sc
FOR EACH ROW
BEGINSELECT sc_seq.NEXTVAL INTO :NEW.scid FROM DUAL;
END;
/-- 插入示例數據到成績表
INSERT INTO sc (sid, cid, score) VALUES
(1, 1, 90.5),
(1, 2, 85.0),
(2, 1, 82.0),
(2, 3, 88.5),
(3, 2, 78.5),
(3, 3, 85.0);

查看創建視圖的權限

MySQL：

????????創建視圖需要具有CREATE VIEW權限。同時具有涉及的列的SELECT權限。可以使用SELECT語句來查詢這些權限信息。

SELECT Select_priv,Create_view_priv FROM mysql.user WHERE user='用戶名';

參數說明

（1）Select_priv：屬性表示用戶是否具有SELECT權限，Y表示擁有SELECT權限，N表示沒有。

（2）Create_view_priv：屬性表示用戶是否具有CREATE VIEW權限；

（3）mysql.user：表示MySQL數據庫下面的user表。

（4）用戶名：參數表示要查詢是否擁有權限的用戶，該參數需要用單引號引起來。

Oracle：

在 Oracle 中，查看和管理創建視圖的權限涉及系統權限、角色和對象權限的綜合查詢。

-- 1. 檢查用戶是否有CREATE VIEW系統權限
SELECT privilege
FROM dba_sys_privs
WHERE grantee = '用戶名'AND privilege IN ('CREATE VIEW', 'CREATE ANY VIEW');-- 2. 檢查用戶是否通過角色獲得權限
SELECT rp.grantee, rp.role, sp.privilege
FROM dba_role_privs rp
JOIN dba_sys_privs sp ON rp.granted_role = sp.grantee
WHERE rp.grantee = '用戶名'AND sp.privilege IN ('CREATE VIEW', 'CREATE ANY VIEW');

創建視圖語句

MySQL：

create [or replace] [algorithm={undefied|merge|temptable}]
view 視圖名 [(屬性清單)]
as select 語句
[with [cascaded|local] check option];

參數說明：

（1）algorithm:可選項，表示視圖選擇的算法。

（2）視圖名：表示要創建的視圖名稱。

（3）屬性清單：可選項，指定視圖中各個屬性的名詞，默認與select 語句中的查詢的屬性相同。

（4）select語句:表示一個完整的查詢語句，將查詢記錄導入視圖中。

（5）with check option：可選項，表示更新視圖時要保證在該視圖的權限范圍內。

Oracle：

CREATE [OR REPLACE] 
VIEW 視圖名 [(列名1, 列名2, ...)]
AS 
SELECT 查詢語句
[WITH CHECK OPTION [CONSTRAINT 約束名]];

與MySQL對比：

ALGORITHM 子句：
????????Oracle 不支持 MySQL 的?ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}?選項。Oracle 會自動優化視圖執行計劃，無需手動指定算法。
CHECK OPTION 語法：
- Oracle 支持?WITH CHECK OPTION，但沒有?CASCADED/LOCAL?關鍵字。
- 可通過?CONSTRAINT?為檢查約束命名（可選）。
視圖列命名：
與 MySQL 相同，可在視圖名后顯式指定列名列表。

例：創建視圖

-- MySQL  Oracle
create view v1 as SELECT s.sname, c.cname, sc.成績
FROM sc
JOIN stu s ON sc.sid = s.sid
JOIN co c ON sc.cid = c.cid;

? ? ? ? 創建視圖時指定屬性清單

-- MySQL  Oracle
create [or replace] view v1 (sname,cname,score)as 
-- []內內容為可選項，代表如果視圖已存在進行重載
SELECT s.sname, c.cname, sc.score
FROM sc
JOIN stu s ON sc.sid = s.sid
JOIN co c ON sc.cid = c.cid;

?注：

（1）運行創建視圖的語句需要用戶具有創建視圖（create view）的權限，若加了[or replace]時，還需要用戶具有刪除視圖[drop view]的權限。

（2）select語句不能包含from字句中的子查詢。

（3）select語句不能引用系統或用戶變量。

（4）select語句不能引用預處理語句參數。

（5）在存儲子程序內，定義不能引用子程序參數或局部變量。

（6）在定義中引用的表或視圖必須存在。但是，創建了視圖后，能夠舍棄定義引用的表或視圖。要想檢查視圖定義是否存在此類問題，可使用check table語句。

（7）在定義中不能引用temporary表，不能創建temporary視圖。

（8）在視圖定義中命名的表必須已存在。

（9）不能將觸發程序與視圖關聯在一起。

（10）在視圖定義中允許使用order by，但是，如果從特定視圖進行了選擇，而該視圖使用了具有自己order by的語句，它將被忽略。

修改視圖

????????修改視圖是指修改數據庫中已存在的表的定義。當基本表的某些字段發生改變時，可以通過修改視圖來保持視圖和基本表之間一致。在MySQL中通過create or replace view語句和alter view語句來修改視圖。Oracle中只能通過create or replace view語句來修改，無法用alter view修改。

create or replace view修改視圖

-- MySQL  Oracle
create or replace 
view v1 (sname , cname , score) as
select s.sname,c.cname,sc.score
from sc
join stu s on sc.sid=s.sid
joinco  c on sc.cid=c.cid;

????????該語句與創建視圖語句幾乎相同，但是增加了 or replace 代表創建v1視圖，若視圖已存在，則重載，所以盡管已經有v1視圖，我們仍然可以使用該語句進行創建或修改v1視圖。

alter view修改視圖

-- MySQL
alter view v1 as 
select sid , sname from stu;

查看視圖

????????查看視圖是指查看數據庫中已存在的視圖的定義。使用describe關鍵字。

-- MySQL Oracle
describe v1;

刪除視圖

????????刪除視圖是指刪除數據庫中已存在的視圖，刪除視圖時，只能刪除視圖的定義，不會刪除數據。MySQL中，使用drop view語句刪除視圖，同時，用戶必須擁有drop權限。

-- MySQL
drop view if exists v1;-- Oracle
-- 先查詢視圖是否存在
SELECT * FROM all_views WHERE view_name = 'V1';-- 若有結果，再執行刪除
DROP VIEW V1;

????????if exists為如果存在，防止報錯，存在則刪除，不存在則不進行操作。

? ? ? ? 求關注，求點贊，求收藏！！！