PostgreSQL学习笔记

一、安装

Centos:

添加RPM：

-- centos 6 安装 postgresql 10

yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-6-x86_64/pgdg-centos10-10-2.noarch.rpm

 

-- centos 7 安装 postgresql  10

yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

安装客户端：

yum install postgresql10

安装服务端：

yum install postgresql10-server

初始化数据库：

Postgresql安装目录是/usr/pgsql-10,而Postgresql的数据目录是/var/lib/pgsql/版本号/data目录

在这里，如果在装系统开始分配var空间足够大则可以继续，如果分配var空间不够，我们需要更改数据目录，在这里，我们假设var空间足够大。直接开始初始化。

/usr/pgsql-10/bin/postgresql-10-setup initdb

启动数据并设置开机启动：

sudo systemctl start postgresql-10

sudo systemctl enable postgresql-10.service

二、创建用户和数据库

# su - postgres -- 切换到postgres（默认用户）

-bash-4.2$ psql  -- 进入数据库
psql (10.7)
Type "help" for help

创建用户：

postgres=# create user username with password '****';

CREATE ROLE

修改密码：

postgres=# ALTER USER postgres WITH PASSWORD '***';
ALTER ROLE

将数据库得权限，全部赋给某个用户

postgres=# grant all on database dbtest to username; -- 将dbtest所有权限赋值给username

GRANT

导入整个数据库

psql -U username databasename < /data/dum.sql -- 用户名和数据库名

$ su - postgres   #切换账号
$ psql --version     #psql版本信息
$ psql -l            #查看databases列表
$ createdb test      #创建test库
$ psql test          #进入test库
> help               #帮助信息
> \h
> \?
> \l
> \x                 #翻转显示
> \q                 #退出数据库
> select now();      #当前时间
> select version();  #系统及版本信息
$ dropdb test        #删除test库

三、操作表单

> create table posts (title varchar(255), content text);   #创建表
> \dt                                                      #展示所有表
> \d posts                                                 #建表信息
> alter table posts rename to testposts;                   #修改表名
> drop table komaposts;                                    #删除表

$ vim db.sql                                               #编写表文件
... 
create table posts (title varchar(255), content text);
...                                          
> \i db.sql                                                #使用表文件建表

四、字段类型

常用字段

• 数值型：
  • integer(int)  整型 
  • real 浮点
  • serial 整型序列
• 文字型：
  • char 定长字符
  • varchar 限长字符
  • text 不定字符
• 布尔型：
  • boolean
• 日期型：
  • date
  • time
  • timestamp
• 特色类型：
  • Array
  • 网络地址型(inet)
  • JSON型
  • XML型

    

约束条件：

not null:不能为空
unique:在所有数据中值必须唯一
check:字段设置条件
default:字段默认值
primary key(not null, unique):主键，不能为空，且不能重复

五、语句

INSERT语句：

test=# insert into posts (title,content) values ('title1','content1');
INSERT 0 1

 

SELECT语句:

> select * from users;
> select player, score from users;

WHERE语句

使用where语句来设定select,update,delete语句数据抽出的条件。

select player,score from users where score>30;

select player,score from users where score!=30;

select * from users where score > 20 and score < 30;

test=# select * from users where player like '阿_';

test=# select * from users where player like '阿%';

数据抽出选项

select语句在抽出数据时，可以对语句设置更多的选项，已得到想要的数据。

• order by
• limit
• offset

select * from users order by score asc;        #升序
select * from users order by score desc;       #降序
select * from users order by team;
select * from users order by team, score;
select * from users order by team, score desc;
select * from users order by team desc, score desc;
select * from users order by score desc limit 3;              #行数
select * from users order by score desc limit 3 offset 1;     #位置偏移
select * from users order by score desc limit 3 offset 2;
select * from users order by score desc limit 3 offset 3;

统计抽出数据

• distinct                          #去重
• sum
• max/min
• group by/having            #组合使用

select distinct team from users;
select sum(score) from users;
select max(score) from users;
select min(score) from users;
select * from users where score = (select max(score) from users);
select * from users where score = (select min(score) from users);
select team, max(score) from users group by team;
select team, max(score) from users group by team having max(score) >= 25;
select team, max(score) from users group by team having max(score) >= 25 order by max(score);

方便的函数

• length                  #字段长度
• concat                 #连接多个字符串
• alias                    #as别名
• substring             #分割字符串
• random               #随机数（0~1）

select player, length(player) from users;
select player, concat(player, '/', team) from users;
select player, concat(player, '/', team) as "球员信息" from users;
select substring(team, 1, 1) as "球队首文字" from users;
select concat('我', substring(team, 1, 1)) as "球队首文字" from users;
select random();
select * from users order by random();
select * from users order by random() limit 1;

更新和删除

• update [table] set [field=newvalue,...] where ...
• delete from [table] where ...

update users set score = 29.1 where player = '阿詹';
update users set score = score + 1 where team = '勇士';
update users set score = score + 100 where team IN ('勇士', '骑士');
delete from users where score > 30;

变更表结构

• alter table [tablename] ...
• create index ...
• drop index ...

> alter table users add fullname varchar(255);
> alter table users drop fullname;
> alter table users rename player to nba_player;
> alter table users alter nds_player type varchar(100);
> create index nba_player_index on users(nba_player);
> drop index nba_player_index;

操作多个表

> select users.player, twitters.content from users, twitters where users.id = twitters.user_id;
> select u.player, t.content from users as u, twitters as t where u.id = t.user_id;
> select u.player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;

视图概念

视图（View）是从一个或多个表导出的对象。视图与表不同，视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，在对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。

视图就是一个SELECT语句，把业务系统中常用的SELECT语句简化成一个类似于表的对象，便于简单读取和开发。

• 使用数据库视图(view)
  • create view ...
  • drop view ...

create view curry_twitters as select u.player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;
\dv           #全部视图
\d curry_twitters
select * from curry_twitters;
drop view curry_twitters;

使用事务

数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。 事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。事务是数据库运行中的逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。

• PostgreSql数据库事务使用
  • begin
  • commit
  • rollback

select * from users;
begin;
update users set score = 50 where player = 'bob';
update users set score = 60 where player = 'tom';
commit;
select * from users;
begin;
update users set score = 0 where player = 'bob';
update users set score = 0 where player = 'tom';
rollback;
select * from users;