当前位置:Gxlcms > 数据库问题 > MySQL 视图的基础操作(五)

MySQL 视图的基础操作(五)

时间:2021-07-01 10:21:17 帮助过:16人阅读

 

1.为什么使用视图: 
    为了提高复杂SQL语句的复用性和表操作的安全性(例如:工资字段不想展示给所有能查看该查询结果的人),MySQL提供了视图特性。所谓视图,本质上是一种虚拟表,其内容与真实的表相似,包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储数据值的形式存在。行和列的数据来自定义视图的查询所引用基本表,并且在具体使用视图时动态生成。 
视图有如下特点; 
      1. 视图的列可以来自不同的表,是表的抽象和逻辑意义上建立的新关系。 
      2. 视图是由基本表(实表)产生的表(虚表)。 
      3. 视图的建立和删除不影响基本表。 
      4. 对视图内容的更新(添加、删除和修改)直接影响基本表。 
      5. 当视图来自多个基本表时,不允许添加和删除数据。

2.创建视图: 
在创建视图时,首先要确保拥有CREATE VIEW的权限,并且同时确保对创建视图所引用的表也具有相应的权限。 
2.1 创建视图的语法形式: 
    虽然视图可以被看成是一种虚拟表,但是其在物理上是不存在的,即数据库管理系统没有专门的位置为视图存储数据。根据视图的概念发现其数据来源于查询语句,因此创建视图的语法为:

CREATE VIEW view_name AS 查询语句
//说明:和创建表一样,视图名不能和表名、也不能和其他视图名重名。视图的功能实际就是封装了复杂的查询语句。

示例:

  1. use zhaojd_test; //选择一个自己创建的库
  2. create table t_product( //创建表
  3. id int primary key,
  4. pname varchar(20),
  5. price decimal(8,2)
  6. );
  7. insert into t_product values(1,‘apple‘,6.5); //向表中插入数据
  8. insert into t_product values(2,‘orange‘,3); //向表中插入数据
  9. create view view_product as select id,name from t_product; //创建视图
  10. select * from view_product;

结果为:
+------+--------+
| id   | name   |
+------+--------+
| 1    | apple  |
| 2    | orange |
+------+--------+
//其实在创建视图时实际代码里写的是一个表查询语句,只不过把这个查询语句封装起来重新起了一个名字,方便可以重复使用。
//再者,安全性方面可以隐藏一些不希望看到的字段,比如这里的价格字段。
//注意:在SQL语句的命名规范中,视图一般以view_xxx或者v_xxx的样式来命名。视图的查询语句和表的查询语句相同。

2.2 创建各种视图: 
    由于视图的功能实际上时封装查询语句,那么是不是任何形式的查询语句都可以封装在视图中呢? 

2.2.1 封装实现查询常量语句的视图(常量视图):

示例:

  1. mysql> create view view_test1 as select 3.1415926;
  2. Query OK, 0 rows affected (0.07 sec)
  3. mysql> select * from view_test1;
  4. +-----------+
  5. | 3.1415926 |
  6. +-----------+
  7. | 3.1415926 |
  8. +-----------+
  9. 1 row in set (0.00 sec)

2.2.2 封装使用聚合函数(SUM、MIN、MAX、COUNT等)查询语句的视图:

