PostgreSQl 物化视图

物化视图（Materialized View）是 PostgreSQL 提供的一个扩展功能，它是介于视图和表之间的一种对象。

物化视图和视图的最大区别是它不仅存储定义中的查询语句，而且可以像表一样存储数据。物化视图和表的最大区别是它不支持 INSERT、UPDATE、DELETE 以及 MERGE 语句，只能通过刷新物化视图进行数据的更新。

物化视图通过提前运行并存储查询结果，通常用于查询优化、数据仓库、数据集成等场景。

另外，PostgreSQL 目前不支持物化视图的实时更新，因此物化视图中的数据通常不是最新数据。

 一、创建测试数据

PostgreSQL 使用 CREATE MATERIALIZED VIEW 语句创建视图：

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] name
AS query
[ WITH [ NO ] DATA ];

其中，IF NOT EXISTS 可以避免重复创建一个已经存在的物化视图时产生错误；name 是物化视图的名称；query 是物化视图的查询语句。

WITH DATA 表示创建时填充物化视图中的数据，这个是默认选项；WITH NO DATA 表示创建时不填充数据，此时无法查询物化视图中的数据，需要执行 REFRESH MATERIALIZED VIEW 命令刷新物化视图数据之后才能查询。

-- 老师表
CREATE TABLE Teachers (  
    TeacherID INT NOT NULL PRIMARY KEY,  
    FirstName VARCHAR(50) NOT NULL,  
    LastName VARCHAR(50) NOT NULL  
);

-- 课程表
CREATE TABLE Courses (  
    CourseID INT NOT NULL PRIMARY KEY,  
    CourseName VARCHAR(100) NOT NULL,  
    TeacherID INT,  
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)  
);


-- 测试数据
INSERT INTO Teachers (TeacherID, FirstName, LastName) VALUES (1, 'John', 'Doe');  
INSERT INTO Teachers (TeacherID, FirstName, LastName) VALUES (2, 'Jane', 'Smith');

INSERT INTO Courses (CourseID, CourseName, TeacherID) VALUES (1, 'Mathematics', 1);  
INSERT INTO Courses (CourseID, CourseName, TeacherID) VALUES (2, 'Science', 2);  
INSERT INTO Courses (CourseID, CourseName, TeacherID) VALUES (3, 'History', 1);



select * from Teachers;
select * from Courses;


CREATE MATERIALIZED VIEW Teachers_Courses_View AS 
SELECT
	t.FirstName || ' ' ||t.LastName as teacher_name,
	c.CourseName as course_name
FROM
	Courses c LEFT JOIN Teachers T ON C.TeacherID = T.TeacherID

二、创建物化视图：学生-课程-老师的关联关系

CREATE MATERIALIZED VIEW Teachers_Courses_View AS 
SELECT
	t.FirstName || ' ' ||t.LastName as teacher_name,
	c.CourseName as course_name
FROM
	Courses c LEFT JOIN Teachers T ON C.TeacherID = T.TeacherID

 查询数据

二、创建物化视图的索引

物化视图可以像表一样支持索引，实现约束和查询优化。

数据重复这里不能创建索引，这些测试数据太简单了，这里做个介绍即可。

三、刷新物化视图

PostgreSQL 物化视图不会自动刷新数据，需要手动执行 REFRESH MATERIALIZED VIEW 语句。

测试插入数据，不刷新视图则不会更新数据，刷新之后数据就出来了

INSERT INTO Teachers (TeacherID, FirstName, LastName) VALUES (3, '尼古拉斯', '张三');  
INSERT INTO Courses (CourseID, CourseName, TeacherID) VALUES (4, '论如何无责犯罪', 3);

REFRESH MATERIALIZED VIEW Teachers_Courses_View;

四、修改物化视图

ALTER MATERIALIZED VIEW 语句可以修改物化视图的一些属性，例如名称、字段名等：

alter MATERIALIZED view Teachers_Courses_View rename teacher_name to tt_name;
alter MATERIALIZED view Teachers_Courses_View rename to Teachers_Courses_View_10086;

五、删除物化视图

PostgreSQL 使用 DROP MATERIALIZED VIEW 语句删除物化视图：

DROP MATERIALIZED VIEW Teachers_Courses_View_10086;

物化视图介绍就这些了，欢迎斧正交流！！！