【AntDB数据库 SQL语法专栏】——DDL语句（2）

ALTER TABLE

说明：

更改一个表的定义

语法：

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]
ALTER TABLE [ IF EXISTS ] name
    ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
ALTER TABLE [ IF EXISTS ] name
    DETACH PARTITION partition_name

其中常用action可以是以下之一：

ADD [ COLUMN ] column_type [ column_constraint [ ... ] ]
DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column TYPE type [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD table_constraint
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
OWNER TO new_owner
SET TABLESPACE tablespace_name
复制

示例：

--重命名一个现有的表：
ALTER TABLE distributors RENAME TO suppliers;

--为一列增加一个非空约束：
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

--把一个表移动到一个不同的模式：
ALTER TABLE myschema.distributors SET SCHEMA yourschema;
复制

DROP TABLE

说明：

移除一个表

语法：

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
复制

示例：

--移除department和company两张表，若表格不存在，会报错
DROP TABLE department, company;

--移除department表，即使表格不存在，也会报错
DROP TABLE IF EXISTS department;
复制

CREATE VIEW

说明：

定义一个新视图

语法：

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
复制

示例：

--创建一个基于表格COMPANY的视图COMPANY_VIEW
CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE FROM COMPANY;
复制

ALTER VIEW

说明：

更改一个视图的定义

语法：

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )
复制

示例：

--把视图foo重命名为bar：
ALTER VIEW foo RENAME TO bar;

--为视图a_view的列ts附加一个默认列值
ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now();
复制

DROP VIEW

说明：

移除一个视图

语法：

DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
复制

示例：

--移除视图COMPANY_VIEW，若视图不存在会报错
DROP VIEW COMPANY_VIEW;

--移除视图COMPANY_VIEW，即使视图不存在，也不会报错
DROP VIEW IF EXISTS COMPANY_VIEW;
复制

CREATE INDEX

说明：

定义一个新索引

语法：

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]
复制

示例：

--在表films中的列title上创建一个 B-树索引：
CREATE UNIQUE INDEX title_idx ON films (title);
复制