MySQL视图基本操作

MySQL视图基本操作

1 为什么使用视图

  （1）数据库某些SQL语句使用频率较高，次次都重复编写效率偏低，从而降低了数据库的使用效率；

  （2）数据库某些SQL语句较为复杂，编写困难且语句较多，并不能确保次次都能编写正确，并且错误或低性能的SQL语句往往可能给数据库带来灾难性的影响，通常是查询响应效率偏低，查询周期过长，从而降低了数据的实用性和效能；

  （3）数据库某些查询，需要固定到应用程序中，作为支持应用的功能性使用，需要一种较为方便且安全方式予以支持；

  基于以上因素，MySQL提供了视图特性，以提高SQL语句的复用性和数据表操作的安全性。

  什么是视图？

  （1）视图，可以视为一种封装了sql查询语句的一类存储对象。

  （2）视图，本质上是一种虚拟表，其内容与真实的表相似，包含一系列的字段列和记录行数据。

  （3）视图，并不在数据库以实际数据表的方式存在，而是通过定义查询来引用基本表数据，并在具体引用视图时动态生成。

  （4）视图，所引用的数据可以被视为封装起来的查询语句数据，并不对所存储的基本表数据产生影响，故而提高了数据库中数据的安全性。

  视图的特点：

  （1）视图的字段列，可以来自于不同的表，是表的抽象和在逻辑意义上建立的新关系。

  （2）视图是由基本表（实表）产生的表（虚表）；

  （3）视图的建立和删除不影响基本表；

  （4）对视图内容的更新（添加、删除和修改）直接影响基本表；

  （5）当视图来自于多个基本表时，不允许添加和删除数据。

2 视图基本操作

2.1 创建视图

2.1.1 创建视图基本语法

  视图是一种虚拟表，在物理上是不存在的，即数据库管理系统没有专门的位置为视图存储数据。

  视图的数据来源于查询语句，因此创建视图的语法为：

  create  view  view_name  as  SQL_Statement;

  注：视图名，不能和表名、也不能和其他视图名重名。

    视图名，推荐命名规范为“view_xxx”或者“v_xxx”。

2.1.2 创建各类视图

  （一）查询类视图创建

  （1）创建常量视图

    示例：create view view_testPI as  select  3.1415926;

  （2）封装使用聚合函数（sum、avg、min、max、count等）查询语句的视图

    示例：create  view  view_testCount as  select  avg(Salary) from employee;

  （3）封装实现排序功能（order by）查询语句的视图

    示例：create  view  view_testOrder as  

          select  WorkID,Name,Salary from employee order by Salary desc;

  （4）封装实现表内连接（inner join）查询语句的视图

    示例1：create  view  view_testInnerJoin1 as

          select Name,Salary,e.Department from employee e,department d where e.WorkID=d.WorkID;

    示例2：create  view  view_testInnerJoin2 as

          select Name,Salary,e.Department from employee e inner join department d  on e.WorkID=d.WorkID；

  （5）封装实现表外连接（left join和right join）查询语句的视图

    示例1：create  view  view_testLeftJoin as 

          select e.WorkID,e.Name,e.JobTitle,d.Department from employee e

            left join department d on e.Department=d.Department;

    示例2：create  view  view_testRightJoin as  

          select e.WorkID,e.Name,e.JobTitle,d.Department from employee e

            right join department d on e.Department=d.Department;

  （6）封装实现子查询相关查询语句的视图

    示例：create view  view_testInChild as

            select  WorkID,Name,Gender,Age,Degree,Department  from employee

            where WorkID in (select WorkID from department);

  （7）封装实现记录联合（union和union all）查询语句的视图

    示例：create view view_testUnion as 

            select  JobTitle,WorkID from employee union select JobTitle,WorkID from department;

2.2 查看视图

2.2.1 全局查看全部视图

  查看数据库内已经创建了哪些视图；

  （1）查看全部的基本表和视图表：

    1）方式1：show tables;

    2）方式2：show full tables;

    3）方式3【查看基本表】：select * from information_schema.tables where table_schema='databaseName';

       示例：select * from information_schema.tables where table_schema='company';

  （2）只查看视图表:

    1）方式1：show full tables where Table_type='VIEW';

    2）方式2：show tables where Tables_in_newtest like 'view_%';

    3）方式3：show full tables where Tables_in_newtest like 'view_%';

    注：方式2和方式3，均需要视图命名带有前缀，如上“view_”所示。

    4）方式4【查看视图表】：select * from information_schema.views where table_schema='databaseName';

       示例：select * from information_schema.views where table_schema='company';

2.2.2 查看具体视图详细信息

  （1）查看视图定义信息【字段及数据类型等】

    语法：describe|desc ViewName;

    示例1：describe view_testorder;

    示例2：desc view_testorder;

  （2）查看视图状态信息

    语法：show  table  status like  'ViewName';

  （3）查看视图定义信息

    语法：show create view ViewName;

    示例：show create view view_testorder;

  （4）查看视图元数据信息

    语法：select * from information_schema.views where table_schema='databaseName' and table_name='viewName';

    示例：select * from information_schema.views where table_schema='company' and table_name='view_testorder';

2.3 删除视图

  语法：drop  view view_Name;

  示例：drop view  view_testPI；

   注：删除表，对基本表没有影响。

2.4 修改视图

  修改视图，本质上是对视图语句进行修改，所修改的语句，可能对基本表产生影响，也可能不产生影响。

  若所修改的语句，仅是查询语句，则对基本表

  语法：

  示例：

2.5 利用视图操作基本表

  通过视图对基本表进行操作，主要包括查询、插入、删除、更新操作。

  基于此，视图对基本表数据的操作，可将其分为：查询类视图、插入类视图、删除类视图、更新类视图；

  仅查询类视图，数据可来源于多个基本数据表；

  而插入类、删除类、更新类视图，仅能够对单个基本表进行操作。

  涉及对基本表的数据进行插入、删除、更新操作，必须建立在其语句本身可单独正确执行为前提，否则视图运行出错。

  即通过视图对基本表进行的插入、删除、更新操作，同样受基本表本身的约束限制，否则无法正确运行。

2.5.1 视图查询操作

  查询视图，主要是对视图内部为查询类语句进行运行，并显示查询结果。

  （1）显示全部字段及记录信息

    语法：select * from viewName;

    示例：select * from view_testorder;

  （2）显示部分字段及记录信息

    语法：select field1,field2,...,fieldn from viewName;

    示例：select Name,Salary from view_testorder;

2.5.2 视图插入操作

  语法：insert into view_Name (field1,field2,...,fieldN) values (value1,value2,...,valueN);

  示例：insert into view_department (DepartmentID,Department,Manager,WorkID) values (108,'采购部','王俊',202205002);

  注：上述示例，在基本表中存在唯一性、非空约束，故插入新纪录时，必须插入该字段值。

2.5.3 视图删除操作

  语法：delete from view_Name  where field=value;

  示例：delete from view_department where DepartmentID=108;

  注：上述示例中，删除操作是一件很危险的操作，一般按唯一键进行删除，忌按非唯一键删除。

2.5.4 视图更新操作

  语法：update view_Name  set field2=value2 where field1=value1;

  示例：update view_department set Department='研究院' where DepartmentID=107;

  注：上述示例中，更新操作也是一件很危险的操作，一般按唯一键进行更新，忌按非唯一键更新，非唯一键容易造成多条记录都发生更新。

 

3 尾记

  视图给查询操作带来显著的方便，但对插入、删除、更新操作来说，往往是一场灾难，须谨慎为之。