MySQL資料庫之視圖

来源:https://www.cnblogs.com/chenhuabin/archive/2018/12/16/10126273.html
-Advertisement-
Play Games

為了簡化複雜SQL語句編寫以及提高資料庫安全性，MySQL資料庫提供了視圖特性。視圖是一張虛擬表，不以資料庫中儲存的數據值形式存在。在開發中，開發者往往只對某些特定數據和所負責的特定任務感興趣，只需要看到這一部分數據即可。這時候就可以用到視圖來完成。 ...


1 引言

         為了簡化複雜SQL語句編寫，以及提高資料庫安全性，MySQL資料庫視圖特性。視圖是一張虛擬表，不在資料庫中以儲存的數據值形式存在。在開發中，開發者往往只對某些特定數據和所負責的特定任務感興趣,只需要看到這一部分數據即可。這時候就可以用到視圖來完成。

2 視圖簡介

2.1 什麼是視圖

　　資料庫中的視圖是一個虛擬表，但它同真實表一樣，包含一系列帶有名稱的行和列數據。行和列數據來自由定義視圖查詢所引用的表，並且在應用視圖時動態生成。另外，視圖還可以在已經存在的視圖的基礎上定義。

　　視圖一經定義變存儲在資料庫中，與其相對應的數據並沒有像表那樣在資料庫中再存儲一份，通過視圖看到的數據只是存儲在基本表中的數據。對視圖的操作與對標的操作一樣，可以對其進行查詢、修改和刪除。當對通過視圖看到的數據進行修改時，相應的基本表中的數據也會發生變化；同時，若是基本表的數據發生變化，則這種變化也會自動地反映在視圖上。

2.2 視圖的作用

         與直接從真實數據表中進行數據操作相比，視圖具有以下的有點：

         （1）簡單化

         看到的就是需要的。視圖不僅可以簡化用戶對數據的理解，也可以簡化他們的操作。那些被經常使用的查詢可以被定義為視圖，從而用戶不必為以後的每一次操作指定全部的條件。

         （2）安全性

         通過視圖用戶只能查詢和修改他們所能看到的數據。資料庫中的其他數據則既看不見也娶不到。資料庫授權命令可以使每個用戶對資料庫的檢索限制到特定的資料庫對象上，但不能限制到特定行和特定列上。但通過視圖，用戶可以被限制到資料庫的行列級別的子集上。

         （3）邏輯數據獨立性

         視圖可以幫助用戶屏蔽真實表結構變化帶來的影響。

3 創建視圖

         視圖包含了SELECT查詢結果，因此屬兔的創建基於SELECT語句和已存在的資料庫，視圖可以建立在一張表上，也可以建立在多張表上。

         本篇博文中，所有操作基於以下兩張數據表（emp表、dept表）進行：

emp表：

 　　

         emp表sql語句如下：

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

 

-- ----------------------------

-- Table structure for emp

-- ----------------------------

DROP TABLE IF EXISTS `emp`;

CREATE TABLE `emp`  (

  `empno` int(4) NOT NULL,

  `ename` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,

  `job` varchar(9) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,

  `mgr` int(4) NULL DEFAULT NULL,

  `hiredate` date NULL DEFAULT NULL,

  `sal` float(7, 2) NULL DEFAULT NULL,

  `comm` float(7, 2) NULL DEFAULT NULL,

  `deptno` int(2) NULL DEFAULT NULL,

  PRIMARY KEY (`empno`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

 

-- ----------------------------

-- Records of emp

-- ----------------------------

INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1998-12-17', 800.00, NULL, 20);

INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);

INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);

INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20);

INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);

INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30);

INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10);

INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1981-11-17', 3000.00, NULL, 20);

INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10);

INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);

INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20);

INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30);

INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20);

INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-02-23', 1300.00, NULL, 10);

INSERT INTO `emp` VALUES (8888, 'CHB', 'CLERK', 7369, '2018-12-10', 8000.00, 100.00, NULL);

 

SET FOREIGN_KEY_CHECKS = 1;
emp表SQL語句

　　dept表：

 　　

　　dept表SQL語句如下：

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

 

-- ----------------------------

-- Table structure for dept

-- ----------------------------

DROP TABLE IF EXISTS `dept`;

