1.入门

自从MySQL被Oracle收购以后，逐渐成为开源关系型数据库的首选。

本文介绍PostgreSQL的安装和基本用法，供初次使用者上手。以下内容基于Debian操作系统，其他操作系统实在没有精力兼顾，但是大部分内容应该普遍适用。

一、安装

首先，安装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';

postgres=> CREATE ROLE o;CREATE ROLEpostgres=> CREATE DATABASE d OWNER = o;ERROR:  must be member of role "o"postgres=> GRANT o TO root;GRANT ROLEpostgres=> CREATE DATABASE d OWNER = o;CREATE DATABASE

第三件事是创建用户数据库，这里为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 usertbl(name VARCHAR(20), signupdate DATE);

# 插入数据

INSERT INTO usertbl(name, signupdate) 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 usertbl ALTER COLUMN signupdate SET NOT NULL;

# 更名栏位

ALTER TABLE usertbl RENAME COLUMN signupdate TO signup;

# 删除栏位

ALTER TABLE user_tbl DROP COLUMN email;

# 表格更名

ALTER TABLE usertbl RENAME TO backuptbl;

# 删除表格

DROP TABLE IF EXISTS backup_tbl;

2.操作指令

（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

3.与MySQL比较：

 

服务启动：

    1)#service postgresql start     2)#/etc/init.d/postgresql start     3)#su  postgresql        $ pg_ctl start PostgreSQL的进程号:1210、1207、

服务启动：     1)#service mysqld start     2)#/etc/init.d/mysqld start     3)#safe_mysqld&

MySQL的进程号为1663

第一次进入数据库：     #su – postgres     $createdb  （建名为postgres的数据库）     $psql  第一次进入数据库：      #mysql      mysql>    (出现这个提示符说明成功)

创建用户：(用户Ajian，密码：123)     #su – postgres     $ psql     =#create user ajian with password ‘123’ 创建用户：(用户Ajian，密码：123)      #grant all privileges on *.* to " identified by "123" (注意：同还可以分配权限，这里是ALL)

创建数据库(My)：     #su – postgres     $psql     =#create database My with owner = ajian template = template1 encoding=’UNICODE’; 创建数据库(My)：      1)#mysql      Mysql>create database My;      2)#mysqladmin create My

查看用户和数据库：     #su – postgres     $ psql     =#\l         (查看数据库)     =#\du        (查看用户) 查看用户和数据库：     1)#mysql      Mysql>show databases;   (看数据库)      2)#mysqlshow

新建用户登录： （首先修改配置文件） # vi /var/lib/pgsql/data/pg_hba.conf(在最后加) host all all 127.0.0.1 255.255.255.255 md5

再重启服务：#service postgresql restart 登录：#psql –h 127.0.0.1 –U ajian My Password:

新建用户登录：      1)#mysql –u ajian –p  (带口令登录)      2)#mysql       Mysql>use My;      (不带口令登录一般用于本机)

创建表(employee)： =#create table employee( (#employee_id int primary key, (#name char(8), (#sex char(2)); 创建表： >create table employee( ->employee_id int primary key, ->name char(8), ->sex char(2));

查看表：     =#\dt 查看表：     >show tables;

查看表的结构：     =#\d employee 查看表的结构：     >sescribe employee;

向表中添加数据：    =#insert into employee values   -#(‘1’,’zhang’,’F’); -#(‘2’,’chen’,’M’,); 向表中添加数据： >insert into employee values   ->(‘1’,’zhang’,’F’); ->(‘2’,’chen’,’M’,);

查看表的数据：   =#select * from emlpoyee 查看表的数据： >select * from emlpoyee;

创建索引(IN_employee)： =#create index IN_employee on employee(name);

查看索引： =#\di 删除索引： =#drop index IN_employee on employee; 重建索引： =#reindex table employee;(重建employee所有的) =#reindex index IN_employee;(重建指定的)

创建索引(IN_employee)： 1)>create index IN_employee on employee(name); 2)>alter table employee add index IN_employee(name); 查看索引： >show index from employee; 删除索引： 1)>drop index IN_employee on employee; 2)>alter table emlpoyee drop index IN_employee;

 

删除表：

   =#drop table employee; 删除表：    >drop table employee;

删除数据库：(注意命令前面的标志)    1)=#drop database ajian;    2)$dropdb ajian 删除数据库：(注意命令前面的标志)    1）>drop database ajian;    2)#mysqladmin drop ajian

 

（完）