PostgreSQL数据库用户认证

精选转载

lizhi0189 2007-10-23 11:47:38

PostgreSQL是现在比较流行的数据库之一，这个起源于伯克利（BSD）的数据库研究计划目前已经衍生成一项国际开发项目，并且有非常广泛的用户。据我了解国内四大国产数据库，其中三个都是基于PostgreSQL开发的。并且，因为许可证的灵活，任何人都可以以任何目的免费使用，修改，和分发 PostgreSQL，不管是私用，商用，还是学术研究使用。本文只是简单介绍一下postgresql的安装和简单的使用，语法方面涉及的比较少，以方便新手上路为目的。

1.系统环境和安装方法；

PostgreSQL的安装方法比较灵活，可以用源码包安装，也可以用您使用的发行版所带的软件包来安装，还可以采用在线安装……

1.1 系统环境：Ubuntu Linux 7.04 ；Fedora；Slackware；

1.2 安装；

Ubuntu下安装

软件其实很简单，用新立得软件包管理器搜索psql便能查到postgresql-client-8.2(同时可以搜到8.1版本，哪一个都可以)，选中-应用即可。或者在终端下输入

xiaop@localhost$ sudo apt-get install postgresql-8.2

Slackware下安装：

请到 linuxpackages.net 上查找您所用系统的对应版本，用pkginstall 来安装，或您安装slap-get 工具，在线自动安装；要用到root权限，可以通用sudo。关于su和sudo的参考；《Linux 系统中的超级权限的控制》

安装postgresql的软件包，可用下面的办法；

xiaop@localhost# pkginstall post*.tgz
或
xiaop@localhost# slapt-get --install postgresql-8.2.4

在Fedora中，您可以用软件包在线安装工具来安装

注：这样就安装好了PostgreSQL 8.2了，该安装将自动创建一个缺省的数据库集群（pgsqldb.org中的译法）“main”，且生成一个数据库超级用户postgres。

2. 启动PostgreSQL 数据库服务器；

2.1 在流行Linux发行版的启动方法；

在Ubuntu系统中，服务器启动脚本放在 /etc/init.d目录中，您可以用下面的方法来启动，对于Fedora和Gentoo 也是类似的;

xiaop@localhost~# /etc/init.d/postgresql-8.2 start 注：启动；
xiaop@localhost~# /etc/init.d/postgresql-8.2 restart 注：重启;
xiaop@localhost~# /etc/init.d/postgresql-8.2 stop 注：停止；
xiaop@localhost~# /etc/init.d/postgresql-8.2 status 注：查看状态；

在Slackware中，PostgreSQL的启动脚本放在 /etc/rc.d目录中，如果您用从linuxpackages.net 上下载的软件包或在线安装的软件包；

xiaop@localhost~# /etc/rc.d/rc.postgres start

如果您用源码包编译安装，启动PostgreSQL，请查看PostgreSQL官方文档；

2.2 关于 PostgreSQL启动和存储目录；

启动PostgreSQL服务器时，一般是以postgres 用户来启动的，自编译安装的除外；对于数据库的存储一般是放在/var/lib中的相关目录，比如 /var/lib/pgsql或 /var/lib/postgresql/8.2/main/ 目录等；不同的发行版可能不太一样，不过还是大同小异，您可以通过修改数据存储位置把数据库存在其它地方；

3.创建用户

添加用户命令格式。

createuser 是 SQL 命令 CREATE USER的封装。
命令：createuser [-a] [-A] [-d] [-D] [-e] [-P] [-h 主机名] [-p port] 用户名

参数说明：

[-a]：允许创建其他用户，相当于创建一个超级用户；
[-A]：不允许此用户创建其他用户；
[-d]：允许此用户创建数据库；
[-D]：不允许此用户创建数据库；
[-e]：将执行过程显示到Shell上；
[-P]：创建用户时，同时设置密码；
[-h 主机名]：为某个主机上的Postgres创建用户；
[-p port]：与-h参数一同使用，指定主机的端口。

3.1添加用户;

3.1.1不带参数的创建用户;

xiaop@localhost~$ createuser testuser 
Shall the new user be allowed to create databases? (y/n) n --------是否可以创建数据库：否
Shall the new user be allowed to create more new users? (y/n) n ---------是否可以创建新用户：否
CREATE USER 

