PostgreSQL学习笔记（二）：PostgreSQL基本操作

PostgreSQL 是一个功能强大的开源关系型数据库管理系统 (RDBMS)，支持标准的 SQL 语法，并扩展了许多功能强大的操作语法.

数据类型

  1. 数值类型
    数据类型描述存储大小示例值
    SMALLINT小范围整数，范围：-32,768 到 32,7672 字节-123
    INTEGER 或 INT中范围整数，范围：-2,147,483,648 到 2,147,483,6474 字节12345
    BIGINT大范围整数，范围：-9,223,372,036,854,775,808 到 9,223,372,036,854,775,8078 字节12345678901234
    NUMERIC(p, s) 或 DECIMAL(p, s)精确的数字，p 是总位数，s 是小数点后位数可变长1234.56
    REAL 或 FLOAT4单精度浮点数，范围：6 位小数精度4 字节1.23
    DOUBLE PRECISION 或 FLOAT8双精度浮点数，范围：15 位小数精度8 字节123.45678
    SERIAL自动递增整数，等同于 INTEGER + 自增4 字节1, 2, 3...
    BIGSERIAL自动递增整数，等同于 BIGINT + 自增8 字节1, 2, 3...
  2. 字符类型
    数据类型描述存储大小示例值
    CHAR(n) 或 CHARACTER(n)固定长度字符串，不足位数会用空格补齐n 字节'abc '
    VARCHAR(n) 或 CHARACTER VARYING(n)可变长度字符串，最多 n 个字符实际长度 + 1 字节'abc'
    TEXT不限长度的字符串可变长'Hello, world!'

    说明：
    <1>：CHAR(n) 固定长度，适合存储固定格式的字符串。
    <2>：VARCHAR(n) 是常用的可变长度类型。
    <3>：TEXT 用于存储长文本。

  3. 布尔类型
    数据类型描述存储大小示例值
    BOOLEAN布尔值，TRUE、FALSE 或 NULL1 字节TRUE
  4. 日期和时间类型
    数据类型描述存储大小示例值
    DATE日期（不包含时间）4 字节'2025-01-06'
    TIME [WITHOUT TIME ZONE]时间（不包含日期）8 字节'12:30:00'
    TIMESTAMP [WITHOUT TIME ZONE]日期和时间8 字节'2025-01-06 12:30:00'
    TIMESTAMP WITH TIME ZONE带时区的日期和时间8 字节'2025-01-06 12:30:00+08'
    INTERVAL时间间隔16 字节'1 year 2 months'
  5. 几何类型
    数据类型描述示例值
    POINT平面中的一个点'(1.5, 2.5)'
    LINE无限长的直线'{1, -1, 0}'
    LSEG有限线段'(1.5, 2.5), (3.5, 4.5)'
    BOX矩形框'((1, 2), (3, 4))'
    PATH路径（开或闭）'((1, 1), (2, 2), (3, 3))'
    POLYGON多边形'((1, 1), (2, 2), (3, 3))'
    CIRCLE圆形'<(1, 1), 5>'
    几何类型用于存储点、线、圆等几何信息
  6. 数组类型
    数据类型描述示例值
    INTEGER[]整数数组{1, 2, 3}
    TEXT[]文本数组{'hello', 'world'}
  7. JSON和JSONB类型
    数据类型描述示例值
    JSON原始 JSON 格式，保留格式'{"key": "value"}'
    JSONB二进制存储的 JSON，查询更快'{"key": "value"}'
  8. UUID类型
    数据类型描述示例值
    UUID通用唯一标识符 (128 位)'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'
  9. XML类型
    数据类型描述示例值
    XMLXML 数据'<note><to>Tove</to></note>'
  10. 枚举类型

     枚举类型允许定义一组固定值。

    CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');

    使用：

    CREATE TABLE person (name TEXT,current_mood mood
);
  11. 范围类型
    数据类型描述示例值
    INT4RANGE整数范围[1,10)
    NUMRANGE数字范围[1.5,2.5)
    TSRANGE不带时区的时间范围['2023-01-01', '2023-12-31')
    TSTZRANGE带时区的时间范围['2023-01-01 00:00:00+00', '2023-12-31 23:59:59+00')

