最新要闻

广告

手机

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

 警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

家电

  1. 首页
  2. 移动通信
  3. 内容

MySQL视图

来源：博客园


【资料图】

一：视图概述

1：为什么使用视图

视图可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段。

2：什么是视图

①：视图是一种虚拟表，本身是不具有数据的，占用很少的内存空间，它是SQL中的一个重要概念。②：视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。③：视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化。④：向视图提供数据内容的语句为SELECT语句, 可以将视图理解为存储起来的SELECT语句在数据库中，视图不会保存数据，数据真正保存在数据表　　中。当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化。⑤：视图是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况　　下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便。

二：视图操作

1：基本语法

CREATE [OR REPLACE]    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]    VIEW 视图名称 [(字段列表)]    AS 查询语句    [WITH [CASCADED|LOCAL] CHECK OPTION]
-- 创建数据库及使用表CREATE DATABASE IF NOT EXISTS demo_view_school CHARACTER SET utf8 COLLATE utf8_general_ci;USE demo_view_school;-- 创建数据表CREATE TABLE `student`(    `sid`      int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT "学生ID",    `sname`    varchar(5) NOT NULL COMMENT "学生姓名",    `ssex`     enum("男","女") DEFAULT "男" COMMENT "性别",    `sage`     tinyint(11) unsigned NOT NULL COMMENT "年龄",    `saddress` mediumtext COMMENT "住址",    `tid`      int(11) DEFAULT NULL COMMENT "引用老师ID") ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8;CREATE TABLE `teacher`(    `tid`      int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT "老师ID",    `tname`    varchar(5) NOT NULL COMMENT "老师姓名",    `tsex`     enum("男","女") DEFAULT "男" COMMENT "性别",    `tage`     tinyint(3) unsigned DEFAULT NULL COMMENT "年龄",    `taddress` varchar(10) DEFAULT NULL COMMENT "住址") ENGINE=InnoDB DEFAULT CHARSET=utf8;-- 填充数据INSERT INTO `demo_view_school`.`student` (`sid`, `sname`, `ssex`, `sage`, `saddress`, `tid`) VALUES(1, "王生安", "男", 21, "安徽合肥", 1),(2, "李鑫灏", "女", 21, "安徽合肥", 1),(3, "薛佛世", "男", 21, "安徽蚌埠", 2),(4, "蔡壮保", "男", 21, "安徽安庆", 2),(5, "钱勤堃", "女", 23, "安徽合肥", 1),(6, "潘恩依", "女", 24, "安徽合肥", 1),(7, "陈国柏", "女", 24, "安徽六安", 2),(8, "魏皑虎", "女", 24, "安徽六安", 2);INSERT INTO `demo_view_school`.`teacher` (`tid`, `tname`, `tsex`, `tage`, `taddress`) VALUES(1, "张老师", "女", 28,"江苏南京"),(2, "李老师", "男", 28,"江苏无锡");
视图创建基表数据

2：视图的增删改查

