MySQL视图简介与操作

2022-01-10 01:50:52

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)

码农公寓