MYSQL視圖的學習筆記

標籤：des   io   ar   os   使用   sp   for   strong   資料   

MYSQL視圖的學習筆記，學至Tarena金牌講師何山，金色晨曦科技公司技術總監沙利穆 課程筆記的綜合。

視圖及圖形化工具

 

1.       視圖的定義

視圖就是從一個或多個表中，匯出來的表，是一個虛擬存在的表。視圖就像一個視窗（資料展示的視窗），通過這個視窗，可以看到系統專門提供的資料（也可以查看到資料表的全部資料），使用視圖就可以不用看到資料表中的所有資料，而是只想得到所需的資料。

在資料庫中，只存放了視圖的定義，並沒有存放視圖的資料，資料還是儲存在原來的表裡，視圖的資料是依賴原來表中的資料的，所以原來的表的資料發生了改變，那麼顯示的視圖的資料也會跟著改變,例如向資料表中插入資料，那麼在查看視圖的時候，會發現視圖中也被插入了同樣的資料。

視圖在外觀上和表很相似，但是它不需要實際上的實體儲存體，視圖實際上是由預定義的查詢形式的表所組成的。

視圖可以包含表的全部或者部分記錄，也可以由一個表或者多個表來建立，當我們建立一個視圖的時候，實際上是在資料庫裡執行了SELECT語句，SELECT語句包含了欄位名稱、函數、運算子，來給使用者顯示資料。

在資料庫中，視圖的使用方式與表的使用方式一致，我們可以像動作表一樣去操作視圖，或者去擷取資料。

一般來說，我們只是利用視圖來查詢資料，不會通過視圖來操作資料。

1.1    基於視圖的視圖

基於已存在的視圖，還可以再建立視圖。

1.2    視圖和表的區別

視圖和表的主要區別，就是看是否佔用物理空間。

 

1.3 視圖的作用

（1）選取有用的資訊，篩選的作用

（2）操作簡單化，所見即所需，視圖看到的資訊，就是需要瞭解的資訊

（3）增加資料的安全性：查詢或者修改指定的資料，非指定的資料是觸碰不到的。

（4）提高邏輯的獨立性

1.4 視圖的特點

（1）簡單性（簡單化）：可以展現特定的資料，而無需重複設定查詢條件，簡化操作。

（2）安全性：視圖可以只展現資料表的一部分資料，對於我們不希望讓使用者看到全部資料，只希望使用者看到部分資料的時候，可以選擇使用視圖。

（3）邏輯獨立性：當真實的資料表結構發生了變化，可以通過視圖來屏蔽真實表的結構變化，從而實現了視圖的邏輯獨立性。

 

視圖可以使應用程式和資料庫表在一定程度上獨立。如果沒有視圖，應用一定是建立在表上的。有了視圖之後，程式可以建立在視圖之上，從而程式與資料庫表被視圖分割開來。視圖可以在以下幾個方面使程式與資料獨立：

 

①如果應用建立在資料庫表上，當資料庫表發生變化時，可以在表上建立視圖，通過視圖屏蔽表的變化，從而應用程式可以不動。

 

②如果應用建立在資料庫表上，當應用發生變化時，可以在表上建立視圖，通過視圖屏蔽應用的變化，從而使資料庫表不動。

 

③如果應用建立在視圖上，當資料庫表發生變化時，可以在表上修改視圖，通過視圖屏蔽表的變化，從而應用程式可以不動。

 

④如果應用建立在視圖上，當應用發生變化時，可以在表上修改視圖，通過視圖屏蔽應用的變化，從而資料庫可以不動。

 

2.       建立視圖

CREATE VIEW 視圖名稱[(column_list)] AS SELECT 語句

例：

CREATE VIEW  province_view AS SELECT * FROM province;

SELECT * FROM province_view;

說明：建立的視圖表province_view與province表一模一樣。

 

2.1    指定視圖顯示的欄位：

CREATE VIEW province_view1(id,name) AS SELECT id,pro_name FROM province;

mysql> SELECT * FROM province_view1;

+-----+------+

| id  | name |

+-----+------+

|   1 | 北京 |

|   2 | 上海 |

|   3 | 遼寧 |

|   4 | 天津 |

|   5 | 廣東 |

|   6 | 福建 |

