<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
create extension if not exists dblink;
#查詢 SELECT * FROM dblink ( 'host=localhost port=5432 dbname=piedssdb_sort user=postgres password=qQq314159@26', 'select user_id,account from piedss_biz.sys_user' ) AS T ( ID TEXT, NAME TEXT); #新增 SELECT dblink_exec ( 'host=localhost port=5432 dbname=piedssdb_sort user=postgres password=qQq314159@26', 'INSERT INTO piedss_biz.sys_user(user_id,account,password,sex,super_admin_flag,status_flag,del_flag,create_user,real_name) VALUES(''1588006895019589631'',''ericfrq'',''1qazWSX'',''F'',''Y'',''1'',''N'',''dms_datahub'',''管理員'') ' ); #將庫A的資料查詢出來後直接插入存庫B INSERT INTO dms_usercenter_userinfo ( ID, true_name, username, PASSWORD, phone ) SELECT * FROM dblink ( 'host=localhost port=5432 dbname=piedssdb_sort user=postgres password=qQq314159@26', 'SELECT sys_user.user_id,sys_user.real_name,sys_user.account,sys_user.password,sys_user.tel FROM piedss_biz.sys_user' ) AS T ( ID TEXT, true_name TEXT, username TEXT, PASSWORD TEXT, phone TEXT ); #修改 SELECT dblink_exec ( 'host=localhost port=5432 dbname=piedssdb_sort user=postgres password=qQq314159@26', 'UPDATE piedss_biz.sys_user SET account=''ericfrq'',password=''1qazWSX'',sex=''F'',super_admin_flag=''Y'',status_flag=''1'',del_flag=''N'',create_user=''dms_datahub'',real_name=''管理員''WHERE user_id=''158800689501958963111''' ); #刪除 SELECT dblink_exec ( 'host=localhost port=5432 dbname=piedssdb_sort user=postgres password=qQq314159@26', 'DELETE FROM piedss_biz.sys_user WHERE user_id=''4028db8283d486350183d533f7570000'' AND create_user=''dms_datahub''' );
#起別名 select dblink_connect('bieming', 'host=localhost port=5432 dbname=piedssdb_sort user=postgres password=qQq314159@26'); #進行操作 SELECT dblink_exec ( 'bieming', 'INSERT INTO piedss_biz.sys_user(user_id,account,password,sex,super_admin_flag,status_flag,del_flag,create_user,real_name) VALUES(''1588006895019589631'',''ericfrq'',''1qazWSX'',''F'',''Y'',''1'',''N'',''dms_datahub'',''管理員'') ' ); #關閉連線 SELECT dblink_disconnect('bieming');
參考下面補充介紹:pgsql個人筆記,mybatis+postgresql寫原生sql,不用xml
下面統計的sql中用到的聚合函數具體解析說明: 第一部分
第二部分
mybatis+postgresql寫原生sql,不用xml
@Select({"${sqlStr}"}) @Results({ @Result(column = "gid", property = "gid", jdbcType = JdbcType.INTEGER, id = true), @Result(column = "name", property = "name", jdbcType = JdbcType.VARCHAR), @Result(column = "geom", property = "geom", jdbcType = JdbcType.VARCHAR), @Result(column = "code", property = "code", jdbcType = JdbcType.VARCHAR) }) List<ModelPolygon> exeNativeSql(@Param("sqlStr") String sqlStr); @Select({"${sqlStr}"}) List<ModelPolygon> exeNativeSql(@Param("sqlStr") String sqlStr); //"select gid as gid,name as name,ST_AsGeoJson(geom) as geom,code as code from wl_model_polygon"
整個dao層的寫法:
package com.xxx.mapper; import com.alibaba.fastjson.JSONObject; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.xxx.entity.UserInfo; import org.apache.ibatis.annotations.*; import org.apache.ibatis.type.JdbcType; import java.util.List; @Mapper public interface UserInfoMapper extends BaseMapper<UserInfo> { @Select({"${sqlStr}"}) @Results({ @Result(column = "id", property = "id", jdbcType = JdbcType.VARCHAR, id = true), @Result(column = "username", property = "username", jdbcType = JdbcType.VARCHAR), @Result(column = "email", property = "email", jdbcType = JdbcType.VARCHAR), @Result(column = "phone", property = "phone", jdbcType = JdbcType.VARCHAR), @Result(column = "password", property = "password", jdbcType = JdbcType.VARCHAR), @Result(column = "true_name", property = "trueName", jdbcType = JdbcType.VARCHAR), @Result(column = "usetime", property = "usetime", jdbcType = JdbcType.VARCHAR) }) List<UserInfo> exeNativeQuerySql(@Param("sqlStr") String sqlStr); @Select({"${sqlStr}"}) List<JSONObject> exeNativeExecSql(@Param("sqlStr") String sqlStr); }
<!--資料量統計 --> <select id="getDataByParams" resultType="com.htht.datatrans.app.vo.CountProtocolVO"> SELECT * FROM ( SELECT COUNT ( * ) AS useNode FROM ( SELECT DISTINCT regexp_split_to_table( ( concat_ws ( ',', array_to_string( ARRAY_AGG ( stp.source_server ), ',' ), array_to_string( ARRAY_AGG ( stp.target_server ), ',' ) ) ), ',' ) FROM sync_t_protocol AS stp WHERE stp.deleted = 0 AND stp.protocol_type = 'data communication' ) res ) node1, ( SELECT COUNT ( * ) AS runningNode FROM ( SELECT DISTINCT regexp_split_to_table( ( concat_ws ( ',', array_to_string( ARRAY_AGG ( stp.source_server ), ',' ), array_to_string( ARRAY_AGG ( stp.target_server ), ',' ) ) ), ',' ) FROM sync_t_protocol AS stp WHERE stp.deleted = 0 AND stp.protocol_type = 'data communication' AND stp.run_state = 'running' ) res ) node2, ( SELECT COUNT ( * ) AS protocolTotal FROM sync_t_protocol AS stp WHERE stp.deleted = 0 AND stp.protocol_type = 'data communication' ) protocol1, ( SELECT COUNT ( * ) AS runningProtocol FROM sync_t_protocol AS stp WHERE stp.deleted = 0 AND stp.protocol_type = 'data communication' AND stp.run_state = 'running' ) protocol2, ( SELECT COUNT ( * ) AS exceptionalProtocol FROM sync_t_protocol AS stp WHERE stp.deleted = 0 AND stp.protocol_type = 'data communication' AND stp.run_state = 'exception' ) protocol3, ( SELECT ROUND( SUM ( stad.data_volume ) :: NUMERIC / ( 1024 * 1024 * 1024 ), 3 ) AS runningData FROM sync_t_action_detail AS stad INNER JOIN sync_t_protocol AS stp ON stad.protocol_id = stp.protocol_id AND stp.deleted = 0 AND protocol_type = 'data communication' WHERE stad.execute_state = ANY ( STRING_TO_ARRAY( 'running', ',' ) ) ) data1, ( SELECT ROUND( SUM ( stad.data_volume ) :: NUMERIC / ( 1024 * 1024 * 1024 ), 3 ) AS historyData FROM sync_t_action_detail AS stad INNER JOIN sync_t_protocol AS stp ON stad.protocol_id = stp.protocol_id AND stp.deleted = 0 AND protocol_type = 'data communication' WHERE stad.execute_state = ANY ( STRING_TO_ARRAY( 'succeed,failed', ',' ) ) ) data2 </select> <select id="getPagesByParams" resultType="com.htht.datatrans.app.vo.CloudVO"> select * from ops_t_cloud where delete=0 <if test="cloudProvider != null and cloudProvider != ''"> and cloud_provider like '%'||#{cloudProvider,jdbcType=VARCHAR}||'%' </if> order by cloud_id </select> <select id="getByCloudCodes" resultType="com.htht.datatrans.app.entity.Cloud"> select * from ops_t_cloud where delete=0 <if test="cloudCodes != null and cloudCodes != ''"> and cloud_code = ANY(STRING_TO_ARRAY(#{cloudCodes,jdbcType=VARCHAR}, ',')) </if> order by cloud_id </select> <select id="getPagesByParams" resultType="org.springblade.modules.datatrans.vo.ServerPageVO"> select ots.*,otc.cloud_name as cloudName,otc.domain_name as domainName from ops_t_server ots inner join ops_t_cloud otc on ots.cloud_id = otc.cloud_id <if test="institutionId != null and institutionId != ''"> inner join ops_t_institution oti ON CAST(oti.institution_id AS VARCHAR) = ots.institution_id </if> where ots.deleted=0 <if test="cloudProvider != null and cloudProvider != ''"> and otc.cloud_provider like concat(concat('%',#{cloudProvider,jdbcType=VARCHAR}),'%') </if> order by ots.server_id </select>
將address欄位裡的 “區” 替換為 “嘔” 顯示,如下
select *,replace(address,'區','嘔') AS rep from test_tb
將name欄位裡的 “我” 替換為 “你” 儲存,如下
UPDATE blade_visual SET "name" = ( REPLACE ( NAME, '你', '你們三' ) )
比如面相交
1、使用步驟 新建空間索引create extension postgis;建立geometry型別欄位
3.插入geometry資料
insert into wl_model_polygon(geom,name,code) values ('SRID=4326;POLYGON ((116.2078857421875 39.928694653732364, 116.20925903320312 39.91078961774283, 116.20651245117188 39.89393354266699, 116.23397827148436 39.86547951378614, 116.24496459960938 39.82752244475985, 116.29852294921876 39.78954439311165, 116.3397216796875 39.78532331459258, 116.3836669921875 39.78848914776114, 116.41799926757811 39.79904087286648, 116.444091796875 39.80748108746673, 116.45919799804688 39.818029898770206, 116.48117065429686 39.83490462943255, 116.50314331054688 39.86231722624386, 116.50588989257812 39.88023492849342, 116.5045166015625 39.90973623453719, 116.4935302734375 39.925535281697286, 116.5045166015625 39.94975340768179, 116.47979736328125 39.98132938627215, 116.47567749023438 39.99395569397331, 116.45507812500001 40.000267972646796, 116.43859863281249 40.000267972646796, 116.4166259765625 39.998163944585805, 116.36581420898438 40.00868343656941, 116.35208129882812 40.00447583427404, 116.30264282226562 40.01078714046552, 116.27792358398436 39.999215966720165, 116.24771118164061 39.99500778093748, 116.23260498046874 39.990799335838034, 116.21200561523438 39.95606977009003, 116.2078857421875 39.928694653732364)) ','產流區單元','1'); insert into wl_model_polygon(geom,name) values ('SRID=4326;POLYGON ((118.76382985390228 30.94145000894207, 118.76367454479498 30.941584547525736, 118.76350796485406 30.941783659824637, 118.76339844820404 30.941924731032316, 118.76330916107543 30.942036894992782, 118.76327040751187 30.94208876002824, 118.76320401397413 30.942103072784164, 118.76311833308432 30.942151844969032, 118.76297412628924 30.94233241273298, 118.76284033474406 30.942507490217793, 118.76274061465483 30.942508998759877, 118.76272709824036 30.942414705157432, 118.76260312963427 30.941400575247428, 118.76246246134042 30.940958834692708, 118.76241983918237 30.940824987759868, 118.76235477020532 30.94068130925791, 118.76232222882629 30.940647540114867, 118.76293788696353 30.940087796711964, 118.76307156743417 30.939971500356137, 118.76327063857775 30.93979831612114, 118.7635558539929 30.939541452438277, 118.7637265129556 30.93939848398361, 118.76377770256443 30.939355600092142, 118.76441910672565 30.9388159785355, 118.76463064154075 30.938667159236218, 118.76495341070222 30.938493604345012, 118.76523672506141 30.938409477348614, 118.7654197381786 30.9383707434975, 118.76582985307277 30.938323591604444, 118.76622053407164 30.9382963001612, 118.76643330279228 30.938318107809664, 118.7664801815057 30.938337017341382, 118.76652477352764 30.938350675989682, 118.7666582796586 30.938456597505137, 118.76673673369658 30.938603248874927, 118.76677236100761 30.938782266531803, 118.76684549711081 30.939149764149192, 118.76701632885761 30.93988929949859, 118.7670376347395 30.939981532336844, 118.7664187768753 30.94010020307178, 118.76614981686157 30.940150404326346, 118.7658940991671 30.940243370814187, 118.76569247579346 30.940342755588517, 118.76556089310861 30.940412552128976, 118.76552036966268 30.940466789099446, 118.76550573912039 30.940574355758315, 118.76551217968313 30.941150469586262, 118.76551098575817 30.941290908017095, 118.76550989936004 30.941418699044846, 118.76542260756776 30.94141695016964, 118.76499121731501 30.941408306476433, 118.76391937007008 30.94138581330907, 118.76382985390228 30.94145000894207))','產流區單元');
4.pg庫清空資料和主鍵自增
TRUNCATE TABLE wl_model_polygon; TRUNCATE wl_model_polygon RESTART IDENTITY;
5.相交分析sql
select gid,name,ST_AsGeoJson(geom) as geom from wl_model_polygon t where ST_Intersects(t.geom,ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[118.78355107920095,30.938155072659868],[118.78134774048146,30.939763084116294],[118.7812957819458,30.93972376187253],[118.78286595934765,30.93838280705404],[118.7833158576293,30.93793078253492],[118.78355013577584,30.938153972966006],[118.78355107920095,30.938155072659868]]]} ')) select gid,name,ST_AsGeoJson(geom) as geom from wl_model_polygon t where ST_Intersects(t.geom,ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[118.78355107920095,30.938155072659868],[118.78134774048146,30.939763084116294],[118.7812957819458,30.93972376187253],[118.78286595934765,30.93838280705404],[118.7833158576293,30.93793078253492],[118.78355013577584,30.938153972966006],[118.78355107920095,30.938155072659868]]]}'))
1、navicat建立自增欄位
設定為serial4型別
儲存後自動加序列
2、重置自增序列號為指定數值
第一步:select pg_get_serial_sequence('ts_mapservice', 'f_remark');
檢視序列為public.ts_mapservice_f_remark_seq
第二步:更新序列值ALTER SEQUENCE public.ts_mapservice_f_remark_seq RESTART WITH 8;
或者直接初始化自增數值:TRUNCATE TABLE wl_model_polygon; TRUNCATE wl_model_polygon RESTART IDENTITY;
到此這篇關於postgresql使用dblink跨庫增刪改查的文章就介紹到這了,更多相關postgresql跨庫增刪改查內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com!
到此這篇關於postgresql使用dblink跨庫增刪改查的文章就介紹到這了,更多相關postgresql跨庫增刪改查內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com!
相關文章
<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
综合看Anker超能充系列的性价比很高,并且与不仅和iPhone12/苹果<em>Mac</em>Book很配,而且适合多设备充电需求的日常使用或差旅场景,不管是安卓还是Switch同样也能用得上它,希望这次分享能给准备购入充电器的小伙伴们有所
2021-06-01 09:31:42
除了L4WUDU与吴亦凡已经多次共事,成为了明面上的厂牌成员,吴亦凡还曾带领20XXCLUB全队参加2020年的一场音乐节,这也是20XXCLUB首次全员合照,王嗣尧Turbo、陈彦希Regi、<em>Mac</em> Ova Seas、林渝植等人全部出场。然而让
2021-06-01 09:31:34
目前应用IPFS的机构:1 谷歌<em>浏览器</em>支持IPFS分布式协议 2 万维网 (历史档案博物馆)数据库 3 火狐<em>浏览器</em>支持 IPFS分布式协议 4 EOS 等数字货币数据存储 5 美国国会图书馆,历史资料永久保存在 IPFS 6 加
2021-06-01 09:31:24
开拓者的车机是兼容苹果和<em>安卓</em>,虽然我不怎么用,但确实兼顾了我家人的很多需求:副驾的门板还配有解锁开关,有的时候老婆开车,下车的时候偶尔会忘记解锁,我在副驾驶可以自己开门:第二排设计很好,不仅配置了一个很大的
2021-06-01 09:30:48
不仅是<em>安卓</em>手机,苹果手机的降价力度也是前所未有了,iPhone12也“跳水价”了,发布价是6799元,如今已经跌至5308元,降价幅度超过1400元,最新定价确认了。iPhone12是苹果首款5G手机,同时也是全球首款5nm芯片的智能机,它
2021-06-01 09:30:45