PostgreSQL数据库学习--基础

很久没有写日志了，由于之前忙着换工作，从福州回到南京，从网络设备嵌入式行业转到了软件行业，虽然也不知道是否是正确的选择，不过真的对于做嵌入式linux的两年没有写过驱动，会被很多公司鄙视的，可能是我能力不行，待遇真的没有想象的提高，甚至可以说比原来的福利待遇还要差一点儿，不过朝九晚五的生活还是很舒服的。新工作也进行了差不多一个月的时间，和同事的相处中大概了解到目前的同事大部分都是有5年以上工作经验的，都有一定的项目经验，未来应该能和他们的相处中收获不少，朝九晚五的工作方式说实在的目前我还觉得不是很适应。不过开源的思路在目前的团队中非常的盛行，大部分的开发都是在开源软件基础上修改，更多的是作为集成，我认为这对于软件的理解更深刻。

这段时间主要是学习PostgreSQL数据库的相关部署、管理等工作，说实在的一直在进行资料学习，当然是理论和实践相结合的方式啦。我们的目标主要是实现PostgreSQL的扩展，作为数据中心的数据库承载。减少目前对专业数据库的依赖。不过PostgreSQL在这个方面的还是有一定的局限性。Postgres-XL是一个基于PostgreSQL的数据库集群。在学习Postgres-XL之前总结一下PostgreSQL的知识。

由于postgreSQL中只能通过连接数据库然后再操作，因此在初始化的过程中采用复制模板的方式创建了三个数据库，分别是template1,template0和postgres，其中前两个为模板数据库，特别是template1是其他数据库创建的默认模板。而postgres是作为用户操作的第一个连接数据库，也是通过模板生产，即通过这个数据库就能进行数据库相关的操作。
      psql -p 5432 -U test -h localhost -d postgres
      或者直接psql -p xxxx postgres
这些都是依据实际情况进行尝试。

关于psql的使用

psql的shell命令:

    -c cmd 运行一条sql命令

    -d dbname 连接到对应的数据库

    -f filename 执行文件中的命令

    -l 列出所有的数据库，然后退出

    -h 数据库服务器的host或者socket目录

    -p 数据库服务器的端口号

    -U 数据库的用户名

    -w 不需要密码

    -W 强制输入密码

   

psql环境下的常用命令:

    \? 列出psql内部的所有命令

    \i 执行文件中的命令

    \o 发送查询结果到文件

    \d[S+] 列出表，视图，序列

    \d[S+] name 描述表，视图，序列，索引

    \da 列出聚合函数

    \db 列出表空间

    \dg \du 列出角色

    \di 列出索引

    \dp 列出权限

    \dt 列出表

    \du 列出用户

    \l 列出所有的数据库

    \c dbname user host port 连接数据库

    \conninfo 连接的相关信息

一个 PostgreSQL数据库集群包含一个或多个命名的数据库。用户和用户组在整个集群的范围内是共享的，但是其它数据并不是共享的。任何给定的与服务器的客户连接都只能访问在一个数据库里的数据，就是那个在连接请求里声明的。一个数据库包含一个或多个命名的模式， 模式又包含表。模式还包含其它命名的对象，包括数据类型，函数， 以及操作符。同一个对象名可以在不同的模式里使用而不会导致冲突；和数据库不同，模式不是严格分离的： 一个用户可以访问他所连接的数据库中的任意模式中的对象， 只要他有权限。

模式的好处:

      >> 模式实际上允许多个用户使用同一个数据库而不会干扰其他用户，实现了多用户下的隔离。

      >> 把数据库对象组织成逻辑组，便于管理

      >> 第三方应用可以放在不同的模式中，使得它们不会和

其他的对象冲突。

要创建一个模式，使用命令 CREATE SCHEMA。 给出你选择的模式名字。

      CREATE SCHEMA myshema；

      CREATE SCHEMA schemaname AUTHORIZATION username;

如果一个模式是空的（所有它里面的对象都已经删除），那么删除一个模式的命令

      DROP SCHEMA myschema;

要删除一个包含所有对象的模式，使用

      DROP SCHEMA myschema CASCADE;

在应用程序中通常采用不包含模式的表，当多个模式中存在同名的表时，就需要一种默认的方式指明具体的表。在PostgreSQL中就是通过搜索路劲来判断该表究竟是哪张表。搜索路劲就是一个需要查找的模式列表，搜索路劲中找到的第一个表就被当做选定的表。如果没有匹配的表就报错误，即使匹配表的名字在其他的模式（不在搜索路劲中）中也会报错。通过SHOW search_path查看。因此在添加了对应的模式下后，可能需要重新设置搜索路劲，否则可能无法直接查找到模式下的表。可以通过设置：

      SET search_path TO "$user",public,newaddschema;

