pgsql入门&工作中常用语法

一、常用SQL

#查看所有数据库
\dn


#版本
show server_version;


# 查询主键id个数
select nextval('pay.t_test_id_seq'::regclass);


#使用数据库
set search_path to open;


# a表所有字段关联b表所有字段 
on CONCAT(a.*) = CONCAT(b.*)


#查看所有表
 \dt


#查看某个表的结构
\d 表名;


#给用户组授权schema
grant all on SCHEMA  pay_data to 账户名;


#给用户组某个表的权限
grant select,insert,update,delete on 表名 to 账户名;


#查询当前账号的连接数
select count(1) from pg_stat_activity where state='idle';


#查询当前账号的连接数以及ip
select usename,client_addr,count(1) from pg_stat_activity where state='idle' group by 1,2;


#字段唯一
sp_id character varying(128) NOT NULL UNIQUE


#两个字段组合唯一
alter table 表名 add constraint uk_mac_vendor unique (order_id,uc_id);


#新增
insert into pay_data.t_ee values ('1003703830'),('1003703831');


#复制带索引/注释的表
CREATE TABLE  表名S (like 表名 INCLUDING INDEXES INCLUDING DEFAULTS including constraints including comments) DISTRIBUTED by (f_wallet_id);

#给表字段加索引
CREATE INDEX id_idx ON 表名 USING btree (f_trans_id);


#修改表的字段由NOT NULL修改为NULL：
alter table 表名 alter modify_reason drop not null;


#将表的sp_id字段修改为VARCHAR
alter table 表名 alter column sp_id  type VARCHAR(128) using sp_id::VARCHAR;


#清空表数据
truncate table 表名;


#修改表字段的名称
ALTER TABLE 表名 RENAME settle_type to fee_settle_type;


#清除自增id
alter sequence t_sp_income_confirms_id_seq  start 1;


#删除字段
ALTER TABLE 表名  DROP COLUMN target_confirm_time;


#新增字段
ALTER TABLE 表名  ADD COLUMN target_confirm_time  character varying(128);

--为表my_table 添加gid字段,设置默认值为1
ALTER TABLE my_table ADD COLUMN gid INTEGER  DEFAULT 1;
 
--修改my_table 的gid 字段默认值为2
ALTER TABLE my_table ALTER COLUMN gid SET DEFAULT 2;
 
--删除my_table的gid字段的默认值
ALTER TABLE my_table ALTER COLUMN gid DROP DEFAULT;


#删除表
DROP TABLE  表名;


#修改表的名称
alter table 表名 rename to 表名1;


#更新字段（关联另一张表）
update 表名 b set dt=(select dt from 表名2 a where a.uid=b.uid);


#联表更新
UPDATE t_info w
SET f_bank_id = r.f_bank_user_id_page::bigint
FROM t_receive r
WHERE w.f_recvable_id = r.f_recvable_id and w.f_recvable_id in(select distinct f_recvable_id from t_info where F_time>='2024-01-01');


#修改表的分布键
ALTER TABLE 表名 set distributed by(f_batch_id);


#清空缓存
analyze 表名;


#查看账号下是否有锁表的情况
select * from pg_stat_activity where usename='pay_cloud_data_rd';


#创建外表，外表文件的路径：/home/work/pay-datax-admin/ext
CREATE EXTERNAL TABLE uc.r_ext_t_full_user_auth (LIKE uc.t_full_user_auth) LOCATION ('gpfdist://host:port/r_ext_t_full_user_auth.dat') FORMAT 'TEXT' (DELIMITER E'|' NULL '\N' escape 'off');


#删除外表
DROP external  TABLE r_ext_t_full_user_auth_pre;


#将外表信息导入主表
insert into uc.t_full_user_auth select * from uc.r_ext_t_full_user_auth;


#判断某个日期或时间戳字段是周几
select extract(dow from '2021-08-05 00:00:00'::timestamp);


#538不满6位数时，左变补0，让其达到6位长度
select lpad(‘538’, 6, ‘0’) 


#538不满6位数时，右边补0，让其达到6位长度
select rpad(‘538’, 6, ‘0’)


# 多个字符串拼接
concat('abcde', 2, NULL, 22)

使用except查找两张表的差异:数据出现第一个结果集中但不在第二个结果集中的
即：查询出现foo表中，但不出现在bar表中
select id ,name,'not in bar' as note from foo
except
select id,name,'not in bar' as note from bar;

