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 列出表，视图，序列
\d 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> create table city (name text, population float, altitude int);
CREATE TABLE
xxxx> \d city;
Table "public.city"
Column | Type | Modifiers
------------+------------------+-----------
name | text |
population | double precision |
altitude | integer |
xxxx>
xxxx>
xxxx> create table capitals(state char(2)) INHERITS(city);
CREATE TABLE
xxxx> \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> insert into city(name, population, altitude) values ('zhangjiajie', 1.2, 1000), ('suzhou', 45, 20);
INSERT 0 2
//插入数据到capitals表中
xxxx> insert into capitals(name, population, altitude, state) values ('changsha', 102, 800, 'HN'), ('nanjing', 950, 30, 'JS');
INSERT 0 2
//查询city表, 其中数据包含了子表中的数据
xxxx> select * from city;
name | population | altitude
-------------+------------+----------
suzhou | 45 | 20
zhangjiajie | 1.2 | 1000
changsha | 102 | 800
nanjing | 950 | 30
(4 rows)
//查询子表，只包含子表中的数据
xxxx> select * from capitals;
name | population | altitude | state
----------+------------+----------+-------
changsha | 102 | 800 | HN
nanjing| 950 | 30 | JS
(2 rows)
//ONLY字段，只从父表中查询数据, 只包含了父表中的数据，并不包含子表中的数据。
xxxx> select * from only city;
name | population | altitude
-------------+------------+----------
suzhou | 45 | 20
zhangjiajie | 1.2 | 1000
(2 rows)
//更新父表中的数据属性
xxxx> update city SET population = 3 where name = 'zhangjiajie';
UPDATE 1
//通过父表更新子表中的数据成员
xxxx> update city SET population = 450 where name = 'changsha';
UPDATE 1
xxxx> select * from only city;
name | population | altitude
-------------+------------+----------
suzhou | 45 | 20
zhangjiajie | 3 | 1000
(2 rows)
xxxx> select * from city;
name | population | altitude
-------------+------------+----------
suzhou | 45 | 20
zhangjiajie | 3 | 1000
nanjing | 950 | 30
changsha | 450 | 800
(4 rows)
//删除父表中的数据
xxxx> delete from city where name = 'suzhou';
DELETE 1
//通过父表删除子表中的数据
xxxx> delete from city where name = 'nanjing';
DELETE 1
xxxx> select * from city;
name | population | altitude
-------------+------------+----------
zhangjiajie | 3 | 1000
changsha | 450 | 800
(2 rows)
在 PostgreSQL 里， 一个表可以从零个或多个其它表中继承属性，而且一个查询既可以引用一个表中的所有行， 也可以引用一个表的所有行加上所有其后代表的行。如需要只查询、更新、删除父表中的数据，可以采用ONLY关键字，该关键字保证删除操作只在父表中进行，而不会子表中进行。
根据之前的描述可知，对于表中的对象都存在一些系统属性，即所谓的表的系统列，其中包含了对象标识符(oid)、所属的表对象标识符(tableoid)，因此在存在继承关系的一系列表中，可以通过查询tableoid标识该对象所归属的表。
xxxx> 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> 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> 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 最好只是用于引用系统表。 好资源，必须支持楼主。
页:
[1]