Postgres数据库笔记

安装教程

Postgres 12安装版本

1、双击下载安装包,开始安装

2、选择你的安装路径,我用的是默认的。

3、选择安装组件,不懂的选就是全部勾上。

4、设置数据库路径,一般会根据刚才选择的postgres安装路径而设置,一般不用改。

5、设置超级用户的密码,我本地账号密码都设置的是postgres

6、设置端口号,本地可以直接用默认就行。

7、直接点 Next

8、点 Next

9、开始安装,等待安装完成。

10、去掉勾选,直接点 Finish,

11、安装完成。

安装postgis-bundle-pg12x64-setup-3.0.0-4插件包

1、点击“I Agree”

2、选择组件,勾选Create spatial database,然后点击Next,如下图所示:
PostgreSQL安装PostGIS插件包


3、PostGIS插件的安装位置必须要和PostgreSQL在同一个目录,保持默认即可,然后点击Next

4、设置数据库密码

5、设置数据库

6、安装过程中提示写入环境变量值,全部点击“是”即可,如下图所示

7、直至安装完成,点击close即可,如下图所示

备份恢复

使用pgAdmin备份恢复数据库

备份数据库

1、右击选中数据库,选择”Backup”

2、选中三个点

3、输入备份文件名,点击create

4、编码选择utf8

5、权限选择postgres,完成后,点击“Backup”

6、出现这个状态就说明备份成功!备份单表也是一样,只是右击选择的不一样!

恢复数据库

1、创建新的数据库,右击Database

2、在弹窗的框上,选择三个点

3、选择你要备份的数据库backup文件

4、权限选择为”postgres”,选择后,点击“Restore”

5、开始恢复,出现下面情况就恢复成功了。恢复单表也是一样,只是右击选择的不一样!

使用命令行备份恢复数据库

恢复数据库

pg_dump -h 164.82.233.54 -U postgres databasename > C:\databasename.bak

根据父级id,查询子级

with recursive cte as(select a.atid,cast(a.name as varchar(200)) from app_aqxj_equipment a where a.atid='5cbd1b00-ca0f-461c-9f13-2215bdaf1d9c'union all select k.atid,cast(c.name||'>'||k.name as varchar(200)) as name  from app_aqxj_equipment k inner join cte c on c.atid = k.parent_id--可以根据条件筛选where  k.type_id <> 'xunjianxiang')select atid,name from cte ;create or replace function func_get_loginUnit(in_userid varchar(200),out o_area text)
returns text as $$
DECLARE
 v_rec_record RECORD;
BEGIN
 o_area = '';
 FOR v_rec_record IN (WITH RECURSIVE r AS (
 SELECT userid,usertitle,userparentid,userwbscode FROM ta_users WHERE userid = in_userid
 union ALL
 SELECT ta_users.userid,ta_users.usertitle,ta_users.userparentid,ta_users.userwbscode FROM ta_users, r 
	WHERE ta_users.userid = r.userparentid and ta_users.userparentid <> '-1'
 )
SELECT userid,concat_ws(' > ',usertitle,'') as name,userparentid,userwbscode FROM r order by userwbscode asc) LOOP
 o_area := o_area || v_rec_record.name;
 
 END LOOP;
 return;
END;
$$
language plpgsql;

根据父级查询子级

with recursive cte as
(
select a.atid,cast(a.name as varchar(200)) from app_aqxj_equipment a 
where a.atid='036bc777-c718-4a81-bbc7-b8fe6f8ce913'
union all 
select k.atid,cast(c.name||'>'||k.name as varchar(200)) as name  from app_aqxj_equipment k 
inner join cte c on c.atid = k.parent_id
where  k.type_id <> 'xunjianxiang'
)select atid,name from cte

根据子级查询父级

WITH RECURSIVE dict AS (
     SELECT *
     FROM app_aqxj_equipment
     WHERE atid= '036bc777-c718-4a81-bbc7-b8fe6f8ce913'
     union ALL
     SELECT app_aqxj_equipment.*
     FROM app_aqxj_equipment,
          dict
     WHERE app_aqxj_equipment.atid = dict.parent_id
)
SELECT atid AS id, name as name, parent_id as parentId
FROM dict
ORDER BY name

