随着大数据越炒越热,postgresql作为开源数据库代表之一扮演着越来越重要的角色,个人感觉必须要自学一把,今天开始持续更新,一边学习一边记录。
一.首先什么是postgresql
PostgreSQL is an object-relational database management system (ORDBMS) based on
POSTGRES, Version 4.2
, developed at the University of California at Berkeley Computer Science Department。
先不管伯克利什么的,postgresql是属于ORDBMS,即面向对象数据库管理系统,区别于oracle、db2等关系型数据库(RDBMS),ORDBMS面向的是对象,但本质上也是属于关系型数据库,其广泛应用于地理环境相关数据库业务中。
PostgreSQL is an open-source descendant of this original Berkeley code. It supports a large part of the SQL standard and offers many modern features:
- complex queries
- foreign keys
- triggers
- views
- transactional integrity
- multiversion concurrency control
Also, PostgreSQL can be extended by the user in many ways, for example by adding new
- data types
- functions
- operators
- aggregate functions
- index methods
- procedural languages
看起来和大多数数据库一样,可以复杂查询、外键、触发器、视图、事务完整性、多版本并发控制、可以创建数据类型、函数、操作符、聚合函数、索引、结构语言等。
二.postgresql基本功能
2.1 创建数据库
首先了解下postgresql的结构,和绝大多数数据库一样,是server/client结构
我在官网上下载了postgresql的rpm包可以直接安装,os是centos6.5
安装结束后初始化数据库、启动数据库
#service postgresql-9.4 initdb
#/etc/init.d/postgresql-9.4 start
此时我们能看到默认端口5432的监听起来了
安装好rpm包后会自动创建postgres超级用户,切换到postgres账户上创建一个数据库wyzpostgre
createdb wyzpostgre
当然我们作为练习可以创建个用户练习
登录数据库(必须要安装client,这一步我们再之前就做过了)
psql wyzpostgres
登录数据库后大家已经迫不及待的敲出命令了,好的,我们先查一下版本号吧
wyzpostgres=# SELECT version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
(1 row)
wyzpostgres=# SELECT current_date;
date
------------
2015-12-28
(1 row)
和其他sql语言不同,如果要查询帮助或者退出之类的,是以反斜杠开头
\q 则是退出
2.2 创建表
wyzpostgres=# CREATE TABLE weather (
wyzpostgres(# city varchar(80),
wyzpostgres(# temp_lo int, -- low temperature
wyzpostgres(# temp_hi int, -- high temperature
wyzpostgres(# prcp real, -- precipitation
wyzpostgres(# date date
wyzpostgres(# );
--表示注释的内容可以作为说明,已分号结束。
好的我们已经有了weather这个表了,再来一个city
wyzpostgres=# CREATE TABLE cities (
wyzpostgres(# name varchar(80),
wyzpostgres(# location point
wyzpostgres(# );
注意,location这个变量的类型是point,这是postgres特有的类型,用于地理位置。
(删除表的命令很简单,drop table xxx)
2.3 向表中添加行
wyzpostgres=# INSERT INTO weather VALUES ('NanJing', 46, 50, 0.25, '2016-12-28');
wyzpostgres=# INSERT INTO cities VALUES ('NanJing', '(-194.0, 53.0)');
2.4 查询表
wyzpostgres=# SELECT * FROM weather;
查询的命令就是sql语言,可以使用as、order by等等方面查看结果,当然也包含内连接、外连接、全连接 等等
聚合函数
如果想查询某个城市最高温度,也许我会
wyzpostgres=# SELECT city FROM weather WHERE temp_lo = max(temp_lo);
ERROR: aggregate functions are not allowed in WHERE
LINE 1: SELECT city FROM weather WHERE temp_lo = max(temp_lo);
看到提示了,聚合函数不能在where的子句中
那么只能这样通过子链接查询
2.5 更新
wyzpostgres=# update weather set temp_hi=9 where temp_lo=-1;
2.6 删除
delete from weather where city='GuangZhou';
三. postgresql高级功能
3.1 视图
视图可以方便查询,避免每次查询键入过多的选项
3.2 外键
外键的主要作用就是维护表的完整性,比如在weather中插入一条city中没有匹配的记录行,外键可以帮助我们来作限制使得这条插入命令无法成功。
The new declaration of the tables would look like this:
CREATE TABLE cities ( city varchar(80) primary key, location point);CREATE TABLE weather ( city varchar(80) references cities(city), temp_lo int, temp_hi int, prcp real, date date);
Now try inserting an invalid record: