在Ubuntu下安装Postgresql：PostgreSql安装指南

学习请参考：PostgreSQL8.1中文文档

一、安装与连接

 1、在ubuntu系统上安装:

             命令：sudo apt-get install postgresql 

                      sudo apt-get install pgadmin3   //安装图形客户端（可选）

 2、创建系统用户（就是与你系统同名的用户，方便以后操作）

      首先获得postgres的超级终端：

             sudo -u postgres sh

      然后创建新用户：

             createuser -s -P konglingchun //-s表示konglingchun为超级用户；-P表示使用密码。详细请查看createuser - -help

     最后退出超级终端：

            exit

3、创建数据库

           createdb mydb  //默认会以系统用户创建

4、连接数据库

          psql mydb   //默认会以系统用户连接

二、表操作

1、创建新表

create table weather (
    city            varchar(80),
    temp_lo         int,           -- 最低气温
    temp_hi         int,           -- 最高气温
    prcp            real,          -- 降水量
    date            date
);

注：PostgresSQL 支持标准的 SQL 类型 int，smallint， real，double precision， char(N)， varchar(N)，date， time，timestamp 和 interval，还支持其他的通用类型和丰富的几何类型。

2、删除表

drop table tablename;

3、插入行操作

insert into weather (city, temp_lo, temp_hi, prcp, date)
    values ('San Francisco', 43, 57, 0.0, '1994-11-29');

4、导入文件操作

copy weather from '/home/user/weather.txt';

5、查询表操作

  5.1整表查询

SELECT * FROM weather;

  5.2选段查询

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

 5.3条件查询

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

注：常用的布尔操作符（AND，OR， 和 NOT）。

  5.4分组查询

SELECT * FROM weather
    ORDER BY city;

  5.5降序查询

SELECT * FROM weather
    ORDER BY city desc;

  5.6唯一查询

SELECT DISTINCT city
    FROM weather;

  5.7连接查询

SELECT city, temp_lo, temp_hi, prcp, date, location
    FROM weather, cities
    WHERE city = name;

  5.8聚集函数

    常用聚集函数有count（数目）， sum（和），avg（均值）， max（最大值）和min（最小值）。

聚集函数可以用在select后：

SELECT max(temp_lo) FROM weather;

也可以用在having后：

SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;

但是不能用在where后：

SELECT city FROM weather WHERE temp_lo = max(temp_lo);     WRONG

注：WHERE 和 HAVING 的基本区别如下： WHERE 在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）， 而 HAVING 在分组和聚集之后选取分组的行。 因此，WHERE 子句不能包含聚集函数； 因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。 相反，HAVING 子句总是包含聚集函数。

6、更新操作

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

7、删除操作

DELETE FROM weather WHERE city = 'Hayward';

8、删除所有记录

DELETE FROM tablename;

三、高级特性

1、视图

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

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

SELECT * FROM myview;

2、外键

    用途：你想确保没有人可以在 weather 表里插入一条在 cities 表里没有匹配记录的数据行。 这就叫维护你的表的参考完整性。

新的表声明看起来会象下面这样：

CREATE TABLE cities (
        city            varchar(80) primary key,
        location        point
);

CREATE TABLE weather (
        city            varchar(80) references cities(city),
        temp_lo         int,
        temp_hi         int,
        prcp            real,
        date            date
);

然后我们试图插入一条非法的记录（假设cities表里没有'Berkeley'字段，若有不明请参考文档）：

INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');

 就会出现如下错误：

ERROR:  insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL:  Key (city)=(Berkeley) is not present in table "cities".

3、事物

     事务被认为是原子的：从其它事务的角度来看，它要么是全部发生，要么完全不发生。

在 PostgreSQL 里，一个事务是通过把 SQL 命令用 BEGIN 和 COMMIT 命令包围实现的。例如：

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- 等等
COMMIT;

如果在该事务的过程中，我们决定不做提交（可能是我们刚发现 Alice 的余额是负数）， 那么我们可以发出 ROLLBACK 命令而不是 COMMIT 命令，那么到目前为止我们的所有更新都会被取消。

4、继承

      我们以表 cities 和表 capitals为例。自然，首府（capital）也是城市（cities）之一，所以我们就可以让表capitals中的部分字段从cities表中继承：

CREATE TABLE cities (
    name            text,
    population      real,
    altitude        int     -- (单位是英尺)
);

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);

注意: 尽管继承经常是有用的，但是它还没有集成唯一约束或者外键，因此制约了其实用性。