从父级到子级

WITH RECURSIVE dict AS (
     SELECT *
     FROM app_aqxj_equipment
     WHERE atid= '036bc777-c718-4a81-bbc7-b8fe6f8ce913'
     union ALL
     SELECT app_aqxj_equipment.*
     FROM app_aqxj_equipment,
          dict
     WHERE app_aqxj_equipment.parent_id = dict.atid
)
SELECT atid AS id, name as name, parent_id as parentId
FROM dict
ORDER BY name

方法函数

SUBSTRING('abcd',2); -- result:bcd 表示从下标从2开始截取到末尾
SUBSTRING('abcd',1,2); -- result:ab 表示从下标从1开始,截取2个字符
to_char(date_trunc('day',now()),'yyyy-mm-dd') --时间格式转换为字符日期类型
to_date('2020-09-09','yyyy-mm-dd')) --把字符转换为日期格式 没有时分秒
to_timestamp('2020-09-09 22:22:00','yyyy-MM-dd hh24:mi:ss') --有时分秒
to_number(12,'99') --把字符转为数值型
split_part('2020-09-08','-',2) --分割字符,获取的是月,也就是09
concat_ws(':','1','2') --查询字段拼接 结果:1:2
string_agg(name,','); --多行合成一列,例如:张三,李四,王五,赵六

regexp_split_to_array('1,2,3',',') --返回数组格式
regexp_split_to_table('1,2,3',',') --拆分返回行模式

select case when 2>1 then '大于' else '小于' end --if判断

时间函数

date_trunc函数

date_trunc('day',now()) --获取的是当前天的
date_trunc('week',now()) --获取的是本周的初始日期,也就是本周星期一的日期
date_trunc('week',date_trunc('week',now())+ interval '6 days' )+ interval '6 days' --获得本周最后一天,也就是周日的日期
date_trunc('month',now()) --获取的是当月的初始日期,也就是1号
date_trunc('month',now() + interval '1 months') - interval '1 days' --获得本月最后一天
date_trunc('year',now()) --获取的是当年的初始日期,也就是本年的1月1号
date_trunc('year',now() + interval '12 months') - interval '1 days' --获得本年的最后1天

select date_trunc('month',now()) +interval '12 h';		
//每月1号 12点		
select date_trunc('month',now()) + interval '15 d 9 h 30 min';		
//每月15号9点半		
select date_trunc('day',now()) + interval '9 h';		
//每天9点		
select date_trunc('day',now()) + interval '7 d';		
//每周的今天		
select date_trunc('weak',now())  + interval '1d 1minute';		
//每周的周二第一分钟		
select date_trunc('h',now()) + interval '30 minute';		
//每小时		
select date_trunc('minute',now()) + interval '30 second';		
//每分钟		
select date_trunc('minute',now()) + interval '30 minute 30 second';		
//每30分钟 			
select date_trunc('quarter',now()) + interval '15 d 15 h 15 minute 30 second';		
//本季度的第15天,15小时 15分 30秒		
select date_trunc('quarter',now() ) - interval '1 h';		
//每个季度最后一天的晚上11点		
select date_trunc('quarter',now() + interval '3 month') - interval '1 h';		
//每个季度的最后一天的晚上的11点(从下个季度开始算起).	

timestamp函数

SELECT now()::timestamp + '1 year';
SELECT now()::timestamp + '1 month';
SELECT now()::timestamp + '1 day';
SELECT now()::timestamp + '1 hour';
SELECT now()::timestamp + '1 min';
SELECT now()::timestamp + '1 sec';
select now()::timestamp + '1 year 1 month 1 day 1 hour 1 min 1 sec';
SELECT now()::timestamp + (col || ' day')::interval FROM table

创建function函数

函数命名规则:fun_xxx()
#示例1
create or replace function test1(name varchar)
returns varchar as $$
begin
 name := name + '-李四';
 --方法参数里面没有out修饰符,得用return返回参数
 return name; #return var:=var+1 这种格式不支持
end; $$
language plpgsql;
select test1()
结果:李四

