携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第18天，点击查看活动详情

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程😜😜😜
擅长Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理。
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

前言

PostgreSQL技能树基础篇

---

1.基本概念

1.1 关系型数据库

下列数据库产品中，哪一个通常不被视作关系型数据库？ A
A.redis
B.MySQL
C.H2
D.SQL Server

1.2 服务器和客户端

## 连接 PostgreSQL 数据库
​
下列选项中，可以用于连接到PostgreSQL数据库的有：C
A.GUI 工具 PgAdmin
B.命令行工具 psql
C.全部都可以
D.Go 语言驱动库 PQ。
​
​
## PostgreSQL 服务器
​
关于 PostgreSQL 服务器，以下说法错误的是：D
A.PostgreSQL 的服务器将数据存储在一个 owner 为 postgres 的目录下。
B.PostgreSQL 的每个活跃连接对应一个 worker 进程。
C.PostgreSQL 可以设置监听端口、地址和最大连接数
D.PostgreSQL 的服务器和客户端必须运行在不同的计算机上。

2.安装和使用

2.1 安装

下列内容，不正确的是哪一项？C
A.修改 postgres.conf 配置文件，可以修改 PG 的监听地址和端口。
B.在 windows 上，可以下载安装包进行安装。
C.安装过程需要 root 权限，也应该将 root 设置为超级用户。
D.PostgreSQL 默认的超级用户名为 postgres 。

2.2 登陆

你的团队有一个公用的开发服务器，你要安装一个 PostgreSQL 服务公用，你们希望每位同事 使用一个独立的开发数据库，不互相混淆。同时这个服务器不向其他部门开放。数据也希望有足 够的安全保障。你的同事们都有这台开发服务器的 ssh 账号，平时远程登录到服务器上工作。
下面哪个组合最符合你们的需求？D
​
1.为每位同事建立一个数据库账号
2.初始化口令为123456，告知他们登录后修改密码。
3.为每位同事建立对应的开发数据库，将他们的用户设置为该数据库的 owner
4.将每位同事的用户身份都设置为superuser，并禁止他们登录别人的数据库
5.为每位同事建立一个与操作系统账号同名的数据库账号
6.修改 pg_hba.conf ，设置本地用户可以用操作系统集成登录
7.设置 postgres 账号口令为 123456，群发告知同事
​
A.7, 6
B.2, 3, 4, 5, 6, 7
C.5, 3, 6
D.1, 2, 4
​
​
## RDS 管理
RShop 公司在云服务商处购买了一个 PostgreSQL RDS 服务，作为 DBA ，你会采取哪些措施来管理它？D
​
1.将云服务商提供的用户名口令交付开发团队
2.要求开发团队交付数据库部署脚本
3.部署并初始化数据库结构
4.建立应用用户，采用口令登录，仅赋予数据查询和 DML 权限
5.将应用用户的连接方式写入配置中心，供应用系统访问
6.待开发团队初始化数据库后，修改用户名口令
7.修改用户名口令后，将系统用户和口令保存在可信的存储中
​
A.2, 3, 4, 5, 7
B.2, 4, 6
C.1, 3
D.1, 3, 6, 4, 5, 7

2.3 使用

你是 SmartMarket 公司的一名开发人员，公司使用 Linux 运行 PostgreSQL 服务，而你的开发机使用 MacOS。下列说法正确的是：A
​
1.在 MacOS 上，使用 Homebrew 安装 PostgreSQL 后，在当前用户下执行 psql postgres 可以登入数据库
2.在 Linux 上，使用 yum 或 apt 安装 PostgreSQL 后，sudo su postgres 切换到 postgres 用户，可以执行 psql 登入数据库
3.对于自己安装的数据库服务器（非RDS），如果需要开放远程登录，需要编辑数据目录下的 postgresql.conf 和 pg_hua.conf
4.安装 PostgreSQL 前，需要重装系统以获得一个干净的内核环境
5.安装 PostgreSQL 前，需要手工建立一个名为 postgres 的超级用户
6.安装 PostgreSQL 需要先安装 gcc 或 llvm clang
7.对于使用 linux 的开发者，通常需要安装 postgresql 的开发包，这通常可以在主流的 linux 发行版的软件服务中找到，
一般叫 postgresql-devel 或 postgresql-dev
​
​
A.1, 2, 3, 7
B.1, 2, 3, 4, 5, 6, 7
C.1, 2, 3
D.5, 6, 7

