真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

成都創(chuàng)新互聯(lián)網(wǎng)站制作重慶分公司

MySQL視圖

前言：

什么是視圖？

創(chuàng)新互聯(lián)專注于鎮(zhèn)賚企業(yè)網(wǎng)站建設(shè),響應(yīng)式網(wǎng)站,商城網(wǎng)站制作。鎮(zhèn)賚網(wǎng)站建設(shè)公司,為鎮(zhèn)賚等地區(qū)提供建站服務(wù)。全流程按需規(guī)劃網(wǎng)站，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務(wù)

一張?zhí)摫?，和真?shí)的表一樣。視圖包含一系列帶有名稱的行和列數(shù)據(jù)。視圖是從一個(gè)或多個(gè)表中導(dǎo)出來(lái)的，我們可以通過(guò)insert，update，delete來(lái)操作視圖。當(dāng)通過(guò)視圖看到的數(shù)據(jù)被修改時(shí)，相應(yīng)的原表的數(shù)據(jù)也會(huì)變化。同時(shí)原表發(fā)生變化，則這種變化也可以自動(dòng)反映到視圖中。

視圖具有以下優(yōu)點(diǎn)：

  • 簡(jiǎn)單化：看到的就是需要的。視圖不僅可以簡(jiǎn)化用戶對(duì)數(shù)據(jù)的理解，也可以簡(jiǎn)化操作。經(jīng)常被使用的查詢可以制作成一個(gè)視圖；
  • 安全性：通過(guò)視圖用戶只能查詢和修改所能見(jiàn)到的數(shù)據(jù)，數(shù)據(jù)庫(kù)中其他的數(shù)據(jù)既看不見(jiàn)也取不到。數(shù)據(jù)庫(kù)授權(quán)命令可以讓每個(gè)用戶對(duì)數(shù)據(jù)庫(kù)的檢索限制到特定的數(shù)據(jù)庫(kù)對(duì)象上，但不能授權(quán)到數(shù)據(jù)庫(kù)特定的行，列上；
  • 邏輯數(shù)據(jù)獨(dú)立性：視圖可幫助用戶屏蔽真實(shí)表結(jié)構(gòu)變化帶來(lái)的影響。

視圖和表的區(qū)別以及聯(lián)系是什么？

兩者的區(qū)別：

  • 視圖是已經(jīng)編譯好的SQL語(yǔ)句，是基于SQL語(yǔ)句的結(jié)果集的可視化的表，而表不是；
  • 視圖沒(méi)有實(shí)際的物理記錄，而表有；
  • 表是內(nèi)容，視圖窗口；
  • 表和視圖雖然都占用物理空間，但是視圖只是邏輯概念存在，而表可以及時(shí)對(duì)數(shù)據(jù)進(jìn)行修改，但是視圖只能用創(chuàng)建語(yǔ)句來(lái)修改 ；
  • 視圖是查看數(shù)據(jù)表的一種方法，可以查詢數(shù)據(jù)表中某些字段構(gòu)成的數(shù)據(jù)，只是一些SQL 語(yǔ)句的集合。從安全角度來(lái)說(shuō)，視圖可以防止用戶接觸數(shù)據(jù)表，因而不知道表結(jié)構(gòu) ；
  • 表屬于全局模式中的表，是實(shí)表。而視圖屬于局部模式的表，是虛表；
  • 視圖的建立和刪除只影響視圖本身，而不影響對(duì)應(yīng)表的基本表。

兩者的聯(lián)系：

視圖是在基本表之上建立的表，它的結(jié)構(gòu)和內(nèi)容都來(lái)自于基本表，它依賴基本表存在而存在。一個(gè)視圖可以對(duì)應(yīng)一個(gè)基本表，也可以對(duì)應(yīng)多個(gè)基本表。視圖是基本的抽象和邏輯意義上建立的關(guān)系。

一、創(chuàng)建視圖

1、創(chuàng)建單表視圖

MySQL> create table t(quantity int,price int);          
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(3,50);               
Query OK, 1 row affected (0.00 sec)

