如何使用 SQL 視圖簡化數據處理

来源:https://www.cnblogs.com/vin-c/archive/2022/05/19/16287705.html
-Advertisement-
Play Games

本文介紹什麼是 SQL 視圖,它們怎樣工作,何時使用它們。視圖提供了一種封裝 SELECT 語句的層次,可用來簡化數據處理,重新格式化或保護基礎數據。 一、視圖 視圖是虛擬的表。與包含數據的表不一樣,視圖只包含使用時動態檢索數據的查詢。 說明:SQLite 的視圖 SQLite 僅支持只讀視圖,所以 ...


目錄

本文介紹什麼是 SQL 視圖,它們怎樣工作,何時使用它們。視圖提供了一種封裝 SELECT 語句的層次,可用來簡化數據處理,重新格式化或保護基礎數據。

一、視圖

視圖是虛擬的表。與包含數據的表不一樣,視圖只包含使用時動態檢索數據的查詢。

說明:SQLite 的視圖

SQLite 僅支持只讀視圖,所以視圖可以創建,可以讀,但其內容不能更改。

理解視圖的最好方法是看例子。如何使用 SQL INNER JOIN 聯結兩個或多個表 用下麵的 SELECT 語句從三個表中檢索數據:

SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
 AND OrderItems.order_num = Orders.order_num
 AND prod_id = 'RGAN01';

此查詢用來檢索訂購了某種產品的顧客。任何需要這個數據的人都必須理解相關表的結構,知道如何創建查詢和對錶進行聯結。檢索其他產品(或多個產品)的相同數據,必須修改最後的 WHERE 子句。

現在,假如可以把整個查詢包裝成一個名為 ProductCustomers 的虛擬表,則可以如下輕鬆地檢索出相同的數據:

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

這就是視圖的作用。ProductCustomers 是一個視圖,作為視圖,它不包含任何列或數據,包含的是一個查詢(與上面用以正確聯結表的查詢相同)。

提示:DBMS 的一致支持

我們欣慰地瞭解到,所有 DBMS 非常一致地支持視圖創建語法。

1.1 為什麼使用視圖

我們已經看到了視圖應用的一個例子。下麵是視圖的一些常見應用。

  • 重用 SQL 語句。
  • 簡化複雜的 SQL 操作。在編寫查詢後,可以方便地重用它而不必知道其基本查詢細節。
  • 使用表的一部分而不是整個表。
  • 保護數據。可以授予用戶訪問表的特定部分的許可權,而不是整個表的訪問許可權。
  • 更改數據格式和表示。視圖可返回與底層表的表示和格式不同的數據。

創建視圖之後,可以用與表基本相同的方式使用它們。

可以對視圖執行 SELECT 操作,過濾和排序數據,將視圖聯結到其他視圖或表,甚至添加和更新數據(添加和更新數據存在某些限制,關於這個內容稍後做介紹)。

重要的是,要知道視圖僅僅是用來查看存儲在別處數據的一種設施。視圖本身不包含數據,因此返回的數據是從其他表中檢索出來的。在添加或更改這些表中的數據時,視圖將返回改變過的數據。

註意:性能問題

因為視圖不包含數據,所以每次使用視圖時,都必須處理查詢執行時需要的所有檢索。

如果你用多個聯結和過濾創建了複雜的視圖或者嵌套了視圖,性能可能會下降得很厲害。

因此,在部署使用了大量視圖的應用前,應該進行測試。

1.2 視圖的規則和限制

創建視圖前,應該知道它的一些限制。不過,這些限制隨不同的 DBMS 而不同,因此在創建視圖時應該查看具體的 DBMS 文檔。

下麵是關於視圖創建和使用的一些最常見的規則和限制。

  • 與表一樣,視圖必須唯一命名(不能給視圖取與別的視圖或表相同的名字)。

  • 對於可以創建的視圖數目沒有限制。

  • 創建視圖,必須具有足夠的訪問許可權。這些許可權通常由資料庫管理人員授予。

  • 視圖可以嵌套,即可以利用從其他視圖中檢索數據的查詢來構造視圖。

    所允許的嵌套層數在不同的 DBMS 中有所不同(嵌套視圖可能會嚴重降低查詢的性能,因此在產品環境中使用之前,應該對其進行全面測試)。

  • 許多 DBMS 禁止在視圖查詢中使用 ORDER BY 子句。

  • 有些 DBMS 要求對返回的所有列進行命名,如果列是計算欄位,則需要使用別名(關於列別名的更多信息,請參閱 SQL 如何創建計算欄位)。

  • 視圖不能索引,也不能有關聯的觸發器或預設值。

  • 有些 DBMS 把視圖作為只讀的查詢,這表示可以從視圖檢索數據,但不能將數據寫回底層表。詳情請參閱具體的 DBMS 文檔。

  • 有些 DBMS 允許創建這樣的視圖,它不能進行導致行不再屬於視圖的插入或更新。

    例如有一個視圖,只檢索帶有電子郵件地址的顧客。如果更新某個顧客,刪除他的電子郵件地址,將使該顧客不再屬於視圖。

    這是預設行為,而且是允許的,但有的 DBMS 可能會防止這種情況發生。

