发布于 2022-12-31 13:59:04

OushuDB学习笔记（用户指南）

四、用户指南

1、创建数据库
initdb 命令在初始化时创建 postgres
集群初始化时创建 template1
新数据库就是从 template1 复制，对 template1 的修改会传播到之后创建的所有库
库的所有者或者超级用户才能删除数据库
无法删除当前库，可用切换到其他数据库后再删除

常用命令
创建删除库
create database dbname;
create database dbname owner rolename;
drop database dbname;
createdb dbname
createdb -O rolename dbname
dropdb dbname
列出数据库
\l
select datname from pg_database;
切换数据库
\c dbname

登录数据库 postgres
[gpadmin@localhost bin]$ psql -d postgres
psql (8.2.15)
Type "help" for help.

创建数据库
postgres=# create database testdb;
CREATE DATABASE

列出数据库
postgres=# select datname from pg_database;

datname
template1
template0
postgres
testdb
(4 rows)

删除数据库
postgres=# drop database testdb;
DROP DATABASE

列出数据库
postgres=# select * from pg_database;
datname | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | dat2tablespace | datconfig | datacl
-----------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+----------------+-----------+--------
template1 | 10 | 6 | t | t | -1 | 0 | 899 | 1663 | 16385 | |
template0 | 10 | 6 | t | f | -1 | 0 | 899 | 1663 | 16385 | |
postgres | 10 | 6 | t | t | -1 | 0 | 899 | 1663 | 16385 | |
(3 rows)

