高效使用Greenplum：入门、进阶与数据中台
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

4.2　数据表的基本使用

数据表是基于数据类型的自由组合，可以包含多种不同的数据类型。数据类型确定一列值的类型，多个列组合起来就是数据表。数据表一般包含多条记录，也叫行记录。

4.2.1　表对象定义

Greenplum使用CREATE TABLE语句来创建数据库表格。CREATE TABLE语法格式如下。

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( 一个或多个列 ));

CREATE TABLE是一个关键词，用于告诉数据库系统将创建一个数据表。表名不分大小写，且不能与同一模式中的其他表、序列、索引、视图或外部表等对象同名。

CREATE TABLE在当前数据库中创建一个新的空白表，该表将由发出此命令的用户拥有。表格中的每个字段都会定义数据类型，如下所示。

--以下创建一个表，表名为COMPANY，主键为ID，NOT NULL表示字段不允许包含NULL值
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL);

我们再创建一个表格，在后面章节会用到。

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL);

我们可以使用\d命令查看表格是否创建成功，如下所示。

testdb=# \d
           List of relations
 Schema |    Name    | Type  |  Owner   --------+------------+-------+----------
 public | company    | table | postgres
 public | department | table | postgres(2 rows)

使用\d tablename查看表格信息，如下所示。

testdb=# \d company
                  Table "public.company"
 Column  |     Type      | Collation | Nullable | Default ---------+---------------+-----------+----------+---------
 id      | integer       |           | not null | 
 name    | text          |           | not null | 
 age     | integer       |           | not null | 
 address | character(50) |           |          | 
 salary  | real          |           |          | Indexes:
    "company_pkey" PRIMARY KEY, btree (id)

4.2.2　表的基本操作

在Greenplum中，ALTER TABLE命令用于添加、修改、删除一张已经存在表的列，也可以用ALTER TABLE命令添加或删除约束。

用ALTER TABLE命令在一张已存在的表上添加列的语法如下。

ALTER TABLE table_name ADD column_name datatype;

在一张已存在的表上删除列，语法如下。

ALTER TABLE table_name DROP COLUMN column_name;

修改表中某列的数据类型，语法如下。

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

给表中某列添加NOT NULL约束，语法如下。

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

给表某列添加UNIQUE约束，语法如下。

ALTER TABLE table_name
ADD CONSTRAINT Unique_tablename UNIQUE(column1, column2...);

给表添加CHECK约束，语法如下。

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

给表添加主键，语法如下。

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

删除约束，语法如下。

ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;

使用CREATE TABLE AS命令查询建表，语法如下。

CREATE TABLE table_name AS 
SELECT * FROM XX WHERE xx 
DISTRIBUTE BY(colname);

使用SELECT INTO命令查询建表，语法如下。

SELECT * INTO new_table FROM old_table
WHERE xx

使用LIKE命令复制表结构后建表，默认情况下分布键和原来的表一致，语句如下。

CREATE TABLE new_table (LIKE old_table);

注意

虽然CREATE TABLE AS和SELECT INTO命令有一样的效果，但是SELECT INTO不能手动指定分布键，数据库会按照查询的结果默认分布。

4.2.3　数据的基本操作

1. INSERT

Greenplum中的INSERT INTO语句用于向表中插入新记录，我们可以只插入一行，也可以同时插入多行。

INSERT INTO语句的语法格式如下。

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value11, value12, value13,...value1N),(value21, value22, value23,...
    value2N),....;

其中column1、column2、…、columnN为表中字段名，value1、value2、…、valueN为字段对应的值。

在使用INSERT INTO语句时，字段列必须和数据值数量相同，且顺序也要对应。如果我们向表中的所有字段插入值，则可以不需要指定字段，指定插入的值即可。

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

2. DELETE

使用DELETE语句可以删除Greenplum表中的数据。以下是使用DELETE语句删除数据的通用语法。

DELETE FROM table_name WHERE [condition];

一般我们需要在WHERE子句中指定条件来删除对应的记录，可以使用AND或OR运算符来指定一个或多个条件语句。如果没有指定WHERE子句，Greenplum表中的所有记录都将被删除。

注意

频繁地删除和更新会造成表存储内容不连续，导致较多碎片影响表的查询性能。VACUUM操作用于释放、再利用更新/删除行所占据的磁盘空间。

3. UPDATE

UPDATE语句用于更新Greenplum数据库中的数据，以下是使用UPDATE语句修改数据的通用SQL语法。

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

使用UPDATE语句可以同时更新一个或多个字段，也可以在WHERE子句中指定任何条件。

更高级一点的用法是，我们可以用UPDATE实现类似Oracle merge的效果，即利用table_b表的数据去更新table_a表符合条件的值。需要注意的是，根据条件得到的table_b表必须是唯一的一条记录。

UPDATE table_a a
SET column1 = b.column1,
    column2 = b.column2
FROM table_b b
WHERE [condition];

4. TRUNCATE

Greenplum中的TRUNCATE TABLE用于删除表的数据，不会删除表结构。如果用DROP TABLE命令删除表，则会连表的结构一起删除。如果想插入数据，则需要重新建立表。

虽然TRUNCATE TABLE和DELETE具有相同的效果，但是由于TRUNCATE实际上并不扫描表，因此执行速度更快。此外，TRUNCATE TABLE命令可以立即释放表空间，无须后续执行VACUUM操作，这一点在大型表上非常有用。

TRUNCATE TABLE的基础语法如下。

TRUNCATE TABLE  table_name;
上一章目录下一章