(引)GNU make、automake、qmake 学习指引
(摘)Postgresql数据库学习备录(二)

(摘)Postgresql数据库学习备录(一)

孔令春 posted @ 2009年10月22日 22:29 in 杂七杂八 with tags 安装postgresql , 2753 阅读

在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 类型 intsmallintrealdouble precisionchar(N)varchar(N)datetimetimestampinterval,还支持其他的通用类型和丰富的几何类型。

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;

注:常用的布尔操作符(ANDOR, 和 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

注:WHEREHAVING 的基本区别如下: 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 命令用 BEGINCOMMIT 命令包围实现的。例如:

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);

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


登录 *


loading captcha image...
(输入验证码)
or Ctrl+Enter