人大金仓SQL语法DDL和DML介绍_kingbase ddl语法

SQL使用文档

SQL类型

数据定义语言DDL

数据定义语言DDL，是用于描述数据库中要存储的实体对象的语言。

常见的数据库对象

数据类型（type）：系统定义的数据类型和用户自定义的数据类型都是数据库对象。

表（table）：基本的数据存储集合，由行和列组成。

视图（view）：从一个表或者多个表中抽出的逻辑上相关的数据集合。

序列（sequence）：提供有规律的数组。

索引（index）：建立在表基础上的对象，主要作用是为了更加方便的检索

同义词（SYNONYM）：给对象取别名，简化缩写。

约束：用以增加表的完整性约束。

函数：跟很多编程语言一样，有系统预定义的函数和自定义函数之分，主要作用是获取我们需要的值。

触发器：建立在表之上，在我们对表里的记录进行增、删、改得时候，会自动执行我们定义好的语句。

存储过程：和函数类似，主要作用是把一系列的sql语句封装起来。

常见的DDL子句

根据功能上的不同，我们常常又把DDL子句分为以下几种子句：

约束子句

约束让我们能够根据需要自行定义数据，保持数据的完整性等性质，SQL支持在表和列的维度定义约束。如果有用户视图违反一个约束去操作数据，那么SQL将会抛出一个错误。

常见的约束子句

• CREATE TABLE

在创建表时指定表、列的约束。

例如，我们可以在创建表时给列和表定义约束：

CREATE TABLE employee(
    id INT4 UNIQUE,      -- 定义列级唯一约束
    name TEXT NOT NULL,  -- 定义列级非空约束
    PRIMARY KEY(id)      -- 定义表级主键约束
);
1
2
3
4
5

• ALTER TABLE

对已有表的约束进行调整。

例如，我们对上面刚创建的表取消它的name列非空约束：

ALTER TABLE employee ALTER COLUMN name SET NOT NULL;

• CREATE VIEW

在创建视图时指定约束。

例如，当我们在创建视图时为其添加了一个检查选项，实则是添加了一个约束。

CREATE VIEW empl AS (
    SELECT * FROM employee
) WITH CHECK OPTION;
1
2

约束名称

我们可以使用CONSTRAINT关键字为约束命名，如果我们在定义约束时没有使用，那么系统将会为约束自动命名，例如：

CREATE TABLE student(
    id INT4 PRIMARY KEY,
    name TEXT NOT NULL,
    age INT4,
    -- 下面定义了年龄必须大于等于0的约束
    -- 并命名为age_more_than_0
    CONSTRAINT age_more_than_0 CHECK (age >= 0)
);
1
2
3
4
5
6
7

常见的约束类型

• NOT NULL

该列不允许包含空值

• NULL

该列允许包含空值（默认情况），主要是兼容非标准SQL数据库。

• CHECK (expression) [NO INHERIT]

指定一个bool表达式，在插入和更新数据时检查该表达式，结果为TRUE或者UNKNOWN都会视为成功，结果为FALSE将阻止修改并抛出异常。

ALTER TABLE employee ADD CHECK (id > 0 AND age > 0);

上面的例子表示为employee表添加了一个检查约束，要求新数据必须满足id和age列都大于0。

一个列检查约束应该只引用该列的值，而一个表检查约束可以引用多个列的值。

CREATE TABLE employee(
  id INT4 PRIMARY KEY,
  name TEXT NOT NULL,
  age INT4,
  birthday DATE CHECK (birthday > '1900-01-01'),
  CHECK (id > 0 AND age > 0)
);
1
2
3
4
5
6

检查约束不能包含子查询，也不能引用当前行的列之外的变量。可以引用系统列tableoid，但不能引用其他列。

添加了NO INHERIT关键字的检查约束不会传递给子表。

当存在多个检查约束时，会在检查完非空约束后，按照检查约束的字母顺序对它们依次进行检查。

• 唯一约束

列约束：UNIQUE

表约束：UNIQUE (column_name[,...]) [INCLUDE ( column_name [, ...])]

唯一约束的列约束和表约束行为相同，都是指定一个列和多个列不能包含重复的值。

CREATE TABLE employee(
  id INT4 UNIQUE,
  name TEXT,
  age INT2,
  deps VARCHAR(50),
  UNIQUE(name,deps)
);
1
2
3
4
5
6
7

列约束作用于一个列，表约束可以作用于多个列。

空值对于唯一约束来说不是相等的。

