PostgreSQL入門-安裝與基本使用（Ubuntu16）

PostgreSQL入門-安裝與基本使用（Ubuntu）

PostgreSQL 是一個(gè)免費(fèi)的對(duì)象-關(guān)系數(shù)據(jù)庫服務(wù)器(ORDBMS)，號(hào)稱是 "世界上最先進(jìn)的開源關(guān)系型數(shù)據(jù)庫"。

PostgreSQL 是以加州大學(xué)計(jì)算機(jī)系開發(fā)的 POSTGRES 4.2版本為基礎(chǔ)的對(duì)象關(guān)系型數(shù)據(jù)庫。

今天在Ubuntu系統(tǒng)上，我們一起來安裝并簡單使用一下PostgreSQL數(shù)據(jù)庫。

1.查看當(dāng)前系統(tǒng)版本:

$?cat?/etc/issue
Ubuntu?16.04.6?LTS?\n?\l

$?sudo?lsb_release?-a
LSB?Version:	
core-9.20160110
ubuntu0.2-amd64:core-9.20160110
ubuntu0.2-noarch:security-9.20160110
ubuntu0.2-amd64:security-9.20160110
ubuntu0.2-noarch
Distributor?ID:	Ubuntu
Description:	Ubuntu?16.04.6?LTS
Release:	16.04
Codename:	xenial

系統(tǒng)是 Ubuntu 16.04.6 LTS。

2.安裝 PostgreSQL

$?sudo?apt-get?install?postgresql

執(zhí)行實(shí)例如下:

$?sudo?apt-get?install?postgresql
Reading?package?lists...?Done
Building?dependency?tree???????
Reading?state?information...?Done
The?following?additional?packages?will?be?installed:
??libpq5?
??postgresql-9.5?
??postgresql-client-9.5?
??postgresql-client-common?
??postgresql-common?
??postgresql-contrib-9.5?
??ssl-cert
?…?…
Creating?config?file?/etc/postgresql-common/createcluster.conf?with?new?version
Creating?config?file?/etc/logrotate.d/postgresql-common?with?new?version
Building?PostgreSQL?dictionaries?from?installed?myspell/hunspell?packages...
Removing?obsolete?dictionary?files:
Setting?up?postgresql-9.5?(9.5.19-0ubuntu0.16.04.1)?...
Creating?new?cluster?9.5/main?...
??config?/etc/postgresql/9.5/main
??data???/var/lib/postgresql/9.5/main
??locale?en_US.UTF-8
??socket?/var/run/postgresql
??port???5432
update-alternatives:?using?/usr/share/postgresql/9.5/man/man1/postmaster.1.gz?to?provide?/usr/share/man/man1/postmaster.1.gz?(postmaster.1.gz)?in?auto?mode
Setting?up?postgresql?(9.5+173ubuntu0.2)?...
Setting?up?postgresql-contrib-9.5?(9.5.19-0ubuntu0.16.04.1)?...
Processing?triggers?for?libc-bin?(2.23-0ubuntu11)?...
Processing?triggers?for?ureadahead?(0.100.0-19.1)?...
Processing?triggers?for?systemd?(229-4ubuntu21.21)?...

默認(rèn)已經(jīng)安裝了 postgresql 的服務(wù)器(postgresql-9.5)和客戶端(postgresql-client-9.5)。

2019年10月03日，已經(jīng)發(fā)布了PostgreSQL 12，如果想安裝最新版的，需要更新一下源，參加?PostgreSQL Apt Repository

可以使用?psql --version?來查看當(dāng)前安裝的版本:

$?psql?--version
psql?(PostgreSQL)?9.5.19

安裝后會(huì)默認(rèn)生成一個(gè)名為?postgres的數(shù)據(jù)庫和一個(gè)名為postgres的數(shù)據(jù)庫用戶。

同時(shí)還生成了一個(gè)名為?postgres?的 Linux 系統(tǒng)用戶。

可以使用以下命令查看:

#查看用戶$?cat?/etc/passwd#查看用戶組??$?cat?/etc/group

3.使用PostgreSQL控制臺(tái)修改 postgres 數(shù)據(jù)庫用戶密碼

