postgreSQL 操作

数据库操作

1
2
3
4
5
6
7
8
9
10
11
# 创建数据库
CREATE DATABASE dbname;

# 列出数据库 list
\l

# 进入数据库 connect
\c {database_name}

# 删除数据库
DROP DATABASE [IF EXISTS] {database_name}

表操作

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 方法一：使用关键字创建
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,  # 4字节整数类型， 主键， 不能为空
   NAME           TEXT    NOT NULL,  # 文本类型，不能为空
   AGE            INT     NOT NULL,  # 4字节整数类型，不能为空
   ADDRESS        CHAR(50),          # 字符串类型（限制50个字符）
   SALARY         REAL               # 4字节浮点数类型
);

create table posts (title varchar(255), content text);

# 方法二：将上述内容写在一个文件中，通过sql文件来创建
vi db.sql
\i db.sql

常用类型

  • 数值型
    • integer 4字节整数类型（范围 2147483648 到 +2147483647）
    • real 4字节浮点数类型（6 位十进制数字精度）
    • serial 自增整数类型
  • 文字型
    • char 字符串类型（缺点：会自动用空格填充到字节限制）
    • varchar 字符串类型
    • text 文本类型
  • 布尔型
    • boolean 布尔类型（作逻辑删除，数据库不做实际删除）
  • 日期型
    • date 日期类型
    • time 时间类型
    • timestamp 日期类型 + 时间类型
  • 特殊类型
    • Array 数组类型
    • inet ip地址
    • json json格式
    • xml xml格式

官方文档关于数据类型：https://www.postgresql.org/docs/current/datatype.html

查看表

这里的查看表并不是查看表的数据，而是查看表的字段和字段详细信息

1
2
3
4
5
# 查看当前数据库表list
\dt 

# 常看指定表
\d {post_name}

删除表

1
DROP TABLE {table_name};

添加表约束

为表的字段添加约束条件

1
2
3
4
5
not null:     不能为空
unique：      在所有数据中值必须唯一
check：       自定义一个check函数
default：     字段默认值
primary key： 标识表的主键（一般为id）

一个添加表的范例（包含一些限制条件和默认值），后续的案例都会用到这个表。

1
2
3
4
5
6
7
8
9
10
create table users (
	id serial primary key unique,              # 设置id字段为主键，且不可重复
	name varchar(255) not null,                # 限制字节长度为255，且字段不能为空
	age int default 0,                         # 默认值为0
	salary real,                               
	department varchar(50),                    # 限制字节长度为50
	password text check(length(password) > 3), # 字符长度大于3
	is_del boolean default FALSE,              # 设置字段默认值为false
	created_date timestamp default 'now'       # 设置字段默认值为 {当前时间}
);

添加条目 INISERT

添加条目到表中

语法

1
2
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 逐条插入
insert into users (name) values ('liyc');
insert into users (name, age, salary, department) values ('shiva', 18, 6500.0, 'pmd');

# 多条插入
insert into users (name, age, salary, department, password) values
	('bobo', 42, 25000.0, 'lay', 'dlhf2@fas'),
	('iris', 28, 13750.0, 'pmd', 'fasf2@fas'),
	('zhangly', 19, 200.0, 'ple', 'qff2@fas'),
	('yanyan', 20, 300.0, 'ple', '456f2@fas'),
	('chris', 32, 7000.0, 'ani', 'ZLFhf2@fas'),
	('tina', 26, 11000.0, 'rto', 'OPf2@fSs');

# 查看表条目
select * from users;

查看条目 SELECT

1
2
3
4
5
6
7
8
# 查看所有
select * from users;

# 查看指定字段
select name, age from users;

# 切换显示方式，横向/纵向
\x

过滤条件 WHERE

select，update，delete语句都可以使用where语句过滤条件

示例

