(资料图)
视图可以帮我们使用表的一部分而不是所有的表,另一方面也可以针对不同的用户制定不同的查询视图。比如,针对一个公司的销售人员,我们只想给他看部分数据,而某些特殊的数据,比如采购的价格,则不会提供给他。再比如,人员薪酬是个敏感的字段,那么只给某个级别以上的人员开放,其他人的查询视图中则不提供这个字段。
①:视图是一种虚拟表,本身是不具有数据的,占用很少的内存空间,它是SQL中的一个重要概念。②:视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。③:视图的创建和删除只影响视图本身,不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时,数据表中的数据会相应地发生变化。④:向视图提供数据内容的语句为SELECT语句, 可以将视图理解为存储起来的SELECT语句在数据库中,视图不会保存数据,数据真正保存在数据表 中。当对视图中的数据进行增加、删除和修改操作时,数据表中的数据会相应地发生变化。⑤:视图是向用户提供基表数据的另一种表现形式。通常情况下,小型项目的数据库可以不使用视图,但是在大型项目中,以及数据表比较复杂的情况 下,视图的价值就凸显出来了,它可以帮助我们把经常查询的结果集放到虚拟表中,提升使用效率。理解和使用起来都非常方便。
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,"江苏无锡");视图创建基表数据
#### 创建视图-- 创建单表视图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算法,其他情况下不用指定,默认即可。
.[page]
Copyright 2015-2022 财报分析网版权所有 备案号:京ICP备12018864号-25 联系邮箱:29 13 23 6 @qq.com