PostgreSQL查询数据库的连接信息

select * from pg_stat_activity;
-- pg_stat_activity为系统视图
-- 可根据where限定数据库名，连接ip，表名等字段查询相关信息

query_start:active状态的查询开始时间，如果状态不是active的，那么就是最后一次查询开始的时间

state:运行状态，可以为几种值。
active:正在执行查询；
idle:等待新的命令；
idle in transaction:后端是一个事务，但是尚未执行查询；
idle in transaction(aborted):和idle in transaction类似，除了事务执行出错。

query:执行的查询文本（即SQL）。
如果状态是active，那么就是正在执行的SQL；
如果是其他状态，则展示最后一次执行的SQL。

所以可以用：
select count(*) from pg_stat_activity where state='idle';
查询闲置连接数。如果数字过大，可以认为是有问题的（如连接忘记关闭）。
如果想进一步定位到有问题的SQL，可以如下查询：
select query,count(*) as num from pg_stat_activity where state='idle' group by query order by num desc;

杀掉进程
SELECT pg_terminate_backend(PID);
这种可以kill掉各种操作(select、update、delete、drop等)操作

Java实现页面（一）

package checkmod;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.ButtonGroup;
import javax.swing.JButton;
import javax.swing.JCheckBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JRadioButton;
import javax.swing.JTextField;

public class Checkmod extends JFrame implements ActionListener {

private JPanel jp=new JPanel();
private JCheckBox[] jcba={new JCheckBox("豫菜"),new JCheckBox("川菜"),new JCheckBox("粤菜"),new JCheckBox("西餐"),new JCheckBox("其他")};
private JRadioButton[] jrba={new JRadioButton("5~15岁"),new JRadioButton("16~35岁"),new JRadioButton("26~35岁"),new JRadioButton("36~45岁"),new JRadioButton("36~45岁"),new JRadioButton("46~55岁")};
private JButton[] jba={new JButton("确定"),new JButton("取消")};
private JLabel[] jla={new JLabel("年龄段："),new JLabel("爱好："),new JLabel("调查结果：")};
private JTextField jtf=new JTextField();
private ButtonGroup bg=new ButtonGroup();
public Checkmod(){
jp.setLayout(null);
for(int i=0;i<5;i++){
jrba[i].setBounds(40+i*100,40,80,30);
jcba[i].setBounds(40+i*120,100,120,30);
jp.add(jrba[i]);jp.add(jcba[i]);
jrba[i].addActionListener(this);
jcba[i].addActionListener(this);
bg.add(jrba[i]);
if(i>1)
continue;
jla[i].setBounds(20, 20+i*50, 80, 30);
jba[i].setBounds(400+i*120, 200, 80,26);
jp.add(jla[i]);jp.add(jba[i]);
jba[i].addActionListener(this);
}
jla[2].setBounds(20, 150, 120, 30);jp.add(jla[2]);
jtf.setBounds(120, 150, 500, 26);jp.add(jtf);
jtf.setEditable(false);
this.add(jp);this.setTitle("食物调查表");
this.setBounds(100, 100, 700, 280);
this.setVisible(true);this.setResizable(false);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}


public static void main(String[] args) {
// TODO Auto-generated method stub
new Checkmod();
}

@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
if(e.getSource()==jba[1]){
for(int i=0;i<jcba.length;i++){
jcba[i].setSelected(false);
jtf.setText("");
}
}else{
StringBuffer temp1=new StringBuffer("你是一个");
StringBuffer temp2=new StringBuffer();
for(int i=0;i<5;i++){
if(jrba[i].isSelected()){
temp1.append(jrba[i].getText());
}
if(jcba[i].isSelected()){
temp2.append(jcba[i].getText()+".");
}
}
if(temp2.length()==0){
jtf.setText("爱好为空？？？");
}else{
temp1.append("的人，比较喜欢");
temp1.append(temp2.substring(0, temp2.length()-1));
jtf.setText(temp1.append("。").toString());
}
}
}

}

来源连接：https://jingyan.baidu.com/article/f79b7cb319780c9144023ec1.html

PostgreSQL跨库联查

--导入支持(windows下使用，其他环境不支持)

create extension dblink

--方法一（缺陷：默认端口号5432）

select * from
dblink ( 'host=$IP port=$端口号 dbname=$数据库名 user=$用户名 password=$密码','$sql') as a(
$表结构（类似于建表命令 字段名 字段类型，....）
)

--方法二

--建立连接
SELECT dblink_connect('$连接名(自定义)','host=$IP dbname=$数据库名 user=$用户名 password=$密码');
--查看连接
select dblink_get_connections();

--断开所有连接
select dblink_disconnect();

--断开指定连接
select dblink_disconnect('$连接名');

--查询(如创建连接时没有定义连接名，则此处可省略)
SELECT * FROM dblink('$连接名','$sql')AS a ($表结构);

PostgreSQL查询数据库中所有表的行数

--联合两层schema并比较对应表的行数是否相等(需在vacuum后才准确，否则会有增删浮动)

SELECT a.relname,a.reltuples merge,b.reltuples center,a.reltuples=b.reltuples FROM (
SELECT relname, reltuples
FROM pg_class r JOIN pg_namespace n
ON (relnamespace = n.oid)
WHERE relkind = 'r' AND n.nspname = 'merge'
) a FULL JOIN (
SELECT relname, reltuples
FROM pg_class r JOIN pg_namespace n
ON (relnamespace = n.oid)
WHERE relkind = 'r' AND n.nspname = 'center'
) b ON a.relname=b.relname