1
2
3
4
5
6
7
select * from users where age > 20;                 # age 字段大于20
select * from users where age < 30;                 # age 字段小于30
select * from users where age > 20 and age < 30;    # age 字段大于20且小于30
select * from users where department = 'ple';       # department 字段等于ple
select * from users where department != 'ple';      # department 字段不等于ple
select * from users where name like 'zhang%';       # 以zhang开头
select * from users where name like 'zhang_';       # 以zhang开头，后面仅一个字符

过滤后条目操作

使用select语句过滤数据后，再对数据进行更多选项操作（如排序，随机抽选）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# order 排序  asc升序  desc降序
select * from users order by age asc;
select * from users order by age desc;
select * from users order by department;
select * from users order by department, age;
select * from users order by department, age desc;
select * from users order by department desc, age desc;

# limit 从头取多少个
select * from users order by age desc limit 3;

# offset 排序偏移
select * from users order by age desc limit 3 offset 1;
select * from users order by age desc limit 3 offset 2;
select * from users order by age desc limit 3 offset 3;

过滤后条目计算

  • distinct 去重
  • sum 加计算
  • max/ min 找最大/最小
  • group by/ having 分组/ 分组过滤条件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#列出所有department，并去重复
select distinct department from users;

# 计算出所有人salary总和
select sum(salary) from users;

# 取出salary最大或最小值
select max(salary) from users;
select min(salary) from users;

# 取出age最大或最小的一行条目
select * from users where age = (select max(age) from users);
select * from users where age = (select min(age) from users);

# 以department来分组，取出每个组age最大的
select department, max(age) from users group by department;

# 以department来分组，组内salary的最大值如果不大于5000，过滤掉
select department, max(salary) from users group by department having max(salary) > 5000;

常用函数

  • length 长度
  • concat 连接字符串
  • alias 别名
  • substring 切割字符串
  • random 随机

更多：

https://www.postgresql.org/docs/current/functions.html

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 返回name和name的长度
select name, length(name) from users;

# 字段相加department + '_' + name
select name, concat(department, '_', name) from users;

# as 给字段取个别名
select name, concat(department, '_', name) as "部门和人员" from users;

# 切割字符串name，步数为1，取1个值
select substring(name, 1, 1) as "名字的第一个字母" from users;

# concat,substring,as结合使用
select concat(name, '_', substring(department, 1, 1)) as "未命名" from users;

# 随机对表排序
select * from users order by random();

# 随机对表排序，抽出第一个值
select * from users order by random() limit 1;

# 随机对表排序，抽出前三个值
select * from users order by random() limit 3;

表条目更新和删除

  • update 更新
  • delete 删除

语法：

1
2
update [table] set [field=newvalue, ...] where ...
delete from [table] where ...

示例：

1
2
3
4
5
6
7
8
9
10
11
# 更新name=liyc的信息
update users set age = 18, salary=1950.2, department='lay' where name = 'liyc';

# 给name=liyc的age增加1
update users set age = age + 1 where name = 'liyc';

# 给部门为lay和ple的薪资加5k
update users set salary = salary + 5000 where department in ('lay', 'ple');

# 删除年龄大于50的user
delete from users where age > 50;

变更表结构

  • alter 增删或修改字段
  • create index 创建索引
  • drop index 删除索引

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 添加一个字段
alter table users add cn_name varchar(255);

# 删除一个字段（非常不建议的操作）
alter table users drop cn_name

# 修改字段名
alter table users rename cn_name to "中文名";

# 修改字段类型和限制条件
alter table users alter department type varchar(20);

# 创建索引
create index name_index on users(name);

# 删除索引
drop index name_index;

操作多个表

对多个表的数据进行组合，关联查询

为方便示例先新建一个表，并添加一些数据：

（这个数据是用户的提交描述）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table notes (
	id serial primary key,
	user_id integer,
	content varchar(255) not null
);

