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,2)
into stops3
FROM
public.road r,
public.roadwithstop2 s ;
参考：http://www.jb51.cc/article/p-rtwnzcdt-bdh.html
1.3 关系设计原则
1.3.1 将数据拆分成列
不要将不同的数据放在同一列中,尤其是不同数据类型的;
1.3.2 用唯一标记来作为键
如果表中需要多列进行标识,可能设计出现问题;
1.3.3 移除重复信息
如果出现重复信息,可以考虑进行分表设计;
1.3.4 正确的命名
保持风格一致
尽量简单;
1.4 数据类型
integer:整型;
char:定长字符串;
varchar:变长字符串;
numeric:数字型;
NULL:未知型(不是0,不是空字符串);
2 Postgresql：开源关系数据库
方法：
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文件输入
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名称’);
设置值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:重新查询缓冲区命令;
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
