PostgreSQL 入门实践

2021/7/2 19:25:19

编程Tag： 入门 id postgresql Player 实践 Postgres users score mydb

本文主要是介绍PostgreSQL 入门实践，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

PostgreSQL 学习手册

文章目录

[*PostgreSQL 学习手册*]()
一、什么是PostgreSQL
- 1、认识PostgreSQL
- 2、数据库的分类
二、PostgreSQL的部署
- 1、基于RPM方式部署
三、PostgreSQL SQL学习
- 1、数据库操作
- 2、表操作
- 3、表增删改
- 4、表的查询
- - （1）素材准备
  - （2）查看表信息
  - （3）按照约束条件产看表信息
  - (4)模糊匹配
  - (5)统计查询
  - （6）套用函数查询
  - (7)多表查询
- 5、修改表结构
- 6、索引
- 7、视图
四、事物
- 1、开启事物
- 2、回滚

一、什么是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)

这篇关于PostgreSQL 入门实践的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！