MySQL的视图

最新推荐文章于 2025-03-05 18:44:42 发布

JAX_fire

最新推荐文章于 2025-03-05 18:44:42 发布

阅读量570

点赞数

分类专栏：数据库文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/JAX_fire/article/details/125367313

版权

数据库专栏收录该内容

19 篇文章

订阅专栏

本文详细介绍了视图的概念，包括其作为虚拟表的作用，如何简化查询、保障安全，以及使用CREATE OR REPLACE VIEW语句创建、ALTER VIEW修改和区分可更新视图的方法。通过实例演示了视图的创建、修改和更新操作，以及注意事项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

概念：

视图（view）是一个虚拟表，并非真实存在，其本质是跟去SQL语句获取动态的数据集，并为其明明，用户使用时秩序使用视图名称即可获取结果集，并可以将其当做表来使用。
数据库只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。
使用视图查询数据时，数据库系统会从原来的表中去除对应的数据。因此，视图中的数据时依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

作用：

简化代码，可以吧重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用。
安全原因，如果一张表中有很多数据，很多信息不希望让所有人看到，此时可以使用视图，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，可以对不同的用户没设定不同的视图。

方法：
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE} ]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED |LOCAL] CHECK OPTION]

注：
（1）algorithm：可选项，表示视图选择的算法。
（2）view_name：表示要创建的视图名称。
（3）column_list：可选项，指定视图中各个属性的名称，默认情况下与SELECT语句中的查询的属性相同。
（4）selcet_statement：表示一个完整的查询语句，将查询记录导入视图中。
（5）[with [cascaded |local ] check option]：可选项，表示更新视图时要保证在该视图的权限范围之内。

例如：
先创建表和数据

CREATE TABLE dept  (
  deptno int NOT NULL,
  dname varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  loc varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`deptno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO dept VALUES (10, '教研部', '北京');
INSERT INTO dept VALUES (20, '学工部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '武汉');

CREATE TABLE emp  (
  empno int NOT NULL,
  ename varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  job varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  mgr int NULL DEFAULT NULL,
  hiredate date NULL DEFAULT NULL,
  sal decimal(7, 2) NULL DEFAULT NULL,
  COMM decimal(7, 2) NULL DEFAULT NULL,
  deptno int NULL DEFAULT NULL,
  PRIMARY KEY (empno) USING BTREE,
  INDEX fk_emp(mgr) USING BTREE,
  CONSTRAINT fk_emp FOREIGN KEY (mgr) REFERENCES emp (empno) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, NULL, 20);
INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30);
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30);
INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750.00, NULL, 20);
INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500.00, 14000.00, 30);
INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, NULL, 30);
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, NULL, 10);
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, NULL, 20);
INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000.00, NULL, 10);
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30);
INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, NULL, 20);
INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500.00, NULL, 30);
INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, NULL, 20);
INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, NULL, 10);

CREATE TABLE salgrade  (
  grade int NOT NULL,
  losal int NULL DEFAULT NULL,
  hisal int NULL DEFAULT NULL,
  PRIMARY KEY (grade) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);

开始尝试

CREATE OR REPLACE VIEW view_emp
AS
SELECT ename,job FROM emp;

SHOW TABLES;
SHOW FULL TABLES;

SELECT * FROM view_emp;

修改视图

概念：修改视图是指修改数据库中已经存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。MySQL中通过CREATE OR REPLACE VIEW 语句和ALTER VIEW语句来修改视图。

方法：
ALTER VIEW 视图名 AS SELECT 语句

例如：

ALTER VIEW view_emp
AS 
SELECT a.deptno,a.dname,a.loc,b.ename,b.sal FROM dept a,emp b WHERE a.deptno = b.deptno;

SELECT * FROM view_emp;

更新视图

概念：某些视图是可更新的。也就是说，可以在UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关心。如果视图包含下述结构的任何一种，那么它就是不可更新的：

聚合函数（SUM(),MIN(),MAX(),COUNT()等）
DISTINCT
GROUP BY
HAVING
UNION或UNION ALL
位于选择列表中的子查询
JOIN
FROM子句中的不可更新视图
WHERE子句的子查询，引用FROM子句中的表
仅引用文字值（在该情况下，没有要更新的基本表）

注：视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。

例如：

CREATE OR REPLACE VIEW view1_emp
AS 
SELECT ename,job FROM emp;

SELECT * FROM view1_emp;

UPDATE view1_emp SET ename='周瑜' WHERE ename ='鲁肃';

-- 这个无法运行，因为emp中插入的数据还缺少其他值
INSERT INTO view1_emp VALUES('孙权','文员');

-- 视图包含聚合函数不可更新
CREATE OR REPLACE VIEW view2_emp
AS 
SELECT COUNT(*) FROM emp;

SELECT * FROM view2_emp;

INSERT INTO view2_emp VALUES(100);
UPDATE view2_emp SET cnt = 100;

-- 视图包含distinct不可更新
CREATE OR REPLACE VIEW view3_emp
AS 
SELECT DISTINCT job FROM emp;

SELECT * FROM view3_emp;

INSERT INTO view3_emp VALUES('财务');

-- 视图包含group by HAVING不可更新
CREATE OR REPLACE VIEW view4_emp
AS 
SELECT deptno,COUNT(*) cnt  FROM emp GROUP BY deptno HAVING cnt>2;

SELECT * FROM view4_emp;

INSERT INTO view4_emp VALUES(30,100);

-- 视图包含UNION或HUNION ALL 不可更新
CREATE OR REPLACE VIEW view5_emp
AS 
SELECT empno,ename  FROM emp WHERE empno<= 1005
UNION
SELECT empno,ename  FROM emp WHERE empno> 1005;

SELECT * FROM view5_emp;

INSERT INTO view5_emp VALUES(1015,'韦小宝');

-- 位于选择列表中的子查询不可更新
CREATE OR REPLACE VIEW view6_emp
AS 
SELECT empno,ename,sal  FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

SELECT * FROM view6_emp;

INSERT INTO view6_emp VALUES(1015,'韦小宝',300000);

-- 两张表进行JOIN不可更新
CREATE OR REPLACE VIEW view7_emp
AS 
SELECT dname,ename,sal  FROM emp a JOIN dept b on a.deptno=b.deptno; 

SELECT * FROM view7_emp;

INSERT INTO view7_emp(dname,ename,sal) VALUES('行政部','韦小宝',300000);

-- FROM子句中的不可更新
CREATE OR REPLACE VIEW view8_emp
AS 
SELECT '行政部' dname ,'杨过' ename;

SELECT * FROM view8_emp;

INSERT INTO view8_emp VALUES('行政部','韦小宝');

其他操作

重命名视图

方法：RENAME TABLE 视图名 TO 新视图名;

删除视图

方法：DROP VIEW 视图名[,视图名…];

注：删除视图时，只能删除视图的定义，不会删除数据。


-- 重命名视图
RENAME TABLE view1_emp to myview1;

-- 删除视图
DROP VIEW IF EXISTS myview1;