PostgreSQL数据库基本使用及Psycopg基本使用

一、PostgreSQL是什么？

　　PostgreSQL是一个功能强大的开源对象关系数据库管理系统(ORDBMS)。用于安全地存储数据; 支持最佳做法，并允许在处理请求时检索它们。

PostgreSQL(也称为Post-gress-Q-L)由PostgreSQL全球开发集团(全球志愿者团队)开发。它不受任何公司或其他私人实体控制。

它是开源的，其源代码是免费提供的。PostgreSQL是跨平台的，可以在许多操作系统上运行，如Linux，OS X和Microsoft Windows等。

PostgreSQL特点

    跨平台
    支持文本、图像、视频、声音等
    并提供C/C++、Java、Perl、Python、Ruby放数据库连接(ODBC)的编程接口。
    支持SQL的许多功能，例如复杂的SQL查询，子查询，外键，触发器，视图，视图，多进程并发控制(MVCC)、异步复制。
    在PostgreSQL中，表可以设置为从“父”表继承其特征。

PostgreSQL是第一个实现多版本并发控制（MVCC）功能的数据库管理系统，甚至在Oracle之前。MVCC功能在Oracle中称为快照隔离。

PostgreSQL是一个通用的对象 - 关系数据库管理系统。它允许您添加使用不同编程语言（如C / C ++，Java等）开发的自定义函数。

PostgreSQL旨在实现可扩展性。在PostgreSQL中，您可以定义自己的数据类型，索引类型，函数语言等。如果您不喜欢系统的任何部分，您可以随时开发自定义插件以增强它以满足您的要求，例如，添加新的优化。

如果您需要任何支持，可以使用活跃的社区来提供帮助。您可以随时找到PostgreSQL社区的答案，以了解使用PostgreSQL时可能遇到的问题。许多公司在您需要时提供商业支持服务。

View Code

PostgreSQL工具

psql：命令行工具，也是管理PostgreSQL的主要工具
pgAdmin:是PostgreSQL免费开源的图形化界面管理工具

二、安装PostgreSQL

可以参考postgresql官网安装教程:https://www.postgresql.org/download/linux/redhat/

1、Linux安装（centos）

安装参考博客：https://www.jianshu.com/p/cc5bc168e5e1

添加RPM：

-- centos 6 安装 postgresql 10

yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-6-x86_64/pgdg-centos10-10-2.noarch.rpm


-- centos 7 安装 postgresql  11

yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm

安装客户端：

yum install postgresql11

安装服务端：

yum install postgresql11-server

初始化与设置自启动：

/usr/pgsql-11/bin/postgresql-11-setup initdb
systemctl enable postgresql-11
systemctl start postgresql-11

默认会创建一个名为postgres的linux登录用户，这里进行密码修改
（注意，这里修改的是linux登陆的账号密码，不是数据库的）

passwd postgres

Python3安装

pip install psycopg2

三、PostgreSQL数据库命令

学习文档：https://www.yiibai.com/postgresql/install-postgresql.html

# su - postgres -- 首先切换到postgres

-bash-4.1$ psql  -- 输入psql
psql (10.5)
Type "help" for help.

postgres=# 

注意：退出使用exit命令

创建用户

postgres=# create user username with password '****';
CREATE ROLE
postgres=#

需要注意：

　　1. 要以英文分号结尾

　　2.密码需要引号包裹

1、创建数据库

create database mydb;
或者

postgres=# create database dbtest owner username; -- 创建数据库指定所属者
CREATE DATABASE
postgres=#

将数据库得权限，全部赋给某个用户

postgres=# grant all on database dbtest to username -- 将dbtest所有权限赋值给username
GRANT
postgres=#

2、删除数据库

drop database mydb

这个动作将在物理上把所有与该数据库相关的文件都删除并且不可取消，因此做这中操作之前一定要考虑清楚。

3、访问数据库

运行PostgreSQL的交互式终端程序，它被称为psql，它允许你交互地输入、编辑和执行SQL命令。

psql mydb -- 用下面的命令为mydb数据库激活它

4、查看数据库

postgres=# \l   -- l是L的小写

5.选择数据库，如果创建表的话，前提是在哪个数据库中：

\c mydb -- 选择mydb数据库，注意\c是小写

6.如果要退出psql

mydb=> \q

7、备份数据库

可以使用pg_dump和pg_dumpall来完成。比如备份sales数据库： 
pg_dump drupal>/opt/Postgresql/backup/1.bak

导入整个数据库

psql -U username databasename < /data/dum.sql -- 用户名和数据库名

四、数据表操作

创建一个新表

通过指定表的名字、所有列的名字以及其数据类型来创建新的表：

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低温度
    temp_hi         int,           -- 最高温度
    prcp            real,          -- 湿度
    date            date
);

和mysql命令终端类似，psql可以识别该命令直到分号结束。SQL 是对关键字和标识符大小写不敏感的语言，只有在标识符用双引号包围时才能保留它们的大小写。两个中横线(--)表示注释。

PostgreSQL支持标准的SQL类型int、smallint、real、double precision、char(N)、varchar(N)、date、time、timestamp和interval，还支持其他的通用功能的类型和丰富的几何类型。PostgreSQL中可以定制任意数量的用户定义数据类型。因而类型名并不是语法关键字，除了SQL标准要求支持的特例外。

第二个例子将保存城市和它们相关的地理位置：

CREATE TABLE cities (
    name            varchar(80),
    location        point
);

类型point就是一种PostgreSQL特有数据类型的例子。

删除一张表:

drop table tablename; -- 删除数据表

重命名一个表：

alter table [表名A] rename to [表名B];

列举表

\dt

查看表结构

