MySQL视图是一个虚拟表，内容由select查询语句定义, 同真实的表数据一致, 但是视图并不在数据库中以存储的数据值形式存在。
试图引用自定义查询表的字段, 并且在引用试图时动态生成, 对其所引用的基础表来说MySQL视图的作用类似于筛选。
- 定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图
- 视图查询没有任何限制，通过它们进行数据修改时的限制也很少
- 视图是存储在数据库中的SQL查询语句，它主要出于两种原因：
- 安全原因, 视图可以隐藏一些敏感的信息
- 简化查询, 使复杂的查询易于理解和使用
一、创建视图
1.创建单表视图
CREATE VIEW 视图名 AS SELECT 语句;
mysql> create database bgx default character set utf8; Query OK, 1 row affected (0.00 sec) mysql> use bgx; Database changed mysql> create view u_grant AS select user,host,authentication_string from mysql.user; Query OK, 0 rows affected (0.00 sec) mysql> select * from u_grant; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | % | *5F2035C0E578A25296968F8A3F33B8D9EFE1C8F0 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | +---------------+-----------+-------------------------------------------+ 3 rows in set (0.00 sec)
2.创建多表视图环境准备
创建产品表
mysql> create database shop; Query OK, 1 row affected (0.01 sec) mysql> use shop; Database changed 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
创建销售表
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()); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0
查询产品卖出金额
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)
二、查看视图
1.查看视图名
SHOW TABLES 视图名;
2.查看视图详细信息
SHOW TABLE STATUS FROM 视图名;
3.查看视频定义信息
SHOW CREATE VIEW 视图名;
4.查看视图表结构
DESC 视图名;
三、修改视图
1.删除后新创建
mysql> drop view bgx.u_grant; Query OK, 0 rows affected (0.00 sec) mysql> create view bgx.u_grant AS select user,host from mysql.user; Query OK, 0 rows affected (0.01 sec) mysql> select * from bgx.u_grant; +---------------+-----------+ | user | host | +---------------+-----------+ | root | % | | mysql.session | localhost | | mysql.sys | localhost | +---------------+-----------+ 3 rows in set (0.00 sec)
2.使用alter修改视图
mysql> alter view bgx.u_grant AS select user,host,authentication_string from mysql.user; Query OK, 0 rows affected (0.00 sec) mysql> select * from bgx.u_grant; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | % | *5F2035C0E578A25296968F8A3F33B8D9EFE1C8F0 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | +---------------+-----------+-------------------------------------------+ 3 rows in set (0.00 sec)
四、删除视图
语法
DROP VIEW view_name [,view_name]…;
mysql> use bgx; Database changed mysql> drop view u_grant; Query OK, 0 rows affected (0.00 sec)
