PostgreSQL数据库使用

安装

1. 下载安装
2. 配置环境变量，如：path中添加D:\software\PostgreSQL\bin

使用

登陆 psql -h localhost -U postgres -p 5432

\?: 查看所有帮助
\l: 查看所有数据库
\c dataname: 切换数据库
\dt: 查看所有自己创建的表
\dt+: 查看所有自己创建的表，显示表的相关内容占的磁盘大小
\dt(+) tablename: 参看指定表
\dv: 查看所有自己创建的视图
\dv+: 查看所有自己创建的视图，显示大小
\df: 查看所有自己创建的function
\df+: 查看所有自己创建的function,显示function的内容
\df(+) func_name: 显示指定的function
\ef func_name: 编辑function
\dy：查看触发器
\dx： 查看添加的PostgreSQL扩展模块
\du： 查看所有角色
\dp viewortable： 查看表或视图的权限
\sf+ 函数名: 查看函数的创建语句

创建数据库

create database testDB;

选择数据库

\c testDB;

创建表

CREATE TABLE yc_test
(
  seq_id bigint NOT NULL,
  name character varying(100),
  version character varying(50),
  create_time timestamp without time zone,

  CONSTRAINT yc_test_pkey PRIMARY KEY (seq_id)
);

创建序列

DROP SEQUENCE IF EXISTS yc_test_seq CASCADE;
CREATE SEQUENCE yc_test_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

创建存储过程，插入数据：

create or replace function insertData() returns 
boolean AS
$BODY$
declare index integer;
  begin
    index:=1;
    FOR index IN 1..20000 LOOP
    INSERT INTO yc_test(seq_id, name, version, create_time) VALUES (nextval('yc_test_seq'), 'ycName1','version_1', CURRENT_TIMESTAMP);
    INSERT INTO yc_test(seq_id, name, version, create_time) VALUES (nextval('yc_test_seq'), 'ycName2','version_2', 
    end loop;
    return true;
  end;
$BODY$
LANGUAGE plpgsql;

执行存储过程

select insertData();

删除存储过程

drop function insertData();

---

PostgrepSQL常用命令

1. 创建视图

create view view_name as select column_name from table_name where condition;

2. 修改表/视图 owner

alter table table_name owner to user_name;

3. 数据修改

• insert

insert into table_name(cloumn_name1, cloumn_name2) values(value_1, value_2);
主键为序列ID（自增）
insert into table_name(seq_id, cloumn_name) values (nextval(‘table_name_seq’), value_1);

• update

update table_name set column_name=‘xx’ where condition;

1. 字符串拼接

select concat(column_name1, column_name2) from table_name;

5. 分组

select cloumn_name1, aggregate_function(cloumn_name) from table_name group by cloumn_name1;

6. 去重

select distinct column_name from table_name;

7. 添加字段

alter table table_name add column_name varchar2(20) not null;

8. 删除字段

alter table table_name delete column column_name;

9. 修改表中的一个字段名

alter table table_name rename column oldName to newName;

10. 事物相关

• 查看默认事物隔离级别

show default_transaction_isolation;

• 修改默认事物隔离级别

begin;
set default_transaction_isolation=‘serializable’;

• 查看当前事物隔离级别

show transaction_isolation;

• 修改当前事物隔离级别 (需在事物中执行)

begin;
set transaction_isolation=‘read committed’;

1. 索引相关

• 查询索引
  select * from pg_indexes where tablename=‘table_name’;
• 创建索引
  CREATE INDEX index_name ON table_name (column_name1, column_name2);
• 删除索引
  drop index index_name;
• 全表顺序扫描失效
  set enable_seqscan=false;
• 索引扫描失效
  set enable_indexscan=false;

12. SQL执行分析

• 查看执行时间
  开启：\timing on 关闭： \timing off
• explain
  explain analyze select * from table_name;
  explame: explain(analyze, verbose, buffers, timing, format text) select * from yc_test;