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

sql常用查询的方法

-- 查询某个字段的数量并排序
select od_count,count(*) from all_bus_od_grid_geom GROUP BY od_count order by od_count

-- 根据时间字段，查询某一天某小时的客流量
select TO_CHAR(time, 'yyyy-mm-dd') as date,TO_CHAR(time, 'HH24') as hour,od_count 
from r_metro_od_station_sz_geom

-- 查询字段id中含有数字的id
select district_id from patent where (PATINDEX('%[0-9]%', district_id)=0)>1

-- 查询符合条件的字段数与整体数量的比例，换算成百分数
select count,round((cast(stations as NUMERIC)/cast(count as NUMERIC)),4)*100 as percent from table1

-- 按地铁线路查询 共线公交线路名 | 公交站点数 | 共线站点数 | 共线站点比列
-- 嵌套查询，利用中间表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 

-- 通过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

-- 查询每周逐时(按小时分组) 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

-- 将两个表合并，必须保证列名和字段都相同，合并后的新表可以进行排序
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

-- 查询某个表中的数据并生成中间表，按照年份和月份进行分组排序 语法格式：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

-- 对身份证和手机号进行加密与隐藏
SELECt concat_ws('****',left(phone,3),right(phone,4)) as phone,
concat_ws('****',left(identification_number,3),right(identification_number,4)) as number

-- 根据出生年月计算年龄，去除‘省’这个字 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 大于五年
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

-- 查询某类表的名称和大小，包括系统表
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

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');

sql常用插入数据的方法

-- 使用copy命令导入数据，最快能达到1s导入100M的数据
COPY tb2(t1,t2,t3) TO '/mnt/postgresql/weibo.csv' CSV HEADER delimiter ',';

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 )  

sql常用触发器

--此触发器作用在click_time字段上，目的是获得当前的点击时间
create or replace function upd_timestamp() returns trigger as
$$
begin
    new.click_time = current_timestamp;
    return new;
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();

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')

-- 此函数的调用方法如下,需要首先生成下面的函数 gen_whole_index_sqls
select * from  unnest(gen_whole_index_sqls('public','tablename','pg_default'))
-- 如需批量执行查询后的语句，建议用脚本

-- 创建对某个表的所有字段生成索引的sql语句，可自动选择合适的索引方法
create or replace function gen_whole_index_sqls(  
  v_nsp name,   
  v_tbl name,   
  v_tbs name   
) returns text[] as $$  
declare  
  v_attname name;  
  v_typid oid;  
  v_typca "char";  
  v_typname name;  
  res text[];  
  idxprefix text := to_char(clock_timestamp(),'yyyymmddhh24miss');  
  idxsuffix int := 1;  
  sql text := 'create index IF NOT EXISTS i'||idxprefix||'_%s on '||quote_ident(v_nsp)||'.'||quote_ident(v_tbl)||' using %s (%I %s) tablespace '||quote_ident(v_tbs)||' ;';  
begin   
  for v_attname,v_typid in select attname,atttypid from pg_attribute where not attisdropped and attnum >= 1 and attrelid=(quote_ident(v_nsp)||'.'||quote_ident(v_tbl))::regclass  
  loop  
  select typcategory,typname into v_typca,v_typname from pg_type where oid=v_typid;  
  case v_typca  
  when 'A' then  
    res := array_append(res, format(sql,idxsuffix,'gin',v_attname,''));  
  when 'D', 'N', 'T' then  
    res := array_append(res, format(sql,idxsuffix,'btree',v_attname,''));  
  when 'S' then  
    if v_typname='text' or v_typname='varchar' then  
      res := array_append(res, format(sql,idxsuffix,'btree',v_attname,'text_pattern_ops'));  
    elsif v_typname='bpchar' then  
      res := array_append(res, format(sql,idxsuffix,'btree',v_attname,'bpchar_pattern_ops'));  
    else  
      res := array_append(res, format(sql,idxsuffix,'btree',v_attname,''));  
    end if;  
    -- 如果字符串要支持模糊查询，使用gin索引 
    -- if v_typname='text' or v_typname='varchar' then 
    -- res := array_append(res, format(sql,idxsuffix,'gin',v_attname,'gin_trgm_ops')); 
    -- else 
    -- res := array_append(res, format(sql,idxsuffix,'btree',v_attname,'')); 
    -- end if; 
  when 'G' then  
    if v_typname not in ('line') then  
      res := array_append(res, format(sql,idxsuffix,'gist',v_attname,''));  
    else  
      continue;  
    end if;  
  when 'I', 'R' then  
    res := array_append(res, format(sql,idxsuffix,'gist',v_attname,''));  
    -- 可选spgist 
    -- res := array_append(res, format(sql,idxsuffix,'spgist',v_attname,'')); 
  when 'U' then  
    case v_typname   
    when 'geography', 'geometry' then  
      res := array_append(res, format(sql,idxsuffix,'gist',v_attname,''));  
    when 'jsonb' then  
      res := array_append(res, format(sql,idxsuffix,'gin',v_attname,'jsonb_path_ops'));  
      -- 可选默认gin ops 
      -- https://www.postgresql.org/docs/11/static/datatype-json.html#JSON-INDEXING 
      -- res := array_append(res, format(sql,idxsuffix,'gin',v_attname,'')); 
    when 'tsvector' then  
      res := array_append(res, format(sql,idxsuffix,'gin',v_attname,''));  
    when 'uuid', 'xid' then  
      res := array_append(res, format(sql,idxsuffix,'hash',v_attname,''));  
    else  
      continue;  
    end case;  
  else  
    continue;  
  end case;  
  idxsuffix := idxsuffix+1;  
end loop;  
return res;  
end;  
$$ language plpgsql strict;