PostgreSQL — 常规操作

目录

用户管理

• 创建用户

 CREATE USER <username> WITH PASSWORD <password>;

• 删除用户

DROP USER <username>;

• 分配权限

 GRANT ALL ON <table_name> TO <username>;

• 撤销权限

REVOKE ALL ON <table_name> FROM <username>;

库操作

操作	SQL 语句	指令
创建数据库	CREATE DATABASE <db_name>	createdb <db_name>
删除数据库	DROP DATABASE <db_name>	dropdb <db_name>
选择数据库	\c <db_name>	psql <db_name>
列出所有库	\l	psql -l

表操作

操作	SQL/指令
创建数据表	CREATE TABLE <table_name>(<column_name> , …);
删除数据表	DROP TABLE <table_name>;
列出表格	\dt
列出视图	\dv
查看表格	\d <table_name>
文件导入表格	\i db.sql
变更表结构	alter table <table_name> add/drop/rename/alter type …；
创建索引	create index <index_name> on <table_name>(column);
删除索引	drop index <index_name>;
操作多个表	select ., . where . = .
删除数据	TRUNCATE TABLE <table_name>; 只删除数据，不删除表

INSERT 语句

INSERT INTO <table_name> (column1, column2, column3) VALUES (value1, value2, value3), (value11, value22, value33)

SELECT 语句

SELECT * FROM <table_name>;
SELECT <column1>, <column2> FROM <table_name>;

WHERE 语句

SELECT * FROM <table_name> WHERE <column>=<value>;
UPDATE <table_name> SET <column1>=<value1>, <column2>=<value2> WHERE [condition];

LIKE 语句

LIKE 语句配合通配符可以获取包含某些字符的数据，：

• % 任意字符
• _ 单个字符

SELECT FROM <table_name> WHERE column LIKE '%bcd%';

AND & OR 语句

• AND：同时成立。
• OR：至少满足一个。

SELECT * FROM <table_name> WHERE <column1> >= <value1> AND/OR <column2> < <value2>;

ORDER BY 语句

• asc：正序。
• desc：倒序。
• limit：前几个。
• offset：偏移量。

SELECT * FROM <table_name> order by <column_name> asc;

JOIN 语句

• INNER JOIN：内连接（默认）。

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

• CROSS JOIN：交叉连接。

SELECT ... FROM table1 CROSS JOIN table2 ...

• LEFT OUTER JOIN：左外连接。

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

• RIGHT OUTER JOIN：右外连接。

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ..

• FULL OUTER JOIN：全外连接。

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...

VIEW 语句

将常用的 SELECT 语句简化为一个视图（View）对象，便于简单读取和开发：

create view <view_name> as select <table_name>.<column1> where <table_name>.<column1> = <value>;

触发器

PostgreSQL 触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。

• 创建触发器：

CREATE TGIGGER <trigger_name> [BEFORE|AFTER|INSTEAD OF] <event_name> ON <table_name>;

• 列出触发器：

SELECT * FROM pg_trigger;

• 删除触发器：

drop trigger ${trigger_name} on ${table_of_trigger_dependent};

事务

保证原子性操作的 3 条指令：

• begin：开始。
• commit：提交。
• rollback：回滚。

内置函数

• distinct：过滤重复的数据。
• sum：求和。
• max/min：最大值/最小值。
• group by/having：对集合进行分组再进行计算 /过滤。
• length：长度。
• concat： 连接字符串。e.g. concat(column，'/'，column2)
• alias：别名。
• substring：切割字符串。
• random：随机数。