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

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

PostgreSQL 是一個免費的對象-關系數據庫服務器(ORDBMS)，號稱是 "世界上最先進的開源關系型數據庫"。

PostgreSQL 是以加州大學計算機系開發的 POSTGRES 4.2版本為基礎的對象關系型數據庫。

今天在Ubuntu系統上，我們一起來安裝并簡單使用一下PostgreSQL數據庫。

1.查看當前系統版本:

$?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

系統是 Ubuntu 16.04.6 LTS。

2.安裝 PostgreSQL

$?sudo?apt-get?install?postgresql

執行實例如下:

$?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)?...

默認已經安裝了 postgresql 的服務器(postgresql-9.5)和客戶端(postgresql-client-9.5)。

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

可以使用?psql --version?來查看當前安裝的版本:

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

安裝后會默認生成一個名為?postgres的數據庫和一個名為postgres的數據庫用戶。

同時還生成了一個名為?postgres?的 Linux 系統用戶。

可以使用以下命令查看:

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

3.使用PostgreSQL控制臺修改 postgres 數據庫用戶密碼

默認生成的 postgres 的數據庫用戶沒有密碼，現在我們使用 postgres Linux用戶的身份來登錄到管理控制臺中。

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

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

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

postgres=#

使用?\password?命令，為?postgres?用戶設置一個密碼

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

創建用戶數據庫，這里為testdb，并指定所有者為db_user。

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

將 testdb 數據庫的所有權限都賦予 db_user 數據庫用戶， 否則 db_user 只能登錄控制臺，沒有數據庫操作權限。

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

使用?\du?查看當前的數據庫用戶:

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?命令退出控制臺， 并使用?exit?命令退出當前?db_user?Linux用戶。

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

4.數據庫基本操作實例

創建數據庫與刪除數據庫:

#?創建數據庫
postgres=#?CREATE?DATABASE?lusiadas;CREATE?DATABASE#?刪除數據庫
postgres=#?DROP?DATABASE?lusiadas;DROP?DATABASE

使用?\c?切換數據庫:

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".

新建表與刪除表:

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

增刪改查操作:

#?創建一個用戶表?tb_users(三個字段，其中id?為自增ID)
testdb=>?CREATE?TABLE?tb_users(id?bigserial,?age?INT?DEFAULT?0,?name?VARCHAR(20));CREATE?TABLE
?#?使用?INSERT?語句插入數據?
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#?查詢數據
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)

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

#?插入2條數據
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)

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

#?修改表結構:?修改字段?
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() 查看數據庫的大小:

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) 定義時間類型精度為0：(有時區)

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

使用 current_timestamp(0) 定義時間類型精度為0：(去掉時區)? ?焦作國醫堂胃腸醫院好不好：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)

時間戳:

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

設置數據庫時區:

視圖 pg_timezone_names 保存了所有可供選擇的時區:

#?查看時區??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)