首页  思维导图  详情

PostgreSQL

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





AI智能生成

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

复合

CREATE TYPE my_name AS (
字段名类型,
......
);

范围

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

分隔符

;

导入

\i file

psql -x -f file

导出

\o file

查看

库

编码

\encoding

show client_encoding;

show server_encoding;

表

\dt

\d tablename

表空间

\db

索引

\di

模式

\dn

视图

\dv

序列

\ds

函数

\df

\df func_name

用户

\du

\dg

权限

\dp

连接

\conninfo

\copyright

版本

select version();

输出

\echo string

帮助

\h command

\help command_name

退出

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'

IN ROLE role_name [, ...]

IN GROUP role_name [, ...]

ROLE role_name [, ...]

ADMIN role_name [, ...]

USER role_name [, ...]

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

\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

使用 pgAdmin 工具

选择/连接

\c db_name

psql [OPTIONS] db_name

使用 pgAdmin 工具

表

约束

创建

NOT NULL

UNIQUE

DEFAULT

PRIMARY KEY

FOREIGN KEY

EXCLUSION

CREATE TABLE COMPANY7(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT,
AGE INT ,
EXCLUDE USING gist
(NAME WITH =, -- 如果满足 NAME 相同，AGE 不相同则不允许插入，否则允许插入
AGE WITH <>) -- 其比较的结果是如果整个表边式返回 true，则不允许插入，否则允许
);

CHECK

删除

ALTER TABLE table_name DROP CONSTRAINT some_name;

自动递增

SMALLSERIAL

SERIAL

BIGSERIAL

创建

CREATE TABLE table_name(
column1 datatype,
column2 datatype,
.....
columnN datatype,
PRIMARY KEY( 一个或多个列 )
);

删除

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;

数据

插入

INSERT INTO table_name (column1, column2,...columnN) VALUES (value1, value2,...valueN);

INSERT INTO table_name VALUES (value1,value2,value3,...valueN);

删除

DELETE FROM table_name [WHERE conditions];

清空

TRUNCATE TABLE table_name;

修改

UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN [WHERE conditions];

查询

SELECT [Distinct] column1, column2, columnN [AS my_new_column_name]
[FROM table_name] [AS my_new_table_name]
[WHERE [conditions | expression]]
[GROUP BY column1, column2....columnN]
[HAVING [conditions]]
[ORDER BY column1, column2, .. columnN] [ASC | DESC]
[LIMIT [no of rows] [OFFSET [row num]]]
;

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

AND

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

SELECT * FROM table_name WHERE [conditions]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN;

SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

HAVING

SELECT * FROM table_name WHERE [conditions]
GROUP BY column1, column2....columnN
HAVING [conditions];

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

ORDER BY

SELECT * FROM table_name WHERE [condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

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;

子查询

SELECT column_name1,... column_nameN FROM table1 [, table2 ]
WHERE column_name operator
(SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE])

WITH

WITH with_name AS (SQL)

With CTE AS (Select ID, NAME, AGE FROM COMPANY )
Select * From CTE;

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

SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
UNION
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition];

UNION ALL

SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
UNION ALL
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition];

模式

创建

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

create schema myschema;

create table myschema.company(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
PRIMARY KEY (ID)
);

删除

DROP SCHEMA my_schema;

DROP SCHEMA my_schema CASCADE;

事务

开始

BEGIN;

BEGIN TRANSACTION;

结束

END TRANSACTION;

COMMIT;

回滚

ROLLBACK

视图

创建

CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];

删除

DROP VIEW view_name;

索引

创建

单列索引

CREATE INDEX index_name ON table_name (column_name);

组合索引

CREATE INDEX index_name ON table_name (column1, column2);

唯一索引

CREATE UNIQUE INDEX index_name ON table_name (column_name);

局部索引

CREATE INDEX index_name ON table_name (conditional_expression);

隐式索引

删除

DROP INDEX index_name;

查看

\d tablename

\di

触发器

创建

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)

mod(y, x)

pi()

sign(double/numeric)

sign(-8.4)

sign(8.4)

sign(0)

幂

power(a double, b double)

power(a numeric, b numeric)

根

sqrt(double/numeric)

cbrt(double)

对数

ln(double/numeric)

log(double/numeric)

log(b numeric,x numeric)

指数

exp(double/numeric)

转型

向下取整

ceil(double/numeric)

ceil(42.8)

向上取整

floor(double/numeric)

floor(42.8)

四舍五入取整

round(double/numeric)

round(42.4)

四舍五入取浮点数

round(v numeric, s int)

round(42.438,2)

截断

trunc(double/numeric)

trunc(42.8)

trunc(v numeric, s int)

trunc(42.438,2)

角度/弧度

degrees(double)

radians(double)

随机数

random()

三角函数

acos(x)

asin(x)

atan(x)

atan2(x, y)

cos(x)

cot(x)

sin(x)

tan(x)

字符串

加长

string 丨丨 string

'Post' 丨丨 'greSQL'

repeat(string text, number int)

repeat('Pg', 4)

大小写

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)

convert(string text, [src_encoding name,] dest_encoding name)

convert( 'text_in_utf8', 'UTF8', 'LATIN1')

ascii(text)

ascii('x')

chr(int)

chr(65)

md5(string text)

to_ascii(text [, encoding])

to_ascii('Karel')

to_hex(number int/bigint)

to_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')

strpos(string, substring)

strpos('high','ig')

替换

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

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

replace(string text, from text, to text)

replace('abcdefabcdef', 'cd', 'XX')

translate(string text, from text, to text)

translate('12345', '14', 'ax')

截取

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

substring('Thomas' from 2 for 3)

substring(string from pattern)

substring('Thomas' from '…$')

substring(string from pattern for escape)

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

substr(string, from [, count])

substr('alphabet', 3, 2)

split_part(string text, delimiter text, field int)

split_part('abc~@~def~@~ghi', '~@~', 2)

去除

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

trim(both 'x' from 'xTomxx')

btrim(string text [, characters text])

btrim('xyxtrimyyx','xy')

ltrim(string text [, characters text])

ltrim('zzzytrim','xyz')

rtrim(string text [, character text])

rtrim('trimxxxx','x')

填充

lpad(string text, length int [, fill text])

lpad('hi', 5, 'xy')

rpad(string text, length int [, fill text])

rpad('hi', 5, 'xy')

类型转换

转数字

to_number(text, text)

转字符串

to_char(timestamp, text)

to_char(interval, text)

to_char(int, text)

to_char(double precision, text)

to_char(numeric, text)

转时间/日期

to_date(text, text)

to_timestamp(text, text)

to_timestamp(double precision)

接口

Python

连接

connect = psycopg2.connect(host='192.168.149.133', port='5432',
user='postgres', password='postgres',
database='my_db')

断开

connect.close()

游标

获取

cursor = connect.cursor()

关闭

cursor.close()

提交

connect.commit()

执行SQL

单 | 多个

单个

insert_sql = "INSERT INTO my_tb VALUES (999, 'admin', 99, '男');"
cursor.execute(insert_sql)

多个

insert_sql = "INSERT INTO my_tb VALUES (%s, %s, %s, %s);"
params = [
[x, '张{}'.format(x), x, '男' if x % 2 else '女'] for x in range(18)
]
cursor.executemany(insert_sql, params)

参数

无

insert_sql = "INSERT INTO my_tb VALUES (999, 'admin', 99, '男');"
cursor.execute(insert_sql)

有

insert_sql = "INSERT INTO my_tb VALUES (%s, %s, %s, %s);" cursor.execute(insert_sql, (999, 'admin', 99, '男'))

获取查询结果

一条

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)