PostgreSQL 入门(一)

PostgreSQl 数据库是一个功能强大,又带有移动互联网特征的开源数据库。如果你仅仅是想把数据库作为一个简单的储存功能使用(如一些大的互联网公司),一些较复杂的功能都想放在应用中来实现,那么选择 MySQL 或者一些 NoSQL 产品都是合适的;如果你应用的数据访问很简单(如大多数的 blog 系统),那么后端使用 MySQL 也是很合适的。但如果你的应用不像 blog 系统那么简单,而你又不想消耗太多的开发资源,那么 PostgreSQL 是一个明智的选择。最有说服力的例子就是图片分享公司 Instagram,在使用 python+PostgreSQL 架构后,只是十几个人就支持起了整个公司的业务。在数据库中使用 PostgreSQL 的感觉,就像在开发语言中使用 Python,会让你的工作变得简洁而高效。

————《PostgreSQL 修炼之道》

关于 "PostgreSQL" 的读法,开发者把它拼读为 "post-gress-Q-L"。它也经常被简略念为 "postgres"。发音参考下面的音频。来自:https://wiki.postgresql.org/wiki/FAQ

PostgreSQL 的安装

PostgreSQL 官网 下载安装包安装。最后一步会出现 "Stack Builder",提示你为哪一个 PostgreSQL 安装附加软件(如果你电脑上安装了多个数据库实例的话),如果不需要安装附件可以点击取消。

PostgreSQL 的简单配置

PostgreSQL 数据库的配置主要是通过修改 Data 目录下的 postgresql.conf 文件来实现的。

1. 修改监听的 IP 和端口

在 Data 目录下编辑 postgresql.conf 文件,找到如下内容:

# listen_addresses = '*'    # what IP address(es) to listen on;
# port = 5432             # (change requires restart)

"listen_addresses" 表示监听地址。 "localhost" 表示主机 127.0.0.1, "*" 表示在本地所有地址上监听。 "port" 表示监听数据库的端口,默认 "5432" 不用更改,如果同时安装了几个数据库,可以分别改为不同的端口。

2. 与数据库 log 相关的函数

日志的收集一般是要打开的,所以需要设置:

logging_collector = on

日志的目录一般使用默认值:

log_directory = 'pg_log'

日志的切换和是否选择覆盖可以使用如下几种方案。
方案一:每天生成一个新的日志文件。

log_filename = 'postgresql-%Y-%m-%d__%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0

方案二:每当日志写满一定大小(如 10MB)则切换一个日志。

log_filename = 'postgresql-%Y-%m-%d__%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 0
log_rotation_size = 10M

方案三:只保留 7 天的日志,进行循环覆盖。

log_filename = 'postgresql-%Y-%m-%d__%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0

3. 内存参数的设置

shared_buffers : 共享内存的大小,主要用于共享数据块。
如果机器上有足够内存,可以将 shared_buffers 改大一些,这样数据块读取数据时就可以从共享内存中读取,而不需要读取文件。
work_mem : 单个 SQL 执行时,排序、hash join 所使用的内存,SQL 运行完成后,内存就释放了。
将 work_mem 设置大一些,会让排序操作快一些。

4. 从 windows 命令行进入 PostgreSQL

首先确保 PostgreSQL 被添加进系统变量中,如何添加参见百度经验如何添加环境变量或路径添加到系统的Path里面。从命令行通过命令psql -U postgres启动 PostgreSQL1

SQL 语言

SQL 是结构化查询语言(STRUCTURED QUERY LANGUAGE)的简称。分为 DQL、DML、DDL 几类。

  • DQL (Data Query Language) : 数据查询语言,基本就是 SELECT 查询命令,用于数据查询。
  • DML (Data Manipulation Language) : 数据操纵语言。主要用于插入、更新、删除数据,所以也分为 INSERT、UPDATE、DELETE 三种语句。
  • DDL (Data Definition Language) : 数据定义语言,主要用于创建、删除,以及修改表、索引等数据库对象语言。

1. DDL 语句

1. 建表语句

注意: SQL 语句以 ";" 结尾。

CREATE TABLE table_name(
co101_name data_type,
co102_name data_type,
co103_name data_type,
co104_name data_type,
);

e.g.

CREATE TABLE score(
student_name varchar(40),
chinese_score int,
math_score int,
test_date date
);

在 psql 中,可以使用\d显示数据库中有那些表,如下:

