postgresql常用的sql语句

一、数据库常用简写命令

1、登录

psql -U pname -d database  // pname 表示postgres的登录用户名,database 则表示要访问的数据库 

2、查看所有的数据库 \l

\l+   列出所有数据库 (加 "+" 获取更多的信息) 

postgresql常用的sql语句

3、查看所有的表(这里的红色部分根据你要查看的数据库的名字来改变)

\ddatabasename(不能有空格)

postgres-# \dpostgres
 public | connection_alert_contents                    | table    |                   |                   | 
 public | connection_alert_contents_id_seq             | sequence |                   |                   | 
 public | connection_alert_settings                    | table    |                   |                   | 
 public | connection_alert_settings_id_seq             | sequence |                   |                   | 
 public | connection_groups                            | table    |                   |                   | 
 public | connection_groups_id_seq                     | sequence |                   |                   | 

\dt  只列出非系统表

# \dt
 public | database_performances                 | table | postgres
 public | deadlocks                             | table | postgres

4、查看表的结构(这里的红色部分根据你要查看的表的名字来改变)

\d tablename

postgres-# \d   学生信息表
 序号   | bigint                 |           | not null | 
 姓名   | character varying(255) |           | not null | 
 性别   | character varying(255) |           | not null | 
 班级   | character varying(255) |           | not null | 
 年级   | character varying(255) |           | not null | 
 身份证 | character varying(255) |           | not null | 

5、切换数据库

\c database

postgresql常用的sql语句

6、\du  列出用户 

postgres-# \du
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

7、\df  列出函数 
8、\di  只列出索引 
9、\do  只列出操作符 
10、\ds  只列出序列 
11、\dS  列出系统表和索引
12、\db  列出表空间 (加 "+" 获取更多的信息) 
13、\dg  列出组 
14、\dn  列出模式 (加 "+" 获取更多的信息) 
15、\do  列出操作符 
16、\dl  列出大对象, 和 \lo_list 一样 
17、\dp  列出表, 视图, 序列的访问权限

18、\dT  列出数据类型 (加 "+" 获取更多的信息)

二、增删改查

1、 创建database

CREATE DATABASE database_name;

CREATE DATABASE 测试数据库
WITH 
OWNER = postgres
ENCODING = ‘UTF8‘
LC_COLLATE = ‘en_US.UTF-8‘
LC_CTYPE = ‘en_US.UTF-8‘
TABLESPACE = pg_default
CONNECTION LIMIT = -1;

postgres=# \l
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 tinadb     | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 zj         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 测试数据库 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

2、创建schema

CREATE SCHEMA schema_name;

3、查看数据库下面所有的schema

select * from information_schema.schemata;

4、查看一个schema下面所有的表

select * from information_schema.tables where table_schema = ‘schema_name‘;

5、创建角色

CREATE ROLE role_name [...];

 

[...]为可多选角色属性参数,参数可分别为:

 

  1. 登陆(login):具有login属性的角色可以连接数据库,一个带有login属性的角色可以等同于“数据库用户”,因为数据库用户创建的时候默认自带login属性.
  2. 超级用户(superuser):超级用户拥有至高无上的权限,但是这个权限分配的时候,一定要小心,想要创建超级用户,必须使用带超级用户的角色去创建带“超级用户”的角色 .
  3. 创建数据库(createdb):创建数据库属性,顾名思义就是拥有“创建数据库”属性的角色可以创建数据库,对于超级用户的角色来说,自身可以创建数据库,不用带“创建数据库”属性 .
  4. 创建角色(createrole):带有“创建角色”属性的角色可以创建其他角色。一般带有“创建角色”属性的角色还可以删除和更改其他角色(删除和更改只限于普通用户) 
  5. 口令(password):只有在客户认证方法要求与数据库建立连接必须使用口令的时候, 口令才比较重要.

 

在创建好角色后,如果需要修改的话,可以使用alter role语句修改

 

12.权限设置

 

将名字为schema_name的schema的所有权限给予role_name角色;

 

grant all on schema schema_name to role_name;

 

将名字为schema_name的schema下面所有的表的操作权限给予role_name角色;

 

grant all on all tables in schema schema_name to role_name;

 

如果你不准备将所有的权限都给予role_name角色,可以给予部分的权限,每个权限值之间以逗号分隔,而权限的明细如下:

 

  • SELECT:该权限用来查询表或是表上的某些列,或是视图,序列。
  • INSERT:该权限允许对表或是视图进行插入数据操作,也可以使用COPY FROM进行数据的插入。
  • UPDATE:该权限允许对表或是或是表上特定的列或是视图进行更新操作。
  • DELETE:该权限允许对表或是视图进行删除数据的操作。
  • TRUNCATE:允许对表进行清空操作。
  • REFERENCES:允许给参照列和被参照列上创建外键约束。
  • TRIGGER:允许在表上创建触发器。
  • CREATE:对于数据库,允许在数据库上创建Schema;对于Schema,允许对Schema上创建数据库对象;对于表空间,允许把表或是索引指定到对应的表空间上(在Oracle中,我们是用quota子句或是unlimited tablespace权限来控制)。该权限与Oracle上的CREATE类权限完全不同了。
  • CONNECT:允许用户连接到指定的数据库上。
  • TEMPORARY或是TEMP:允许在指定数据库的时候创建临时表。
  • EXECUTE:允许执行某个函数。
  • USAGE:对于程序语言来说,允许使用指定的程序语言创建函数;对于Schema来说,允许查找该Schema下的对象;对于序列来说,允许使用currval和nextval函数;对于外部封装器来说,允许使用外部封装器来创建外部服务器;对于外部服务器来说,允许创建外部表。
  • ALL PRIVILEGES:表示一次性给予可以授予的权限。

 

 

 三、常用脚本

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

https://github.com/digoal/blog/blob/master/202005/20200509_02.md

postgresql常用的sql语句

上一篇:mysql排序查询


下一篇:mysql约束