範例詳解MySQL資料庫檢視

本篇文章給大家帶來了關於的相關知識，其中主要整理了資料庫檢視的相關問題，包括了檢視的介紹與作用、檢視的建立、檢視的修改、檢視的更新、檢視的重新命名與刪除、檢視的練習等等內容，下面一起來看一下，希望對大家有幫助。

推薦學習：

1 檢視的介紹與作用

檢視的介紹：

檢視 view 是一個虛擬表，非真實存在，其 本質是根據SQL語句獲取動態的資料集，併為其命名， 使用者使用時只需要使用檢視名稱即可獲取結果集，並可以將其當作表來使用。
資料庫中只存放了檢視的定義，而並沒有存放檢視中的資料。 資料還存在於原來的資料表中。
使用檢視查詢資料時，資料庫系統會從原來的表中取出對應的資料。因此， 檢視中的資料是依賴於原來表中資料的。 當表的資料發生改變，檢視中的資料也會隨之改變。

檢視的作用：

簡化程式碼， 我們可以把重複使用的查詢封裝成檢視重複使用，同時可以 使複雜的查詢易於理解；
更加安全， 比如，如果有一張表中有很多資料，很多資訊不希望被其他人看到，這時就可以使用到檢視，對不同的使用者使用不同的檢視。

2 檢視的建立

建立檢視的語法如下：

create [or replace] [algorithm = {undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]

引數說明：

algorithm： 表示檢視選擇的演演算法，可選；
view_name： 建立的檢視名稱；
column_list： 指定檢視中各個屬性的名詞，預設情況下與SELECT語句中查詢的屬性相同；
select_statement： 表示一個完整的查詢語句，將查詢記錄匯入檢視中；
[with [cascaded | local] check option]： 表示更新檢視時要保證該檢視在許可權範圍之內。

3 檢視的修改

修改檢視是指修改資料庫中已存在的表的定義。當基本表中的某些欄位發生改變時，可以通過修改檢視來保持檢視和基本表之間的一致。

語法格式：

alter view 檢視名 as select語句;

4 檢視的更新

並不是所有的檢視都可以更新。可以在UPDATE、DELETE或INSERT等語句中使用檢視，以更新基本表的內容。對於可更新的檢視，在檢視中的行和基本表中的行之間必須具有一對一的關係，如果檢視包含下述結構中的任何一種，則該檢視不可更新：

聚合函數（SUM()、MIN()、MAX()等）；
DISTINCT；
HAVING；
UNION或者UNION ALL；
位於選擇列表中的子查詢；
JOIN；
FROM子句中的不可更新檢視；
WHERE子句中的子查詢，參照FROM子句中的表；
僅使用文字值（在該情況下，沒有要更新的基本表）。

注意：
檢視中雖然可以更新資料，但是有很多限制。一般情況下，最好將檢視作為查詢資料的虛擬表，而不要通過檢視更新資料。
當真實表中修改了某個存在檢視中的欄位時，檢視需要更新，否則該檢視就會變成無效檢視！

5 檢視的重新命名與刪除

重新命名檢視：

rename table 檢視名 to 新檢視名;

刪除檢視：

drop view if exists 檢視名;

刪除檢視時，只刪除了檢視的定義，而並不會刪除真實表中的資料

如果想同時刪除多個檢視，則使用下面的語法格式：

drop view if exists 檢視名1, 檢視名2, 檢視名3...;

6 檢視的練習

6.1 資料準備

在進行練習時可以先根據下面程式碼建立用於練習的兩個基本表：

create table college(
    cno   int         null,
    cname varchar(20) null);

create table student(
    sid     int         null,
    name    varchar(20) null,
    gender  varchar(20) null,
    age     int         null,
    birth   date        null,
    address varchar(20) null,
    score   double      null);

兩表的基本資料如下圖所示：

在這裡插入圖片描述

6.2 查詢平均分最高的學校名稱

結合之前學過的知識可以 嘗試使用子查詢和連線查詢 來實現，參考程式碼如下：

SELECT cname
FROM (SELECT cname, rank() over (order by avg_score desc ) item
      FROM (SELECT cname, avg(score) avg_score
            FROM student
                     JOIN college ON sid = cno
            GROUP BY cname) t) tt
WHERE item = 1;

在上述程式碼中，先將student 與 college兩表關聯，將關聯的查詢作為子表，並根據子表進行平均數的排序，平均數序號為1的平均分數最高，再以此為子表進行子查詢，查詢出了平均分最高的學校。具體結果如下：
在這裡插入圖片描述

這種方式雖然能夠解決問題，但是相對複雜，不容易看懂，為了簡化程式碼，我們可以將每一個子查詢建立為一個檢視

檢視解決方式程式碼：

-- 1 檢視一，連線兩表並計算平均數
CREATE VIEW t_view AS
SELECT cname, avg(score) avg_score
FROM student
         JOIN college ON sid = cno
GROUP BY cname;

-- 2 檢視二，利用檢視一對平均分數進行排序標號
CREATE VIEW tt_view AS
SELECT cname, rank() over (order by avg_score desc ) item
FROM (t_view);

-- 3 利用檢視查詢
SELECT cname
FROM (tt_view)
WHERE item = 1;

在建立完檢視後，如果想要查詢平均分前三名學校，則方便很多，建立好的檢視可以直接使用！

參考程式碼及結果：

SELECT cnameFROM (tt_view)WHERE item = 1
   OR item = 2
   OR item = 3;

在這裡插入圖片描述

推薦學習：

以上就是範例詳解MySQL資料庫檢視的詳細內容，更多請關注TW511.COM其它相關文章！