首页  思维导图  详情

PostgreSQL

2020-12-24 14:25:57   38  举报





AI智能生成

为你推荐

查看更多



PostgreSQL核心功能模块(不含监控模块)

告警分析

postgreSQL数据库

PostgreSQL Citus Cluster

PostgreSQL

作者其他创作

大纲/内容

PostgreSQL

语法

数据类型

数值

smallint

integer

bigint

decimal

numeric

real

double precision

smallserial

serial

bigserial

货币

money

字符

character(n)

char(n)

变长，有长度限制

character varying(n)

varchar(n)

变长，无长度限制

text

日期/时间

timestamp [ (p) ] [ without time zone ]

timestamp [ (p) ] with time zone

date

time [ (p) ] [ without time zone ]

time [ (p) ] with time zone

interval [ fields ] [ (p) ]

布尔

boolean

枚举

CREATE TYPE enum_name AS ENUM (data_list);

几何

point

line

lseg

box

path

polygon

circle

网络地址

cidr

inet

macaddr

位串

文本搜索

tsvector

tsquery

UUID

XML

XMLPARSE (DOCUMENT xml_text)

XMLPARSE (CONTENT xml_text)

JSON

array_to_json()

row_to_json()

数组

integer[]

text[][]

复合

范围

int4range

int8range

numrange

tsrange

tstzrange

daterange

自定义

对象标识符

oid

regproc

regprocedure

regoper

regoperator

regclass

regtype

regconfig

regdictionary

伪

any

anyelement

anyarray

anynonarray

anyenum

anyrange

cstring

internal

language_handler

fdw_handler

record

trigger

void

opaque

自动递增

SMALLSERIAL

SERIAL

BIGSERIAL

运算符

算术运算符

|/ 25.0 结果为 5

||/

||/ 27.0 结果为 3

5 ! 结果为 120

!! 5 结果为 120

比较运算符

逻辑运算符

AND

NOT

按位运算符

命令

切换到 postgres 用户

linux

sudo -i -u postgres

windows

mac

连接

psql [OPTIONS] [db_name] [user_name]

切换库

\\c db_name

设置

编码

\\encoding encode_type

密码

\\password user_name

输出

边框

\\pset border 0 | 1 | 2

单行/多行

自动提交

\\set autocommit on | off

\\set ECHO_HIDDEN on|off

分隔符

;

\\g

导入

\\i file

psql -x -f file

导出

\\o file

查看

库

\\l

\\encoding

span style=\

show server_encoding;

表

\\dt

\\d tablename

表空间

\\db

索引

\\di

模式

\\dn

视图

\\dv

序列

\\ds

函数

\\df

\\df func_name

用户

\\du

\\dg

权限

\\dp

\\z

\\conninfo

\\copyright

版本

select version();

\\echo string

帮助

\\h command

\\help command_name

\\?

退出

\\q

SQL

用户/角色

创建

CREATE USER | ROLE name [[WITH] options];

options参数

SUPERUSER | NOSUPERUSER

CREATEDB | NOCREATEDB

CREATEROLE | NOCREATEROLE

CREATEUSER | NOCREATEUSER

INHERIT | NOINHERIT

REPLICATION | NOREPLICATION

BYPASSRLS | NOBYPASSRLS

CONNECTION LIMIT connlimit

[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'

VALID UNTIL 'timestamp'

SYSID uid

createuser user_name

删除

DROP USER | ROLE name;

dropuser user_name

修改

ALTER USER | ROLE name WITH options;

SELECT * FROM pg_roles;

select * from pg_user;

\\du+

授权

GRANT privileges ON objects TO [PUBLIC | user_name | GROUP group_name];

撤销

REVOKE privileges ON objects FROM [PUBLIC | user_name | GROUP group_name];

参数值

privileges

ALL PRIVILEGES

INSERT

DELETE

UPDATE

SELECT

TRUNCATE

REFERENCES

TRIGGER

CREATE

CONNECT

TEMPORARY

EXECUTE

USAGE

objects

table

view

sequence

CREATE DATABASE db_name;

createdb [OPTIONS] db_name [description]

使用 pgAdmin 工具

DROP DATABASE [IF EXISTS] db_name;

dropdb [CONNECT_OPTIONS] [OPTIONS] db_name

选择/连接

psql [OPTIONS] db_name

约束

NOT NULL

UNIQUE

DEFAULT

PRIMARY KEY

FOREIGN KEY

EXCLUSION

CHECK

ALTER TABLE table_name DROP CONSTRAINT some_name;

DROP TABLE table_name;

重置

TRUNCATE TABLE table_name;

列

添加

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

显式转换

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype USING column_name::datatype;

ALTER TABLE table_name ADD constraint_type;

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type;

ALTER TABLE table_name ALTER column_name SET NOT NULL;

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

select * from pg_tables;

数据

插入

DELETE FROM table_name [WHERE conditions];

清空

查询

SELECT CURRENT_TIMESTAMP;

SELECT (15 + 6) AS ADD_RESULT;

SELECT age FROM my_table;

SELECT age as MY_AGE FROM my_table WHERE age >18;

SELECT DISTINCT name FROM my_table;

SELECT * FROM sqlite_master;

SELECT age AS my_new_name FROM table_name;

SELECT age my_new_name FROM table_name;

SELECT * FROM table_name AS my_new_name;

Distinct

SELECT DISTINCT column FROM table_name;

FROM

CROSS JOIN

SELECT * FROM table1 CROSS JOIN table2;

INNER JOIN

SELECT * FROM table1 INNER JOIN table2 ON condition;

LEFT OUTER JOIN

SELECT * FROM table1 LEFT OUTER JOIN table2 ON condition;

RIGHT OUTER JOIN

SELECT * FROM table1 RIGHT OUTER JOIN table2 ON condition;

FULL OUTER JOIN

SELECT * FROM table1 FULL OUTER JOIN table2 ON condition;

WHERE

SELECT * FROM table_name WHERE [condition];

SELECT * FROM table_name WHERE [condition1] AND [condition2]...AND [conditionN];

BETWEEN

SELECT * FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN];