示例:
首先准备需要用到的两张表及其初始化数据;

  1. CREATE TABLE t_group(
  2. id INT PRIMARY KEY AUTO_INCREMENT,
  3. NAME VARCHAR(20)
  4. );
  5. CREATE TABLE t_student(
  6. id INT PRIMARY KEY AUTO_INCREMENT,
  7. NAME VARCHAR(20),
  8. sex CHAR(1),
  9. group_id INT,
  10. FOREIGN KEY (group_id) REFERENCES t_group (id)
  11. );
  12. //t_group表中插入数据
  13. INSERT INTO t_group (NAME) VALUES(‘group_1‘);
  14. INSERT INTO t_group (NAME) VALUES(‘group_2‘);
  15. INSERT INTO t_group (NAME) VALUES(‘group_3‘);
  16. INSERT INTO t_group (NAME) VALUES(‘group_4‘);
  17. INSERT INTO t_group (NAME) VALUES(‘group_5‘);
  18. //t_student表中插入数据
  19. INSERT INTO t_student (NAME,sex,group_id) VALUES(‘zjd_1‘,‘M‘,1);
  20. INSERT INTO t_student (NAME,sex,group_id) VALUES(‘zjd_2‘,‘M‘,1);
  21. INSERT INTO t_student (NAME,sex,group_id) VALUES(‘zjd_3‘,‘M‘,2);
  22. INSERT INTO t_student (NAME,sex,group_id) VALUES(‘zjd_4‘,‘W‘,2);
  23. INSERT INTO t_student (NAME,sex,group_id) VALUES(‘zjd_5‘,‘W‘,2);
  24. INSERT INTO t_student (NAME,sex,group_id) VALUES(‘zjd_6‘,‘W‘,2);
  25. INSERT INTO t_student (NAME,sex,group_id) VALUES(‘zjd_7‘,‘M‘,3);
  26. INSERT INTO t_student (NAME,sex,group_id) VALUES(‘zjd_8‘,‘W‘,4);
  27. INSERT INTO t_student (NAME,sex,group_id) VALUES(‘zjd_9‘,‘W‘,4);
  28. ================================================================
  29. mysql> create view view_test2 as select count(name) from t_student;
  30. Query OK, 0 rows affected (0.71 sec)
  31. mysql> select * from view_test2;
  32. +-------------+
  33. | count(name) |
  34. +-------------+
  35. | 9 |
  36. +-------------+
  37. 1 row in set (0.01 sec)

2.2.3 封装了实现排序功能(ORDER BY)查询语句的视图:

示例:

  1. mysql> create view view_test3 as select name from t_student order by id desc;
  2. Query OK, 0 rows affected (0.06 sec)
  3. mysql> select * from view_test3;
  4. +-------+
  5. | name |
  6. +-------+
  7. | zjd_9 |
  8. | zjd_8 |
  9. | zjd_7 |
  10. | zjd_6 |
  11. | zjd_5 |
  12. | zjd_4 |
  13. | zjd_3 |
  14. | zjd_2 |
  15. | zjd_1 |
  16. +-------+
  17. 9 rows in set (0.00 sec)

2.2.4 封装了实现表内连接查询语句的视图:

示例:(第二组学生的姓名)

  1. mysql> create view view_test5 as select s.name from t_student s,t_group g where s.group_id=g.id and g.id=2;
  2. Query OK, 0 rows affected (0.07 sec)
  3. mysql> select * from view_test5;
  4. +-------+
  5. | name |
  6. +-------+
  7. | zjd_3 |
  8. | zjd_4 |
  9. | zjd_5 |
  10. | zjd_6 |
  11. +-------+
  12. 4 rows in set (0.00 sec)

2.2.5 封装了实现表外连接(LEFT JOIN和RIGHT JOIN)查询语句的视图:

示例:(第二组学生姓名)

  1. mysql> create view view_test6 as select s.name from t_student s left join t_group g on s.group_id=g.id where g.id=2;
  2. Query OK, 0 rows affected (0.09 sec)
  3. mysql> select * from view_test6;
  4. +-------+
  5. | name |
  6. +-------+
  7. | zjd_3 |
  8. | zjd_4 |
  9. | zjd_5 |
  10. | zjd_6 |
  11. +-------+
  12. 4 rows in set (0.01 sec)

2.2.6 封装了实现子查询相关查询语句的视图:

示例:

  1. mysql> create view view_test7 as select s.name from t_student s where s.id in(select id from t_group);
  2. Query OK, 0 rows affected (0.08 sec)
  3. mysql> select * from view_test7;
  4. +-------+
  5. | name |
  6. +-------+
  7. | zjd_1 |
  8. | zjd_2 |
  9. | zjd_3 |
  10. | zjd_4 |
  11. | zjd_5 |
  12. +-------+
  13. 5 rows in set (0.00 sec)

2.2.7 封装了实现记录联合(UNION和UNION ALL)查询语句的视图:

 

  1. mysql> create view view_test8 as select id,name from t_student union all select id,name from t_group;
  2. Query OK, 0 rows affected (0.08 sec)
  3. mysql> select * from view_test8;
  4. +----+---------+
  5. | id | name |
  6. +----+---------+
  7. | 1 | zjd_1 |
  8. | 2 | zjd_2 |
  9. | 3 | zjd_3 |
  10. | 4 | zjd_4 |
  11. | 5 | zjd_5 |
  12. | 6 | zjd_6 |
  13. | 7 | zjd_7 |
  14. | 8 | zjd_8 |
  15. | 9 | zjd_9 |
  16. | 1 | group_1 |
  17. | 2 | group_2 |
  18. | 3 | group_3 |
  19. | 4 | group_4 |
  20. | 5 | group_5 |
  21. +----+---------+
  22. 14 rows in set (0.01 sec)

