MySQL檢視簡介與操作

秦無殤發表於2019-04-14
MySql

1、準備工作

在MySQL資料庫中建立兩張表balance（餘額表）和customer（客戶表）並插入資料。

create table customer(
    id int(10) primary key,
    name char(20) not null,
    role char(20) not null,
    phone char(20) not null,
    sex char(10) not null,
    address char(50) not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#外來鍵為customerId
create table balance(
    id int(10) primary key,
    customerId int(10) not null,
    balance DECIMAL(10,2),
    foreign key(customerId) references customer(id) 

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

向客戶表和餘額表中各插入3條資料。

insert into customer values(0001,"xiaoming",'vip1','12566666','male','江寧區888號');
insert into customer values(0002,"xiaohong",'vip10','15209336760','male','建鄴區888號');
insert into customer values(0003,"xiaocui",'vip11','15309336760','female','新街口888號');

insert into balance values(1,0001,900.55);
insert into balance values(2,0002,900.55);
insert into balance values(3,0003,10000);

2、檢視簡介

檢視可以簡單理解成虛擬表，它和資料庫中真實存在資料表不同，檢視中的資料是基於真實表查詢得到的。檢視和真實表一樣具備相似的結構。真實表的更新，查詢，刪除等操作，檢視也支援。那麼為什麼需要檢視呢？

a、提升真實表的安全性：檢視是虛擬的，可以只授予使用者檢視的許可權而不授予真實表的許可權，起到保護真實表的作用。
b、定製化展示資料：基於同樣的實際表，可以通過不同的檢視來向不同需求的使用者定製化展示資料。
c、簡化資料操作：適用於查詢語句比較複雜使用頻率較高的場景，可以通過檢視來實現。
......

需要說明一點的是：檢視相關的操作需要使用者具備相應的許可權。以下操作使用root使用者，預設使用者具備操作許可權。

建立檢視語法

create view <檢視名稱>  as <select語句>;

修改檢視語法

修改檢視名稱可以先刪除，再用相同的語句建立。

#更新檢視結構
alter view <檢視名稱>  as <select語句>;
#更新檢視資料相當於更新實際表,不適用基於多表建立的檢視
update ....

注意：部分檢視的資料是無法更新，也就是無法使用update，insert等語句更新，比如：

a、select語句包含多個表
b、檢視中包含having子句
c、試圖中包含distinct關鍵字
......

刪除檢視語法

drop view <檢視名稱>

3、檢視的操作

基於單表建立檢視

mysql> create view  bal_view 
    -> as
    -> select * from  balance;
Query OK, 0 rows affected (0.22 sec)

建立完成後，檢視bal_view的結構和記錄。可以發現通過檢視查詢到資料和通過真實表查詢得到的結果完全一樣。

#查詢bal_view的結構
mysql> desc bal_view;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | int(10)       | NO   |     | NULL    |       |
| customerId | int(10)       | NO   |     | NULL    |       |
| balance    | decimal(10,2) | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
3 rows in set (0.07 sec)
#查詢bal_view中的記錄
mysql> select  * from bal_view;
+----+------------+----------+
| id | customerId | balance  |
+----+------------+----------+
|  1 |          1 |   900.55 |
|  2 |          2 |   900.55 |
|  3 |          3 | 10000.00 |
+----+------------+----------+
3 rows in set (0.01 sec)

通過建立檢視的語句不難得出結論：當真實表中的資料發生改變時，檢視中的資料也會隨之改變。那麼當檢視中的資料發生改變時，真實表中的資料會變化嗎？來實驗一下，修改id=1的客戶balance為2000。

mysql> update bal_view set balance=2000 where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

來看一下真實表balance中的資料。

mysql> select  * from bal_view where id=1;
+----+------------+---------+
| id | customerId | balance |
+----+------------+---------+
|  1 |          1 | 2000.00 |
+----+------------+---------+
1 row in set (0.03 sec)

結論：檢視表中的資料發生變化時，真實表中的資料也會隨之改變。

基於多表建立檢視

建立檢視cus_bal,共兩個欄位客戶名稱和餘額。

mysql> create view cus_bal
    -> (cname,bal)
    -> as
    -> select customer.name,balance.balance from customer ,balance
    -> where customer.id=balance.customerId;
Query OK, 0 rows affected (0.05 sec)
#檢視cus_bal中的資料
mysql> select *  from  cus_bal;
+----------+----------+
| cname    | bal      |
+----------+----------+
| xiaoming |  2000.00 |
| xiaohong |   900.55 |
| xiaocui  | 10000.00 |
+----------+----------+
3 rows in set (0.28 sec)

修改檢視

將cus_bal檢視中的cname改成cusname。

mysql> alter view  cus_bal
    -> (cusname,bal)
    -> as
    -> select customer.name,balance.balance from customer ,balance
    -> where customer.id=balance.customerId;
Query OK, 0 rows affected (0.06 sec）
#檢視修改後檢視結構。
mysql> desc  cus_bal;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| cusname | char(20)      | NO   |     | NULL    |       |
| bal     | decimal(10,2) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

修改基於多表建立的檢視

mysql> insert into cus_bal(cusname,bal) values ("ee",11);
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'rms.cus_bal'

刪除檢視

刪除檢視cus_bal

drop view cus_bal;
mysql> drop view cus_bal;
Query OK, 0 rows affected (0.00 sec)

相關文章