PostgreSQL一文以概之

最新推荐文章于 2023-06-17 23:12:06 发布

拦路雨g

最新推荐文章于 2023-06-17 23:12:06 发布

阅读量840

点赞数

分类专栏： sql 文章标签： postgresql 数据库 sql

本文链接：https://blog.csdn.net/lanluyug/article/details/120491008

版权

sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

SQL标准

ISO/IEC 9075:1992,简称SQL92，是最通用的SQL标准。
PostgreSQL非常接近SQL92标准。

SQL命令类型

DML：Data Manipulation Language, 数据操作语言:。增删改查。
DDL：Data Definition Language，数据定义语言。结构控制。
DCL：Data Control Language，数据控制语言。访问权限控制。

简单的SQL命令

建表

create table test_table(
id serial,
name varchar(64)
);

插入

insert into test_table (id, name) values (1,'lanluyu');

insert into test_table values (1,'lanluyu');

删除

delete from tableName where columnName= 'value';

-- 全表数据删除，无法恢复
truncate table tableName;

修改

update tableName set column1 = 'newValue1',column2='newValue2' where columnN = 'valueN'

查询

select id, name from test_table;

数据类型

bool 布尔
- 1 字节
integer 整型
serial 自动设置为一个唯一的数字
char 定长字符数组
varchar 可变长的字符数组
date 日期
numeric 定位的数字
- numeric(7,2)将存储7位数字，2位小数。
PostgreSQL扩展类型
二进制大对象（BLOB）
- 可定义成bytea类型（byte array）

bit

bit(n)
bit varying(n)

bool

drop table if exists test_bool;
create table test_bool(
val varchar(8),
flag bool
);

翻译为true(不区分大小写)	翻译为false
‘1’	‘0’
‘yes’	‘no’
‘y’	‘n’
‘true’	‘false’
‘t’	‘f’

自测

insert into test_bool values ('true',true);
insert into test_bool values ('1','1');
insert into test_bool values ('t','t');
insert into test_bool values ('Y','Y');
insert into test_bool values ('no','no');
insert into test_bool values ('f','f');
insert into test_bool values ('NULL',NULL);
insert into test_bool values ('FALSE','FALSE');
insert into test_bool values ('0','0');
insert into test_bool values ('n','n');
insert into test_bool values ('N','N');

-- 如果插入其他值则会报错
insert into test_bool values ('x','x');-- 报错

返回结果

val （varchar）	flag (bool)
TRUE	TRUE
1	TRUE
t	TRUE
Y	TRUE
no	FALSE
f	FALSE
NULL	NULL
FALSE	FALSE
0	FALSE
n	FALSE
N	FALSE

bool运算(AND, OR,NOT)

select 'true'::bool and 'false'::bool aa;

数值类型

smallint
- 2字节
int
- 4字节
bigint
- 8字节
numeric
- 变长
real
- 4字节
double precision
- 8字节
serial
- int
bigserial
- bigint

字符（最大1GB）

char 单个字符
char(n) n个定长字符
varchar(n) n个可变长度字符
text 长度不限制的字符串
数字类型

子类型	标准名	描述
Small Integer	Smallint	2字节符号整数，-32768~32767
Integer	Int	2字节符号整数，-2147483648~2147483647
Serial		和int一样
float	float(n)	支持最少精度为n，存储最多8字节的浮点数
float8	real	双精度（8字节）浮点数字
numeric	numeric(p,s)	拥有p个实数，s位小数，始终是一个确切的数字，效率比浮点数低
decimal
money	numeric(9,2)	pg特有的类型，在高版本已经取消

numeric(5,2)最大只能存储999.99

时间类型

定义	意义
date	存储日期信息
time	存储时间信息
timestamp	存储日期和时间
interval	存储timestamp之间的差别信息
timestamptz	存储包含时区的timestamp

特殊数据类型

定义	意义
box	矩形
line	一组点
point	一对几何学的数字
lseg	一条线段
polygon	一条封闭的几何线
cidr或inet	一个IPv4的地址，录入192.168.0.1
macaddr	MAC地址

数组

create table test_array(
label varchar(32),
val  int[]
);
insert into test_array values 
('val01','{1,0,2,4}');
insert into test_array values 
('val02','{1,0,2,4,5}');

select * from test_array;
-- 结果
-- label	val
-- val01	{1,0,2,4}
-- val02	{1,0,2,4,5}
select val[3] from test_array where label='val02';
select val[1:2] from test_array where label='val02';