insert into notes (user_id, content) values
(1, '提交了modu_rig文件v002'),
(1, '修改test文件'),
(2, '更新xx到v22版本'),
(2, 'layout完成20%'),
(3, '提交了modu_rig文件v004'),
(4, '修改test021文件'),
(5, 'layout完成60%'),
(6, '修改test文件，并提交'),
(7, '提交了modu_rig文件v005'),
(8, '整理文件');

示例：

1
2
3
4
5
6
7
8
# users表的name，和notes表的content根据user_id关联，然后取出
select users.name, notes.content from users, notes where users.id = notes.user_id;

# 添加别名，减少代码数
select u."中文名", n.content from users as u, notes as n where u.id = n.user_id;

# 只提出user_id=1的notes
select u."中文名", n.content from users as u, notes as n where u.id = n.user_id and u.id=1;

视图

  • create view
  • drop view

View视图可以理解为一个SELECT语句，将项目中常用的SELECT语句简化成一个虚拟表对象。

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 创建视图，视图的名称为 zhangly_notes
# as后的语句则是过滤条件
create view zhangly_notes as select u.name, n.content from users as u, notes as n where u.id = n.user_id and u.name='zhangly';

# 查看 zhangly_notes 视图
select * from zhangly_notes;

# 查看所有视图
\dv

# 查看制定视图详情
\d zhangly_notes

# 删除视图
drop view zhangly_notes;

事务

  • begin 开始事务
  • commit 提交事务
  • rollback 回滚事务

Transaction事务，是指单个逻辑工作单元执行的一系列操作，确保该操作要么完全地执行，要么完全不执行。

举个例子，如果要一次性的对数据库更新一万条数据，可能要花费好几分钟时间。

在数据更新的时候，如果断电，则会导致有的数据更新成功，而有的数据没有更新。

为了避免这样的问题，使用事务进行预更新操作，当所有命令都执行完成后，才进行实际的更新。

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 提交操作

begin;  //开始事务
update users set department = 'lay' where name = 'zhangly';  //进行一些更新
update users set department = 'lay' where name = 'yanyan';
update users set department = 'lay' where name = 'liyc';
commit;  //执行到这一步的时候，才会进行实际的更新操作

# 回滚操作

begin;
update users set department = 'pmd' where name = 'zhangly';  //进行一些更新
update users set department = 'ani' where name = 'yanyan';
update users set department = 'cfx' where name = 'liyc';
rollback; //回滚到事务开始，也就是删除上述的预更新

备份和恢复

  • pg_dump 备份单个数据库
  • pg_dumpall 备份所有数据库
  • pg_restore 恢复数据库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 备份数据库，需要退出交互模式下进行

pg_dump mydb > mydb.bak
pg_dump -f /tmp/mydb.bak mydb
pg_dump - F t /tmp/postgres.tar mydb # 指定为tar压缩格式

pg_dumpall > pg_backup.bak

# 恢复数据库
# 对于bak格式的文件，可以直接用psql运行进行恢复
CREATE DATABASE mydb; # 首先要创建数据库
psql -f /tmp/mydb.bak mydb

# 对于tar压缩格式
pg_restore /tmp/mydb.tar -d mydb

用户操作

1
2
3
4
5
6
7
8
9
10
11
# 创建用户
create user test with password 'test';

# 删除用户，删除之前要释放所有权限
drop user test;

# 查看所有用户
\du

# 查看用户表
select * from pg_user;

配置文件

配置文件路径：C:\Program Files\PostgreSQL\14\data\postgresql.conf
和C:\Program Files\PostgreSQL\14\data\pg_hba.conf

权限

1
2
3
4
5
6
# 使用管理员账户进入数据库进行给予
\c mydb;
grant all privileges on all tables in schema public to test;

# 删除权限
revoke all privileges on all tables in schema public from test;

角色管理

角色和用户的不同是，角色不带有登录属性。

1
2
3
4
5
6
# 创建一个角色
create role {name};

# 查看角色表
select * from pg_roles;