3.查询数据

3.1 psql

关于 PSQL 命令行，以下说法正确的是：C
​
1.psql 命令行内置在 postgresql 安装包中
2.psql 命令行默认以系统当前用户身份登录本机，连接用户同名数据库
3.进入 psql 命令行，可以使用 ? 查看内置的快捷指令
4.在 psql 命令行中，可以通过 ! 在服务端执行shell命令
5.psql 命令行必须以 postgres 用户身份执行
6.psql 命令行必须以 root 用户身份执行
7.psql 只能用于 linux 和 bsd 系统
8.在 psql 中执行 \d 可以查看 relations 列表，即用户表和视图
9.在 psql 中执行 \l 可以查看数据库列表
10.在 psql 中执行 \d 表名 可以查看指定表或视图的详细信息
11.在 psql 中执行 \timing 可以开关查询计时
12.analyze 表名; 和 explain 查询; 以及 explain analyze 查询; 都是 psql 特有的命令
​
A.全部都错
B.5, 6, 7, 8, 9, 10, 11, 12
C.1, 2, 3, 4, 8, 9, 10, 11
D.9, 10, 11, 12

3.2 SQL基本语法

下列 SQL 语句，哪一项不合法？D
A.select * from employee where dept = 'hr';
B.select 3.14;
C.select now();
D.from test select abc;

3.3 SQL、DML和DDL

## 插入
​
现有一个表：
create table book(
    id serial primary key ,
    title text not null ,
    meta jsonb default '{}'::jsonb,
    price money,
    isbn text not null ,
    publish_at date not null 
);
create unique index on book(isbn);
create index on book using gin(meta);
​
那么下列哪个选项的代码可以执行成功？B
​
A.
insert into book(title, price, isbn, publish_at) select 'a book title', 'unknown', 'xx-xxxx-xxxx', '2019-12-1'::date;
insert into book(title, price, isbn, publish_at) select 'a other book title', 'unknown', 'xx-xxxx-xxxx', '2019-12-1'::date;
​
B.
insert into book(title, price, isbn, publish_at) select 'a book title', 25.4, 'xx-xxxx-xxxx', '2019-12-1'::date;
insert into book(title, price, isbn, publish_at) select 'a other book title', 25.4, 'yy-yyyy-xxxx', '2019-12-1'::date; 
​
C.
insert into book(price, isbn, publish_at) select 25.4, 'xx-xxxx-xxxx', '2019-12-1'::date;
insert into book(price, isbn, publish_at) select 35.4, 'yy-yyyy-xxxx', '2019-12-1'::date;
​
D.
insert into book(title, price, isbn, publish_at) select 'a book title', 25.4, 'xx-xxxx-xxxx', '2019-12-1'::date;
insert into book(title, price, isbn, publish_at) select 'a other book title', 35.4, 'xx-xxxx-xxxx', '2019-12-1'::date; 
​
## 更新数据
​
现有 employee 表如下：
create table employee
(
    id serial primary key,
    name text,
    dept text,
    salary money
);
​
我们希望修改销售部(dept 字段为 sale)员工 Dora Muk 的工资，将其增加 1000，返回她的工号。
正确的修改语句是：C
​
A.update employee set salary = salary + 1000 where dept = 'sale' and name = 'Dora Muk';
B.update employee set salary += 1000 returning id;
C.update employee set salary = salary + 1000 where dept = 'sale' and name = 'Dora Muk' returning id;
D.update employee set salary = salary + 1000 returning id;
​
## 删除
SmartMarket 公司的业务数据库中，有一个 orders 表，其结构主要是以下形态：
create table orders
(
    id         serial primary key,
    meta       jsonb     default '{}'::jsonb,
    content    jsonb     default '{}'::jsonb,
    created_at timestamp default now(),
    deal       boolean
)
​
有一个业务系统会实时的将已经成交（deal 字段为 true）的订单数据转储，现在我们仅需要一个清理 程序，
将已经成 交的数据从 orders 表删除并记录被删除的数据id。
下面哪个操作是对的？
​
A.
在一个独立的定时任务中执行
delete
from orders;
​
B.
在一个独立的定时任务中执行
delete
from orders
where deal
returning id;
并记录id
​
C.
在一个独立的定时任务中执行
delete
from orders
where deal;
并记录操作前后表中的最大 id
​
D.
在一个独立的定时任务中执行
drop table orders;
create table orders
(
    id         serial primary key,
    meta       jsonb     default '{}'::jsonb,
    content    jsonb     default '{}'::jsonb,
    created_at timestamp default now(),
    deal       boolean
);
​
## 基本概念
​
下列叙述中，正确的是:D
1.带有 select 关键字的查询不会修改数据
2.查询表需要 select 权限
3.修改一个数据库表或对象的结构，通常语句中会出现 create、add、drop、alter 等关键字
4.修改数据内容，通常会出现update、insert、delete等关键字
5.update、insert、delete可以单独授权
​
A.1, 2, 3, 4, 5
B.3, 4, 5
C.4, 5
D.2, 3, 4, 5
​
注解：
带有 select 关键字的查询不会修改数据是错误的，其它的都是正确的。

