postgresql 运程访问
pg_hba.conf -> host all all 192.168.0.0/24 md5
postgresql.conf -> listen_addresses = '*' # what IP address(es) to listen on;
postgresql 连线被拒,请检查主机名称和埠号,并确定 postmaster 可以接受 TCP/IP 连线
这主要是由于用户密码认证方式引起的,Postgresql数据库安装好后默认采用md5密码加密认证方式。
pg_hba.conf -> host all all 0.0.0.0/0 md5
postgresql.conf -> listen_addresses = '*' # what IP address(es) to listen on;
安装jdbc驱动 (程序 -> PostgreSQL -> Application Stack Builder -> Database Driders(全部安装))
重启服务start server
PostgreSQL 替换字符串方法及字符串操作函数
update ab set a=replace(a,'aaa','0') 把a字段里面的‘aaa’字符串替换成0
[其它相关函数](http://www.jsjtt.com/shujuku/postgresql/29.html)
单表distinct/多表group by查询去除重复记录
select count(distinct member_id) from abc_coupongain where coupon_id='Coupon_0000000000000000000000542' group by member_id
Column 'id' in field list is ambiguous
列'ID'在字段列表中重复,其实就是两张表有相同的字段,但是使用时表字段的名称前没有加表名,导致指代不明
转发排行前50名
SELECT
count(1) AS logCount,
info.headimgurl AS headimgurl,
info.mobile AS NAME,
info.openid AS openid
FROM
wcd_log log
INNER JOIN user_info info ON log.belong_id = info.openid
WHERE
wcd_id ='2314' group by belong_id order by logCount desc limit 50
修改数据库密码
alter user postgres with password 'new password';
查询某个字段中是否包含字符串
select count(*) as 使用次数, abc_log.name as 功能名称 from abc_log where abc_log.type <>'04' and position('Enterp_' in name)=0 group by abc_log.name order by 使用次数 desc LIMIT 100
mysql用 locate('Enterp_', name);
查询数据库连接数情况
select * from pg_stat_activity order by query_start desc
pg 创建函数
CREATE OR REPLACE FUNCTION createSeqId(text, text)
RETURNS text AS
$BODY$
DECLARE
t text;
i int;
j int;
n int;
BEGIN
n=nextval($2);
i=32-character_length($1)-character_length(n||'');
j=1;
t='';
for j IN 1..i loop
T = T||'0';
END loop ;
t = $1 || t||n;
RETURN t;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
postgresql 字符串转化为数字类型排序
SELECT sell FROM abc_product WHERE enterprise_id = '' ORDER BY to_number(sell, '999999999') desc
postgresql null 排序问题
SELECT sell FROM abc_product WHERE enterprise_id = ''ORDER BY (sell IS not NULL), sell asc
postgresql 去除重复数据
select * from abc_news where news_id in (select min(news_id) from abc_news where category='Category_00000000000000000344331' group by sort) order by sort desc
windows my.init文件位置
C:\ProgramData\MySQL\MySQL Server 5.5
查看并修改 max_allowed_packet 大小
show VARIABLES like '%max_allowed_packet%';
set global max_allowed_packet = 20*1024*1024*10;
show VARIABLES like '%max_allowed_packet%';
关联标签高级搜索
select * from tag_relation_ship where type='01' and tag_id in (55,64,82) group by item_id having count(item_id) > 2
数据还原
// 直接在终端输入,无需进入psql
psql -h localhost -p 5432 -U postgres -d jihui88_backup -t abc_category < /Users/wangyj/Downloads/abbcc2.dmp
计算表数据占用量
SELECT
table_schema,
TABLE_NAME,
reltuples,
pg_size_pretty (
pg_total_relation_size (
'"' || table_schema || '"."' || TABLE_NAME || '"'
)
)
FROM
pg_class,
information_schema.tables
WHERE
relname = TABLE_NAME
ORDER BY
reltuples DESC
LIMIT 20