天天看點
傳回

【資料庫】POSTGRESQL+pgAdmin

一.操作

此文章中postgresql版本是psql (PostgreSQL) 9.6rc1

此文章中若出現ricky，為使用者名；mydb，為資料庫名；

-h：資料庫IP 
-U：登入使用者 
-d：登入的資料庫 
-p：登入端口

1、指令行登入資料庫

psql -U 使用者名 -d 資料庫名

（還可以填上伺服器位址和端口5432）

如：

psql -U ricky -d mydb

如果

psql 資料庫名

則預設使用登陸系統的使用者名連接配接資料庫，如果系統的預設使用者名和資料庫名相同直接可以省掉資料庫名，直接psql

登陸成功後可能出現兩種提示符：

資料庫名=# 超級使用者
資料庫名=> 普通使用者

2、使用圖形界面管理資料庫

可以使用pgAdmin圖形界面管理工具，這個工具在安裝資料庫的同時就可以安裝。

3、退出shell

使用

\q

退出

mydb=> \q

4、反斜杠

psql程式中有些不屬于SQL指令，是以反斜杠開頭。

比如

\h

可以檢視幫助資訊。

基礎操作

  • 檢視所有使用者

    xxxx=#

    my_db=# \du

     
角色清單
 角色名稱 |                    屬性                    | 成員屬于
----------+--------------------------------------------+----------
 postgres | 超級使用者, 建立角色, 建立 DB, 複制, 繞過RLS | {}
 ricky    | 超級使用者, 建立角色, 建立 DB                | {}
  • 更改密碼 
my_db=# \password 資料庫名   這裡修改了使用者的密碼
輸入新的密碼：
新密碼
  • 删除使用者

    my_db=# drop user 使用者名;

    錯誤: 目前使用者不能被删

    之是以會出現這樣的錯誤是因為使用目前資料庫的使用者正要被删除，這樣是不允許的。應該使用超級管理者登入資料庫系統，然後删除該使用者。

  • 建立使用者

xxxx=#

create user 使用者名;

CREATE ROLE

xxxx=#

alter user 使用者名 password'密碼';

ALTER ROLE

xxxx=#

\du

(此步驟用來檢視所有使用者)

  • 建立資料庫

mydb=#

CREATE DATABASE 使用者名;

CREATE DATABASE

mydb=#

GRANT ALL PRIVILEGES ON DATABASE 資料庫名 to 使用者名;

将資料庫的權限給使用者

GRANT

mydb=#

\c 資料庫名

連接配接到剛剛建立的資料庫

檢視所有的資料庫

注：template0是不可修改的空白資料庫

testdb=#

\l

 
資料庫清單
   名稱    |  擁有者  | 字元編碼 |            校對規則            |             Ctype              |       存取權限     
-----------+----------+----------+--------------------------------+--------------------------------+-----------------------
 mydb     | ricky    | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
 postgres  | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
 template0 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +
           |          |          |                                |                                | postgres=CTc/postgres
 template1 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +
           |          |          |                                |                                | postgres=CTc/postgres
 testdb    | ricky    | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =Tc/ricky            +
           |          |          |                                |                                | ricky=CTc/ricky
(5 行記錄)
  • 檢視表

mydb=#

\d

 
關聯清單
 架構模式 |   名稱   |  類型  | 擁有者
----------+----------+--------+--------
 public   | user_tbl | 資料表 | ricky
 public   | yser_tbl | 資料表 | ricky
  • 檢視表結構

mydb=#

\d user_tbl

( \d 加上表名)

資料表 "public.user_tbl"
    欄位    |         類型          | 修飾詞
------------+-----------------------+--------
 name       | character varying(20) |
 signp_date | date

  • 其他常用指令

    切換資料庫,相當于mysql的use dbname

    \c 資料庫名

     列舉資料庫，相當于mysql的show databases

    \l

     列舉表，相當于mysql的show tables

    \dt

     檢視表結構，相當于desc tblname,show columns from tbname

    \d tblname

     
