MySQL视图基本操作

共计 3602 个字符，预计需要花费 10 分钟才能阅读完成。

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)

：