4.基本结构

4.1 表

## 表的基本结构
​
关于数据表的介绍，哪一句是对的？ A
​
A.表中每一行记录的结构都要遵循表定义。
B.每个表只能有一个索引
C.表的主键必须是自增的整数 id
D.表一定要有主键
​
## 建表语句
现在我们尝试建立一个简化的交易流水表 trade，需要一个自增主键，一个content字段保存订单详情,
需要有一个时间戳字段记录订单入库时间，那么哪一个语句是对的？D
A.
create table trade (
    id serial primary key,
    content text,
    created_at timestamp
);
​
B.
create table trade (
    id integer primary key,
    content text,
    created_at timestamp default now()
);
​
C.
create table trade (
    id serial,
    content text,
    created_at timestamp default now()
);
​
D.
create table trade (
    id serial primary key,
    content text,
    created_at timestamp default now()
);
​
## 自增序列
PostgreSQL 表中的自增列 serial 的底层实现机制是：C
​
A.系统表
B.Serial 文件
C.绑定 sequence 对象的表达式默认值
D.auto increment 计数器
​
​
注解：
赋值serial伪类型给id列，PostgreSQL将执行下列步骤：
建立序列对象并设置下一个生成值做为列的缺省值。
给对应列增长NOT NULL约束，由于序列老是生成一个整数值，不能为null值。
赋值序列的拥有者给id列，所以当id列或表被删除时，序列对象自动被删除。

4.2 函数

关于 PostgreSQL 函数，错误的是：C
A.函数的参数和返回值可以是简单变量，也可以是结果集或自定义类型
B.函数可以用 SQL 写，也可以用 PLPGSQL，还可以用 Python、Perl、LUA等语言。
C.函数必须是无副作用的
D.函数之间可以互相引用
​
注解：
函数副作用 指当调用函数时，除了返回函数值之外，还对主调用函数产生附加的影响。
例如修改全局变量（函数外的变量）或修改参数。
函数副作用会给程序设计带来不必要的麻烦，给程序带来十分难以查找的错误，
并且降低程序的可读性。严格的函数式语言要求函数必须无副作用。

4.3 用户和权限