\password           設定密碼。
\q                  退出。
\h                  檢視SQL指令的解釋，比如\h select。
\?                  檢視psql指令清單。
\l                  列出所有資料庫。
\c [database_name]  連接配接其他資料庫。
\d                  列出目前資料庫的所有表格。
\d [table_name]     列出某一張表格的結構。
\du                 列出所有使用者。
\e                  打開文本編輯器。
\conninfo           檢視目前資料庫，和連接配接的資訊。

—————————————————————————————————————————

以上摘抄自網絡，下面是自行操作的經驗記錄

參考自https://www.bilibili.com/video/av24590479/?p=3

一.基本操作+查詢

  • 在terminal直接進入PG的某個資料庫

    psql -U 使用者名 資料庫名

    若成功進入，則傳回：

    psql(版本号)

    Type “help” for help.

  • 退出PG

    xxxx=#

    \q

  • 儲存退出

    ctrl+O？？未試過

  • 檢視所有使用者 xxxx=#

    \du

  • 建立使用者

    xxxx=#

    create user 使用者名;

    CREATE ROLE

    xxxx=#

    alter user 使用者名 password'密碼';

     ALTER ROLE
  • 檢視所有資料庫 xxxx=#

    \l

  • 建立資料庫

    xxxx=#

    CREATE DATABASE 使用者名;

    CREATE DATABASE

    xxxx=#

    GRANT ALL PRIVILEGES ON DATABASE 資料庫名 to 使用者名;

    将資料庫的權限給使用者

    GRANT

    xxxx=#

    \c 資料庫名

     連接配接到剛剛建立的資料庫

  • 檢視目前使用者

    xxxx=#

    \conninfo

     檢視目前資料庫，和連接配接的資訊。

  • 檢視目前資料庫

    xxxx=# 這裡的

    xxxx

     就是目前資料庫的名字。

  • 檢視資料庫中所有表

    xxxx=#

    \dt

    若資料庫中存在表，則傳回List of relations；若不存在表，則傳回：

    No relations found.

  • 檢視表

    xxxx=#

    \dt 表名

     比如： 
List of relations
 Schema |   Name    | Type  | Owner 
--------+-----------+-------+-------
 public |   表名     | table | chiu
(1 row)

  • 檢視表的詳細資訊

    xxxx+#

    \d 表名

     比如： 
資料庫名=# \d 表名
                                               Table "public.表名"
     Column     |            Type             | Collation | Nullable |                     Default                     
----------------+-----------------------------+-----------+----------+-------------------------------------------------
 id             | integer                     |           | not null | nextval('表名_id_seq'::regclass)
 name           | character varying(30)       |           | not null | 
 season_count   | integer                     |           | not null | nextval('表名_season_count_seq'::regclass)
 origin_release | timestamp without time zone |           |          | 
 status         | integer                     |           | not null | nextval('表名_status_seq'::regclass)
 delete_reason  | text                        |           |          | ' '::text
Indexes:
    "表名_pkey" PRIMARY KEY, btree (id)
  • 檢視某個表的所有column

資料庫名=#

select * from 表名;

 
id | name | season_count | origin_release | status | delete_reason 
----+------+--------------+----------------+--------+---------------
(0 rows)

  • 在PG中引入表

    xxxx+#

    \i 檔案名

     ？？？未試過

二.建表

  • 建立一個表（簡單版本）

    xxxx=#

    create table 資料庫名(一個column名 varchar(255),另個column名 text);

     CREATE TABLE
  • 建立一個表 
create table 資料庫名(
   id serial primary key,
   title varchar(255) not null,
   content text check(length(content) > 8),   //内容必須大于8個位元組
   id_draft boolean default TRUE,
   id_del boolean default FALSE,
   created_date timestamp default 'now'
);

若成功則傳回：
CREATE TABLE

(自己寫的例子)

create table tv_series(
   id serial primary key,
   name varchar(30) not null,
   season_count serial not null,
   origin_release timestamp,
   status serial,
   delete_reason text default ' '
);

  • 檢視系統中所有的表

    xxxx=#

    \dt

     
List of relations
 Schema |   Name    | Type  | Owner 
--------+-----------+-------+-------
 public |    表名    | table | 使用者名
