数据库操作

PSQL数据库操作:

服务器数据导出 / 导入:

  • PSQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

    -- 导出数据
    psql -h <db_host> -p <db_post> -U <user_name> -d <db_name> -c "COPY (select * from <table_name> where <column_name> >0 ) to STDOUT with csv header" > outer_risk_result-1-3.csv

    -- 导入新数据
    psql -h <db_host> -p <db_post> -U <user_name> -d <db_name> -c "COPY <table_name> FROM STDIN with csv header" < outer_risk_result.csv

    ---- 当导出数据指定了表头，导出时也需要指定表头
    psql -h <db_host> -p <db_post> -U <user_name> -d <db_name> -c "COPY (select name,update_time from outer_risk_result where score>0 ) to STDOUT with csv header" > change_data.csv
    psql -h <db_host> -p <db_post> -U <user_name> -d <db_name> -c "COPY outer_risk_result(name,update_time) FROM STDIN with csv header" < change_data.csv

服务器数据库备份：

  • PSQL
    1
    2
    3
    4
    5
    6
    7
    8
    # 备份

    pg_dump -h host -U username <databasesname> > [filepath/backfile.bak]

    ### 恢复

    psql -h host -U username -d <databasename> < [filepath/backfile.bak]

SQL 笔记

  • 对integer类型字端进行模糊查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 模拟前提：表结构

create table info(
 id int primary key,
 name varchar,
 age int
);

--查询：

> Integer属性的模糊查询：

```sql
select * from info where cast(id as VARCHAR (20)) like '11%';
  • 对integer 与字符串数组进行联合查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- 模拟前提：表结构

create table data_permission(
 id int primary key,
 group_ids varchar[]
);

create table data_group (
 id int primary key,
 name varchar
 members varchar[]
);

-- data_permission.group_ids 存储的是data_group.id的集合。 

-- 查询data_permission.group_ids关联的data_group数据。
select
    dp.user_id
    ,dp.group_ids
    ,dg.id
    ,dg.name
    ,dg.members
from data_permission dp, data_group dg
where  dp.permissions @> array[cast(dg.id as varchar)] 
group by dp.user_id, dp.permissions, dg.id, dg.name, dg.members ;