postgreSql基礎命令及linux下postgreSql命令 @ 彥霖實驗筆記

（1）用戶實用程序：

createdb 創建一個新的PostgreSQL的數據庫（和SQL語句：CREATE DATABASE 相同）

createuser 創建一個新的PostgreSQL的用戶（和SQL語句：CREATE USER 相同）

dropdb 刪除數據庫

dropuser 刪除用戶

pg_dump 將PostgreSQL數據庫導出到一個腳本文件

pg_dumpall 將所有的PostgreSQL數據庫導出到一個腳本文件

pg_restore 從一個由pg_dump或pg_dumpall程序導出的腳本文件中恢复PostgreSQL數據庫

psql 一個基於命令行的PostgreSQL交互式客戶端程序

vacuumdb 清理和分析一個PostgreSQL數據庫，它是客戶端程序psql環境下SQL語句VACUUM的shell腳本封裝，二者功能完全相同

（2）系統實用程序

initdb 創建一個用於存儲數據庫的PostgreSQL數據目錄，並創建預定義的模板數據庫template0和template1,生成共享目錄表 catalog；此程序通常只在安裝PostgreSQL時運行一次

initlocation 創建一個輔助的PostgreSQL數據庫存儲區域

ipcclean 從停止的PostgreSQL服務器中清除共享內在和孤立信號標志

pg_ctl 启動、停止、重启PostgreSQL服務（比如：pg_ctl start 启動PostgreSQL服務，它和service postgresql start相同）

pg_controldata 顯示PostgreSQL服務的內部控制信息

postgres PostgreSQL單用戶模式的數據庫服務

postmaster PostgreSQL多用戶模式的數據庫服務

4.這裏面最重要的是psql這個客戶端程序最为重要。启用客戶端程序psql的方法是：

切換到PostgreSQL預定義的數據庫超級用戶postgres，启用客戶端程序psql，並連接到自己想要的數據庫，比如說：

psql template1

出現以下界面，說明已經進入到想要的數據庫，可以進行想要的操作了。

template1=#

5.在數據庫中的一些命令：

template1=# \l 查看系統中現存的數據庫

template1=# \q 退出客戶端程序psql

template1=# \c 從一個數據庫中轉到另一個數據庫中，如template1=# \c sales 從template1轉到sales

template1=# \dt 查看表

template1=# \d 查看表結構

template1=# \di 查看索引
[基本數據庫操作]========================
*創建數據庫：
create database [數據庫名];

*查看數據庫列表：
\d

*刪除數據庫：
drop database [數據庫名];

創建表：
create table ([字段名1] [類型1] <references 關聯表名(關聯的字段名)>;,[字段名2] [類型2],......<,primary key (字段名m,字段名n,...)>;);

*查看表名列表：
\d

*查看某個表的狀況：
\d [表名]

*重命名一個表：
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 [表名];--刪空整個表

6.要注意隨時對數據庫進行清理、收回磁盤空間並更新統計信息，使用下面的命令就搞定！

vaccumdb -d sales -z

-a 對所有的數據庫操作

-z 保證不斷地刪除失效的行，節約磁盤空間，將統計信息更新为最近的狀態

7.PostgreSQL用戶認證

PostgreSQL數據目錄中的pg_hba.conf的作用就是用戶認證，可以在/var/lib/pgsql/data中找到。

有以下幾個例子可以看看：

(1)允許在本機上的任何身份連接任何數據庫

TYPE DATABASE         USER     IP-ADDRESS    IP-MASK     METHOD

local all             all                                 trust(無條件進行連接)

(2)允許IP地址为192.168.1.x的任何主機與數據庫sales連接

TYPE DATABASE         USER     IP-ADDRESS    IP-MASK     METHOD

host sales            all      192.168.1.0    255.255.255.0 ident sameuser(表明任何操作系統用戶都能夠以同名數據庫用戶進行連接)

8.看了那麼多，來一個完整的創建PostgreSQL數據庫用戶的示例吧

(1)進入PostgreSQL高級用戶

(2)启用客戶端程序，並進入template1數據庫

psql template1

(3)創建用戶

template1=# CREATE USER hellen WITH ENCRYPED PASSWORD'zhenzhen'

(4)因为設置了密碼，所以要編輯pg_hba.conf，使用戶和配置文件同步。

在原有記錄上面添加md5

local    all    hellen      md5

(4)使用新用戶登錄數據庫

template1=# \q

psql -U hellen -d template1

PS：在一個數據庫中如果要切換用戶，要使用如下命令：

template1=# \!psql -U tk -d template1

9.設定用戶特定的權限

還是要用例子來說明：

創建一個用戶組：

sales=# CREATE GROUP sale;

添加幾個用戶進入該組

sales=# ALTER GROUP sale ADD USER sale1,sale2,sale3;

授予用戶級sale針對表employee和products的SELECT權限

sales=# GRANT SELECT ON employee,products TO GROUP sale;

在sale中將用戶user2刪除

sales=# ALTER GROP sale DROP USER sale2;

10.備份數據庫

可以使用pg_dump和pg_dumpall來完成。比如備份sales數據庫：

pg_dump sales>/home/tk/pgsql/backup/1.bak