常用sql集锦(级联添加更新)

级联跟新数据（根据a表更新b表数据）

--  pgsql写法
update td_book_ranking_month_summary（用别名报错）  set book_author = b.book_author from td_book b
where td_book_ranking_month_summary.book_id = b.book_id

--  mysql写法
update back_element_info_table set ele_id = 
(select element_id from back_element_table bet where ele_id = bet.old_id)

生成随机数

select id,substring((random()::varchar),3,6)  from generate_series (1,200) as t(id)

导出带表头的数据

select  '姓名', '性别', '年龄', '住址'
UNION ALL
select  'name' as 姓名, 'sex' as 性别, 'age' as 年龄, 'address' as 住址
from myinfo

查看序列

select relname from pg_class where relkind='S'

pgsql时间操作，计算时间差

-- 时间操作
-- select date_part('day','2018-01-31 15:49:15'::timestamp - '2018-01-10 10:12:15'::timestamp)
-- select floor(extract(epoch from now()));
-- 

pgsql断开数据库连接

SELECT pg_terminate_backend(pg_stat_activity.pid)  
FROM pg_stat_activity  
WHERE datname='数据库名称' AND pid<>pg_backend_pid();

-- 查看数据库锁死数据详情（待补齐）
select * from pg_stat_activity;

select * from pg_locks order;

select * from pg_class;

-- 查看活动进程
select distinct activity.pid, activity.client_addr, activity.datname, activity.backend_xid, activity.backend_xmin,
least(activity.query_start, activity.xact_start) as start_time, activity.waiting, locks.mode,
(current_timestamp - least(activity.query_start, activity.xact_start)) as runtime, activity.query
from pg_stat_activity activity
left join pg_locks locks ON activity.pid = locks.pid
order by activity.pid asc;

-- 结束指定进程
select pg_terminate_backend(21960);

-- 结束死锁进程
SELECT pg_terminate_backend(pg_stat_activity.pid)  
FROM pg_stat_activity  
WHERE backend_xid in (select distinct backend_xmin from pg_stat_activity) AND pid<>pg_backend_pid();

-- 查看数据库大小
select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database where  pg_database.datname = 'elib-yuedudao'; 

-- 查看表大小
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;

拓展链接：https://www.cnblogs.com/winkey4986/p/6433704.html
https://www.cnblogs.com/mchina/archive/2013/04/19/3028573.html

pgsql函数

create or replace function getRecord(paramet varchar)
    returns varchar
as
$$
declare
    paramet1 varchar;
		paramet2 varchar;
		paramet3 numeric;
		paramet4 varchar;
		paramet5 varchar;
		paramet6 varchar;
begin  
	  paramet1 = substring(paramet from 1 for 2);   --    字典显示 01    总书库   02  大学城书库
		paramet2 = substring(paramet from 3 for 1);		-- 区 字典显示  1   一 2  二 3  三 4  四
		paramet3 = cast(substring(paramet from 4 for 2) as numeric);		-- 排 数字显示 01  1 
		paramet4 = substring(paramet from 6 for 1);   -- 面 默认显示 A   A
		paramet5 = cast(substring(paramet from 7 for 2) as numeric);   -- 架 数字显示 01  1
		paramet6 = cast(substring(paramet from 9 for 2) as numeric);   -- 层 数字显示 01  1
		
		if position('01' in paramet1) then paramet1 = '总书库';
		else paramet1 = '大学城书库';
		end if;	
		
		if position('1' in paramet2) then paramet2 = '一';
		end if;
		if position('2' in paramet2) then paramet2 = '二';
		end if;
	  if position('3' in paramet2) then paramet2 = '三';
		end if;
		if position('4' in paramet2) then paramet2 = '四';
		end if;
		if position('5' in paramet2) then paramet2 = '五';
		end if;
		if position('6' in paramet2) then paramet2 = '六';
		end if;
		if position('7' in paramet2) then paramet2 = '七';
		end if;
		if position('8' in paramet2) then paramet2 = '八';
		end if;
		if position('9' in paramet2) then paramet2 = '九';
		end if;	
	
return paramet1||paramet2||'区'||paramet3||'排'||paramet4||'面'||paramet5||'架'||paramet6||'层';
end;
$$ language plpgsql;
select  getRecord('01101A0102') ;

select getRecord(book_shelf_barcode) from  td_book_shelf where book_shelf_barcode ~* '[0][1-2][0-9][0-9][0-9][A-Z][0-9][0-9][0-9][0-9]'
-- 调用
update td_book_shelf set book_shelf_description = getRecord(book_shelf_barcode) where book_shelf_barcode ~* '[0][1-2][0-9][0-9][0-9][A-Z][0-9][0-9][0-9][0-9]'

级联更新

insert into td_book(book_author, book_page_number, book_price, book_publisher, book_title)  
select bookauthor, bookpage, bookprice, bookpublish, bookname from  bookitem_tab 
where bookbarcode in (select distinct bookbarcode from bookitem_tab); 

删除重复数据

SELECT max(book_shelf_id) as book_shelf_id1,min(book_shelf_id),count(*)  as book_shelf_id2 FROM "td_book_shelf" group BY  book_shelf_barcode having count(*) = 2 
order by min(book_shelf_id)

update td_library_collection  set book_shelf = a.book_shelf_id1 from (SELECT max(book_shelf_id) as book_shelf_id1,min(book_shelf_id) as book_shelf_id2 FROM "td_book_shelf" group BY  book_shelf_barcode having count(*) = 2 
) a where td_library_collection.book_shelf = a.book_shelf_id2 -- and a.book_shelf_id2 = 7

jpa添加唯一键，解决数据新增并发-非主键

@Table(name = "td_library_collection", uniqueConstraints = {@UniqueConstraint(columnNames = {"bookBarcode", "library_id"})})