注：不带参数创建用户时，Postgres会询问此用户的权限，上面的例子创建了一个普通用户;

3.1.2 为指定的主机和端口上创建用户 ;

xiaop@localhost~$ createuser -h 172.28.18.51 -p 5000 -D -A -e testuser 
CREATE USER joe NOCREATEDB NOCREATEUSER; 
CREATE USER 

注：这个命令为主机172.28.18.51的5000端口创建用户testuser，此用户不可以创建数据库和其他用户。

3.1.3创建超级用户;

xiaop@localhost~$ createuser -P -d -a -e testuser 
Enter password for new user: testuser 
Enter it again: testuser 
CREATE USER joe PASSWORD 'testuser' CREATEDB CREATEUSER; 
CREATE USER 

注：这个命令在本地创建一个超级用户(-a)，可以创建数据库(-d)，同时要求设置密码。

3.2 删除用户：

命令：dropuser [-i] [-h] [-p] [-e] 用户名

参数说明：
[ -i]：删除用户前，要求确认；
[-h 主机名]：删除某个主机上的Postgres用户；
[-p port]：与-h参数一同使用，指定主机的端口；
[-e]：将执行过程显示到Shell上。

3.2.1 删除本地的Postgres用户;

xiaop@localhost~$ dropuser testuser 
DROP USER 

3.2.2 删除远程Postgres服务器上的用户;

xiaop@localhost~$ dropuser -p 5000 -h 172.28.18.51 -i -e testuser 
User "testuser" and any owned databases will be permanently deleted. 
Are you sure? (y/n) y 
DROP USER "testuser" 
DROP USER 

注：此命令删除主机172.28.18.51(-h)的5000端口(-p)的用户testuser，并且需要确认(-i);

4. 创建和删除数据库；

4.1创建数据库

看看您能否访问数据库服务器的第一个例子就是试着创建一个数据库;
要创建一个新的数据库，在我们这个例子里叫 mydb，您可以使用下面的命令：

xiaop@localhost~$ createdb mydb

它应该生成下面这样的响应：

CREATE DATABASE

如果这样，那么这一步就成功了，如果您看到类似下面这样的信息

createdb: command not found

那么就是PostgreSQL没有安装好,要么是就根本没装上;
您还可以用其它名字创建数据库。 PostgreSQL 允许您在一个节点上创建任意数量的数据库。数据库名必须是以字母开头并且小于 63 个字符长。一个方便的做法是创建和您当前用户名同名的数据库。许多工具假设该数据库名为缺省数据库名，所以这样可以节省您的敲键。要创建这样的数据库，只需要键入 :

xiaop@localhost~$ createdb

4.2 删除数据库

如果您再也不想使用您的数据库了，那么您可以删除它。比如，如果您是数据库 mydb 的所有人（创建人），那么您就可以用下面的命令删除它：

xiaop@localhost~$ dropdb mydb

注:（对于这条命令而言，数据库名不是缺省的用户名。这样您就必须声明它。）这个动作物理上将所有与该数据库相关的文件都删除并且不可取消，因此做这件事之前一定要想清楚;

5.访问数据库

一旦您创建了数据库，您就可以访问它，您可以运行PostgreSQL交互的终端程序，叫psql，它允许您交互地输入，编辑，和执行 SQL 命令。（图形化登录请参见6. Postgresql图形化管理工具pgAdmin3）

5.1 激活数据库

您需要启动psql，试验刚才的例子。您可以用下面的命令为 mydb 数据库激活它：

xiaop@localhost~$ psql mydb

如果您省略了数据库名字，那么它缺省就是您的用户账号名字。

Welcome to psql 8.2.4, the PostgreSQL interactive terminal. 
Type: \copyright for distribution terms 
       \h for help with SQL commands 
       \? for help with psql commands 
       \g or terminate with semicolon to execute query 
       \q to quit 
mydb=# 

注：最后一行 mydb=#，这个提示符意味着您是数据库超级用户。

5.2 帮助和退出数据库

psql 程序有一些不属于 SQL 命令的内部命令。它们以反斜杠开头，"\"。有些这种命令在欢迎信息中列出。比如，您可以用下面的命令获取各种PostgreSQL SQL 命令的帮助语法：

