携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第19天，点击查看活动详情
📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程😜😜😜
擅长Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理。
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️
前言
PostgreSQL技能树高级篇1.服务端编程
1.1 聚合和分组
现有员工信息表如下：
create table employee
(
id serial primary key,
name text,
dept text,
salary money
);
下面哪条查询，可以给出每个部门工资最高的员工的 id, name, dept, salary 四项信息？A
A.
select l.id, l.name, l.dept, l.salary
from employee as l
join (select max(salary) as salary, dept
from employee
group by dept) as r
on l.dept = r.dept and l.salary = r.salary
B.
select id, name, dept, max(salary)
from employee
group by dept, id, name
having salary = max(salary);
C.
select id, name, dept, max(salary)
from employee
group by dept;
D.
select id, name, dept, max(salary)
from employee
where salary = max(salary)
group by dept;
1.2 连接查询
我们现在看下面这个客户/订单系统
create table customers
(
id serial primary key,
company_name text,
address text,
city text,
state text
);
create table products
(
id serial primary key,
description text,
unit_price money
);
create table orders
(
id serial primary key,
product_id integer references products (id),
order_date timestamp,
quantity integer,
customer_id integer references customers(id)
);
我们希望这个数据库能够允许每个订单包含多种商品，那么应该如何改造？B
A.在 order 表的主键上加唯一约束
B.添加一个 order_detail 表，引用 order id、product id，增加 quantity 列
order 表中删除 product id 和 quantity 列
C.将 order 表的修改为以 product id 和 customer id 作为联合主键
D.删除 orders 表中的 product id 和 quantity 列
1.3 子查询
现有员工表
create table employee
(
id serial primary key,
name text,
dept text,
salary money
)
我们希望找出比销售部（dept 为 sale）工资最高的员工工资更高的那部分人，
查询出他们的完整信息，下面哪一项可以满足要求？C
A.
select l.id, l.name, l.dept, l.salary
from employee as l
join employee as r on l.salary > max(r.salary)
where r.dept = 'sale'
group by r.dept
B.
select id, name, dept, salary
from employee
having salary > (select max(salary) from employee where dept = 'sale')
C.
select id, name, dept, salary
from employee
where salary > (select max(salary)
from employee
where dept = 'sale')
D.
select id, name, dept, salary
from employee
where dept = 'sale'
group by dept
having salary > max(salary)
1.4 分页
我们有如下订单表：
create table orders
(
id serial primary key,
product_id integer,
order_date date default now(),
quantity integer,
customer_id integer
);
现在开发人员希望查询指定的某一天内的数据，并按每一百条一页查询，那么正确的语句应该是：B
A.
select id, product_id, order_date, quantity, customer_id
from orders
where date = $1
offset $2;
B.
select id, product_id, order_date, quantity, customer_id
from orders
where date = $1
offset $2 limit 100;
C.
select id, product_id, order_date, quantity, customer_id
from orders
where date = $1;
D.
select id, product_id, order_date, quantity, customer_id
from orders
where date = $1 and
offset $2 and limit 100;
1.5 树结构溯根
现有一个表 node
create table node
(
id serial primary key,
pid integer,
content text
);
其 pid 列引用 id 列，形成一个树结构，根节点的 pid 为 0。
现在我们希望写一个查询，找到某一个给定id的记录，其父节点、父节点的父节点，直至根节点的路径。
那么这个查询应该是：C
A.
with t as (
select id, pid, content
from node
where id = $1
union all
select node.id, node.pid, node.level
from node
join t on node.id = t.pid)
select node.id, node.pid, content
from node
join t on node.id = t.id;
B.
select node.id, node.pid, node content
from node as t
where t.pid = (select id from t where id = t.pid)
C.
with recursive t(id, pid, content) as (
select id, pid, content
from node
where id = $1
union all
select node.id, node.pid, node.content
from node
join t on node.id = t.pid)
select node.id, node.pid, content
from node
join t on node.id = t.id;
D.
select node.id, node.pid, node.content
from node
join node as p on node.pid = p.id
where id = $1;
注解：
Postgres中有个 with recursive的查询方式，可以满足递归查询(一般>=2层)
1.6 循环
下面哪一项定义的函数可以生成指定范围内的整数数列？B
A.
create function gen(start integer, stop integer)
returns integer as
$$
begin
for idx in start .. stop
loop
yield idx;
end loop;
end;
$$ language plpgsql;
B.
create function gen(start integer, stop integer)
returns setof integer as
$$
begin
for idx in start .. stop
loop
return next idx;
end loop;
end;
$$ language plpgsql;
C.
create function gen(start integer, stop integer) returns integer as
$$
begin
for idx in start .. stop
loop
return idx;
end loop;
end;
$$ language plpgsql;
D.
create function gen(start integer, stop integer)
returns setof integer as
$$
begin
for idx in start .. stop
loop
select idx;
end loop;
end;
$$ language plpgsql;
1.7 DDL
## 创建表
下列方法中，可以创建表的有：B
1.使用 create table xxx();语句
2.使用 copy 语句从 csv 导入数据
3.使用 create table as select... 语句从查询中创建表
A.1, 2
B.1， 3
C.2, 3
D.只有 1
## 建表权限
SmartMarket 公司有一百名分析师，他们都属于 analyst 团队，现在他们希望能够在 market 仓库的 ana schema 下 自由的建表和删除表，
以便进行模型试验。下列操作步骤中需要选择哪几项？(ana schema 和分析师的团队角色还不存在) D
1.create schema ana
2.create role analyst
3.grant create, usage on schema ana to analyst
4.将 analyst 角色授予每个分析师的账号
5.删除 public schema
6.grant superuser on schema ana to analyst
7.grant owner on schema ana to analyst;
A.2, 3, 4, 5
B.1, 2, 6, 7
C.所有全部
D.1，2， 3， 4
2.集群和复制
2.1 standby
关于 PG Standby，错误的是：B
A.从节点处于制度状态
B.从节点不需要和主节点硬件环境一致
C.复制节点需要有主节点的超级用户权限。
D.从节点会保持与主节点的数据和结构一致
## 高可靠备份
你的团队有一个非常重要的业务数据库，希望在需要时，可以重建它在一周内任意某个时间点的状态。下列规划中不必要的是： B
1.对 WAL 日志建立备份，按时间保留所有的 WAL 文件作为增量备份
2.每天做一次冷备
3.需要重现时，用离目标时间点最近的前一次冷备恢复一个数据库节点，启动为 standby 状态，用对应的 wal 恢复。
4.建立流备份节点
5.每天非高峰期将数据库离线，完整复制一份数据区目录
A.1, 2
B.4, 5
C.3
D.2, 4
2.2 流式复制
关于流式复制，错误的是：C
A.主从节点的硬件配置可以不一致
B.订阅节点可以作为只读的从节点，提供读写分离
C.流式复制的主节点损坏，会导致订阅节点也损坏。
D.节点间传输的是 wal 数据流
2.3 FDW
## 外部数据源
RDVD 公司有一个 Oracle 数据库和一个PG数据库，现在需要每天汇总一次 PG 中的订单表和 oracle 中的客户信息表，从中生成一份 报表。
现在你想在尽量不增加冗余数据的前提下，在PG服务上每天执行一次简单的查询任务来生成报表，那么应该：A
A.
在 PG 数据库建立一个 fdw 数据源，连接 Oracle 数据库
在 PG 服务器中建立一个视图， 对客户信息表进行的查询变成一个 PG 查询
基于客户信息视图和订单表生成每日报表
B.
在 PG 服务器上建一个同构的客户表
在 Oracle 服务器上写一个触发器
将客户信息写入 PostgreSQL 数据库
C.
用一个定时任务复制 Oracle 中的客户信息变更
写入 PG 数据库
D.
设置 PG 为 Oracle 的复制节点
订阅 Oracle 的变更流
\