提示:參閱具體的 DBMS 文檔

上面的規則不少,而具體的 DBMS 文檔很可能還包含別的規則。因此,在創建視圖前,有必要花點時間瞭解必須遵守的規定。

二、創建視圖

理解了什麼是視圖以及管理它們的規則和約束後,我們來創建視圖。

視圖用 CREATE VIEW 語句來創建。與 CREATE TABLE 一樣,CREATE VIEW 只能用於創建不存在的視圖。

說明:視圖重命名

刪除視圖,可以使用 DROP 語句,其語法為 DROP VIEW viewname;

覆蓋(或更新)視圖,必須先刪除它,然後再重新創建。

2.1 利用視圖簡化複雜的聯結

一個最常見的視圖應用是隱藏複雜的 SQL,這通常涉及聯結。請看下麵的例子:

CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
 AND OrderItems.order_num = Orders.order_num;

這條語句創建一個名為 ProductCustomers 的視圖,它聯結三個表,返回已訂購了任意產品的所有顧客的列表。

如果執行 SELECT * FROM ProductCustomers,將列出訂購了任意產品的顧客。

檢索訂購了產品 RGAN01 的顧客,可如下進行:

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

輸出:

cust_name               cust_contact
-------------------     ------------------
Fun4All                 Denise L. Stephens
The Toy Store           Kim Howard

這條語句通過 WHERE 子句從視圖中檢索特定數據。當 DBMS 處理此查詢時,它將指定的 WHERE 子句添加到視圖查詢中已有的 WHERE 子句中,以便正確過濾數據。

可以看出,視圖極大地簡化了複雜 SQL 語句的使用。利用視圖,可一次性編寫基礎的 SQL,然後根據需要多次使用。

提示:創建可重用的視圖

創建不綁定特定數據的視圖是一種好辦法。

例如,上面創建的視圖返回訂購所有產品而不僅僅是 RGAN01 的顧客(這個視圖先創建)。

擴展視圖的範圍不僅使得它能被重用,而且可能更有用。這樣做不需要創建和維護多個類似視圖。

2.2 用視圖重新格式化檢索出的數據

如前所述,視圖的另一常見用途是重新格式化檢索出的數據。下麵的 SELECT 語句在單個組合計算列中返回供應商名和位置:

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
       AS vend_title
FROM Vendors
ORDER BY vend_name;

輸出:

vend_title
-----------------------------------------------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

下麵是相同的語句,但使用了 || 語法:

SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
       AS vend_title
FROM Vendors
ORDER BY vend_name;

輸出:

vend_title
-----------------------------------------------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

現在,假設經常需要這個格式的結果。我們不必在每次需要時執行這種拼接,而是創建一個視圖,使用它即可。把此語句轉換為視圖,可按如下進行:

CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
       AS vend_title
FROM Vendors;

下麵是使用 || 語法的相同語句:

CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
       AS vend_title
FROM Vendors;

這條語句使用與以前 SELECT 語句相同的查詢創建視圖。要檢索數據,創建所有的郵件標簽,可如下進行:

SELECT * FROM VendorLocations;

輸出:

vend_title
-----------------------------------------------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

說明:SELECT 約束全部適用

在本文的前面提到,各種 DBMS 中用來創建視圖的語法相當一致。

那麼,為什麼會有多種創建視圖的語句版本呢?

因為視圖只包含一個 SELECT 語句,而這個語句的語法必須遵循具體 DBMS 的所有規則和約束,所以會有多個創建視圖的語句版本。

2.3 用視圖過濾不想要的數據

視圖對於應用普通的 WHERE 子句也很有用。例如,可以定義 CustomerEMailList 視圖,過濾沒有電子郵件地址的顧客。為此,可使用下麵的語句:

CREATE VIEW CustomerEMailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

顯然,在將電子郵件發送到郵件列表時,需要排除沒有電子郵件地址的用戶。這裡的 WHERE 子句過濾了 cust_email 列中具有 NULL 值的那些行,使它們不被檢索出來。

現在,可以像使用其他表一樣使用視圖 CustomerEMailList

SELECT *
FROM CustomerEMailList;