#示例2
create or replace function test2(out name varchar)
returns varchar as $$
begin
 name := name + '-李四';
 --方法参数里面有out修饰符,不用return返回参数
 --return name; #return var:=var+1 这种格式不支持
end; $$
language plpgsql;
select test1()
结果:李四

#示例3
create or replace function test3(out name varchar,in age integer)
returns varchar as $$
begin
 name := name + '-李四' + age;
 --方法参数里面有out修饰符,不用return返回参数
 --return name; #return var:=var+1 这种格式不支持
end; $$
language plpgsql;
select test1(18)
结果:李四18

--函数里编写if
create or replace function getSXYear(out test varchar(100))
returns varchar as $$
begin
	if 1=1 then
		test := '真';
	else
		test := '假';
	end if;
end; $$
language plpgsql;
select getSXYear();

创建游标

创建游标方式一

create or replace function function_test()  
returns refcursor as --返回一个游标
$body$
declare --定义变量及游标	
	bound_cursor refcursor; --游标	
	v_atid varchar(200);
begin	
	open bound_cursor for execute 'select atid from app_aqxj_task_plan'; --打开游标 并注入要搜索的字段的记录	
	loop  --开始循环		
		fetch bound_cursor into v_step_desc;  --将游标指定的值赋值给变量多选一,隔开		
		if found then			
			raise notice '%',v_step_desc;		
		else
			exit;		
		end if;	
	end loop;  --结束循环	
	close bound_cursor;  --关闭游标	
	//raise notice 'the end of msg...'; --打印消息	
	return bound_cursor; --返回游标exception when others then --抛出异常	
	raise exception 'error--(%)',sqlerrm;  --字符“%”是后面要显示的数据的占位符
end;
$body$
language plpgsql;

创建游标方式二

create or replace function function_test2()
  returns refcursor as --返回类型为游标
$$
declare --变量声明区
  bound_cursor cursor for select atid from app_aqxj_task_plan;
  v_atid varchar(200);
begin
  open bound_cursor;
  loop  --开始循环
        fetch bound_cursor into v_atid;  --将游标指定的值赋值给变量多个以,分开
        if found then --任意判断
			raise notice '%',v_atid;
		else
			exit;
		end if;
    end loop;  --结束循环
    close bound_cursor;  --关闭游标
  return bound_cursor;
end;
$$
language plpgsql;

实用代码

实现夸库查询

--创建扩展
create extension
dblink;--dblink函数实现夸库查询

select * from dblink(
	'host=127.0.0.1 dbname=数据库名字 user=postgres password=postgres',
	'select atid,name from app_aqxj_place_info') as t
	(atid varchar(200),name varchar(200));

可以看出数据库每个表有多大,并且排序

--查出所有表(包含索引)并排序
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 20

SQL性能优化

查询优化

创建索引

CREATE INDEX w008_execrise_info_gradetyp_index ON w008_execrise_info (gradetyp);

有很多情况下索引是不起作用的,比如 like后面跟的字段,还有条件语句or关联的字段,这种情况就是要考虑查询策略了。

查看当前表的索引内容:

select * from pg_indexes where tablename='w008_execrise_info';    

删除索引

drop index indexName;  

重置索引:
对于一些经常改动的表,如果时间长了发现查询效率变慢,可以考虑重置一下索引;
因为如果表的内容被频繁的修改的话会产生许多类似’索引碎片’的东西,会导致查询索引本身的时间变长;

reinde index index_name; //重置单个索引
reinde table table_name; //重置整个表的索引
reinde database db_name; //终止整个数据库的所以你

评论

  1. Tongyao
    2月前
    2021-5-06 12:06:41

    with recursive cte as
    (
    select a.atid,cast(a.name as varchar(200)) from app_aqxj_equipment a
    where a.atid=’239972297dd742e28043bfdfa9eef264′
    union all
    select k.atid,cast(c.name||’>’||k.name as varchar(200)) as name from app_aqxj_equipment k
    inner join cte c on c.atid = k.parent_id
    where k.type_id <> ‘xunjianxiang’
    )select atid,name from cte
    子级调用父级,这样也可以做

    • Tongyao
      7天前
      2021-6-18 15:39:44

      这是 父级查询所有子级的!!!

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
下一篇