数据操作

创建数据库和用户

// 创建数据库
CREATE DATABASE database_name;// 删除数据库
DROP DATABASE database_name;// 创建用户
CREATE USER user_name WITH PASSWORD 'password';// 为用户授予权限
GRANT ALL PRIVILEGES ON DATABASE database_name TO user_name;

数据定义语言 (DDL)

// 创建表
CREATE TABLE table_name (column_name1 data_type [constraints],column_name2 data_type [constraints],...
);// 添加列
ALTER TABLE table_name ADD COLUMN column_name data_type;// 修改列
ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;// 删除列
ALTER TABLE table_name DROP COLUMN column_name;// 删除表
DROP TABLE table_name;

数据操作语言 (DML)

基本查询：

// 插入数据
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);// 查询数据
SELECT column1, column2 FROM table_name WHERE condition;SELECT * FROM table_name;// 更新数据
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;// 删除数据
DELETE FROM table_name WHERE condition;

扩展查询：

// 分组查询
SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 1;// 连接查询（JOIN）
内连接：
SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b
ON a.common_column = b.common_column;左连接：
SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b
ON a.common_column = b.common_column;右连接：
SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b
ON a.common_column = b.common_column;// 子查询
SELECT column1
FROM table_name
WHERE column2 IN (SELECT column2FROM another_tableWHERE condition
);

PostgreSQL特性

PostgreSQL提供了几种丰富的数据类型，有一些特定的用法。

数组

在 PostgreSQL 中，数组是一种非常强大的数据类型，它可以存储一组同类型的值（例如整数、文本等），以下是关于数组的详细用法。

创建数组字段：

CREATE TABLE users (id SERIAL PRIMARY KEY,name TEXT,favorite_numbers INTEGER[],  -- 整数数组字段favorite_colors TEXT[]       -- 文本数组字段
);

插入数组数据：

INSERT INTO users (name, favorite_numbers, favorite_colors)
VALUES ('Alice', '{1, 2, 3}', '{"red", "blue", "green"}');

查询数组数据：

// 查询 favorite_numbers 包含 2 的用户
SELECT name FROM users WHERE 2 = ANY (favorite_numbers);// 查询数组是否包含所有指定值
-- 查询 favorite_numbers 包含所有值 {1, 2} 的用户
SELECT name FROM users WHERE favorite_numbers @> '{1, 2, 4}';// 查询数组是否为另一个数组的子集
-- 查询 favorite_numbers 是 {1, 2, 3} 的子集的用户
SELECT name FROM users WHERE favorite_numbers <@ '{1, 2, 3}';

更新数组：

// 向 Alice 的 favorite_numbers 中追加一个新值 40
UPDATE users SET favorite_numbers = favorite_numbers || 40 WHERE name = 'Alice';// 将 Alice 的 favorite_numbers 的第 1 个元素更新为 100
UPDATE users SET favorite_numbers[1] = 100 WHERE name = 'Alice';

删除数据中元素：

UPDATE users SET favorite_numbers = array_remove(favorite_numbers, '40') WHERE name = 'Alice';

PostgreSQL 提供了许多用于数组操作的内置函数和操作符：

操作符/函数描述示例
ARRAY[...]创建数组SELECT ARRAY[1, 2, 3];
ANY (array)判断值是否在数组中SELECT 2 = ANY('{1, 2, 3}');
@>数组包含另一个数组SELECT '{1, 2, 3}'::int[] @> '{1, 2}';
<@数组是另一个数组的子集SELECT '{1, 2}'::int[] <@ '{1, 2, 3}';
array_length(array, n)获取数组的长度SELECT array_length('{1, 2, 3}', 1);
unnest(array)将数组展开为行SELECT unnest('{1, 2, 3}'::int[]);
array_remove删除数组中某个值UPDATE users SET favorite_numbers = array_remove(favorite_numbers, '40')