| 100 | 吉林 |

+-----+------+

7 rows in set (0.00 sec)

 

2.2 建立基於兩個表的視圖：

使用WHERE串連兩個表：

CREATE VIEW v3(name,score) AS SELECT s_name,score FROM student,score

WHERE student.s_id=score.s_id

and score.c_id=‘BY‘;

 

2.3 視圖的演算法

ALGORITHM=

UNDEFINED:MYSQL自動選擇要使用的演算法

MERGE：使用視圖的語句與視圖的定義是合并在一起的，視圖定義的某一部分取代語句對應的部分

TEMPTABLE：暫存資料表，視圖的結果存入暫存資料表，然後使用暫存資料表來執行語句

 

WHIT [CASCADED|LOCAL] CHECK OPTION：表示更新視圖的時候，要保證在視圖的許可權範圍之內：

CASCADED 預設值，表示更新視圖的時候，要滿足視圖和表的相關條件

LOCAL：表示更新視圖的時候，要滿足該視圖定義的一個條件即可

 

說明：使用WHIT [CASCADED|LOCAL] CHECK OPTION選項可以保證資料的安全性

 

3.建立完整的視圖

CREATE ALGORITHM VIEW 視圖名稱[(column_list)] AS SELECT 語句

WITH  [CASCADED|LOCAL] CHECK OPTION

 

文法提示命令：? CREATE VIEW

 

Name: ‘CREATE VIEW‘

Description:

Syntax:

CREATE

    [OR REPLACE]

    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

    [DEFINER = { user | CURRENT_USER }]

    [SQL SECURITY { DEFINER | INVOKER }]

    VIEW view_name [(column_list)]

    AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

 

例子：

CREATE ALGORITHM=UNDEFINED VIEW user_view3(id,username,age) AS SELECT

id,username,age FROM users2 WITH CASCADED CHECK OPTION;

 

4. 查看視圖

查看已建立好的視圖：

4.1 查看已建立好的視圖的方法：

DESC

DESCRIBE

SHOW COLUMNS FROM 視圖名稱

SHOW TABLE STATUS LIKE

SHOW CREATE VIEW

4.1.1 DESC

mysql> desc user_view3;

+----------+----------------------+------+-----+---------+-------+

| Field    | Type                 | Null | Key | Default | Extra |

+----------+----------------------+------+-----+---------+-------+

| id       | smallint(5) unsigned | NO   |     | 0       |       |

| username | varchar(20)          | NO   |     | NULL    |       |

| age      | tinyint(3) unsigned  | YES  |     | NULL    |       |

+----------+----------------------+------+-----+---------+-------+

3 rows in set (0.02 sec)

 

4.1.2 DESCRIBE

mysql> DESCRIBE user_view3;

+----------+----------------------+------+-----+---------+-------+

| Field    | Type                 | Null | Key | Default | Extra |

+----------+----------------------+------+-----+---------+-------+

| id       | smallint(5) unsigned | NO   |     | 0       |       |

| username | varchar(20)          | NO   |     | NULL    |       |

| age      | tinyint(3) unsigned  | YES  |     | NULL    |       |

+----------+----------------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

 

4.1.3 SHOW COLUMNS FROM 視圖名稱

mysql> SHOW COLUMNS FROM user_view3;

+----------+----------------------+------+-----+---------+-------+

| Field    | Type                 | Null | Key | Default | Extra |

+----------+----------------------+------+-----+---------+-------+

| id       | smallint(5) unsigned | NO   |     | 0       |       |

| username | varchar(20)          | NO   |     | NULL    |       |

| age      | tinyint(3) unsigned  | YES  |     | NULL    |       |

+----------+----------------------+------+-----+---------+-------+

3 rows in set (0.02 sec)

 

4.2 查看視圖的基本資料（也可查看原表的資訊）：

SHOW TABLE STATUS LIKE ‘視圖名稱’;

 

mysql> SHOW TABLE STATUS LIKE ‘province_view‘\G;

*************************** 1. row ***************************

           Name: province_view

         Engine: NULL

        Version: NULL

     Row_format: NULL

           Rows: NULL

 Avg_row_length: NULL

    Data_length: NULL

