PostgreSQL数据库基础

 环境

• Ubuntu 20.04

• PostgreSql 12.2

 

psql的卸载与安装

1.删除相关的安装
sudo apt-get --purge remove postgresql\*
​
2.删除配置及文相关件
sudo rm -r /etc/postgresql/
sudo rm -r /etc/postgresql-common/
sudo rm -r /var/lib/postgresql/
​
3.删除用户和所在组
sudo userdel -r postgres
sudo groupdel postgres
​
4.重新安装
sudo apt-get install postgresql
​
5.查看psql版本
psql --version

 

psql的简单使用

～～bash 终端环境

$ sudo su postgres    # 切换为postgre用户，操作psql数据库
$ psql --version      # 查看当前psql数据库的版本
$ psql -l             # 查看当前所有的psql数据库
$ createdb hcl        # 创建psql数据库
$ psql -l
$ psql hcl            # 进入psql数据库hcl
> help  
> \h                  # 获取sql帮助
> \?                  # 获取psql帮助
> \l
> \q                  # 退出当前数据库
$ psql hcl
> select now();       # 获取当前时间
> select version()l   # 获取当前psql版本
> \q
$ dropdb hcl          # 删除psql数据库
$ psql -l

 

psql操作数据库表

～～bash 终端环境

$ sudo su postgres    # 切换为postgre用户，操作psql数据库
$ createdb itcast     # 创建psql数据库
$ psql itcast         # 进入psql数据库
> create table article(title varchar(255), content text);     # 创建数据库表
> \dt                # 列出所有表
> \d article         # 查看article表的详细信息
> alter table article rename to articles;    # 修改数据库表名
(-# 表示上一条语句未输入完毕，可能是缺少分号(;)，会报错，=# 表示上一条语句输入完毕)
> drop table articles;# 删除数据库表
> \q                  # 退出数据库

～～bash 终端环境

可复用sql语句

$ su root    # 切换到root用户
$ [root用户下] nano db.sql   # 创建sql文件
'''
create table article(title varchar(255), content text);
'''
$ sudo su postgres          # 切换到postgres用户
$ psql itcast               # 进入数据库中
> \i db.sql                 # 执行sql文件中的语句
> \dt                       # 查看数据库所有表

 

psql 添加表约束

create table posts(
        id serial primary key,
        title varchar(255) not null,
        content text check(length(content)>8),
        is_draft boolean default TRUE,
        is_delete boolean default FALSE,
        created_date timestamp default 'now'
);
​
'''
约束条件：
not null：不能为空
unique： 数据唯一
check：字段设置条件
default： 字段默认值
primary key（not null， unique）：主键，非空且唯一
'''

psql的插入语句（insert）

$ psql hcl
> \dt posts
​
> insert into posts (title,content) values ('','');   # 报错，content长度要大于8
> insert into posts (title,content) values (null,''); # 报错，title不能为null（可以为空）
> insert into posts (title,content) values ('title1','content11');  # 插入成功
> select * from posts;  # 查看posts表中的数据

psql的查询语句（select）

1. 创建数据库表并插入数据

$ su root
$ nano db2.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
('kuli', 28.3, 'yongshi'),
('hadeng', 30.2, 'huojian'),
('adu', 25.6, 'yongshi'),
('azhan',27.8,'qishi'),
('shengui', 31.3,'leiting'),
('baibian',19.8,'rehuo');
​
'''
​
$ sudo su postgres
$ psql hcl
> \i db2.sql
> \dt

2. 查询数据库数据

> select * from users;  # 查询users数据表中的所有数据
> \x  # 开启拓展显示（纵向显示）
> select * from users;
? \x  # 关闭拓展显示
> select * from users;
> select players,score from users;  # 查询指定字段数据

 

psql的条件语句（where）

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

$ psql hcl
> 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 = 'yongshi';
> select * from users where team != 'yongshi';
> select * from users where player like 'a%';
> select * from users where player like 'a__';

 

psql的数据抽出选项

select语句在抽出数据时，可以对语句设置更多的选项，来获取想要的数据

• order by

• limit

• offset

$ psql hcl
> select * from users order by score; # 按照得分排序
> 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;  # 获取3条数据
> select * from users order by score desc limit 3 offset 1; # 偏移量为1（跳过第一条数据），获取3条数据
> select * from users order by score desc limit 3 offset 2; # 偏移量为2（跳过第二条数据），获取3条数据

 

psql统计抽出的数据

• distinct

• sum

• max/min

• group by/ having

$ psql hcl
> 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; # 以球队分组，获取每个球队的最大得分且最大得分要高于25分
> select team,max(score) from users group by team having max(score)>25 order by max(score); # 以球队分组，获取每个球队的最大得分且最大得分要高于25分，并进行排序
 

 

psql常用函数

• length

• concat # 连接两个字符串

• as # 别名

• substring # 截取

• random

$ psql hcl
> select player,length(player) from users;  # 获取运动员名称及长度
> select player,concat(player,'/',team) from users;
# 获取运动员名称及所属球队
> select player,concat(player,'/',team) as "introduce" from users;  # 获取运动员名称及所属球队并起别名
> select player,substring(team,1,1) as "First"  from users; # 获取运动员名称，并获取球队首字
> select player,concat('my ',substring(team,1,1)) as "First"  from users; # 获取运动员名称，并进行截取、连接、起别名
> select random();  # 随机数
> select * from users order by random();  # 随机排序
> select * from users order by random() limit 1;  # 随机排序 获取第一条数据

  

psql的更新和删除（物理删除）

• update [table] set [field = new value,...] where ...

• delete from [table] where ...

$ psql hcl
> update users set score = 29.1 where player = 'azhan';    # 更新azhan的得分为29.1
> update users set score = score + 1 where team = 'yongshi';    # 更新勇士队的得分，加一分
> update users set score = score + 100 where team IN ('yongshi','qishi');    # 更新勇士队和骑士队的得分，加一百分
> delete from users where score > 30;    # 删除得分大于30的数据，物理删除

 

psql变更表结构及索引的添加删除

• alter table [tablename] ...

• create index [index_name] on table(field)

• drop index [index_name]

$ psql hcl
> \d users; # 查看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;

 

psql的连表操作

1. 创建数据库表并插入数据

$ su root
$ nano 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, '明年听说有条大鱼要来,谁呀?');
'''
​
$ dropdb hcl;
$ createdb hcl;
$ psql hcl;
> \i renew.sql

2. 查询数据库数据

> select * from users;
> select * from twitters;
> select users.player,twitters.content from users,twitters where users.id = twitters.user_id;   # 查询球员的姓名和发布的twitters内容
> select u.player,t.content from users as u,twitters as t  where u.id = t.user_id;
  # 查询球员的姓名和发布的twitters内容
> select u.player,t.content from users as u,twitters as t  where u.id = t.user_id and u.id=1;
  # 查询id为1的球员的姓名和发布的twitters内容

 

 

使用视图

视图封装了select语句

对于经常需要查询的语句，可以提前建立视图view，方便于编码和管理。

• create view [viewname] as [select语句]

• drop view [viewname]

$ psql hcl
> 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; 
# 创建视图curry_twitters
> \dv # 查看所有视图
> \d curry_twitters # 查看curry_twitters视图的详细信息
> select * from curry_twitters; # 使用视图进行查询
> \dv 
> drop view curry_twitters; # 删除视图
> \dv

 

 

使用事务

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

• PostgresSql数据库事务的使用

  • begin

  • commit

  • rollback

$ psql hcl
> 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;