PostgreSQL 数据定义语言 DDL

文章目录

  • 表创建
    • 主键约束
    • 非空
    • 唯一约束
    • 检查约束
    • 外键约束
    • 默认值约束
  • 触发器
  • 表空间
    • 构建表空间
  • 视图
  • 索引
    • 索引的基本概念
    • 索引的分类
    • 创建索引
  • 物化视图

表创建

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 intoscore
(id,student_id,math_score,english_score,chinese_score)values
(1,1,66,66,66);insert intoscore
(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>> ]
[ DECLAREdeclarations ]
BEGINstatements
END [ label ];

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

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

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

-- 构建一个删除学生分数的触发器函数。
create function trigger_function_delete_student_score() returns trigger as $$
begindelete 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:INSERTUPDATE [ OF column_name [, ... ] ]DELETETRUNCATE

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 $$
declarei int := 0;
beginwhile i < 3000000 loopi = i + 1;insert intotb_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;

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/197643.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

ACWSpring1.3

首先,前端写ajax写上我们的访问路径(就在我们前端的源代码里面),我们建了两个包pkController用于前端页面url映射过来一层一层找到我们的RestController返回bot1里面有键值,返回的这就是一个session对象bot1这个map.前端拿到我们bot1里的两个值给到我们前端显示出来 1准备页面:…

Linux:补充一些常用命令

Linux&#xff1a;补充一些常用命令 1. free -h2. df -lh3. du -sh *4. uname -a5. which6. mvn install 编译打包7. find -name *.jar8. cd -9. nohup java -jar *.jar &10. ps -ef|grep java11. netstat -ntlp 1. free -h free 命令显示系统使用和空闲的内存情况&#x…

Flutter笔记:缩放手势

Flutter笔记 缩放手势 作者&#xff1a;李俊才 &#xff08;jcLee95&#xff09;&#xff1a;https://blog.csdn.net/qq_28550263 邮箱 &#xff1a;291148484163.com 本文地址&#xff1a;https://blog.csdn.net/qq_28550263/article/details/134485138 目 录 1. 概述2. 缩放手…

Java多线程核心技术第一阶段-Java多线程基础 02

接上篇&#xff1a;Java多线程核心技术第一阶段-Java多线程基础 01 3.3 清除中断状态的使用场景 this.interrupted()方法具有清除状态标志值的功能&#xff0c;借用此特性可以实现一些效果。 【示例3.3.1】在MyThread4线程中向list1和list2存放数据&#xff0c;基于单一职责原…

docker-compose 部署 MySQL 8

目录 前言MySQL 配置文件(my.cnf)docker-compose.yml安装卸载 前言 Windows/Linux 系统通过 docker-compose 部署 MySQL8.0。 MySQL 配置文件(my.cnf) # 服务端参数配置 [mysqld] usermysql # MySQL启动用户 default-storage-engineINNODB # 创建新表时…

【脑与认知科学】【n-back游戏】

请参考课堂内容&#xff0c;设计一种测试工作记忆的实验方法&#xff0c;并选择三位同学作为被试测试工作记忆。请画出实验流程图&#xff0c;叙述实验测试目标&#xff0c;并分析实验结果。 举例&#xff1a;一般我们选择n_back来测试对数字或字母的记忆&#xff0c;选择色块实…

springboot实现在线人数统计

在线人数统计 笔者做了一个网站&#xff0c;需要统计在线人数。 在线有两种&#xff1a; 一、如果是后台系统如果登录算在线&#xff0c;退出的时候或者cookie、token失效的时候就算下线 二、如果是网站前台&#xff0c;访问的时候就算在线 今天我们来讲一下第2种情况&…

SQL零基础入门教程,贼拉详细!贼拉简单! 速通数据库期末考!(九)

UNION ALL UNION ALL 用于合并两个或多个 SELECT 语句的结果。 请注意&#xff0c;UNION ALL 合并的每个 SELECT 语句必须是查询相同数量&#xff0c;相同数据类型的字段&#xff0c;且顺序也必须一致。另外结果集中的列名总是等于 UNION ALL 中第一个 SELECT 语句中的列名。 …

数据结构与算法-图

图 &#x1f388;2.图的存储结构&#x1f4d6;2.4.2邻接表的存储✅2.4.2.1逆邻接表✅2.4.2.2邻接表存储结构的定义✅2.4.2.3邻接表存储结构的类定义✅2.4.2.4创建n个顶点m条边的无向网✅2.4.2.5创建n个顶点m条边的有向网✅2.4.2.6定位操作-查找定点信息在顶点数组中的下标✅2.4…