postgres=# \d
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | score | table | postgres
(1 row)

使用\d score可以查看这张表

postgres=# \d score
               Table "public.score"
    Column     |         Type          | Modifiers
---------------+-----------------------+-----------
 student_name  | character varying(40) |
 chinese_score | integer               |
 math_score    | integer               |
 test_date     | date                  |

在建表的时候可以指定主键,主键是表中行的唯一标识,不可重复。

CREATE TABLE student(no int primary key, student_name varchar(40), age int);

2. 删除表语句

DROP TABLE table_name;

e.g.

DROP TABLE student;

2. DML 语句

1. 插入语句

INSERT INTO student VALUES(1, 'David', 14);

也可以这样输入:

INSERT INTO student(no, age, student_name) VALUES(2, 13, 'Robert');

还可以这样输入:

INSERT INTO student(no, student_name) VALUES(3, 'Lily');

这样,不插入数据的列会被置空。
使用SELECT查询语句

SELECT * FROM student;

查看到的数据如下:

postgres=# SELECT * FROM student;
 no | student_name | age
----+--------------+-----
  1 | David        |  14
  2 | Robert       |  13
  3 | Lily         |
(3 rows)

2. 更新语句

假设要把 "student" 表中所有学生的年龄(age)更新为 "15" 则更新语句如下:

UPDATE student SET age = 15;

还可以使用过滤表达式WHERE指定更新哪条数据或哪些数据。

UPDATE student SET AGE = 14 WHERE no = 3;

或者同时更新多个列的值。

UPDATE student SET age = 13, student_name = 'Lucy' WHERE no = 3;

3. 删除语句

DELETE FROM student WHERE no = 3;

如果没有 "WHERE" 子句,表明要删除整个表的数据。

DELETE FROM student;

3. DQL 语句

1. 查询

查询 "student" 表中数据的语句为:

select no, student_name from student;

查询 student 表中所有数据的语句为:

select * from student;

过滤条件查询:

SELECT * FROM student where no = 2;

也可以使用表达式语句:

SELECT * FROM student where age >= 13;

2. 排序

使用排序子句对查询出的数据进行排序。

SELECT * FROM student ORDER BY age;

排序子句 "ORDER BY" 应该放到 "WHERE" 子句之后。

SELECT * FROM student WHERE AGE >= 15 ORDER BY student_name;

还可以按多个列进行排列。

SELECT * FROM student ORDER BY age, student_name;

也可以在排序子句的列名后面加DESC进行倒序排列。

SELECT * FROM student DERDER BY age DESC, student_name;

3. 表 join

表 join 也称为多表关联查询。假设有一张班级表 class,建表语句:

CREATE TABLE class(no int primary key, class_name varchar(40));

表中的no表示班级编号,class_name表示班级名称。
插入一些测试数据:

postgres=# CREATE TABLE class(no int primary key, class_name varchar(40));
CREATE TABLE
postgres=# INSERT INTO class VALUES(1, 'ClassOne');
INSERT 0 1
postgres=# INSERT INTO class VALUES(2, 'ClassTwo');
INSERT 0 1
postgres=# INSERT INTO class VALUES(3, 'ClassThree');
INSERT 0 1
postgres=# INSERT INTO class VALUES(4, 'ClassFour');
INSERT 0 1
postgres=# SELECT * FROM class;
 no | class_name
----+------------
  1 | ClassOne
  2 | ClassTwo
  3 | ClassThree
  4 | ClassFour
(4 rows)

还有另一张学生表 student, 建表语句为:

CREATE TABLE student(no int primary key, student_name varchar(40), age int, class_no int);

也插入一些数据:

postgres=# INSERT INTO student VALUES(1, 'Amy', 14, 1);
INSERT 0 1
postgres=# INSERT INTO student VALUES(2, 'Betty', 15, 1);
INSERT 0 1
postgres=# INSERT INTO student VALUES(3, 'Carl', 13, 2);
INSERT 0 1
postgres=# INSERT INTO student VALUES(4, 'Darwin', 15, 2);
INSERT 0 1
postgres=# INSERT INTO student VALUES(5, 'Emma', 15, 3);
INSERT 0 1
postgres=# INSERT INTO student VALUES(6, 'Faraday', 14, 3);
INSERT 0 1
postgres=# INSERT INTO student VALUES(7, 'Grey', 15, 4);
INSERT 0 1
postgres=# INSERT INTO student VALUES(8, 'Helen', 14, 4);
INSERT 0 1
postgres=# SELECT * FROM student;
 no | student_name | age | class_no