#### 创建视图-- 创建单表视图CREATE VIEW t_view1(sid,sname,ssex,saddress) AS SELECT sid,sname,ssex,saddress FROM student;-- 创建多表视图CREATE VIEW t_view2(sid,sname,ssex,saddress,tname) AS SELECT s.sid,s.sname,s.ssex,s.saddress,t.tname FROM student s INNER JOIN teacher t USING(tid);-- 注：创建完视图后，基表上的主键外键唯一键等等都会丢失-- 注：创建完视图会在MySQL的data数据存储目录里存在t_view1.frm，t_view2.frm的文件#### 查看视图-- 查看数据库的表对象、视图对象SHOW TABLES;    -- 示例：        +----------------------------+        | Tables_in_demo_view_school |        +----------------------------+        | student                    |        | t_view1                    |        | t_view2                    |        | teacher                    |        +----------------------------+-- 查询表结构DESC t_view2;    -- 示例：        +----------+-------------------+------+-----+---------+-------+        | Field    | Type              | Null | Key | Default | Extra |        +----------+-------------------+------+-----+---------+-------+        | sid      | int(11)           | NO   |     | 0       |       |        | sname    | varchar(5)        | NO   |     | NULL    |       |        | ssex     | enum("男","女")   | YES  |     | 男      |       |        | saddress | mediumtext        | YES  |     | NULL    |       |        | tname    | varchar(5)        | NO   |     | NULL    |       |        +----------+-------------------+------+-----+---------+-------+-- 查询视图状态（因为视图是基于基表创建的，所以视图就像一个被封装的SELECT）SHOW TABLE STATUS LIKE "t_view2"\G    -- 示例：（因为是视图所以就Comment为VIEW）        *************************** 1. row ***************************                   Name: t_view2                 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#### 使用视图-- 视图主要就是为了查询，所以将视图当作表一样查询即可-- 普通查询SELECT * FROM t_view2;    -- 示例：        +-----+-----------+------+--------------+-----------+        | sid | sname     | ssex | saddress     | tname     |        +-----+-----------+------+--------------+-----------+        |   1 | 王生安     | 男   | 安徽合肥      | 张老师      |        |   2 | 李鑫灏     | 女   | 安徽合肥      | 张老师      |        |   3 | 薛佛世     | 男   | 安徽蚌埠      | 李老师      |        |   4 | 蔡壮保     | 男   | 安徽安庆      | 李老师      |        |   5 | 钱勤堃     | 女   | 安徽合肥      | 张老师      |        |   6 | 潘恩依     | 女   | 安徽合肥      | 张老师      |        |   7 | 陈国柏     | 女   | 安徽六安      | 李老师      |        |   8 | 魏皑虎     | 女   | 安徽六安      | 李老师      |        +-----+-----------+------+--------------+-----------+#### 修改视图-- 视图本身不可修改，但是视图的来源是可以修改的-- 修改视图其实就是修改视图本身的来源（SELECT语句）-- 比如修改t_view1视图ALTER VIEW t_view1(sid,sname) AS SELECT sid,sname FROM student;    -- 示例：            select * from t_view1;        +-----+-----------+        | sid | sname     |        +-----+-----------+        |   1 | 王生安     |        |   2 | 李鑫灏     |-- 使用 OR REPLACE 修改视图（和ALTER VIEW一样）CREATE OR REPLACE VIEW t_view3(sid,sname) AS SELECT sid,sname FROM student;#### 删除视图-- 单个删除、批量删除DROP VIEW 视图名称；DROP VIEW 视图名称,视图名称；注：为什么删除视图不用DROP TABLE；因为TABLE表示存数据的，而VIEW则是没有具体数据的，所以删视图它只是删结构(.frm的文件)，删TABLE则是删结构和数据(.frm和.ibd)

三：视图数据操作

视图数据新增：①：多表视图不能新增数据    ②：可以向单表视图插入数据，但是视图中包含的字段必须和基表中所有不能为空(或没有默认值)的字段对应视图数据删除：①：多表视图不能删除数据    ②：单表视图可以删除数据视图数据更新：理论上无论是单表视图还是多表视图都可以更新数据    更新限制：WITH CHECK OPTION，如果对视图在新增的时候，限定了某个字段有限制，那么    对视图进行数据更新操作时，系统会进行验证，要保证更新之后，数据依然可以按实体查询出来，否则不让更新。    -- 示例    -- 创建视图    -- 单表视图（加上sage约束,表示视图来源数据都是21岁）    -- WITH CHECK OPTION修改数据时，我们不能修改sage字段导致不符合条件    CREATE VIEW t_view3(sid,sname,sage,ssex,saddress)     AS SELECT sid,sname,sage,ssex,saddress FROM student WHERE sage = 21 WITH CHECK OPTION;    -- 更新示例    UPDATE t_view3 SET sage = 66 WHERE sid = 1;    ERROR 1369 (HY000): CHECK OPTION failed "demo_view_school.t_view3"关于视图更新总结一下：    要使视图可更新，视图中的行和底层基本表中的行之间必须存在一对一的关系。    另外当视图定义出现如下情况时，视图不支持更新操作：        ①：在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；        ②：视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；        ③：在定义视图的SELECT语句中使用了JOIN联合查询，视图将不支持INSERT和DELETE操作；        ④：在定义视图的SELECT语句后的字段列表中使用了数学表达式或子查询，视图将不支持INSERT，            也不支持UPDATE使用了数学表达式、子查询的字段值；        ⑤：在定义视图的SELECT语句后的字段列表中使用DISTINCT、聚合函数、GROUP BY 、HAVING、UNION等，            视图将不支持INSERT、UPDATE、DELETE；        ⑥：在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持INSERT、UPDATE、DELETE；        ⑦：视图定义基于一个不可更新视图；        ⑧：常量视图。