輸出:

cust_id        cust_name        cust_email
----------     ------------     ---------------------
1000000001     Village Toys     [email protected]
1000000003     Fun4All          [email protected]
1000000004     Fun4All          [email protected]

說明:WHERE 子句與 WHERE 子句

從視圖檢索數據時如果使用了一條 WHERE 子句,則兩組子句(一組在視圖中,另一組是傳遞給視圖的)將自動組合。

2.4 使用視圖與計算欄位

在簡化計算欄位的使用上,視圖也特別有用。

下麵是 SQL 如何創建計算欄位 中介紹的一條 SELECT 語句,它檢索某個訂單中的物品,計算每種物品的總價格:

SELECT prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

輸出:

prod_id      quantity      item_price      expanded_price
--------     ---------     -----------     --------------
RGAN01       5             4.9900          24.9500
BR03         5             11.9900         59.9500
BNBG01       10            3.4900          34.9000
BNBG02       10            3.4900          34.9000
BNBG03       10            3.4900          34.9000

要將其轉換為一個視圖,如下進行:

CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
       prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM OrderItems

檢索訂單 20008 的詳細內容(上面的輸出),如下進行:

SELECT *
FROM OrderItemsExpanded
WHERE order_num = 20008;

輸出:

order_num   prod_id   quantity    item_price    expanded_price
---------   -------   ---------   ----------    --------------
20008       RGAN01    5           4.99          24.95
20008       BR03      5           11.99         59.95
20008       BNBG01    10          3.49          34.90
20008       BNBG02    10          3.49          34.90
20008       BNBG03    10          3.49          34.90

可以看到,視圖非常容易創建,而且很好使用。正確使用,視圖可極大地簡化複雜數據的處理。

三、小結

視圖為虛擬的表。它們包含的不是數據而是根據需要檢索數據的查詢。視圖提供了一種封裝 SELECT 語句的層次,可用來簡化數據處理,重新格式化或保護基礎數據。

原文鏈接:https://www.developerastrid.com/sql/sql-view/

(完)


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

-Advertisement-
Play Games
更多相關文章
  • 軟硬體環境 軟體 版本 操作系統 Redhat 7 及以上版本,麒麟V10 DM 資料庫 DM 8.0 及以上版本 CPU 架構 x86、ARM、龍芯、飛騰等國內外主流 CPU DM-Oracle 環境準備 在DM伺服器中完成Dblink環境準備工作。 配置Oracle oci客戶端 在Oracle ...
  • 導讀: 隨著全球數據量的不斷增長,越來越多的業務需要支撐高併發、高可用、可擴展、以及海量的數據存儲,在這種情況下,適應各種場景的數據存儲技術也不斷的產生和發展。與此同時,各種資料庫之間的同步與轉化的需求也不斷增多,數據集成成為大數據領域的熱門方向,於是SeaTunnel應運而生。SeaTunnel是 ...
  • hive 存儲格式有很多,但常用的一般是 TextFile、ORC、Parquet 格式,在我們單位最多的也是這三種 hive 預設的文件存儲格式是 TextFile。 除 TextFile 外的其他格式的表不能直接從本地文件導入數據,要先導入到 TextFile 格式的表中,再從表中用 inser ...
  • 本文介紹 SQL 所涉及的幾個數據處理特性:約束、索引和觸發器。約束是實施引用完整性的重要部分,索引可改善數據檢索的性能,觸發器可以用來執行運行前後的處理。 一、約束 SQL 已經改進過多個版本,成為非常完善和強大的語言。許多強有力的特性給用戶提供了高級的數據處理技術,如約束。 關聯表和引用完整性已 ...
  • 本文介紹什麼是 SQL 游標,為什麼使用游標,如何使用游標。你使用的 DBMS 可能會提供某種形式的游標,以及這裡沒有提及的功能。更詳細的內容請參閱具體的 DBMS 文檔。 一、游標 SQL 檢索操作返回一組稱為結果集的行,這組返回的行都是與 SQL 語句相匹配的行(零行到多行)。 簡單地使用 SE ...
  • 本文介紹什麼是 SQL 事務處理,如何利用 COMMIT 和 ROLLBACK 語句對何時寫數據、何時撤銷進行明確的管理;還學習瞭如何使用保留點,更好地控制回退操作。 一、事務處理 使用事務處理(transaction processing),通過確保成批的 SQL 操作要麼完全執行,要麼完全不執行 ...
  • 本文介紹什麼是 SQL 存儲過程,為什麼要使用存儲過程,如何使用存儲過程,以及創建和使用存儲過程的基本語法。 一、存儲過程 迄今為止,我們使用的大多數 SQL 語句都是針對一個或多個表的單條語句。並非所有操作都這麼簡單,經常會有一些複雜的操作需要多條語句才能完成,例如以下的情形。 為了處理訂單,必須 ...
  • 大家好,我是大D。 今天開始給大家分享關於大數據入門技術棧——Hadoop的學習內容。 初識 Hadoop 為瞭解決大數據中海量數據的存儲與計算問題,Hadoop 提供了一套分散式系統基礎架構,核心內容包含HDFS ( Hadoop Distributed File System, 分散式文件系統) ...
