04.MySQL视图操作

• 1.创建视图
• 2.查看视图
• 3.修改视图
• 4.删除视图

徐亮伟, 江湖人称标杆徐。多年互联网运维工作经验，曾负责过大规模集群架构自动化运维管理工作。擅长Web集群架构与自动化运维，曾负责国内某大型电商运维工作。
个人博客"徐亮伟架构师之路"累计受益数万人。
笔者Q:552408925、572891887
架构师群:471443208

MySQL视图是一个虚拟表，内容由select查询语句定义, 同真实的表数据一致, 但是视图并不在数据库中以存储的数据值形式存在。
试图引用自定义查询表的字段, 并且在引用试图时动态生成, 对其所引用的基础表来说MySQL视图的作用类似于筛选。

• 1.定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。
• 2.视图查询没有任何限制，通过它们进行数据修改时的限制也很少。
• 3.视图是存储在数据库中的SQL查询语句，它主要出于两种原因：
  • 1.安全原因, 视图可以隐藏一些敏感的信息。
  • 2.简化查询, 使复杂的查询易于理解和使用。

1.创建视图

1.创建单表视图

CREATE VIEW 视图名 AS SELECT 语句;

mysql> use bgx;
mysql> create view u_grant AS select user,host,authentication_string from mysql.user;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from u_grant;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *4927A5B79C852EA2CE585A7679C5C26DF683F18C |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

2.创建多表视图环境准备

mysql> create database shop;
mysql> use shop;

#创建产品表
mysql> create table product(
id int unsigned auto_increment primary key not null,
name varchar(60) not null,
price double not null
);
#插入产品数据
mysql> insert into product(name,price) values
('apple',5),
('balane',6),
('pear',7);

#创建销售表
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);
#插入销售数据
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)

3.创建多表视图

mysql> create view totol_product AS 
select product.name,product.price,purchase.quantity,
product.price * purchase.quantity AS Total 
from purchase,product  
where purchase.name = product.name;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from totol_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 totol_product;
+--------+-------+----------+-------+
| name   | price | quantity | Total |
+--------+-------+----------+-------+
| apple  |     5 |        7 |    35 |
| balane |     6 |       20 |   120 |
| pear   |     7 |       10 |    70 |
+--------+-------+----------+-------+
3 rows in set (0.00 sec)

2.查看视图

• 1.查看视图名
  • SHOW TABLES 视图名;
• 2.查看视图详细信息
  • SHOW TABLE STATUS FROM 视图名\G
• 3.查看视频定义信息
  • SHOW CREATE VIEW 视图名\G
• 4.查看视图表结构
  • DESC 视图名;

3.修改视图

1.删除后新创建

mysql> drop view bgx.u_grant;
mysql> create view bgx.u_grant AS
select user,host from mysql.user;

mysql> select * from bgx.u_grant;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.01 sec)

2.使用alter修改视图

mysql> alter view bgx.u_grant AS
select user,host,authentication_string from mysql.user;
mysql> select * from bgx.u_grant;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *4927A5B79C852EA2CE585A7679C5C26DF683F18C |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

4.删除视图

语法DROP VIEW view_name [,view_name]…;

mysql> use bgx;
mysql> drop view u_grant;
Query OK, 0 rows affected (0.00 sec)