PostgreSQL 数据定义语言 DDL

2023-09-14 15:12:19

文章目录

表创建

PostgreSQL表的构建语句与所有数据库都一样，结构如下，其核心在于构建表时，要指定上一些约束，例如主键、非空、唯一、检查、外键、默认值等。

CREATE TABLE table_name (  
    column1 datatype,  
    column2 datatype,  
    column3 datatype,  
    ...  
);

主键约束

-- 主键约束
drop table test;
create table test(
    id bigserial primary key ,
    name varchar(32)
);

非空

-- 非空约束
drop table test;
create table test(
    id bigserial primary key ,
    name varchar(32) not null
);

唯一约束

drop table test;
create table test(
    id bigserial primary key ,
    name varchar(32) not null,
    id_card varchar(32) unique
);
insert into test (name,id_card) values ('张三','333333333333333333');
insert into test (name,id_card) values ('李四','333333333333333333');
insert into test (name,id_card) values (NULL,'433333333333333333');

检查约束

-- 检查约束
-- 价格的表，price，discount_price
drop table test;
create table test(
    id bigserial primary key,
    name varchar(32) not null,
    price numeric check(price > 0),
    discount_price numeric check(discount_price > 0),
    check(price >= discount_price)
);
insert into test (name,price,discount_price) values ('粽子',122,12);

外键约束

不用

默认值约束

一般公司内，要求表中除了主键和业务字段之外，必须要有5个字段：created，create_id，updated，update_id，is_delete ，通常可以给这些字段设置默认值。

-- 默认值
create table test(
    id bigserial primary key,
    created timestamp default current_timestamp
);

触发器

触发器Trigger，是由事件出发的一种存储过程，当进行insert，update，delete，truncate操作时，会触发表的Trigger。

这里以学生信息和学生分数为例，在删除学生信息的同时，自动删除学生的分数。

先构建表信息，填充数据

create table student(
    id int,
    name varchar(32)
);
create table score(
    id int,
    student_id int,
    math_score numeric,
    english_score numeric,
    chinese_score numeric
);
insert into student (id,name) values (1,'张三');
insert into student (id,name) values (2,'李四');
insert into
    score
(id,student_id,math_score,english_score,chinese_score)
    values
(1,1,66,66,66);

insert into
    score
(id,student_id,math_score,english_score,chinese_score)
    values
(2,2,55,55,55);

select * from student;
select * from score;

为了完成级联删除的操作，需要编写pl/sql。

先查看一下PGSQL支持的plsql，查看一下PGSQL的plsql语法

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

触发器函数允许使用一些特殊变量

NEW
数据类型是RECORD；该变量为行级触发器中的INSERT/UPDATE操作保持新数据行。在语句级别的触发器以及DELETE操作，这个变量是null。

OLD
数据类型是RECORD；该变量为行级触发器中的UPDATE/DELETE操作保持新数据行。在语句级别的触发器以及INSERT操作，这个变量是null。

构建一个删除学生分数的函数。

-- 构建一个删除学生分数的触发器函数。
create function trigger_function_delete_student_score() returns trigger as $$
begin
    delete from score where student_id = old.id;
    return old;
end;
$$ language plpgsql;

在学生信息表删除时，执行声明的函数

image.png

CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

当 CONSTRAINT选项被指定，这个命令会创建一个 约束触发器 。这和一个常规触发器相同，不过触发该触发器的时机可以使用SET CONSTRAINTS调整。约束触发器必须是表上的 AFTER ROW触发器。它们可以在导致触发器事件的语句末尾被引发或者在包含该语句的事务末尾被引发。在后一种情况中，它们被称作是被 延迟 。一个待处理的延迟触发器的引发也可以使用 SET CONSTRAINTS立即强制发生。当约束触发器实现的约束被违背时，约束触发器应该抛出一个异常。

编写触发器，指定在删除某一行学生信息时，触发当前触发器，执行trigger_function_delete_student_score()函数

