PostgreSQL数据库，pg数据库的一些操作命令

2023-10-07 阅读：1655 分类：后端标签： PgSQL

本文记录 pg数据库的一些操作，整理一些日常PostgreSQL数据库操作，供新手使用和参考。

1、登录PG数据库

psql -U 用户名 -h 127.0.0.1 -p 5432 -d 数据库名

-U 登录的用户名

-h 连接的主机（默认127.0.0.1，可替换成远程主机）

-p 端口（默认5432）

-d 数据库名

2、数据库操作

2.1、查看所有数据库：\l

2.2、进入某一数据库：\c 数据库名字

2.3、查看数据库表：\dt

2.4、列出某一张表格的结构：\d 表名

2.5、查看某个表的所有数据：select * from 表名;（后面别忘分号）

2.6、导出数据库：/usr/pgsql-12/bin/pg_dump -U 用户名数据库名 > /home/username/db.sql

2.7、导入数据库：psql -U 用户名数据库名(缺省时同用户名) < /home/username/db.sql

2.8、退出数据库：ctrl + z 或者 \q

3、语句操作

3.1. 定义数据库结构：可以通过 CREATE TABLE 语句来创建表格、索引和视图等数据库对象。

3.2. 查询数据：可以使用 SELECT 语句来查询表格中的数据，并可以使用各种聚合函数、排序和分组等操作来对数据进行统计和分析。

3.3. 更新数据：可以使用 INSERT、UPDATE 和 DELETE 语句来插入、更新和删除表格中的数据。

3.4. 控制权限：可以使用 GRANT 和 REVOKE 语句来授权和回收权限，以保护数据的安全性。

3.5. 事务处理：可以使用 EGIN、COMMIT 和 ROLLBACK 语句来实现事务的开始、提交和回滚等操作。

创建用户：

create user test_user;

创建数据库:

create database testdb;

对应的删除数据库：

drop database testdb;

进入到数据库中：

\c testdb

建表：

create table mytable(id int, name varchar(255));

查看表：

\d mytable;

删除表格：

drop table mytable;

#如果要删除多个表

drop table table1, table2;

4、增删改查

增：Insert

为数据库新增数据，这边postgresql和其他的数据库差别不大，都是用的insert into

#先创建一个表
CREATE TABLE EMPLOYER(
  ID INT PRIMARY KEY   NOT NULL,
  NAME      TEXT  NOT NULL,
  AGE      INT   NOT NULL,
  ADDRESS    CHAR(50),
  SALARY     REAL,
  JOIN_DATE   DATE
);

#插入数据

INSERT INTO EMPLOYER(ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Bob', 32, 'Beijing', 20000.00,'2001-01-01');

#设置一些Default属性

INSERT INTO EMPLOYER(ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (2, 'CC', 23, 'Shanghai', 20000.00, DEFAULT );

#插入多行

INSERT INTO EMPLOYER(ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Dod', 25, 'Guangzhou ', 65000.00, '2007-02-03' ), (4, 'Eoe', 27, 'Shenzhen', 85000.00, '2011-11-11');

我们可以用Select指令来查看一下刚才插入的数据：

testdb=# select * from EMPLOYER;
 id | name | age |           address            | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
 1 | Bob | 32 | Beijing                      | 20000 | 2001-01-01
 2 | CC  | 23 | Shanghai                      | 20000 |
 3 | Dod | 25 | Guangzhou                     | 65000 | 2007-02-03
 4 | Eoe | 27 | Shenzhen                      | 85000 | 2011-11-11
(4 rows)

这时候可以注意，postgresql对于大小写是不敏感的，所以后续会使用小写。

删：Delete

#根据条件筛选，删除某一列

testdb=# delete from employer where id = 1;

DELETE 1

testdb=# select * from employer;
 id | name | age |           address            | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
 2 | CC  | 23 | BeiJing                      | 100000 |
 4 | Eoe | 27 | BeiJing                      | 100000 | 2011-11-11
 3 | Dod | 25 | BeiJing                      | 100000 | 2007-02-03
(3 rows)

这时候可以看待id为1的那一行已经被删除了。

#删除整张表

Delect from company

改：Update

修改的方法是通过Update实现的，用法也很简单，如下

#通过where选择某一行，更新某一项

testdb=# update employer set salary = 100 where id = 3;

UPDATE 1

testdb=# select * from employer;
 id | name | age |           address            | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
 1 | Bob | 32 | Beijing                      | 20000 | 2001-01-01
 2 | CC  | 23 | Shanghai                      | 20000 |
 4 | Eoe | 27 | Shenzhen                      | 85000 | 2011-11-11
 3 | Dod | 25 | Guangzhou                     |  100 | 2007-02-03
(4 rows)

#更新很多项，并且非空项全部更新（因为没有加上筛选条件），这边注意，CC原本就没有join_date，所以这边没更新

testdb=# update employer set address = 'BeiJing', salary=100000;

UPDATE 4

testdb=# select * from employer;
 id | name | age |           address            | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
 1 | Bob | 32 | BeiJing                      | 100000 | 2001-01-01
 2 | CC  | 23 | BeiJing                      | 100000 |
 4 | Eoe | 27 | BeiJing                      | 100000 | 2011-11-11
 3 | Dod | 25 | BeiJing                      | 100000 | 2007-02-03
(4 rows)

查：Select

查询大多是通过Select加各种条件实现的，这边给一些简单的Demo

#如果要全选，就select *，但是如果只想选择对应的列就得都列出来

select ID, NAME, ADDRESS from EMPLOYER;

testdb=# select ID, NAME, ADDRESS from EMPLOYER;
 id | name |           address
----+------+----------------------------------------------------
 1 | Bob | Beijing
 2 | CC  | Shanghai
 3 | Dod | Guangzhou
 4 | Eoe | Shenzhen
(4 rows)

# Where条件

# 这边可以用算数运算符进行判定

testdb=# select * from EMPLOYER where SALARY > 20000;
 id | name | age |           address            | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
 3 | Dod | 25 | Guangzhou                     | 65000 | 2007-02-03
 4 | Eoe | 27 | Shenzhen                      | 85000 | 2011-11-11
(2 rows)

#也可以用And or 或者not作为逻辑运算符号，比如

testdb=# select * from EMPLOYER where SALARY > 20000 and SALARY < 80000;
 id | name | age |           address            | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
 3 | Dod | 25 | Guangzhou                     | 65000 | 2007-02-03
(1 row)

#也可以嵌套查询

testdb=# select * from employer where age > (select age from employer where salary > 65000);
 id | name | age |           address            | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
 1 | Bob | 32 | Beijing                      | 20000 | 2001-01-01
(1 row)