注意pg的下标是从1开始的，而不是0；

强转的两种方式

select cast(val[3] as varchar(8)) from test_array where label='val02';

select val[3]::varchar(8) from test_array where label='val02';

魔法变量

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER

select current_date,current_time,current_timestamp,current_user;

返回结果

current_date	current_time	current_timestamp	current_user
2021/7/10	13:56:18.853743+08:00	2021-07-10 13:56:18.853743+08	postgres

Blob类型

create table test_blob(
	id varchar(64),
	image bytea
);

复合类型(自定义类型)

-- 自定义类型
create type inventory_item as (
name varchar(64),
id int,
price numeric
);

create table on_hand(
item inventory_item,
count int
);

insert into on_hand values (
'("lanluyu",28,1.1)', 1
);

select (item).name from on_hand;

枚举类型

create type data_source_item as enum (
'MIMC_MobileNetWork',
'MIMC_FixedLineNetWork'
);

日期

PostgreSQL使用的date输出标准
- ISO-8601样式输出
- 输出格式：YYYY-MM-DD hh:mm:ss.ssTZD
- 例如： 2021-07-08 17:05:30.23+5表示2021年7月8号下午五点五分30.23秒，时区在UTC之前五小时。
输入
- 字符串形式
- 例如：2005年2月1日
  - ‘2005-02-01’、‘2005/02/01’、‘2005 02 01’、‘2005|02|01’、‘2005.02.01’
  - ‘February 1,2005’
设置日期格式

create table test_date(
	day date
);

insert into test_date values ('2005-02-01');

insert into test_date values ('2005/02/01');

insert into test_date values ('2005 02 01');

insert into test_date values ('2005|02|01');


select cast('2021-07-08 12:00:11:30.23+5' as date);
select cast('2021-07-08' as date);
// timestamp类型
select cast('2021-07-08' as timestamp);

insert into test_date VALUES ('2021-06-08');

insert into test_date VALUES ('2021-07-08');

select * from test_date where date_part('month', day)=7;
-- 结果：2021-07-08

-- 比较日期
select * from test_date where day <> cast('2021-07-08' as date);
-- 结果：2021-06-08

insert into test_date VALUES (DATE'01/02/03');
-- 2001-02-03

show datestyle;
-- ISO, YMD
set datestyle = MDY;
insert into test_date VALUES (DATE'01/02/03');
-- 2003-01-02

insert into test_time VALUES (TIME'10:20:02');
insert into test_time VALUES (TIMESTAMP'01/02/03 10:20:02');

可以提取的部分（date_part第一个参数）

Year
Month
Day
Hour
Minute
Second

select now(),CURRENT_TIMESTAMP;

时间相减会得到天数

select now() - day from test_date
-- 32 days 09:55:20.966894

多表操作

表关联

select columns from tableA a, tableB b where a.id = b.id
-- tableName as t或者tableName t为表取别名

也可转变为：

select columns from tableA a Join tableB b on (a.id = b.id)

Union和Union all

-- union all 不去重
-- union 去重
select columns from tableA
union（或者 union all）
select columns from tableB

一般使用union all

自连接

-- 自测环境
create table part(
id int,
name varchar(32),
part_id int
);

insert into part values(1, 'table and chairs',null);
insert into part values(2, 'table',1);
insert into part values(3, 'chair',1);

-- 自连接
select p1.name as name1, p2.name as name2 from part p1, part p2
where p1.id = p2.part_id;

表连接

左连接 left join
- 返回：左表和公共部分
右连接 right join
- 返回：右表和公共部分
内连接 inner join
- 返回：公共部分

intersect返回共同行

select empno,ename,job,sal,deptno
from emp
where (ename,job,sal) in (
select ename,job,sal from emp
    intersect
    select ename,job,sal from V
)

查找另一个表没有的值

select deptno from dept
except 
select deptno from emp;

select deptno from dept where deptno not in (select deptno from emp)

更新

update emp
	set sal = sal * 1.2
	where exists ( select 1
                 from emp_bonus
                  where emp.empno = emp_bonus.empno
                 )

上述select null 和select * 在exists中产生一致作用

数据交互

插入特殊字符

insert into tableName values ('O\'Rourke','Street A\\33');
-- 插入O'Rourke,Street A\33

查看所有表名

select * from pg_tables;

创建序列

create sequence test_seq;

生成值

select nextval('test_seq');
select nextval('test_seq');
-- 1
-- 2