create trigger trigger_student 
after 
delete 
on student 
for each row 
execute function trigger_function_delete_student_score();
-- 测试效果
select * from student;
select * from score;
delete from student where id = 1;

表空间

在存储数据时，数据肯定要落到磁盘上，基于构建的tablespace，指定数据存放在磁盘上的物理地址。如果没有自己设计tablespace，PGSQL会自动指定一个位置作为默认的存储点。可以通过一个函数，查看表的物理数据存放在了哪个磁盘路径下。

-- 查询表存储的物理地址
select pg_relation_filepath('student');

image.png

这个位置是在$PG_DATA后的存放地址，41000就是存储数据的物理文件。

$PG_DATA == /var/lib/pgsql/12/data/

构建表空间

image.png
构建表空间,构建表空间需要用户权限是超级管理员，其次需要指定的目录已经存在

create tablespace tp_test location '/var/lib/pgsql/12/tp_test';

image.png

构建数据库，以及表，指定到这个表空间中

image.png

其实指定表空间的存储位置后，PGSQL会在$PG_DATA目录下存储一份，同时在咱们构建tablespace时，指定的路径下也存储一份。

这两个绝对路径下的文件都有存储表中的数据信息。

/var/lib/pgsql/12/data/pg_tblspc/41015/PG_12_201909212/41016/41020
/var/lib/pgsql/12/tp_test/PG_12_201909212/41016/41020

进一步会发现，其实在PGSQL的默认目录下，存储的是一个link，连接文件，类似一个快捷方式

视图

跟MySQL一样，把一些复杂的操作封装起来，还可以隐藏一些敏感数据。

视图对于用户来说，就是一张真实的表，可以直接基于视图查询一张或者多张表的信息。视图对于开发来说，就是一条SQL语句。

image.png

在PGSQL中，简单（单表）的视图是允许写操作的。但是强烈不推荐对视图进行写操作，虽然PGSQL默认允许（简单的视图），写入的时候，其实修改的是表本身。

简单视图

-- 构建一个
create view vw_score as 
(select id,math_score from score);

select * from vw_score;
update vw_score set math_score = 99 where id = 2;

多表视图

-- 复杂视图(两张表关联)
create view vw_student_score as 
(select stu.id as id ,stu.name as name ,score.math_score from student stu,score score where stu.id = score.student_id);

select * from vw_student_score;

update vw_student_score set math_score =999 where id = 2;

image.png

索引

索引的基本概念

索引是数据库中快速查询数据的方法。在提升查询效率的同时，也会带来一些问题：

  • 增加了存储空间
  • 写操作时，花费的时间比较多

索引可以提升效率，甚至还可以给字段做一些约束。

索引的分类

  • B-Tree索引：最常用的索引。

  • Hash索引：跟MySQL类似，做等值判断。

  • GIN索引：针对字段的多个值的类型，比如数组类型。

创建索引

image.png

准备大量测试数据，方便查看索引效果

-- 测试索引效果
create table tb_index(
    id bigserial primary key,
    name varchar(64),
    phone varchar(64)[]
);

-- 添加300W条数据测试效果
do $$
declare
    i int := 0;
begin
    while i < 3000000 loop
        i = i + 1;
        insert into
            tb_index
        (name,phone) 
            values
        (md5(random()::text || current_timestamp::text)::uuid,array[random()::varchar(64),random()::varchar(64)]);
    end loop;
end;
$$ language plpgsql;

在没有索引的情况下，先基于name做等值查询，看时间，同时看执行计划

-- c0064192-1836-b019-c649-b368c2be31ca
select * from tb_index where id = 2222222;
select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
explain select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
-- Seq Scan  代表全表扫描 
-- 时间大致0.3秒左右

在有索引的情况下，再基于name做等值查询，看时间，同时看执行计划