3GPP协议解读(一)_23.501_23.502_PDU Session_SMF与UDP的交互

UE发起计算服务申请后&#xff0c;网络侧处理的流程 UE发起服务的流程&#xff1a;service request网络侧处理服务涉及的通信数据通过PDU Session进行传输&#xff0c;涉及到SMF与UPF的交互。PDU Session的建立、管理全部由SMF&#xff08;Session Management Function&#x…

【uniapp】 video视频层级、遮挡其他弹窗或顶部导航 使用nvue覆盖

uniapp 顶部导航和弹窗被video遮挡解决办法 第一步&#xff1a;配置 subNVues {"path": "pages/index/index","style": {"navigationBarTitleText": "uni-app","navigationStyle": "custom","app-…

SQL零基础入门教程,贼拉详细!贼拉简单! 速通数据库期末考!(八)

FULL OUTER JOIN 除了前面讲到的 INNER JOIN&#xff08;内连接&#xff09;、LEFT JOIN&#xff08;左连接&#xff09;、RIGHT JOIN&#xff08;右连接&#xff09;&#xff0c;还有另外一种关联方式&#xff0c;即 FULL OUTER JOIN&#xff08;全外连接&#xff09; FULL O…

Javaweb之Vue指令案例的详细解析

2.3.5 案例 需求&#xff1a; 如上图所示&#xff0c;我们提供好了数据模型&#xff0c;users是数组集合&#xff0c;提供了多个用户信息。然后我们需要将数据以表格的形式&#xff0c;展示到页面上&#xff0c;其中&#xff0c;性别需要转换成中文男女&#xff0c;等级需要…

java游戏制作-拼图游戏

一.制作主界面 首先创建一个Java项目命名为puzzlegame。 再在src中创建一个包&#xff0c;用来制作主界面 代码&#xff1a; 结果&#xff1a; 二.设置界面 代码&#xff1a; 三.初始化界面 代码&#xff1a; 优化代码&#xff1a; 结果&#xff1a; 四.添加图片 先在Java项…

HDFS、MapReduce原理--学习笔记

1.Hadoop框架 1.1框架与Hadoop架构简介 &#xff08;1&#xff09;广义解释 从广义上来说&#xff0c;随着大数据开发技术的快速发展与逐步成熟&#xff0c;在行业里&#xff0c;Hadoop可以泛指为&#xff1a;Hadoop生态圈。 也就是说&#xff0c;Hadoop指的是大数据生态圈整…

机器学习的医疗乳腺癌数据的乳腺癌疾病预测

项目视频讲解:基于机器学习的医疗乳腺癌数据的乳腺癌疾病预测 完整代码数据分享_哔哩哔哩_bilibili 效果演示: 代码: #第一步!导入我们需要的工具 import numpy as np import pandas as pd import matplotlib.pyplot as plt import seaborn as sns %matplotlib inlin…

Postman实现接口的加密和解密

近期在复习Postman的基础知识&#xff0c;在小破站上跟着百里老师系统复习了一遍&#xff0c;也做了一些笔记&#xff0c;希望可以给大家一点点启发。 1、目前市面上的加密的方式 对称式加密&#xff1a;DES&#xff0c;AES&#xff0c;Base64加密算法 非对称加密&#xff1a…

跟随鼠标的粒子特效分享

前些天发现了一个巨牛的人工智能学习网站,通俗易懂,风趣幽默,忍不住分享一下给大家。点击跳转到网站。 广告打完,我们进入正题,先看效果: 上代码: html, body {padding: 0;margin: 0;overflow: hidden; }import * as PIXI from https://cdn.skypack.dev/pixi.js@7.2.…

若依启动步骤

1.创建数据库 2.启动redis 3.改后端的数据库连接配置 4.配置redis redis的地址&#xff1a;cmd中ipconfig命令查看 6.启动后端&#xff1a;如下 7.启动前端ruoyi-ui中 先运行npm install&#xff0c;再npm run dev。项目就启动成功了。 用户名&#xff1a;admin 密码&#x…

C#入门(1):程序结构、数据类型

一、C#程序结构 第一个C#程序 using System;namespace base_01 {class Program{#region 代码折叠块static void Main(string[] args){//控制台输出Console.WriteLine("Hello World!");Console.Write("C#是微软的编程语言"); //不换行输出//Console.Rea…