一周排行
    -Advertisement-
    Play Games
  • 前言 本文將以 C# 語言來實現一個簡單的布隆過濾器,為簡化說明,設計得很簡單,僅供學習使用。 感謝@時總百忙之中的指導。 布隆過濾器簡介 布隆過濾器(Bloom filter)是一種特殊的 Hash Table,能夠以較小的存儲空間較快地判斷出數據是否存在。常用於允許一定誤判率的數據過濾及防止緩存 ...
  • 目錄 一.簡介 二.效果演示 三.源碼下載 四.猜你喜歡 零基礎 OpenGL (ES) 學習路線推薦 : OpenGL (ES) 學習目錄 >> OpenGL ES 基礎 零基礎 OpenGL (ES) 學習路線推薦 : OpenGL (ES) 學習目錄 >> OpenGL ES 轉場 零基礎 O ...
  • 「簡單有價值的事情長期堅持做」 這是成功最簡單,但也最難學的秘訣。不經過訓練,人很難意識到時間複利的威力。 仙劍奇俠傳的「十里坡劍神」和金庸群俠傳的「十級野球拳」,就是簡單的事情持之以恆反覆做,最後就有巨大的威力 唐家三少成為網文收入第一,最重要的一步是十四年從未斷日更 這樣的案例很多,一開始可能成 ...
  • 迎面走來了你的面試官,身穿格子衫,挺著啤酒肚,髮際線嚴重後移的中年男子。 手拿泡著枸杞的保溫杯,胳膊夾著MacBook,MacBook上還貼著公司標語:“我愛加班”。 面試開始,直入正題。 面試官: 看你簡歷上面寫著精通MySQL,我先問你事務的特性是什麼? 老生常談,這個還有誰不會背的嗎? 我: ...
  • 基礎知識 python是一門腳本語言,它是解釋執行的。 python使用縮進做為語法,而且python2環境下同一個py文件中不能同時存在tab和空格縮進,否則會出錯,建議在IDE中顯示縮進符。 python在聲明變數時不寫數據類型,可以type(xx)來獲取欄位的類型,然後可以int(),list ...
  • 為什麼要多線程下載 俗話說要以終為始,那麼我們首先要明確多線程下載的目標是什麼,不外乎是為了更快的下載文件。那麼問題來了,多線程下載文件相比於單線程是不是更快? 對於這個問題可以看下圖。 橫坐標是線程數,縱坐標是使用對應線程數下載對應文件時花費的時間,藍橙綠代表下載文件的大小,每個線程下載對應文件2 ...
  • 詳細講解python爬蟲代碼,爬微博搜索結果的博文數據。 爬取欄位: 頁碼、微博id、微博bid、微博作者、發佈時間、微博內容、轉發數、評論數、點贊數。 爬蟲技術: 1、requests 發送請求 2、datetime 時間格式轉換 3、jsonpath 快速解析json數據 4、re 正則表達式提... ...
  • 背景: 一般我們可以用HashMap做本地緩存,但是HashMap功能比較弱,不支持Key過期,不支持數據範圍查找等。故在此實現了一個簡易的本地緩存,取名叫fastmap。 功能: 1.支持數據過期 2.支持等值查找 3.支持範圍查找 4.支持key排序 實現思路: 1.等值查找採用HashMap2 ...
  • 目錄 一.簡介 二.效果演示 三.源碼下載 四.猜你喜歡 零基礎 OpenGL (ES) 學習路線推薦 : OpenGL (ES) 學習目錄 >> OpenGL ES 基礎 零基礎 OpenGL (ES) 學習路線推薦 : OpenGL (ES) 學習目錄 >> OpenGL ES 轉場 零基礎 O ...
  • 本章是系列文章的第八章,用著色演算法進行寄存器的分配過程。 本文中的所有內容來自學習DCC888的學習筆記或者自己理解的整理,如需轉載請註明出處。周榮華@燧原科技 寄存器分配 寄存器分配是為程式處理的值找到存儲位置的問題 這些值可以存放到寄存器,也可以存放在記憶體中 寄存器更快,但數量有限 記憶體很多,但 ...