JSON和JSONB

在 PostgreSQL 中，JSON 和 JSONB 是两种用于存储 JSON 数据的列类型。它们功能强大，广泛用于现代应用中。

JSON 与 JSONB 的主要区别

  1. 存储方式：

    • JSON：以文本格式存储数据，存储和检索时保持输入顺序。
    • JSONB：以二进制格式存储，经过解析和去重处理（键的顺序不保存）。

  2. 性能：

    • JSON：适合读取为原始 JSON 字符串，查询效率较低。
    • JSONB：支持索引，查询和操作效率更高。

  3. 使用场景：

    • JSON：需要保持原始 JSON 数据格式（如调试或日志记录）。
    • JSONB：需要高效查询、过滤或处理 JSON 数据。

创建表：

CREATE TABLE example (id SERIAL PRIMARY KEY,data JSON,      -- 存储为 JSON 格式data_b JSONB    -- 存储为 JSONB 格式
);

插入数据：

INSERT INTO example (data, data_b) VALUES 
('{"name": "Alice", "age": 25, "tags": ["developer", "gamer"]}', '{"name": "Alice", "age": 25, "tags": ["developer", "gamer"]}');

查询数据：

1. 访问 JSON 数据的字段

使用 -> 和 ->> 操作符：

  • ->：提取字段为 JSON 类型。
  • ->>：提取字段为文本类型。
-- 提取 JSON 对象的字段
SELECT data->'name' AS name_json, data_b->'name' AS name_jsonb FROM example;-- 提取字段值为文本
SELECT data->>'name' AS name_text, data_b->>'name' AS name_text FROM example;

2、查询嵌套数据

-- 提取嵌套 JSON 对象中的字段值
SELECT data->'tags'->>0 AS first_tag, data_b->'tags'->>0 AS first_tag FROM example;

3、JSONB函数和操作符

  • 检查字段是否存 
    -- 检查 JSONB 中是否包含某个字段
SELECT data_b ? 'name' AS has_name FROM example;-- 检查多个字段是否存在
SELECT  data_b ?& array['name', 'age'] AS has_all 
FROM example;
  • 数组操作 
    -- 查询 JSONB 数组是否包含某个值
SELECT data_b->'tags' ? 'developer' AS contains_tag FROM example;
  • 删除字段 
    SELECT data_b - 'age' AS without_age FROM example;

数据更新：

// 更新json数据中age字段值为30
UPDATE example SET data_b = jsonb_set(data_b, '{age}', '30')
WHERE data_b->>'name' = 'Alice';

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

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

相关文章

Leffa 虚拟试衣论文笔记

Leffa 虚拟试衣论文笔记

Leffa: Learning Flow Fields in Attention for Controllable Person Image Generation https://github.com/xuanandsix/awesome-virtual-try-on-note/tree/main/Leffa 打开链接查看详情&#xff0c;更多虚拟试穿论文持续更新。
阅读更多...
06-RabbitMQ基础

06-RabbitMQ基础

目录 1.初识MQ 1.1.同步调用 1.2.异步调用 1.3.技术选型 2.RabbitMQ 2.1.安装 2.2.收发消息 2.2.1.交换机 2.2.2.队列 2.2.3.绑定关系 2.2.4.发送消息 2.3.数据隔离 2.3.1.用户管理 2.3.2.virtual host 3.SpringAMQP 3.1.导入Demo工程 3.2.快速入门 3.2.1.消…
阅读更多...
阻抗（Impedance）、容抗（Capacitive Reactance）、感抗（Inductive Reactance）

阻抗（Impedance）、容抗（Capacitive Reactance）、感抗（Inductive Reactance）