(1 row)

  • 檢視剛剛建的表的詳細資訊（顯示column與其type）

    xxxx=#

    \d 表名

     
Table "表名"
    Column    |          Type          | Collation | Nullable | Default 
--------------+------------------------+-----------+----------+---------
一個column名   | character varying(255) |           |          | 
另個column名   | text                   |           |          |

  • 更改表名

    xxxx=#

    alter table 舊表名 rename to 新表名;

    ALTER TABLE

    xxxx=#

    \dt

     檢查下更新

  • 删除表（非常危險，工作時謹慎使用!!!）

    xxxx=#

    drop table 表名;

     DROP TABLE

三.在表内insert value語句

模版：

insert into [tablename] (field, ...) values (value, ...)
            [表名]   (字段，一般指非空字段)     (指派)

//字段與指派是一一對應的

比如表格column如下：

id | name | season_count | origin_release | status | delete_reason 
----+------+--------------+----------------+--------+---------------
(0 rows)

插入語句操作為：

insert into tv_series(id,name,season_count,origin_release) values(101,'西部世界',1,'2016/10/2');

成功後傳回：

INSERT 0 1

未完待續

注意！

  • MySQL 裡面有

    auto_increment

    自增字段，PostgreSQL 沒有自增字段這一說法。

    但是有單獨的對象：序列。 我們可以用序列或者其他土方法來是實作這樣的文法。

    http://blog.chinaunix.net/uid-259788-id-4078773.html

  • PostgreSQL不支援unsigned。

    https://stackoverflow.com/questions/20810134/why-unsigned-integer-is-not-available-in-postgresql

* 介紹與下載下傳

設定端口：預設5432

是以加州大學伯克利分校計算機系開發的 PostGRES，現在已經更名為PostgreSQL，版本 4.2為基礎的對象關系型資料庫管理系統（ORDBMS）。PostgreSQL支援大部分 SQL标準并且提供了許多其他現代特性：複雜查詢、外鍵、觸發器、視圖、事務完整性、MVCC。同樣，PostgreSQL 可以用許多方法擴充，比如， 通過增加新的資料類型、函數、操作符、聚集函數、索引。免費使用、修改、和分發 PostgreSQL，不管是私用、商用、還是學術研究使用。

安裝方法：2選1:

  • 一.通過homebrew安裝：（**推薦！**需要先安裝homebrew，詳情在本文中頁面搜尋）

    https://www.jianshu.com/p/9e91aa8782da

  • 二.官網下載下傳DMG：（不推薦的原因是還要手動在bash_profile輸入PATH，才能使用psql）

    https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

一.用homebrew安裝postgresql時，裝好後裡就可直接用psql。

  • 1.安裝：

    brew install postgresql

  • 2.還差一步完成安裝：

    To migrate existing data from a previous major version of PostgreSQL run:

    從老版本的PostgreSQL中轉移已有的資料

    brew postgresql-upgrade-database

    To have launchd start postgresql now and restart at login:

    現在啟動PostgreSQL，并在登入時重新開機:

    brew services start postgresql

    Or, if you don’t want/need a background service you can just run:

    乜都唔做，啟動先

    pg_ctl -D /usr/local/var/postgres start
  • 3.初始化：

    initdb /usr/local/var/postgres
  • 4.mac安裝PostgreSQL後不會建立使用者名資料庫，執行指令建立資料庫和賬戶:

    createdb

     然後登入PostgreSQL控制台：(psql連接配接資料庫預設選用的是目前的系統使用者)

    psql
  • 5.檢視所有的資料庫：

    \l

     
List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+-------+----------+-------------+-------------+-------------------
 xxxx      | xxxx  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | xxxx  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | xxxx  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/xxxx          +
           |       |          |             |             | xxxx=CTc/xxxx
 template1 | xxxx  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/xxxx          +
           |       |          |             |             | xxxx=CTc/xxxx
(4 rows)

