DDL语句 - COLUMN - 《ZNBase 数据库技术文档》

COLUMN

COLUMN

我们把表中的每一行叫做一个“记录”，每一个记录包含这行中的所有信息，就像在通讯录数据库中某个人全部的信息，但记录在数据库中并没有专门的记录名，常常用它所在的行数表示这是第几个记录。字段（COLUMN）是比记录更小的单位，字段集合组成记录，每个字段描述文献的某一特征，即数据项，并有唯一的供计算机识别的字段标识符。

ADD COLUMN

ADD COLUMN 语句是表修改语句的一部分，向表中添加列。

语法格式

新增列的语法格式如下：

参数说明

column_name

前面一个view_name要重命名的视图的名称。要查找视图名称，请使用:SELECT * FROM information_schema.tables WHERE table_type = ‘VIEW’;后面一个view_name新视图的名称，该视图名称必须是其数据库唯一的，并且遵循这些标识符规则。

table_name

需要添加列的表。

typename

新列的数据类型。

col_qualification

列定义的可选列表，可能包括列级约束，排序规则或列族分配。需要注意的是，无法直接添加具有外键约束的列。你可以添加没有约束的列，然后使用CREATE INDEX索引列，再使用ADD CONSTRAINT将外键约束添加到列。

示例

示例1：添加单个列

ALTER TABLE re_users ADD COLUMN names STRING;

示例2：添加多列

ALTER TABLE re_users ADD COLUMN location STRING, ADD COLUMN LUMN amount DECIMAL;

示例3：添加具有默认值的非空列

ALTER TABLE re_users ADD COLUMN interest DECIMAL NOT NULL DEFAULT (DECIMAL ‘1.3’);

示例4：添加具有唯一值的非空列

ALTER TABLE re_users ADD COLUMN cust_number DECIMAL UNIQUE NOT NULL; > ALTER TABLE re_users ADD COLUMN more_names STRING COLLATE en;

示例5：添加列并将其分配给列族

添加列并将其分配给新列族

ALTER TABLE re_users ADD COLUMN location1 STRING CREATE FAMILY new_family;

添加列并将其分配给现有列族

ALTER TABLE re_users ADD COLUMN location2 STRING FAMILY existing_family;

如果列族不存在，添加列并创建新列族

ALTER TABLE re_users ADD COLUMN new_name STRING CREATE IF NOT EXISTS FAMILY f1;

SHOW COLUMNS

SHOW COLUMNS 语句用于显示一张表中列的详细信息，包括列的名字，类型，默认值及是否非空。

语法格式

显示所有列的语法格式如下：

参数说明

table_name

查看列所在的表名。

示例

示例1：显示列信息

CREATE TABLE re_users ( ID UUID NOT NULL DEFAULT gen_random_uuid (), city STRING NULL, first_name STRING NULL, last_name STRING NULL, full_name STRING NULL AS ( concat ( first_name, ‘ ‘, last_name )) STORED, address STRING NULL, credit_card STRING NULL, dl STRING NULL, NAMES STRING NULL, LOCATION STRING NULL, amount DECIMAL NULL, interest DECIMAL NOT NULL DEFAULT 1.3 ::: DECIMAL :: DECIMAL, cust_number DECIMAL NOT NULL, more_names STRING COLLATE en NULL, CONSTRAINT “primary” PRIMARY KEY ( ID ASC ), UNIQUE INDEX users_dl_key ( dl ASC ), INDEX re_users_city_idx_rename_test ( city ASC ), INDEX re_users_city_first_name_idx ( city ASC, first_name ASC ), UNIQUE INDEX re_users_credit_card_dl_key ( credit_card ASC, dl ASC ), UNIQUE INDEX re_users_cust_number_key ( cust_number ASC ), FAMILY “primary” ( ID, city, first_name, last_name, full_name, address, credit_card, dl, NAMES, LOCATION, amount, interest, cust_number, more_names ), CONSTRAINT check_dl CHECK ( LENGTH ( dl ) < 8 ) ENABLE );

SHOW COLUMNS FROM re_users;

