Ubuntu系统（命令行） | PostgreSQL学习

已于 2025-03-09 21:29:58 修改
阅读量1.1k 收藏 19
点赞数 22
分类专栏： 数据库原理 文章标签： ubuntu postgresql 学习 数据库 sql linux
于 2025-03-05 17:13:26 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/m0_74000326/article/details/146042978
版权

只是记录下自己在做练习，使用SQL语言的时候的心路历程和一些遇到的问题，供大家一起参考学习。
请添加图片描述

1.创建模式

要在PostgreSQL中创建一个名为"scs"的模式（schema），可以使用以下SQL命令：

CREATE SCHEMA scs;

您可以在PostgreSQL命令行中执行此命令。如果您已经连接到了PostgreSQL，只需输入上面的命令并按回车即可。

如果您希望指定该模式的拥有者，可以使用：

CREATE SCHEMA scs AUTHORIZATION username;

其中username是您想要指定的用户名。

创建完成后，可以使用\dn命令来查看所有的模式：

\dn

这会列出数据库中的所有模式，包括刚刚创建的"scs"模式。

创建模式（Schema）在PostgreSQL中有几个重要的用途：

  1. 逻辑分组：模式允许您将数据库对象（如表、视图、函数等）组织成逻辑分组，便于管理大型数据库。
  2. 命名空间：不同模式中可以有同名的表，避免命名冲突。
  3. 权限管理：可以在模式级别设置权限，简化安全管理。
  4. 项目隔离：在同一数据库中为不同项目或应用程序创建单独的模式，提供隔离。
  5. 第三方应用：许多应用程序使用自己的模式来避免与其他对象冲突。

关于\dn命令：

\dn是PostgreSQL命令行工具（psql）中的一个元命令，其中：

  • \ 表示这是一个psql元命令，而不是SQL语句
  • d 代表"describe"（描述）
  • n 代表"namespace"（命名空间，即模式）

所以\dn的全称可以理解为"describe namespaces"，用于列出所有可用的模式。

其他类似的元命令还有：

  • \dt - 列出表（tables）
  • \dv - 列出视图（views）
  • \df - 列出函数（functions）
  • \du - 列出用户（users）

如果之前没有明确需要创建模式，可以直接使用默认的public模式，所有用户都可以访问。

2.创建表/关系

2.1 Student Table

现在您已经成功创建了名为"S-C-S"的模式，要在这个模式中创建表，您需要明确指定模式名。正确的语法应该是：

CREATE TABLE "S-C-S".Student (
    Sno CHAR(9),
    Sname VARCHAR(20),
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
);

