首页 pgsql基本操作

pgsql基本操作

技术张儿 2020-8-5 0 29
标签: postgresql

\l   # 查看所有数据库
\c   # 切换数据库
\di  # 查看索引
\dt  # 查看表
\ds  # 查看序列
\q   # 推出
\timing on  # 开机sql查询时间
\dns  # 查看schema
\du   # 查看用户

[postgres@pg01 ~]$ ps -ef|grep postgres
[postgres@pg01 ~]$ psql -Upostgres -d postgres
psql (11.8)
Type "help" for help.

postgres=# \l #查看所有数据库

postgres=# create database test; #创建数据库
CREATE DATABASE

postgres=# \c test; #切换数据库
You are now connected to database "test" as user "postgres".

test=# \dns #查看schema
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)
test=# create schema data;
CREATE SCHEMA
test=# \dns
  List of schemas
  Name  |  Owner   
--------+----------
 data   | postgres
 public | postgres
(2 rows)

test=# create table data.test (id int,name varchar(64)); #创建表
CREATE TABLE
test=# \dt data.* #查看表
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 data   | test | table | postgres
(1 row)

test=# create table test1 (id int,name varchar(64),unique (id)); #创建表
CREATE TABLE
test=# \dt #查看表
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | test1 | table | postgres
(1 row)

test=# \di #查看索引
                List of relations
 Schema |     Name     | Type  |  Owner   | Table 
--------+--------------+-------+----------+-------
 public | test1_id_key | index | postgres | test1
(1 row)

序列类似于自增id
test=# create table test2 (id serial not null,name varchar(64));
CREATE TABLE
test=# \ds #查看
              List of relations
 Schema |     Name     |   Type   |  Owner   
--------+--------------+----------+----------
 public | test2_id_seq | sequence | postgres
(1 row)

test=# create sequence seq1; #创建自增
CREATE SEQUENCE
test=# \ds
              List of relations
 Schema |     Name     |   Type   |  Owner   
--------+--------------+----------+----------
 public | seq1         | sequence | postgres
 public | test2_id_seq | sequence | postgres
(2 rows)

sql执行时间
test=# \timing on
Timing is on.
test=# select * from test1;
 id | name 
----+------
(0 rows)

Time: 0.920 ms

查看用户
test=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}


作者: 技术张儿 本文发布于2020-8-5 09:18:25
免责声明:本文仅代表作者个人观点。