column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden

+——————-+—————————-+——————-+————————————+——————————————————+————————————————————————————————————————————————————————————————————+—————-+

id | UUID | false | gen_random_uuid() | {primary,users_dl_key,re_users_city_idx_rename_test,re_users_city_first_name_idx,re_users_credit_card_dl_key,re_users_cust_number_key} | false city | STRING | true | NULL | | {re_users_city_idx_rename_test,re_users_city_first_name_idx} | false first_name | STRING | true | NULL | | {re_users_city_first_name_idx} | false last_name | STRING | true | NULL | | {} | false full_name | STRING | true | NULL | concat(first_name, ‘ ‘, last_name) | {} | false address | STRING | true | NULL | | {} | false credit_card | STRING | true | NULL | | {re_users_credit_card_dl_key} | false dl | STRING | true | NULL | | {users_dl_key,re_users_credit_card_dl_key} | false names | STRING | true | NULL | | {} | false location | STRING | true | NULL | | {} | false amount | DECIMAL | true | NULL | | {} | false interest | DECIMAL | false | 1.3:::DECIMAL::DECIMAL | | {} | false cust_number | DECIMAL | false | NULL | | {re_users_cust_number_key} | false more_names | STRING COLLATE en | true | NULL | | {} | false

(14 rows)

ALTER COLUMN

ALTER COLUMN语句设置，更改或删除列的DEFAULT约束或删除NOT NULL约束。

语法格式

修改指定列的语法格式如下：

SET

SET DEFAULT

设置默认值约束会在数据写入表时插入值，而不需要显式定义该列的值。如果列已经存在默认值，你可以使用此语句修改它。

SET NOT NULL

SET NOT NULL 会更改列的标记，标志这一列不允许空值出现。如果表中的任何记录都不包含该列的null值，在set not null后会更改该列的标记，这是在ALTER TABLE期间通过扫描整个表来检查的，但是，如果找到一个有效的CHECK约束，证明不能存在NULL，则跳过表扫描。

SET DATA TYPE

设置数据类型以及排序规则。

DROP

DROP DEFAULT

如果一个列定义了默认值约束，你可以删除这个约束，这意味这后续如果插入该列的数据没有定义的话，将不再插入默认值。

DROP NOT NULL

如果一个列定义了非空约束，你可以删除掉这个约束，这意味着后续该列的值可以为空。

DROP STORED

将计算出的列转换为常规列。

TYPE

修改该列的数据类型。

参数说明

table_name

查看列所在的表名。

column_name

将要被修改的列名。

a_expr

要使用的默认值。

typename

列数据类型。

collation_name

排序规则名称

示例

示例1：设置或更改默认值（DEFAULT）

ALTER TABLE office_dogs ALTER COLUMN alive SET DEFAULT ‘true’;

示例2：删除DEFAULT约束

ALTER TABLE office_dogs ALTER COLUMN alive DROP DEFAULT;

示例3：删除NOT NULL 约束

ALTER TABLE office_dogs ALTER COLUMN alive DROP NOT NULL;

示例4：将计算列转换为常规列

CREATE TABLE office_dogs ( ID INT8 NOT NULL, first_name STRING NULL, last_name STRING NULL, full_name STRING NULL AS ( concat ( first_name, ‘ ‘, last_name )) STORED, alive STRING NULL, CONSTRAINT “primary” PRIMARY KEY ( ID ASC ), FAMILY “primary” ( ID, first_name, last_name, full_name, alive ) ) ;

然后插入几行数据：

INSERT INTO office_dogs (id, first_name, last_name) VALUES (1,’Petee’,’Hirata’), (2,’Carl’,’Kimball’), (3, ‘Ernie’, ‘Narayan’);

SELECT * FROM office_dogs;

id | first_name | last_name | full_name | alive

+——+——————+—————-+———————-+———-+

1 | Petee | Hirata | Petee Hirata | NULL

2 | Carl | Kimball | Carl Kimball | NULL

3 | Ernie | Narayan | Ernie Narayan | NULL

(3 rows)

