chuangjian视图 mysql mysql创建视图的作用

转载

jacksky 2023-07-10 13:35:48

文章标签 chuangjian视图 mysql 数据库 mysql sql 视图 文章分类 MySQL 数据库

MySQL视图

一、创建视图

二、修改视图

三、更新视图

四、删除视图

一、创建视图

1.视图简介
一张虚表，和真实的表一样。视图包含一系列带有名称的行和列数据。视图是从一个或多个表中导出来的，可以通过insert，update，delete来操作视图。虽然视图只是一个SELECT结果的展示，但是可以通过视图来对原表数据进行修改。同时原表发生变化，则这种变化也可以自动反映到视图中。
视图就是将一条SELECT语句的执行结果另存为一张虚拟表，在生产中视图主要是查询使用，将一些不敏感的数据存储成一个视图给专门人员访问。
2.视图的优点

(1)简单化：看到的就是需要的。
视图不仅可以简化用户对数据的理解，也可以简化操作。经常被使用的查询可以制作成一个视图。

(2)安全性：通过视图用户只能查询和修改所能见到的数据，数据库中其他的数据既看不见也取不到。
数据库授权命令可以让每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定的行，列上。

(3)逻辑数据独立性：视图可帮助用户屏蔽真实表结构变化带来的影响。

3.创建视图语法

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

ALGORITHM视图选择的算法解释

Undefined：默认会被选择，但是不常用。

merge：表示将使用的视图语句与视图定义合并起来，使视图定义的某一部分取代语句对应的部分。 

temptable：表示将视图的结果存入临时表，然后用临时表来执行语句。

[WITH [CASCADED | LOCAL] CHECK OPTION]解释

默认为cascaded：表示更新视图时，满足所有相关视图和表的条件。

Local：表示更新视图时，满足该视图本身定义的条件即可。

4.创建单表视图

