PostgreSQL学习记录下

数据定义

表基础

关系型数据库中的一个表非常像纸上的一张表:由行和列组成.
整数:integer,分数/金钱:numeric,字符串:text,日期:datetimestamp

创建一个表,终端如下所示:
mydb=# create  table my_first_table(
mydb(# first_column text,
mydb(# second_column integer
mydb(# );
CREATE TABLE
查询表,终端如下所示:
mydb=# select * from pg_tables where schemaname = 'public';
schemaname |   tablename    | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+----------------+------------+------------+------------+----------+-------------+-------------
public     | cities         | root       |            | t          | f        | t           | f
public     | weather        | root       |            | f          | f        | t           | f
public     | arr            | root       |            | f          | f        | f           | f
public     | my_first_table | root       |            | f          | f        | f           | f
(4 rows)
删除表,终端如下所示:
mydb=# drop table my_first_table ;
DROP TABLE

默认值

一个列可以被分配一个默认值.
1.使用 DEFAULT 关键字在建表的时候设置
2.还可以为一个timestamp 列指定默认值为 CURRENT_TIMESTAMP,这样得到行被插入的时间
3.每一行生成一个序列号 SERIAL,

create table products (
    product_no SERIAL,
    name        text,
    create_at   timestamp default current_timestamp
);
数据插入自带默认值,终端如下所示:
mydb=# insert into products (name) values ('hello');
INSERT 0 1
mydb=# select * from products;
product_no | name  |         create_at
------------+-------+----------------------------
        1 | hello | 2020-11-04 01:50:16.154968
(1 row)
mydb=# insert into products (name) values ('jak'),('tom'),('sery');
INSERT 0 3
mydb=# select * from products;
product_no | name  |         create_at
------------+-------+----------------------------
        1 | hello | 2020-11-04 01:50:16.154968
        2 | jak   | 2020-11-04 01:51:53.141607
        3 | tom   | 2020-11-04 01:51:53.141607
        4 | sery  | 2020-11-04 01:51:53.141607
(4 rows)

生成列

生成的列是一个特殊的列,它总是从其他列计算而来.因此说,它对于列就像视图对于表一样.

生成列有两种: 存储列 和 虚拟列.

解释:存储列在写入(插入或更新)时计算,并且像普通列一样占用存储空间.

解释:虚拟列不占用存储空间并且在读取时进行计算.

注意:生成列目前PostgreSQL只是想了存储生成列.

从概念上讲,生成列在before触发器运行后更新.因此,before触发器中的基础列所做的变更将反映在生成列中.

但相反,不允许访问before触发器中的生成列.

建立一个生成列,在create table 中使用 generated always as 子句,且必须指定关键字STORED
```
create table people (
    height_cm numeric,
    height_in numeric generated always as (height_cm / 2) stored
);
插入数据并读取时,终端显示如下:
mydb=# insert into people (height_cm) values (3.2),(5.01),(7.22222);
INSERT 0 3
mydb=# select * from people
mydb-# ;
height_cm |     height_in
-----------+--------------------
    3.2 | 1.6000000000000000
    5.01 | 2.5050000000000000
7.22222 | 3.6111100000000000
(3 rows)
```

约束

数据类型是一种限制能够存储在表中数据类别的方法.

检查约束

一个检查约束是最普通的约束类型.它允许我们指定一个特定列中的值必须要满足一个布尔表达式;
关键字 check (表达式)

  修改product表,增加price字段,并增加价格检查约束要大于0;终端显示如下:
  mydb=# alter table products add column price numeric check (price > 0);
  ALTER TABLE
  mydb=# \d products;
                                              Table "public.products"
  Column   |            Type             | Collation | Nullable |                   Default
  ------------+-----------------------------+-----------+----------+----------------------------------------------
  product_no | integer                     |           | not null | nextval('products_product_no_seq'::regclass)
  name       | text                        |           |          |
  create_at  | timestamp without time zone |           |          | CURRENT_TIMESTAMP
  price      | numeric                     |           |          |
  Check constraints:
      "products_price_check" CHECK (price > 0::numeric)
  对价格进行修改成负数,看看报错信息,终端显示如下:
  mydb=# select * from products;
  product_no | name  |         create_at          | price
  ------------+-------+----------------------------+-------
          1 | hello | 2020-11-04 01:50:16.154968 |
          2 | jak   | 2020-11-04 01:51:53.141607 |
          3 | tom   | 2020-11-04 01:51:53.141607 |
          4 | sery  | 2020-11-04 01:51:53.141607 |
  (4 rows)
  mydb=# update products set price = '99.9' where name = 'hello';
  UPDATE 1
  mydb=# update products set price = '-99.9' where name = 'hello';
  ERROR:  new row for relation "products" violates check constraint "products_price_check"
  DETAIL:  Failing row contains (1, hello, 2020-11-04 01:50:16.154968, -99.9).
  同时可以对两个价格进行大小比对,还可以使用CONSTRAINT指定一个命名的约束.

非空约束

一个非空约束仅仅指定一个列中不会有空值.
NOT NULL 类似于创建一个检查约束 check (column_name is not null)
PostgreSQL中创建一个显式的非空约束更高效.
唯一约束

唯一约束保证在一列中或者一组列中保存的数据在表中所有行间都是唯一的
UNIQUE 关键字,字段后面加 unique 或者建表时批量增加 unique(a,b,c)
主键
一个主键约束表示可以用作表中行的唯一标识符的一个列或者一组列.这要求那些只都是唯一的并且非空.
PRIMARY KEY 相当于 UNIQUE NOT NULL
外键
一个外键约束指定一列(或一组列)中的值必须匹配出现另一个表中某些行的值.我们说这维持了两个关联表之间的引用完整性.
REFERENCES 被引用表名 (被引用表中的字段名称)
实际上就是一个引用与被引用的关系.保证在一个产品在被订单引用后,不会存在订单中的产品丢失的情况.

排他约束
排他约束保证如果将任何两行的指定列或表达式使用指定操作符进行比较,至少其中一个操作符比较将会返回否或空值.

  创建一个表,如果满足(name 相同,age 不相同)则不允许插入,表达式(name with =,age with <>)为true则不能插入
  create table test1(
      name text,
      age int,
      exclude using gist (name with =,age with <>)
  );
  使用排他约束前必须要引入 btree_gist扩展,终端报错如下:
  mydb=#  create table test1(
  mydb(#             name text,
  mydb(#             age int,
  mydb(#             exclude using gist (name with =,age with <>)
  mydb(#         );
  ERROR:  data type text has no default operator class for access method "gist"
  HINT:  You must specify an operator class for the index or define a default operator class for the data type.
  引入btree_gist扩展,终端显示如下:
  mydb=# CREATE EXTENSION btree_gist;
  CREATE EXTENSION
  然后再次创建表,并进行数据插入测试,终端显示如下:
  mydb=# insert into test1 values ('小明',18);
  INSERT 0 1
  mydb=# insert into test1 values ('小明',18);
  INSERT 0 1
  mydb=# insert into test1 values ('小明',18);
  INSERT 0 1
  mydb=# select * from test1 ;
  name | age
  ------+-----
  小明 |  18
  小明 |  18
  小明 |  18
  (3 rows)
  mydb=# insert into test1 values ('小明',28);
  ERROR:  conflicting key value violates exclusion constraint "test1_name_age_excl"
  DETAIL:  Key (name, age)=(小明, 28) conflicts with existing key (name, age)=(小明, 18).

系统列
每一个表都拥有一些由系统隐式定义的system columns.因此,这些列的名字不能像用户定义的列一样使用.

  tableoid 包含这一行的表的OID.
  xmin    插入该行版本的事务身份(事务ID)
  cmin    插入事务中的命令标识符(从0开始)
  xmax    删除事务的身份(事务ID)
  cmax    删除事务中的命令标识符,或者为0
  ctid    行版本在其表中的物理位置.

修改表

增加列

增加一个列
alter table 表名 add column 字段名 字段类型属性等;

移除列

移除一个列
alter table 表名 drop column 字段名;
如果当前的列被另一个表的外键所引用,则可以使用 cascade 关键字 授权移除任何依赖的所有东西
alter table 表名 drop column 字段名 cascade;

增加约束

alter table 表名 add check (约束表达式); // 增加约束
alter table 表名 add constraint 命名 unique (字段名); // 增加唯一约束
alter table 表名 add foreign key (字段名) references 外键表名 (外键表字段名); // 增加外键
alter table 表名 add column 字段名 属性 ; // 增加一个列字段

移除约束
移除约束首先需要知道它的名称.如果创建时给定了名称,则删除很容易.否则需要找出系统生成的名称.
psql交互命令中是用 \d 表名.会查询表详情.
alter table 表名 drop constraint 约束名.

更改列的默认值

alter table 表名 alter column 字段名 set default 对应的值;

修改列的数据类型

alter table 表名 alter column 字段名

重命名列

alter table 表名 rename column 旧字段名 to 新字段名;

重命名表
```
alter table 表名 rename to 新表名;
```

权限
一个对象被创建后的所有者(或者超级用户)能够对该对象做任何事情.为了允许其他角色使用,需要分配权限.

  权限分为: select(查询),insert(插入),update(更新),delete(删除),truncate(截断),references(外键),trigger(触发器),create(创建),connect(连接),temporary(临时表),usage(创建语言函数).
  重新分配所有者
  alter table 表名 owner to 用户名;
  分配权限命令:
  GRANT,示例:将一个已有角色的joe,对一个已有表accounts进行授权.
  grant update on 表名 to 角色;
  ALL 代表所有权限
  撤销权限,使用revoke 命令:
  REVOKE ALL ON 表名 FROM 角色;

行安全性策略
创建策略 create policy 命令
修改策略 alter policy 命令
删除策略 drop policy 命令
模式
- 创建模式
  create schema myschema;
  在一个新模式中创建一个表: create table myschema.mytable(…);
  删除一个空的模式: drop schema myschema;
  删除一个不为空的模式和包含的所有对象: drop schema myschema cascade;
  创建一个模式并让其他人拥有: create schema schema_name authorization user_name;
- 公共模式
  没有指定模式的都默认public模式
- 模式搜索路劲
  显示当前搜索路劲,命令: show search_path;
  将新模式放在搜索路劲中: set search_path to myschema,public;
- 权限和模式
  默认情况下,用户不能范文不属于他们的方案中的任何对象.要允许这种情况,模式的拥有者必须在该模式上授予usage权限.
- 系统目录模式
  除了public和用户自建的模式外,每一个数据库还包含一个pg_catalog模式,它包含了系统表和所有内建的数据类型/函数以及操作符.
- 使用模式
  模式能够以多种方式组织数据,secure schema usage pattern防止不受信任的用户更改其他用户查询的行为.
- 可移植性
  在SQL标准中,在由不同用户拥有的同一个模式中的对象是不存在的.
继承
建表时使用关键字 inherits (父表名称)
表分区
- 概述
  划分指的是将逻辑上的一个大表分成一些小的物理上的片.划分有很多益处.
  - 在某些情况下查询性能能够显著提升,特别是访问压力大的行在一个分区或者少数几个分区时.划分可以取代索引的主导列,减小索引尺寸以及使索引中访问压力大的部分可能放在内存中.
  - 当查询或更新访问一个分区的大部分行时,顺序扫描取代分散到整个表上的索引和随机访问,改善性能.
  - 如果批量操作的需求是在分区设计时就规划好的,则批量装载和删除可以通过增加或去除分区来完成.
    执行 alter table detach partition 或者 drop table删除一个分区远快于批量操作.同时也避免了批量delete导致的vacuum开销
  - 很少使用的数据可以被迁移到便宜且较慢的存储介质上.
```
PostgreSQL内建分区支持:
范围划分:
  表被根据一个关键列或一组列划分为"范围",不同的分区的范围之间没有重叠.例如根据日期范围划分,或者根据特定业务对象的标识符划分.
列表划分:
  通过显式地列出每个分区中出现的键值来划分表.
哈希分区:
  通过为每个分区指定模数和余数来对表进行分区.
```
- 声明式划分
  就是利用时间范围进行分区,官方建议增加脚本维护DDL
- 使用继承实现
  就是利用继承进行分区
- 分区剪枝
  分区剪枝是一种提升声明式分区表性能的查询优化技术.
  SET enable_partition_pruning = on;
  如果关闭则全表扫描
- 分区和约束排除
  约束排除是一种与分区剪枝类似的查询优化技术.虽然主要被用于使用传统继承方法实现的分区上,但它也可以使用声明式分区.
外部数据
PostgreSQL实现了部分的SQL/MED规定,允许我们使用普通SQL查询来访问位于PostgreSQL之外的数据.这种数据被称为外部数据.
可以在一个外部数据包装器的帮助下访问,就是一个库.
其他数据库对象
表是一个关系型数据库结果的核心对象.还有视图,函数,过程和操作符,数据类型和域,触发器和重写规则
依赖跟踪

例如有外键被引用的数据无法被删除,可以使用 cascade 进行跟踪删除

数据操纵

插入数据

insert into 表名 ('字段名1','字段名2') values ('值1','值2'),('值1-1','值2-2')...;

更新数据

update 表名 set 字段名 = '字段值' where 条件字段 = '条件值';

删除数据

delete from 表名 where  条件字段 = '条件值';

从修改的行中返回数据

命令 returning 子句. insert , update , delete 命令都支持这个可选的子句.
终端显示如下:
mydb=# insert into products (name) values ('k1'),('k2') returning product_no;
product_no
------------
        5
        6
(2 rows)
返回所有信息,终端显示如下:
mydb=# insert into products (name) values ('k1'),('k2') returning *;
product_no | name |         create_at          | price | dis_price
------------+------+----------------------------+-------+-----------
        7 | k1   | 2020-11-04 08:28:40.681002 |       |
        8 | k2   | 2020-11-04 08:28:40.681002 |       |
(2 rows)
INSERT 0 2

查询

从数据库检索数据的过程或命令叫做查询.在SQL 里 select命令用于指定查询.
语法:
[WITH with_queries] SELECT select_list FROM table_expression [sort_specification]
- 表表达式
  
  表表达式计算一个表.包含一个FROM子句,可以根据需要选用where,group by和having子句
  - FROM 子句
    
    从一个用逗号分隔的表引用列表中的一个或者更多其它表中生成一个表.
  - 连接表
    
    一个连接表是根据特定的连接类型的规则从两个其他表(真实表或生成表)中派生的表.
    连接类型
```
  inner join
  left outer join
  right outer join
  full outer join
```
  - 表和列别名
    
    表名 as 表别名, 列名 as 列别名
  - 子查询
  - 表函数
    
    是那些生成一个行集合的函数,这个集合可以是由基本数据类型组成,也可以由复合类型组成.
  - lateral子查询
- where子句
  
  where search_condition
- group by和having子句
  
  在having子句中的表达式可以引用分组的表达式和未分组的表达式
- grouping sets,cube,rollup
  
  使用分组集的概念可以实现比上述更加复杂的分组操作.
- 窗口函数处理
- 选择列表项
  
  最简单的就是 * 全部,如果有多个同名的表字段,需要指定别名
- 列标签
  
  select a as value, b + c as sum from ….
  没有使用as则输出空列
- DISTINCT
  在处理完选择列表之后,结果表可以可选的删除重复行.可以直接在select 后面写上 DISTINCT关键字指定
- 组合查询
  
  查询结果可以集合操作并,交,差进行组合.
  union all , intersect all , except all
- 行排序
  
  order by
- limit和offset
  
  分页检索
- values列表
- with查询(公共表达式)
  - with中的select
  - with中的数据修改语句
数据类型
- 数字类型
- 整数类型
- 任意精度数字
- 浮点类型
- 序数类型
货币类型

money,8bytes
字符串类型
二进制数据类型
- 日期/时间类型
- 日期/时间输入
- 时区
- 间隔输入
- 间隔输出
布尔类型

真 true,yes,on,1,假 false,no,off,0
枚举类型
- 枚举的声明
  枚举(enum)
```
create type mood as ENUM('sad','ok','happy');
create table person(
    name text,
    current_mood mood
);
```
- 排序
  
  一个枚举类型的值的排序是该类型被创建时所列出的值的顺序.枚举类型的所有标准的比较操作符以及相关聚集函数都被支持.
- 类型安全性
  
  每一种枚举数据类型都是独立的并且不能和其他枚举类型相比较.
集合类型

集合数据类型标识二维的空间物体.
- 点点是几何类型的基本二维构造块.
- 线
- 线段用一对线段的端点来标识
- 方框
- 路劲
- 多边形
- 圆
网络地址类型

cidr和inetIPv4和IPv6网络
macaddr6字节MAC地址,macaddr88bytesMAC地址(EUI-64格式)
位串类型

位串就是一串1和0的串.他们可以用于存储和可视化位掩码.
文本搜索类型

postgreSQL提供两种数据类型,他们被设计用来支持全文搜索,全文搜索是一种在自然语言的文档集合中搜索以定位那些最匹配一个查询的文档的活动.
tsvector类型表示一个为文本搜索优化的形式下的文档.
tsquery类型表示一个文本查询.
uuid类型

数据类型UUID存储由RFC 4122,ISO/IEC 9834-8:2005以及相关标准定义的通用唯一标识符.
XML类型
JSON类型
数组
组合类型

孤岛渔夫的博客

PostgreSQL学习记录下

PostgreSQL学习记录下

评论 0

发表评论

点击排行

标签云

友情链接

关注