Chinaunix首页 | 论坛 | 认证专区 | 博客 登录 | 注册

GP-Kinggpking.blog.chinaunix.net

专注Linux、分布式存储、云计算

  • 博客访问: 1164306
  • 博文数量: 160
  • 博客积分: 3730
  • 博客等级: 上尉
  • 技术积分: 3661
  • 用 户 组: 普通用户
  • 注册时间: 2011-06-02 14:36
个人简介

减肥,运动,学习,进步...

文章分类

全部博文(160)

文章存档

2016年(14)

2015年(17)

2014年(17)

2013年(4)

2012年(73)

2011年(35)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
PostgreSQL数据库学习--基础 2015-06-09 23:06:43

分类: Mysql/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 最好只是用于引用系统表。

阅读(4359) | 评论(0) | 转发(2) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册