mydb=> \h

要退出 psql，键入

mydb=> \q

然后 psql 就会退出并且给您返回到命令行 shell; （要获取更多有关内部命令的信息，您可以在 psql 提示符上键入 \?。）

6. Postgresql图形化管理工具pgAdmin3 ;

版本：Version1.4.3

6.1 安装;

6.1.1 Ubuntu安装 ;

有两种方法：

1.在新立得软件包管理器中搜索pgadmin3，找到pgadmin3--应用

2.终端下输入命令：

        
 xiaop@xiaop-laptop:~$ sudo apt-get install pgadmin3

6.1.2 其它系统的安装;

您可以参考其它系统正常的软件的安装方法，起本类似，这里就不多做介绍了;

6.2 pgAdmin3的简单使用;

图形化管理系统相对比较直观，您可以在命令行下操作，然后在pgAdmin3下查看效果;

6.2.1 pgAdmin3的启动

您可以在应用程序---系统工具中找到pgAdmin3的启动项;
也可以在命令行下输入：

xiaop@xiaop-laptop:~$ /usr/bin/pgadmin3 start

6.2.2 连接已创建的数据库mydb ;

点击档案-----新增服务器，然后在跳出的窗口下输入：

地址：localhost 
描述：服务器名称（随意填写） 
维护数据库：postgres 
用户名：自己创建一个（详情参见创建用户） 
密码：和用户名对应（创建用户时自己创建） 

点击确定后大家便可以查看postsql已有的数据库了;
注：pgAdmin3的数据库和终端下创建的数据库是完全同步的（可以用刷新查看效果）， pgAdmin3是比较方便的图形化管理工具，它可以创建图表，管理数据库等，有关pgAdmin3的详细介绍我们在以后讨论，本文主要介绍命令行下的操作。图形化管理工具能做到的命令行都可以做到，您可以在命令行下创建表，在pgAdmin3上查看是否同步：

7. 创建和删除表;

7.1 创建新表;

创建完数据库之后，您就可以创建新表了，可以通过声明表的名字和所有字段的名字及其类型来创建表，例如：

mydb#CREATE TABLE weather (
                city varchar(80),
                temp_lo int, -- 最低气温
                temp_hi int, -- 最高气温
                prcp real, -- 降水量
                date date
                     );

注：您可以在 psql 里连换行符一起键入这些东西。 psql 可以识别该命令直到分号才结束,不要忘记“;”
您可以在 SQL 命令中自由使用空白（也就是空格，tab，和换行符）。这就意味着您可以用和上面不同的对齐方式键入命令。两个划线("--") 引入注释。任何跟在它后面的东西直到该行的结尾都被忽略。 SQL 是对关键字和标识符大小写不敏感的语言，只有在标识符用双引号包围时才能保留它们的大小写属性。

7.2 数据类型;

上面例子中的varchar(80) 声明一个可以存储最长 80 个字符的任意字符串的数据类型。 int 是普通的整数类型。 real 是一种用于存储单精度浮点数的类型。 date 类型应该可以自解释。
PostgresSQL 支持标准的 SQL 类型 int，smallint， real，double precision， char(N)， varchar(N)，date， time，timestamp 和 interval，还支持其他的通用类型和丰富的几何类型。 PostgreSQL 可以客户化为定制任意的用户定义的数据类型，您可以参考PostgreSQL的中文文档来查询;

7.3 删除表;

如果您不再需要某个表，或者您想创建一个不同的表，那么您可以用下面的命令删除它：

mydb#DROP TABLE tablename

8. 向表中添加行;

8.1 INSERT;

INSERT 用于向表中添加行,您可以输入（在数据库中操作）：

mydb#INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

