PostgreSql 操作

zhangly 2021-01-17 22:55:17
Categories: > Tags:

数据库操作

# 创建数据库
CREATE DATABASE dbname;

# 列出数据库 list
\l

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

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

表操作

创建表

# 方法一:使用关键字创建
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

常用类型

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

查看表

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

# 查看当前数据库表list
\dt 

# 常看指定表
\d {post_name}

删除表

DROP TABLE {table_name};

添加表约束

为表的字段添加约束条件

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

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

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

添加条目到表中

语法

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

示例

# 逐条插入
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

# 查看所有
select * from users;

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

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

过滤条件 WHERE

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

示例

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语句过滤数据后,再对数据进行更多选项操作(如排序,随机抽选)

# 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;

过滤后条目计算

#列出所有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;

常用函数

更多:

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

示例:

# 返回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 [table] set [field=newvalue, ...] where ...
delete from [table] where ...

示例:

# 更新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 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;

操作多个表

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

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

(这个数据是用户的提交描述)

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, '整理文件');

示例:

# 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;

视图

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

示例:

# 创建视图,视图的名称为 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;

事务

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

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

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

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

示例:

# 提交操作

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 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

用户操作

# 创建用户
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

权限

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

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

角色管理

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

# 创建一个角色
create role {name};

# 查看角色表
select * from pg_roles;