PostgreSQL 快速入门与实战

已于 2024-06-11 16:55:15 修改
阅读量1.3k 收藏 15
点赞数 29
文章标签： postgresql 数据库
于 2024-06-11 16:54:22 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_38701478/article/details/139511593
版权

1、概述

        前面2篇博客给大家详细的介绍了PostgreSQL的安装和配置，本篇文章就带着大家一起学习一下PostgreSQL的用法，主要内容包括 基本的数据库操作、用户管理、数据备份、SCHEMA(模式)以及和MySQL的区别。

2、数据库基本操作

PostgreSQL是严格遵守SQL规范的，在SQL的标准实现上比MySQL更加完善，所以日常使用的语法基本上都是一样的，这里重点给大家介绍和MySQL不同的操作

2.1、登录数据库

##  默认用户登录  
./psql

## 非默认用户
## psql -h ip地址 -U 用户名 -d 数据库 -p 端口
## 举例  jerry 用户 登录到 192.168.200.19  端口是 8800  数据库是 shop 
psql -h 192.168.200.19 -U jerry  -d shop -p 8800

需要注意的是 psql指令不带参数的时候 默认的用户和数据库都是postgres  

2.2、数据库操作

数据库相关操作如下

## 创建数据库
create database shop;

## 查看所有数据库
\l

## 切换当前数据库
\c shop

##  删除数据库
drop database shop

2.3、表操作

我们先准备以下脚本，然后在控制台执行

create table my_test(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  VARCHAR (25),
	PRIMARY KEY (ID)
);

我们使用 \d 可以查看当前用户下所有的表信息，这里大家可以使用idea工具连接一下

 我们可以看到这张表建到了 postgres 库的public 模式下了。

这里需要给大家说一下模式的概念，我们如果直接建表不指定Schema(模式名)，就默认会在public模式下， 后续章节会给大家详细的介绍。

我们已经用postgres用户在postgres库下面新建了一张my_test 的表，这张表归属在public模式下。那么我们如果要在shop库新建表需要怎么操作呢，首先肯定需要切数据库，具体操作如下

我们可以使用 \c 加数据库名  来切换数据库

 这个时候我们就将这张表创建在了shop库的public模式下了。我们可以使用 \d + 表名  来查看表结构

这里给大家分别介绍了数据库和表的几个基本的操作，其实 PostgreSQL大部分操作和 MySQL都是一样的，这里给大家介绍的都是和MySQL不同的几个操作，先来总结一下：

## 查看数据库
\l
## 切换数据库
\c dbName
## 查看表  和查看表结构
\d <tableName> 
## 退出
\q

3、用户管理

3.1、添加用户

       我们先启动pg的服务，然后切换到postgres用户，使用该用户登录到pg的服务端,相关命令如下

###启动服务
./startup-pg start

## 进入bin目录
cd /usr/local/postgresql-16.3/bin

## 切换用户
su postgres 

## 登录到pg
./psql

登陆成功后的界面如下所示： 

下面我们来创建一个用户名叫tom的用户，并设置他的密码是tom123

返回了一个CREATE ROLE 说明这个用户创建成功了，其实我们也可以使用 CREATE ROLE指令来创建一个用户，比如我们接下来继续创建一个名为jerry的用户

也是可以的，在pg中这两条指令都可以用来创建用户，他们的区别是 CREATE USER 命令创建的用户默认带有LOGIN属性，也就是具备登录的权限

3.2、角色管理

        我们上面创建了tom和jerry两个用户，我们可以使用 \du 指令来查看相关的用户信息

我们可以看到 jerry 这个用户后面有个属性说明  Cannot login。也就是说jerry这个用户不具备登陆的权限，我们必须要给 jerry 这个角色赋予登录的属性(权限)，修改角色权限的指令我们可以使用ALTER ROLE

可以看到 tom和jerry两个角色都具备登录的权限了。其实在PG中用户就等价于角色加上属性(权限)。我们也可以使用ALTER ROLE去修改一个已经存在的角色的属性。我们给jerry用户添加一个创建数据的属性(权限)