3.查看视图: 
3.1 SHOW TABLES语句查看视图名: 
执行SHOW TABLES 语句时不仅可以显示表的名字,同时也是显示出视图的名字。

示例:

  1. mysql> show tables;
  2. +------------------+
  3. | Tables_in_zhaojd |
  4. +------------------+
  5. | t_group |
  6. | t_product |
  7. | t_student |
  8. | v_product |
  9. | view_test1 |
  10. | view_test2 |
  11. | view_test3 |
  12. | view_test4 |
  13. | view_test5 |
  14. | view_test6 |
  15. | view_test8 |
  16. +------------------+
  17. 11 rows in set (0.00 sec)

 3.2 SHOW TABLE STATUS语句查看视图详细信息: 

和SHOW TABLES语句一样,SHOW TABLE STATUS语句不仅会显示表的详细信息,同时也会显示视图的详细信息。

语法如下:
SHOW TABLE STATUS [FROM db_name] [LIKE ‘pattern‘]
//参数db_name用来设置数据库,SHOW TABLES STATUS表示将显示所设置库的表和视图的详细信息。
//设置LIKE关键字,可以查看某一个具体表或视图的详细信息。例如: SHOW TABLE STATUS FROM zhaojd LIKE ‘t_group‘ \G
示例:

  1. mysql> show table status from zhaojd \G
  2. *************************** 1. row ***************************
  3. Name: t_group
  4. Engine: InnoDB
  5. Version: 10
  6. Row_format: Compact
  7. Rows: 5
  8. Avg_row_length: 3276
  9. Data_length: 16384
  10. Max_data_length: 0
  11. Index_length: 0
  12. Data_free: 7340032
  13. Auto_increment: 6
  14. Create_time: 2016-08-19 16:26:06
  15. Update_time: NULL
  16. Check_time: NULL
  17. Collation: utf8_general_ci
  18. Checksum: NULL
  19. Create_options:
  20. Comment:
  21. =============================================================
  22. Name: view_test8
  23. Engine: NULL
  24. Version: NULL
  25. Row_format: NULL
  26. Rows: NULL
  27. Avg_row_length: NULL
  28. Data_length: NULL
  29. Max_data_length: NULL
  30. Index_length: NULL
  31. Data_free: NULL
  32. Auto_increment: NULL
  33. Create_time: NULL
  34. Update_time: NULL
  35. Check_time: NULL
  36. Collation: NULL
  37. Checksum: NULL
  38. Create_options: NULL
  39. Comment: VIEW

3.3 SHOW CREATE VIEW语句查看视图定义信息:

