實例詳解MySQL資料庫視圖

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

推薦學習：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]

參數說明：

1. algorithm：# 表示視圖選擇的演算法，可選；
2. ## view_name： 建立的視圖名稱；
3. column_list： 指定視圖中各個屬性的名詞，預設與SELECT語句中查詢的屬性相同；
4. select_statement： 表示一個完整的查詢語句，將查詢記錄匯入檢視中;
5. ##[ 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视频教程

以上是實例詳解MySQL資料庫視圖的詳細內容。更多資訊請關注PHP中文網其他相關文章！