PostgreSQL入门,PostgreSQL和mysql

时间:2022-06-19 01:19:59

 

PostgreSQL被誉为“世界上功能最强大的开源数据库”,是以加州大学伯克利分校计算机系开发的POSTGRES 4.2为基础的对象关系型数据库管理系统。

PostgreSQL支持大部分 SQL标准并且提供了许多其他现代特性:复杂查询、外键、触发器、视图、事务完整性、MVCC。同样,PostgreSQL 可以用许多方法扩展,比如,通过增加新的数据类型、函数、操作符、聚集函数、索引。

开发者可以免费使用、修改和分发 PostgreSQL,不管是私用、商用、还是学术研究使用。

 

 

 

PostgreSQL:是以加州大学伯克利分校计算机系开发的 POSTGRES,现在已经更名为PostgreSQL,版本 4.2为基础的对象关系型数据库管理系统(ORDBMS)。PostgreSQL支持大部分 SQL标准并且提供了许多其他现代特性:复杂查询、外键、触发器、视图、事务完整性、MVCC。同样,PostgreSQL 可以用许多方法扩展,比如, 通过增加新的数据类型、函数、操作符、聚集函数、索引。免费使用、修改、和分发 PostgreSQL,不管是私用、商用、还是学术研究使用。

特点与优势:

PostgreSQL 是一个*的对象-关系数据库服务器(数据库管理系统),它在灵活的 BSD-风格许可证下发行。它提供了相对其他开放源代码数据库系统(比如 MySQL 和 Firebird),和专有系统(比如 Oracle、Sybase、IBM 的 DB2 和 Microsoft SQL Server)之外的另一种选择。
PostgreSQL 不寻常的名字导致一些读者停下来尝试拼读它,特别是那些把SQL拼读为"sequel"的人。PostgreSQL 开发者把它拼读为 "post-gress-Q-L"。它也经常被简略念为 "postgres"。
事实上, PostgreSQL 的特性覆盖了 SQL-2/SQL-92 和 SQL-3/SQL-99,首先,它包括了可以说是目前世界上最丰富的数据类型的支持,其中有些数据类型可以说连商业数据库都不具备, 比如 IP 类型和几何类型等;其次,PostgreSQL 是全功能的*软件数据库,很长时间以来,PostgreSQL 是唯一支持事务、子查询、多版本并行控制系统(MVCC)、数据完整性检查等特性的唯一的一种*软件的数据库管理系统。 Inprise 的 InterBase 以及SAP等厂商将其原先专有软件开放为*软件之后才打破了这个唯一。最后,PostgreSQL拥有一支非常活跃的开发队伍,而且在许多黑客的努力下,PostgreSQL 的质量日益提高。

从技术角度来讲,PostgreSQL 采用的是比较经典的C/S(client/server)结构,也就是一个客户端对应一个服务器端守护进程的模式,这个守护进程分析客户端来的查询请求,生成规划树,进行数据检索并最终把结果格式化输出后返回给客户端。为了便于客户端的程序的编写,由数据库服务器提供了统一的客户端 C 接口。而不同的客户端接口都是源自这个 C 接口,比如ODBC,JDBC,Python,Perl,Tcl,C/C++,ESQL等, 同时也要指出的是,PostgreSQL 对接口的支持也是非常丰富的,几乎支持所有类型的数据库客户端接口。这一点也可以说是 PostgreSQL 一大优点。

 

 

 


PostgreSQL入门教程

一、安装

首先,安装PostgreSQL客户端。

sudo apt-get install postgresql-client
然后,安装PostgreSQL服务器。

sudo apt-get install postgresql
正常情况下,安装完成后,PostgreSQL服务器会自动在本机的5432端口开启。

如果还想安装图形管理界面,可以运行下面命令,但是本文不涉及这方面内容。

sudo apt-get install pgadmin3
二、添加新用户和新数据库

初次安装后,默认生成一个名为postgres的数据库和一个名为postgres的数据库用户。这里需要注意的是,同时还生成了一个名为postgres的Linux系统用户。