四：视图算法

执行基础数据：ALTER TABLE student ADD ssalary DECIMAL(7,2);UPDATE student set ssalary = ceil(rand()*3000+8000);基表数据信息：+-----+-----------+------+------+--------------+------+----------+| sid | sname     | ssex | sage | saddress     | tid  | ssalary  |+-----+-----------+------+------+--------------+------+----------+|   1 | 王生安     | 男   |   21 | 安徽合肥       |    1 |  9225.00 ||   2 | 李鑫灏     | 女   |   21 | 安徽合肥       |    1 |  8542.00 ||   3 | 薛佛世     | 男   |   21 | 安徽蚌埠       |    2 | 10035.00 ||   4 | 蔡壮保     | 男   |   21 | 安徽安庆       |    2 | 10548.00 ||   5 | 钱勤堃     | 女   |   23 | 安徽合肥       |    1 |  8633.00 ||   6 | 潘恩依     | 女   |   24 | 安徽合肥       |    1 |  9522.00 ||   7 | 陈国柏     | 女   |   24 | 安徽六安       |    2 | 10709.00 ||   8 | 魏皑虎     | 女   |   24 | 安徽六安       |    2 | 10977.00 |+-----+-----------+------+------+--------------+------+----------+①：创建视图，使工资降序排列DESC        CREATE VIEW t_view5 AS SELECT * FROM student ORDER BY ssalary DESC; ②：查询学生表并按照年龄分组        SELECT * FROM t_view5 GROUP BY sage;        -- 查询结果            +-----+-----------+------+------+--------------+------+---------+            | sid | sname     | ssex | sage | saddress     | tid  | ssalary |            +-----+-----------+------+------+--------------+------+---------+            |   1 | 王生安     | 男   |   21 | 安徽合肥       |    1 | 9225.00 |            |   5 | 钱勤堃     | 女   |   23 | 安徽合肥       |    1 | 8633.00 |            |   6 | 潘恩依     | 女   |   24 | 安徽合肥       |    1 | 9522.00 |            +-----+-----------+------+------+--------------+------+---------+        -- 说明：这个就是没有按照顺序执行，应该先DESC排序再分组才是正确的通过上面的查询会发现查询的是错误的，因为DESC排列后已经是从高到底排列了，这时候按照年龄进行分组，分组完应该每个年龄中都是最高的工资视图算法：系统对视图以及外部查询视图的select语句的一种解析方式。视图：可以理解为子查询，2个select，第一个select：得到视图视图算法分成3种：Undefined：未定义(默认)这不是一种算法，是一种推卸责任的算法告诉系统，视图没有定义算法，系统你自己看着办   Temptable：临时表算法：先执行视图的select语句，然后在执行外部查询Select语句。    Merge：合并算法，系统在执行select语句之前，会对视图的select和外部查询视图的select语句进行合并，然后执行        -- Merge效率高，但是慢算法指定：    ①：创建视图（使用算法），使工资降序排列DESC        CREATE ALGORITHM = TEMPTABLEVIEW t_view5 AS SELECT * FROM student ORDER BY ssalary DESC;   ②：查询学生表并按照年龄分组        SELECT * FROM t_view5 GROUP BY sage;        +-----+-----------+------+------+--------------+------+----------+        | sid | sname     | ssex | sage | saddress     | tid  | ssalary  |        +-----+-----------+------+------+--------------+------+----------+        |   4 | 蔡壮保     | 男   |   21 | 安徽安庆       |    2 | 10548.00 |        |   5 | 钱勤堃     | 女   |   23 | 安徽合肥       |    1 |  8633.00 |        |   8 | 魏皑虎     | 女   |   24 | 安徽六安       |    2 | 10977.00 |        +-----+-----------+------+------+--------------+------+----------+算法选择：    如果视图的select语句中包含一个查询子句（order by,limit）,而且很可能该关键字执行顺序比外部查询语句关键字靠后    一定要使用使用temptable算法，其他情况下不用指定，默认即可。

.

关键词：