MySQL 视图的基础操作（五）_Mysql

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

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

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

示例：

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

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

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

2.2.1 封装实现查询常量语句的视图（常量视图）：

示例：

mysql> create view view_test1 as select 3.1415926;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from view_test1;
+-----------+
| 3.1415926 |
+-----------+
| 3.1415926 |
+-----------+
1 row in set (0.00 sec)

2.2.2 封装使用聚合函数（SUM、MIN、MAX、COUNT等）查询语句的视图：

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

CREATE TABLE t_group(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);
CREATE TABLE t_student(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20),
  sex CHAR(1),
  group_id INT,
  FOREIGN KEY (group_id) REFERENCES t_group (id)
);
//t_group表中插入数据
INSERT INTO t_group (NAME) VALUES('group_1');
INSERT INTO t_group (NAME) VALUES('group_2');
INSERT INTO t_group (NAME) VALUES('group_3');
INSERT INTO t_group (NAME) VALUES('group_4');
INSERT INTO t_group (NAME) VALUES('group_5');
//t_student表中插入数据
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_1','M',1);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_2','M',1);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_3','M',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_4','W',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_5','W',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_6','W',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_7','M',3);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_8','W',4);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_9','W',4);
================================================================
mysql> create view view_test2 as select count(name) from t_student;
Query OK, 0 rows affected (0.71 sec)
mysql> select * from view_test2;
+-------------+
| count(name) |
+-------------+
|      9 |
+-------------+
1 row in set (0.01 sec)

2.2.3 封装了实现排序功能（ORDER BY）查询语句的视图：

示例：

mysql> create view view_test3 as select name from t_student order by id desc;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from view_test3;
+-------+
| name |
+-------+
| zjd_9 |
| zjd_8 |
| zjd_7 |
| zjd_6 |
| zjd_5 |
| zjd_4 |
| zjd_3 |
| zjd_2 |
| zjd_1 |
+-------+
9 rows in set (0.00 sec)

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

示例：（第二组学生的姓名）

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;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from view_test5;
+-------+
| name |
+-------+
| zjd_3 |
| zjd_4 |
| zjd_5 |
| zjd_6 |
+-------+
4 rows in set (0.00 sec)

2.2.5 封装了实现表外连接（LEFT JOIN和RIGHT JOIN）查询语句的视图：

示例：（第二组学生姓名）

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;
Query OK, 0 rows affected (0.09 sec)
mysql> select * from view_test6;
+-------+
| name |
+-------+
| zjd_3 |
| zjd_4 |
| zjd_5 |
| zjd_6 |
+-------+
4 rows in set (0.01 sec)

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

示例：

mysql> create view view_test7 as select s.name from t_student s where s.id in(select id from t_group);
Query OK, 0 rows affected (0.08 sec)
mysql> select * from view_test7;
+-------+
| name |
+-------+
| zjd_1 |
| zjd_2 |
| zjd_3 |
| zjd_4 |
| zjd_5 |
+-------+
5 rows in set (0.00 sec)

2.2.7 封装了实现记录联合（UNION和UNION ALL）查询语句的视图：

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

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

示例：

mysql> show tables;
+------------------+
| Tables_in_zhaojd |
+------------------+
| t_group     |
| t_product    |
| t_student    |
| v_product    |
| view_test1    |
| view_test2    |
| view_test3    |
| view_test4    |
| view_test5    |
| view_test6    |
| view_test8    |
+------------------+
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
示例：

mysql> show table status from zhaojd \G
*************************** 1. row ***************************
      Name: t_group
     Engine: InnoDB
    Version: 10
   Row_format: Compact
      Rows: 5
 Avg_row_length: 3276
  Data_length: 16384
Max_data_length: 0
  Index_length: 0
   Data_free: 7340032
 Auto_increment: 6
  Create_time: 2016-08-19 16:26:06
  Update_time: NULL
   Check_time: NULL
   Collation: utf8_general_ci
    Checksum: NULL
 Create_options:
    Comment:
=============================================================
      Name: view_test8
     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

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

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

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

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

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

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

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

示例：