添加唯一索引将自动在约束的列或列组上创建btree索引。可选子句INCLUDE添加到索引的一个或多个列上，在这些列上不强制唯一性。虽然约束没有强制包含在列上，但它仍然依赖它们。

  test=# \d employee
                      Table "public.employee"
  Column |            Type            | Collation | Nullable | Default
  --------+----------------------------+-----------+----------+---------
  id     | integer                    |           |          |
  name   | text                       |           |          |
  age    | smallint                   |           |          |
  deps   | character varying(50 char) |           |          |
  Indexes:
      "employee_id_key" UNIQUE CONSTRAINT, btree (id)
      "employee_name_deps_key" UNIQUE CONSTRAINT, btree (name, deps)
1
2
3
4
5
6
7
8
9
10

• 主键约束

列约束：PRIMARY KEY

表约束：PRIMARY KEY (column_name[,...]) [INCLUDE (column_name[,...])]

主键约束指定一个表的一个或者多个列只能包含唯一(不重复)、非空的只。一个表上只能指定一个主键，可以是列约束，也可以是表约束。

-- 表级主键
CREATE TABLE employee(
  name TEXT,
  age INT2,
  deps VARCHAR(50),
  PRIMARY KEY(name,deps)
);
-- 列级主键
CREATE TABLE employee(
  id INT4 PRIMARY KEY,
  name TEXT,
  age INT2,
  deps VARCHAR(50)
);
1
2
3
4
5
6
7
8
9
10
11
12
13

主键约束指定的列集合应该不同于用一个表上定义的任何唯一约束的列集合（否则唯一约束将会冗余并且被丢弃）

主键约束除了可以看做UNIQUE和NOT NULL的组合，还为模式设计提供了元数据，因为主键标识可以被其他表视作行的唯一标识符。

添加主键约束也会自动在列或者列组上创建btree索引。

  test=# \d employee
                      Table "public.employee"
  Column |            Type            | Collation | Nullable | Default
  --------+----------------------------+-----------+----------+---------
  name   | text                       |           | not null |
  age    | smallint                   |           |          |
  deps   | character varying(50 char) |           | not null |
  Indexes:
      "employee_pkey" PRIMARY KEY, btree (name, deps)
EXCLUDE [USING index_method] (exclude_element WITH operator[,...]) index_parameters [WHERE (predicate)]
1
2
3
4
5
6
7
8
9
10

排除约束，它保证如果任意两行在指定列或表达式上使用指定操作符进行比较，不是所有的比较都将会返回TRUE。如果所有指定的操作符都测试相等，这就等价于一个UNIQUE约束。

排他约束使用一个索引实现，这样，每个指定的操作符必须与用于索引访问方法“index_method”的一个适当的操作符类相关联。操作符是要求被交换的，每一个"exclude_element"可以选择性指定一个操作符类或者顺序选项。

• 外键约束

列约束：REFERENCES reftable [(refcolumn)] [MATCH matchtype] [ON DELETE referential_action] [ON UPDATE referential_action]

表约束：FOREIGN KEY (column_name[,...]) REFERENCES reftable [(refcolumn)] [MATCH matchtype] [ON DELETE referential_action] [ON UPDATE referential_action]

外键约束，要求新表的一列或一个列组必须只包含能匹配到被引用表的某个行在被引用列上的值。如果"refcolumn"列表被忽略，将使用"reftable"的主键。被引用列必须是被引用表中一个非可延迟唯一约束或主键约束的列。且用户必须在被引用的表上拥有REFERENCES权限。

CREATE TABLE fresh_man(
    id INT4 PRIMARY KEY,
    name TEXT,
    teacher INT4 REFERENCES employee,
    deps VARCHAR(50),
    FOREIGN KEY(deps) REFERENCES employee(deps)
);
1
2
3
4
5
6

在引用列的数据被改变时，这个表的列的数据可以执行特定的动作。ON DELETE指定当被引用表中一个被引用行被删除时要执行的动作。ON UPDATE指定当被引用表中的一个被引用列被更新为新值时要执行的动作。如果该行被更新，但是引用列没有更新不会做任何工作。此外，对每个子句可以会有以下几个动作：

NO ACTION

产生一个错误指示删除或更新将会导致一个外键约束违背。如果该约束被延迟，并且仍存在引用行，那么这个错误将在约束检查时被发现并产生。

RESTRICT

产生一个错误指示删除或更新将会导致一个外键约束违背。与NO ACTION不同的是，该检查时不可延迟的。

CASCADE

删除任何引用被删除的行，或者把引用列的值更新为被引用列的新值。

SET NULL

将引用列设置为空。

SET DEFAULT

设置引用列为它们的默认值，如果该值为空，在引用表中必须有一行匹配此值，否则操作将会失败。

