gp数据库日常运维sql语句笔记
(更新中)
后台登录psql管理操作:
psql -d database -h ip -p 5432 -U username
psql -d database -h ip -p 5432 -U username -c "sql" >output_data.csv --当要远程导出大文件时
后台登录oracle管理操作:
sqlplus sys/password@ip:1521/database [as username]
gp查询表结构:
select * from information_schema.columns
where table_name = 'my_table' order by ordinal_position;
后台登陆查询:\d schemas.table_name;
gp查找活跃用户:
select * from pg_stat_activty;
注意:一般用此语句查找用户procpid和sess_id强制用户退出。
select procpid,sess_id from pg_stat_activity;
--procpid='364610' ,sess_id='510024'
杀掉进程,退出用户:
select * from pg_cancel_backend(procpi --中止procpid
select pg_terminate_backend(procpid); --强制终止procpid
注意:gp中可以直接select 字段和select 函数,
相当于oracle中的select * from dual
查询表锁并解除:
select oid from pg_class where relname = 'table_name';
--查询表名的oid(相当于oracle中的rownum)
select pid from pg_locks where relation = '1024' (上面查询到的oid)
select pg_terminate_backend(666); (上面查询到的pid)
gp类型转换:
用::表示类型转换,
如:to_timestamp(transtime,'yyyymmdd hh24:mi:ss')::timestamp
gp时间戳转日期:
select TO_TIMESTAMP(round(1564993508965/1000::decimal));
gp时间相加减:
select date(date'20190905' - interval'1 month');
select timestamp'20191031 10:20:21' - interval'1 month';
gp扩充字段长度:
alter table my_table alter column my_col type character varing(255);
gp添加字段:
alter table my_table add column my_col varchar(255);
gp删除字段:
alter table my_table drop column my_col;
gp修改字段类型:
alter table my_table alter column my_column type varchar(255);
gp修改字段名称:
alter table my_table rename my_column to one_column;
update语句:
update odw.my_table tb
set old_column = case when 1>0 then t2.tnd_code else old_column end,old_column2 = new_column
from tdw.my_stnd t2
where tb.id = t2.id
and tb.data_date < date '20200728';
update odw.my_table tb
set old_column = case when 1>0 then t2.tnd_code when 2>1 then t3. mapp_code else old_column end,old_column2 = new_column
from tdw.my_stnd t2,tdw.my_mapping t3
where tb.id = t2.id
and tb.data_id = t3.data_id;
and tb.data_date < date '20200728';
(notest)