该full_name列是根据first_name和来计算的，last_name无需定义视图。可以使用以下SHOW COLUMNS语句查看列详细信息：

SHOW COLUMNS FROM office_dogs;

column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden

+——————-+—————-+——————-+————————+——————————————————+—————-+—————-+

id | INT8 | false | NULL | | {primary} | false

first_name | STRING | true | NULL | | {} | false

last_name | STRING | true | NULL | | {} | false

full_name | STRING | true | NULL | concat(first_name, ‘ ‘, last_name) | {} | false

alive | STRING | true | NULL | | {} | false

现在，将计算出的列（full_name）转换为常规列：

ALTER TABLE office_dogs ALTER COLUMN full_name DROP STORED;

检查计算的列是否已转换：

SHOW COLUMNS FROM office_dogs;

column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden

+——————-+—————-+——————-+————————+———————————-+—————-+—————-+

id | INT8 | false | NULL | | {primary} | false

first_name | STRING | true | NULL | | {} | false

last_name | STRING | true | NULL | | {} | false

full_name | STRING | true | NULL | | {} | false

alive | STRING | true | NULL | | {} | false

计算列现在变成了常规列，需要这样更新：

INSERT INTO office_dogs (id, first_name, last_name, full_name) VALUES (4, ‘Lola’, ‘McDog’, ‘This is not computed’);

INSERT 1

SELECT * FROM office_dogs;

id | first_name | last_name | full_name | alive

+——+——————+—————-+———————————+———-+

1 | Petee | Hirata | Petee Hirata | NULL

2 | Carl | Kimball | Carl Kimball | NULL

3 | Ernie | Narayan | Ernie Narayan | NULL

4 | Lola | McDog | This is not computed | NULL

DROP COLUMN

DROP COLUMN 语句是修改表语句中的一部分，从表中删除列。

语法格式

删除指定列的语法格式如下：

如果添加了IF EXISTS，当要删除的列存在时，删除列；如果不存在，删除列不成功，但是不抛出错误。

如果不添加IF EXISTS，当要删除的列存在时，删除列；如果不存在，删除列不成功，抛出错误：列不存在。

CASCADE删除所有依赖于列的其他对象（例如VIEWS）。CASCADE不会列出删除的对象，因此应谨慎使用。但是，CASCADE不会删除依赖索引；必须使用DROP INDEX。如果该列是引用中的唯一列，则会删除具有外键约束的列。

RESTRICT（默认值）如果有其他对象依赖该列，则不会删除该列。

参数说明

table_name

包含要删除的列的表的名称。

name

要删除的列的名称。删除具有CHECK约束的列时，CHECK约束也会被删除。

示例

示例1：删除列

如果不再需要表中的列，则可以将其删除。

ALTER TABLE office_dogs DROP COLUMN alive;

示例2：防止删除包含相关对象的列

如果该列具有从属对象，例如views，则InCloud ZNBASE默认情况下不会删除该列；但是，如果确定执行该操作，可以包含该RESTRICT子句。

ALTER TABLE office_dogs DROP COLUMN first_name RESTRICT;

示例3：删除列和相关对象（CASCADE）

SHOW CREATE test_view;

table_name | create_statement

+——————+——————————————————————————————————————————-+

test_view | CREATE VIEW test_view (first_name) AS SELECT first_name FROM db4.public.office_dogs

注意：在默认的情况下我们的会话变量sql_safe_updates = true，因此它会禁止一些潜在不安全的语句（如果为false，则允许使用潜在的不安全SQL语句，包括DROP非空数据库和所有从属对象，DELETE不带WHERE子句，UPDATE不带WHERE子句，以及ALTER TABLE .. DROP COLUMN。有关更多详细信息，请参见允许潜在的不安全SQL语句。

如果想要执行删除操作那么需要设置会话变量：

SET sql_safe_updates=false;

SHOW sql_safe_updates;

sql_safe_updates

+—————————+

off

ALTER TABLE office_dogs DROP COLUMN first_name CASCADE;

show create test_view;

pq: relation “test_view” does not exist