数据库视图操作

MySQL中的视图是⼀种虚拟表 ，它是基于 ⼀个或多个基础表（或其他视图）的select查询结果集 。虚拟表是⼀种逻辑
上的表，它不实际存储数据，⽽是在查询时动态⽣成结果集。因此，视图并不是真正的表，⽽是基于⼀个或多个表
的查询结果的⼀种“虚拟表”。

视图可以看作是对基础表的⼀个逻辑上的封装，它可以隐藏基础表的复杂性，简化查询操作，并提供数据安全性和
数据保护。视图可以像表⼀样进⾏查询、更新、插⼊和删除操作，但实际上它们不存储任何数据，⽽是在查询时动
态⽣成结果集。

视图应用场景:

1. 简化查询操作：视图可以将复杂的查询操作封装起来，使得⽤户可以通过⼀个简单的视图来访问需要的数据。
2. 数据安全性和数据保护：通过视图可以限制⽤户对某些数据的访问权限，保护数据的安全性。
3. 数据库设计：视图可以⽤于数据库设计中，通过将多个表的数据合并到⼀个视图中，简化数据库的设计和维
   护。
4. 性能优化：视图可以提⾼查询性能，通过将多个表的查询结果缓存到视图中，可以避免重复查询。

# 创建视图
CREATE VIEW 视图名 AS SELECT 语句:
mysql>  create view u_grant AS select user,host,authentication_string from mysql.user;

mysql> select * from u_grant;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *FBBAE4DB31FB8548A70C45A2D8CB413D885A64E4 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root          | %         | *FBBAE4DB31FB8548A70C45A2D8CB413D885A64E4 |
+---------------+-----------+-------------------------------------------+

# 创建多表视图环境准备
mysql>  create database shop;
Query OK, 1 row affected (0.00 sec)

mysql>  use shop;
Database changed

#创建产品表
#字段说明: name商品名称 price价格
mysql> create table product(
    -> id int unsigned auto_increment primary key not null,
    -> name varchar(60) not null,
    -> price double not null
    -> );
Query OK, 0 rows affected (0.01 sec)

#插⼊商品数据
mysql> insert into product(name,price) values
    -> ('apple',5),
    -> ('balane',6),
    -> ('pear',7);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

#创建销售表
#字段说明: name 商品名称 quantity 数量 gen_time时间
mysql>  create table purchase(
    -> id int unsigned auto_increment primary key not null,
    -> name varchar(60) not null,
    -> quantity int not null default 0,
    -> gen_time datetime not null);
Query OK, 0 rows affected (0.01 sec)

#插⼊销售数据
mysql> insert into purchase(name,quantity,gen_time) values
('apple',7,now()),
('pear',10,now());

#查询商品卖出⾦额
mysql> select product.name,product.price,
    -> purchase.quantity,
    -> product.price * purchase.quantity as total_value
    -> from product,purchase
    -> where product.name = purchase.name;
+-------+-------+----------+-------------+
| name  | price | quantity | total_value |
+-------+-------+----------+-------------+
| apple |     5 |        7 |          35 |
| pear  |     7 |       10 |          70 |
+-------+-------+----------+-------------+
2 rows in set (0.00 sec)

#创建多表视图
mysql> create view total_product AS
    -> select product.name,product.price,purchase.quantity,
    -> product.price * purchase.quantity AS Total
    -> from purchase,product
    -> where product.name = purchase.name;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from total_product;
+-------+-------+----------+-------+
| name  | price | quantity | Total |
+-------+-------+----------+-------+
| apple |     5 |        7 |    35 |
| pear  |     7 |       10 |    70 |
+-------+-------+----------+-------+
2 rows in set (0.00 sec)

#再次卖出产品后
mysql>  insert into purchase(name,quantity,gen_time) values('balane',20,now());
Query OK, 1 row affected (0.00 sec)

# 再次查看视图
mysql> select * from total_product;
+--------+-------+----------+-------+
| name   | price | quantity | Total |
+--------+-------+----------+-------+
| apple  |     5 |        7 |    35 |
| balane |     6 |       20 |   120 |
| pear   |     7 |       10 |    70 |
+--------+-------+----------+-------+
3 rows in set (0.00 sec)


# 1.查看视图名
SHOW FULL TABLES WHERE TABLE_TYPE='VIEW';

# 2.查看视图详细信息
SHOW TABLE STATUS WHERE Name='视图名';

SHOW TABLE STATUS WHERE Name='total_product'\G

# 查看视图创建定义信息
SHOW CREATE VIEW 视图名\G

SHOW CREATE VIEW total_product;

# 查看视图结构
DESC 视图名;

# 修改视图
mysql> drop view test.u_grant;
mysql> create view test.u_grant AS
select user,host from mysql.user;
mysql> select * from test.u_grant;

# 使⽤alter修改视图
mysql> alter view test.u_grant AS
select user,host,authentication_string from mysql.user;

mysql> select * from test.u_grant;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

# 删除视图
语法DROP VIEW view_name [,view_name]…;

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop view u_grant;
Query OK, 0 rows affected (0.01 sec)