-- name字段构建索引（默认就是b-tree）
create index index_tb_index_name on tb_index(name);
-- 测试效果
select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
explain select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
-- Index Scan 使用索引
-- 0.1s左右

测试GIN索引效果

在没有索引的情况下，基于phone字段做包含查询

-- phone：{0.6925242730781953,0.8569644964711074}
select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
explain select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
-- Seq Scan 全表扫描
-- 0.5s左右

给phone字段构建GIN索引，在查询

-- 给phone字符串数组类型字段构建一个GIN索引
create index index_tb_index_phone_gin on tb_index using gin(phone);
-- 查询
select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
explain select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
-- Bitmap Index 位图扫描
-- 0.1s以内完成

物化视图

前面的普通视图，本质就是一个SQL语句，普通的视图并不会在本地磁盘存储，每次查询视图都是执行这个SQL，效率有点问题。

物化视图从名字上就可以看出来，必然是要持久化一份数据的。使用套路和视图基本一致。这样一来查询物化视图，就相当于查询一张单独的表。相比之前的普通视图，物化视图就不需要每次都查询复杂SQL，每次查询的都是真实的物理存储地址中的一份数据（表），并且可以单独设置索引等信息来提升物化视图的查询效率。

但是有好处就有坏处，更新时间不太好把控。 如果更新频繁，对数据库压力也不小。 如果更新不频繁，会造成数据存在延迟问题，实时性就不好了。

如果要更新物化视图，可以采用触发器的形式，当原表中的数据被写后，可以通过触发器执行同步物化视图的操作。或者就基于定时任务去完成物化视图的数据同步。
image.png

-- 构建物化视图
create materialized view mv_test as (select id,name,price from test);
-- 操作物化视图和操作表的方式没啥区别。
select * from mv_test;
-- 操作原表时，对物化视图没任何影响
insert into test values (4,'月饼',50,10);
-- 物化视图的添加操作(不允许写物化视图)，会报错
insert into mv_test values (5,'大阅兵',66);

PostgreSQL中，对物化视图的同步，提供了两种方式，一种是全量更新，另一种是增量更新。

全量更新语法，没什么限制，直接执行，全量更新

-- 查询原来物化视图的数据
select * from mv_test;
-- 全量更新物化视图
refresh materialized view mv_test;
-- 再次查询物化视图的数据
select * from mv_test;

增量更新，增量更新需要一个唯一标识，来判断哪些是增量，同时也会有行数据的版本号约束。

-- 查询原来物化视图的数据
select * from mv_test;
-- 给物化视图添加唯一索引。
create unique index index_mv_test on mv_test(id);
-- 增量更新物化视图
refresh materialized view concurrently mv_test;
-- 再次查询物化视图的数据
select * from mv_test;
-- 增量更新时，即便是修改数据，物化视图的同步，也会根据一个xmin和xmax的字段做正常的数据同步

update test set name = '汤圆' where id = 5;
insert into test values (5,'猪头肉',99,40);
select * from test;
更多推荐

飞驰的高铁-第15届蓝桥杯第一次STEMA测评Scratch真题精选

[导读]：超平老师的《Scratch蓝桥杯真题解析100讲》已经全部完成，后续会不定期解读蓝桥杯真题，这是Scratch蓝桥杯真题解析第150讲。飞驰的高铁，本题是2023年8月20日举行的第15届蓝桥杯STEMA测评Scratch编程中级组编程第2题，题目要求编程实现模拟高铁飞驰前进的效果。当按下数字1时，画面中的景

游戏创业小知识：游戏运营的步骤和流程

游戏运营是确保游戏在持续运行中保持活跃和成功的过程。以下是游戏运营的一般步骤流程：1.游戏发布前准备游戏选择：了解并熟悉游戏的核心概念、目标受众和游戏玩法。开发团队：组建开发团队，包括程序员、设计师、艺术家和声音设计师等。技术基础设施：建立游戏服务器、数据库和其他必要的技术基础设施。资金筹集：获取足够的资金来支持游戏的