部署博客的步骤（云虚拟机）

### 第一步 下载wordpress按照包 wordpress.zip
### 第二步 将压缩包解压 得到一个包含index.html的目录结构。
### 第三步 下载ftp管理工具 FileZilla
### 第四步 登陆阿里云控制台。查看服务器ip， 用户名，密码。数据库地址，数据库名称，数据库密码。如果忘记可以重置。
### 使用FileZilla链接服务器。如果连接成功，可以先将服务器htdocs目录中的文件删除。然后将刚刚解压得到的所有文件拖拽到htdocs目录中(是所有得到的子文件)，index.html在htdocs的一级目录下。
### 第五步 如果上传完毕。可以登陆自己的域名。会看到wordpress设置页面。根据步骤设置数据库即可。
### 第六步 登陆wordpress后台(xxxx.com/wp-admin) 可以写文章。设置网站模版。

postgresql根据结果集中某字段限定该字段相同值只获取20条

SELECT
T .company_id,
T .business_description_cn,
T .business_scope_cn,
T .company_tag_cn,
T .industry_id,
T .industry,
T .company_name_cn
FROM
(
SELECT
company_id,
business_description_cn,
business_scope_cn,
company_tag_cn,
industry_id,
config."dictionary".name_cn AS industry,
company_name_cn,
ROW_NUMBER() OVER(PARTITION BY industry_id) AS ROW
FROM
company_profile
LEFT JOIN config."dictionary" ON company_profile.industry_id = config."dictionary". ID
WHERE
industry_id IS NOT NULL
AND company_status_id LIKE '2%'
) T WHERE ROW <= 20

ElasticSearch增删改查日记

  public static void main(String[] args) {
  TransportClient client = null;
  try {
  client = new PreBuiltTransportClient(Settings.EMPTY)
  .addTransportAddress(new
  InetSocketTransportAddress(InetAddress.getByName("localhost"), 9300));
  SearchResponse response = client.prepareSearch()
  .setQuery(QueryBuilders.boolQuery()
  .should(QueryBuilders.rangeQuery("tax_paid").from("452588.45").to("452588.45"))
  .should(QueryBuilders.rangeQuery("cash").from("17573086.76").to("17573086.76"))
  .should(QueryBuilders.wildcardQuery("company_name", "*生物*"))
  )
  .get();
  System.out.println(response.getHits().totalHits);
  for (SearchHit hit : response.getHits().getHits()) {
  System.out.print("index:" + hit.getIndex() + "----");
  System.out.print("type:" + hit.getType() + "----");
  System.out.print("value:" + hit.getSource().get("company_id"));
  System.out.println();
  }
  } catch (UnknownHostException e) {
  e.printStackTrace();
  } finally {
  if (client != null) {
  client.close();
  }
  }
  }

/*
 * 批量操作
 */ 
  public static void main(String[] args) { TransportClient client = null; try {
   String json = "{" + "\"user\":\"kimchy\"," + "\"postDate\":\"2013-01-30\"," +
   "\"message\":\"trying out sadfsdafasdf\"" + "}"; String json2 = "{" +
   "\"user\":\"kimchy\"," + "\"postDate\":\"2013-01-31\"," +
  "\"message\":\"trying out aaaaa\"" + "}"; client = new
   PreBuiltTransportClient(Settings.EMPTY) .addTransportAddress(new
   InetSocketTransportAddress(InetAddress.getByName("localhost"), 9300));
   BulkRequestBuilder bulkRequestBuilder = client.prepareBulk();
   bulkRequestBuilder.add(client.prepareIndex("company", "company_profile", "2")
   .setSource(json)); bulkRequestBuilder.add(client.prepareIndex("company",
   "company_profile", "3").setSource(json2)); BulkResponse responses =
   bulkRequestBuilder.get(); if (responses.hasFailures()) {
   System.out.println(responses.toString()); } } catch (UnknownHostException e)
   { e.printStackTrace(); } finally { if (client != null) { client.close(); } }
   }
  

/*
 * 删除数据
 *
 */
  public static void main(String[] args) {
  TransportClient client = null;
  try {
  client = new PreBuiltTransportClient(Settings.EMPTY)
  .addTransportAddress(new
  InetSocketTransportAddress(InetAddress.getByName("localhost"), 9300));
  DeleteResponse response = client.prepareDelete("company", "company_profile",
  "1").get();
  System.out.println(response.status());
  } catch (UnknownHostException e) {
  e.printStackTrace();
  } finally {
  if (client != null) {
  client.close();
  }
  }
  }

/*
 * 插入数据
*/
 public static void main(String[] args) { TransportClient client = null;
   try { client = new PreBuiltTransportClient(Settings.EMPTY)
  .addTransportAddress(new
   InetSocketTransportAddress(InetAddress.getByName("localhost"), 9300)); String
   json = "{" + "\"user\":\"kimchy\"," + "\"postDate\":\"2013-01-30\"," +
   "\"message\":\"trying out Elasticsearch\"" + "}"; IndexResponse response =
   client.prepareIndex("company", "company_profile").setSource(json,
   XContentType.JSON).get(); String _index = response.getIndex(); String _type =
   response.getType(); String _id = response.getId(); long _version =
   response.getVersion(); System.out.println("index:" + _index + "\ntype:" +
   _type + "\nid:" + _id + "\nversion:" + _version); } catch
   (UnknownHostException e) { e.printStackTrace(); } finally { if (client !=
   null) { client.close(); } } }