下面,我们使用postgres用户,来生成其他用户和新数据库。好几种方法可以达到这个目的,这里介绍两种。

第一种方法,使用PostgreSQL控制台。

首先,新建一个Linux新用户,可以取你想要的名字,这里为dbuser。

sudo adduser dbuser
然后,切换到postgres用户。

sudo su - postgres
下一步,使用psql命令登录PostgreSQL控制台。

psql
这时相当于系统用户postgres以同名数据库用户的身份,登录数据库,这是不用输入密码的。如果一切正常,系统提示符会变为"postgres=#",表示这时已经进入了数据库控制台。以下的命令都在控制台内完成。

第一件事是使用password命令,为postgres用户设置一个密码。

password postgres
第二件事是创建数据库用户dbuser(刚才创建的是Linux系统用户),并设置密码。

CREATE USER dbuser WITH PASSWORD ';password';;
第三件事是创建用户数据库,这里为exampledb,并指定所有者为dbuser。

CREATE DATABASE exampledb OWNER dbuser;
第四件事是将exampledb数据库的所有权限都赋予dbuser,否则dbuser只能登录控制台,没有任何数据库操作权限。

GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;
最后,使用q命令退出控制台(也可以直接按ctrl+D)。

q
第二种方法,使用shell命令行。

添加新用户和新数据库,除了在PostgreSQL控制台内,还可以在shell命令行下完成。这是因为PostgreSQL提供了命令行程序createuser和createdb。还是以新建用户dbuser和数据库exampledb为例。

首先,创建数据库用户dbuser,并指定其为超级用户。

sudo -u postgres createuser --superuser dbuser
然后,登录数据库控制台,设置dbuser用户的密码,完成后退出控制台。

sudo -u postgres psql

password dbuser

q
接着,在shell命令行下,创建数据库exampledb,并指定所有者为dbuser。

sudo -u postgres createdb -O dbuser exampledb
三、登录数据库

添加新用户和新数据库以后,就要以新用户的名义登录数据库,这时使用的是psql命令。

psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432
上面命令的参数含义如下:-U指定用户,-d指定数据库,-h指定服务器,-p指定端口。

输入上面命令以后,系统会提示输入dbuser用户的密码。输入正确,就可以登录控制台了。

psql命令存在简写形式。如果当前Linux系统用户,同时也是PostgreSQL用户,则可以省略用户名(-U参数的部分)。举例来说,我的Linux系统用户名为ruanyf,且PostgreSQL数据库存在同名用户,则我以ruanyf身份登录Linux系统后,可以直接使用下面的命令登录数据库,且不需要密码。

psql exampledb
此时,如果PostgreSQL内部还存在与当前系统用户同名的数据库,则连数据库名都可以省略。比如,假定存在一个叫做ruanyf的数据库,则直接键入psql就可以登录该数据库。

psql
另外,如果要恢复外部数据,可以使用下面的命令。

psql exampledb < exampledb.sql
四、控制台命令

除了前面已经用到的password命令(设置密码)和q命令(退出)以外,控制台还提供一系列其他命令。

h:查看SQL命令的解释,比如h select。
?:查看psql命令列表。
l:列出所有数据库。
c [database_name]:连接其他数据库。
d:列出当前数据库的所有表格。
d [table_name]:列出某一张表格的结构。
du:列出所有用户。
e:打开文本编辑器。
conninfo:列出当前数据库和连接的信息。
五、数据库操作

基本的数据库操作,就是使用一般的SQL语言。

创建新表

CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);

插入数据

INSERT INTO user_tbl(name, signup_date) VALUES(';张三';, ';2013-12-22';);

选择记录

SELECT * FROM user_tbl;

更新数据

UPDATE user_tbl set name = ';李四'; WHERE name = ';张三';;

删除记录

DELETE FROM user_tbl WHERE name = ';李四'; ;

添加栏位