下面我们登录一下 jerry 用户，相关命令如下

 ./psql  -h 127.0.0.1 -U jerry  -d shop -p 5432

接着创建一个数据库 jerry_db 

我们可以看到这个数据库的Owner 是jerry。

3.3、 对等认证

之前在使用postgres用户登录的时候我们发现直接可以使用psql 就可以了，这是因为postgreSQL有一种认证方式 peer，它允许操作系统的同名用户直接使用psql指令就能登录，但是仅限于postgreSQL安装的主机上使用，具体描述大家可参见官方文档的第21章节PostgreSQL: Documentation: 16: 21.9. Peer Authentication

我们可以在pg中创建一个 trump  的用户，不设置密码

接着我们退出psql,在主机上也创建一个trump 用户 也不设置密码

我们在切换到trump用户登录的时候发现报错了，报错信息提示 数据库 trump 不存在，好吧 这里我们需要指定具体的数据库。之所以postgres用户没报错 是因为默认有一个postgres的数据库。

带上数据库，我们发现成功了，需要注意的是这种登录方式一般很少使用的，这里仅仅只是给大家介绍pg的一些特性，我们主要的登录方式还是通过用户名和密码登录的。

4、Schema(模式)

4.1、基本概念

 在postgreSQL中有一个比较重要的概念叫 schema,官方文档中第5章节有关于模式的具体说明https://www.postgresql.org/docs/16/ddl-schemas.html

这里我个人的理解是 模式相当于数据库里面的一个逻辑上的"子数据库",我们来看官方文档上的示意，

我们从上述内容中可以知道以下几点内容

1、一个数据库可以有 一个或者多个模式

2、模式中包含了 表、数据类型、函数以及操作符 

3、模式不是严格的隔离

4、一个用户可以访问它所连接的数据库下所有模式，但是需要具备对应的权限

关于这几点我们可以知道模式大概的样子应该是这样的

4.2、Schema相关操作

好了，相关的概念相信大家清楚了，下面我们来实操一下

## 创建数据库
create database tianlongbabu;
## 切换数据库
\c tianlongbabu;
## 创建模式
create schema gaibang;

create schema shaolin;

## 创建表
create table gaibang.user (
   id  INT              NOT NULL,
   user_name  VARCHAR (20)     NOT NULL,
   age INT              NOT NULL,
   face_value VARCHAR (25),
	PRIMARY KEY (id)
);

create table shaolin.user (
   id  INT              NOT NULL,
   user_name  VARCHAR (20)     NOT NULL,
   age INT              NOT NULL,
   face_value VARCHAR (25),
	PRIMARY KEY (id)
);

 上述命令我们新建了一个数据库 tianlongbabu 然后在这个数据库中新建了一个 gaibang 和shaolin的schema，并且分别在这两个schema里面创建了user 表。你可能会好奇同一个数据库里为什么可以创建同名表，到这里相信你也知道 肯定就是和 schema有关了。

好了 我们可以使用下面的语句查询一下schema信息 

SELECT nspname AS schema_name   
FROM pg_namespace  
WHERE nspname !~ '^pg_' AND nspname <> 'information_schema';

我们可以打开 idea 通过图形界面的方式查看一下

我们可以看到tianlongbabu库下面组织了4个schema，其中 shaolin和gaibang是我们自己创建的。

好了，关于schema 出现的原因 官方文档上给出了几点说明

1、允许多个用户使用一个数据库，各自使用单独的schema

2、将数据库对象组织成逻辑组，使其更易于管理。

3、第三方应用程序可以放在单独的模式中，这样它们就不会与其他对象的名称发生冲突

就类似上述案例中的gaibang和shaolin都有一个user表，我们可以创建2个用户 可以使用共同使用tianlongbabu这个数据库，两个用户使用各自的schema 这样就不回相互产生影响了。需要注意的是模式不能嵌套

 4.3、相关命令总结

## 创建模式
create schema schema_name

## 删除模式
drop schema schema_name

## 删除模式并且删除该模式下的数据对象
drop schema schema_name cascade

5、数据备份 

数据备份是一个关系型数据库必不可少的一个功能，postgreSQL 同样也具备快速数据备份的能力。我们首先在前面创建的表中添加几条数据

