建站
咨詢
售后
建站咨詢
新聞中心
【引自asd1123509133的博客】
1. 背景
創(chuàng)新互聯(lián)專注于企業(yè)營(yíng)銷型網(wǎng)站建設(shè)、網(wǎng)站重做改版、海南網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、H5場(chǎng)景定制、成都做商城網(wǎng)站、集團(tuán)公司官網(wǎng)建設(shè)、成都外貿(mào)網(wǎng)站建設(shè)公司、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁(yè)設(shè)計(jì)等建站業(yè)務(wù)，價(jià)格優(yōu)惠性價(jià)比高，為海南等各大城市提供網(wǎng)站開(kāi)發(fā)制作服務(wù)。
* 視圖是數(shù)據(jù)庫(kù)中的虛擬表。包含一系列帶有名稱的行和列數(shù)據(jù)。視圖是從一個(gè)或者多個(gè)表中導(dǎo)出的，視圖的行為與表非常相似，用戶可以使用SELECT語(yǔ)句查詢數(shù)據(jù)，以及使用INSERT、UPDATE和DELETE修改記錄，視圖使用戶操作更加方便而且保障數(shù)據(jù)庫(kù)系統(tǒng)的安全。
* 視圖一旦定義便存儲(chǔ)在數(shù)據(jù)庫(kù)中，與其對(duì)應(yīng)的數(shù)據(jù)并沒(méi)有像表那樣在數(shù)據(jù)庫(kù)中再存儲(chǔ)一份，通過(guò)視圖看到的數(shù)據(jù)只是存放在基本表中的數(shù)據(jù)。對(duì)視圖的操作與對(duì)表的操作一樣，可以對(duì)其進(jìn)行查詢、修改和刪除。當(dāng)對(duì)通過(guò)視圖看到的數(shù)據(jù)進(jìn)行修改時(shí)，相應(yīng)的基本表的數(shù)據(jù)也要發(fā)生變化，同時(shí)若基本表的數(shù)據(jù)放生變化，則這種變化也會(huì)自動(dòng)地反應(yīng)到視圖中。
2. 視圖作用
* 使查詢變得很清晰，視圖中存放的數(shù)據(jù)就是我們所要的數(shù)據(jù)，并且可以簡(jiǎn)化用戶操作。
* 讓數(shù)據(jù)更安全，視圖中的數(shù)據(jù)，不存在視圖中，還是在基本表里面，通過(guò)視圖這層關(guān)系，我們可以有效的保護(hù)我們的重要數(shù)據(jù)
* 提高表的邏輯獨(dú)立性，視圖可以屏蔽原有表結(jié)構(gòu)變化帶來(lái)的影響
3. 視圖類型
* MERGE: 將引用視圖的語(yǔ)句的文本與視圖定義合并起來(lái)，使得視圖定義的某一部分取代語(yǔ)句的對(duì)應(yīng)部分。
* TEMPTABLE: 將視圖的結(jié)果將被置于臨時(shí)表中，然后使用它執(zhí)行語(yǔ)句。
* UNDEFINED：默認(rèn)所使用的算法。MySQL更傾向于MERGE而不是TEMPTABLE，這是因?yàn)镸ERGE通常更有效。
4. 視圖類型例子
* 創(chuàng)建基表users
- mysql> CREATE TABLE users(
- -> id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
- -> name VARCHAR(64) NOT NULL,
- -> sex ENUM('M', 'F') NOT NULL,
- -> age INT NOT NULL
- -> )ENGINE=INNODB CHARSET=utf8mb4;
- Query OK, 0 rows affected (0.15 sec)
* 對(duì)基表 users 插入數(shù)據(jù)
- mysql> INSERT INTO users VALUES(NULL, 'tom', 'M', 23), (NULL, 'jak', 'F', 32), (NULL, 'jus', 'M', 35);
- Query OK, 3 rows affected (0.04 sec)
- Records: 3 Duplicates: 0 Warnings: 0
* 查看基表數(shù)據(jù)
- mysql> SELECT * FROM users;
- +----+------+-----+-----+
- | id | name | sex | age |
- +----+------+-----+-----+
- | 1 | tom | M | 23 |
- | 2 | jak | F | 32 |
- | 3 | jus | M | 35 |
- +----+------+-----+-----+
- 3 rows in set (0.00 sec)
* 創(chuàng)建用戶id和name vusers1視圖，類型為UNDEFINED
- mysql> CREATE ALGORITHM=UNDEFINED VIEW vusers1 AS SELECT id, name FROM users;
- Query OK, 0 rows affected (0.01 sec)
* 分析查看 vusers1 視圖執(zhí)行計(jì)劃
[ 未使用臨時(shí)表 ]
- mysql> EXPLAIN SELECT * FROM vusers1;
- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
- | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
- 1 row in set, 1 warning (0.00 sec)
* 創(chuàng)建用戶id和name vusers2視圖，類型為merge
- mysql> CREATE ALGORITHM=MERGE VIEW vuser2 AS SELECT id, name FROM users;
- Query OK, 0 rows affected (0.03 sec)
* 分析查看 vusers2 視力執(zhí)行計(jì)劃
[ 未使用臨時(shí)表 ]
- mysql> EXPLAIN SELECT * FROM vuser2;
- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
- | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
- 1 row in set, 1 warning (0.01 sec)
* 創(chuàng)建用戶id和name vusers3視圖，類型為T(mén)EMPTABLE
- mysql> CREATE ALGORITHM=TEMPTABLE VIEW vuser3 AS SELECT id, name FROM users;
- Query OK, 0 rows affected (0.19 sec)
* 分析查看 vusers3 視力執(zhí)行計(jì)劃
[ 使用了臨時(shí)表 ]
- mysql> EXPLAIN SELECT * FROM vuser3;
- +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
- | 1 | PRIMARY |
| NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | - | 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
- +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
- 2 rows in set, 1 warning (0.00 sec)
5. 查看視圖信息
* desc 查看視圖基本信息
- mysql> desc vusers1;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | id | bigint(20) | NO | | 0 | |
- | name | varchar(64) | NO | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- 2 rows in set (0.00 sec)
* show table status 查看視圖基本信息
- mysql> show table status like 'vusers1';
- +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
- | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
- +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
- | vuser1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW |
- +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
- 1 row in set (0.00 sec)
* show create view 查看視圖信息
- mysql> show create view vuser1;
- +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
- | View | Create View | character_set_client | collation_connection |
- +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
- | vuser1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vuser1` AS select `users`.`id` AS `id`,`users`.`name` AS `name` from `users` | utf8 | utf8_general_ci |
- +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
- 1 row in set (0.00 sec)
6. 總結(jié)
以需求驅(qū)動(dòng)技術(shù)，技術(shù)本身沒(méi)有優(yōu)略之分，只有業(yè)務(wù)之分。
當(dāng)前題目：MySQL DDL操作--------視圖更優(yōu)實(shí)戰(zhàn)
鏈接分享：http://www.5511xx.com/article/dpeohio.html
