博客

MySQL–视图

  • Post author:
  • Post category:mysql


文章目录



MySQL–视图



1. 视图的概念

视图（View）是一种虚拟存在的表。其内容与真实的表相似，包含一系列带有名称的列和行数据。但是视图并不在数据库中以存储的数据的形式存在。行和列的数据来自定义视图时查询所引用的基本表，并且在具体引用视图时动态生成。

image-20201102175457673



2. 视图的优、缺点



2.1 视图的优点：

  • 简单：使用视图的用户完全不需要关心视图中的数据是通过什么查询得到的，视图中的数据对用户来说已经是过滤好的符合条件的结果集；

  • 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行或某个列，但是通过视图就可以简答地实现；

  • 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，原表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。



2.2 视图的缺点：

  • 性能差：对视图查询最终转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，也要把它变成一个复杂的结合体，需要花费一定的时间。

  • 修改限制：当用户试图修改试图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的试图来说，这是很方便的，但是，对于比较复杂的试图，可能是不可修改的。



3. 视图的操作



3.1 创建视图

视图可以以一张表为基础创建，也可以以多张表为基础创建。创建视图使用CREATE VIEW语句，其基本语法格式如下： 

CREATE [OR REPLACE] VIEW view_name [(column_list)] 

AS 查询语句;

[WITH CHECK OPTION]

创建视图时应注意以下几点：

  • 定义中引用的任何表或视图都必须存在。如果在创建视图后，删除定义时引用的表或视图，则使用视图时将导致错误。

  • SELECT语句中最大列名长度为

    64

    个字符。


  • WITH CHECK OPTION：可选，用于视图数据操作时的检查条件。


    若省略此子句，则不进行检查。

在MySQL中，可以在单个数据表上创建视图，这也是最简单的一种创建方法。


[例3.1]在student表上创建一个视图v_stu1,输出学号，姓名
 

CREATE VIEW v_stu1 AS SELECT

studentid,
studentname 

FROM
	student

默认情况下，视图的字段名与基表的字段名相同。但是，为了增加数据安全性，也可以为视图字段指定不同的名称。


[例3.2]在student表上创建一个视图v_Cs010901,输出Cs010901班同学的学号，姓名和性别,班级，并将视图字段重命名为sid,sname,ssex,claid
 


CREATE VIEW v_Cs010901 ( sid, sname, seex, claid ) AS SELECT
studentid,
studentname,
sex,
classid 
FROM
	student 
WHERE
	classid = 'Cs010901';


[课堂练习]创建一个视图v_sc1，输出学号，学生名，性别，班级号，班级名，并将视图字段重命名为sid,sname,ssex,claid,claname
 

#注意此处的select语句中classid是从student表来的，在本例中，我们的classid无论是从哪个表来都不会影响结果
CREATE VIEW v_sc1 ( sid, sname, ssex, claid, claname ) AS SELECT
studentid,
studentname,
sex,
s.classid,
classname
FROM
	student s,
	class c
WHERE
	s.classid = c.classid;

#我用一个v_sc2表示classid从course表来
CREATE VIEW v_sc2 ( sid, sname, ssex, claid, claname ) AS SELECT
studentid,
studentname,
sex,
c.classid,
classname 
FROM
	student s,
	class c
WHERE
	s.classid = c.classid



3.2 查看视图

①. 使用DESC语句可以查看视图的结构，其基本语法格式如下： 

DESC 视图名;

②. 使用SHOW CREATE VIEW语句可以查看视图定义语句，其基本语法格式如下： 

SHOW CREATE VIEW 视图名;


[练习]查看v_sc1视图的定义语句
 

DESC v_sc1;
DESC v_sc2;
show create view v_sc1;
show create view v_sc2;

image-20201102191354124

image-20201102191414812

image-20201102191537277

因为这样查询不是能很好的看到数据。

我们可以打开命令行界面：

image-20201102191836360

可以看到上面的两个classid分别来自不同的两个表



3.3 修改视图

当基本表的某些字段发生改变时，可以通过修改视图来保持与基本表的一致性。

MySQL提供CREATE OR REPLACE VIEW和ALTER语句来修改视图。

使用CREATE OR REPLACE VIEW语句修改视图的语法形式如下： 

CREATE OR REPLACE VIEW view_name [(column_list)] AS SELECT_statement;

mysql> ALTER VIEW view_students_info
    -> AS SELECT id,name,age
    -> FROM tb_students_info;
Query OK, 0 rows affected (0.07 sec)
mysql> DESC view_students_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | 0       |       |
| name  | varchar(45) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

用ALTER VIEW语句修改视图的语法形式如下： 

ALTER VIEW view_name [(column_list)] AS SELECT_statement;


可以看到，修改视图的语句和创建视图的语句完全一样。当视图存在时，修改语句对视图进行修改；当视图不存在时，创建视图。



3.4 删除视图

使用DROP VIEW语句可删除一个或多个视图。其基本语法格式如下： 

