SQL_PostgreSQL_常用增删改查触发器和函数（转载）

本文主要是博主一年工作经验的所有精华SQL，包含的内容有：查询、插入、删除、更新、触发器和函数等内容，每个SQL都仔细研究并优化了10分钟以上，非常实用，请收藏，以后你的工作上会用到。

sql常用查询的方法
– 查询某个字段的数量并排序
select od_count,count() from all_bus_od_grid_geom GROUP BY od_count order by od_count
1
2
– 根据时间字段，查询某一天某小时的客流量
select TO_CHAR(time, ‘yyyy-mm-dd’) as date,TO_CHAR(time, ‘HH24’) as hour,od_count
from r_metro_od_station_sz_geom
1
2
3
– 查询字段id中含有数字的id
select district_id from patent where (PATINDEX(’%[0-9]%’, district_id)=0)>1
1
2
– 查询符合条件的字段数与整体数量的比例，换算成百分数
select count,round((cast(stations as NUMERIC)/cast(count as NUMERIC)),4)100 as percent from table1
1
2
– 按地铁线路查询 共线公交线路名 | 公交站点数 | 共线站点数 | 共线站点比列
– 嵌套查询，利用中间表d，并从另外一个关联表查询符合条件的cname
select bus_line,count,stations,round((cast(stations as NUMERIC)/cast(count as NUMERIC)),4)100 as percent from (select count(station_id) as stations,bus_line
from metro_entrance_bus_line_100 a
where cname in (select cname from metro_stoppoint where line = ‘02’)
group by bus_line having count(station_id)>1) d,
(select line,count(station_id) from bus_station_line where line in (select distinct(bus_line)
from metro_entrance_bus_line_100 a
where cname in (select cname from metro_stoppoint where line = ‘02’)) group by line
) c where d.bus_line = c.line order by percent desc
1
2
3
4
5
6
7
8
9
10
– 通过line_id 和station_id将三个表join在一起，注意一对多的问题
select c.station_id,c.id,c.name station,a.name line,a.line_id,c.geom from bus_route a left join bus_route_station b on a.line_id = b.line_id left join bus_station c on b.station_id = c.id
1
2
– 查询每周逐时(按小时分组) dow：返回这个日期是星期几，0-周日 1-周一
SELECT extract(dow FROM cast(date as TIMESTAMP)) as week,hour,sum(population) as population FROM section_hour where TO_CHAR(date, ‘yyyy’) =‘2013’ and TO_CHAR(date, ‘mm’) =‘02’
group by week,hour ORDER BY week,hour
1
2
3
– 将两个表合并，必须保证列名和字段都相同，合并后的新表可以进行排序
SELECT * from (
(select station_name_origin,station_name_destination,od_count from od_hour_day_avg1 where station_name_origin = ‘罗湖站’
and hour = 9 and year = ‘2017’ ORDER BY od_count desc limit 50)
UNION
(select station_name_origin,station_name_destination,od_count from od_hour_day_avg1 where station_name_destination = ‘罗湖站’
and year = ‘2017’ and hour = 9 ORDER BY od_count desc limit 50)
) a ORDER BY a.od_count desc
1
2
3
4
5
6
7
8
– 查询某个表中的数据并生成中间表，按照年份和月份进行分组排序 语法格式：row_number() over(partition by 分组列 order by 排序列 desc)
select (ROW_NUMBER() over(order by year,month)+1) as id, into tmp2 from population_new_off_actual
1
2
– 对身份证和手机号进行加密与隐藏
SELECT concat_ws('’,left(phone,3),right(phone,4)) as phone,
concat_ws(’*’,left(identification_number,3),right(identification_number,4)) as number
1
2
3
– 根据出生年月计算年龄，去除‘省’这个字 string_to_array相当于split函数
SELECT
(CURRENT_DATE - birth)/365 age,REPLACE(shiarray [ 1 ], ‘省’, ‘’) city,b.
FROM
(
SELECT
id,string_to_array ( score1, ‘市’ ) AS shiarray
FROM
(
SELECT
id,score_array [ 1 ] score1
FROM
( SELECT id,string_to_array( domicile, ‘县’ ) AS score_array FROM population_building_matching_new) score
) huji
) a left join population_building_matching_new b on a.id = b.id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
– 将某个字段的值转换成字段，即窄表变宽表
– 1 大于等于半年
– 2 大于半年小于等于一年
– 3 大于一年小于等于三年
– 4 大于三年小于等于五年
– 5 大于五年
select building_code,
sum (case when residence_time =1 then total_population else 0 end) as less_half_year,
sum (case residence_time when 2 then total_population else 0 end) as half_to_one_year,
sum (case when residence_time =3 then total_population else 0 end) as one_to_three_year,
sum (case when residence_time =4 then total_population else 0 end) as three_to_five_year,
sum (case when residence_time =5 then total_population else 0 end) as over_five_year
from building_residence_time
group by building_code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
– 查询某类表的名称和大小，包括系统表
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
where table_name like ‘%user%’
ORDER BY
pg_total_relation_size(’"’ || table_schema || ‘"."’ || table_name || ‘"’) DESC
1
2
3
4
5
6
7
8
sql常用更新字段值的方法
– 更改日期字段中的年份，例如将 2017-12-12 改成 2018-12-12
UPDATE mall_taxi_od SET date =
to_date(CONCAT(‘2017’, RIGHT(to_char(date, ‘yyyy-mm-dd’), 6)),‘yyyy-mm-dd’);
1
2
3
sql常用插入数据的方法
– 使用copy命令导入数据，最快能达到1s导入100M的数据
COPY tb2(t1,t2,t3) TO ‘/mnt/postgresql/weibo.csv’ CSV HEADER delimiter ‘,’;
1
2
sql常用删除数据的方法
– 删除两个表中有重复id的记录
DELETE FROM r_metro_traffic_street_geom
WHERE
street_id IN (
SELECT
t.street_id
FROM
r_metro_traffic_street_geom t
INNER JOIN ( SELECT r_metro_traffic_street_geom.street_id FROM r_metro_traffic_street_geom GROUP BY street_id HAVING COUNT( * ) > 1 ) AS a ON a.street_id = t.street_id )
1
2
3
4
5
6
7
8
9
sql常用触发器
–此触发器作用在click_time字段上，目的是获得当前的点击时间
create or replace function upd_timestamp() returns trigger as
$$beginnew.clic{k}_{t}ime=curren{t}_{t}imestamp;returnnew;end$$
language plpgsql;
–删除这个表的触发器
DROP TRIGGER update_comment ON click_nursery ;
–创建触发器update_comment ，即在click_time字段更新之前就执行上面的触发器upd_timestamp()
create trigger update_comment before update on click_nursery for each row execute procedure upd_timestamp();
–创建触发器insert_click ，即在click_time字段插入之前就执行上面的触发器upd_timestamp()
create trigger insert_click before insert on click_nursery for each row execute procedure upd_timestamp();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
sql常用函数
–创建自增的id函数
CREATE SEQUENCE frp_model_cache_id
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
–将自增的函数作用到discuss_table的id列上
alter table discuss_table alter column id set default nextval(‘frp_model_cache_id’)
1
2
3
4
5
6
7
8
9
– 此函数的调用方法如下,需要首先生成下面的函数 gen_whole_index_sqls
select * from unnest(gen_whole_index_sqls(‘public’,‘tablename’,‘pg_default’))
– 如需批量执行查询后的语句，建议用脚本
1
2
3
– 创建对某个表的所有字段生成索引的sql语句，可自动选择合适的索引方法
create or replace function gen_whole_index_sqls(
v_nsp name,
v_tbl name,
v_tbs name
) returns text[] as KaTeX parse error: Expected 'EOF', got '#' at position 2301: …atype-json.html#̲JSON-INDEXING … language plpgsql strict;

原文链接：https://blog.youkuaiyun.com/qq_30803353/article/details/108144317?utm_medium=distribute.pc_feed.none-task-blog-personrec_tag-7.nonecase&depth_1-utm_source=distribute.pc_feed.none-task-blog-personrec_tag-7.nonecase&request_id=5f44272ecea070620e93fc42