關係型資料庫
官方網站：https://www.postgresql.org/
下載地址：https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
postgres=# \l
資料庫列表
名稱 | 擁有者 | 字元編碼 | 校對規則 | Ctype | 存取許可權
-----------+----------+----------+--------------------------------+--------------------------------+-----------------------
postgres | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
template0 | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行記錄)
postgres=# create database mydb;
CREATE DATABASE
postgres=# \l
資料庫列表
名稱 | 擁有者 | 字元編碼 | 校對規則 | Ctype | 存取許可權
-----------+----------+----------+--------------------------------+--------------------------------+-----------------------
mydb | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
postgres | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
template0 | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 行記錄)
postgres=# \c mydb;
您現在已經連線到資料庫 "mydb",使用者 "postgres".
postgres=# drop database mydb
DROP DATABASE
postgres=# \l
資料庫列表
名稱 | 擁有者 | 字元編碼 | 校對規則 | Ctype | 存取許可權
-----------+----------+----------+--------------------------------+--------------------------------+-----------------------
postgres | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
template0 | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行記錄)
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) : 主鍵
mydb=# \dt
關聯列表
架構模式 | 名稱 | 型別 | 擁有者
----------+---------+--------+----------
public | mytable | 資料表 | postgres
public | posts | 資料表 | postgres
mydb=# \d posts;
資料表 "public.posts"
欄位 | 型別 | 校對規則 | 可空的 | 預設
--------------+-----------------------------+----------+----------+-----------------------------------------------------------
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 | | | '2020-09-19 11:30:36.484723'::timestamp without time zone
索引：
"posts_pkey" PRIMARY KEY, btree (id)
檢查約束限制
"posts_content_check" CHECK (length(content) > 8)
mydb=# drop table mytable;
DROP TABLE
mydb=# \dt
關聯列表
架構模式 | 名稱 | 型別 | 擁有者
----------+-------+--------+----------
public | posts | 資料表 | postgres
alter table [tablename] …
create index …
drop index …
mydb=# \d users;
資料表 "public.users"
欄位 | 型別 | 校對規則 | 可空的 | 預設
--------+------------------------+----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
player | character varying(255) | | not null |
score | real | | |
team | character varying(255) | | |
索引：
"users_pkey" PRIMARY KEY, btree (id)
mydb=# alter table users add fullname varchar(255);
ALTER TABLE
mydb=# \d users;
資料表 "public.users"
欄位 | 型別 | 校對規則 | 可空的 | 預設
----------+------------------------+----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
player | character varying(255) | | not null |
score | real | | |
team | character varying(255) | | |
fullname | character varying(255) | | |
索引：
"users_pkey" PRIMARY KEY, btree (id)
mydb=# alter table users drop fullname;
ALTER TABLE
mydb=# \d users;
資料表 "public.users"
欄位 | 型別 | 校對規則 | 可空的 | 預設
--------+------------------------+----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
player | character varying(255) | | not null |
score | real | | |
team | character varying(255) | | |
索引：
"users_pkey" PRIMARY KEY, btree (id)
mydb=# alter table users rename player to nba_player;
ALTER TABLE
mydb=# \d users;
資料表 "public.users"
欄位 | 型別 | 校對規則 | 可空的 | 預設
------------+------------------------+----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
nba_player | character varying(255) | | not null |
score | real | | |
team | character varying(255) | | |
索引：
"users_pkey" PRIMARY KEY, btree (id)
mydb=# alter table users alter nba_player type varchar(100);
ALTER TABLE
mydb=# \d users;
資料表 "public.users"
欄位 | 型別 | 校對規則 | 可空的 | 預設
------------+------------------------+----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
nba_player | character varying(100) | | not null |
score | real | | |
team | character varying(255) | | |
索引：
"users_pkey" PRIMARY KEY, btree (id)
mydb=# create index nba_player_idx on users(nba_player);
CREATE INDEX
mydb=# \d users;
資料表 "public.users"
欄位 | 型別 | 校對規則 | 可空的 | 預設
------------+------------------------+----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
nba_player | character varying(100) | | not null |
score | real | | |
team | character varying(255) | | |
索引：
"users_pkey" PRIMARY KEY, btree (id)
"nba_player_idx" btree (nba_player)
mydb=# drop index nba_player_idx;
DROP INDEX
mydb=# \d users;
資料表 "public.users"
欄位 | 型別 | 校對規則 | 可空的 | 預設
------------+------------------------+----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
nba_player | character varying(100) | | not null |
score | real | | |
team | character varying(255) | | |
索引：
"users_pkey" PRIMARY KEY, btree (id)
預設時，PostgreSQL 允許在建立索引的同時讀取表(SELECT語句)，但是寫入表(INSERT, UPDATE, DELETE)的動作將被阻塞到索引建立完畢
預設時， CREATE INDEX命令將建立 B-tree 索引，它適合大多數情況。
B-tree 適合處理那些能夠按順序儲存的資料之上的等於和範圍查詢。 特別是在一個建立了索引的欄位涉及下面操作符之一進行比較時，PostgreSQL 的查詢規劃器都會考慮使用 B-tree 索引，索引列中的IS NULL或IS NOT NULL條件可以和B-tree索引一起使用。
<
<=
=
>=
>
between
in
col like 'foo%'
col ~ '^foo
Hash 索引只能處理簡單的等於比較。當一個索引了的列涉及到使用= 操作符進行比較的時候，查詢規劃器會考慮使用 Hash 索引。下面的命令用於建立 Hash 索引：
CREATE INDEX name ON table USING hash (column);
TODO
型別介紹：https://www.postgresql.org/docs/12/datatype.html
int
real (浮點型)
serial (序列型 - version)
char (不可變 ， 補空格 TODO)
varchar （可變）
text
boolean
data (年月日)
time (時分秒)
timestamp (年月日時分秒)
array
inet (網路地址型)
json
xml
當插入SQL由於check函數導致失敗時，自增的ID也會被佔用。
mydb=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | character varying(20) | | |
age | integer | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"u_user_age_idx" UNIQUE, btree (name, age)
"u_age_nn_idx" btree (age) WHERE age IS NOT NULL
Number of child tables: 1 (Use \d+ to list them.)
mydb=# SELECT * FROM USERS;
id | name | age
----+-------+-----
2 | Cindy | 16
4 | Dove |
5 | Awa | 14
6 | Seven | 18
1 | Bob | 46
(5 rows)
# 插入相同的 「名稱」 和 「年齡」 失敗 - 受唯一索引限制
mydb=# insert into users (name, age) values ('Bob', 46);
ERROR: duplicate key value violates unique constraint "u_user_age_idx"
DETAIL: Key (name, age)=(Bob, 46) already exists.
# 插入資料，如果已經存在則忽略
mydb=# insert into users (name, age) values ('Bob', 46) on conflict do nothing;
INSERT 0 0
# 插入資料，如果已經存在則更新 「年齡」 為 47
mydb=# insert into users (name, age) values ('Bob', 46) on conflict (name, age) do update set age = 47;
INSERT 0 1
mydb=# select * from users;
id | name | age
----+-------+-----
2 | Cindy | 16
4 | Dove |
5 | Awa | 14
6 | Seven | 18
1 | Bob | 47
(5 rows)
# 插入資料，如果已經存在則在原來的基礎上 加2
mydb=# insert into users (name, age) values ('Bob', 47) on conflict (name, age) do update set age = users.age + 2;
INSERT 0 1
mydb=# select * from users;
id | name | age
----+-------+-----
2 | Cindy | 16
4 | Dove |
5 | Awa | 14
6 | Seven | 18
1 | Bob | 49
(5 rows)
# 插入資料，如果已經存在則在原來的基礎上新增上新增的值
mydb=# insert into users (name, age) values ('Bob', 49) on conflict (name, age) do update set age = users.age + excluded.age;
INSERT 0 1
mydb=# select * from users; id | name | age
----+-------+-----
2 | Cindy | 16
4 | Dove |
5 | Awa | 14
6 | Seven | 18
1 | Bob | 98
(5 rows)
mydb=# select id, player, score, team from users;
id | player | score | team
----+---------+-------+---------
1 | kuli | 28.3 | yongshi
2 | hadeng | 30.2 | huojian
3 | adu | 25.6 | yongshi
4 | azhan | 27.8 | qishi
5 | shengui | 31.3 | leiting
6 | baibian | 19.8 | rehuo
(6 行記錄)
mydb=# select id, player, score, team from users where player like 'a%';
id | player | score | team
----+--------+-------+---------
3 | adu | 25.6 | yongshi
4 | azhan | 27.8 | qishi
(2 行記錄)
# _ 為預留位置
mydb=# select id, player, score, team from users where player like 'ad_';
id | player | score | team
----+--------+-------+---------
3 | adu | 25.6 | yongshi
(1 行記錄)
mydb=# select id, player, score, team from users order by score desc;
id | player | score | team
----+---------+-------+---------
5 | shengui | 31.3 | leiting
2 | hadeng | 30.2 | huojian
1 | kuli | 28.3 | yongshi
4 | azhan | 27.8 | qishi
3 | adu | 25.6 | yongshi
6 | baibian | 19.8 | rehuo
(6 行記錄)
# limit {取的條數} offset {偏移量}
mydb=# select id, player, score, team from users order by score desc limit 1 offset 0;
id | player | score | team
----+---------+-------+---------
5 | shengui | 31.3 | leiting
(1 行記錄)
mydb=# select id, player, score, team from users order by score desc limit 1 offset 1;
id | player | score | team
----+--------+-------+---------
2 | hadeng | 30.2 | huojian
(1 行記錄)
mydb=# select id, player, score, team from users order by score desc limit 2 offset 1;
id | player | score | team
----+--------+-------+---------
2 | hadeng | 30.2 | huojian
1 | kuli | 28.3 | yongshi
(2 行記錄)
mydb=# select id, player, score, team from users order by score desc limit 2 offset 2;
id | player | score | team
----+--------+-------+---------
1 | kuli | 28.3 | yongshi
4 | azhan | 27.8 | qishi
(2 行記錄)
mydb=# select team, max(score) from users group by team;
team | max
---------+------
yongshi | 28.3
qishi | 27.8
rehuo | 19.8
leiting | 31.3
huojian | 30.2
(5 行記錄)
mydb=# select team, max(score) from users group by team having max(score) > 30;
team | max
---------+------
leiting | 31.3
huojian | 30.2
(2 行記錄)
mydb=# select team, max(score) from users group by team having max(score) > 30 order by max(score);
team | max
---------+------
huojian | 30.2
leiting | 31.3
(2 行記錄)
mydb=# select id, player, score, team, concat(player, '/', team) as "info" from users;
id | player | score | team | info
----+---------+-------+---------+-----------------
1 | kuli | 28.3 | yongshi | kuli/yongshi
2 | hadeng | 30.2 | huojian | hadeng/huojian
3 | adu | 25.6 | yongshi | adu/yongshi
4 | azhan | 27.8 | qishi | azhan/qishi
5 | shengui | 31.3 | leiting | shengui/leiting
6 | baibian | 19.8 | rehuo | baibian/rehuo
(6 行記錄)
# 對資料進行隨機排序 (random() 獲取亂數)
mydb=# select id, player, score, team from users order by random();
id | player | score | team
----+---------+-------+---------
2 | hadeng | 30.2 | huojian
3 | adu | 25.6 | yongshi
6 | baibian | 19.8 | rehuo
5 | shengui | 31.3 | leiting
4 | azhan | 27.8 | qishi
1 | kuli | 28.3 | yongshi
(6 行記錄)
# select for update
# [執行緒A] 開啟事務
mydb=# begin;
BEGIN
# [執行緒A] 查詢 ID = 1 的資料，並加鎖
mydb=# select * from users where id = 1 for update;
id | name | age
----+------+-----
1 | Bob | 21
(1 row)
# [執行緒B] 更新阻塞
mydb=# update users set age = 22 where id = 1;
# [執行緒A] 提交事務
mydb=# commit;
# [執行緒B] 更新成功
UPDATE 1
mydb=# select * from twitters;
id | user_id | content
----+---------+----------------------------------
1 | 1 | 今天又是大勝,克萊打的真好!
2 | 2 | 今晚我得了60分,哈哈!
3 | 3 | 獲勝咱不怕,缺誰誰尷尬.
4 | 4 | 明年我也可能轉會西部
5 | 5 | 我都雙20+了，怎麼球隊就是不勝呢?
6 | 1 | 明年聽說有條大魚要來,誰呀?
(6 行記錄)
mydb=# select * from users;
id | nba_player | score | team
----+------------+-------+---------
1 | kuli | 28.3 | yongshi
2 | hadeng | 30.2 | huojian
3 | adu | 25.6 | yongshi
5 | shengui | 31.3 | leiting
6 | baibian | 19.8 | rehuo
4 | azhan | 29.1 | qishi
(6 行記錄)
mydb=# select * from users u, twitters t where u.id = t.user_id;
id | nba_player | score | team | id | user_id | content
----+------------+-------+---------+----+---------+----------------------------------
1 | kuli | 28.3 | yongshi | 1 | 1 | 今天又是大勝,克萊打的真好!
2 | hadeng | 30.2 | huojian | 2 | 2 | 今晚我得了60分,哈哈!
3 | adu | 25.6 | yongshi | 3 | 3 | 獲勝咱不怕,缺誰誰尷尬.
4 | azhan | 29.1 | qishi | 4 | 4 | 明年我也可能轉會西部
5 | shengui | 31.3 | leiting | 5 | 5 | 我都雙20+了，怎麼球隊就是不勝呢?
1 | kuli | 28.3 | yongshi | 6 | 1 | 明年聽說有條大魚要來,誰呀?
(6 行記錄)
mydb=# select * from users u join twitters t on u.id = t.user_id;
id | nba_player | score | team | id | user_id | content
----+------------+-------+---------+----+---------+----------------------------------
1 | kuli | 28.3 | yongshi | 1 | 1 | 今天又是大勝,克萊打的真好!
2 | hadeng | 30.2 | huojian | 2 | 2 | 今晚我得了60分,哈哈!
3 | adu | 25.6 | yongshi | 3 | 3 | 獲勝咱不怕,缺誰誰尷尬.
4 | azhan | 29.1 | qishi | 4 | 4 | 明年我也可能轉會西部
5 | shengui | 31.3 | leiting | 5 | 5 | 我都雙20+了，怎麼球隊就是不勝呢?
1 | kuli | 28.3 | yongshi | 6 | 1 | 明年聽說有條大魚要來,誰呀?
(6 行記錄)
# left join 等同與 left outer join
mydb=# select * from users u left join twitters t on u.id = t.user_id;
id | nba_player | score | team | id | user_id | content
----+------------+-------+---------+----+---------+----------------------------------
1 | kuli | 28.3 | yongshi | 1 | 1 | 今天又是大勝,克萊打的真好!
2 | hadeng | 30.2 | huojian | 2 | 2 | 今晚我得了60分,哈哈!
3 | adu | 25.6 | yongshi | 3 | 3 | 獲勝咱不怕,缺誰誰尷尬.
4 | azhan | 29.1 | qishi | 4 | 4 | 明年我也可能轉會西部
5 | shengui | 31.3 | leiting | 5 | 5 | 我都雙20+了，怎麼球隊就是不勝呢?
1 | kuli | 28.3 | yongshi | 6 | 1 | 明年聽說有條大魚要來,誰呀?
6 | baibian | 19.8 | rehuo | | |
(7 行記錄)
mydb=# select * from users u left outer join twitters t on u.id = t.user_id;
id | nba_player | score | team | id | user_id | content
----+------------+-------+---------+----+---------+----------------------------------
1 | kuli | 28.3 | yongshi | 1 | 1 | 今天又是大勝,克萊打的真好!
2 | hadeng | 30.2 | huojian | 2 | 2 | 今晚我得了60分,哈哈!
3 | adu | 25.6 | yongshi | 3 | 3 | 獲勝咱不怕,缺誰誰尷尬.
4 | azhan | 29.1 | qishi | 4 | 4 | 明年我也可能轉會西部
5 | shengui | 31.3 | leiting | 5 | 5 | 我都雙20+了，怎麼球隊就是不勝呢?
1 | kuli | 28.3 | yongshi | 6 | 1 | 明年聽說有條大魚要來,誰呀?
6 | baibian | 19.8 | rehuo | | |
(7 行記錄)
# cross join 為全部交叉關聯 (此時資料量很大，慎用)
mydb=# select * from users u cross join twitters t;
id | nba_player | score | team | id | user_id | content
----+------------+-------+---------+----+---------+----------------------------------
1 | kuli | 28.3 | yongshi | 1 | 1 | 今天又是大勝,克萊打的真好!
2 | hadeng | 30.2 | huojian | 1 | 1 | 今天又是大勝,克萊打的真好!
3 | adu | 25.6 | yongshi | 1 | 1 | 今天又是大勝,克萊打的真好!
5 | shengui | 31.3 | leiting | 1 | 1 | 今天又是大勝,克萊打的真好!
6 | baibian | 19.8 | rehuo | 1 | 1 | 今天又是大勝,克萊打的真好!
4 | azhan | 29.1 | qishi | 1 | 1 | 今天又是大勝,克萊打的真好!
1 | kuli | 28.3 | yongshi | 2 | 2 | 今晚我得了60分,哈哈!
2 | hadeng | 30.2 | huojian | 2 | 2 | 今晚我得了60分,哈哈!
3 | adu | 25.6 | yongshi | 2 | 2 | 今晚我得了60分,哈哈!
5 | shengui | 31.3 | leiting | 2 | 2 | 今晚我得了60分,哈哈!
6 | baibian | 19.8 | rehuo | 2 | 2 | 今晚我得了60分,哈哈!
4 | azhan | 29.1 | qishi | 2 | 2 | 今晚我得了60分,哈哈!
1 | kuli | 28.3 | yongshi | 3 | 3 | 獲勝咱不怕,缺誰誰尷尬.
2 | hadeng | 30.2 | huojian | 3 | 3 | 獲勝咱不怕,缺誰誰尷尬.
3 | adu | 25.6 | yongshi | 3 | 3 | 獲勝咱不怕,缺誰誰尷尬.
5 | shengui | 31.3 | leiting | 3 | 3 | 獲勝咱不怕,缺誰誰尷尬.
6 | baibian | 19.8 | rehuo | 3 | 3 | 獲勝咱不怕,缺誰誰尷尬.
4 | azhan | 29.1 | qishi | 3 | 3 | 獲勝咱不怕,缺誰誰尷尬.
1 | kuli | 28.3 | yongshi | 4 | 4 | 明年我也可能轉會西部
2 | hadeng | 30.2 | huojian | 4 | 4 | 明年我也可能轉會西部
3 | adu | 25.6 | yongshi | 4 | 4 | 明年我也可能轉會西部
5 | shengui | 31.3 | leiting | 4 | 4 | 明年我也可能轉會西部
6 | baibian | 19.8 | rehuo | 4 | 4 | 明年我也可能轉會西部
4 | azhan | 29.1 | qishi | 4 | 4 | 明年我也可能轉會西部
1 | kuli | 28.3 | yongshi | 5 | 5 | 我都雙20+了，怎麼球隊就是不勝呢?
2 | hadeng | 30.2 | huojian | 5 | 5 | 我都雙20+了，怎麼球隊就是不勝呢?
3 | adu | 25.6 | yongshi | 5 | 5 | 我都雙20+了，怎麼球隊就是不勝呢?
5 | shengui | 31.3 | leiting | 5 | 5 | 我都雙20+了，怎麼球隊就是不勝呢?
6 | baibian | 19.8 | rehuo | 5 | 5 | 我都雙20+了，怎麼球隊就是不勝呢?
4 | azhan | 29.1 | qishi | 5 | 5 | 我都雙20+了，怎麼球隊就是不勝呢?
1 | kuli | 28.3 | yongshi | 6 | 1 | 明年聽說有條大魚要來,誰呀?
2 | hadeng | 30.2 | huojian | 6 | 1 | 明年聽說有條大魚要來,誰呀?
3 | adu | 25.6 | yongshi | 6 | 1 | 明年聽說有條大魚要來,誰呀?
5 | shengui | 31.3 | leiting | 6 | 1 | 明年聽說有條大魚要來,誰呀?
update [tablename] set [field=newvalue, …] where …
delete from [tablename] where …
檢視（View）是從一個或多個表匯出的物件。檢視與表不同，檢視是一個虛表，即檢視所對應的資料不進行實際儲存，資料庫中只儲存檢視的定義，在對檢視的資料進行操作時，系統根據檢視的定義去操作與檢視相關聯的基本表。
檢視就是一個SELECT語句，把業務系統中常用的SELECT語句簡化成一個類似於表的物件，便於簡單讀取和開發。
create view …
drop view …
# 建立檢視
mydb=# create view curry_twitters as select u.nba_player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;
CREATE VIEW
# 查詢所有檢視
mydb=# \dv
關聯列表
架構模式 | 名稱 | 型別 | 擁有者
----------+----------------+------+----------
public | curry_twitters | 檢視 | postgres
(1 行記錄)
# 檢視指定檢視的定義
mydb=# \d curry_twitters;
檢視 "public.curry_twitters"
欄位 | 型別 | 校對規則 | 可空的 | 預設
------------+------------------------+----------+--------+------
nba_player | character varying(100) | | |
content | character varying(255) | | |
mydb=# select * from curry_twitters;
nba_player | content
------------+----------------------------
kuli | 今天又是大勝,克萊打的真好!
kuli | 明年聽說有條大魚要來,誰呀?
(2 行記錄)
mydb=# select * from twitters;
id | user_id | content
----+---------+----------------------------------
1 | 1 | 今天又是大勝,克萊打的真好!
2 | 2 | 今晚我得了60分,哈哈!
3 | 3 | 獲勝咱不怕,缺誰誰尷尬.
4 | 4 | 明年我也可能轉會西部
5 | 5 | 我都雙20+了，怎麼球隊就是不勝呢?
6 | 1 | 明年聽說有條大魚要來,誰呀?
(6 行記錄)
mydb=# update twitters set content = '變更後的值' where id = 1;
UPDATE 1
mydb=# select * from curry_twitters;
nba_player | content
------------+----------------------------
kuli | 明年聽說有條大魚要來,誰呀?
kuli | 變更後的值
(2 行記錄)
# 刪除檢視
mydb=# drop view curry_twitters;
DROP VIEW
mydb=# \dv
沒有找到任何關係.
資料庫事務(Database Transaction) ，是指作為單個邏輯工作單元執行的一系列操作，要麼完全地執行，要麼完全地不執行。 事務處理可以確保除非事務性單元內的所有操作都成功完成，否則不會永久更新面向資料的資源。通過將一組相關操作組合為一個要麼全部成功要麼全部失敗的單元，可以簡化錯誤恢復並使應用程式更加可靠。一個邏輯工作單元要成為事務，必須滿足所謂的ACID（原子性、一致性、隔離性和永續性）屬性。事務是資料庫執行中的邏輯工作單位，由DBMS中的事務管理子系統負責事務的處理。
begin
commit
rollback
mydb=# select * from users;
id | nba_player | score | team
----+------------+-------+---------
1 | kuli | 28.3 | yongshi
2 | hadeng | 30.2 | huojian
3 | adu | 25.6 | yongshi
5 | shengui | 31.3 | leiting
6 | baibian | 19.8 | rehuo
4 | azhan | 29.1 | qishi
(6 行記錄)
# 開啟事務
mydb=# begin;
BEGIN
mydb=# update users set score = 50.0 where id = 1;
UPDATE 1
mydb=# update users set score = 60 where id = 2;
UPDATE 1
# 事務提交前查詢 (可以查詢到未提交讀 - 與事務隔離級別有關)
mydb=# select * from users;
id | nba_player | score | team
----+------------+-------+---------
3 | adu | 25.6 | yongshi
5 | shengui | 31.3 | leiting
6 | baibian | 19.8 | rehuo
4 | azhan | 29.1 | qishi
1 | kuli | 50 | yongshi
2 | hadeng | 60 | huojian
(6 行記錄)
# 事務回滾
mydb=# rollback;
ROLLBACK
# 再次查詢，資料為原始資料
mydb=# select * from users;
id | nba_player | score | team
----+------------+-------+---------
1 | kuli | 28.3 | yongshi
2 | hadeng | 30.2 | huojian
3 | adu | 25.6 | yongshi
5 | shengui | 31.3 | leiting
6 | baibian | 19.8 | rehuo
4 | azhan | 29.1 | qishi
(6 行記錄)
# 開始事務
mydb=# begin;
BEGIN
mydb=# update users set score = 50.0 where id = 1;
UPDATE 1
mydb=# update users set score = 60 where id = 2;
UPDATE 1
# 提價事務
mydb=# commit;
COMMIT
mydb=# select * from users;
id | nba_player | score | team
----+------------+-------+---------
3 | adu | 25.6 | yongshi
5 | shengui | 31.3 | leiting
6 | baibian | 19.8 | rehuo
4 | azhan | 29.1 | qishi
1 | kuli | 50 | yongshi
2 | hadeng | 60 | huojian
(6 行記錄)