默認(rèn)生成的 postgres 的數(shù)據(jù)庫用戶沒有密碼，現(xiàn)在我們使用 postgres Linux用戶的身份來登錄到管理控制臺(tái)中。

#?切換到postgres用戶。$?sudo?su?-?postgres
postgres@iZm5e8p54dk31rre6t96xuZ:~$?
postgres@iZm5e8p54dk31rre6t96xuZ:~$?whoami
postgres

Linux 用戶 postgres 以同名的 postgres 數(shù)據(jù)庫用戶的身份登錄，不用輸入密碼的。

postgres@iZm5e8p54dk31rre6t96xuZ:~$?psql
psql?(9.5.19)
Type?"help"?for?help.

postgres=#

使用?\password?命令，為?postgres?用戶設(shè)置一個(gè)密碼

postgres=#?postgres=#?CREATE?USER?db_user?WITH?PASSWORD?'PWD123456';CREATE?ROLE
postgres=#

創(chuàng)建用戶數(shù)據(jù)庫，這里為testdb，并指定所有者為db_user。

postgres=#?CREATE?DATABASE?testdb?OWNER?db_user;CREATE?DATABASE
postgres=#

將 testdb 數(shù)據(jù)庫的所有權(quán)限都賦予 db_user 數(shù)據(jù)庫用戶， 否則 db_user 只能登錄控制臺(tái)，沒有數(shù)據(jù)庫操作權(quán)限。

postgres=#?GRANT?ALL?PRIVILEGES?ON?DATABASE?testdb?TO?db_user;GRANT

使用?\du?查看當(dāng)前的數(shù)據(jù)庫用戶:

http://m.qd8.com.cn/yiyao/xinxi21_3709996.html

postgres=#?\du;
???????????????List?of?roles
Role?name?|????Attributes??????????????????????|?Member?of?
-----------+------------------------------------------------+-----------
db_user???|???????????????????????????????????????????????????????|?{}
postgres??|?Superuser,Create?role,Create?DB,Replication,Bypass?RLS?|?{}

最后，使用?\q?命令退出控制臺(tái)， 并使用?exit?命令退出當(dāng)前?db_user?Linux用戶。

postgres=#?\qpostgres@iZm5e8p54dk31rre6t96xuZ:~$?
postgres@iZm5e8p54dk31rre6t96xuZ:~$?exitlogout

4.數(shù)據(jù)庫基本操作實(shí)例

創(chuàng)建數(shù)據(jù)庫與刪除數(shù)據(jù)庫:

#?創(chuàng)建數(shù)據(jù)庫
postgres=#?CREATE?DATABASE?lusiadas;CREATE?DATABASE#?刪除數(shù)據(jù)庫
postgres=#?DROP?DATABASE?lusiadas;DROP?DATABASE

使用?\c?切換數(shù)據(jù)庫:

postgres=#?CREATE?DATABASE?testdb;CREATE?DATABASEpostgres=#?\c?testdb;
SSL?connection?(protocol:?TLSv1.3,?cipher:?TLS_AES_256_GCM_SHA384,?bits:?256,?compression:?off)
You?are?now?connected?to?database?"testdb"?as?user?"postgres".

新建表與刪除表:

#?創(chuàng)建一個(gè)表?tb_test：(兩個(gè)字段，其中id?為自增ID)
testdb=>?CREATE?TABLE?tb_test(id?bigserial,?name?VARCHAR(20));CREATE?TABLE#?刪除一個(gè)表?tb_test
testdb=>?DROP?table?tb_test;DROP?TABLE

增刪改查操作:

#?創(chuàng)建一個(gè)用戶表?tb_users(三個(gè)字段，其中id?為自增ID)
testdb=>?CREATE?TABLE?tb_users(id?bigserial,?age?INT?DEFAULT?0,?name?VARCHAR(20));CREATE?TABLE
?#?使用?INSERT?語句插入數(shù)據(jù)?
testdb=>?INSERT?INTO?tb_users(name,?age)?VALUES('張三豐',?212);INSERT?0?1testdb=>?INSERT?INTO?tb_users(name,?age)?VALUES('李四光',?83);INSERT?0?1testdb=>?INSERT?INTO?tb_users(name,?age)?VALUES('王重陽',?58);INSERT?0?1#?查詢數(shù)據(jù)
testdb=>?select?*?from?tb_users;
?id?|?age?|??name??
----+-----+--------
??1?|?212?|?張三豐
??2?|??83?|?李四光
??3?|??58?|?王重陽
(3?rows)
testdb=>?select?*?from?tb_users?WHERE?id=3;
?id?|?age?|??name??
----+-----+--------
??3?|??58?|?王重陽
(1?row)