DROP VIEW [IF EXISTS] 视图1,……视图n;

[例]删除v_stu1视图 

DROP VIEW v_stu1;



3.5 操作视图中数据

  • 插入：insert
  • 修改：update
  • 删除：delete
  • 查看：select


当视图中的数据被修改，基表中的数据会同时被修改。同样，当基表中的数据被修改，视图中的数据也会被修改。


[例]通过v_stu1和v_Cs010901往student表中插入数据,都能够成功吗
 

INSERT INTO v_stu1 VALUES('238','胡涛')
-- > 1423 - Field of view 'view.v_stu1' underlying table doesn't have a default value
#因为通过视图插入数据时，该视图是通过student表得到的，所以在student表中插入数据，会发现student表中的性别没有默认值，我再用show CREATE TABLE student查看一下性别的的约束如下：

show CREATE TABLE student

CREATE TABLE `student` (
  `StudentID` char(12) NOT NULL,
  `StudentName` char(8) NOT NULL,
  `Sex` char(2) NOT NULL,
  `birth1` date DEFAULT NULL,
  `HomeAddr` varchar(80) DEFAULT NULL,
  `EntranceTime` datetime DEFAULT '2015-09-01 00:00:00',
  `ClassID` char(8) DEFAULT NULL,
  PRIMARY KEY (`StudentID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

可以看到性别约束不能为空，所以插不进去

下面这种是可以插成功的
#通过v_Cs010901往student表中插入数据
INSERT INTO v_Cs010901 VALUES('238','胡涛','男','软件B1802')

image-20201102200545165
image-20201102200704907


[课堂练习]通过v_sc1往student表中插入数据（“s001”,”张飞”,”男”,”Cs010901”)，能够成功吗？
 

-- ①使用v_sc1能成功，去使用v_sc2插入时会出错，安全性
INSERT INTO v_sc1(sid,sname,ssex,claid) VALUES('s001','张飞','男','Cs010901');
-- INSERT INTO v_sc1(sid,sname,ssex,claid) VALUES('s003','按时','女','Cs015501');

image-20201102200907836

image-20201102200932084


[例]通过v_sc2修改student表的数据，能够成功吗？
 

-- ②为了避免主键冲突，我特意改了sid，但是插不进去，因为上面第一种方法插入，那个classid是通过student表的。这个不是，这是视图的安全性，
INSERT INTO v_sc2(sid,sname,ssex,claid) VALUES('s002','张飞','男','Cs010901');

image-20201102202123258


[课堂练习]通过v_sc1视图

1.修改姜明凡所在的班级为“10电子商务2班”，可以成功吗？ 

UPDATE v_sc1
SET claname = "10电子商务2班"
WHERE sname = '姜明凡'
-- 能成功，但是姜明凡所在的班级名字都变成了10电子商务2班，所以是不成功的

2.修改姜明凡性别为女生，可以成功吗？ 

-- 能成功
UPDATE v_sc1
SET ssex = '女'
WHERE sname = '姜明凡'

3.修改姜明凡的班级号为“Cs021002”，可以成功吗？ 

-- 分情况，来自class表的改不了，因为classid是主键
UPDATE v_sc1
SET claid = 'Cs021002'
WHERE sname = '姜明凡'
-- 来自student表的可以改，因为classid在student表中不是主键
UPDATE v_sc2
SET claid = 'Cs021002'
WHERE sname = '姜明凡'


[思考]建立一个视图s_num，通过计算student表输出每个班级的人数。


能否通过s_num修改Cs010901班同学人数为20人呢？为什么？
 

我个人认为应该是不能修改成功的，因为班级人数是不能通过改数字更新的，我们创建计算这个人数的视图时都是通过学生的人数来计算的，后面也提到了聚合函数是视图的不可更新条件。

试一下：
CREATE VIEW s_num 
AS SELECT classid,count(*) num
FROM student
GROUP BY classid

image-20201102205507714 

UPDATE s_num
SET num = 20
WHERE classid = 'Cs010901'//不能修改
> 1288 - The target table s_num of the UPDATE is not updatable

image-20201102210002412


并不是所有视图都能更新，只有满足可更新条件的视图才能更新

以下是不可更新条件

1、聚合函数

2、DISTINCT关键字

3、GROUP BY 子句

4、HAVING 子句

5、union 运算符

6、位于选择列表中的子查询

7、from子句中包含多个表

8、select中引用了不可更新视图

9、where子句中的子查询，引用了from子句中的表

10、ALGORITHM指定选择TEMPTABLE，因为使用临时表视图不可更新


[例7.3]如果你是Cs010901班班长，拥有v_Cs010901视图的所有权限，试一下你能不能通过这个视图误插入一个Cs010902的同学呢？如果能，该如何避免这种情况的发生？
 

-- 能插入，因为插入时根据sid插入，只要满足sid不重复就可以插入
INSERT INTO v_Cs010901(sid,sname,ssex,claid) VALUES('s012','张飞','男','Cs010902');
-- 误插入了，那就删除
DELETE FROM v_Cs010901
WHERE sid = 's012'
-- 删除不了，因为找不到Cs010902。删除的时候，
-- 首先要找到这位同学，找这位同学，我们要找到他的班级号，
-- 0Cs010902找不到，所以删不了。

-- 解决方法：
-- 删除视图：
DROP VIEW v_Cs010901;
-- 检查视图是否已被删除
SELECT * FROM v_Cs010901;

-- 重新创建视图并加上WITH CHECK OPTION【用于视图数据操作时的检查条件。若省略此子句，则不进行检查】

CREATE VIEW v_Cs010901(sid,sname,ssex,claid)
AS 
SELECT studentid,studentname,sex,ClassID
FROM student
WHERE classid = 'Cs010901'
WITH CHECK OPTION

-- 再来插入,这样就插不进去了：
INSERT INTO v_Cs010901(sid,sname,ssex,claid) VALUES('s013','张飞','男','Cs010902');
-- 会出现提示> 1369 - CHECK OPTION failed 'xkgl.v_cs010901' 说明此时我们已经将视图安全性改好了



4. 视图和表的对比

关键字 是否占用物理空间 使用
视图 view 占用较小，只保存sql逻辑 一般用于查询
表 table 保存实际的数据 增删改查

​



5. 作业：


1. 创建视图V_course 包括所有学分在3学分以上的课程（课程名，学分）
 

CREATE VIEW V_course
AS
SELECT coursename,credit
FROM course
WHERE credit>3

image-20201102210447048

image-20201102210516328


2. 创建一个视图MAX_MIN，用于显示每个学生成绩的平均分,最高分,最低分（学号，姓名，平均分,最高分,最低分）
 

CREATE VIEW MAX_MIN

AS SELECT 

s.studentid,studentname,avg(grade),max(grade),min(grade)

FROM student s,grade g

WHERE s.studentid = g.studentid

GROUP BY studentid

image-20201102211043115

image-20201102211058757


3. 查看一个视图的定义语句

image-20201102211427231


4. 创建一个视图teacher_in,查找在一宿舍一栋的老师的教师号，姓名，性别，职位，住址，并通过该视图添加教师数据（dep03004，王一，NULL，副教授，一宿舍一栋308），能否成功，能成功截图，不能成功则说明理由并按照自己方式修改插入语句，使其能插入
 

CREATE VIEW teacher_in
AS
SELECT teacherid,teachername,sex,profession,homeaddr
FROM teacher
WHERE homeaddr like '一宿舍1栋%'

image-20201102213027977 

INSERT INTO teacher_in(teacherid,teachername,sex,profession,homeaddr)
VALUES ('dep03004','王一',NULL,'副教授','一宿舍一栋308')

> 1048 - Column 'Sex' cannot be null
性别不能为空，那就加个性别呗

image-20201102213717072 

INSERT INTO teacher_in(teacherid,teachername,sex,profession,homeaddr)
VALUES ('dep03004','王一','男','副教授','一宿舍一栋308')

image-20201102214246798

image-20201102214401892


5. 创建一个视图teacher_in1，查找在一宿舍一栋的并且是副教授的老师的教师号，姓名，性别，职位，住址，并且每当通过视图进行修改操作时，都会进行条件检查，确保修改的信息符合视图查询要求，创建好后，通过teacher_in1插入（dep03005，王一，女，教授，二宿舍一栋308）能够成功，若能则截图，若不能说明为什么不能插入语句
 

CREATE VIEW teacher_in1
AS
SELECT teacherid,teachername,sex,profession,homeaddr
FROM teacher
WHERE homeaddr like '一宿舍1栋%'
AND profession = '副教授'
with CHECK OPTION


INSERT INTO teacher_in1
VALUES
	(
		'dep03005',
		'王一',
		'女',
		'教授',
	'二宿舍一栋308')
	
插入失败，因为创建视图时，根据地址为一宿舍1栋创建的，然后还加了进行条件检查的语句。现在插入二宿舍的肯定插不进来。

image-20201102215505123

image-20201102215527458


6. 创建一个视图student_class,查询每个同学的所在班级名称（包括学号，学生姓名，班级号，班级名称）,通过该视图，去修改St0109010002号同学信息，将其姓名改为张三，班级改为11网络工程
 

CREATE VIEW student_class
AS
SELECT studentid,studentname,s.classid,classname
FROM student s,class c
WHERE s.classid = c.classid

SELECT * FROM student_class;

update student_class 
set studentname="张三",classid=(select distinct classid from (select * from student_class)a
where classname="11网络工程")
where studentid="St0109010002";

SELECT * FROM student_class;

在这里插入图片描述

在这里插入图片描述


7.删除视图student_class
 

DROP VIEW student_class

路过的大神，如果我写的不好的或者错误的地方还希望指点指点，谢谢！


版权声明：本文为HT412515原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
Tags: