物化视图

#  创建数据集
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);
-----------------------------------------------------
-- 创建物化视图 
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 or replace  function  trigger_function() returns trigger
as $$
begin
   if tg_op ='INSERT' then
   REFRESH MATERIALIZED VIEW Teachers_Courses_View;
   elsif tg_op ='UPDATE' then
   REFRESH MATERIALIZED VIEW Teachers_Courses_View;
   elsif tg_op ='DELETE' then
   REFRESH MATERIALIZED VIEW Teachers_Courses_View;
   end if;
   return  new;
end $$
language plpgsql;
-------------------------------关联触发器
create trigger trigger_res
after insert or update or delete
    on Teachers
   for each  row
    execute procedure trigger_function();

create trigger trigger_res2
after insert or update or delete
    on Courses
   for each  row
    execute procedure trigger_function();
--监控数据  执行 crud  实时更新

安装

# 解压 并进入目录下
tar -xvf postgresql-13.5.tar.gz
#  安装依赖
yum -y install openssl-devel libxml2-devel libxslt-devel python-devel cmake gcc-c++ zlib-devel bzip2 readline-devel
# 解决编译报错
yum install -y readline-devel
# 编译安装
./configure --prefix=/usr/local/postgresql
#  安装
make && make install

创建数据库

CREATE DATABASE dbname;

选择数据库
\l   # 查看已经存在的数据库：
\c + 数据库名 来进入数据库：
#系统的命令行查看，我么可以在连接数据库后面添加数据库名来选择数据库：
psql -h localhost -p 5432 -U postgre -d  postgres
 # 删除数据库  DROP DATABASE [ IF EXISTS ] name
DROP DATABASE
#
创建表格
CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( 一个或多个列 ));
#删除表格
DROP TABLE table_name;
\d  #命令来查看
#INSERT INTO 语句
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
#插入多行
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');
INSERT 0 2

#  分页
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]

数据库的备份与恢复

#备份 -f  sql  存储路径 备份的数据库名称

pg_dump -f post_dump.sql postgres
#  备份成tar 
# -F t  问价格式为 tar
pg_dump  -U postgre -F t -f post_dump.tar   postgres
恢复
# -U 用户名  -f  备份sql 路径  数据库名称
psql -U postgre -f ./post_dump.sql postgres

# 以tar 包恢复
#  -d 恢复到的数据库名称   数据库文件路径

pg_restore -U postgre -d postgres post_dump.tar

常用命令

\dt 显示 表
格式化
\x
\q    退出
\l
\c  切换数据库