mysql> create table t(quantity int,price int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(3,50);
Query OK, 1 row affected (0.00 sec)

mysql> create view view_t as select quantity,price,quantity*price from t;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_t;
+----------+-------+----------------+
| quantity | price | quantity*price |
+----------+-------+----------------+
|        3 |    50 |            150 |
+----------+-------+----------------+
1 row in set (0.00 sec)

mysql> create view view_t2(qty,price,total) as select quantity,price,quantity*price from t;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_t2;
+------+-------+-------+
| qty  | price | total |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+
1 row in set (0.00 sec)

4.创建多表视图

mysql> create table student
    -> (
    -> s_id int(3) primary key,
    -> s_name varchar(30),
    -> s_age int(3),
    -> s_sex varchar(8)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table stu_info
    -> (
    -> s_id int(3),
    -> class varchar(50),
    -> addr varchar(100)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into stu_info
    -> (s_id,class,addr)
    -> values
    -> (1,'erban','anhui'),
    -> (2,'sanban','chongqing'),
    -> (3,'yiban','shandong');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create view stu_class(id,name,class) 
    -> as select student.s_id,student.s_name,stu_info.class
    -> from student,stu_info
    -> where student.s_id=stu_info.s_id;
Query OK, 0 rows affected (0.00 sec)

5.查看视图的基本信息
(1)desc(describe)+视图名

mysql> desc stu_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | NO   |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| class | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

(2)show table status like + ‘视图名’\G

mysql> show table status like 'stu_class'\G;
*************************** 1. row ***************************
           Name: stu_class
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.00 sec)

(3)show create view + 视图名\G

mysql> show create view view_t\G
*************************** 1. row ***************************
                View: view_t
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t` AS select `t`.`quantity` AS `quantity`,`t`.`price` AS `price`,(`t`.`quantity` * `t`.`price`) AS `quantity*price` from `t`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

(4)在mysql中，information schema数据库下的views表中存储了所有视图的定义。通过对views表的查询，可以查看数据库中所有视图的详细信息。

mysql> select * from information_schema.views where table_name='view_t'\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: test
          TABLE_NAME: view_t
     VIEW_DEFINITION: select `test`.`t`.`quantity` AS `quantity`,`test`.`t`.`price` AS `price`,(`test`.`t`.`quantity` * `test`.`t`.`price`) AS `quantity*price` from `test`.`t`
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.00 sec)

二、修改视图

1.使用create or replace view修改视图

mysql> select * from t;
+----------+-------+
| quantity | price |
+----------+-------+
|        3 |    50 |
+----------+-------+
1 row in set (0.00 sec)

mysql> select * from view_t;
+----------+-------+----------------+
| quantity | price | quantity*price |
+----------+-------+----------------+
|        3 |    50 |            150 |
+----------+-------+----------------+
1 row in set (0.00 sec)

mysql> create or replace view view_t as select * from t;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_t;
+----------+-------+
| quantity | price |
+----------+-------+
|        3 |    50 |
+----------+-------+
1 row in set (0.00 sec)

2.使用alter修改视图

mysql> select * from view_t;
+----------+-------+
| quantity | price |
+----------+-------+
|        3 |    50 |
+----------+-------+
1 row in set (0.00 sec)

mysql> alter view view_t as select quantity from t;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_t;
+----------+
| quantity |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

三、更新视图

1.使用update更新视图

mysql> select * from t;
+----------+-------+
| quantity | price |
+----------+-------+
|        3 |    50 |
+----------+-------+
1 row in set (0.00 sec)

mysql> select * from view_t;
+----------+
| quantity |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> update view_t set quantity=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from view_t;
+----------+
| quantity |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select * from t;
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
+----------+-------+
1 row in set (0.00 sec)

2使用insert更新视图

mysql> insert into t values(3,5);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
|        3 |     5 |
+----------+-------+
2 rows in set (0.00 sec)

mysql> select * from view_t;
+----------+
| quantity |
+----------+
|        5 |
|        3 |
+----------+
2 rows in set (0.00 sec)

3.使用delete更新视图

mysql> select * from view_t2;
+------+-------+-------+
| qty  | price | total |
+------+-------+-------+
|    5 |    50 |   250 |
|    3 |     5 |    15 |
+------+-------+-------+
2 rows in set (0.00 sec)

mysql> delete from view_t2 where price=5;
Query OK, 1 row affected (0.00 sec)

mysql> select * from view_t2;
+------+-------+-------+
| qty  | price | total |
+------+-------+-------+
|    5 |    50 |   250 |
+------+-------+-------+
1 row in set (0.00 sec)

视图存在以下情况时，更新操作无法执行。

(1)视图中不包含基表中被定义为非空的列;

(2)在定义视图的select语句后的字段列表中使用了数学表达式;

(3)在定义视图的select语句后字段列表中使用了聚合函数时不接受更新操作;

(4)select中，使用了union\top\group by或having无法接受;

四、删除视图

1.使用drop view删除视图

Drop view [IF EXISTS]视图名1，视图名2 ……

mysql> drop view stu_class;
Query OK, 0 rows affected (0.00 sec)

mysql> show create view stu_class;
ERROR 1146 (42S02): Table 'test.stu_class' doesn't exist

2.扩展MySQL中视图和表的区别以及联系是什么？
(1)两者的区别

1.视图是已经编译好的SQL语句,是基于SQL语句的结果集的可视化的表,而表不是。

2.视图没有实际的物理记录,而表有。

3.表是内容，视图是窗口。

4.表和视图虽然都占用物理空间,但是视图只是逻辑概念存在,而表可以及时对数据进行修改,但是视图只能用创建语句来修改。

5.视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些SQL语句的集合。从安全角度来说,视图可以防止用户接触数据表,因而不知道表结构。

6.表属于全局模式中的表,是实表。而视图属于局部模式的表,是虚表。

7.视图的建立和删除只影响视图本身,而不影响对应表的基本表。

(2)两者的联系
视图是在基本表之上建立的表，它的结构和内容都来自于基本表，它依赖基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本的抽象和逻辑意义上建立的关系。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。