PostgreSql

发表于 | 分类于

psql常用命令

连接数据库

psql -h [ip] -U [username] -d [databases]

  • 创建schema
    CREATE SCHEMA my_schema;
    GRANT ALL ON SCHEMA my_schema TO my_user;
  • 查看当前schema
    SHOW search_path;
  • 查询数据库下所有schema
    select * from information_schema.schemata;
  • 查询指定schema下所有表
    select * from information_schema.tables where table_schema=’audit’;
  • 切换schema（连接级）
    set search_path to my_schema;
  • 改变默认schema（数据库级）
    ALTER database “my_database” SET search_path TO my_schema;
  • 指定schema查询
    select count(*) from audit.fxk_pg_oplog;

pg基础知识与基本操作

查询pg库的所有表名称
select tablename from pg_tables where schemaname=’public’;
获取pg表的字段
select column_name from information_schema.columns
where table_name = ‘db_job’;

–查看当前活动的客户端连接数
SELECT count(*) FROM pg_stat_activity WHERE NOT pid=pg_backend_pid();
–查看PostgreSQL正在执行的SQL
SELECT
procpid,
start,
now() - start AS lap,
current_query
FROM
(SELECT
backendid,
pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_activity_start(S.backendid) AS start,
pg_stat_get_backend_activity(S.backendid) AS current_query
FROM
(SELECT pg_stat_get_backend_idset() AS backendid) AS S
) AS S ,pg_stat_activity pa
WHERE
current_query <> ‘‘ and procpid<> pg_backend_pid() and pa.pid=s.procpid and pa.state<>’idle’
ORDER BY
lap DESC;
–查看当前库表和索引的的大小并排序显示前20条
SELECT
nspname,
relname,
relkind as “type”,
pg_size_pretty(pg_table_size(C.oid)) AS size,
pg_size_pretty(pg_indexes_size(C.oid)) AS idxsize,
pg_size_pretty(pg_total_relation_size(C.oid)) as “total”
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN (‘pg_catalog’, ‘information_schema’) AND
nspname !~ ‘^pg_toast’ AND
relkind IN (‘r’,’i’)
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;

procpid：进程id start：进程开始时间 lap：经过时间 current_query：执行中的sql 怎样停止正在执行的sql SELECT pg_cancel_backend(进程id); 或者用系统函数 kill -9 进程id; --查找是否有waiting ps -ef|grep postgres | grep wait

排序：ORDER BY 字段 DESC
模糊不区分大小写查询：UPPER(字段) LIKE UPPER(‘%${search}%’)
查指定条数：LIMIT 长度 OFFSET 起始位置

关联查询：
SELECT fail_count
FROM test_report AS f
INNER JOIN(SELECT MAX(id) AS b_id FROM test_build WHERE job_id = #{jobId}) r
ON f.build_id = r.b_id

去重返回
SELECT DISTINCT
T.parent_id
FROM
db_task AS T
INNER JOIN db_job AS j ON T.status IN (
‘running’,
‘initComplete’,
‘fail’
)
AND j.status IN (‘created’, ‘dispatched’)

输入到文件

  • 将查询语句写入文件
    1
    2
    > \w /txt
    > select...
  • 将查询结果输入到文件
    1
    2
    > \o /txt
    > select...

WITH AS 用法

  • 定义
    也叫做自查询部分，可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高，也可以在UNION ALL的不同部分，作为提供数据的部分。
    对于UNION ALL，使用WITH AS定义了一个UNION ALL语句，当该片断被调用2次以上，优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。而提示meterialize则是强制将WITH AS短语的数据放入一个全局临时表中。很多查询通过该方式都可以提高速度。

  • 用法

    1. 创建一个临时表查询
      1
      2
      WITH cte AS (SELECT * FROM task WHERE status = 1)  //相当于创建了一个cte临时表
      SELECT * FROM task AS t WHERE cte.id = t.id
    2. 创建两个临时表查询 
      1
      2
      3
      4
      WITH
      e AS (SELECT * FROM emp),
      d AS (SELECT * FROM dept)
      SELECT * FROM e, d where e.id = d.id;
    3. 修改指定数据状态 
      1
      2
      3
      4
      WITH cte AS (
          SELECT * FROM task WHERE status = 1
      ) 
      UPDATE task AS t SET status = 2 FROM WHERE t.id = cte.id AND t.status = cte.status

  • 优点

    1. SQL可读性增强。比如对于特定with子查询取个有意义的名字等。
    2. with子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能。

RETURNING 语句

  • 特性

    1. DELETE的情况，RETURNING返回的肯定是DELETE之前的结果。
    2. INSERT的情况，RETURNING返回的一定是INSERT之后的结果。
    3. UPDATE的情况，RETURNING返回的一定是UPDATE之后的结果。
    4. RETURNING语句似乎和RETURN通用。（即两个可以相互替换）
    5. INSERT INTO VALUES语句支持RETURNING语句，而INSERT INTO SELECT语句不支持。
    6. MERGE语句不支持RETURNING语句。

  • 注意

    1. 不能与DML语句和远程对象一起使用；
    2. 不能检索LONG类型信息；
    3. 当通过视图向基表中插入数据时，只能与单基表视图一起使用。

  • 用法
    场景是获取指定任务，但需要修改状态

    1
    2
    3
    4
    5
    WITH cte AS (
        SELECT * FROM task WHERE status = 1
    )
    UPDATE task AS t SET status = 2 FROM cte WHERE t.id = cte.id
    RETURNING t.*

    UPDATE SET FROM

  • 作用： 想用一个表的字段也更新另一个表

  • 用法
    场景1： 修改学生表中某学生姓名的同时也修改年级表中此同学姓名，根据学号

    1
    UPDATE student AS s SET name = 'aa' FROM grade AS g WHERE s.s_id = g.s_ids

    场景2: 按年级表中某学生姓名修改学生表中某学生姓名，根据学号

    1
    UPDATE student AS s SET s.name = g.name FROM grade AS g WHERE s.s_id = g.s_ids

FRO UPDATE

  • 定义
    1. 一种行级锁，又叫排它锁，一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行．如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。行锁永远是独占方式锁。
    2. 释放锁条件：
      1. commit后
      2. 退出数据库
      3. 程序停止
  • 例
    1. select * from t for update 会等待行锁释放之后，返回查询结果。这种通常会等待提交后，才会解锁。
    2. select * from t for update nowait 不等待行锁释放，提示锁冲突，不返回结果
    3. select * from t for update wait 5 等待5秒，若行锁仍未释放，则提示锁冲突，不返回结果
    4. select * from t for update skip locked 查询返回查询结果，但忽略有行锁的记录
  • 用法
    场景： 获取指定状态的任务并修改状态，避免不同线程重复取相同任务 
    1
    2
    3
    4
    5
    WITH cte AS (
        SELECT * FROM task FROM status = 1 FRO UPDATE
    )
    UPDATE task AS t SET status = 2 FROM cte WHERE t.id = cte.id
    RETURNING t.*

    UPDATE SET WHERE IN SELECT