postgres的常用命令

最新推荐文章于 2025-05-15 17:47:16 发布

zhy-dream

最新推荐文章于 2025-05-15 17:47:16 发布

阅读量1k

点赞数 2

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/zhydream77/article/details/98337434

本文详细介绍了如何在PostgreSQL数据库中进行各种操作，包括查看、创建、删除数据库，创建、更新、删除表格，以及插入、查询、更新和删除数据的具体步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

用于查看已经存在的数据库：

使用 \c + 数据库名来进入数据库：

postgres-# \c test
You are now connected to database "test" as user "postgres".

退出数据库的命令:

test-# \q

在系统的命令行查看，我么可以在连接数据库后面添加数据库名来选择数据库：

[postgres@localhost bin]$ ./psql -h localhost -p 5432 -U postgres mydb

命令解析./psql连接数据库的命令 -h localhost链接本地 -p 5432 连接数据库的地址 -U 登录数据库使用的用户链接库内的表

删除数据库

postgres=# drop database test;
DROP DATABASE

系统命令窗口下删除数据库

进入到 PostgreSQL 的安装目录，并进入到 bin 目录，dropdb 名位于 PostgreSQL安装目录/bin 下，执行删除数据库的命令：

[postgres@localhost bin]$ ls
clusterdb initdb   pg_ctl   pg_resetwal pg_waldump
createdb pg_archivecleanup pg_dump   pg_restore postgres
createuser pg_basebackup pg_dumpall pg_rewind postmaster
dropdb   pgbench   pg_isready pg_test_fsync psql
dropuser pg_config   pg_receivewal pg_test_timing reindexdb
ecpg   pg_controldata pg_recvlogical pg_upgrade vacuumdb

[postgres@localhost bin]$ ./dropdb -h localhost -p 5432 -U postgres mydb

命令解析: ./dropdb 启动删除数据库的命令

创建表格

postgres=# create database zhu; ----创建数据库zhu
postgres=# \c zhu ----进入数据库zhu

zhu=# create table company( -----创建一张company表格
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE

zhu=# create table department( -----创建一张department表格
zhu(# ID INT PRIMARY KEY NOT NULL,
zhu(# DEPT CHAR(50) NOT NULL,
zhu(# EMP_ID INT NOT NULL
zhu(# );
CREATE TABLE

向表格中插入数据:

zhu=# insert into company(id,name,age,address,salary) values (1,'zhu',3,'sz',4000);
INSERT 0 1

zhu=# insert into company(id,name,age,address) values (2,'hai',3,'sz');
INSERT 0 1

zhu=# insert into company(id,name,age,address,salary) values (3,'yan',3,'lg',5000);
INSERT 0 1
zhu=# insert into company(id,name,age,address,salary) values (4,'cheng',5,'sh',6000);
INSERT 0 1
zhu=# insert into company(id,name,age,address,salary) values (5,'xi',6,'vj',7000);

表格查看数据:

1.查看表格中的所有数据:

zhu=# select * from company;
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
1 | zhu | 3 | sz | 4000
2 | hai | 3 | sz |
3 | yan | 3 | lg | 5000
4 | cheng | 5 | sh | 6000
5 | xi | 6 | vj | 7000
(5 rows)

2.查看表格中的部分数据

zhu=# select ID,NAME FROM company;
id | name
----+-------
1 | zhu
2 | hai
3 | yan
4 | cheng
5 | xi
(5 rows)

3.按条件去查找数据

zhu=# select * from company where id=1;
id | name | age | address | salary
----+------+-----+----------------------------------------------------+--------
1 | zhu | 3 | sz | 4000
(1 row)

zhu=# select * from company where age>3 and salary>3000;
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
4 | cheng | 5 | sh | 6000
5 | xi | 6 | vj | 7000
(2 rows)

zhu=# select * from company where age>3 or salary=4000;
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
1 | zhu | 3 | sz | 4000
4 | cheng | 5 | sh | 6000
5 | xi | 6 | vj | 7000
(3 rows)

更新表格的部分数据

zhu=# update company set salary=7000 where ID=3;
UPDATE 1

zhu=# update company set address='sz',salary=10000;
UPDATE 5

按调节删除数据

zhu=# delete from company company where id=2;