postgresql的基本sql语句

问题：postgresql的sql语句有哪些？如何显示数据库、数据表？

方法：

1、显示数据库

mysql: show databases;
postgresql: select datname from pg_database;
          或 \l

2、显示数据表

mysql: show tables;
postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'mydatabases';
或 SELECT tablename FROM pg_tables WHERE schemaname = 'public';
注：mydatabase为schema名，默认是public
或
\c mydatabase
\d

3、显示表字段

mysql: describe table_name;
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table_name';
注：tabel_name为数据表名
或
\d tablename

4、显示表名注释

SELECT
    relname AS tabname,
    CAST(obj_description(relfilenode, 'pg_class') AS VARCHAR) AS comment
FROM
    pg_class c
WHERE
    relname = 'sjzt_test.yh';

或

SELECT obj_description('sjzt_test.yh'::regclass);

5、显示字段注释

SELECT
    a.attname AS field,
    t.typname AS type,
    b.description AS comment
FROM pg_class c, pg_attribute a
    LEFT JOIN pg_description b
    ON a.attrelid = b.objoid
        AND a.attnum = b.objsubid, pg_type t
WHERE c.relname = '%s'
    AND a.attnum > 0
    AND a.attrelid = c.oid
    AND a.atttypid = t.oid
ORDER BY a.attnum;

或

SELECT
    a.attname AS NAME,
    format_type ( a.atttypid, a.atttypmod ) AS type,
    col_description ( a.attrelid, a.attnum ) AS COMMENT
FROM
    pg_class AS c,
    pg_attribute AS a 
WHERE
    c.relname = 'snapshot' 
    AND a.attrelid = c.oid 
    AND a.attnum >0

6、创建数据库

# 创建数据库（如果需要）
CREATE DATABASE mydb;

# 连接到该数据库（在客户端执行）
\c mydb

# 在数据库中创建schema
# 在 PostgreSQL 中，每创建一个新数据库时，系统会自动创建一个名为 public 的默认 Schema。
CREATE SCHEMA sjzt_test;

7、在模式下建表

创建 schema
CREATE SCHEMA sjzt_test;

在 sjzt_test schema 下创建 yh 表，使用中文首字母字段名
CREATE TABLE sjzt_test.yh (
    id SERIAL PRIMARY KEY,
    xm VARCHAR(50) NOT NULL,  -- 姓名
    xb VARCHAR(10),           -- 性别
    nl INTEGER                -- 年龄
);

-- 为表添加注释
COMMENT ON TABLE sjzt_test.yh IS '用户信息表';

-- 为字段添加注释
COMMENT ON COLUMN sjzt_test.yh.xm IS '用户姓名';
COMMENT ON COLUMN sjzt_test.yh.xb IS '用户性别';
COMMENT ON COLUMN sjzt_test.yh.nl IS '用户年龄';

其它curd语句和mysql一样

参考：

https://www.runoob.com/postgresql/postgresql-select-database.html