本篇博客是对一些比较常见的数据库知识的汇总，并会持续更新。

Postgres

如何获取postgres所有的表里的记录条数？

sql">SELECT 
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' 
ORDER BY reltuples DESC;

如何获取postgres里某个表的主键?

    public List<String> getPrimaryKeysByTable(String tableName, JdbcTemplate jdbcTemplate) {
        String retrievePrimaryKeySql =
                "select kc.column_name from information_schema.table_constraints tc join information_schema.key_column_usage kc on kc.table_name = \'"
                        + tableName
                        + "\' and kc.table_schema = \'public\' and kc.constraint_name = tc.constraint_name where tc.constraint_type = \'PRIMARY KEY\'  and kc.ordinal_position is not null order by column_name";
        return jdbcTemplate.queryForList(retrievePrimaryKeySql, String.class);
    }

join两个表：

SELECT * FROM billitem INNER JOIN bill ON (billitem.bill_id = bill.id and bilL.market_id = 'ROC') ;

join三个表：

select * from billitem_charges b1 inner join billitem b2 on b1.billitem_id = b2.id
inner join bill b3 on b2.bill_id =b3.id and b3.market_id = 'ROC';

例子：
 

sql">select t.* from technicalresource t join item i  
on t.internal_id = i.internalid join "snapshot" s 
on i.snapshot_id=s.snapshotid and s.subscription_id = 'E25A2EE0-7A10-4F85-AB1E-E3E2BB770725' ;