LOG子句

LOG子句允许数据库指定对表的操作是否记录到WAL日志中。

LOG子句通常出现在创建和更新表、索引以及物化视图时使用。

常见的LOG子句

LOGGED

如果指定，该表被创建为一个记录日志记录的表。被写入到日志的表中的数据会被写到预写式日志中。

UNLOGGED

如果指定，该表被创建为一个不受日志记录的表。这样它比普通表块很多。不过，它们在崩溃时是不安全的。

示例：

创建UNLOGGED表

CREATE UNLOGGED TABLE test (
    id INT4,
    name VARCHAR(40)
);
调整表为LOGGED

ALTER TABLE test SET LOGGED;
1
2
3
4
5
6
7

并行子句

存储子句

数据操作语言DML

由DBMS提供，用于让用户和程序员使用，实现对数据库中数据的操作。

DML语言的分类

按照语言级别：分为过程性DML和非过程DML两类。 按照用途：分为交互式DML语句和嵌入式DML两类。

常见的DML语句

• INSERT语句

用途：

插入一个新行到表中

插入一个或者多个由表达式指定的行

插入一个来自于查询的零行或者多行

在插入时，目标列的名称是可以以任意顺序列出。如果没有给出列名列表，则有两种确定目标列的可能性，一种是以被声明的顺序列出该列的所有列；另一种是如果"values"子句或者"query"值提供了“N”个列，则以被声明的顺序列出该表的前“N”列，VALUES子句或者“query”提供的值会左右关联到这些显示或者隐式给出的目标列。对于后面没有值的列，都将被默认值填充，如果没有设置默认值，则将被设置为空。

如果任意列给的值不是正确类型，则将发生自动类型转换。

ON CONFLICT 可以指定发生唯一约束或排除约束错误时发生的动作。

-- 向表中插入一行
INSERT INTO employee VALUES (1,'zhangsan',30,'SC');
-- 指定列组插入
INSERT INTO employee(id,name,deps) VALUES (2,'lisi','EP');
1
2
3

• UPDATE语句

UPDATE更新满足条件的所有行指定列的值。只需被修改的列需要在SET子句提及，没有被修改的列将保持他们的原值。

有两种方法使用包含在数据库其他表中的信息来修改一个表：

• 使用子选择

• 在FROM子句中指定额外的表

可选的RETURNING子句导致UPDATE基于实际被更新的每一行计算并且返回值。任何使用该表的列以及FROM中提到的其他表的列的表达式都能被计算。计算会使用该表的列的新值。RETURNING列表的语法和SELECT的输出列表相同。

UPDATE employee SET age=18 WHERE name='lisi';

• DELETE语句

DELETE从指定表中删除满足WHERE子句的行。如果WHERE子句没有出现，那么就会删除表中所有的行。

DELETE FROM employee WHERE name='lisi'; 有两种方式可以使用数据库其他表中包含的信息删除一个表的一行：

• 使用子选择
• USING子句中指定额外的表
• 可选的RETURNING子句导致DELETE基于实际被删除的每一行计算并返回值。任何使用被删除表列或者USING中提到的其他表的列的表达式都可以被计算。RETURNING列表的语法和SELECT的输出列表语法相同。

数据查询语言DQL

SELECT语句从零或多个表中检索行。它的语法规则如下：

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT | UNIQUE [ ON ( expression [, ...] ) ] ]
[ * | expression [ [ AS ] output_name ] [, ...] ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST |
LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name
[, ...] ]
[ NOWAIT | SKIP LOCKED | WAIT seconds] [...] ]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

WITH子句

WITH子句允许指定一个或者多个在主查询中可以其名称引用的子查询，在主查询期间子查询实际扮演了临时表或者视图的角色。每一个子查询都可以是一个SELECT、VALUES、INSERT、UPDATE或者DELETE语句。在WITH中书写一个数据修改语句时，通常要包括一个RETURNING子句。构成被主查询读取的临时表的是RETURNING的输出，而不是WITH子句修改的底层表。如果省略RETURNING，该语句仍会被执行，但是它不会产生输出，因此不能被主表引用。

对于每一个WITH查询，都必须制定一个名称，同时也可以指定一个列名列表，如果忽略该列表，那么会从子查询中推导列名。

FROM子句

FROM列表中的所有元素都会被计算（FROM中的每一个元素都是一个真实表和虚拟表）。如果指定了多个源表，结果将是所有源表的笛卡尔积；如果在FROM列表中指定了多一个元素，它们就会交叉连接在一起。

FROM子句可以包含下列元素：