CREATE TABLE `dept`  (

  `deptno` int(2) NOT NULL,

  `dname` varchar(14) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,

  `loc` varchar(13) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,

  PRIMARY KEY (`deptno`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

 

-- ----------------------------

-- Records of dept

-- ----------------------------

INSERT INTO `dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO `dept` VALUES (20, 'RESEARCH', 'DALLAS');

INSERT INTO `dept` VALUES (30, 'SALES', 'CHICAGO');

INSERT INTO `dept` VALUES (40, 'OPERATIONS', 'BOSTON');

 

SET FOREIGN_KEY_CHECKS = 1;
demp表SQL語句

3.1 創建視圖的基本格式

         創建視圖的基本格式如下：

   CREATE VIEW <視圖名稱> [(column_list)]

　　AS SELECT語句;

　　雖然還有更加完整的詳細語法格式，但一般情況下，使用上述基本格式就夠用了。

　　（1）基於單個數據表建立視圖

　　在使用emp表時，如果需要查詢查詢empno（編號）、ename（姓名）、job（職位）、hiredate（雇用日期）、sal（月薪）等欄位，並指定中文拼音別名，SQL語句如下：

select empno bianhao , ename xinmin, job zhiwei, hiredate guyongriqi, sal yuexin from emp;

         如果需要頻繁得進行該語句查詢，那麼每次都要重寫這一行語句。使用視圖可以簡化操作，對empno、ename、job、hiredate、sal創建視圖：

create view view_01 as select empno , ename , job , hiredate , sal from emp;

　　執行完上述語句即可創建名為view_01的視圖，如果你是通過Navicat等圖形界面工具創建視圖，在左側視圖欄下可找到view_01視圖。

 　　

　　在剛創建好的view_01視圖進行查詢：

select * from view_01 ;

　　查詢結果如下：

 　　

　　可以發現，我們並未指定查詢的欄位，但查詢效果卻與直接在真實數據表上指定欄位查詢效果一樣。但view_01視圖並未實現欄位別名，繼續創建一個view_02為每個列添加別名：

create view view_02 (bianhao , xinmin , zhiwei , guyongriqi , yuexin) as select empno , ename , job , hiredate , sal from emp;

　　查看view_02：

select * from view_02 ;

　　結果如下：

 

　　可以看到，每一個列的列名都是我們在創建視圖時自己重新指定的。

　　（2）創建基於多個表上視圖

         創建一個視圖，視圖中包含每個員工編號（empno）、姓名（ename）、職位（job）、領導編號（mgr）、領導姓名（empno）、部門名稱（dname）、部門位置（loc），在真實數據表中查詢語句如下：

select e1.empno bianhao, e1.ename xingming, e1.job zhiwei, e1.mgr lindaobianhao, e2.ename lindaoxingming, d.dname bumenmingchen, d.loc bumenweizhi

from emp e1 , emp e2 , dept d

where e1.mgr=e2.empno and e1.deptno=d.deptno ;

　　創建視圖：

create view view_03 as select e1.empno bianhao, e1.ename xingming, e1.job zhiwei, e1.mgr lindaobianhao, e2.ename lindaoxingming, 
d.dname bumenmingchen, d.loc bumenweizhi

from emp e1 , emp e2 , dept d

where e1.mgr=e2.empno and e1.deptno=d.deptno ;

　　對剛建立的view_03視圖進行查詢：

select * from view_03 ;

　　查詢結果如下：

 

　　可以發現，在select中指定別名與在view視圖中指定別名效果是一樣的。

4 查看視圖

　　（1）查看視圖基本信息：describe 視圖名；

         查看上一章節創建的視圖view_03的基本信息：

describe view_03 ;

 

　　describe一般情況下都寫成desc，兩者是完全等效的。

       另外還可以通過“show create view 視圖名；”來查看視圖詳細信息。

　　（2）查看所有視圖

　　在MySQL中，information_schema資料庫下的views表中存儲了所有視圖的定義，通過對views表的查詢，可以查看資料庫中說喲視圖的詳細信息，查詢語句如下：

select * FROM information_schema.views ;

　　部分查詢結果如下所示：

 

5 修改視圖

　　（1）使用create or replace view語句修改視圖

　　create or replace view語句從字面上也可以理解：既可以創建視圖，也可以修改視圖（存在的話就修改，不存在就創建）。create or replace view語句的語法結構與創建視圖的create語句語法結構是完全一樣的。

　　修改上文中創建的視圖view_01，添加一個部門編號欄位（deptno）:

create or replace view view_02 (bianhao , xinmin , zhiwei , guyongriqi , yuexin , bumenbiaohao) as select empno , ename , job , hiredate , sal , 
deptno from emp;

　　再次查詢view_02，發現確實多了部門編號這一列：

select * from view_02 ;

 

　　（2）使用alter語句修改視圖

　　alter語句是MySQL提供的另一種修改視圖的方法，其語法結果與create or replace語句也是基本一樣的。

　　繼續修改視圖view_02，添加一個獎金欄位（comm）：

alter view view_02 (bianhao , xinmin , zhiwei , guyongriqi , yuexin , bumenbiaohao , jiangjin) as select empno , ename , job , hiredate , sal ,
 deptno , comm from emp;

         查看view_01視圖：

 

　　alter語句與create orreplace語句的區別是當視圖不存在是，alter語句會報錯。

6 更新視圖

　　更新視圖是指通過視圖來插入、更新、刪除表中的數據，因為視圖是一個虛擬表，其中是沒有數據的。通過視圖更新的時候都是轉到真實表上進行的。對視圖的更新操作也包括update、insert和delete。

         （1）update

         通過視圖將員工編號為7369的員工月薪改為1000，在更新前，其數據為：

select * from view_02 where bianhao=7369;

 

　　用update語句進行更新

update view_02 set yuexin=1000 where bianhao=7369;

　　再次通過視圖查看數據，發現數據確實已更新：

 　　

         再看看真實表中數據有沒有更新：

select * from emp where empno=7369;

          可以看出，真實表中的數據也一起更新了，說明對視圖的更新操作本質上是對真實表的更新。

（2）insert

　　先創建一個視圖view_04，該視圖結果與emp表一樣：

create view view_04 as select * from emp ;

　　使用insert語句向view_04視圖中插入一條數據：

insert into view_04 values(8002 , 'MARK' , 'CLERK' ,  7902 , '1998-12-12' , 1100 ,  20 ,500 ) ;

　　查看真實emp表中是否添加這麼一條數據：

select * from emp where empno =8002  ;

 

　　證明在視圖進行insert操作是可以添加數據到真實表的。但一般來說，視圖的表結構與視圖的表結構可能不一樣，若真實表中存在非空等約束，這通過視圖的insert操作就回失敗。所以，insert操作一般不會通過視圖來進行。

　　　　（3）delete

　　通過視圖刪除剛創建的empno為8002的記錄：

delete from view_04 where empno =8002  ;

　　查看真實表中數據是否刪除：

select * from emp where empno =8002  ;

 　　返回的是一個空表，證明真實表中的這條記錄也已經被刪除了。

　　最後總結一下視圖的更新操作，當視圖中包含如下內容時，視圖的更新操作將不能被執行：

　　（1）視圖中不包含真實表中被定義為非空的列（視圖中沒有，但真實表中為非空約束）。

　　（2）在定義視圖的select語句後的欄位列表中使用了數學表達式。

　　（3）在定義視圖的select語句後的欄位列表中使用了聚合函數。

　　（4）在定義視圖的select語句中使用了distinct，union，top， group by，或having子句。

7 總結

         本文是對MySQL數據中視圖的詳細總結，包括了對視圖概念、特性的介紹，然後通過實際示例展示了對視圖的增刪改查操作。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 批量刪除harbor中的鏡像
    一 說明 這個是我第一篇博客，所以我想放上原創的東西，儘管我一直都很擔心自己寫得太low，但是總要學會嘗試，學會改變自己，相信自己。在寫這個腳本時，由於我接觸LInux不是很多，能力有限，僅僅是為了讓自己心情好點，可以說只是為了完成任務，並沒有考慮其中比如代碼重覆，註釋不完整等諸多問題。這是9月份做 ...
  • MongoDB副本集功能及節點屬性梳理
    副本集的主要功能 副本集是MongoDB高可用的基礎，其主要作用 歸納為以下幾點： （1）高可用，防止設備（伺服器、網路）故障。提供自動FailOver功能。 （2）無需配置高可用性虛擬節點；無論是SQL Server 的AlwaysOn 還是 MySQL 的 MHA方案 都需要有可用性組 或集群的 ...
  • linux安裝MySQL5.7記錄
    linux安裝MySQL記錄 1. 在根目錄下創建文件夾/software和資料庫數據文件/data/mysql 2. 從官網下載相應的MySQL版本 3. 解壓並移動到/software目錄下 4. 創建mysql用戶組和mysql用戶 5. 關聯myql用戶到mysql用戶組中 6. 更改文件夾... ...
  • MySQL5.7 常用用戶操作
    MySQL5.7 常用用戶操作 1. 新建用戶 2. 授權 3. 創建用戶時授權 4. 設置與更改用戶密碼(root) 5. 撤銷用戶許可權 6. 刪除用戶 7. 查看用戶的授權 8. 顯示當前用戶信息 ...
  • Redis學習
    Redis學習資源 文章網址，http://blog.java1234.com/index.html?typeId=21 視頻資源，我的網盤 > 紙菠蘿 > 學習_其他 > 一頭扎進(java1234) > 課程目錄： 一頭扎進Redis 第一章Redis 簡介以及安裝 第一節：Redis 簡介 第 ...
  • Windows下MySQL資料庫的安裝與關閉開機自啟動
    我在學習java，安裝資料庫時找了很多教程，現在在這裡總結一下我安裝資料庫的過程，我安裝的是mysql-5.6.42-winx64版本的。 數據官方下載地址：https://dev.mysql.com/downloads/mysql/ mysql下載： 這裡下載的是zip包，不是MSI版。 mysq ...
  • 推薦一個Oracle資料庫學習網站
    推薦一個我個人的Oracle資料庫學習網站，比較系統性的整理，會持續更新的網站。網址： Oracle基礎教程： http://www.oraclejsq.com/article/010100110.html PL/SQL教程： http://www.oraclejsq.com/plsql/01020 ...
  • MySQL 資料庫-索引註意事項
    索引註意事項 （1）最左首碼原則 如果查詢的時候，查詢條件精確匹配索引的左邊連續一列或幾列，則可以命中索引。 （2）避免where 子句中對欄位施加函數，如to_date(create_time)>xxxxxx，這樣會造成無法命中索引。 （3）在使用InnoDB 時，使用與業務無關的自增主鍵作為主鍵... ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類