看到已存在使用者同名資料庫、postgres資料庫，但是postgres資料庫的所有者是目前使用者，沒有postgres使用者。

  • 6.建立postgres使用者

    CREATE USER postgres WITH PASSWORD 'XXXXXX';
  • 7.删除預設生成的postgres資料庫

    DROP DATABASE postgres;
  • 8.建立屬于postgres使用者的postgres資料庫

    CREATE DATABASE postgres OWNER postgres;
  • 9.将資料庫所有權限賦予postgres使用者

    GRANT ALL PRIVILEGES ON DATABASE postgres to postgres;
  • 10.給postgres使用者添加建立- 資料庫的屬性

    ALTER ROLE postgres CREATEDB;

     這樣就可以使用postgres作為資料庫的登入使用者了，并可以使用該使用者管理資料庫

詳情見 https://www.jianshu.com/p/9e91aa8782da

二.用DMG安裝

程式沒把psql所在的目錄放到path裡，可能導緻操作psql -U pgdbo時仍傳回：

-bash: psql: command not found

可以在環境變量裡追加PATH：(以下3個路徑任選1個)

(注：這類檔案應該都要提權操作:

sudo

)

  • 1 重要配置，不建議更改：/etc/profile 裡：

    sudo vi /etc/profile
  • 2 etc/paths：

    sudo vi /etc/paths

     (這裡添加代碼時不用寫export)
  • 3 建議在這裡修改：bath_profile：

    sudo vi ~/.bash_profile

(或參考此處https://blog.csdn.net/weixin_42915286/article/details/84036486)

添加一條：

export PATH=$PATH:/你的psql所在目錄

如：

export PATH=$PATH:/Library/PostgreSQL/11/bin

注：若顯示

E325: ATTENTION
Found a swap file by the name "~/.bash_profile.swp"
          owned by: root   dated: Tue Dec 25 14:15:08 2018
         file name: ~root/.bash_profile
          modified: YES
         user name: root   host name: xxxx-MacBook-Air.local
        process ID: 44360
While opening file "/Users/xxxx/.bash_profile"
             dated: Thu Nov 15 11:59:46 2018

(1) Another program may be editing the same file.  If this is the case,
    be careful not to end up with two different instances of the same
    file when making changes.  Quit, or continue with caution.
(2) An edit session for this file crashed.
    If this is the case, use ":recover" or "vim -r /Users/xxxx/.bash_profile"
    to recover the changes (see ":help recovery").
    If you did this already, delete the swap file "/Users/xxxx/.bash_profile.swp
"
    to avoid this message.

Swap file "~/.bash_profile.swp" already exists!
[O]pen Read-Only, (E)dit anyway, (R)ecover, (D)elete it, (Q)uit, (A)bort:

說明有未儲存的其他版本。

POSTGRESQL之 完全解除安裝方法：

  • 1.如果是postgresql.app的形式，這個簡單，跟其他app一樣，删除app即可。
  • 2.如果是使用installer圖形界面方式安裝的。則需要打開終端指令行。
  • 3.執行

    open /Library/PostgreSQL/9.2/uninstall-postgresql.app

     （以實際URL為準）
  • 4.删除postgresql檔案夾

    sudo rm -rf /Library/PostgreSQL
  • 5.删除配置檔案

    sudo rm /etc/postgres-reg.ini
  • 6.System Preference - Users&Groups 删除POSTGRESQL使用者
    【資料庫】POSTGRESQL+pgAdmin
  • 7.删除共享記憶體設定 （我沒有做過特殊設定，是以我本機是沒有這個檔案的，如果有，可以删除。）

    sudo rm /etc/sysctl.conf

參考部落格：

https://blog.csdn.net/stk_tianwen/article/details/17757393

http://stackoverflow.com/questions/8037729/completely-uninstall-postgresql-9-0-4-from-mac-osx-lion

end

參考部落格：

https://blog.csdn.net/yunqishequ1/article/details/77323647

https://www.jianshu.com/p/21a9a1d0488a

二.用戶端 pgADMIN

MAC OS 官網下載下傳連結：

https://www.postgresql.org/ftp/pgadmin/pgadmin4/v3.6/macos/

資料庫管理系統
上一篇: ubuntu16.04 使用apt安裝的超大資料的postgresql9.6資料庫遷移
下一篇: 為什麼需要使用雲計算技術？

繼續閱讀