命令记不住

登陆

MySQL 视图操作

  |   129 浏览

MySQL视图操作

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)
推荐阅读