默认情况下，search_path是"$user",public。即在缺省的设置中搜索路劲（search_path）的第一个元素声明将要搜索一个和当前用户同名的模式。 因为还没有这样的模式存在，所以这条记录被忽略。第二个元素指向我们已经看过的公共模式。搜索路径中存在的第一个模式是创建新对象的缺省位置。 这就是为什么缺省的对象都会创建在 public 模式里的原因。如果在任何其它环境中引用对象，而且没有用模式修饰 （表修改，数据变更，或者查询命令），那么系统会遍历 搜索路径，直到找到一个匹配的对象。因此，在缺省的配置里， 任何未修饰的访问同样也只能引用 public 模式。

由于模式的作用主要是实现多用户的分隔，因此关于模式是需要权限的，用户通常是无法看到模式下不属于他们的对象。因此需要添加对应的权限。

除了public和用户创建的模式之外，每个数据库都包含一个pg_catalog模式，该模式包含系统表和所有内置数据类型，函数和操作符。因此pg_catalog模式总是搜索路劲的一部分，只是隐含地在搜索路劲之前搜索。

关于模式:

      >> 如果未创建模式，则所有用户都访问public模式。

      >> 为每个用户创建一个模式与该用户的用户名相同，由于默认的搜索路劲会优先搜索"$user"（即用户名）,如果将public取消，那么用户就真的限制在自己的模式中。

      >> 安装共享的应用可以把相关的数据对象放在独立的模式中，只需要为所有共享的用户之间授予权限即可。

标准的SQL中同一个模式里的对象由不同的用户所拥有的概念是不存在的。

继承
PostgreSQL实现了表的继承。主要是用于减少一些依赖关系的父子表之间的查询等问题。以下是基本的测试过程:

xxxx[xxxx@postgres]> create table city (name text, population float, altitude int);
CREATE TABLE
xxxx[xxxx@postgres]> \d city;
            Table "public.city"
   Column   |       Type       | Modifiers 
------------+------------------+-----------
 name       | text             | 
 population | double precision | 
 altitude   | integer          | 


xxxx[xxxx@postgres]> 
xxxx[xxxx@postgres]> 
xxxx[xxxx@postgres]> create table capitals(state char(2)) INHERITS(city);
CREATE TABLE
xxxx[xxxx@postgres]> \d capitals;
          Table "public.capitals"
   Column   |       Type       | Modifiers 
------------+------------------+-----------
 name       | text             | 
 population | double precision | 
 altitude   | integer          | 
 state      | character(2)     | 
Inherits: city
从上述的显示可知，在capital表中实际上包含了city的字段，其中capitals中只是添加了一个字段，但在表的描述中却直接继承了父类的成员。因此子表是比父表属性更多的表。


//插入数据到city表中
xxxx[xxxx@postgres]> insert into city(name, population, altitude) values ('zhangjiajie', 1.2, 1000), ('suzhou', 45, 20);
INSERT 0 2
//插入数据到capitals表中
xxxx[xxxx@postgres]> insert into capitals(name, population, altitude, state) values ('changsha', 102, 800, 'HN'), ('nanjing', 950, 30, 'JS');
INSERT 0 2
//查询city表, 其中数据包含了子表中的数据
xxxx[xxxx@postgres]> select * from city;
    name     | population | altitude 
-------------+------------+----------
 suzhou      |         45 |       20
 zhangjiajie |        1.2 |     1000
 changsha    |        102 |      800
 nanjing     |        950 |       30
(4 rows)
//查询子表，只包含子表中的数据
xxxx[xxxx@postgres]> select * from capitals;
   name   | population | altitude | state 
----------+------------+----------+-------
 changsha |        102 |      800 | HN
 nanjing  |        950 |       30 | JS
(2 rows)
//ONLY字段，只从父表中查询数据, 只包含了父表中的数据，并不包含子表中的数据。
xxxx[xxxx@postgres]> select * from only city;
    name     | population | altitude 
-------------+------------+----------
 suzhou      |         45 |       20
 zhangjiajie |        1.2 |     1000
(2 rows)


//更新父表中的数据属性
xxxx[xxxx@postgres]> update city SET population = 3 where name = 'zhangjiajie';
UPDATE 1
//通过父表更新子表中的数据成员
xxxx[xxxx@postgres]> update city SET population = 450 where name = 'changsha';
UPDATE 1
xxxx[xxxx@postgres]> select * from only city;
    name     | population | altitude 
-------------+------------+----------
 suzhou      |         45 |       20
 zhangjiajie |          3 |     1000
(2 rows)


xxxx[xxxx@postgres]> select * from city;
    name     | population | altitude 
-------------+------------+----------
 suzhou      |         45 |       20
 zhangjiajie |          3 |     1000
 nanjing     |        950 |       30
 changsha    |        450 |      800