设置值

select setval('test_seq',1);

copy命令(psql命令不需要加“；”)

\copy tableName from 'filePath'

\copy tableName from 'filePath' using delimiter ','

通过另外一个表更新

update tableA set columnA = b.columnB from tableB b

事务

一个有限的数据库操作序列构成的单元

目的
- 从失败中恢复；
- 多客户端隔离。
事务的属性（ACID）
- 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
- 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
- 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
- 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。

select * from test_date;

-- 开始一个事务，或者BEGIN TRANSACTION;
begin;

-- 有限个操作
delete from test_date where day='2021-02-03';

-- 事务确认
commit;

-- 事务回滚
ROLLBACK;

使用停留点

begin;

-- 使用停留点
SAVEPOINT savepoint_name;    -- 声明一个 savepoint

-- 有限个操作
delete from test_date where day='2021-02-03';

ROLLBACK TO savepoint_name;  -- 回滚到savepoint

RELEASE SAVEPOINT savepoint_name;  -- 删除指定保留点


-- 事务确认
commit;

-- 事务回滚
ROLLBACK;

当执行有限个事务的过程中发生报错，则无法继续执行数据库操作，只能commit 或者 rollback。

锁

防止用户破坏数据库的一致性

排他锁 EXCLUSIVE
- 其他事务不能对它读取与修改
共享锁 SHARE
- 可以被其他事务读取，但不能修改

-- 语法形式
LOCK [ TABLE ]
name
 IN
lock_mode

lock_mode
- ACCESS SHARE
  - 只与Access Exclusive锁模式冲突。
  - 查询命令（Select command）将会在它查询的表上获取Access Shared锁，一般地，任何一个对表上的只读查询操作都将获取这种类型锁。
- ROW SHARE
  - 与Exclusive和Access Exclusive锁模式冲突。
  - Select for update和Select for share命令将获得这种类型锁，并且所有被引用但没有for update 的表上会加上Access Shared锁。
- ROW EXCLUSIVE
  - 与Share，Shared Row Exclusive，Exclusive，Access Exclusive模式冲突。
  - Update/Delete/Insert命令会在目标表上获得这种类型的锁，并且在其它被引用的表上加上Access Share锁，一般地，更改表数据的命令都将在这张表上获得Row Exclusive锁。
- SHARE UPDATE EXCLUSIVE
  - Share Update Exclusive，Share，Share Row Exclusive，Exclusive，Access exclusive模式冲突，这种模式保护一张表不被并发的模式更改和Vacuum。
  - Vacuum(without full)，Analyze 和 Create index concur-ently命令会获得这种类型锁。
- SHARE
  - 与Row Exclusive，Shared Update Exclusive，Share Row Exclusive，Exclusive，Access exclusive锁模式冲突，这种模式保护一张表数据不被并发的更改。
  - Create index命令会获得这种锁模式。
- SHARE ROW EXCLUSIVE
  - 与Row Exclusive，Share Update Exclusive，Shared，Shared Row Exclusive，Exclusive，Access Exclusive锁模式冲突。
  - 任何PostgreSQL命令不会自动获得这种类型的锁。
- EXCLUSIVE
  - 与ROW Share , Row Exclusive, Share Update Exclusive, Share , Share Row Exclusive, Exclusive, Access Exclusive模式冲突，这种锁模式仅能与Access Share 模式并发，换句话说，只有读操作可以和持有Exclusive锁的事务并行。
  - 任何PostgreSQL命令不会自动获得这种类型的锁。
- ACCESS EXCLUSIVE
  - 与所有模式锁冲突(Access Share，Row Share，Row Exclusive，Share Update Exclusive，Share , Share Row Exclusive，Exclusive，Access Exclusive)，这种模式保证了当前只有一个人访问这张表；ALTER TABLE，DROP TABLE，TRUNCATE，REINDEX，CLUSTER，VACUUM FULL 命令会获得这种类型锁，在Lock table 命令中，如果没有申明其它模式，它也是默认模式。

详细查看：https://blog.csdn.net/greywolf0824/article/details/85072530

begin;

-- 事务结束才会释放锁
LOCK TABLE test_date IN EXCLUSIVE MODE;

修改表结构

加字段

alter table tableName add column columnName columnType;

删字段

alter table tableName drop column columnName;

改字段名称

alter table tableName rename column oldColumnName to newColumnName;

改字段类型

alter table tableName columnName type newType[using expression];