语法为:
 SHOW CREATE VIEW viewname;
 示例:

  1. mysql> show create view view_test8 \G
  2. *************************** 1. row ***************************
  3. View: view_test8
  4. Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test8` AS select `t_student`.`id` AS `id`,
  5. `t_student`.`NAME` AS `name` from `t_student` union all select `t_group`.`id` AS `id`,`t_group`.`NAME` AS `name` from `t_group`
  6. character_set_client: utf8
  7. collation_connection: utf8_general_ci
  8. 1 row in set (0.00 sec)
  9. //根据执行结果可以发现,SHOW CREATE VIEW语句返回两个字段,分别为表示视图名的View字段和关于视图定义的Create view字段。

3.4 DESCRIBE | DESC 语句查看视图定义信息:

语法为:
 DESCRIBE | DESC viewname;
示例:

  1. mysql> desc view_test8;
  2. +-------+-------------+------+-----+---------+-------+
  3. | Field | Type | Null | Key | Default | Extra |
  4. +-------+-------------+------+-----+---------+-------+
  5. | id | int(11) | NO | | 0 | |
  6. | name | varchar(20) | YES | | NULL | |
  7. +-------+-------------+------+-----+---------+-------+
  8. 2 rows in set (0.02 sec)

3.5 通过系统表查看视图信息: 
当MySQL安装成功后,会自动创建系统数据库infomation_schema。在该数据库中存在一个包含视图信息的表格,可以通过查看表格views来查看所有视图的相关信息。

示例:

  1. mysql> use information_schema;
  2. Database changed
  3. mysql> select * from views where table_name=‘view_test8‘ \G
  4. *************************** 1. row ***************************
  5. TABLE_CATALOG: def
  6. TABLE_SCHEMA: zhaojd
  7. TABLE_NAME: view_test8
  8. VIEW_DEFINITION: select `zhaojd`.`t_student`.`id`AS`id`,`zhaojd`.`t_student`.`NAME` AS `name` from `zhaojd`.`t_student` union all select `zhaojd`.`t_group`.`id` AS`id`,`zhaojd`.`t_group`.`NAME` AS `name` from `zhaojd`.`t_group`
  9. CHECK_OPTION: NONE
  10. IS_UPDATABLE: NO
  11. DEFINER: root@localhost
  12. SECURITY_TYPE: DEFINER
  13. CHARACTER_SET_CLIENT: utf8
  14. COLLATION_CONNECTION: utf8_general_ci
  15. 1 row in set (0.01 sec)

4.删除视图: 
在删除视图时首先要确保拥有删除视图的权限。

语法为:
DROP VIEW view_name [,view_name] ......
//从语法可以看出,DROP VIEW一次可以删除多个视图
示例:

  1. mysql> use zhaojd;
  2. Database changed
  3. mysql> show tables;
  4. +------------------+
  5. | Tables_in_zhaojd |
  6. +------------------+
  7. | t_group |
  8. | t_product |
  9. | t_student |
  10. | v_product |
  11. | view_test1 |
  12. | view_test2 |
  13. | view_test3 |
  14. | view_test4 |
  15. | view_test5 |
  16. | view_test6 |
  17. | view_test8 |
  18. +------------------+
  19. 11 rows in set (0.00 sec)
  20. mysql> drop view view_test1, view_test2;
  21. Query OK, 0 rows affected (0.01 sec)
  22. mysql> show tables;
  23. +------------------+
  24. | Tables_in_zhaojd |
  25. +------------------+
  26. | t_group |
  27. | t_product |
  28. | t_student |
  29. | v_product |
  30. | view_test3 |
  31. | view_test4 |
  32. | view_test5 |
  33. | view_test6 |
  34. | view_test8 |
  35. +------------------+
  36. 9 rows in set (0.00 sec)

5.修改视图: 
5.1 CREATE OR REPLACE VIEW语句修改视图: 
对于已经创建好的表,尤其是已经有大量数据的表,通过先删除,然后再按照新的表定义重新建表的方式来修改表,需要做很多额外的工作,例如数据的重载等。可是对于视图来说,由于是“虚表”,并没有存储数据,所以完全可以通过该方式来修改视图。

实现思路就是:先删除同名的视图,然后再根据新的需求创建新的视图即可。

  1. DROP VIEW view_name;
  2. CREATE VIEW view_name as 查询语句;

但是如果每次修改视图,都是先删除视图,然后再次创建一个同名的视图,则显得非常麻烦。于是MySQL提供了更方便的实现替换的创建视图的语法,完整语法为:

CREATE OR REPLACE VIEW view_name as 查询语句;

 5.2 ALTER语句修改视图:

语法为:

ALTER VIEW view_name as 查询语句;

6.利用视图操作基本表: 
再MySQL中可以通常视图检索基本表数据,这是视图最基本的应用,除此之后还可以通过视图修改基本表中的数据。 
6.1检索(查询)数据: 
    通过视图查询数据,与通过表进行查询完全相同,只不过通过视图查询表更安全,更简单实用。只需要把表名换成视图名即可。 
6.2利用视图操作基本表数据: 
    由于视图是“虚表”,所以对视图数据进行的更新操作,实际上是对其基本表数据进行的更新操作。在具体更新视图数据时,需要注意以下两点; 
    1. 对视图数据进行添加、删除直接影响基本表。 
    2. 视图来自于多个基本表时,不允许添加、删除数据。 
    视图中的添加数据操作、删除数据操作、更新数据操作的语法同表完全相同。只是将表名换成视图名即可。

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持phpstudy。

MySQL 视图的基础操作(五)

标签:存在   不同的   连接   选择   ***   概念   comm   client   方式   

人气教程排行