原生微信小程序中进行 API 请求

原生微信小程序中进行API请求当在原生微信小程序中进行API请求时，封装请求可以提高代码的可维护性和可扩展性。在本篇博客中，我们将一步步介绍如何进一步封装请求，并添加请求超时、拦截器和请求取消功能。第一步：基本请求封装首先，我们创建一个用于发送HTTP请求的基本封装。在微信小程序中，我们使用wx.request发送请求

day41 jdk8新特性Stream流 数据库安装

流（Stream）中保存了对集合或者数组数据的操作，和集合类似，但是集合中保存的是数据。Stream不能保存数据一、创建流通过Collection对象的stream()或者parallelStream()通过Arrays类的stream(Array[]<T>)方法通过Stream接口of()iterate()gener

Java版本spring cloud + spring boot企业电子招投标系统源代码

项目说明随着公司的快速发展，企业人员和经营规模不断壮大，公司对内部招采管理的提升提出了更高的要求。在企业里建立一个公平、公开、公正的采购环境，最大限度控制采购成本至关重要。符合国家电子招投标法律法规及相关规范，以及审计监督要求；通过电子化平台提高招投标工作的公开性和透明性；通过电子化招投标，使得招标采购的质量更高、速度

计算机视觉与深度学习-经典网络解析-ZFNet-[北邮鲁鹏]

这里写目录标题ZFNet主要改进减小第一层卷积核将第二、第三个卷积层的卷积步长都设置为2增加了第三、第四个卷积层的卷积核个数ZFNetZFNet是一种基于AlexNet的模型，由MatthewD.Zeiler和RobFergus在2013年提出。相对于AlexNet，ZFNet结构与AlexNet网络结构基本一致，进行

阿里云服务器ECS_云主机_服务器托管_计算性能介绍

阿里云服务器是什么？云服务器ECS是一种安全可靠、弹性可伸缩的云计算服务，云服务器可以降低IT成本提升运维效率，免去企业或个人前期采购IT硬件的成本，阿里云服务器让用户像使用水、电、天然气等公共资源一样便捷、高效地使用服务器。阿里云服务器具有安全、稳定、弹性升降配、高性能、易用可扩展等优势。阿里云百科来详细说下什么是阿

使用 rtty 进行远程 Linux 维护和调试

rtty是一个用于在终端上进行远程连接和数据传输的工具。它提供了一种简单的方式来与远程设备进行通信，使得在不同主机之间传输数据变得更加方便。安装rtty是一个可执行程序，可以在Linux、macOS和Windows等平台上使用。Linux/macOS在终端中执行以下命令，使用curl下载rtty可执行文件：curl-L

Golang代码漏洞扫描工具介绍——trivy

Golang代码漏洞扫描工具介绍——trivyGolang作为一款近年来最火热的服务端语言之一，深受广大程序员的喜爱，笔者最近也在用，特别是高并发的场景下，golang易用性的优势十分明显，但笔者这次想要介绍的并不是golang本身，而且golang代码的漏洞扫描工具，毕竟作为服务端的程序，安全性一直是一个不同忽视的地

新势力在智能化路上，正抢了Tier 1的生意

作者|&nbsp;Amy编辑|&nbsp;德新上半年的汽车行业价格内卷，下半年则一下资本涌入，风起云涌。先是蔚来拿到了11亿美元来自中东的投资，紧接着7月大众以7亿美元投资小鹏汽车，8月哪吒完成70亿元Crossover轮投资。传闻中，还有大众捷达与Stelliantis两大集团接洽零跑汽车，秘密洽谈投资收购以及潜在的

.NET超简单轻量级的HTTP请求组件Flurl

简介Flurl是一个用于构建基于HTTP请求的C#代码的库。它的主要目的是简化和优雅地处理网络请求（只用很少的代码完成请求）。Flurl提供了一种简单的方法来构建GET、POST、PUT等类型的请求，以及处理响应和异常。它还提供了一些高级功能，如链式调用、缓存请求结果、自动重定向等。本文将介绍Flurl的GET、POS