Max_data_length: NULL

   Index_length: NULL

      Data_free: NULL

 Auto_increment: NULL

    Create_time: NULL

    Update_time: NULL

     Check_time: NULL

      Collation: NULL

       Checksum: NULL

 Create_options: NULL

        Comment: VIEW

1 row in set (0.00 sec)

 

說明：

（1）       可以從Comment: VIEW看出它是一個視圖，如果是資料表，Comment選項的值為空白。

（2）       因為視圖是虛擬出的一張表，所以很多選項的值都是NULL，如果SHOW TABLE STATUS LIKE ‘table_name’; 那麼這些選項將會顯示出數值。

 

4.3 查看指定視圖的建立資訊（專門查看視圖資訊的命令）

 

SHOW CREATE VIEW 視圖名稱;

 

mysql> SHOW CREATE VIEW user_view3\G;

*************************** 1. row ***************************

                View: user_view3

         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `user_view3` AS select `users2`.`id` AS `id`,`users2`.`username` AS `username`,`users2`.`age` AS `age` from `users2` WITH CASCADED CHECK OPTION

character_set_client: gbk

collation_connection: gbk_chinese_ci

1 row in set (0.00 sec)

 

4.4 視圖資料的儲存位置

 

mysql> SELECT * FROM information_schema.views\G

 

所有的視圖都儲存在了information_schema.views中。

 

4.5.修改視圖：

 

如果視圖不存在，則建立視圖，如果視圖存在，則修改視圖：

（1）CREATE OR REPLACE VIEW 視圖名稱[(column_list)] AS SELECT 語句

（2）ALTER VIEW視圖名稱[(column_list)] AS SELECT 語句

 

4.5.1 CREATE OR REPLACE VIEW 視圖名稱[(column_list)] AS SELECT 語句

（1）例子：

CREATE OR REPLACE VIEW user_view3(id,username) AS SELECT id,username FROM users2;

 

（2）如果輸入的視圖名稱不存在，這MYSQL自動建立該視圖：

 

（3）修改視圖：

CREATE OR REPLACE ALOGRITHM=TEMPTABLE VIEW user_view4(id) AS SELECT id FROM

users2;

 

（4）修改基於兩個表的視圖，兩個表使用WHERE進行串連：

CREATE OR REPLACE VIEW v3 AS SELECT s_name,s_sex,score FROM student,score

WHERE student.s_id=score.s_id AND score.c_id=‘BY‘;

 

4.5.2 ALTER

ALTER VIEW 視圖名稱[(column_list)] AS SELECT 語句

 

ALTER VIEW user_view4(id,username,age) AS SELECT id,username,age FROM users2;

 

修改基於兩個表的視圖：

ALTER VIEW v3 AS SELECT s_name,score FROM student,score

WHERE student.s_id=score.s_id

AND score.c_id=‘TC‘;

 

5.更新視圖

所謂更新視圖，其實就是通過視圖，對資料進行插入，修改和刪除的操作。

5.1 修改視圖的資料

注意：修改視圖的資料，將直接修改資料表（即原表）的真實資料。

UPDATE v3 SET score=100 WHERE s_name=‘倪妮‘;

5.2 通過視圖插入、刪除資料的原理與5.1一致，均與資料表的操作文法一致

 

6.刪除視圖：

刪除視圖，不會影響原表的資料，但是刪除視圖的資料，則會影響到原表。

 

6.1 DROP VIEW 視圖名稱;

 

DROP VIEW 視圖名稱;

DROP VIEW user_view4;

 

6.2 DROP VIEW IF EXISTS

在刪除已不存在的視圖的時候，不進行任何操作：

DROP VIEW  IF EXISTS視圖名稱;

例：

DROP VIEW IF EXISTS v1;

 

6.3 刪除多個視圖

 

DROP VIEW IF EXISTS v2,v3;

圖形化工具的使用

1.圖形化工具：

MYSQL Workbench

PHPMyAdmin

Navicat for MySQL：是一個用戶端的軟體，需要安裝用戶端的軟體才能使用。

MySQL-Front

 

2.PHPMyAdmin

PHPMYADMIN是以Web-Base的方式，架構在網站主機上的MYSQL資料庫管理工具，它是使用了WEB介面管理MYSQL資料庫。

通過PHPMyAdmin可以完全地對資料庫進行操作。

MYSQL視圖的學習筆記