INSERT INTO gaibang."user" (id, user_name, age, face_value) VALUES (1, '乔峰', 35, '100');
INSERT INTO gaibang."user" (id, user_name, age, face_value) VALUES (2, '白世镜', 45, '46');
INSERT INTO gaibang."user" (id, user_name, age, face_value) VALUES (3, '吴长青', 59, '45');
INSERT INTO gaibang."user" (id, user_name, age, face_value) VALUES (4, '康敏', 28, '50');
INSERT INTO gaibang."user" (id, user_name, age, face_value) VALUES (5, '马大元', 40, '50');
INSERT INTO gaibang."user" (id, user_name, age, face_value) VALUES (6, '宋长老', 60, '48');


INSERT INTO shaolin."user" (id, user_name, age, face_value) VALUES (1, '玄苦大师', 70, '80');
INSERT INTO shaolin."user" (id, user_name, age, face_value) VALUES (2, '虚竹', 23, '99');
INSERT INTO shaolin."user" (id, user_name, age, face_value) VALUES (3, '玄难大师', 72, '88');
INSERT INTO shaolin."user" (id, user_name, age, face_value) VALUES (4, '玄悲大师', 71, '90');
INSERT INTO shaolin."user" (id, user_name, age, face_value) VALUES (5, '扫地僧', 90, '101');

添加上述数据后 我们可以查看一下，确认无误后 我们回到主机上，我们先到安装目录的bin目录下

## 来到目录下
cd /usr/local/postgresql-16.3/bin

## 切换用户
su postgres 

## 备份数据
./pg_dump tianlongbabu > tianlongbabu.sql
## 或者
./pg_dump tianlongbabu > tianlongbabu.bak

这样 就可能将数据库tianlongbabu整库备份了，其中sql文件和bak文件的区别是 sql是明文，bak是压缩二进制格式的文件。备份好了之后我们就可以删掉数据库

我们删掉tianlongbabu之后 我们再来恢复

## 创建数据库
create database tianlongbabu;

## 恢复数据
./psql tianlongbabu < tianlongbabu.sql

我们发现数据已经恢复了。需要注意的是命令一般只能单库备份，如果我们需要备份多个数据库我们可以使用 pg_dumpall  这个指令

6、PostgreSQL和MySQL

PostgreSQL和MySQL实现上存在很多差异，这里给大家介绍他们在组织表的方式上的差异。以下是关于这两种数据库管理系统组织表的方式的详细比对

1、PostgreSQL

  1. 堆表结构：
    • PostgreSQL使用堆表结构来存储数据。这意味着数据按照其插入的顺序进行存储，并不按照任何特定的顺序（如主键顺序）进行排序。
    • 每个文件由多个块组成，块是物理磁盘中的存储单位。块由四个主要部分组成：块头（PageHeaderData）、记录（包括Linp和Tuple）、空闲空间（Freespace）和特定数据（Special space）。
    • Linp：是ItemIdData类型，长度固定，在块中从前向后分配。每个ItemIdData都记录了一个偏移，用于指向Tuple。
    • Tuple：是记录的头信息，与记录本身一起构成完整的记录。记录本身长度不固定，在块中从后向前分配。
    • Freespace：位于Linp和Tuple之间，用于存储未分配的空间（空闲空间）。新插入页面中的元组即对应的项标识符将从这部分空间中来分配。
    • Special space：用于存放与索引方法相关的特定数据。但在普通表文件块中并没有使用，其内容被置为空。
  2. 表空间：
    • PostgreSQL支持表空间的概念，可以将表、索引、物化视图等物理对象进行分组并存储到不同的物理位置，从而提升I/O能力。