mysql> create view view_t as select quantity,price,quantity*price as tottal from t;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from view_t;         
+----------+-------+-------+
| quantity | price | total |
+----------+-------+-------+
|        3 |    50 |   150 |
+----------+-------+-------+
1 row in set (0.00 sec)

2、創(chuàng)建多表視圖


mysql> create table student
    -> (
    -> s_id int(3) primary key,
    -> s_name varchar(30),
    -> s_age int(3),
    -> s_sex varchar(8)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table stu_info
    -> (
    -> s_id int(3),
    -> class varchar(50),
    -> addr varchar(100)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into stu_info values
    -> (1,'erban','anhui'),
    -> (2,'sanban','chongqing'),
    -> (3,'yiban','shangdong');

mysql> create view stu_class(id,name,class) as 
    -> select student.s_id,student.s_name,stu_info.class
    -> from student,stu_info where student.s_id=stu_info.s_id;

3、查看視圖的相關(guān)信息


mysql> desc stu_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | NO   |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| class | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show table status like 'stu_class'\G
*************************** 1. row ***************************
           Name: stu_class
         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.01 sec)

mysql> show create view stu_class\G
*************************** 1. row ***************************
                View: stu_class
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stu_class` AS select `student`.`s_id` AS `id`,`student`.`s_name` AS `name`,`stu_into`.`class` AS `class` from (`student` join `stu_into`) where (`student`.`s_id` = `stu_into`.`s_id`)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql> select * from information_schema.views where TABLE_SCHEMA='test02'\G

4、修改視圖

方式一：


mysql> create or replace view view_t as select * from t;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from view_t;     
+----------+-------+
| quantity | price |
+----------+-------+
|        3 |    50 |
+----------+-------+
1 row in set (0.00 sec)

方式二：

mysql> alter view view_t(abc) as select quantity from t;   
Query OK, 0 rows affected (0.00 sec)

mysql> desc view_t;          
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| abc   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

5、更新視圖

1）update指令更新

mysql> select * from t;
+----------+-------+
| quantity | price |
+----------+-------+
|        3 |    50 |
+----------+-------+
1 row in set (0.00 sec)

mysql> select * from view_t;
+------+
| abc  |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

mysql> update view_t set abc=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from view_t;               
+------+
| abc  |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

mysql> select * from t;                  
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
+----------+-------+
1 row in set (0.00 sec)
2）insert指令更新
mysql> select * from t;               
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
+----------+-------+
1 row in set (0.00 sec)

mysql> select * from view_t;             
+------+
| abc  |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

mysql> insert into t values(3,5);       
Query OK, 1 row affected (0.00 sec)

mysql> select * from view_t;             
+------+
| abc  |
+------+
|    5 |
|    3 |
+------+
2 rows in set (0.00 sec)
mysql> select * from t;                     
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
|        3 |     5 |
+----------+-------+
2 rows in set (0.00 sec)
3） delete指令刪除表數(shù)據(jù)

mysql> create view view_t2(qty,price,total) as select quantity,price,quantity*price from t;
mysql> select * from view_t2;         
+------+-------+-------+
| qty  | price | total |
+------+-------+-------+
|    5 |    50 |   250 |
|    3 |     5 |    15 |
+------+-------+-------+
2 rows in set (0.00 sec)
mysql> delete from view_t2 where price=5;           
Query OK, 1 row affected (0.00 sec)

mysql> select * from view_t2;            
+------+-------+-------+
| qty  | price | total |
+------+-------+-------+
|    5 |    50 |   250 |
+------+-------+-------+
1 row in set (0.00 sec)
mysql> select * from t;              
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
+----------+-------+
1 row in set (0.00 sec)

6、刪除視圖

mysql> drop view view_t;

文章標(biāo)題：MySQL視圖
網(wǎng)站路徑：http://weahome.cn/article/gjepgh.html

其他資訊

在線咨詢

微信咨詢

電話咨詢

028-86922220（工作日）

18980820575（7×24）

提交需求

返回頂部