注意以下几点：

  1. 模式名称"S-C-S"需要用双引号括起来，因为它包含连字符
  2. 用点(.)连接模式名和表名
  3. 每条SQL语句结束后需要加分号(😉

或者，您可以先设置搜索路径，使"S-C-S"成为默认模式：

SET search_path TO "S-C-S", public;

然后就可以不指定模式名直接创建表：

CREATE TABLE Student (
    Sno CHAR(9),
    Sname VARCHAR(20),
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
);

使用哪种方式取决于您的偏好和需求。如果这是一次性操作，直接指定模式可能更简单；如果您将在这个模式中创建多个对象，设置搜索路径会更方便。

您已经成功在"S-C-S"模式中创建了Student表，但是当您使用\dt命令时没有显示任何表，这是因为\dt默认只显示当前搜索路径中的表。

要查看特定模式中的表，您需要使用带有模式参数的\dt命令：

\dt "S-C-S".*

或者查看所有模式中的表：

\dt *.*

请注意，PostgreSQL命令是区分大小写的，\dt是正确的命令（小写dt），而您之前尝试的\DT（大写DT）是无效的。

您也可以设置搜索路径，这样默认的\dt命令就会包含"S-C-S"模式的表：

SET search_path TO "S-C-S", public;
\dt

这样就能看到您刚刚创建的Student表了。

1.2 Course Table

在这里插入图片描述

您的SQL语句中有几个语法错误。以下是修正后的语句：

CREATE TABLE "S-C-S".Course (
    Cno CHAR(8) PRIMARY KEY,
    Cname VARCHAR(20),  -- 注意是VARCHAR不是VARYCHAR
    Ccredit SMALLINT,
    Cpno CHAR(8),
    FOREIGN KEY (Cpno) REFERENCES "S-C-S".Course(Cno)  -- 注意是FOREIGN不是FOREGIN
);

主要的错误有：

  1. 表名后面的括号中出现了"Course("，这是语法错误
  2. 数据类型"VARYCHAR"拼写错误，正确的是"VARCHAR"
  3. “FOREGIN KEY"拼写错误，正确的是"FOREIGN KEY”
  4. 外键引用需要包含模式名"S-C-S"，否则PostgreSQL会在默认模式中查找Course表

另外，请注意这个表有一个自引用的外键约束，即Cpno引用同一个表的Cno字段，通常用于表示课程的先修课程关系。这是合法的设计，但请确保这是您的意图。

再改

CREATE TABLE "S-C-S".Course(Cno CHAR(8),Cname VARCHAR(20),
Ccredit SMALLINT,Cpno CHAR(8),FOREIGN KEY(Cpno) 
REFERENCES Course(Cno));

错误：在创建Course表时，您的语句有一个问题：外键引用的表没有指定模式。因为Course表是在"S-C-S"模式中，所以外键引用也应该指向同一模式中的表。

正确的SQL语句应该是：

CREATE TABLE "S-C-S".Course(
    Cno CHAR(8) PRIMARY KEY,  -- 添加主键约束
    Cname VARCHAR(20),
    Ccredit SMALLINT,
    Cpno CHAR(8),
    FOREIGN KEY(Cpno) REFERENCES "S-C-S".Course(Cno)
);

注意两个修改：

  1. 添加了PRIMARY KEY约束到Cno列（被引用的列必须是主键或唯一键）
  2. 在REFERENCES子句中添加了模式名"S-C-S"

如果不指定模式名，PostgreSQL会在搜索路径中查找Course表，如果找不到则会出错。
在这里插入图片描述
成功创建了。

1.3 SC选课表的创建

CREATE TABLE “S-C-S”.SC(Sno CHAR(80

写到这忽然想起来自己的student表格貌似没指定主键，在这里就引用不了外码，被引用的外键一定是那一个表的主码。

因此现在就打算修改一下原来的Student表格的属性
Sno，就跳到后面去学习了一下修改语句。
在这里插入图片描述
在这里插入图片描述

例3.10就是我所需要的，我要修改约束条件，增加一个PRIMARY KEY。
在这里插入图片描述
但是提醒我已经指定过主键了，我寻思着我之前没指定啊，接着我想查询一下之前到底是否指定过主键，用到另一个命令\d “S-C-S”.Student。（此处还是要注意定位到相应的模式下）
在这里插入图片描述
这里显示，我的确已经指定了主键。在这里我发现我对各种命令还不够熟悉，因此让Claude整理了一份，供大家参考。

以下是PostgreSQL命令行(psql)中常用的元命令及其含义：

  1. \d [模式名.]表名 - describe (描述表结构)

    \d "S-C-S".Student

    显示指定表的详细结构，包括列、数据类型和约束

  2. \dt [模式名.]* - describe tables (描述所有表)

    \dt "S-C-S".*

    列出指定模式中的所有表

  3. \dn - describe namespaces (描述命名空间/模式)

    \dn

    列出所有模式

  4. \du - describe users (描述用户)

    \du

    列出所有数据库用户和角色

  5. \l - list (列出数据库)

    \l

    列出所有数据库

  6. \c 数据库名 - connect (连接到数据库)

    \c university

    切换到指定的数据库

  7. \s - show history (显示历史)

    \s

    显示命令历史记录

  8. \i 文件名 - include/input (包含/输入文件)

    \i script.sql

    执行指定SQL脚本文件中的命令

  9. ? - help (帮助)

    \?

    显示所有psql命令的帮助信息

  10. \h [SQL命令] - help SQL (SQL帮助)

    \h CREATE TABLE

    显示特定SQL命令的语法帮助

  11. \q - quit (退出)

    \q

    退出psql程序

  12. \df - describe functions (描述函数)

    \df

    列出所有函数

  13. \dv - describe views (描述视图)

    \dv

    列出所有视图

  14. \x - expanded display (扩展显示)

    \x

    切换扩展显示模式，使结果垂直显示而非表格形式

  15. \timing - timing (计时)

    \timing

    切换SQL执行时间显示

这些命令在PostgreSQL命令行中很有用，可以帮助您浏览和管理数据库。

在这里插入图片描述
好的，现在回到主线任务，继续创建我们的SC表格。
这个错误很奇怪，因为您的SQL语法看起来是正确的。问题可能出在PostgreSQL解析含有特殊字符的模式名时。让我们尝试一个替代方案：

  1. 使用转义字符或不同的引号风格：
CREATE TABLE "S-C-S".SC (
  Sno CHAR(8),
  Cno CHAR(8),
  Grade SMALLINT,
  PRIMARY KEY(Sno,Cno),
  FOREIGN KEY(Sno) REFERENCES "S-C-S".Student(Sno),
  FOREIGN KEY(Cno) REFERENCES "S-C-S".Course(Cno)
);

如果这个方案仍然无效，可以尝试以下其他解决方法：

  1. 先设置搜索路径，然后不使用模式名创建表：
SET search_path TO "S-C-S", public;
CREATE TABLE SC (
  Sno CHAR(8),
  Cno CHAR(8),
  Grade SMALLINT,
  PRIMARY KEY(Sno,Cno),
  FOREIGN KEY(Sno) REFERENCES Student(Sno),
  FOREIGN KEY(Cno) REFERENCES Course(Cno)
);
  1. 创建一个不带连字符的模式，然后使用该模式：
CREATE SCHEMA scs;
CREATE TABLE scs.SC (
  Sno CHAR(8),
  Cno CHAR(8),
  Grade SMALLINT,
  PRIMARY KEY(Sno,Cno),
  FOREIGN KEY(Sno) REFERENCES "S-C-S".Student(Sno),
  FOREIGN KEY(Cno) REFERENCES "S-C-S".Course(Cno)
);

错误消息中指出问题出在"-"字符附近，这表明PostgreSQL可能在处理带连字符的模式名时遇到了问题。

此时三个表就已经全定义好了。

2. 在Student表中增添一个字段address

在这里插入图片描述
测试一下
在这里插入图片描述
已经修改成功了。

3.修改字段长度

请添加图片描述

在这里插入图片描述
我的理所当然：ALTER TABLE "S-C-S".Student ALTER COLUMN Address VARCHAR(20) TO VARCHAR(30);
在这里插入图片描述
但是貌似这个也不对，需要加TYPE
在这里插入图片描述

4. 删除字段在这里插入图片描述

ALTER TABLE "S-C-S".Student DROP COLUMN Address;

在这里插入图片描述
可以看到已经成功删去

4. 创建索引

先简单介绍一下索引

假设我们有一个"员工"表（Employees），包含以下字段：

  • id（员工编号）
  • name（姓名）
  • department（部门）
  • salary（薪资）

这个表中有10万名员工的记录。

没有索引的查询

假设我们想查找名为"张伟"的员工：

SELECT * FROM Employees WHERE name = '张伟';

没有索引的情况下，数据库会执行"全表扫描"，即：

  1. 检查第1行的name是否为"张伟"
  2. 检查第2行的name是否为"张伟"
  3. …以此类推，直到检查完所有10万行

这可能需要相当长的时间，特别是表很大的情况下。

有索引的查询

现在，我们在name列上创建一个索引：

CREATE INDEX idx_employee_name ON Employees(name);

创建索引后，数据库会建立一个类似于电话簿的结构（通常是B树或B+树），其中按字母顺序排列了所有员工的姓名，并记录了每个姓名对应的行位置。

再次执行同样的查询：

SELECT * FROM Employees WHERE name = '张伟';

这次数据库会：

  1. 通过索引直接找到所有name为"张伟"的记录位置
  2. 只读取这些特定的行

如果表中有5个叫"张伟"的员工，数据库只需要读取5行数据，而不是全部10万行，查询速度可能提高几百甚至几千倍。

索引的实际效果

在实际数据库系统中，可以通过"执行计划"查看索引的效果：

没有索引时的执行计划可能显示：

Table: Employees
Type: ALL (全表扫描)
Rows examined: 100,000

有索引时的执行计划可能显示：

Table: Employees
Type: ref (索引查找)
Possible keys: idx_employee_name
Key: idx_employee_name
Rows examined: 5

通过这个例子，你可以看到索引如何显著减少数据库需要检查的行数，从而提高查询性能。

4.1 创建唯一索引

CREATE UNIQUE INDEX UISNAME ON “S-C-S”.Student(Sno);

在这里插入图片描述
可以看到创建了一个B树的索引

4.2 删除唯一索引，创建cluster索引

在这里插入图片描述

DROP INDEX "S-C-S".UISNAME;

在这里插入图片描述
同样要加模式。

接下来创建聚类索引：
在这里插入图片描述
遇到的错误是因为PostgreSQL中创建聚集索引的语法与你使用的不同。在PostgreSQL中，创建聚集索引的正确语法是先创建一个普通索引，然后使用CLUSTER命令来指定该索引作为聚集索引。

正确的做法应该是这样：

  1. 先创建一个普通索引（如果还没有合适的索引）：
CREATE INDEX CISNAME ON "S-C-S".Student(Sno);  -- 假设你想按Sno列聚集
  1. 然后使用CLUSTER命令指定该索引作为表的聚集索引：
CLUSTER "S-C-S".Student USING CISNAME;

或者，如果你已经有了想要用作聚集索引的现有索引（比如"uisname"），可以直接：

CLUSTER "S-C-S".Student USING uisname;

与其他一些数据库系统（如SQL Server）不同，PostgreSQL没有直接的"CREATE CLUSTER INDEX"语法，而是采用上述两步方法来实现聚集索引的功能。

执行CLUSTER命令后，表数据会被物理重排以匹配索引顺序，但请注意，这是一次性操作，PostgreSQL不会自动维护这种物理排序。如果需要保持聚集状态，你需要定期重新执行CLUSTER命令。

5.SC TABLE 创建一个唯一索引

CREATE UNIQUE INDEX SCNO ON "S-C-S".Sc(Sno,Cno);

在这里插入图片描述
可以看到已经成功创建

6. 删除表

在这里插入图片描述
我们可以分别试试

6.1 RESTRICT

DROP TABLE "S-C-S".Student RESTRICT;

在这里插入图片描述
存在依赖关系，也就是别的表引用的这个表的主码，因此不能删除。

6.2 CASCADE

DROP TABLE "S-C-S".Student CASCADE;

在这里插入图片描述
用命令dt，输出结果可以看到学生表成功被删除。

【PostgreSQL】 PostgreSQL 常用命令行
九师兄
11-05 304
dt 列举表，相当于show tables\d tblname 查看表结构，相当于desc tblname,show columns from tbname一般性\c[onnect] [资料库名称|- 用户名称|- 主机|- 埠号|-]连线到新的资料库 (目前是 “test”)\cd [目录] 改变目前的工作目录\copyright 显示 PostgreSQL 的使用和发行条款\encoding [字元编码名称]显示或设定用户端字元编码。
Ubuntu安装PostgreSQL
unhurried人生——冕临的博客
07-04 983
原因是配置PostgreSQL编译选项时，系统找不到zlib是一个用于数据压缩的库，许多软件（包括PostgreSQL）都依赖它进行压缩和解压缩操作，如果PostgreSQL需要提供压缩和解压缩功能，就需要安装zlib库。如果不需要readline支持，或者只是想要快速安装PostgreSQL而不处理readline的依赖问题，可以选择禁用readline支持。如果不需要ICU支持，或者只是想要快速安装PostgreSQL而不处理ICU的依赖问题，可以选择禁用ICU支持。看到activate即启动成功。
postgresql命令行
javadatong的博客
05-30 878
连接数据库, 默认的用户和数据库是postgres psql -U user -d dbname \c dbname 切换数据库,相当于mysql的use dbname \l 列举数据库，相当于mysql的show databases \dt 列举表，相当于show tables \d tblname 查看表结构，相当于desc tblname,show columns ...
ubuntu 安装 postgresql
最新发布
开发小能手-roy的博客
03-29 1670
ubuntu 安装 postgresql
postgresql 命令行操作
myli92的博客
11-06 3308
【代码】postgresql 命令行操作。
Ubuntu系统——操作PostgreSQL数据库
一坨小橙子的博客
03-24 3425
Ubuntu安装及操作PostgreSQL数据库
postgresql-11 Ubuntu22.04离线安装包
10-19
PostgreSQL是一种开源的对象关系型数据库管理系统（ORDBMS），它以其强大的功能、高度的稳定性以及对ACID（原子性、一致性、隔离性、持久性）事务的支持而受到广大开发者的青睐。在Ubuntu 22.04上离线安装PostgreSQL...
ubuntu18下安装postgresql
weixin_37836199的博客
08-26 752
ubuntu下安装postgresql 此文介绍在ubuntu18下安装较新版本的postgresql服务，及配置远程连接。 配置软件源 先检查可安装的版本 apt show postgresql ubuntu默认软件源里的postgresql会比较老一些，如果默认源中的版本合适，则可跳过此步。 # 获取 GPG 密钥 wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - # 添加
Ubuntu18.04安装 postgreSQL10.6
McCedar的博客
03-26 568
一、安装 1、第一步更新系统并安装最新版postgreSQL sudo apt-get update sudo apt-get install postgresql 这里安装时没有指明安装版本，系统默认安装最新版本psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)) 安装完成后，默认会： （1）创建名为"postgres"的Linux用户 （2）创建名为...
postgresql ubantu离线安装版
03-26
PostgreSQL是一种开源的对象关系型数据库管理系统（ORDBMS），它在Ubuntu操作系统上广泛使用，尤其在需要稳定性和高性能的环境中。Ubuntu离线安装版的PostgreSQL适用于那些没有互联网连接或者网络带宽有限的环境，...
ubuntu postgresql 安装
xie__jin__cheng的博客
05-10 959
添加新用户使用createuser命令来添加新的PostgreSQL用户。在提示时，输入新用户的密码。2.创建新数据库在psql命令行中，你可以使用SQL命令来创建新数据库。退出psql命令行输入\q并回车来退出psql命令行。
PostgreSQL命令大全
qqrrjj2011的博客
01-20 5549
以上是PostgreSQL数据库常用命令及使用案例的一部分，更多详细内容请参阅官方文档。对于大型应用和企业级环境，可能还需要涉及更复杂的集群配置、高可用性解决方案、性能调优等方面的知识。请参考官方文档以获取完整信息：https://www.postgresql.org/docs/current/index.html。示例：以用户postgres身份连接到名为mydb的数据库。
postgresql命令行 && Sequlize基本操作指令
Go_going_的博客
07-01 3555
以下概念来自psql中文文档PostgreSQL是以加州大学伯克利分校计算机系开发的POSTGRES， 版本 4.2为基础的对象关系型数据库管理系统（ORDBMS）。POSTGRES 领先的许多概念在很久以后才出现在一些商业数据库系统中。psql是一个PostgreSQL的基于终端的前端。它让你能交互式地键入查询，把它们发送给PostgreSQL，并且查看查询结果。或者，输入可以来自于一个文件或者命令行参数。此外，psql还提供一些元命令和多种类似 shell 的特性来为编写脚本和自动化多种任务提供便利。
ubuntu安装postgreSQL
qq_36973384的博客
09-19 2462
【代码】ubuntu安装postgreSQL。
在Ubuntu上安装PostgreSQL
hnxxlstc的博客
08-20 1909
本文介绍了在Ubuntu中安装和卸载PostgreSQL，其中Ubuntu版本为 22.04.4 LTS。
PostgreSQL 命令行工具介绍
分享既学习
09-18 2572
psql 是 PostgreSQL 自带的命令行交互客户端工具，类似于 MySQL 的不过相当于 MySQL 的命令行工具 psql 功能更丰富些，例如单击 tab 自动补全。本篇文章介绍 psql 的使用及常用命令。
PostgreSQL命令行常用的命令
Fern技术屋
03-06 667
登录数据库：psql -h localhost -U username -d dbname \c dbname 切换数据库,相当于mysql的use dbname \l 列举数据库，相当于mysql的show databases \dt 列举表，相当于show tables \d tblname 查看表结构   \cd [目录] 改变目前的工作目录 \copyright 显示 Po
Ubuntu 安装PostgreSQL
专注基础架构领域
03-03 5973
最近项目需要再Ubuntu中安装PostgreSQL-11.4，特此记录便于日后查阅。 1、下载 $ wget https://ftp.postgresql.org/pub/source/v11.4/postgresql-11.4.tar.gz $ tar -zxf postgresql-11.4.tar.gz 注：如果下载较慢可以直接拿本文附件中的安装包。 2、安装 安装所需要的依赖...
Linux与Ubuntu系统学习笔记精要
学习Linux和Ubuntu对于IT专业人员来说至关重要，无论是为了系统管理、服务器部署、软件开发还是其他IT相关工作。以下是从给定文件信息中提炼的知识点： 1. Linux操作系统核心概念 Linux操作系统基于UNIX哲学，它是...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值