ALTER TABLE user_tbl ADD email VARCHAR(40);

更新结构

ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;

更名栏位

ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;

删除栏位

ALTER TABLE user_tbl DROP COLUMN email;

表格更名

ALTER TABLE user_tbl RENAME TO backup_tbl;

删除表格

DROP TABLE IF EXISTS backup_tbl;

 

 

 

 

 

 

 

 

架构基础
行话:pgsql使用了客户端/服务器模式。一个pgsql会话包含包含两个进程:
服务器进程。管理数据库文件,接收客户端程序的连接请求,代表客户端执行数据库操作。数据库服务器程序叫做postgres。客户端应用程序。进行数据库操作。客户端应用程序本质上可以非常多样化,一个客户端可以是一个面向文本的工具,一个图形界面应用程序,一个通过访问数据库来显示网页的web服务器,或者一个专门的数据库维护工具。有些是pgsql自带的,大部分是用户开发的。
客户端和服务器可以在不同的主机上,通过TCP/IP连接通信。记住这一点很重要,因为客户机上的文件再服务器上可能是访问不到的,或者只能换个文件名访问。
pgsql服务器可以处理多并发的连接,它会为每个连接fork一个新的进程。客户端和新的服务器进程的通信与就服务器进程没有关系。主服务器进程会一直保持运行,等待客户端连接。

创建数据库
创建数据库可以检查你是否连接到了服务器。一个pgsql服务器可以管理很多数据库。
创建一个名为mydb的数据库:
$ createdb mydb
如果回车之后没有任何输出,说明创建数据库成功。你可以跳过本节了。

万一不幸出错了,这里给出了一些解决办法。
1. 找不到命令,错误如下:
createdb: command not found
说明pgsql的bin目录没有添加到环境变量。你可以选择将bin目录添加到环境变量,或者去bin目录下打开命令窗口,再或者使用绝对路径调用createdb命令。
$ /user/local/pgsql/bin/createdb mydb
建议添加还是添加环境变量,一劳永逸。
2. 服务未启动,错误如下:
createdb: could not connect to database postgres: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
表示服务器未启动,至少是在createdb执行的时候还未启动。多试试吧,我也不知道该咋办。
3. 没有账户,错误如下:
createdb: could not connect to database postgres: FATAL: role "joe" does not exist
这里joe是操作系统账户,pgsql用户账户与操作系统账户不同。你需要登录Postgres账户,创建一个用户账户。也可能是你被分配的pgsql账户名与操作系统用户名不同,这时,你可以使用-U选项指定账户或设置PGUSER环境变量来指定pgsql的用户名。
$ createdb -U user mydb
其中,user就是你的pgsql账户名。
4. 权限不足,错误如下:
createdb: database creation failed: ERROR: permission denied to create database
这是你的账户没有创建数据库的权限,你可以登录管理员账户进行修改。
pgsql允许创建任意数量的数据库。数据库名称必须以英文字母开头,且不超过63字节。
可以省略数据库名,创建与当前用户同名的数据库,如下:
$ createdb
删除数据库用dropdb命令:
$ dropdb mydb
dropdb命令必须指定数据库名称,即使是与用户名同名的数据库也必须指定,它不会默认用账户名。它会删除与数据库相关联的所有文件,并且不能撤销,所以一定要三思而行。

访问数据库
有三种方式可以访问数据库:
pgsql终端,psql命令行工具;pgAdmin或支持ODBC或JDBC的office套件;编程语言API。
这里只介绍第一种方式,第二种方式不做介绍,第三种方式后面再说。
打开cmd(或PowerShell)命令行窗口,希望你之前已经设置过环境 变量了。输入一下命令启动psql :
$ psql mydb
mydb就是之前创建的数据库。如果不指定数据库名称,默认会访问与当前账户名同名的数据库。
回车后输入密码,你会得到如下输出:
​x psql (10.4)Type "help" for help.​mydb=>
mydb=>就是命令提示符,你可以输入SQL指令(以;结尾)或psql命令(以\开头)。
如果命令提示符是mydb=#,表示当前账户是超级用户。
你可以输入以下几个命令试试:
mydb=> SELECT version();
mydb=> SELECT current_date;
mydb=> SELECT 2 + 2;
上面三个命令都是SQL命令,它们都以;结尾。
psql内部命令都以\开头。例如\h命令可以获取帮助,\q命令用来退出psql。
mydb=> \h
mydb=> \q