改默认值

alter table tableName alter column [set DEFAULT value | drop default];

改约束

alter table tableName alter column [set not null | drop not null];

alter table tableName add check check_express;

alter table tableName add constraint name constraint_define;

修改分布键

alter table tablename set DISTRIBUTED BY (columnname);

查看主键

select pg_constraint.conname as pk_name from pg_constraint;

删除主键

alter table tablename drop constraint primaryname

新增主键

alter table dic_protocol_info add constraint dic_protocol_info_pkey primary key (no);

联合主键

alter table rds_source_define add constraint rds_source_define_pkey PRIMARY KEY ("sourceno", "field_name");

改表名

alter table oldTableName rename to newTableName;

删除表

drop table tableName;

权限

在 PostgreSQL 中，权限分为以下几种：

SELECT
INSERT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER
CREATE
CONNECT
TEMPORARY
EXECUTE
USAGE

GRANT 语法

GRANT 命令的基本语法如下：

GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }

privilege − 值可以为：SELECT，INSERT，UPDATE，DELETE， RULE，ALL。
object − 要授予访问权限的对象名称。可能的对象有： table， view，sequence。
PUBLIC − 表示所有用户。
GROUP group − 为用户组授予权限。
username − 要授予权限的用户名。PUBLIC 是代表所有用户的简短形式。

另外，我们可以使用 REVOKE 命令取消权限，REVOKE 语法：

REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }

 -- 创建用户
 CREATE USER lanluyu WITH PASSWORD 'password';
 
 -- 分配权限
 GRANT ALL ON test_date TO lanluyu;
 
 -- 权限撤销
 REVOKE ALL ON test_date FROM lanluyu;
 
 -- 删除用户
 DROP USER lanluyu;

外键约束

create table test_names(
id serial,
name varchar(32),
constraint names_pk primary key (id)
);
create table test_references(
no serial,
id integer,
constraint ref_id_fk foreign key (id) references test_names(id)
);
insert into test_names values (default, '1');
insert into test_names values (default, '2');

select * from test_names;

insert into test_references values (default, 1);
-- 错误:  在 "test_names" 上的更新或删除操作违反了在 "test_references" 上的外键约束 "ref_id_fk"
-- DETAIL:  键值对(id)=(1)仍然是从表"test_references"引用的.
delete from test_names where id=1;
-- 成功
delete from test_names where id=2;

延迟约束

允许违反外键约束

create table test_references(
no serial,
id integer,
constraint ref_id_fk foreign key (id) references test_names(id) initially deferred
);

ON UPDATE 和ON DELETE

删除主键，相应的外键行也会被删除

create table test_references(
no serial,
id integer,
constraint ref_id_fk foreign key (id) references test_names(id) on delete cascade
);

删除主键，外键列会被置为null

create table test_references(
no serial,
id integer,
constraint ref_id_fk foreign key (id) references test_names(id) on update set null
);

组合使用

create table test_references(
no serial,
id integer,
constraint ref_id_fk foreign key (id) references test_names(id) on delete cascade on update set null
);

视图

定义：从一个表或多个表导出的虚拟表；

View（视图）是一张假表，只不过是通过相关的名称存储在数据库中的一个 PostgreSQL 语句。

优点：
- 1. 简化操作；
  2. 安全性，只能看到指定数据；
  3. 逻辑独立性。
缺点：
- 性能差
- 修改限制
建立视图

create view viewName as select-statement;

使用视图

select * from viewName;

\d viewName可以查看视图描述

删除视图

drop view viewName;

替换视图

create or replace view viewName as select-statement;

窗口函数

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

pg窗口命令

命令	描述
?	获得帮助消息
\do	列出操作类型
\dt	列出表
\dT	列出类型
\h	列出SQL的命令
\i	执行文件内的sql
\r	重置缓冲器（忽略任何输入）
\q	退出psql
\c databaseName	切换数据库
\d+ tableName	表结构
\!	在pg窗口执行shell命令

拦路雨g

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
PostgreSQL一文以概之

SQL标准ISO/IEC 9075:1992,简称SQL92，是最通用的SQL标准。PostgreSQL非常接近SQL92标准。SQL命令类型DML：Data Manipulation Language, 数据操作语言:。增删改查。DDL：Data Definition Language，数据定义语言。结构控制。DCL：Data Control Language，数据控制语言。访问权限控制。简单的SQL命令建表create table test_table(id serial
复制链接

扫一扫