文档章节

PostgreSQL新手入门

quanpower
 quanpower
发布于 2014/03/23 15:52
字数 3251
阅读 385
收藏 21

1.入门

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

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

postgresql

一、安装

首先,安装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 ROLE

postgres=> CREATE DATABASE d OWNER = o;
ERROR:  must be member of role "o"

postgres=> GRANT o TO root;
GRANT ROLE

postgres=> 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 ajian@"% " 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

 

(完)

© 著作权归作者所有

quanpower
粉丝 65
博文 166
码字总数 104911
作品 0
嘉定
CTO(技术副总裁)
私信 提问
加载中

评论(1)

Mybrc91
Mybrc91
mark
Linux下PostgreSQL基础操作

PostgreSQL一直以为是一个和sqlite一个级别的数据库,但是在查询之后,自己的态度就完全变了,mysql被oricle公司收购以后,PG就成了比较流行的开源数据库的首选,而且heroku上面不支持mysql...

Zoker
2014/01/31
4.8K
2
CSDN日报190318:简单爬虫:给女友定时发送睡前小故事

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/blogdevteam/article/details/88640303 程序人生 | 简单爬虫:给女友定时发送睡前小故事 作者:librauee 最近...

CSDN官方博客
03/18
0
0
【零基础】PostgreSQL从入门到精通

基本了解: PostgreSQL:是以加州大学伯克利分校计算机系开发的 POSTGRES,现在已经更名为PostgreSQL,版本 4.2为基础的对象关系型数据库管理系统(ORDBMS)。PostgreSQL支持大部分 SQL标准并...

mcy0425
2018/08/22
123
0
BSD 安装PostgreSQL

安装好之后,是没有5432 端口监听的 一通过更改数据据 postgresql.conf 的 listenaddresses 改成 "*", 另外一个是设置增加 pghba.conf 遠端連線的權限。 用 CREATE USER test WITH PASSWORD...

MtrS
2013/08/03
66
0
pagerank 算法 快速入门

背景 pageRank 是Google CEO 拉里佩奇提出的一种算法,来计算互联网里的网站的重要性,以对搜索进行排名。 此处为啥算法叫pagerank,因为是以Google公司创办人拉里·佩奇(Larry Page)之姓来...

yuejiewc
2018/05/23
0
0

没有更多内容

加载失败,请刷新页面

加载更多

代理模式之JDK动态代理 — “JDK Dynamic Proxy“

动态代理的原理是什么? 所谓的动态代理,他是一个代理机制,代理机制可以看作是对调用目标的一个包装,这样我们对目标代码的调用不是直接发生的,而是通过代理完成,通过代理可以有效的让调...

code-ortaerc
49分钟前
4
0
学习记录(day05-标签操作、属性绑定、语句控制、数据绑定、事件绑定、案例用户登录)

[TOC] 1.1.1标签操作v-text&v-html v-text:会把data中绑定的数据值原样输出。 v-html:会把data中值输出,且会自动解析html代码 <!--可以将指定的内容显示到标签体中--><标签 v-text=""></......

庭前云落
今天
7
0
VMware vSphere的两种RDM磁盘

在VMware vSphere vCenter中创建虚拟机时,可以添加一种叫RDM的磁盘。 RDM - Raw Device Mapping,原始设备映射,那么,RDM磁盘是不是就可以称作为“原始设备映射磁盘”呢?这也是一种可以热...

大别阿郎
今天
10
0
【AngularJS学习笔记】02 小杂烩及学习总结

本文转载于:专业的前端网站☞【AngularJS学习笔记】02 小杂烩及学习总结 表格示例 <div ng-app="myApp" ng-controller="customersCtrl"> <table> <tr ng-repeat="x in names | orderBy ......

前端老手
昨天
14
0
Linux 内核的五大创新

在科技行业,创新这个词几乎和革命一样到处泛滥,所以很难将那些夸张的东西与真正令人振奋的东西区分开来。Linux内核被称为创新,但它又被称为现代计算中最大的奇迹,一个微观世界中的庞然大...

阮鹏
昨天
18
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部