MySQL-数据库视图操作

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)