## 授权
​
管理员要给用户 fred 授权，允许他查询 emplyee 表，应用哪一条语句？ A
A.grant select on table employee to fred;
B.grant all on table employee to fred;
C.grant query on table employee to fred;
D.grant select on table employee of fred;
​
​
## 撤销权限
数据组的 Fred 调到了研发团队，不再参与分析生产数据，现在管理员要收回他对 trade 表的查询权限，
假设这个权限是授予他本人的数据库用户fred ，下面哪个操作是对的？D
​
A.revoke select on trade to fred;
B.grant not select on trade to fred;
C.revoke owned trade from fred;
D.revoke select on trade from fred;
​
## 角色
你是 rental dvd 公司的数据库管理员，公司数据分析组有 Fred、Alice、James、Jone 四位成员，
现在你需要给数据分析组授权，允许他们 查询 trade 数据库的 public schema 中的所有表，规范的操作应该是 B
​
A.
grant select on all tables in schema public to fred, alice, james, jone;
​
B.
create role analysis;
grant analysis to fred, alice, james, jone;
grant select on all tables in schema public to analysis;
​
C.
create role analysis;
grant analysis to fred, alice, james, jone;
grant all on all tables in schema public to analysis;
​
D.
create role analysis;
grant analysis to fred, alice, james, jone;
grant select on all to analysis;

4.4 索引和约束

## 主键
​
关于 PostgreSQL 的主键，哪一项是错误的？D
A.语法约束上，可以允许无主键的表，但是从工程实践上，应该保持每个表都有正确的主键。
B.主键可以是一个字段，也可以是多个字段的组合
C.主键隐含了聚集索引和唯一约束
D.主键列只能是自增 id。
​
## 唯一约束
现有一个图书登记表：
create table book(
    id serial primary key ,
    title text,
    publish_at date,
    isbn text,
    meta jsonb default '{}'::jsonb
)
​
我们发现有时候客户可能会重复输入同一本书的信息，怎样约束用户不会输入同一本书？
​
1.删除id列，将isbn设置为主键
2.在 isbn 列上加唯一约束
3.执行 create index on book(id, title, publish_at, isbn, meta)
4.在 id 键上加唯一约束
​
A.3
B.4
C.3 和 4
D.1 或者 2

4.4 触发器

SmartMarket 公司的OA数据库中包含以下结构：
​
create table employee(
    id serial primary key ,
    name text,
    dept text,
    salary money,
    meta jsonb default '{}'::jsonb
);
​
create table budget(
    id serial primary key ,
    dept text,
    amount money
)
​
我们省略了无关的内容。当某个员工的工资发生变动时，我们要修改他所在部门的预算。
那么以下哪个选项可以解决问题？D
​
A.将预算总额字段变成计算列，通过统计员工工资生成。
B.在 budget 表添加触发器，当员工信息变动时，重算相关部门的预算。
C.将员工信息表的工资字段设置为部门预算总额的外键引用字段，并设置级联更新。
D.在 employee 表添加一个 after 触发器，当员工信息变动时，重算相关部门的预算。

4.5 数据库扩展

## 过程语言
​
关于 PostgreSQL PL 语言，错误的是：B
A.PG 可以支持 Python、Perl、Lua 等多种 PL 语言编写函数。
B.要安装新的扩展语言，需要重新编译 PostgreSQL 内核。
C.实现新的 PL 语言需要遵循 PostgreSQL 的语言扩展规范。
D.通过 create language 可以安装新的 pl 语言支持
​
## 外部扩展
​
下列哪个功能需要通过create exension语句安装扩展得到？D
A.面向对象语法
B.JSON Path 支持
C.XML 和 XSLT 支持
D.postgis
​
注解：
PostGIS是对象－关系型数据库系统PostgreSQL的一个扩展，它的出现让人们开始重视基于数据库管理系统的空间扩展方式，
而且使PostGIS有望成为今后管理空间数据的主流技术

\