mysql> use information_schema;
Database changed
mysql> select * from views where table_name='view_test8' \G
*************************** 1. row ***************************
    TABLE_CATALOG: def
    TABLE_SCHEMA: zhaojd
     TABLE_NAME: view_test8
   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`
    CHECK_OPTION: NONE
    IS_UPDATABLE: NO
       DEFINER: root@localhost
    SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.01 sec)

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

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

mysql> use zhaojd;
Database changed
mysql> show tables;
+------------------+
| Tables_in_zhaojd |
+------------------+
| t_group     |
| t_product    |
| t_student    |
| v_product    |
| view_test1    |
| view_test2    |
| view_test3    |
| view_test4    |
| view_test5    |
| view_test6    |
| view_test8    |
+------------------+
11 rows in set (0.00 sec)
mysql> drop view view_test1, view_test2;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_zhaojd |
+------------------+
| t_group     |
| t_product    |
| t_student    |
| v_product    |
| view_test3    |
| view_test4    |
| view_test5    |
| view_test6    |
| view_test8    |
+------------------+
9 rows in set (0.00 sec)

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

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

DROP VIEW view_name;
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. 视图来自于多个基本表时，不允许添加、删除数据。
    视图中的添加数据操作、删除数据操作、更新数据操作的语法同表完全相同。只是将表名换成视图名即可。

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

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索mysql
视图
mysql视图操作、mysql基础操作、mysql 视图、mysql 创建视图、mysql 视图 性能，以便于您获取更多的相关知识。

时间： 2024-09-30 01:10:10

MySQL 视图的基础操作（五）_Mysql的相关文章

mysql表的基础操作汇总（三）_Mysql

针对mysql表进行的基础操作,具体内容如下 1.创建表: 创建表的语法形式: CREATE TABLE table_name( 属性名 数据类型, 属性名 数据类型, ... 属性名 数据类型 ) 示例: CREATE TABLE t_dept( deptno int, dname varchar(20), loc varchar(20) 备注:在创建表之前,通常你需要使用 USE 库名:这样一条语句来选择一个库,在所选择的库中创建表.对于表名标识符,不能是mysql的关键字,如 CREATE

mysql 索引的基础操作汇总（四）_Mysql

1.为什么使用索引:      数据库对象中的索引其实和书的目录类似,主要是为了提高从表中检索数据的速度.由于数据存储在数据库表中,所以索引是创建在数据库表对象上,由表中的一个字段或者多个字段生成的键组成,这些键存储在数据结构(B-树或者哈希表)中,通过MySQL可以快速有效查找与键相关联的字段.根据索引的存储类型,可以将索引分为B型树索引(BTREE)和哈希索引(HASH).注意:InnoDB和MyISAM存储引擎支持BTREE类型索引,MEMORY存储引擎支持HASH类型的索引,默认为前者索

MySQL 触发器的基础操作（六）_Mysql

1.为什么使用触发器:      在MySQL数据库中,数据库对象表是存储和操作数据的逻辑结构,而数据库对象触发器则用来实现由一些表事件触发的某个操作.在数据库系统中,当执行表事件时,则会激活触发器,从而执行器包含的操作.触发器的操作包含创建.查看.删除.      触发器是数据库对象之一,该对象与编程语言 中的函数非常类似,都需要声明.执行等.但是触发器的执行不是由程序调用,也不是由手工启动,而是由事件来触发.激活从而实现执行.例如:在学生表中有学生名字.学生总数字段,每当添加一条学生记录时,

利用tcpdump对mysql进行抓包操作技巧_Mysql

利用tcpdump对mysql进行抓包操作如下所示: 命令如下: 复制代码 代码如下: tcpdump -s 0 -l -w - dst 192.168.244.10 and port 3306 -i eno16777736 |strings 其中-i指定监听的网络接口,在RHEL 7下,网络接口名不再是之前的eth0,而是 eno16777736. 在RHEL 5&6下,可直接不带-i参数,因为它默认是eth0.在RHEL 7下,如果不用-i参数指定网络接口,则会报如下错误: 复制代码 代码如

Linux下将数据库从MySQL迁移到MariaDB的基础操作教程_Mysql

自从2012年来,维基百科已经开始从MySQL迁移到MariaDB的过程,是维基媒体数据库架构变革的一个重大里程碑,将英文和德文版的维基百科数据库Wikidata移植到了MariaDB 5.5版本上. 在过去几年中,维基百科一直使用 Facebook 的 MySQL 5.1 衍生版本 作为我们的产品数据库,构建版本号是r3753.我们很高兴该产品的性能表现,Facebook 有着全世界最棒的数据库工程师,他们为 MySQL 生态系统带去了很多改进. 现在 MariaDB 的优化器的增强.Perc

mysql常用命令行操作语句_Mysql

第一招.mysql服务的启动和停止 net stop mysql net start mysql 第二招.登陆mysql 语法如下: mysql -u用户名 -p用户密码 键入命令mysql -uroot -p, 回车后提示你输入密码,输入12345,然后回车即可进入到mysql中了,mysql的提示符是: mysql> 注意,如果是连接到另外的机器上,则需要加入一个参数-h机器IP 第三招.增加新用户 格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by

Mysql主从复制(master-slave)实际操作案例_Mysql

在这一章节里, 我们来了解下如何在 Mysql 中进行用户授权及主从复制   这里先来了解下 Mysql 主从复制的优点:   1. 如果主服务器出现问题, 可以快速切换到从服务器提供的服务 2. 可以在从服务器上执行查询操作, 降低主服务器的访问压力 3. 可以在从服务器上执行备份, 以避免备份期间影响主服务器的服务 注意一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从服务器查询, 实时性要求高的数据仍然需要从主数据库获得   在这里我们首先得完成用户授权, 目的是为了给从服务器有

很全面的Mysql数据库、数据库表、数据基础操作笔记（含代码）_Mysql

 Mysql数据库.数据库表.数据基础操作笔记分享给大家,供大家参考,具体内容如下 一.数据库操作 1.创建数据库 Create database db name[数据库选项]; tip:语句要求使用语句结束符";"来结束服务. 标识符(数据库名)命名规则: 大小写取决于当前操作系统. 见名知意,推荐使用下划线法. 标识符的字符: 使用任意字符,数字,甚至中文.但是一些特殊的组合,例如纯数字组合,特殊符号等需使用标识符限定符来包裹. 限定符:反引号`` 中文可以:但要求客户端编码 在m

GIS基础软件及操作(五)

原文 GIS基础软件及操作(五) 练习五.空间分析的基本操作 空间分析的基本操作 空间分析模块 空间分析是基于地理对象的位置和形态的空间数据的分析技术,其目的在于提取和传输空间信息.空间分析是地理信息系统的主要特征.空间分析能力(特别是对空间隐含信息的提取和传输能力)是地理信息系统区别与一般信息系统的主要方面,也是评价一个地理信息系统成功与否的一个主要指标. 本章的大部分练习都会用到空间分析扩展模块,要使用"空间分析模块"首先在ArcMap中执行菜单命令[自定义]>[扩展模块],