#?更新數(shù)據(jù)?(執(zhí)行后輸出更新的條數(shù)，第二次執(zhí)行失敗所以輸出為`UPDATE?0`)
testdb=>?UPDATE?tb_users?set?name?=?'全真派王重陽'?WHERE?name?=?'王重陽';
UPDATE?1
testdb=>?UPDATE?tb_users?set?name?=?'全真派王重陽'?WHERE?name?=?'王重陽';
UPDATE?0

#?插入2條數(shù)據(jù)
testdb=>?INSERT?INTO?tb_users(name,?age)?VALUES('趙四',?0);
INSERT?0?1
testdb=>?INSERT?INTO?tb_users(name,?age)?VALUES('趙五娘',?0);
INSERT?0?1

#?模糊查詢
testdb=>?SELECT?*?FROM?tb_users?WHERE?name?LIKE?'趙%';
?id?|?age?|??name??
----+-----+--------
??4?|???0?|?趙五娘
??5?|???0?|?趙四
(2?rows)

#?修改表結(jié)構(gòu):?新增字段?
testdb=#?ALTER?TABLE?tb_users?ADD?email?VARCHAR(50);
ALTER?TABLE

#?修改表結(jié)構(gòu):?修改字段?
testdb=#?ALTER?TABLE?tb_users?ALTER?COLUMN?email?TYPE?VARCHAR(100);
ALTER?TABLE

#?刪除字段
testdb=#?ALTER?TABLE?tb_users?DROP?COLUMN?email;
ALTER?TABLE

#?刪除記錄
testdb=>?DELETE?FROM?tb_users?WHERE?id?=?5;
DELETE?1

使用 pg_database_size() 查看數(shù)據(jù)庫的大小:

testdb=#?select?pg_database_size('testdb');
?pg_database_size?
------------------
??????????7991967
(1?row)
testdb=#?select?pg_size_pretty(pg_database_size('testdb'));
?pg_size_pretty?
----------------
?7805?kB
(1?row)

5.PostgreSQL 的 timestamp 類型

查詢 current_timestamp

testdb=#?select?current_timestamp;
???????current_timestamp???????
-------------------------------
?2019-11-11?08:33:35.369887+00
(1?row)

使用 current_timestamp(0) 定義時(shí)間類型精度為0：(有時(shí)區(qū))

testdb=#?select?current_timestamp(0);
???current_timestamp????
------------------------
?2019-11-11?08:31:08+00
(1?row)

使用 current_timestamp(0) 定義時(shí)間類型精度為0：(去掉時(shí)區(qū))? ?焦作國醫(yī)堂胃腸醫(yī)院好不好：http://jz.lieju.com/zhuankeyiyuan/37175212.htm

testdb=#?select?current_timestamp(0)::timestamp?without?time?zone;
??current_timestamp??
---------------------
?2019-11-11?08:31:20
(1?row)

testdb=#?select?cast?(current_timestamp(0)?as??timestamp?without?time?zone);
??current_timestamp??
---------------------
?2019-11-11?08:32:26
(1?row)

時(shí)間戳:

testdb=#?select?extract(epoch?from?now());
????date_part?????
------------------
?1573461495.47821
(1?row)

設(shè)置數(shù)據(jù)庫時(shí)區(qū):

視圖 pg_timezone_names 保存了所有可供選擇的時(shí)區(qū):

#?查看時(shí)區(qū)??select?*?from?pg_timezone_names;

比如可以選擇上海?Asia/Shanghai?或重慶?Asia/Chongqing， 最簡單的直接?PRC:

testdb=#?set?time?zone?'PRC';?SET
testdb=#?show?time?zone;
?TimeZone?
----------
?PRC
(1?row)
testdb=#?SELECT?LOCALTIMESTAMP(0);
???localtimestamp????
---------------------
?2019-11-11?16:42:54
(1?row)