#字段内容最大的长度
select max(length(certificate_code)) from aml.user_info_check ;


#查看指定字符在一个字符串中出现次数
select array_length(regexp_split_to_array('ffff;65432',';'),1)-1;


#截取指定字符左边的所有字符
select split_part('ffff;6543;ujhnbvc;98765432;hgfdsajhgf',split_part('ffff;6543;ujhnbvc;98765432;hgfdsajhgf', ';', 3),1) || split_part('ffff;6543;ujhnbvc;98765432;hgfdsajhgf', ';', 3);


#截取字符串前几位
select substr('9000012711999001000544',0,5);


#截取字符串后几位
select substring('9000012711999001000544',char_length('9000012711999001000544')-4,4);

#截取字符串的最后一位
substring(industry,length(industry))

#删除字符串最后一位
RTRIM(industry,'/') 


#连续登陆3天用户
select t1.user_name from user_login_table as t1 join user_login_table as t2 on t1.user_name = t2.user_name where t2.logindate between t1.logindate::TIMESTAMP + INTERVAL '-2 day' and t1.logindate group by t1.user_name, t1.logindate having count(1) >= 3;


#将自己表max的值，更新到自己表的字段上

第一种方案：将code、max 写入临时表；join；写入新表
insert into pay_data.zxy_no4_source select s.uid,s.level,s.year,s.code,c.max_level from pay_data.c_user_level s join (select * from pay_data.zxy_no4_code_max_level) c on s.code=c.code and s.year=c.year;
优化成：
第二种方案：利用窗口函数
insert into pay_data.zxy_no4_source select s.uid,s.level,s.year,s.code,(max(level) over (partition by year,code)) from pay_data.c_user_level s;


#判断字符串是否为数字
select * from pay_data.zxy_no4_source  where code ~'^([0-9]+[.]?[0-9]*|[.][0-9]+)$';

#判断是否存在中文字
select 'hello中国' ~ '[\u2e80-\ua4cf]|[\uf900-\ufaff]|[\ufe30-\ufe4f]';

#json字符串取值
{"firstLevel":"A0009","secondLevel":"B0149"}
select f_industry_category::json ->> 'firstLevel' from a;

#字段内容：以 分号分隔，分组 民生银行;建设银行;百信银行
SELECT type,regexp_split_to_table(bind_cards,';'),count(distinct did) from pay_data.t_zxy_test_res  group by 1,2;

#在本表中查询，两个字段同时不存在，需要根据配置表排除
#a1中的通道号+银行号 同时不是配置表中的数据
select
     *
    from
      表名 a1
    where
      NOT EXISTS (
        SELECT
          1
        FROM
           (
            select
              *
            from
              表名2
            where
              status = 1
          ) b
        WHERE
          b.channel_code = a1.f_channel_code
          and b.front_code = a1.f_front_code
      )



# 将数据库内容导出到文件

psql -A -E -F "|" -t -c  "select * from pay_data.t_temp_test" "host=$host port=$port dbname=$dbname user=$user password=$password" >> /home/work/test_111111.txt

#将文件里面的数据导入到数据库

psql  -A -E  -t -c  "\copy ddd.t_temp_test FROM '/home/work/test.txt' with DELIMITER as '|' NULL as ''" "host=$host port=$port dbname=$dbname user=$user password=$password"


#新增字段约束 CHECK
CREATE TABLE pay_data.t_study_1  (
    id INT PRIMARY KEY NOT NULL,
    name  character varying(128),
    salary  REAL CHECK(salary > 0)
);

# 删除字段的约束 CHECK
ALTER TABLE  表名 DROP CONSTRAINT 约束键名;


#判断数据的时间 是否是连续的
SELECT
  f_user_id,
  f_log_time,
  CASE
    WHEN ABS(
      f_log_time :: date - LAG(f_log_time :: date) OVER (
        ORDER BY
          f_log_time
      ) :: date
    ) = 0 THEN '连续'
    ELSE '不连续'
  END AS sequence_type
FROM
  core.log
where
  f_log_time >= '2024-04-01'
  and f_user_id = '2222'
order by
  2;

#（判断两个时间段的交集）pgsql 有10条数据,每条数据都有两个时间段,传入两个时间,判断这个时间覆盖了几条数据
select *
from orders
where (start_time, end_time) overlaps ('2023-04-01', '2023-07-01');

二、学习