2、MySQL（InnoDB存储引擎）

  1. 索引组织表：
    • 在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。
    • 如果在创建表时没有显式定义主键，InnoDB存储引擎会按照一定规则选择或创建主键。
  2. 逻辑存储结构：
    • InnoDB存储引擎的逻辑存储结构包括表空间、段（segment）、区（extent）和页（page）。
    • 表空间：所有数据都被逻辑地存放在一个表空间中。
    • 段：表空间由各个段组成，常见的段有数据段、索引段、回滚段等。
    • 区：在任何情况下每个区的大小都为1MB。InnoDB存储引擎一次从磁盘申请4-5个区，以保证区中页的连续性。
    • 页：页是InnoDB磁盘管理的最小单位，默认每个页的大小为16KB。B+树索引本身并不能找到具体的一条记录，能找到的只是该记录所在的页。
  3. 存储引擎：
    • MySQL支持多种存储引擎，每种存储引擎都有其独特的组织表的方式。InnoDB是其中最常用的存储引擎之一，它采用索引组织表的方式。

总结来说，PostgreSQL和MySQL在组织表的方式上存在明显的差异。PostgreSQL使用堆表结构，并通过表空间的概念提供了灵活的物理存储管理。而MySQL（InnoDB存储引擎）则采用索引组织表的方式，将数据按照主键顺序进行存储，并通过逻辑存储结构（表空间、段、区和页）来管理数据。

相信大家已经了解到了这两款数据库底层组织表的方式以及使用的数据结构了，至于具体的选型大家可以根据自己的业务场景进行综合评估。本次教程就先到这里了，希望对大家有所帮助

