常用sql记录

pgsql 常用命令

shell连接:PGPASSWORD=123456 psql -U test-d database
redis连接:redis-cli -a 123456

DDL

一.字段操作

1.新增字段:

ALTER TABLE public.tbl_asset_login ADD ip_ssh_url varchar(1024) NULL;

二. 索引操作

pgsql常用索引B-tree，Hash，GiST，SP-GiST，GIN和BRIN
B-tree可以处理对可以排序成某些顺序的数据的等式和范围查询。><=
Hash索引只能处理简单的等式比较。当使用=运算符进行比较时，查询计划器将考虑使用Hash索引。以下命令用于创建Hash索引：

CREATE INDEX name ON table USING HASH (column);

唯一索引

CREATE UNIQUE INDEX name ON table (column [, ...]);

1. 新增索引

CREATE INDEX continent_index ON tbl_geo_info (continent);
新建索引时会造成锁表;大表时会对业务产生影响,实测千万级数据17个字段的表格，新增四条索引耗时22min。pgsql版本9.4.4
CREATE INDEX concurrently continent_index ON tbl_geo_info (continent);
增加concurrently,建立索引时pg会扫描两次表,类似第二次类似于增量建立索引,不会锁表,但是耗时较长.

2. 删除索引:

drop index index_name

3. 重建索引:

reindex index_name

三 表操作

DML:

1. 查询某个字段相同的所有数据

select * from tbl_asset_info tal where city in (select city from tbl_asset_info group by city having count(1) > 1);

2. 左连接

select tal.* from tbl_asset_login tal left join tbl_asset_info info on info.uuid=tal.asset_uuid where info.asset_key=‘1.0.0.2’

3.有则更新，无则插入

• insert into table_name (key1,key2,key3) values(‘value1’,‘value2’,‘value3’)
  on conflict(key1,key2) do
  update set key1=‘value1’,key2=‘value2’,key3=‘value3’;
  PS:on conflict()中的字段，必须是主键索引或唯一索引
• INSERT INTO {tableName} ({insertKey}) SELECT {insertValue} WHERE NOT EXISTS (SELECT 1 FROM {tableName} WHERE column = {value} limit 1)

4. 数组的插入和提取

• insert into table_name (key1,key2) values (‘value1’, array[1,2]);
  该方法在处理空数据时会报错
• insert into table_name (key1,key2) values (‘value1’, ‘{1,2}’);

5. 时间格式

key1 varchar
key2 time without time zone eg:07:00:01

• insert into table_name(key1,key2) values (‘value1’,‘value2’);
• select key1,key2::text from table_name ;

其他操作:

1. 命令行执行sql文件:

PGPASSWORD=123456 psql -h host -U username -d myDataBase -a -f test.sql

2. 命令行执行sql语句;

PGPASSWORD=123456 psql -h host -U username -d myDataBase -a -c “select * from tbl_asset_info;”

3. count()探寻

主键：explain select count(tgi.id) from tbl_geo_info tgi ;
非索引字段：explain select count(tgi.ip_end_str) from tbl_geo_info tgi;
explain select count(*) from tbl_geo_info tgi;
explain select count(1) from tbl_geo_info tgi;
四者返回相同，耗时相同

Aggregate  (cost=454290.88..454290.89 rows=1 width=4)
  ->  Seq Scan on tbl_geo_ipv4 tgi  (cost=0.00..422950.70 rows=12536070 width=4)