- 浏览: 69143 次
- 性别:
- 来自: 深圳
文章分类
- 全部博客 (49)
- jquery (0)
- postgresql (14)
- linux (6)
- svn (4)
- php (5)
- css+div (1)
- web optimization (0)
- java (1)
- amchart (0)
- shell (0)
- perl (0)
- memcache (0)
- fastcgi (1)
- yslow (0)
- project (2)
- 浏览器兼容 (1)
- OpenWrt (0)
- mysql (1)
- http协议 (0)
- php 命令行 (1)
- XSS攻击/AJAX跨域攻击 (1)
- centos (1)
- iphone (1)
- php-ice (1)
- vim sed awk grep (1)
- xhprof (3)
最新评论
-
szcs10138456:
<script>alert(1);</scr ...
php性能监测模块XHProf -
walle1027:
总结的非常全面,感谢博主。
postgresql sql 总结 -
输入法:
其他两种方式呢?
php 3种安装方法
[size=x-small]
//更新过期的 update capture set flag=1 where (extract (epoch from now())::bigint-extract (epoch from capture_time )::bigint)>time_val --系统表 pg_stat_activity 数据库活动进程视图 Pg_database 系统数据库字典 Pg_stat_database系统统计数据库字典视图 Pg_stat_sys_tables 系统字典表 Pg_stat_sys_indexes系统字典表索引 Pg_stat_user_tables用户表 Pg_stat_user_indexes 用户表索引 pg_stat_all_tables 数据库所有的表(包括系统表和用户表) pg_stat_all_indexs数据库所有的表的索引(包括系 统表和用户表的索引) select datname,procpid,query_start,current_query,waiting,client_addr ,usename from pg_stat_activity ; --查询更新语句 select datname,procpid,query_start,current_query,waiting,client_addr ,usename from pg_stat_activity where and current_query like '%update%'; ps aux|grep postgres|grep 14190 --取消查询语句 select pg_cancel_backend(14190); --kill 各种sql pg_terminate_backend(pid int), psql -h .. -p .. -d db_name -U username -c "copy public.test_1 to stdout" > test_1.out -- 除0 NULLIF 等价=case when select count(nullif(answer_time_of_date,'0')) as succ_calls from client_cdr select count(case when answer_time_of_date ='0' then null else answer_time_of_date end) as succ_calls from client_cdr select count(case when release_cause_from_protocol_stack ~* '^486*' then release_cause_from_protocol_stack else null end) as busy_calls from client_cdr exchange=# select 3/NULLIF(0,0); SELECT COALESCE(SUM(amount), 0) / NULLIF(SUM(current_balance), 0) as average_price FROM client_payment --防止除0 select 3/0 select 3/NULLIF(0,0) SELECT COALESCE(SUM(cost), 0) / NULLIF(SUM(amount), 0) as average_price FROM sales SELECT *, first_name||' '||last_name AS full_name FROM test_table; --取出几个列中最大的值 select LEAST(current_balance,amount) from client_payment where client_payment_id=1 --取出几个列中最大的值 select greatest(current_balance,amount) from client_payment where client_payment_id=1 --取出几个列中不为空的值 select COALESCE(current_balance,amount) from client_payment where client_payment_id=1 --取出几个列中空的值 select NULLIF(current_balance,amount) from client_payment where client_payment_id=1 在PostgreSQL中实现行号 2010-05-28 09:30 建立一个序列,每次查询果将其当前值设置为0,然后用nextval()函数取值就可以 create sequence test_row; select setval('test_row',1); select nextval('test_row')-1 as rowno ,table.* from table; :%s/,\n/\r/g --去掉最后一个, :%s/^1//g--去掉第一个1 --PostgreSQL中如何删除重复记录 --ostgreSQL中删除重复记录其实很简单,不论有多少行重复,只要在要删除重复记录的表中table加一列rownum字段(id为table表中的主键),类型设置为serial类型即可,然后执行sql --删除原理 添加一个自增列, 按照 重复的 字段分组,取出rownum最大的一列,这一列就是去掉重复后的列 insert into version values(3,'22','333') --添加自增字段 ALTER TABLE "version" ADD COLUMN rownum serial; --去掉重复后的结果 select * from version where rownum in ( select max(rownum) from version group by table_name ) --删除重复 delete from version where rownum not in ( select max(rownum) from version group by table_name ) --知道有什么用户在访问,访 问什么库以及访问的进程 select datid,datname,procpid,usename, xact_start from pg_stat_activity; --kill 查询 ill Select 查询,而updae,delete DML不生效 select pg_cancel_backend(线程id); --查看有哪些SQL正在执行 select datname,procpid,query_start,current_query,waiting,client_addr from pg_stat_activity where waiting='t'; --最近15分钟的acd ,asr ,pdd,ca --15 min select sum(call_count_asr::integer*asr::real)/sum(call_count_asr::integer) as asr, (sum(acd::real)/sum(call_count::integer))/60 as acd, (sum(pdd::real) / sum(call_count::integer)) as pdd, sum(ca::integer) as ca into r from host_info where ip=host_ip and time::bigint between extract(epoch from now())::bigint-(60*15) and extract(epoch from now())::bigint; select extract (epoch from now())::bigint //当前时间对应的时间戳 select now()//现在时间 update host_info set time=(select extract(epoch from '2010-12-21 01:52:10.849097+00'::timestamp with time zone )::bigint) --累加sql select invoice_id, client_id , (select sum(total_amount) as past_due from invoice as inner_invoice where client_id = invoice.client_id and inner_invoice.invoice_end <= invoice.invoice_end) as total from invoice; update host_info set time=(select extract(epoch from '2010-12-20 11:22:10.849097+00'::timestamp with time zone )::bigint) ----------备份cdr class4_pr=# copy (select * from client_cdr limit 1) to '/tmp/exports/cdr.csv' csv HEADER; --导入cdr class4_pr=# copy client_cdr from '/tmp/exports/cdr.csv' csv HEADER ; ---------------------------------------------------------------重建触发器 psql carry_sst < /home/test/record.sql SELECT * FROM prefixes WHERE prefix @> '0123456789' ORDER BY length(prefix) DESC LIMIT 1; EXPLAIN ANALYZE touch /var/lock/subsys/local svnserve -d -r /shanmin/svn ---------------------------------------------------------------------- COPY----------- 用法- ----------------------------------------------------------------------- COPY error_message_info FROM '/tmp/message.csv' CSV HEADER ;//导入 COPY (select * from error_message_info limit 2) TO '/tmp/message1.csv' CSV HEADER ;//导出 ---case when 用法 case when call_duration='' then '0' ELSE call_duration end , select call_count, case when call_count='0' then '1' ELSE call_count end from host_info where call_count='0' ---v---------------v------------------各种时间转换---v------------------v--------------- SELECT to_timestamp(start_time_of_date::bigint) as time from cdr --时间处理 select extract(epoch from (logout_time -login_time )) from acd_login_log SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); 实现按月按年,按日统计 把数据库里的数据按每一天统计当天的点击量,当然是用 group by,可惜问题是时间字段存储为bigint型,看来还得先转成timestamp型的,于是翻了手册,写了如下SQL搞定! select to_char(to_timestamp(accesstime), ‘YYYY-MM-DD’) as d, count(validclick) as c from dbtm_ad_list group by d order by c desc 其中to_timestamp()函数将int型数据转换为timestamp类型的值,格式为”YYYY-MM-DD”, 再将其值用to_char()函数转换为文本类型,便可以用group by分组了。 select ( current_timestamp(0) - interval '24 hour') --将微秒改为时间戳 SELECT to_timestamp(substring(answer_time_of_date from 1 for 10) ::bigint)from client_cdr //更新当前时间 update host_info set time=(select EXTRACT(EPOCH from current_timestamp(0)))::text select * from current_timestamp(0) =="2010-11-10 10:40:46+08" select EXTRACT(EPOCH from current_timestamp(0)) (1289356894) --将 1289356894转换为 2010-11-10 10:40:46+08 SELECT to_timestamp(time::bigint) from host_info -- Column: create_time 给字段设置默认值为当前时间 -- ALTER TABLE account_balance DROP COLUMN create_time; ALTER TABLE account_balance ADD COLUMN create_time timestamp with time zone; ALTER TABLE account_balance ALTER COLUMN create_time SET STORAGE PLAIN; ALTER TABLE account_balance ALTER COLUMN create_time SET NOT NULL; ALTER TABLE account_balance ALTER COLUMN create_time SET DEFAULT ('now'::text)::timestamp(0) with time zone; COMMENT ON COLUMN account_balance.create_time IS '改动时间'; ---v---------------v------------------各种时间转换---v------------------v--------------- --导出数据库表结构 [root@class4 tmp]# /usr/local/pgsql/bin/pg_dump -s class4v3_test > tmp.sql --导入数据库表结构 [root@class4 tmp]# psql class4v3 < tmp.sql --sql CASE account_payment.payment_method WHEN 0 THEN '系统充值卡 '::text WHEN 1 THEN '快钱'::text WHEN 2 THEN '系统管理员(手工充值)'::text WHEN 3 THEN '易宝'::text ELSE 'other'::text END AS payment_method1, --pg_dump 导出sql数据 . 利用select into语句创建一个临时表,然后使用pg_dump导出临时表。 select * into test_tbl from some_tbl where some_field > some_value; pg_dump -d db_name -t test_tbl > /tmp/test.sql /tmp/test.sql里面包含的,就是要求的特定记录了。 --模糊查询 $condition="'%".$key."%'"; select resource.alias,resource.resource_id , resource.name ,cps_limit,capacity,ingress,egress,active, a.ip_cnt , client_id from resource left join (select count(*)as ip_cnt,resource_id from resource_ip group by resource_id) a on a.resource_id=resource.resource_id where resource.name like $condition or resource.alias like $condition or ( select count(*)>0 from resource_ip where resource_ip.resource_id =resource.resource_id and resource_ip.ip::varchar like $condition ) order by resource.resource_id limit '$pageSize' offset '$offset' -- --前缀匹配的用法 只取 select into id_rate rate_id from rate where rate_table_id = id_rate_table and code @> NEW.origination_destination_number::prefix_range and current_timestamp between effective_date and end_date order by code desc limit 1; $code_where=" and origination_destination_number::prefix_range <@ '$code'"; select * from cdr where origination_destination_number::prefix_range <@ '$code'"; select trunk.trunk_ip||':'||trunk.port address, trunk.trunk_id from prefix,trunk where trunk.trunk_id = prefix.trunk_id and prefix_code @>'% s' order by length (prefix_code::text) desc limit 1 --in的替代解决办法 SELECT * FROM tab WHERE col1 IN (SELECT col2 FROM TAB2) select * from client where client_id::text in (select client_id from class4_view_client_cost ) 改为: select * from client where exists(select client_id from class4_view_client_cost where class4_view_client_cost.client_id::integer=client.client_id) SELECT * FROM tab WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2) --建立索引 -- Index: code_index_code -- DROP INDEX code_index_code; CREATE INDEX code_index_code ON code USING btree (code); --索引测试 --索引前执行 4452 ms select * from class4_view_client_cost --索引后执行 14ms --prefix 特有的索引 -- Index: idx_code_prefix -- DROP INDEX idx_code_prefix; CREATE INDEX idx_code_prefix ON code USING gist (code gist_prefix_range_ops); --导出sql $copy_sql = "COPY ($download_sql) TO '$copy_file' DELIMITER '$DELIMITER' CSV $HEADER "; //daochu --导入sql cmd:='COPY ' || table_name || '(ani,dnis,action_ani,action_dnis,ani_method,dnis_method) FROM '''||filecsv ||''' CSV HEADER'; --删除重复行 id name score 1 aaa 30 2 aaa 30 3 aaa 36 4 bbb 30 5 bbb 30 6 aaa 30 7 bbb 30 8 ccc 24 9 ccc 51 保留同NAME中ID最小. delete tb from tb t where id not in (select min(id) from tb where name = t.name) 保留同NAME中ID最大. delete tb from tb t where id not in (select max(id) from tb where name = t.name) 相关方法见下: --如何按字段删除重复记录 一张表里面以两个字段为唯一字段,当几条记录的这两个字段完全相同时,需要删除重复项,如下表 a b c d 1 2 3 4 1 5 3 5 1 2 7 9 以a、b为唯一字段,第一条和第三条的a、b完全相同,所以,需要删除第一条记录1 2 3 4 或者第三条记录1 2 7 9 即如下结果: a b c d 1 2 3 4 1 5 3 5 或 a b c d 1 5 3 5 1 2 7 9 CREATE TABLE Tb1(id int, [a] varchar(255), [b] varchar(255), [c] varchar(255), [d] varchar(255)) INSERT Tb1(id, [a], [b], [c], [d]) SELECT 1, '1','2','3','4' UNION ALL SELECT 2, '1','5','3','5' UNION ALL SELECT 3, '1','2','7','9' UNION ALL SELECT 4, '1','4','7','6' delete Tb1 where [id] not in (select max([id]) from Tb1 group by a,b ) select * from tb1 drop table tb1 如果要同时删除第一和第三行 即如下结果: a b c d 1 5 3 5 语句如下: delete m from tb t inner join ( select a ,b from tb group by a , b having count(*)>1 )n on m.a = n.a and m.b = n.b 或 delete * from tb as m, ( select a ,b from tb group by a , b having count(*)>1 )n where m.a = n.a and m.b = n.b ------------------------------------------------------------------------------------ 在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?谢谢! 1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录 delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1) 3、查找表中多余的重复记录(多个字段) select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录 delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录 select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 比方说在A表中存在一个字段“name”,而且不同记录之间的“name”值有可能会相同, 现在就是需要查询出在该表中的各记录之间,“name”值存在重复的项; Select Name,Count(*) From A Group By Name Having Count(*) > 1 如果还查性别也相同大则如下: Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1 ------------------------------------------------------------------------------------------------ declare @max integer,@id integer declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >; 1 open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin select @max = @max -1 set rowcount @max delete from 表名 where 主字段 = @id fetch cur_rows into @id,@max end close cur_rows set rowcount 0 方法二 有两个意义上的重复记录,一是完全重复的记录,也即所有字段均重复的记录,二是部分关键字段重复的记录,比如Name字段重复,而其他字段不一定重复或都重复可以忽略。 1、对于第一种重复,比较容易解决,使用 select distinct * from tableName 就可以得到无重复记录的结果集。 如果该表需要删除重复的记录(重复记录保留1条),可以按以下方法删除 select distinct * into #Tmp from tableName drop table tableName select * into tableName from #Tmp drop table #Tmp 发生这种重复的原因是表设计不周产生的,增加唯一索引列即可解决。 2、这类重复问题通常要求保留重复记录中的第一条记录,操作方法如下 假设有重复的字段为Name,Address,要求得到这两个字段唯一的结果集 select identity(int,1,1) as autoID, * into #Tmp from tableName select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID select * from #Tmp where autoID in(select autoID from #tmp2) 最后一个select即得到了Name,Address不重复的结果集(但多了一个autoID字段,实际写时可以写在select子句中省去此列) select * from tablename where id in ( select id from tablename group by id having count(id) > 1) delete a from 表 a where exists(select * from 表 where name=a.name and id>a.id) -- 级联删除 Foreign Key: class4_fkey_code_code_deck_id -- ALTER TABLE code DROP CONSTRAINT class4_fkey_code_code_deck_id; ALTER TABLE code ADD CONSTRAINT class4_fkey_code_code_deck_id FOREIGN KEY (code_deck_id) REFERENCES code_deck (code_deck_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE; --创建序列 CREATE SEQUENCE invocie_item_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 137 CACHE 1; --设置序列 ALTER TABLE invoice_item ALTER COLUMN id SET DEFAULT nextval('invocie_item_id_seq'::regclass); select * from crosstab ('select code,rate_id,resource_id from resource join rate on rate.rate_table_id=resource.rate_table_id where resource_id in (448,449,450) order by 1,2') AS ct(code prefix_range, res_id_1 integer, res_id_2 integer, res_id_3 integer, res_id_4 integer, res_id_5 integer, res_id_6 integer, res_id_7 integer, res_id_8 integer) select code,resource_id from resource join rate on rate.rate_table_id=resource.rate_table_id where resource_id in (448,449,450) order by 1,2;[/size]
发表评论
-
Public key for postgresql-libs-8.4.7-1PGDG.rhel5.i386.rpm is not installed
2011-05-28 10:01 795Public key for postgresql-libs- ... -
php 操作postgresql
2011-05-26 21:35 944<?php # $id = $_GET['id']; ... -
PostgreSQL 导出数据
2011-05-20 10:03 710导出数据 [root@class4 bin]# ./pg_du ... -
RedHat Linux 安装 PostgreSQL 8.4.2
2011-04-26 23:27 1312下载并解包 $ tar zxvf postgresql-8.2 ... -
postgresql 内存优化
2011-04-07 10:04 1082PG的配置文件是数据库目录下的postgresql.conf文 ... -
postgresql访问认证设置
2011-04-07 09:20 2274安装postgresql是一件很简单的事,但是当我第一次安装了 ... -
postgresql的客户端编码
2011-04-07 09:09 2286可以通过以下步骤: 1、打开pgadmin连接到数据库 2 ... -
【Postgresql】字符串操作函数
2011-04-02 14:37 1022函数:string || string 说明:String ... -
check_expired_record
2011-03-21 16:21 682function check_expired_record( ... -
PostgreSQL 里的表空间
2011-02-10 19:18 1529PostgreSQL 里的表空间允许数据库管理员在文件系统里定 ... -
pg的列名是区分大小写的,但SQL语句会自动转换为小写,除非用双引号强调。
2010-12-30 20:04 1603pg对大写字母的处理 列名要加双引号 添加一个 AA列 -- ... -
PostgreSQL 实现累加
2010-12-21 09:41 2255exchange=# select invoice_id, c ... -
PostgreSQL 实现按月按年,按日统计 分组统计
2010-09-20 16:21 2134--按年分组查看 select to_char ...
相关推荐
PostgreSQL数据库领域经典著作 系统讲解PostgreSQL技术内幕,深入分析PostgreSQL特色功能,包含大量来自实际生产环境的经典案例和经验总结 PostgreSQL数据库是目前功能最强大的开源数据库,它基本上包含其他所有...
postgresql 开发指南 总结性文档 包含了 join group groupsets rollup cube sql函数 pgsql函数 等示例。及汇总
博客:PostgreSQL的学习心得和知识总结(六十五)|关于PostgreSQL数据库 实现MySQL数据库find_in_set()函数 的实现方案
Sequence是数据库系统按照一定规则自动增加的数字序列。这个序列一般作为代理主键(因为不会重复),没有其他任何...比如Oracle、DB2、PostgreSQL数据库有Sequence,MySQL、SQL Server、Sybase等数据库没有Sequence。
postgreSQL窗口函数总结 1 窗口函数说明 2 row_number/rank/dense_rank的区别 2 窗口函数语句 2 1 准备数据 3 1.1 创建测试的表test1 3 1.2 插入数据到test1表中 3 2 rank over 窗口函数使用 3 2.1 按照分区查看每行...
Postgresql内置分区表的使用总结 内置分区表简介 在postgresql10之后,引入了内置分区表,用户不需要先在父表上定义insert,update,delete触发器,对父表的DML操作会自动路由到相应分区,相比传统分区表大幅度降低了...
PostgreSQL 正则表达式 常用函数的总结 对那些需要进行复杂数据处理的程序来说,正则表达式无疑是一个非常有用的工具。本文重点在于阐述 PostgreSQL 的一些常用正则表达式函数以及源码中的一些函数。 正则相关部分的...
现在所用的gogo查的版本中,查询热点区域所用的API,是先在servlet中计算出用户选择区域周围的区域ID,构造出SQL查询语句,后从postgresql中读取所需的热点区域信息。 SQL语句如下: Select regionno from ...
总结下前期利用python操作postgresql数据库的笔记,方便以后使用,顺便安利markdown真舒服 文章目录前言python上传dataframe数据入数据库pd.to_sql上传copy_from上传python读取数据库数据为dataframe创建connection...
总结 清单 [DB Migration](#db migration) Server - Scalable Open Source PostgreSQL-based Database Cluster. - - - Scalable PostgreSQL cluster for real-time workloads. - Open source streaming SQL database...
集成SQL IntegreSQL为您的集成测试管理隔离的PostgreSQL数据库。 通过允许工程师使用真正的PostgreSQL测试数据库编写快速执行,并行和确定性的集成测试,对您的工程师有所帮助。 尽可能接近测试中的生活环境。 ...
目前大概只有MySQL,SQLite,PostgreSQL,Sybase带有这样的功能,而包括Oracle和SQL Server在内的很多数据库都没有。 鉴于这样的情况,一般开发者采用一种通用的方法来避免不安全的数据写入数据库–base64编码。这样...
数据库总结(350 个数据库,14 个分类)共 140 个:Oracle、MySQL、Microsoft SQLServer、PostgreSQL、SQLite
JSP中连接数据库总结.连接Oracle8/8i/9i数据库(用thin模式),Sql Server,DB2,Informix,Sybase,MySQL,PostgreSQL
在 Meteor 的 Trello 路线图 ( ) 中,投票最多的功能是 SQL 支持,PostgreSQL 或 MySQL。 由于 Meteor 中没有日期,我在这里总结了我找到的部分解决方案。1.- 仅将 SQL 用于客户端查询。 让我们面对现实吧,Mongo ...
各种数据库JDBC连接方法总结-包括: SQL Server2000 、 SQL Server2005、Oracle、Access直连方法:(不写数据源名的桥连方法)、MySql、IBM DB2、Sybase、Informix、PostgreSQL、MaxDB、FrontBase、JavaDB/Derby等
当前各种主流数据库有很多,包括Oracle, MS SQL Server, Sybase, Informix, MySQL, DB2, Interbase / Firebird, PostgreSQL, SQLite, SAP/DB, TimesTen, MS ACCESS等等。数据库编程是对数据库的创建、读写等一列的...
《oracle数据库性能优化的艺术》重在“授人以渔”,虽然主要内容是围绕oracle数据库系统展开的,但是书中的观点同样适用于db2、sql server、mysql、postgresql等数据库系统。 oracle数据库性能优化的艺术 目录 ...
Oracle Nologging全面总结 Oracle RAC 集群安装部署 Oracle RMAN 单实例异机迁移恢复(版本:11GR2) Oracle存储过程性能分析案例 Oracle技术加油站:快速处理紧急性能问题的工具与经验 Oracle诊断性能问题时常用...
DBeaver 是一款简单,可靠的软件,面向需要管理多个SQL数据库,执行SQL脚本,迁移...总结起来,在管理从简单到复杂的多个数据库连接,触发器,过程和视图以及执行SQL脚本时,DBeaver 被证明是一种稳定有效的解决方案。