阻抗&#xff08;Impedance&#xff09;、容抗&#xff08;Capacitive Reactance&#xff09;、感抗&#xff08;Inductive Reactance&#xff09; 都是交流电路中描述电流和电压之间关系的参数&#xff0c;但它们的含义、单位和作用不同。下面是它们的定义和区别&#xff1a; …
阅读更多...
【网络协议】IPv4 地址分配 - 第二部分

【网络协议】IPv4 地址分配 - 第二部分

前言 在第 1 部分中&#xff0c;我们学习了 IPv4 地址的分配方式&#xff0c;了解了各种类型的 IPv4 地址&#xff0c;并进行了基础的子网划分&#xff08;Subnetting&#xff09;。在第 2 部分中&#xff0c;我们将继续学习子网划分&#xff0c;并引入一些新的概念。 【网络…
阅读更多...
Linux 正则表达式 ⑪

Linux 正则表达式 ⑪

正则表达式 1.Linux grep 命令 Linux grep (global regular expression) 命令用于查找文件里符合条件的字符串或正则表达式。 grep 指令用于查找内容包含指定的范本样式的文件&#xff0c;如果发现某文件的内容符合所指定的范本样式&#xff0c;预设 grep 指令会把含有范本样…
阅读更多...
二、模型训练与优化（1）：构建并训练模型

二、模型训练与优化（1）：构建并训练模型

目录 1. 安装 Anaconda&#xff08;推荐&#xff09; 步骤&#xff1a; 2. 创建并激活虚拟环境 步骤&#xff1a; 3. 安装必要的库 步骤&#xff1a; 4. 编写训练脚本 步骤&#xff1a; 5. 运行训练脚本 步骤&#xff1a; 总结&#xff1a; 在完成了准备工作的基础上…
阅读更多...
JavaEE初阶——计算机工作原理

JavaEE初阶——计算机工作原理

一、什么是JavaEE JavaEE&#xff08;Java Platform&#xff0c;Enterprise Edition&#xff09;是sun公司&#xff08;2009年4月20日甲骨文将其收购&#xff09;推出的企业级应用程序版本。这个版本以前称为 J2EE。能够帮助我们开发和部署可移植、健壮、可伸缩且安全的服务器…
阅读更多...
医学图像分析工具02：3D Slicer || 医学影像可视化与分析工具 支持第三方插件

医学图像分析工具02：3D Slicer || 医学影像可视化与分析工具 支持第三方插件

3D Slicer 是一款功能全面的开源医学影像分析软件&#xff0c;广泛应用于影像处理、三维建模、影像配准和手术规划等领域。它支持多种医学影像格式&#xff08;如 DICOM、NIfTI&#xff09;和丰富的插件扩展&#xff0c;是神经科学、放射学和生物医学研究中不可或缺的工具。 在…
阅读更多...
STM32裸机开发转FreeRTOS教程

STM32裸机开发转FreeRTOS教程

目录 1. 简介2. RTOS设置&#xff08;1&#xff09;分配内存&#xff08;2&#xff09;查看任务剩余空间&#xff08;3&#xff09;使用osDelay 3. 队列的使用&#xff08;1&#xff09;创建队列&#xff08;1&#xff09;直接传值和指针传值&#xff08;2&#xff09;发送/接收…
阅读更多...
使用高云小蜜蜂GW1N-2实现MIPI到LVDS（DVP）转换案例分享

使用高云小蜜蜂GW1N-2实现MIPI到LVDS（DVP）转换案例分享

作者&#xff1a;Hello&#xff0c;Panda 大家晚上好&#xff0c;熊猫君又来了。 今天要分享的是一个简单的MIPI到LVDS&#xff08;DVP&#xff09;接口转换的案例。目的就是要把低成本FPGA的应用潜力充分利用起来。 一、应用背景 这个案例的应用背景是&#xff1a;现在还在…
阅读更多...
Express 加 sqlite3 写一个简单博客

Express 加 sqlite3 写一个简单博客

例图&#xff1a; 搭建 命令&#xff1a; 前提已装好node.js 开始创建项目结构 npm init -y package.json:{"name": "ex01","version": "1.0.0","main": "index.js","scripts": {"test": &q…
阅读更多...
git撤回提交、删除远端某版本、合并指定版本的更改