代码洁癖症患者
关注
  • 29
    点赞
  • 踩
  • 15
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
【零基础】PostgreSQL从入门到精通
lsj960922的博客
02-09 5079
基本了解：PostgreSQL：是以加州大学伯克利分校计算机系开发的 POSTGRES，现在已经更名为PostgreSQL，版本 4.2为基础的对象关系型数据库管理系统（ORDBMS）。PostgreSQL支持大部分 SQL标准并且提供了许多其他现代特性：复杂查询、外键、触发器、视图、事务完整性、MVCC。同样，PostgreSQL 可以用许多方法扩展，比如， 通过增加新的数据类型、函数、操作符、...
PostgreSQL教程.pdf
05-30
这篇 PostgreSQL 教程提供 SQL 的基本和高级概念。此 PostgreSQL 系列教程是专为初学 者和专业人士编写提供的，需要读者有一些基本的数据库和编程基础。
PostgreSQL的基本使用
m0_72560900的博客
05-05 2006
在 PostgreSQL 中，Schema（模式）是数据库对象的集合，用于组织和命名数据库对象，如表、视图、索引、函数等。Schema 可以看作是一个命名空间，用于将数据库对象划分为逻辑上的分组，方便管理和控制访问权限。命名空间管理：Schema 提供了命名空间，可以将数据库对象按照业务逻辑或功能进行组织和分类，避免对象命名冲突，提高数据库的可维护性和可扩展性。对象隔离。
PostgreSQL新手入门_postgresql基础语法入门
最新发布
2401_84563465的博客
05-16 1044
举例来说，我的Linux系统用户名为ruanyf，且PostgreSQL数据库存在同名用户，则我以ruanyf身份登录Linux系统后，可以直接使用下面的命令登录数据库，且不需要密码。这时相当于系统用户postgres以同名数据库用户的身份，登录数据库，这是不用输入密码的。上面命令的参数含义如下：-U指定用户，-d指定数据库，-h指定服务器，-p指定端口。添加新用户和新数据库以后，就要以新用户的名义登录数据库，这时使用的是psql命令。然后，登录数据库控制台，设置dbuser用户的密码，完成后退出控制台。
PostgreSQL 入门教程
JAVA开发区的博客
11-13 1849
PostgreSQL是一个功能强大且灵活的开源关系型数据库管理系统。它具有许多优点，如可靠性、可扩展性、安全性和可定制性。本文提供了有关PostgreSQL的历史背景、概念、特点、用法、安装步骤和简单示例的详细介绍。通过深入了解PostgreSQL，您可以更好地利用它的优势来构建高性能和可靠的应用程序。当您熟悉了PostgreSQL的基本概念和用法后，还有许多更深入的特性和功能可以探索。
PostgreSQL快速入门
weixin_45636230的博客
03-07 1468
PostgreSQL PostgreSQL 是一个免费的对象-关系数据库服务器。 数据类型 数值类型 货币类型 字符类型 日期/时间类型 布尔类型 枚举类型 #例如： CREATE TYPE mood AS ENUM('sad','happy','ok'); #创建一周天数： CREATE TYPE week AS ENUM('Mon','Tue','Wed','Thu','Fri','Sat','Sun'); 几何类型 网络地址类型 位串类型 文本搜索类
PostgreSQL入门到精通——世界上功能最强大的开源数据库
weixin_34194359的博客
08-04 465
视频教程 《PostgreSQL从入门到精通》免费视频课程： PostgreSQL数据库从入门到精通（点击学习） 阿里云高级技术专家、PostgreSQL中国用户会创始人之一 周正中（德哥）主讲 关于PostgreSQL PostgreSQL被誉为“世界上功能最强大的开源数据库”，是以加州大学伯克利分校计算机系开发的POSTGRES 4.2为基础的对象关...
postgresql入门
chenduwei的博客
05-11 1121
pgsql入门
postgreSQL入门
cyyang123的博客
07-18 429
schema(模式)：就类比于一个文件系统的目录，一个数据库中包含一个或多个命名的模式，模式中又包含表，还包含其他的对象，包括数据类型、函数以及操作符等。同一个对象名可以在不同的模式中使用而不会导致冲突，例如schema和MySchema中包含名称为myTable的表。一个用户可以访问他所连接的数据库中的任意模式中的对象，只要他有权限。 使用schema的原因： 1、允许多个用户使用同一个数据...
Postgres 快速入门（一）
达文西的博客
01-16 2488
Postgres 快速入门 简介 postgres 是一款开源，免费的对象-关系数据库。 登陆 psql -U 用户名 -d 数据库名 -h 127.0.0.1 -p 5432 参数含义如下：-U指定用户，-d指定数据库，-h指定服务器，-p指定端口。 如果 postgres 中存在和当前系统用户同名的用户，可以不用输入-U 用户名，直接 psql 数据库名 如果要访问的数据库名和用户名一致，那么可以不用输入任何参数直接登陆： psql 命令行操作 \l or \list：列出当前的所有数据库
PostgreSQL从入门到精通视频教程（全）.zip
06-09
这个“PostgreSQL从入门到精通视频教程”旨在帮助初学者和有经验的开发者深入理解PostgreSQL的核心概念和高级特性。 **一、基础篇** 1. **安装与配置**：了解如何在不同的操作系统（如Windows、Linux、macOS）上...
PostgreSQL9.3 DBA最佳实战培训PPT 580页
11-27
本套580页的PostgreSQL 9.3 DBA最佳实战培训资料，旨在帮助新手从零基础快速掌握PostgreSQL的使用和运维技巧。 **1. PostgreSQL基础知识** - 数据库概念：理解关系型数据库的基本原理和术语，如表、索引、视图、...
Spring Boot入门与实战_springboot_spring_
10-02
**Spring Boot 入门与实战** Spring Boot 是一个由 Pivotal 团队开发的 Java 框架，旨在简化创建独立的、生产级别的基于 Spring 的应用。它通过提供默认配置来消除大量的样板代码，使得开发者可以更快地构建应用...
开源的数据库，PostgreSQL 基础入门实战
实验楼--动手做实验，轻松学IT！
08-28 360
PostgreSQL 简介与安装 实验介绍 大家好，本课程是关于 PostgreSQL 数据库的使用说明，细致讲解 PostgreSQL 的特性与使用方法，尽量做到描述朴实、深入浅出、示例充足、覆盖重点。 由于同类型的数据库 MySQL 的使用量远大于 PostgreSQL ，以至于大家都知道 MySQL 并且掌握它的基本用法。在我们的课程中，会出现一些二者的比较说明。 按照惯例，第一节实验是 PostgreSQL 的简介与安装。 知识点 PostgreSQL 简介 在 Ubuntu 系统中安装 Post
PostgreSQL基本操作
m0_37559973的博客
11-14 5902
PostgreSQL基本操作
PostgreSQL 基础（一）-- 新手教程
热门推荐
chinusyan的专栏
05-29 1万+
PostgreSQL 基础（一）-- 教程
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值