Note/大学笔记/数据库导论/笔记/数据库笔记06——视图——2022-04-05.md

# 视图



## 一、视图概述

虚拟表，是从数据库中一个或多个表中导出来的

简单，安全性，权限限制



## 二、创建视图（create view）

```mysql
create view 视图名 as 查询语句;
示例：
create view vt as select quantity,price,quantity*price from t;
```

![image-20220405082050293](https://lsky.hhdxw.top/imghub/img/image-20220405082050293.png)

```mysql
create view 视图名(表中列新名称) as 查询语句;
示例：
create view vt2(qty,p,total) as select quantity,price,quantity*price from t;
```

![image-20220405082320391](https://lsky.hhdxw.top/imghub/img/image-20220405082320391.png)

```mysql
在多个表中创建视图
create view vt4 as select stu.s_id,name,glass from stu join info on stu.s_id=info.a_id;
```



## 三、查看视图

### 1.describe查看视图基本信息

```mysql
desc vt4;
```

![image-20220405084010690](https://lsky.hhdxw.top/imghub/img/image-20220405084010690.png)

### 2.show table status like'视图名'语句查看视图的基本信息

查询视图信息：

```mysql
show table status like 'vt4';
```

![image-20220405085355433](https://lsky.hhdxw.top/imghub/img/image-20220405085355433.png)

查询结果，comment值为view说明该为视图，其他大部分信息为null说明是一个虚表

查询表信息：

```mysql
show table status like'stu';
```

![image-20220405085422810](https://lsky.hhdxw.top/imghub/img/image-20220405085422810.png)

查询结果包含了存储引擎、行数等信息，comment信息为空，这是表和视图查询结果的区别

### 3.show create view 视图名：语句查看视图的详细信息（视图名、创建视图的语句等信息)

```mysql
show create view vt4;
```

![image-20220405090735295](https://lsky.hhdxw.top/imghub/img/image-20220405090735295.png)

### 4.在views表中查询所有视图

```mysql
select * from information_schema.views;
```



## 四、修改视图

### 1.create ro replace view

```mysql
create ro replace view 视图名 as 查询语句;
示例：
create ro replace view vt as select * from t;
```

![image-20220405091444983](https://lsky.hhdxw.top/imghub/img/image-20220405091444983.png)

### 2.alter view

```mysql
alter view 视图名 as 查询语句;
示例：
alter view vt as select price from t;
```

![image-20220405091450188](https://lsky.hhdxw.top/imghub/img/image-20220405091450188.png)

### 

## 五、更新视图（相当于更新基本表）

# 1.update

```mysql
update 视图名 set 字段名=新值;
示例：（表中数据跟随更改）
update vt set price=30;
```

![image-20220405091758090](https://lsky.hhdxw.top/imghub/img/image-20220405091758090.png)

### 2.insert 

```mysql
insert into 视图名 values(数据);
示例：
insert into vt values(80);
```

![image-20220405092415253](https://lsky.hhdxw.top/imghub/img/image-20220405092415253.png)

### 3.delect

```mysql
delete from 视图名 where 条件;
示例：
delete from vt where price=80;
```

![image-20220405092640643](https://lsky.hhdxw.top/imghub/img/image-20220405092640643.png)

## 六、删除视图

```mysql
drop view 视图名;
添加判断
drop view if exists vt;
```

![image-20220405093346162](https://lsky.hhdxw.top/imghub/img/image-20220405093346162.png)

## 七、综合案例