PostgreSQL数据库

PostgreSQL数据库

2012年1月19日

2015年6月8日 重构

2015年7月3日 添加类型转换

1  数据库基本常识

1.1 概述

1.1.1 扁平文件(PlainText:简单文本)

是一种用于文本存储的,没有特别格式的,仅以简单文件做为存储内容的文件格式;

1.2 SQL操作

1.2.1 选择

查询表的子行

select * fromstudent;

1.2.2 投影(选择某些列)

选择某些列;selectfname,lname from student;

1.2.3 Join(连接)

目标：多个表联合查询。

方法：innerjoin,left join, right join,full join。

inner join(内连接,默认join)：必须保证匹配才能返回。

left join(左连接)：全部返回左表，及与左表匹配的右表数据。

right join(右连接)：全部返回右表，及与右表匹配的左表数据。

full join(全连接)：left+ right join。

参考：http://www.w3school.com.cn/sql/sql_join_left.asp

示例：

SELECTPersons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

LEFT JOIN Orders

ONPersons.Id_P=Orders.Id_P

ORDER BY Persons.LastName

1.2.4 distinct:去除重复行

可以去除重复的数据,但在实用中,应尽量减少这种用法,它可能会隐藏错误的输入,并会拖慢查询速度,应在十分确定的情况下使用;

1.2.5 排序的问题

对于字符串则以空白填充不足的字符数;

按照ASCII码进行排序,空白在前,A-Za-z;

1.2.6 将查询结果保存为新表：select 查询列into 新表名where…

示例：

SELECT

 st_astext(ST_CollectionExtract(st_split(r.geom,s.the_geom),2)),ST_CollectionExtract(st_split(r.geom,s.the_geom),2)

into stops3

FROM

  public.road r,

  public.roadwithstop2 s  ;

参考：http://blog.csdn.net/longshenlmj/article/details/17719323

 

1.3 关系设计原则

1.3.1 将数据拆分成列

不要将不同的数据放在同一列中,尤其是不同数据类型的;

1.3.2 用唯一标记来作为键

如果表中需要多列进行标识,可能设计出现问题;

1.3.3 移除重复信息

如果出现重复信息,可以考虑进行分表设计;

1.3.4 正确的命名

保持风格一致

尽量简单;

1.4 数据类型

integer:整型;

serial:序列号,自动增加;

char:定长字符串;

varchar:变长字符串;

numeric:数字型;

NULL:未知型(不是0,不是空字符串);

2  PostgreSQL：开源关系数据库

目标：使用SQL操作的关系型数据库。

方法：

2.1 数据库操作

2.1.1 数据库配置：配置用户名、密码。

pg_hba.conf，注意：md5方式以密文发送密码，有可能无法解析，可以使用trust来发送明文。

2.1.2 创建数据库

使用createdb命令创建新的数据库

createdb–U username dbname

2.1.3 连接数据库

进入postgresql->bin目录下;

所有的命令程序都在这个目录下;

psql–U username –d dbName;

2.1.4 切换数据库

\c dbnameusername

2.2 创建新用户

使用createuser命令可以创建新用户;

createuser–U 当前用户 –P newuser

2.3 进行SQL操作

2.3.1 直接输入sql语句,这时要注意,以分号结束;

2.3.2 使用.sql文件输入

这时要注意sql文件的编码,Unicode编码不能识别;

2.4 基本的PSQL命令

\?  :帮助;

\h cmd:sql 中cmd的帮助;

\dt:显示所有表

\do:显示操作符的定义;

\dT:显示类型的定义;

\r:重置缓冲器;

\q:退出;

以上只是一小部分内容,具体参看\?给出的列表;

2.5 扩展功能：create Extension 功能名

2.5.1 Postgis功能

安装postgis相关表和函数。

psql -f../share/extension/postgis.sql

添加扩展功能：createExtension postgis;

2.5.2 添加pgRouting功能：create Extension pgRouting;

参考：http://workshop.pgrouting.org/chapters/installation.html

2.6 重启服务：pg_ctl

在postgresql的bin目录下，使用pg_ctr start -D ../data 可以重新启动数据库。

服务启动后可以正常连接。

参考：http://my.oschina.net/dyx1024/blog/28312

3  SQL

3.1 DML:数据操作语言

用来进行数据操作;

3.1.1 select 重名命列;

select colname<as> newName,… from tabName;(newName可以使用双引号包括,但不能使用单引用)

3.1.2 select 排序

selectcol1,col2  … from tabname order by col1asc,col2 desc…;

3.1.3 select between:域查询

select * fromtablename where colname between low and high;

对于字符串则以空白填充不足的字符数;

3.1.4 模糊查询:like

%:代表任意字符串;

_:代表单个任意字符;

3.1.5 限制输出:limit

limit:限制数目;

offset:开始位置;

3.1.6 null测试

使用 is或is not;普通比较使用=;

3.1.7 日期格式设置

3.1.7.1 输入输出格式

3.1.7.1.1pg中一共有四种显示格式:

ISO:2012-1-31;