git撤回提交、删除远端某版本、合并指定版本的更改

撤回提交 vscode的举例 一、只提交了还未推送的情况下 1.撤回最后一次提交&#xff0c;把最后一次提交的更改放到暂存区 git reset --soft HEAD~12.撤回最后一次提交&#xff0c;把最后一次提交的更改放到工作区 git reset --mixed HEAD~13.撤回最后一次提交&#xff0c;不…
阅读更多...
[Linux]redis5.0.x升级至7.x完整操作流程

[Linux]redis5.0.x升级至7.x完整操作流程

1. 从官网下载最新版redis&#xff1a; 官网地址&#xff1a;https://redis.io/download 注&#xff1a;下载需要的登录&#xff0c;如果选择使用github账号登录&#xff0c;那么需要提前在github账号中取消勾选“Keep my email addresses private”&#xff08;隐藏我的邮箱…
阅读更多...
xss-labs关卡记录15-20关

xss-labs关卡记录15-20关

十五关 随便传一个参数&#xff0c;然后右击查看源码发现&#xff0c;这里有一个陌生的东西&#xff0c;就是ng-include。这里就是&#xff1a; ng-include指令就是文件包涵的意思&#xff0c;用来包涵外部的html文件&#xff0c;如果包涵的内容是地址&#xff0c;需要加引号。…
阅读更多...
数据库回滚：大祸临头时

数据库回滚：大祸临头时

原文地址 什么是数据库回滚&#xff1f; 数据库技术中&#xff0c;回滚是通过撤销对数据库所做的一项或多项更改&#xff0c;将数据库返回到先前状态的操作。它是维护数据完整性和从错误中恢复的重要机制。 什么时候需要数据库回滚&#xff1f; 数据库回滚在以下几个场景中很…
阅读更多...
年会抽奖Html

年会抽奖Html

在这里插入图片描述 <!-- <video id"backgroundMusic" src"file:///D:/background.mp3" loop autoplay></video> --> <divstyle"width: 290px; height: 580px; margin-left: 20px; margin-top: 20px; background: url(D:/nianhu…
阅读更多...
基于FPGA的出租车里程时间计费器

基于FPGA的出租车里程时间计费器

基于FPGA的出租车里程时间计费器 功能描述一、系统框图二、verilog代码里程增加模块时间增加模块计算价格模块上板视频演示 总结 功能描述 &#xff08;1&#xff09;&#xff1b;里程计费功能&#xff1a;3公里以内起步价8元&#xff0c;超过3公里后每公里2元&#xff0c;其中…
阅读更多...
nginx-链路追踪(trace)实现

nginx-链路追踪(trace)实现

一. 需求场景&#xff1a; 在日常运维工作中&#xff0c;会经常遇到在有多重调用链的场景下&#xff0c;如请求遇到非致命error时&#xff0c;在各环节的定位会非常麻烦&#xff0c;举个例子&#xff1a;比如说&#xff0c;在一个有多重调用链的服务环境下&#xff0c;一个请求…
阅读更多...
c#使用SevenZipSharp实现压缩文件和目录

c#使用SevenZipSharp实现压缩文件和目录

封装了一个类&#xff0c;方便使用SevenZipSharp&#xff0c;支持加入进度显示事件。 双重加密压缩工具范例&#xff1a; using SevenZip; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Threading.…
阅读更多...
MySQL和Hive中的行转列、列转行

MySQL和Hive中的行转列、列转行

水善利万物而不争&#xff0c;处众人之所恶&#xff0c;故几于道&#x1f4a6; 文章目录 MySQL1.行转列2.列转行 Hive1.行转列2.列转行(1)侧窗(2)union MySQL 1.行转列 把多行转成列。直接group&#xff0c;sum(if()) 2.列转行 Hive 1.行转列 select name,sum(if(kmshuxu…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023