2、创建数据表
表类型：
1）ao：行存，不支持新执行器，snappy/zlib，不支持更新删除，不支持索引
2）orc：行列混存，支持新执行器，snappy/lz4，支持更新删除，不支持索引
3）magma：行列混存，支持新执行器，自动选择无需指定，支持更新删除，支持索引
Magma 表不支持缺省值
约束：检查约束(字段约束、表约束，判断是真或 null），非空约束(not null)，主键约束(唯一且非空，只有 magma 支持，非定长列要在最后，支持多个字段)

常用命令
列出表
\dt
查看表结构
\d tblname
查看索引
\di
创建表
create table default_table(col1 text, col2 integer);
create table ao_row_nozip (col1 text, col2 integer)with(appendonly=true,orientation=row);
create table ao_row_snappy(col1 text, col2 integer)with(appendonly=true,orientation=row,compresstype=snappy);
create table ao_row_zlib(col1 text, col2 integer)with(appendonly=true,orientation=row,compresstype=zlib);
create table ao_orc_nozip (col1 text, col2 integer)with(appendonly=true,orientation=orc);
create table ao_orc_lz4 (col1 text, col2 integer)with(appendonly=true,orientation=orc,compresstype=lz4);
create table ao_orc_snappy(col1 text, col2 integer)with(appendonly=true,orientation=orc,compresstype=snappy);
create table magma_tb1 (col1 text, col2 integer) format 'Magmaap';
create table magma_tb2 (col1 text, col2 integer, primary key(col1)) format 'Magmaap';
create table magma_tb3 (col1 text not null, col2 integer check(col2>3)) format 'Magmaap';
create table magma_tb4 (col1 text, col2 integer, primary key(col1,col2)) format 'Magmaap';
删除表
drop table tblname
缺省值
time timestamp default now()
price numeric default 9.99
检查约束
create table constraint_table(
price numeric check(price > 0),
discounted_price numeric constraint positive_discounted_price check(discounted_price > 0),
constraint valid_discount check(price > discounted_price)
);

testdb=# \d constraint_table;
Orc Table "public.constraint_table"
Column | Type | Modifiers
------------------+---------------+-----------
price | numeric(10,0) |
discounted_price | numeric(10,0) |
Check constraints:
"constraint_table_price_check" CHECK (price > 0::numeric)
"positive_discounted_price" CHECK (discounted_price > 0::numeric)
"valid_discount" CHECK (price > discounted_price)
Table Bucket Number: 8
Distributed randomly

3、修改表
增加删除列
增加删除约束
修改缺省值
修改列数据类型
重命名列
重命名表

AO 表都支持
ORC 表只支持增加删除约束、重命名表
Magma 不支持任何操作

常用命令
增加列
alter table tbl add column col text default value check(col <> '');
删除列
alter table tbl drop column col;
增加约束
alter table tbl add check(col <> '');
alter table tbl alter column col set not null;
删除约束
alter table tbl drop constraint constraint_name;
alter table tbl alter column col drop not null;
改变缺省值
alter table tbl alter column col set default value;
alter table tbl alter column col drop default;
改变列数据类型
alter table tbl alter column col type numeric(10,2);
重命名列
alter table tbl rename column col1 to col2;
重命名表
alter table tbl1 rename to tbl2;

4、表分区
1）查询优化器可针对分区表进行优化，只扫描涉及到的分区；
2）如果按照日期分区，可以方便删除过期数据；

适用于：大型事实表，经常使用分区字段进行查询、维护数据，数据分布均匀，分区数不宜过多

支持 Range 分区（只允许一列）和 List 分区（允许多列）
Range 分区和 List 分区可以进行组合构成多级分区

创建 Range 分区
create table sales(id int, date date, amt decimal(10,2))
partition by range(date)
(start (date '2008-01-01') inclusive
end (date '2009-01-01') exclusive
every (interval '1 month'));
查看分区表
\d+ sales
指定子表名
partition jan08 start (date '2008-01-01') inclusive
默认分区
default partition extra

创建 List 分区
create table sales(id int, gender char(1))
partition by List(gender)
(partition girls values('F'),
partition boys values('M'),
default partition other);

创建多级分区
partition by range(logdate)
subpartition by list(city)
subpartition template
(subpartition beijing values('BJ'),
subpartition shanghai values('SH'),
default subpartition other)
(start (date '2020-01-01') inclusive
end (date '2020-02-01') exclusive
every (interval '1 month'))

partition by range(r_year)
subpartition by range(r_month)
subpartition template
(start (1) end (13) every(1),
default subpartition other_months)
(start (2017) end (2020) every (1),
default partition other_years)

partition by range(r_year)
subpartition by range(r_month)
subpartition template
(start (1) end (13) every(1),
default subpartition other_months)
subpartition by list(city)
subpartition template
(subpartition beijing values('BJ'),
subpartition shanghai values('SH'),
default subpartition other)
(start (2017) end (2020) every (1),
default partition other_years)

查看分区表的设计
select * from pg_partitions where tablename='sales'
查看用子分区模板创建的子分区信息
select * from pg_partition_templates where tablename = 'sales'
查看分区表的分区键列
select * from pg_partition_columns where tablename = 'sales'

5、数据操纵
orc:插入、更新、删除
magma:插入、更新、删除
ao:插入

不支持分布键和主键的 update 操作

6、数据查询
select * from
where between ... and ... --区间查询
and ... in ('...', '...')
and ... not in ('...', '...')
and ... like '_xxx%'
order by xx asc, xx desc
limit mm|all offset nn --跳过 nn 条取 mm 条或取 all 全部

表链接
内连接：join 或 inner join
on (a.id = b.id) 或 using(id)
using 会把两个表的 id 列并为一列

外连接：
左连接：left join 或 left outer join
a left join b，则 a 为主表 b 无法匹配的用 null 补齐
右连接：right join 或 right outer join
a right join b，则 b 为主表 a 无法匹配的用 null 补齐
全链接：full join 或 full outer join
保留两侧无法匹配的数据，字段用 null 补齐

交叉连接
t1 cross join t2
返回笛卡尔积，m 行*n 行

t1 natural .... join t2
using 缩写形式，用两个表的同名列作为 using 列表
如果没有同名列，则相当于 cross join

union all 和 union

7、数据类型
bigint 依赖于编译器支持 8 字节整数，如果机器不支持，则和 integer 表现一样但扔占用 8 字节
numeric，小数位超过定义的位数会自动四舍五入
char 定长类型，长度不足补空白，浪费存储空间
varchar 变长类型长度有限制，存储时有长度检查
text 变长类型长度无限制，优先选择

布尔类型
真：TRUE, 'yes', '1', 'true', 'y', 't'
假：FALSE，'no'，'0'，'false'，'n'，'f'
null 作为布尔型的空值

日期类型
data/time/time with time zone/timestamp/timestamp with time zone

8、函数与运算符
and 的优先级：False > Unknown(null) > True
or 的优先级：True > Unknown(null) > False

函数：数学、日期/时间、字符串、聚合、二进制字符、位串、集合、网络地址、系统信息、管理

9、索引
Magma 支持，ao/orc 不支持

支持主键索引、唯一索引、普通索引
支持单列索引、多列组合索引
支持多种数据类型任意组合
支持 b-tree 索引
不支持并发创建
不支持表达式索引、部分索引、覆盖索引
不支持 hash/GiST/SP-GiST/GIN/BRIN 类型索引
不支持指定 tablespace

默认 b-tree 索引
b-tree 支持最多 32 列组合，可被声明为唯一，适合所有数据类型，支持排序，支持等值和范围比较

OushuDB

浏览 (650) 点赞 (2) 收藏

test