鲁楠
发布于

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=# \l
List of databases
Name | Owner | Encoding | Access privileges
-----------+---------+----------+-------------------
postgres | gpadmin | UTF8 |
template0 | gpadmin | UTF8 |
template1 | gpadmin | UTF8 |
(3 rows)

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

列出数据库
postgres=# \l
List of databases
Name | Owner | Encoding | Access privileges
-----------+---------+----------+-------------------
postgres | gpadmin | UTF8 |
template0 | gpadmin | UTF8 |
template1 | gpadmin | UTF8 |
testdb | gpadmin | UTF8 |
(4 rows)

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

datname
template1
template0
postgres
testdb
(4 rows)

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

列出数据库
postgres=# \l
List of databases
Name | Owner | Encoding | Access privileges
-----------+---------+----------+-------------------
postgres | gpadmin | UTF8 |
template0 | gpadmin | UTF8 |
template1 | gpadmin | UTF8 |
(3 rows)

列出数据库
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=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+------------------+-------+---------+-------------
public | ao_orc_lz4 | table | gpadmin | orc
public | ao_orc_nozip | table | gpadmin | orc
public | ao_orc_snappy | table | gpadmin | orc
public | ao_row_nozip | table | gpadmin | append only
public | ao_row_snappy | table | gpadmin | append only
public | ao_row_zlib | table | gpadmin | append only
public | constraint_table | table | gpadmin | orc
public | default_table | table | gpadmin | orc
public | magma_tb1 | table | gpadmin | magmaap
public | magma_tb2 | table | gpadmin | magmaap
public | magma_tb3 | table | gpadmin | magmaap
public | magma_tb4 | table | gpadmin | magmaap
(12 rows)

testdb=# \d default_table
Orc Table "public.default_table"
Column | Type | Modifiers
--------+---------+-----------
col1 | text |
col2 | integer |
Table Bucket Number: 8
Distributed randomly

testdb=# \d ao_row_nozip
Append-Only Table "public.ao_row_nozip"
Column | Type | Modifiers
--------+---------+-----------
col1 | text |
col2 | integer |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: f
Table Bucket Number: 8
Distributed randomly

testdb=# \d ao_row_snappy
Append-Only Table "public.ao_row_snappy"
Column | Type | Modifiers
--------+---------+-----------
col1 | text |
col2 | integer |
Compression Type: snappy
Compression Level: 0
Block Size: 32768
Checksum: f
Table Bucket Number: 8
Distributed randomly

testdb=# \d ao_row_zlib
Append-Only Table "public.ao_row_zlib"
Column | Type | Modifiers
--------+---------+-----------
col1 | text |
col2 | integer |
Compression Type: zlib
Compression Level: 1
Block Size: 32768
Checksum: f
Table Bucket Number: 8
Distributed randomly

testdb=# \d ao_orc_nozip
Orc Table "public.ao_orc_nozip"
Column | Type | Modifiers
--------+---------+-----------
col1 | text |
col2 | integer |
Table Bucket Number: 8
Distributed randomly

testdb=# \d ao_orc_lz4
Orc Table "public.ao_orc_lz4"
Column | Type | Modifiers
--------+---------+-----------
col1 | text |
col2 | integer |
Table Bucket Number: 8
Distributed randomly

testdb=# \d ao_orc_snappy
Orc Table "public.ao_orc_snappy"
Column | Type | Modifiers
--------+---------+-----------
col1 | text |
col2 | integer |
Table Bucket Number: 8
Distributed randomly

testdb=# \d magma_tb1
Magma Ap Table "public.magma_tb1"
Column | Type | Modifiers
--------+---------+-----------
col1 | text |
col2 | integer |
Table Bucket Number: 0
Distributed by: (col1)

testdb=# \d magma_tb2
Magma Ap Table "public.magma_tb2"
Column | Type | Modifiers
--------+---------+-----------
col1 | text | not null
col2 | integer |
Indexes:
"magma_tb2_pkey" PRIMARY KEY, btree (col1)
Table Bucket Number: 0
Distributed by: (col1)

testdb=# \d magma_tb3;
Magma Ap Table "public.magma_tb3"
Column | Type | Modifiers
--------+---------+-----------
col1 | text | not null
col2 | integer |
Check constraints:
"magma_tb3_col2_check" CHECK (col2 > 3)
Table Bucket Number: 0
Distributed by: (col1)

testdb=# \d magma_tb4;
Magma Ap Table "public.magma_tb4"
Column | Type | Modifiers
--------+---------+-----------
col1 | text | not null
col2 | integer | not null
Indexes:
"magma_tb4_pkey" PRIMARY KEY, btree (col1, col2)
Table Bucket Number: 0
Distributed by: (col1)

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 列组合,可被声明为唯一,适合所有数据类型,支持排序,支持等值和范围比较

评论
    test