psql与cmd
pgsql安装后会默认安装两个工具,pgAdmin和SQL Shell(psql)。前者是图形界面工具,后者是命令行工具。
---------------------

 

 

 

 

 

二、添加新用户和新数据库

初次安装后,默认生成一个名为postgres的数据库和一个名为postgres的数据库用户。这里需要注意的是,同时还生成了一个名为postgres的Linux系统用户。

下面,我们使用postgres用户,来生成其他用户和新数据库。好几种方法可以达到这个目的,这里介绍两种。

第一种方法,使用PostgreSQL控制台。

首先,新建一个Linux新用户,可以取你想要的名字,这里为dbuser。

sudo adduser dbuser

然后,切换到postgres用户。

sudo su - postgres

下一步,使用psql命令登录PostgreSQL控制台。

psql

这时相当于系统用户postgres以同名数据库用户的身份,登录数据库,这是不用输入密码的。如果一切正常,系统提示符会变为"postgres=#",表示这时已经进入了数据库控制台。以下的命令都在控制台内完成。

第一件事是使用\password命令,为postgres用户设置一个密码。

\password postgres

第二件事是创建数据库用户dbuser(刚才创建的是Linux系统用户),并设置密码。

CREATE USER dbuser WITH PASSWORD 'password';

第三件事是创建用户数据库,这里为exampledb,并指定所有者为dbuser。

CREATE DATABASE exampledb OWNER dbuser;

第四件事是将exampledb数据库的所有权限都赋予dbuser,否则dbuser只能登录控制台,没有任何数据库操作权限。

GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;

最后,使用\q命令退出控制台(也可以直接按ctrl+D)。

\q

第二种方法,使用shell命令行。

添加新用户和新数据库,除了在PostgreSQL控制台内,还可以在shell命令行下完成。这是因为PostgreSQL提供了命令行程序createuser和createdb。还是以新建用户dbuser和数据库exampledb为例。

首先,创建数据库用户dbuser,并指定其为超级用户。

sudo -u postgres createuser --superuser dbuser

然后,登录数据库控制台,设置dbuser用户的密码,完成后退出控制台。

sudo -u postgres psql

\password dbuser

\q

接着,在shell命令行下,创建数据库exampledb,并指定所有者为dbuser。

sudo -u postgres createdb -O dbuser exampledb

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

安装 PostgreSQL 10

下载 PostgreSQL 10,postgresql-10.1-3-linux-x64-binaries.tar.gz。下载地址:https://get.enterprisedb.com/postgresql/postgresql-10.1-3-linux-x64-binaries.tar.gz。

(注:安装脚本如下(需要有 /opt/local 写权限),可使用如下命令创建 /opt/local 目录。)

1

2

sudo mkdir /opt/local

sudo chown -R $USER:$USER /opt/local

install_pg.sh

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

OPT_BASE=/opt

PGVERSION=10.1

PGBASE=$OPT_BASE/local/pgsql

PGHOME=$OPT_BASE/local/pgsql/$PGVERSION

PGDATA=$OPT_BASE/var/pgsql/$PGVERSION

PG_SOFT_¥TAR="postgresql-10.1-3-linux-x64-binaries.tar.gz"

if [ -d $PGHOME ]; then

rm -rf $PGHOME

elif [ ! -d $PGBASE ]; then

mkdir -p $PGBASE

fi

if [ ! -d $PGDATA ]; then

mkdir -p $PGDATA

fi

echo "Install PostgreSQL"

tar zxf $PG_SOFT_TAR -C $PGBASE

