PostgreSQL

PostgreSQL

本节参考：PostgreSql入门，特别感谢小马视频的学习课程，深入浅出了 PostgreSQL。在此基础上针对自己的学习过程，修改了部分内容。

本文为 PostgreSQL 入门指南，通过极简的语言，带你走进 PostgreSQL 世界的大门。

1. 什么是PostgreSql？

PostgreSQL 是一个自由的对象-关系数据库服务器(数据库管理系统)，是从伯克利写的 POSTGRES 软件包发展而来的。经过十几年的发展， PostgreSQL 是世界上可以获得的最先进的开放源码的数据库系统，它提供了多版本并发控制，支持几乎所有SQL语句（包括子查询，事务和用户定义类型和函数），并且可以获得非常广阔范围的（开发）语言绑定（包括C,C++,Java,perl,python,php,nodejs,ruby）。

知识点

面向关系的数据库
- Oracle
- MySql
- SQLServer
- PostgreSql
NoSql
- MongoDB
- Redis

数据库排名

https://db-engines.com/en/ranking

官方网站

https://www.postgresql.org/

技术准备

SQL语言基础

使用环境

Ubuntu Server 16 LTS
PostgreSql 9.5.x

安装

$ sudo apt-get install postgresql
$ psql --version

2. 初来乍到数据库

知识点

psql的基础
数据库简单操作
写个SQL

实战演习

$ sudo su postgres
$ psql --version
$ psql -l
$ createdb komablog
$ psql -l
$ psql komablog
> help
> \h
> \?
> \l
> \q
$ psql komablog
> select now();
> select version();
> \q
$ dropdb komablog
$ psql -l

3. 操作数据表

知识点

create table
drop table
psql使用

实战演习

$ sudo su postgres
$ createdb komablog
$ psql -l
$ psql komablog
> create table posts (title varchar(255), content text);
> \dt
> \d posts
> alter table posts rename to komaposts;
> \dt
> drop table komaposts;
> \dt
> \q
$ nano db.sql
...
create table posts (title varchar(255), content text);
...
$ psql komablog
> \i db.sql
> \dt

4. 字段类型

知识点

PostgreSql的基础数据类型

PostgreSql的基础数据类型

数值型：
- integer(int)
- real
- serial
文字型：
- char
- varchar
- text
布尔型：
- boolean
日期型：
- date
- time
- timestamp
特色类型：
- Array
- �网络地址型(inet)
- JSON型
- XML型

参考网站：

https://www.postgresql.org/docs/9.5/static/datatype.html

5. 添加表约束

知识点

表子段的约束条件

实战演习

db.sql

create table posts (
    id serial primary key,
    title varchar(255) not null,
    content text check(length(content) > 8),
    is_draft boolean default TRUE,
    is_del boolean default FALSE,
    created_date timestamp default 'now'
);

-- 说明
/*
约束条件：

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

6. INSERT语句

知识点

insert into [tablename] (field, ...) values (value, ...)

实战演习

$ psql komablog
> \dt
> \d posts

SQL部分

> insert into posts (title, content) values ('', '');
> insert into posts (title, content) values (NULL, '');
> insert into posts (title, content) values ('title1', 'content11');
> select * from posts;
> insert into posts (title, content) values ('title2', 'content22');
> insert into posts (title, content) values ('title3', 'content33');
> select * from posts;

7. SELECT语句

知识点

select 基本使用

实战演习

init.sql

create table users (
    id serial primary key,
    player varchar(255) not null,
    score real,
    team varchar(255)
);

insert into users (player, score, team) values
('库里', 28.3, '勇士'),
('哈登', 30.2, '火箭'),
('阿杜', 25.6, '勇士'),
('阿詹', 27.8, '骑士'),
('神龟', 31.3, '雷霆'),
('白边', 19.8, '热火');

SQL实战

$ psql komablog
> \i init.sql
> \dt
> \d users
> select * from users;
> \x
> select * from users;
> \x
> select * from users;
> select player, score from users;

8. WHERE语句

知识点

where语句的使用

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

实战演习

> select * from users;
> select * from users where score > 20;
> select * from users where score < 30;
> select * from users where score > 20 and score < 30;
> select * from users where team = '勇士';
> select * from users where team != '勇士';
> select * from users where player like '阿%';
> select * from users where player like '阿_';

9. 数据抽出选项

知识点

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;

10. 统计抽出数据

知识点

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);

11. 方便的函数

知识点

length
concat
alias
substring
random

参考网站：

https://www.postgresql.org/docs/9.5/static/functions.html

实战演习

> 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;

12. 更新和删除

知识点

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;

13. 变更表结构

知识点

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

实战演习

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

14. 操作多个表

知识点

表结合查询的基础知识

实战演习

renew.sql

create table users (
    id serial primary key,
    player varchar(255) not null,
    score real,
    team varchar(255)
);
insert into users (player, score, team) values
('库里', 28.3, '勇士'),
('哈登', 30.2, '火箭'),
('阿杜', 25.6, '勇士'),
('阿詹', 27.8, '骑士'),
('神龟', 31.3, '雷霆'),
('白边', 19.8, '热火');

create table twitters (
    id serial primary key,
    user_id integer,
    content varchar(255) not null
);
insert into twitters (user_id, content) values
(1, '今天又是大胜,克莱打的真好!'),
(2, '今晚我得了60分,哈哈!'),
(3, '获胜咱不怕,缺谁谁尴尬.'),
(4, '明年我也可能转会西部'),
(5, '我都双20+了，怎么球队就是不胜呢?'),
(1, '明年听说有条大鱼要来,谁呀?');

SQL实行

$ dropdb komablog;
$ createdb komablog;
$ psql komablog;
> \i renew.sql
> select * from users;
> select * from twitters;
> 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;

15. 使用视图

视图概念

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

简单解释

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

知识点

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

实战演习

> select u.player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;
> 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;
> \dv

实战建议

在自己项目中，为了提高数据查询速度，可在表中加入索引index。同时对于经常需要查询的语句，可以提前建立视图view，方便于编码和管理。

16. 使用事务

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

知识点

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

实战演习

> select * from users;
> begin;
> update users set score = 50 where player = '库里';
> update users set score = 60 where player = '哈登';
> commit;
> select * from users;
> begin;
> update users set score = 0 where player = '库里';
> update users set score = 0 where player = '哈登';
> rollback;
> select * from users;

参考资料

【小马技术】PostgreSql 关系型数据库入门_哔哩哔哩 (゜-゜)つロ干杯~-bilibili

Files

PostgreSQL.md

Latest commit

History

PostgreSQL.md

File metadata and controls

PostgreSQL

1. 什么是PostgreSql？

知识点

数据库排名

官方网站

技术准备

使用环境

安装

2. 初来乍到数据库

知识点

实战演习

3. 操作数据表

知识点

实战演习

4. 字段类型

知识点

PostgreSql的基础数据类型

5. 添加表约束

知识点

实战演习

db.sql

6. INSERT语句

知识点

实战演习

SQL部分

7. SELECT语句

知识点

实战演习

init.sql

SQL实战

8. WHERE语句

知识点

实战演习

9. 数据抽出选项

知识点

实战演习

10. 统计抽出数据

知识点

实战演习

11. 方便的函数

知识点

实战演习

12. 更新和删除

知识点

实战演习

13. 变更表结构

知识点

实战演习

14. 操作多个表

知识点

实战演习

renew.sql

SQL实行

15. 使用视图

视图概念

简单解释

知识点

实战演习

实战建议

16. 使用事务

知识点

实战演习

参考资料