(4 rows)
//删除父表中的数据
xxxx[xxxx@postgres]> delete from city where name = 'suzhou';
DELETE 1
//通过父表删除子表中的数据
xxxx[xxxx@postgres]> delete from city where name = 'nanjing';
DELETE 1
xxxx[xxxx@postgres]> select * from city;
    name     | population | altitude 
-------------+------------+----------
 zhangjiajie |          3 |     1000
 changsha    |        450 |      800
(2 rows)

在 PostgreSQL 里， 一个表可以从零个或多个其它表中继承属性，而且一个查询既可以引用一个表中的所有行， 也可以引用一个表的所有行加上所有其后代表的行。如需要只查询、更新、删除父表中的数据，可以采用ONLY关键字，该关键字保证删除操作只在父表中进行，而不会子表中进行。

根据之前的描述可知，对于表中的对象都存在一些系统属性，即所谓的表的系统列，其中包含了对象标识符(oid)、所属的表对象标识符(tableoid)，因此在存在继承关系的一系列表中，可以通过查询tableoid标识该对象所归属的表。
xxxx[xxxx@postgres]> select c.tableoid, c.name, c.population, c.altitude from city c where altitude < 2000;
 tableoid |    name     | population | altitude 
----------+-------------+------------+----------
    16397 | zhangjiajie |          3 |     1000
    16403 | changsha    |        450 |      800
从上面两个不同的tableoid即可知道当前的数据来自多个不同的表。

一个表可以从多个父表继承，这种情况下它拥有父表们的字段的总和。这种情况将出现如果多个表中存在相同的字段，或者同时出现在父表和子表的定义中，这些字段将被融合，即新子表中将只有一个这样的字段，但是想要融合，字段必须是相同的数据类型，负责会抛出错误。所有父表的检查约束都会自动被所有子表继承。 不过其它类型的约束没有被继承。

继承定义了之后，没有办法从一个子表上删除一个继承关系， 除非是删除整个表。如果子表存在，则不能删除父表。 如果你想删除一个表和其所有后代，一个简单的方法是用 CASCADE 选项删除全部表。
xxxx[xxxx@postgres]> drop table city; //不能直接删除父表
ERROR:  cannot drop table city because other objects depend on it
DETAIL:  table capitals depends on table city
table subcity depends on table capitals
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
xxxx[xxxx@postgres]> drop table city CASCADE; //递归的形式删除父表、子表
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table capitals
drop cascades to table subcity
DROP TABLE

注意: 权限并不是自动继承的，企图访问一个父表的用户必须要么对子表有相同的权限， 要么必须使用 "ONLY" 表示法。或者在现有的系统里创建一个新的继承关系。继承特性的一个严重的局限性是索引（包括唯一约束）和外键约束只施用于单个表， 而不包括它们的继承的子表。

分区
     PostgreSQL 支持基本的表分区功能,分区的意思是把逻辑上的一个大表分割成物理上的几块儿,具有如下的好处:
     >> 某些类型的查询性能提升，减少了所有的数据集合。
     >> 更新的性能可以提升，表每块的索引要比整个数据集的索引小，索引如果不存放在内存中，索引的读写操作也会产生大量的磁盘访问。
     >> 批量删除只需要删除某个分区，减少操作数据的集合空间。
     >> 少用的数据可以存放在更慢的介质中。
      分区通常只有在大数据量的情况下才有价值。通常如果表的大小超过了数据库服务器的物理内存大小，通常就可以考虑分区。PostgreSQL支持通过表继承进行分区，每个分区必须作为单独一个父类的子表创建，而父表通常是空的，存在只是为了代表整个数据集。这种实现主要依据了在PostgreSQL中的SELECT，UPDATE，DELETE操作都可以通过父类直接操作，通常分区主要进行上述三个操作，分区子类的操作也可以直接通过父类操作。

前端和后端概念

       PostgreSQL采用基于消息的协议用于前后端通信(客户端和服务器)。该协议是在TCP/IP和Unix domain-socket之上的。端口5432已经在IANA(互联网地址编码分配机构)注册，通常作为服务器TCP的端口。但不限于该端口。 为了有效的服务多客户，服务器为每个客户端启动了新的后端程序，目前的实现中，当新的连接检测到以后会立即创建一个新的子进程。通常后端和服务器可交换，就像前端和客户端可交换。因此后端实际上是指该创建出来的子进程。在客户端退出之后，后端也会退出。

对象标识符(OID)

       PostgreSQL 在内部使用对象标识符（OID）作为各种系统表的主键。 同时，系统不会给用户创建的表增加一个 OID 系统字段（除非在建表时声明了 WITH OIDS 或者是配置参数 default_with_oids 设置成了真）。 类型 oid 代表一个对象标识符。目前 oid 类型是用一个无符号的四字节整数实现的。 因此，它是不够用于提供大数据库范围内的唯一性保证的， 甚至在单个的大表中也不行。因此，我们不鼓励在用户创建的表中使用 OID 字段做主键。OID 最好只是用于引用系统表。