PostgreSQL 学习手册
文章目录
-
[*PostgreSQL 学习手册*]() - 一、什么是PostgreSQL
- 二、PostgreSQL的部署
- 三、PostgreSQL SQL学习
- 四、事物
一、什么是PostgreSQL
1、认识PostgreSQL
PostgreSQL 是一个*的对象-关系数据库服务器(数据库管理系统)，是从伯克利写的 POSTGRES 软件包发展而来的。经过十几年的发展， PostgreSQL 是世界上可以获得的最先进的开放源码的数据库系统， 它提供了多版本并发控制，支持几乎所有SQL语句（包括子查询，事务和用户定义类型和函数），并且可以获得非常广阔范围的（开发）语言绑定 （包括C,C++,Java,perl,python,php,nodejs,ruby）。
2、数据库的分类
- 面向关系的数据库
- Oracle
- MySql
- SQLServer
- PostgreSql
- NoSql
- MongoDB
- Redis
数据库使用排名：https://db-engines.com/en/ranking
由此可见，PostgreSQL因为他的开源、拥有强大的技术支持、使用量呈现上升趋势，所以我们很有必要来学习。
二、PostgreSQL的部署
1、基于RPM方式部署
注：基于centos 7.8 系统部署PostgreSQL 12.7 版本
1、安装PostgreSQL 12.7
# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Install PostgreSQL:
sudo yum install -y postgresql12-server
# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12
2、加入系统服务
postgresql-setup --initdb
systemctl enable postgresql.service
systemctl start postgresql.service
3、查看PostgreSQL安装情况
[root@node03 ~]# netstat -lnutp | grep :5432
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 1470/postmaster
tcp6 0 0 ::1:5432 :::* LISTEN 1470/postmaster
[root@node03 ~]# ps -ef | grep postmaster
postgres 1470 1 0 10:01 ? 00:00:00 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
root 11809 1163 0 10:18 pts/0 00:00:00 grep --color=auto postmaster
4、进入PostgreSQL
[root@node03 ~]# su postgres
bash-4.2$ exit
exit
[root@node03 ~]#
[root@node03 ~]# su - postgres
Last login: Fri Jul 2 10:19:56 CST 2021 on pts/0
-bash-4.2$ psql --version
psql (PostgreSQL) 12.7
-bash-4.2$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
-bash-4.2$ createdb mydb
-bash-4.2$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mydb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
-bash-4.2$ psql mydb
psql (12.7)
Type "help" for help.
三、PostgreSQL SQL学习
1、数据库操作
# 查看当前实例的所有数据库
-bash-4.2$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | A
ccess privileges
-----------+----------+----------+-------------+-------------+----
-------------------
mydb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/
postgres +
| | | | | pos
tgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/
postgres +
| | | | | pos
tgres=CTc/postgres
(4 rows)
# 创建testdb数据库并查看
-bash-4.2$ createdb testdb
-bash-4.2$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | A
ccess privileges
-----------+----------+----------+-------------+-------------+----
-------------------
mydb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/
postgres +
| | | | | pos
tgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/
postgres +
| | | | | pos
tgres=CTc/postgres
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
# 进入testdb数据库
-bash-4.2$ psql testdb
psql (12.7)
Type "help" for help.
# 执行相应的函数查询
testdb=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
testdb=# select now();
now
-------------------------------
2021-07-02 10:33:54.605319+08
(1 row)
testdb=# \q
-bash-4.2$
# 删除testdb数据库
-bash-4.2$ dropdb testdb
-bash-4.2$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mydb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
2、表操作
# 创建数据库（执行SQL指令）
-bash-4.2$ psql mydb
psql (12.7)
Type "help" for help.
mydb=# \dt
Did not find any relations.
mydb=# create table post(title varchar(255), content text);
CREATE TABLE
mydb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | post | table | postgres
(1 row)
mydb=# \d post
Table "public.post"
Column | Type | Collation | Nullable | Default
---------+------------------------+-----------+----------+---------
title | character varying(255) | | |
content | text | | |
mydb=# drop table post;
DROP TABLE
mydb=# \dt
Did not find any relations.
# 创建数据库（从文件中导入）
-bash-4.2$ cat testtable.sql
create table stu_info(id int, name varchar(10));
-bash-4.2$ psql mydb
psql (12.7)
Type "help" for help.
mydb=# \i testtable.sql
CREATE TABLE
mydb=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | stu_info | table | postgres
(1 row)
mydb=# \d stu_info
Table "public.stu_info"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | |
name | character varying(10) | | |
# 创建约束条件的表
mydb=# create table posts (
mydb(# id serial primary key,
mydb(# title varchar(255) not null,
mydb(# content text check(length(content) > 8),
mydb(# is_draft boolean default TRUE,
mydb(# is_del boolean default FALSE,
mydb(# created_date timestamp default 'now'
mydb(# );
CREATE TABLE
mydb=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | posts | table | postgres
public | stu_info | table | postgres
(2 rows)
mydb=# \d posts
Table "public.posts"
Column | Type | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+----------------------------------------
-------------------
id | integer | | not null | nextval('posts_id_seq'::regclass)
title | character varying(255) | | not null |
content | text | | |
is_draft | boolean | | | true
is_del | boolean | | | false
created_date | timestamp without time zone | | | '2021-07-02 11:04:39.774181'::timestamp
without time zone
Indexes:
"posts_pkey" PRIMARY KEY, btree (id)
Check constraints:
"posts_content_check" CHECK (length(content) > 8)
3、表增删改
# 插入数据
mydb=# insert into posts (title, content) values ('title1', 'content11');
INSERT 0 1
mydb=# select * from posts;
id | title | content | is_draft | is_del | created_date
----+--------+-----------+----------+--------+----------------------------
3 | title1 | content11 | t | f | 2021-07-02 11:04:39.774181
(1 row)
mydb=# insert into posts (title, content) values ('title2', 'content22');
INSERT 0 1
mydb=# insert into posts (title, content) values ('title3', 'content33');
INSERT 0 1
mydb=# select * from posts;
id | title | content | is_draft | is_del | created_date
----+--------+-----------+----------+--------+----------------------------
3 | title1 | content11 | t | f | 2021-07-02 11:04:39.774181
4 | title2 | content22 | t | f | 2021-07-02 11:04:39.774181
5 | title3 | content33 | t | f | 2021-07-02 11:04:39.774181
(3 rows)
# 更新数据
mydb=# select * from users;
id | player | score | team
----+--------+-------+------
1 | 库里 | 28.3 | 勇士
2 | 哈登 | 30.2 | 火箭
3 | 阿杜 | 25.6 | 勇士
4 | 阿詹 | 27.8 | 骑士
5 | 神龟 | 31.3 | 雷霆
6 | 白边 | 19.8 | 热火
(6 rows)
mydb=# update users set score = 29.1 where player = '阿詹';
UPDATE 1
mydb=# update users set score = score + 1 where team = '勇士';
UPDATE 2
mydb=# update users set score = score + 100 where team IN ('勇士', '骑士');
UPDATE 3
mydb=# select * from users;
id | player | score | team
----+--------+-------+------
2 | 哈登 | 30.2 | 火箭
5 | 神龟 | 31.3 | 雷霆
6 | 白边 | 19.8 | 热火
4 | 阿詹 | 129.1 | 骑士
1 | 库里 | 129.3 | 勇士
3 | 阿杜 | 126.6 | 勇士
(6 rows)
mydb=# delete from users where score > 30;
DELETE 5
mydb=# select * from users;
id | player | score | team
----+--------+-------+------
6 | 白边 | 19.8 | 热火
(1 row)
4、表的查询
（1）素材准备
# 创建users表
mydb=# create table users (
mydb(# id serial primary key,
mydb(# player varchar(255) not null,
mydb(# score real,
mydb(# team varchar(255)
mydb(# );
CREATE TABLE
# 表中插入数据
mydb=# insert into users(player, score, team) values
mydb-# ('库里', 28.3, '勇士'),
mydb-# ('哈登', 30.2, '火箭'),
mydb-# ('阿杜', 25.6, '勇士'),
mydb-# ('阿詹', 27.8, '骑士'),
mydb-# ('神龟', 31.3, '雷霆'),
mydb-# ('白边', 19.8, '热火');
INSERT 0 6
# 查看表信息
mydb=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | posts | table | postgres
public | stu_info | table | postgres
public | users | table | postgres
(3 rows)
mydb=# \d users
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
player | character varying(255) | | not null |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
（2）查看表信息
# 查看表所有信息
mydb=# select * from users;
id | player | score | team
----+--------+-------+------
1 | 库里 | 28.3 | 勇士
2 | 哈登 | 30.2 | 火箭
3 | 阿杜 | 25.6 | 勇士
4 | 阿詹 | 27.8 | 骑士
5 | 神龟 | 31.3 | 雷霆
6 | 白边 | 19.8 | 热火
(6 rows)
# 查看指定列信息
mydb=# select player, score from users;
player | score
--------+-------
库里 | 28.3
哈登 | 30.2
阿杜 | 25.6
阿詹 | 27.8
神龟 | 31.3
白边 | 19.8
(6 rows)
（3）按照约束条件产看表信息
mydb=# select * from users where score > 20;
id | player | score | team
----+--------+-------+------
1 | 库里 | 28.3 | 勇士
2 | 哈登 | 30.2 | 火箭
3 | 阿杜 | 25.6 | 勇士
4 | 阿詹 | 27.8 | 骑士
5 | 神龟 | 31.3 | 雷霆
(5 rows)
mydb=# select * from users where score > 30;
id | player | score | team
----+--------+-------+------
2 | 哈登 | 30.2 | 火箭
5 | 神龟 | 31.3 | 雷霆
(2 rows)
mydb=# select * from users where score > 20 and score < 30;
id | player | score | team
----+--------+-------+------
1 | 库里 | 28.3 | 勇士
3 | 阿杜 | 25.6 | 勇士
4 | 阿詹 | 27.8 | 骑士
(3 rows)
mydb=# select * from users where team = '火箭';
id | player | score | team
----+--------+-------+------
2 | 哈登 | 30.2 | 火箭
(1 row)
mydb=# select * from users where team = '勇士';
id | player | score | team
----+--------+-------+------
1 | 库里 | 28.3 | 勇士
3 | 阿杜 | 25.6 | 勇士
(2 rows)
mydb=# select * from users where team != '勇士';
id | player | score | team
----+--------+-------+------
2 | 哈登 | 30.2 | 火箭
4 | 阿詹 | 27.8 | 骑士
5 | 神龟 | 31.3 | 雷霆
6 | 白边 | 19.8 | 热火
(4 rows)
(4)模糊匹配
mydb=# select * from users where player like '阿%';
id | player | score | team
----+--------+-------+------
3 | 阿杜 | 25.6 | 勇士
4 | 阿詹 | 27.8 | 骑士
(2 rows)
mydb=# select * from users where player like '阿_';
id | player | score | team
----+--------+-------+------
3 | 阿杜 | 25.6 | 勇士
4 | 阿詹 | 27.8 | 骑士
(2 rows)
(5)统计查询
# 去重查询
mydb=# select team from users;
team
------
勇士
火箭
勇士
骑士
雷霆
热火
(6 rows)
mydb=# select distinct team from users;
team
------
热火
骑士
火箭
雷霆
勇士
(5 rows)
# 使用函数查询
mydb=# select sum(score) from users;
sum
-----
163
(1 row)
mydb=# select max(score) from users;
max
------
31.3
(1 row)
mydb=# select min(score) from users;
min
------
19.8
(1 row)
mydb=# select avg(score) from users;
avg
--------------------
27.166666348775227
(1 row)
# 子查询
mydb=# select * from users where score = (select max(score) from users);
id | player | score | team
----+--------+-------+------
5 | 神龟 | 31.3 | 雷霆
(1 row)
mydb=# select * from users where score = (select min(score) from users);
id | player | score | team
----+--------+-------+------
6 | 白边 | 19.8 | 热火
# 分组查询
mydb=# select team, max(score) from users group by team;
team | max
------+------
热火 | 19.8
骑士 | 27.8
火箭 | 30.2
雷霆 | 31.3
勇士 | 28.3
(5 rows)
mydb=# select team, max(score) from users group by team having max(score) >= 25;
team | max
------+------
骑士 | 27.8
火箭 | 30.2
雷霆 | 31.3
勇士 | 28.3
(4 rows)
mydb=# select team,max(score) from users group by team having max(score) >= 25 order by max(score);
team | max
------+------
骑士 | 27.8
勇士 | 28.3
火箭 | 30.2
雷霆 | 31.3
(4 rows)
（6）套用函数查询
mydb=# select player, concat(player, '/', team) from users;
player | concat
--------+-----------
库里 | 库里/勇士
哈登 | 哈登/火箭
阿杜 | 阿杜/勇士
阿詹 | 阿詹/骑士
神龟 | 神龟/雷霆
白边 | 白边/热火
(6 rows)
mydb=# select player, concat(player, '/', team) as "球员信息" from users;
player | 球员信息
--------+-----------
库里 | 库里/勇士
哈登 | 哈登/火箭
阿杜 | 阿杜/勇士
阿詹 | 阿詹/骑士
神龟 | 神龟/雷霆
白边 | 白边/热火
(6 rows)
mydb=# select concat('我', substring(team, 1, 1)) as "球队首文字" from users;
球队首文字
------------
我勇
我火
我勇
我骑
我雷
我热
(6 rows)
mydb=# select random();
random
--------------------
0.8281039666733854
(1 row)
mydb=# select * from users order by random();
id | player | score | team
----+--------+-------+------
6 | 白边 | 19.8 | 热火
1 | 库里 | 28.3 | 勇士
2 | 哈登 | 30.2 | 火箭
3 | 阿杜 | 25.6 | 勇士
5 | 神龟 | 31.3 | 雷霆
4 | 阿詹 | 27.8 | 骑士
(6 rows)
mydb=# select * from users order by random() limit 1;
id | player | score | team
----+--------+-------+------
1 | 库里 | 28.3 | 勇士
(1 row)
(7)多表查询
- 素材准备
mydb=# create table users (
mydb(# id serial primary key,
mydb(# player varchar(255) not null,
mydb(# score real,
mydb(# team varchar(255)
mydb(# );
CREATE TABLE
mydb=# insert into users (player, score, team) values
mydb-# ('库里', 28.3, '勇士'),
mydb-# ('哈登', 30.2, '火箭'),
mydb-# ('阿杜', 25.6, '勇士'),
mydb-# ('阿詹', 27.8, '骑士'),
mydb-# ('神龟', 31.3, '雷霆'),
mydb-# ('白边', 19.8, '热火');
INSERT 0 6
mydb=# create table twitters (
mydb(# id serial primary key,
mydb(# user_id integer,
mydb(# content varchar(255) not null
mydb(# );
CREATE TABLE
mydb=# insert into twitters (user_id, content) values
mydb-# (1, '今天又是大胜,克莱打的真好!'),
mydb-# (2, '今晚我得了60分,哈哈!'),
mydb-# (3, '获胜咱不怕,缺谁谁尴尬.'),
mydb-# (4, '明年我也可能转会西部'),
mydb-# (5, '我都双20+了，怎么球队就是不胜呢?'),
mydb-# (1, '明年听说有条大鱼要来,谁呀?');
INSERT 0 6
mydb=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | posts | table | postgres
public | stu_info | table | postgres
public | twitters | table | postgres
public | users | table | postgres
(4 rows)
mydb=# select * from users;
id | player | score | team
----+--------+-------+------
1 | 库里 | 28.3 | 勇士
2 | 哈登 | 30.2 | 火箭
3 | 阿杜 | 25.6 | 勇士
4 | 阿詹 | 27.8 | 骑士
5 | 神龟 | 31.3 | 雷霆
6 | 白边 | 19.8 | 热火
(6 rows)
mydb=# select * from twitters;
id | user_id | content
----+---------+----------------------------------
1 | 1 | 今天又是大胜,克莱打的真好!
2 | 2 | 今晚我得了60分,哈哈!
3 | 3 | 获胜咱不怕,缺谁谁尴尬.
4 | 4 | 明年我也可能转会西部
5 | 5 | 我都双20+了，怎么球队就是不胜呢?
6 | 1 | 明年听说有条大鱼要来,谁呀?
(6 rows)
- 多表查询
mydb=# select users.player, twitters.content from users, twitters where users.id = twitters.user_id;
player | content
--------+----------------------------------
库里 | 今天又是大胜,克莱打的真好!
哈登 | 今晚我得了60分,哈哈!
阿杜 | 获胜咱不怕,缺谁谁尴尬.
阿詹 | 明年我也可能转会西部
神龟 | 我都双20+了，怎么球队就是不胜呢?
库里 | 明年听说有条大鱼要来,谁呀?
(6 rows)
mydb=# select u.player, t.content from users as u, twitters as t where u.id = t.user_id;
player | content
--------+----------------------------------
库里 | 今天又是大胜,克莱打的真好!
哈登 | 今晚我得了60分,哈哈!
阿杜 | 获胜咱不怕,缺谁谁尴尬.
阿詹 | 明年我也可能转会西部
神龟 | 我都双20+了，怎么球队就是不胜呢?
库里 | 明年听说有条大鱼要来,谁呀?
(6 rows)
mydb=# select u.player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;
player | content
--------+----------------------------
库里 | 今天又是大胜,克莱打的真好!
库里 | 明年听说有条大鱼要来,谁呀?
(2 rows)
5、修改表结构
# 增加列
mydb=# alter table users add fullname varchar(100);
ALTER TABLE
mydb=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
----------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
player | character varying(255) | | not null |
score | real | | |
team | character varying(255) | | |
fullname | character varying(100) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
# 删除列
mydb=# alter table users drop fullname;
ALTER TABLE
# 对列进行重命名
mydb=# alter table users rename player to nba_player;
ALTER TABLE
mydb=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
nba_player | character varying(255) | | not null |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
# 更改列属性
mydb=# alter table users alter nba_player type varchar(80);
ALTER TABLE
mydb=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
nba_player | character varying(80) | | not null |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
6、索引
mydb=# create index nbs_player on users(nba_player);
CREATE INDEX
mydb=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
nba_player | character varying(80) | | not null |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"nbs_player" btree (nba_player)
mydb=# drop index nbs_player;
DROP INDEX
mydb=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
nba_player | character varying(80) | | not null |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
7、视图
mydb=# select u.player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;
player | content
--------+----------------------------
库里 | 今天又是大胜,克莱打的真好!
库里 | 明年听说有条大鱼要来,谁呀?
(2 rows)
mydb=# 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;
CREATE VIEW
mydb=# \dv
List of relations
Schema | Name | Type | Owner
--------+----------------+------+----------
public | curry_twitters | view | postgres
(1 row)
mydb=# \d curry_twitters
View "public.curry_twitters"
Column | Type | Collation | Nullable | Default
---------+------------------------+-----------+----------+---------
player | character varying(255) | | |
content | character varying(255) | | |
mydb=# select * from curry_twitters;
player | content
--------+----------------------------
库里 | 今天又是大胜,克莱打的真好!
库里 | 明年听说有条大鱼要来,谁呀?
(2 rows)
mydb=# drop view curry_twitters;
DROP VIEW
mydb=# \dv
Did not find any relations.
四、事物
1、开启事物
mydb=# select * from users;
id | player | score | team
----+--------+-------+------
1 | 库里 | 28.3 | 勇士
2 | 哈登 | 30.2 | 火箭
3 | 阿杜 | 25.6 | 勇士
4 | 阿詹 | 27.8 | 骑士
5 | 神龟 | 31.3 | 雷霆
6 | 白边 | 19.8 | 热火
(6 rows)
mydb=# begin;
BEGIN
mydb=# update users set score = 50 where player = '库里';
UPDATE 1
mydb=# update users set score = 60 where player = '哈登';
UPDATE 1
mydb=# commit;
COMMIT
mydb=# select * from users;
id | player | score | team
----+--------+-------+------
3 | 阿杜 | 25.6 | 勇士
4 | 阿詹 | 27.8 | 骑士
5 | 神龟 | 31.3 | 雷霆
6 | 白边 | 19.8 | 热火
1 | 库里 | 50 | 勇士
2 | 哈登 | 60 | 火箭
(6 rows)
2、回滚
mydb=# begin;
BEGIN
mydb=# update users set score = 0 where player = '库里';
UPDATE 1
mydb=# update users set score = 0 where player = '哈登';
UPDATE 1
mydb=# select * from users;
id | player | score | team
----+--------+-------+------
3 | 阿杜 | 25.6 | 勇士
4 | 阿詹 | 27.8 | 骑士
5 | 神龟 | 31.3 | 雷霆
6 | 白边 | 19.8 | 热火
1 | 库里 | 0 | 勇士
2 | 哈登 | 0 | 火箭
(6 rows)
mydb=# rollback;
ROLLBACK
mydb=# select * from users;
id | player | score | team
----+--------+-------+------
3 | 阿杜 | 25.6 | 勇士
4 | 阿詹 | 27.8 | 骑士
5 | 神龟 | 31.3 | 雷霆
6 | 白边 | 19.8 | 热火
1 | 库里 | 50 | 勇士
2 | 哈登 | 60 | 火箭
(6 rows)
id | player | score | team
----±-------±------±-----
3 | 阿杜 | 25.6 | 勇士
4 | 阿詹 | 27.8 | 骑士
5 | 神龟 | 31.3 | 雷霆
6 | 白边 | 19.8 | 热火
1 | 库里 | 50 | 勇士
2 | 哈登 | 60 | 火箭
(6 rows)
## 2、回滚
```sql
mydb=# begin;
BEGIN
mydb=# update users set score = 0 where player = '库里';
UPDATE 1
mydb=# update users set score = 0 where player = '哈登';
UPDATE 1
mydb=# select * from users;
id | player | score | team
----+--------+-------+------
3 | 阿杜 | 25.6 | 勇士
4 | 阿詹 | 27.8 | 骑士
5 | 神龟 | 31.3 | 雷霆
6 | 白边 | 19.8 | 热火
1 | 库里 | 0 | 勇士
2 | 哈登 | 0 | 火箭
(6 rows)
mydb=# rollback;
ROLLBACK
mydb=# select * from users;
id | player | score | team
----+--------+-------+------
3 | 阿杜 | 25.6 | 勇士
4 | 阿詹 | 27.8 | 骑士
5 | 神龟 | 31.3 | 雷霆
6 | 白边 | 19.8 | 热火
1 | 库里 | 50 | 勇士
2 | 哈登 | 60 | 火箭
(6 rows)