SQL:1/31/2012;(默认样式)

Postgres:WedDec 31;

German:31.1.2012;

3.1.7.1.2有三种输入格式

DMY:European,Euro

MDY:US,NonEuro,NonEuropean;

YMD:ISO;

3.1.7.2 转换函数

cast(‘string’as date);

cast(‘string’as timestampe);

3.1.7.3 设置日期输入输出格式

不管设置成什么格式,iso的格式都可以用于输入;

3.1.7.3.1set datastyleto ‘input,output’;(input,output的顺序不重要)

3.1.7.3.2也可以通过更改postgresql.conf(..>data>postgresql.conf)datestyle变量设置整个数据库的格式;

3.1.7.4 常用函数

3.1.7.4.1date_part(‘month’,colname):

获取日期中的一部分(year,month,day,hour,minute,second);

3.1.7.4.2now():获取当前时间日期;

3.1.7.5 日期运算

需要使用cast转换后进行运算;

3.1.8 SQL92 Join ON新方法:只是where语句的分担模式

select * fromtab1 join tab2 on tab1.col1=tab2.col1;

3.1.9 聚集函数

使用聚焦函数时,可以使用group by和having子句;

group by:分组;

having:目标列中的条件;

3.1.10 子查询

对于返回单个值的select语句,可以与常量一样使用;

对于返回多个值的select语句,使用in进行范围设定;

3.1.11 类型转换:cast(express As type)或者：：

参考：http://stackoverflow.com/questions/15537709/what-does-do-in-postgresql

http://www.postgresql.org/docs/current/static/sql-expressions.html

3.2 DDL:数据定义语言

用来控制数据库的结构定义;

创建数据库:

createdb –U username–d dbName;

createuser –U username –P newuser

3.2.1 创建表:

create tabletablename

(

        columnname type constraint,

        columnname type constraint,

        columnname type constraint

)

3.2.2 删除表:

drop tabletablename;

3.2.3 填充表

insert inttablename values(表中所有列的数据);

insert intotablename(column1,column2…) values(col1,col2,…);

注意:

1.  使用单引号包围字符串;

2.  \转义一些特殊字符;

3.  空值为NULL,不带单引号;

3.2.4 Serial字段控制

Serial这种序列字段在PgAdminIII中被定义为序列,以表名-列名-seq命名;

当前值currval(‘seq名称’);

生成下一个值nextval(‘seq名称’);

设置值setval(‘seq名称’,value);

3.2.5 copy从其它文件中直接插入数据库

\copy tablenamefrom ‘filepath’ using delimiters ‘seprator’ with NULL as ‘nullString’

3.2.6 insert select:从查询中插入

insert into table(col1,…) select …;

注意:

插入时,一般要设置一个中间过渡表,使其具有与目的表的列,并增加一个bool型的更新标志,只有正确的加入的数据才被插入原始表;然后将这些数据删除;

3.2.7 更新数据

update tabname set colname=value where …;

注意:更新之前查看where的条件对应多少行数据;

3.2.8 使用其它表的数据更新数据

update tabname set colname=value from othertabwhere …;

3.2.9 删除数据

3.2.9.1  delete from tablename where…;

3.2.9.2  truncate:永久删除,不可恢复,高效;

truncate Table tabname;

3.3 DCL:数据控制语言

用来进行数据库访问控制;

4  PSQL

psql类似于oracle sql*plus,是pg的命令行工具;

4.1 初始化文件是.psqlrc

=#:拥有全部功能;

=>:只拥用数据使用功能;

4.2 psql命令语法

psql option dbnameusername

4.3 内部命令

\p:缓冲区命令;

\r:清除缓冲区;

\e:打开外部编辑器(默认使用记事本);

\g:重新查询缓冲区命令;

\s:保存缓冲区内容(分版本,可能不被支持);

\i:从.sql文件中读取命令;

\o:输出为文件;

 

 

5  ODBC:

odbc是一种开放的数据库连接程序;历史较久,由ODBC进行数据库统一管理,从而将具体的数据库与客户端程序分离;

5.1 安装ODBC驱动;

下载psqlodbc.msi,完成安装;

5.2 在控制面版->管理工具->ODBC数据源中添加PostgreSQL;

根据数据库的地址,名称和端口,用户名等信息,填充下面的对话框;使用Test测试成功后,添加到ODBC数据源中;

5.3 ACCESS使用ODBC获取数据

5.4 Excel使用ODBC获取数据

6  pgAdminIII:是psql的图形用户界面;

6.1 建立数据库连接:

在建立数据库连接之前,应添加一个代表当前网络的IP地址作为服务器地址;在客户端访问配置中,IP地址使用CIDR方式进行处理;这里应使用/32掩码代表唯一一个IP;

6.2 CIDR

classless interdemain routing无类型内部路由选择;在ip后用1的位数表示掩码;

192.168.1.0/24;代表域内192.168.1.0~192.168.1.255

6.3 备份

右击需要备份的数据内容,选择备份位置及选项,确定就可以进行数据库备份了.