mv $PGBASE/pgsql $PGHOME

cp pg-pwfile $PGHOME

echo "Init PostgreSQL"

pushd $PGHOME

./bin/initdb --pgdata="$PGDATA" --auth=ident --auth-host=md5 --encoding=UTF-8 --locale=zh_CN.UTF-8 --username=postgres --pwfile=pg-pwfile

rm -f pg-pwfile

popd

cp pg_hba.conf $PGDATA

cp postgresql.conf $PGDATA

chmod 600 $PGDATA/*.conf

echo "Start PostgreSQL"

$PGHOME/bin/pg_ctl -D $PGDATA -l logfile start

sleep 5

#cp .pgpass ~/

$PGHOME/bin/psql -h localhost -U postgres -d postgres -f pg_init.sql

install_pg.sh 脚本安装时依赖文件的完整版压缩包在此下载:https://yangbajing.me/files/postgresql10-scripts.tar.gz

  • pg-pwfile:在初始化数据库时设置默认管理员账户的密码

  • pg_hba.conf:默认只允许 127.0.0.1/8 访问数据库,这里改成允许所有网段可访问

  • postgresql.conf:修改数据库监听地址为 * ,监听所有本地网络地址

  • pg_init.sql:创建一个普通账户 yangbajing 和测试用数据库 yangbajing ,密码也设置为 yangbajing

安装后PG数据库管理管理员账号是 postgres,密码为 postgres。同时,还创建了一个普通账号:yangbajing 和同名数据库 yangbajing,密码也是 yangbajing

将 /opt/local/pgsql/10.1/bin 目录加入系统环境变量。

1

2

echo 'export PATH="/opt/local/pgsql/10.1/bin:$PATH" >> ~/.bashrc

. ~/.bashrc

使用如下命令来启动或停止PostgreSQL 10数据库

启动数据库

1

pg_ctl -D /opt/local/var/pgsql/10.1 -l logfile start

停止数据库

1

pg_ctl -D /opt/local/var/pgsql/10.1 -l logfile stop

体验 PG

输入以下命令访问PG数据库:

1

psql -h localhost -U yangbajing -d yangbajing -W

根据提示输入密码登录,进入 psql 的 REPL 界面。

1

2

3

4

5

Password for user yangbajing:

psql.bin (10.1)

Type "help" for help.

yangbajing=>

先建立一些测试表:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

CREATE TABLE t_role (

id         INT PRIMARY KEY,

name       VARCHAR(255) NOT NULL,

created_at TIMESTAMPTZ

);

CREATE TABLE t_user (

id         BIGSERIAL PRIMARY KEY,

name       VARCHAR(255) NOT NULL,

roles      INT []       NOT NULL,

data       JSONB,

created_at TIMESTAMPTZ

);

INSERT INTO t_role (id, name, created_at) VALUES (1, '超级管理员', now()), (2, '管理员', now()), (3, '用户', now());

INSERT INTO t_user(name, roles, data, created_at) VALUES

('root', '{1}', '{"email":"root@yangbajing.me"}', now()),

('羊八井', '{2,3}', '{"email":"yangbajing"}', now()),

('哈哈', '{3}', '{"email":"haha@yangbajing.me"}', now());

先来执行两个简单的 SELECT 查询:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

yangbajing=> select * from t_role;

id |    name    |          created_at

----+------------+-------------------------------

1 | 超级管理员 | 2018-02-01 22:03:17.168906+08

2 | 管理员     | 2018-02-01 22:03:17.168906+08

3 | 用户       | 2018-02-01 22:03:17.168906+08

(3 rows)

yangbajing=> select * from t_user;

id |  name  | roles |                 data                  |          created_at

----+--------+-------+---------------------------------------+-------------------------------

2 | root   | {1}   | {"email": "root@yangbajing.me"}       | 2018-02-01 22:06:21.140465+08

3 | 哈哈   | {3}   | {"email": "haha@yangbajing.me"}       | 2018-02-01 22:06:21.140465+08

1 | 羊八井 | {2,3}   | {"email": "yangbajing@yangbajing.me"} | 2018-02-01 22:04:41.580203+08

(3 rows)

接下来,尝试一些 PG 特色特性。

InsertOrUpdate

插入或更新,是一个很有用的特性,当在主键冲突时可以选择更新数据。在PG中,是使用 ON CONFLICT 来实现这个特性的。

1

2

3

4

INSERT INTO t_role (id, name, created_at)

VALUES (3, '普通用户', now())

ON CONFLICT (id)

DO UPDATE SET name = EXCLUDED.name;

在常用的 INSERT 语句后面用 ON CONFLICT (...) DO .... 语句来指定在某个/些字段出现冲突时需要执行的语句。在 on CONFLICT (...) 里的参数需要是主键或唯一索引(可以为复合字段)。当冲突发生时则会执行 DO .... 后面的语句,这里我们选择更新 name 字段的值。EXCLUDED 是用户引用在 VALUES .... 部分我们将插入的数据,EXCLUDED.name 在这里就是 '普通用户' 。除 DO UPDATE,我们还可以使用 DO NOTHING 来简单的忽略插入时的主键冲突。

SERIAL/BIGSERIAL

看看表 t_user 的结构:

1

2

3

4

5

6

7

8

9

10

11

yangbajing=> \d t_user

Table "public.t_user"

Column   |           Type           | Collation | Nullable |              Default

------------+--------------------------+-----------+----------+------------------------------------

id         | bigint                   |           | not null | nextval('t_user_id_seq'::regclass)

name       | character varying(255)   |           | not null |

roles      | integer[]                |           | not null |

data       | jsonb                    |           |          |

created_at | timestamp with time zone |           |          |

Indexes:

"t_user_pkey" PRIMARY KEY, btree (id)

在建表时 id 字段的类型定义的是 BIGSERIAL ,但这里却是显示的 bigint 类型;另外,还多了一个默认值:nextval('t_user_id_seq'::regclass) 。这是 PG 中的 序列 ,PG中使用序列来实现 自增值 的特性。

序列:t_user_id_seq

1

2

3

4

5

6

yangbajing=> \d t_user_id_seq

Sequence "public.t_user_id_seq"

Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache

--------+-------+---------+---------------------+-----------+---------+-------

bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

Owned by: public.t_user.id

也可以先创建序列,再设置字段的默认值为该序列的下一个值。

1

CREATE SEQUENCE t_user_id2_seq INCREMENT BY 1 MINVALUE 1 START WITH 1;

这里创建一个序列,设置最小值为1,从1开始按1进行递增。

数组类型

在创建 t_user 表的 roles 字段时,使用了数组类型 INT [] 。数组类型对于我们的数据建模来说很有用,使用得好可以大大减少关系表的数量。

根据索引返回值

1

2

3

4

5

6

7

yangbajing=> SELECT id, name, roles[2], created_at FROM t_user;

id |  name  | roles |          created_at

----+--------+-------+-------------------------------

2 | root   |       | 2018-02-01 22:06:21.140465+08

3 | 哈哈   |       | 2018-02-01 22:06:21.140465+08

1 | 羊八井 |     1 | 2018-02-01 22:04:41.580203+08

(3 rows)

注意:PG 中,索引下标从0开始

以行的形式输出数组元素

1

2

3

4

5

6

7

8

yangbajing=> SELECT id, unnest(roles) AS role_id FROM t_user;

id | role_id

----+---------

2 |       1

3 |       3

1 |       2

1 |       1

(4 rows)

包含查找

1

2

3

4

5

yangbajing=> SELECT * FROM t_user WHERE roles @> ARRAY[1,2];

id |  name  | roles |                 data                  |          created_at

----+--------+-------+---------------------------------------+-------------------------------

1 | 羊八井 | {2,1} | {"email": "yangbajing@yangbajing.me"} | 2018-02-01 22:04:41.580203+08

(1 row)