SELECT * FROM table_name WHERE column LIKE 'XXXX%';

SELECT * FROM table_name WHERE column LIKE 'XXXX_';

NOT IN

EXISTS

IS NOT

IS NULL

GROUP BY

HAVING

SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;

ORDER BY

SELECT * FROM COMPANY ORDER BY SALARY ASC;

SELECT * FROM COMPANY ORDER BY NAME DESC;

LIMIT

SELECT * FROM table_name WHERE [condition] LIMIT [no of rows];

SELECT * FROM COMPANY LIMIT 3;

SELECT * FROM table_name WHERE [condition] LIMIT [no of rows] OFFSET [row num];

SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

子查询

WITH

WITH with_name AS (SQL)

WITH RECURSIVE t(n) AS (VALUES (0) UNION ALL SELECT SALARY FROM COMPANY WHERE SALARY < 20000)SELECT sum(n) FROM t;

WITH moved_rows AS ( DELETE FROM COMPANY WHERE SALARY >= 30000 RETURNING *)INSERT INTO COMPANY1 (SELECT * FROM moved_rows);

UNION

UNION ALL

CREATE SCHEMA my_schema[.my_table](...);

create schema myschema;

DROP SCHEMA my_schema;

DROP SCHEMA my_schema CASCADE;

事务

开始

BEGIN;

BEGIN TRANSACTION;

结束

END TRANSACTION;

COMMIT;

回滚

ROLLBACK

DROP VIEW view_name;

单列索引

CREATE INDEX index_name ON table_name (column_name);

组合索引

唯一索引

CREATE UNIQUE INDEX index_name ON table_name (column_name);

局部索引

CREATE INDEX index_name ON table_name (conditional_expression);

隐式索引

DROP INDEX index_name;

触发器

CREATE TRIGGER trigger_name[BEFORE | AFTER | INSTEAD OF] event_name ON table_name[FOR EACH [ROW | STATEMENT]][WHEN conditions][-- 触发器逻辑....];

CREATE TRIGGER trigger_name[BEFORE | AFTER | INSTEAD OF] event_name ON table_name[FOR EACH [ROW | STATEMENT]][WHEN conditions]EXECUTE PROCEDURE func();

DROP TRIGGER [IF EXISTS] trigger_name ON table_name;

SELECT * FROM pg_trigger;

锁

LOCK [TABLE] lock_name IN mode;

类型

排它锁

共享锁

死锁

咨询锁

数学

abs(x)

pi()

sign(double/numeric)

sign(-8.4)

sign(8.4)

sign(0)

幂

根

sqrt(double/numeric)

cbrt(double)

对数

ln(double/numeric)

log(double/numeric)

指数

exp(double/numeric)

转型

向下取整

ceil(double/numeric)

ceil(42.8)

向上取整

floor(double/numeric)

floor(42.8)

四舍五入取整

round(double/numeric)

round(42.4)

四舍五入取浮点数

截断

trunc(double/numeric)

trunc(42.8)

角度/弧度

degrees(double)

radians(double)

随机数

random()

三角函数

acos(x)

asin(x)

atan(x)

cos(x)

cot(x)

sin(x)

tan(x)

字符串

加长

string 丨丨 string

'Post' 丨丨 'greSQL'

大小写

upper(string)

upper('tom')

initcap(text)

initcap('hi thomas')

lower(string)

lower('TOM')

convert(string using conversion_name)

convert('PostgreSQL' using iso_8859_1_to_utf8)

ascii(text)

ascii('x')

chr(int)

chr(65)

md5(string text)

\tto_ascii('Karel')

to_hex(number int/bigint)

\tto_hex(9223372036854775807)

长度

bit_length(string)

bit_length('jose')

octet_length(string)

octet_length('jose')

char_length(string)

char_length('jose')

length(string text)

length('jose')

位置

position(substring in string)

position('om' in 'Thomas')

替换

overlay(string placing string from int [for int])

overlay('Txxxxas' placing 'hom' from 2 for 4)

截取

substring(string [from int] [for int])

\tsubstring('Thomas' from 2 for 3)

substring(string from pattern)

substring('Thomas' from '…$')

substring(string from pattern for escape)

substring('Thomas' from '%#\"o_a#\"_' for '#')

去除

trim([leading丨trailing 丨 both] [characters] from string)

trim(both 'x' from 'xTomxx')

填充

类型转换

转数字

转字符串

转时间/日期

to_timestamp(double precision)

接口

Python

断开

connect.close()

游标

获取

cursor = connect.cursor()

关闭

cursor.close()

提交

connect.commit()

执行SQL

单 | 多个

单个

insert_sql = \

多个

参数

无

有

获取查询结果

一条

select_sql = \"SELECT * FROM my_tb\"cursor.execute(select_sql)result = cursor.fetchone()

多条

select_sql = \"SELECT * FROM my_tb\"cursor.execute(select_sql)result = cursor.fetchall()print(result)

 收藏

立即使用

Python

 收藏

立即使用

Git

 收藏

立即使用

Redis

 收藏

立即使用

Linux

啊喔鹅

职业：本科

去主页





0 条评论

回复删除



取消

下一页