----+--------------+-----+----------
  1 | Amy          |  14 |        1
  2 | Betty        |  15 |        1
  3 | Carl         |  13 |        2
  4 | Darwin       |  15 |        2
  5 | Emma         |  15 |        3
  6 | Faraday      |  14 |        3
  7 | Grey         |  15 |        4
  8 | Helen        |  14 |        4
(8 rows)

若现在想查询出每个学生与班级的关系,那么就需要关联查询两张表:

SELECT student_name, class_name FROM student, class WHERE student.class_no = class.no;

查询结果如下:

 student_name | class_name
--------------+------------
 Amy          | ClassOne
 Betty        | ClassOne
 Carl         | ClassTwo
 Darwin       | ClassTwo
 Emma         | ClassThree
 Faraday      | ClassThree
 Grey         | ClassFour
 Helen        | ClassFour
(8 rows)

表关联查询就是在WHERE条件上加上需要关联的条件(两张表关联):

WHERE student.class_no = class.no;

由于在两张表中,有一些列的名称是重复的,如在表 student 中 "no" 表示学生号,而在表 class 中表示班级号,所以在关键条件中要明确使用“表名”加“列名”来唯一定位这个列。如果输入的表名比较长,不是很方便,这时可以给表起个别名,如下所示:

SELECT student_name, class_name FROM student a, class b WHERE a.clsss_no = b.no;

上面的语句中,给表 student 起的别名为 "a",表 class 的别名为 "b",这时条件表达式中 "b.no" 就代表了表 class 中的 "no" 列。
在关联查询的 "WHERE" 子句中可以再加上其他过滤条件,如:

postgres=# SELECT student_name, class_name FROM student a, class b WHERE a.class_no = b.no AND a.age > 14;
 student_name | class_name
--------------+------------
 Betty        | ClassOne
 Darwin       | ClassTwo
 Emma         | ClassThree
 Grey         | ClassFour
(4 rows)

4. 其他 SQL 语句

1. INSERT INTO... SELECT 语句

使用此语句可以把数据从一张表插入到另一张表中,这个语句属于 DML 语句。
先建立一张学生表的备份表: student_bak

CREATE TABLE student_bak(no int primary key, student_name varchar(40), age int, class_no int);

再利用INSERT INTO... SELECT语句把数据插入到 student_bak 中。

postgres=# INSERT INTO student_bak SELECT * FROM student;
INSERT 0 8
postgres=# SELECT * FROM student_bak;
 no | student_name | age | class_no
----+--------------+-----+----------
  1 | Amy          |  14 |        1
  2 | Betty        |  15 |        1
  3 | Carl         |  13 |        2
  4 | Darwin       |  15 |        2
  5 | Emma         |  15 |        3
  6 | Faraday      |  14 |        3
  7 | Grey         |  15 |        4
  8 | Helen        |  14 |        4
(8 rows)

2. UNION 语句

可以将从两个表查询出来的数据整合在一个结果集合下面,如:

SELECT * FROM student WHERE no = 1 UNION SELECT * FROM student_bak where no = 2;

注意,UNION可以把结果集中相同的两条记录合并成一条:

SELECT * FROM student WHERE no = 1 UNION SELECT * FROM student_bak where no = 1;

如果不想合并,请使用UNION ALL,如下:

SELECT * FROM student WHERE no = 1 UNION ALL SELECT * FROM student_bak where no = 1;

3. TRUNCATE TABLE 语句

TRUNCATE TABLE 语句的用途是清空表内容,相当于重新定义一个新表的方法把原来的内容直接丢弃了,因此执行速度快。不带 WHERE 条件子句的 DELETE 语句也表示清空内容,如果要删除许多数据,就会比较慢。
如果想清理 sutdent_bak 中的数据,可以使用以下命令:

TRUNCATE TABLE student_bak;

[TOC]

本资料为《PostgreSQL 修练之道 从小工到专家》的学习笔记,以备查阅,权侵删。

参考资料

[1]《PostgreSQL 修练之道 从小工到专家》 唐成 机械工业出版社
[2] 在原生 Windows 上安装和运行 PostgreSQL


  1. [注]: http://blog.chinaunix.net/uid-354915-id-3498734.html