table_name：一个现有表或试图的名称。如果在表名前指定了ONLY，则只会扫描该表；如果没有指定ONLY，该表及其所有代表都会被扫描。特别的，可以在表名后指定*来显示地指示包括其后代。

@dblink：在SELECT语句的FROM子句中，用户可以直接在远程数据库对象名称后加@dblink来指定一个外部数据库的对象。

partition：PARTITION指定一个单层分区表的分区或者复合分区的主分区名称。

subpartition：SUBPARTITION指定一个复合分区的子分区的名称。

alias：一个包含别名的FROM项的替代名称。别名被用于 让书写简洁或者消除自连接中的混淆（其中一个表会被扫描多次）。当提供一个别名时，表或者函数的实际名称会被隐藏。如果写了一个别名，还可以写一个列别名列表来为该表的一个或者多个列提供替代名称。

select：一个SELECT子句可以出现在FROM子句中。这就好像把它的输出创建为一个存在于该SELECT命令期间的临时表。注意SELECT子句必须用圆括号包围，并且必须为它提供一个别名，也可以在这里使用一个VALUES命令。

with_query_name：可以通过写一个WITH查询的名称来引用它，就好像该查询的名称是一个表名（实际上，该WITH 查询会为主查询隐藏任何具有相同名称的真实表。如果必要，你可以使用带模式限定的方式以相同的名称来引用真实表）。可以像表一样，以同样的方式提供一个别名。

function_name：函数调用可以出现在FROM子句中，这个函数的输出就好像创建了一个SELECT期间的临时表。当为该函数调用增加可选的WITH ORDINALITY子句时，会在该函数的输出列之后追加一个新的列来为每一行编号。

可以用和表一样的方式提供一个别名，同时也可以添加列别名为函数的返回属性命名。

join_type：

一个JOIN子句联合两个FROM项，如有必要可以使用圆括号确定嵌套的顺序，在没有圆括号时，JOIN会从左至有嵌套。在任何时候，JOIN的联合比逗号分隔的FROM列的效率都更高。对于多张表，我们可以使用以下几种表构造链接：

[INNER] JOIN

产生简单的笛卡尔积，根据ON条件进行筛选。

CROSS JOIN

产生简单的笛卡尔积。

(LEFT|RIGHT|FULL) [OUTER] JOIN

OUTER JOIN产生被限制过的笛卡尔积，除了配对过的行，还会根据限定词产生为配对的行。

ON join_condition和USING (join_column[,...])：

连接条件的不同写法。

WHERE子句

如果指定了WHERE子句，所有不满足该条件的行都会被从输出中消除,语法如下:

WHERE condition

其中"condition"是任意计算得到布尔类型结果的表达式，如果用一行的实际值替换其中的变量引用后，该表达式返回真，则该行符合条件。

GROUP BY子句和HAVING子句

如果指定了GROUP BY子句，被返回的行会以指定的顺序排序，如果没有给定ORDER BY，系统将会以最快产生行的顺序返回它们。指定了GROUP BY子句或者如果有聚集函数，输出会被组合成由在一个或者多个值上匹配的行构成的分组，并且在其上计算聚集函数的结果。如果出现了HAVING子句，它会消除不满足给定的条件的分组，组合语法如下：

GROUP BY grouping_element [,...] [HAVING condition]

DISTINCT子句和UNIQUE子句

SELECT DISTINCT从结果中消除重复的行。SELECT DISTINCT ON消除在所有指定表达式上匹配的行。SELECT ALL(默认)将返回所有候选行，包括重复的行。

同时，在KingbaseES中的SELECT UNIQUE也可以从结果中消除重复的行。SELECT UNIQUE ON消除在所有指定表达式上匹配的行。

ORDER BY子句

如果指定了ORDER BY子句，被返回的行会以指定的顺序排序；反之会以最快产生行的顺序返回它们，语法如下：

ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]

ORDER BY子句导致结果行被按照指定的表示式排序。如果两行按照最左边的表达式是相等的，则会根据下一个表达式比较它们，依次类推，如果按照所有指定的表达式它们都是相等的，则它们被返回的顺序取决于实现。

每一个"expression"可以是输出列（SELECT列表项）的名称或者序号，它也可以是由输入列值构成的任意表达式。

LIMIT子句和OFFSET子句

如果指定了LIMIT或者（FETCH FIRST）或者OFFSET子句，SELECT语句只返回结果行的一个子集，最简单的LIMIT子句和OFFSET子句如下：

LIMIT {count | ALL} OFFSET start

更多信息，参见https://help.kingbase.com.cn/v8/index.html