PostgreSQL数据库

一、PostgreSQL数据库简介

PostgreSQL 是一个免费的对象-关系数据库管理系统(ORDBMS)。

二、psql

psql是PostgreSQL的一个命令行交互式客户端工具，它具有非常丰富的功能，类似于Oracle的命令行工具sqlplus。

psql命令：

1、查看psql命令列表

\?

2、数据库相关操作

（1）查看有哪些数据库（postgres是该数据库的默认用户）

        切换su - postgres用户(直接在终端输入psql也可实现)，在postgres用户下输入\l     

 （2）连接指定服务器上的数据库

         psql -h IP地址或数据库名 -p 端口 -U 用户名 -d 数据库名

（3）创建名字为mydb的数据库

         CREATE DATABASE mydb

（4）查看所有数据库

         \l

（5）退出数据库

        \q

（6）切换到名字为mydb的数据库

         \c mydb

（7）删除mybd数据库

drop database mybd

（8）备份与恢复PostgreSQL数据库

         备份格式：*.bak、*.sql、*.tar

• 将数据库mydb备份到当前目录（.bak或.sql格式）

        pd_dump mydb > mydb.bak

• 将数据库mydb备份到/tmp目录（.bak或.sql格式）

        pd_dump -U 用户名 -f /tmp/mydb.bak mydb

• 将数据库mydb备份为指定格式

       pd_dump -U 用户名 -F t -f /tmp/mydb.tar mydb

• 恢复*.bak或*.sql的备份数据库，以下命令代表将mydb.sql的备份数据库恢复到mydb数据库

        psql -U 用户名 -f /tmp/mydb.sql mydb

• 恢复自定义格式的备份数据库

       pg_restore -U 用户名 -d mydb /tmp/mydb.tar

       备注：在恢复备份数据库之前需要新建空白数据库

3、数据库的表的相关操作

（1）查看数据库中的表

         \d

（2）查看表的具体信息

\d 表名

 

（3）创建表

命令：CREATE TABLE 表名(字段1 字段1的数据类型,字段2 字段2的数据类型,字段3 字段3的数据类型,···);

（4） 删除某个表

drop table 表名;

（5）在表中每次插入一条数据

命令：INSERT INTO 表名 (字段1,字段2,字段3,···) VALUES (字段1的值,字段2 的值,字段3的值,···);

（6）查询表中的所有数据

SELECT *  FROM 表名;

 （7）查询表中特定条件的数据记录

SELECT * FROM 表名 WHERE 字段名 = 字段值;

(8)统计表中的所有记录总数

SELECT COUNT(*) AS "RECORDS" FROM 表名;

(9)更新表中某个ID的某个字段的值

UPDATE 表名 SET 字段名=字段更新的值 WHERE ID =ID 号;

(10) 同时更新表中某个ID的多个字段的值

UPDATE 表名 SET 字段1=字段1更新的值,字段2=字段2更新的值 WHERE ID =ID号;

(11)同时更行表中多个字段的值（不区分ID）

UPDATE 表名 SET 字段1=字段1更新的值,字段2=字段2更新的值;

(12)按特定条件删除表中的数据

DELETE FROM 表名 WHERE 字段名=字段值;

(13)查看当前时间

SELECT CURRENT_TIMESTAMP;

（14）找出表中某个字段不为空的记录

SELECT * FROM 表名 WHERE 字段 IS NOT NULL;

（15）SELECT * FROM 表名 WHERE 字段 IS NULL;

 （16）从表的某行开始获取N条数据，一般通过该命令实现分页功能

以下语句表示：从表t_host的0行开始获取20条数据。

SELECT "t1"."id", "t1"."create_at", "t1"."update_at", "t1"."sn", "t1"."ip", "t1"."ipmi", "t1"."ipmi_user", "t1"."ipmi_password", "t1"."card_type", "t1"."power_status", "t1"."stage", "t1"."task_id", "t1"."create_by", "t1"."update_by" FROM "t_host" AS "t1" LIMIT 20 OFFSET 0

4、数据库的用户相关操作

（1）新建用户，以下命令代表新建一个名称为tuling密码为tuling的用户

         create user tuling with password "tuling";

（2）查看数据库中的用户

         \du

（3）修改配置文件（如果不修改配置文件，则无法使用新用户登陆数据库）

         vim /var/lib/pgsql/14/data/pg_hba.conf（将原文件的peer修改为下图的md5）

         

         重启服务postgresql服务

         systemctl restart postgresql-14

（4）用某个用户登陆数据库

         psql -U 用户名 -d 数据库名

（5）给用户tuling授权，以下命令代表给tuling用户使用mydb数据库授权，此时并无读写权限

         grant all privileges on database mydb to tuling;

（6）给用户tuling授权数据库的所有表的操作权限

        grant all privileges on all tables in schema public to tuling;

       备注：该命令必须切换到所授权的数据库中操作。切换数据库命令:\c 数据库名。

（7）移除用户tuling在数据库mydb的所有权限

         revoke all privileges on database mydb from tuling;

（8）移除用户tuling在数据库中所有表的权限

         revoke all privileges on all tables in schema public from tuling;

（9）删除用户

         drop user 用户名

 

三、数据库中运算符相关操作

• 算术运算符

• 比较运算符 

• 逻辑运算符  
•  位运算符

四、使用python调用psql语句

python包：psycopg2

（1）连接数据库

（2）查看当前数据库中的表

（3）创建表

 （4）删除某个表

（5）在表中每次插入一条数据

（6）查询表中的所有数据 

（7）查询表中特定条件的数据记录

（8）统计表中所有记录总数

（9）查询表中某个字段不为空的记录

（10）查询表中某个字段为空的记录

（11）更新表中某个ID的某个字段的值 

（12）同时更新表中某个ID的多个字段的值

（13）同时更行表中多个字段的值（不区分ID）

（14）按特定条件删除表中的数据

（15）查看当前时间

 

 五、使用orm调用psql语句 

orm简介：即将SQL语句使用类来实现。

数据库的表中的一行数据代表一个model类的实例。

python包：peewee

安装：pip install peewee

           pip install pymysql

（1）连接数据库

备注：db.connect() 只有在用到的时候才需要打开连接

（2）查看当前数据库中的表

（3）查看某个表的具体信息

 

（4） 创建表(使用类来实现数据库中的表)

 （5）删除某个表

（6） 在表中每次插入一条数据

（7） 在表中一次插入多条数据

（8）查询表中所有数据

将以上数据库中查询到的数据使用字典的数据类型展示：

print(list(notes.dicts()))

（9） 查询表中特定条件的数据记录

（10） 统计表中的所有记录总数

（11） 更新表中某个ID的某个字段的值

（12） 同时更新表中某个ID的多个字段的值

（13） 同时更行表中多个字段的值（不区分ID）

（14） 按特定条件删除表中的数据

（15）关闭数据库连接

（16） 使用sql()方法显示生成的 SQL 语句

（17） 使用order_by()对检索到的实例进行排序

（18） 获取表中的某几行数据

 参考文档：python | Peewee 教程 | 犀牛的博客