SQL: Data Definition

20th August 2020 at 2:19pm
SQL

Default Value 重要

CREATE TABLE products (
    product_no SERIAL,
    name text,
    price numeric DEFAULT 9.99
);

SERIAL 是 PostgreSQL 的自增 integer。DEFAULT 关键字指定了默认值;如果没有 DEFAULTINSERT INTO 如果不给值,列的默认值为 null。

Generated Columns 不重要

一个字段是根据另一字段计算而来。感觉不实用。

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

Constraint 重要

See SQL: Data Definition: Constraint.

System Columns 不重要

有部分名字被系统所保留,不能作为列名。具体看 文档

Modifying Tables 重要

没有多少需要理解的内容。具体操作方法看 文档

Privileges 一般

数据库系统,可以针对 object(如表、库、函数等)做权限控制。比如可以使你可以读写某一个表,但是其他人只能读。

PG 提供了一个 特殊的角色 PUBLIC。对 PUBLIC 授权的能力,会对所有角色都可用。

具体的命令、可以控制的粒度,看 PG 文档。这里不做细化。

Row Security Policies 不重要

不重要,略。

Schemas 重要

Cluster > Database > Schema > Table

每一个 PG 的 cluster(一个或者多个 server 组成的 PG 服务),包含多个 database。每个 database 可以有多个 schema,也都包含默认的 public schema。Schema 中包含 tables。

一个客户端(psql、client library 等)一次只能访问一个 database。

具体的 SQL 语句,看 PG 文档

Search Path 不重要

一般在写 SQL 或者写程序时,你需要使用 schema.table 这种方式来指定一张表,如 ecomerce.products。如果没有带 schema 名,则默认先搜同用户名的 schema,再搜 public。SHOW search_path; 可以看到搜索路径:

 search_path
--------------
 "$user", public

比如对于 SELECT * FROM product,如果你连接上数据库的用户名是 joe,数据库会先尝试搜 joe.product;没有的话再找 public.product

Inheritance 不重要

PG 提供了 table 继承的能力。感觉不实用。略过。

Table Partitioning 一般

See SQL: Data Definition: Table Partitioning.

Foreign Data / Other Database Objects 不重要

PG 的这两节文档没有看,不太重要。

Dependency Tracking 一般

数据库中各种 object 会互相依赖,比如某个表可能被另外一个表中的外键所依赖:

DROP TABLE products;

ERROR:  cannot drop table products because other objects depend on it
DETAIL:  constraint orders_product_no_fkey on table orders depends on table products
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

你可以给 DROP 语句加上 CASCADE,这样会把上面例子 DETAIL 中的描述的对象全部删掉。在这个例子中会删掉 orders_product_no_fkey 这个外键约束:

DROP TABLE products CASCADE;

TOC