\d tblname

查看索引

\di

增

insert语句用于向表中添加行:

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

需要注意的是：那些不是简单数字的值，通常使用单引号包裹，date类型实际上对可接收的格式相当灵活，不过我们应该坚持使用这种清晰的格式。

point类型要求一个座标对作为输入，如下：

INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');

另一种方式是列出所有的列，一个可选的语法允许你明确地列出列：

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

你可以用另外一个顺序列出列或者是忽略某些列，比如说，我们不知道降水量：

INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);

你还可以使用COPY从文本文件中装载大量数据。这种方式通常更快，因为COPY命令就是为这类应用优化的，只是比 INSERT少一些灵活性。比如：

COPY weather FROM '/home/user/weather.txt';

删

删除表中某行数据： 
delete from [表名] where [该行特征]; 
delete from [表名];--删空整个表

删除表中的字段： 
alter table [表名] drop column [字段名];

去除缺省值：  
alter table [表名] alter column [字段名] drop default;

改

修改表中的某行某列的数据 
update [表名] set [目标字段名]=[目标值] where [该行特征];

在已有的表里添加字段： 
alter table [表名] add column [字段名] [类型];

给一个字段设置缺省值：  
alter table [表名] alter column [字段名] set default [新的默认值];

重命名一个字段：  
alter table [表名] rename column [字段名A] to [字段名B];

查

使用DISTINCT运算符选择不同的行
使用where进行过滤
使用order by进行排序
根据between,in,like选择不同的行
使用group by进行分组
使用hving子句为组应用条件
使用inner join、left join、full outer join、cross join进行连表查询

postgresql的select例子

SELECT语法:

SELECT
 column_1,
 column_2,
 ...
FROM
 table_name;

首先，指定要在select子句中查询数据表的列，多个列使用逗号分隔，如果要查询所有列，使用星号(*)作为简写；其次，在From之后指出表名。使用星号(*)不是一个好习惯:

-- 在SELECT语句中使用星号（*）不是一个好习惯  。想象一下，你有一个包含许多列的大表，SELECT带有星号（*）的语句将从整列中检索所有数据，这可能不是必需的。
此外，从表中检索不必要的数据会增加数据库服务器和应用程序之间的流量。
因此，您的应用程序将变得缓慢且可扩展性降低。
因此，最好在SELECT子句中明确指定列名，以便只从表中获取所需的数据。

LIMIT：PostgreSQL 中limit是select语句可选的子句，它获取查询返回的所有行的子集。

LIMIT语法:

SELECT * FROM table_name LIMIT n;

如果要在返回行之前跳过m行，请使用offset子句:

SELECT *FROM table LIMIT n OFFSET m;

由于数据库表行的顺序是不可预测的，因此在使用limit子句的时候，应始终使用该order by子句来控制行的顺序。

我们经常使用LIMIT子句来获取表中最高或最低项的数据，例如，要获得最贵的前十名电影，您可以按照售价按降序排序，并使用LIMIT条款获得前10部电影:

select * from film order by rental_rate desc limit 10;

ORDER BY:如何对查询返回的结果集进行排序

当你从表中查询数据，PostgreSQL按照他们插入的顺序返回行，需要对结果集进行排序，需要使用select的order by子句

select column_1,column_2 from table_name order by column_1 ASC,column_2 DESC;

首先，在order by子句中指定要排序的列，如果基于多个列对结果集进行排序，使用逗号分隔。

使用ASC表示升序，DESC按照降序，如果省略，默认按照ASC。

DISTINCT：删除结果集中重复行的子句

使用distinct语句可以从结果集中删除重复的行，该distinct子句为每组重复项保留一行

select distinct column_1 from table_name; -- column_1列中的值用于评估重复项

select distinct column_1,column_2 from table_name; -- column_1列和column_2两者的结合用于评估重复项

Pgsql常用命令：

 连接数据库, 默认的用户和数据库是postgres
psql -U user -d dbname

切换数据库,相当于mysql的use dbname
\c dbname
列举数据库，相当于mysql的show databases
\l
列举表，相当于mysql的show tables
\dt
查看表结构，相当于desc tblname,show columns from tbname
\d tblname

\di 查看索引 

创建数据库： 
create database [数据库名]; 
删除数据库： 
drop database [数据库名];  
*重命名一个表： 
alter table [表名A] rename to [表名B]; 
*删除一个表： 
drop table [表名]; 

*在已有的表里添加字段： 
alter table [表名] add column [字段名] [类型]; 
*删除表中的字段： 
alter table [表名] drop column [字段名]; 
*重命名一个字段：  
alter table [表名] rename column [字段名A] to [字段名B]; 
*给一个字段设置缺省值：  
alter table [表名] alter column [字段名] set default [新的默认值];
*去除缺省值：  
alter table [表名] alter column [字段名] drop default; 
在表中插入数据： 
insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......); 
修改表中的某行某列的数据： 
update [表名] set [目标字段名]=[目标值] where [该行特征]; 
删除表中某行数据： 
delete from [表名] where [该行特征]; 
delete from [表名];--删空整个表 
创建表： 
create table ([字段名1] [类型1] <references 关联表名(关联的字段名)>;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;); 

\copyright     显示 PostgreSQL 的使用和发行条款
\encoding [字元编码名称]
                 显示或设定用户端字元编码
\h [名称]      SQL 命令语法上的说明，用 * 显示全部命令
\prompt [文本] 名称
                 提示用户设定内部变数
\password [USERNAME]
                 securely change the password for a user
\q             退出 psql


可以使用pg_dump和pg_dumpall来完成。比如备份sales数据库： 
pg_dump drupal>/opt/Postgresql/backup/1.bak