注：所有数据类型都使用了相当明了的输入格式。那些不是简单数字值的常量必需用单引号(')包围，就象在例子里一样。

8.2 point类型输入;

point 类型要求一个座标对作为输入，如下：

mydb#INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');

8.3 COPY;

您还可以使用 COPY 从文本文件中装载大量数据。这么干通常更快，因为 COPY 命令就是为这类应用优化的，只是比 INSERT 少一些灵活性．比如：

mydb#COPY weather FROM '/home/user/weather.txt';

注：weather.txt是您提前写好的符合格式标准的表格内容文档;

9. 查询一个表;

9.1 SELECT;

要从一个表中检索数据就是查询这个表。 SQL 的 SELECT 就是做这个用途的。该语句分为选择列表（列出要返回的字段部分），表列表（列出从中检索数据的表的部分），以及可选的条件（声明任意限制的部分）。比如，要检索表 weather 的所有行，键入：

SELECT * FROM weather; 
<code> 
输出结果：
<code>
     city | temp_lo | temp_hi | prcp | date 
---------------+---------+---------+------+------------ 
 San Francisco | 46 | 50 | 0.25 | 1994-11-27 
 San Francisco | 43 | 57 | 0 | 1994-11-29 
 Hayward | 37 | 54 | | 1994-11-29 
(3 rows) 

您可以在选择列表中写任意表达式，而不仅仅是字段列表。比如，您可以：

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

这样应该得出：

     city | temp_avg | date
---------------+----------+------------
 San Francisco | 48 | 1994-11-27
 San Francisco | 50 | 1994-11-29
 Hayward | 45 | 1994-11-29
(3 rows)

请注意这里的 AS 子句是如何给输出字段重新命名的。（AS 子句是可选的。）

9.2 WHERE;

一个查询可以使用 WHERE 子句"修饰"，声明需要哪些行。 WHERE 子句包含一个布尔（真值）表达式，只有那些布尔表达式为真的行才会被返回。允许您在条件中使用常用的布尔操作符（AND，OR，和 NOT）。比如，下面的查询检索旧金山的下雨天的天气：

mydb#SELECT * FROM weather
    WHERE city = 'San Francisco' AND prcp > 0.0;

结果：

     city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
 San Francisco | 46 | 50 | 0.25 | 1994-11-27
(1 row)

9.3 排序;

您可以要求返回的查询是排好序的：

mydb#SELECT * FROM weather
    ORDER BY city;

得出结果：

     city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
 Hayward | 37 | 54 | | 1994-11-29
 San Francisco | 43 | 57 | 0 | 1994-11-29
 San Francisco | 46 | 50 | 0.25 | 1994-11-27

在这个例子里，排序的顺序并非绝对清晰的，因此您可能看到 San Francisco 行随机的排序。但是如果您使用下面的语句，那么就总是会得到上面的结果

SELECT * FROM weather
    ORDER BY city, temp_lo;

您可以要求查询的结果按照某种顺序排序，并且消除重复的行输出：

mydb#SELECT DISTINCT city
    FROM weather;

得出结果：

     city
---------------
 Hayward
 San Francisco
(2 rows)

再次声明，结果行的顺序可能是随机的。

10. 视图;

假设您的应用对天气记录和城市位置的组合列表特别感兴趣，而您又不想每次键入这些查询。那么您可以在这个查询上创建一个视图，它给这个查询一个名字，您可以像普通表那样引用它。

进入数据库后输入：

mydb#CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;

建立视图;

然后选择建好的视图：

SELECT * FROM myview;

结果如下：

     city | temp_lo | temp_hi | prcp | date | location 
---------------+---------+---------+------+------------+----------- 
 San Francisco | 46 | 50 | 0.25 | 1994-11-27 | (-194,53) 
 San Francisco | 43 | 57 | 0 | 1994-11-29 | (-194,53) 
(2 rows) 

11. 更新行;

您可以用 UPDATE 命令更新现有的行。假设您发现所有 11 月 28 日的温度计数都低了两度，那么您就可以用下面的方式更新数据：

mydb#UPDATE weather
    SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
    WHERE date > '1994-11-28';

看看数据的新状态：

SELECT * FROM weather;

结果为：

     city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
 San Francisco | 46 | 50 | 0.25 | 1994-11-27
 San Francisco | 41 | 55 | 0 | 1994-11-29
 Hayward | 35 | 52 | | 1994-11-29
(3 rows)

12. 删除行;

数据行可以用 DELETE 命令从表中删除。假设您对Hayward的天气不再感兴趣，那么您可以用下面的方法把那些行从表中删除：

mydb#DELETE FROM weather WHERE city = 'Hayward';

我们用下面形式的语句的时候一定要小心

DELETE FROM tablename;

如果没有条件，DELETE 将从指定表中删除所有行，把它清空。做这些之前系统不会请求您确认！