热文推荐
  1. [C++ 网络协议] 多种I/O函数

    1.Linux的send&recv函数1.1send函数和recv函数#include<sys/socket.h>ssize_tsend(intsockfd,//套接字文件描述符constvoid*buf,//保存待传输数据的缓冲地址值size_tnbytes,//待传输的字节数intflags//传输数据时指定的可选

  2. JavaScript 测试基础，TDD、BDD、Benchmark

    目录测试框架TDDBDDBenchmark使用场景结果报告示例示例代码当我写一段测试的时候，我在想些什么设计思路思路一思路二当我以测试驱动开发的时候，我在想些什么原则：谁开发，谁测试。注意：原则上应该先写测试，再进行编码；如果需求时间紧，可以先进行功能实现，但务必后续维护时候将测试代码补充完善。BDD（优先）+TDD（

  3. 【业务功能篇112】maven 指定打包某个module子模块 打包命令参数

    -am--also-make构建所列模块的所有依赖模块；-pl--projects<arg>构建制定的模块，模块间用逗号分隔；指定settings.xml打包mvncleanpackage--settingsC:\Users\Admini\.m2\settings.xml如果需要根据指定的settings.xml文件打

  4. html综合案例2

    <!DOCTYPEhtml><htmllang="en"><head><metacharset="UTF-8"><metahttp-equiv="X-UA-Compatible"content="IE=edge"><metaname="viewport"content="width=device-width,initi

  5. 软信天成：如何提高云数据仓库的数据质量？

    随着云计算的深入普及、5G和边缘计算等技术带来了数据量的爆发增长，数据驱动商业运作向实时化和自动化迈进，越来越多的企业开始考虑嵌入基于云计算的企业数据仓库，以Snowflake、MicrosoftSynapseAnalytics、GoogleBigQuery、AmazonRedshift等为代表的云数据仓库或成为数据时

  6. 神经网络案例分析

    神经网络（neuralnetwork）是一种模拟人脑神经思维方式的数据模型，神经网络有多种，包括BP神经网络、卷积神经网络，多层感知器MLP等，最为经典为神经网络为多层感知器MLP（Multi-LayerPerception），SPSSAU默认使用该模型。类似其它的机器学习模型（比如决策树、随机森林、支持向量机SVM等

  7. STM32 CAN使用记录：FDCAN基础通讯

    文章目录目的基础说明关键配置与代码轮询方式中断方式收发测试示例链接总结目的CAN是非常常用的一种数据总线，被广泛用在各种车辆系统中。这篇文章将对STM32中FDCAN的使用做个示例。CAN的一些基础介绍与使用可以参考下面文章：《CAN基础概念》https://blog.csdn.net/Naisu_kun/articl

  8. 贸易战的影响：跨境电商的“黑洞”风险与机遇

    当今全球贸易局势充满了不确定性和动荡。贸易战的阴云笼罩下，跨境电商企业面临着前所未有的挑战，但与此同时，也蕴藏着巨大的机遇。本文将深入探讨贸易战对跨境电商的影响，以及企业在这个新现实中如何应对风险并寻找机遇。贸易战的背景贸易战是国家之间为争夺贸易利益而采取的一系列贸易政策和关税措施的冲突。近年来，中美之间的贸易战引起了

  9. 大模型股票交易-挖掘新闻和情绪价值

    埃隆·马斯克(ElonMusk)的星际飞船于2023年4月20日升空后爆炸。想象一下，当时您正在观察股市，突然出现新闻，您会如何交易TSLA股票？我希望您不要与我争论，您作为交易者（而不是投资者）要做的第一件事就是摆脱现有的多头头寸并可能做空股票。让我们看看这样的交易是否有利可图。根据此链接SpaceXrocketla