openGauss SQL Language
大约 26 分钟
openGauss SQL Language
注意：
此文档对应的 openGauss 版本为 6.1。
openGauss 在支持 SQL 标准的同时，还兼容主流商业数据库特有的 SQL 语法和能力。
概述
SQL（Structured Query Language，结构性查询语言）是一种特定目的编程语言，用于管理关系数据库管理系统，或在关系流数据管理系统中进行流处理。SQL 基于关系代数和元组关系演算，包括一个数据定义语言和数据操作语言。SQL 的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。
DDL（Data Definition Language）数据定义语言
- 用于定义或修改数据库中的对象。如：表、索引、视图、数据库、序列、用户、角色、表空间、存储过程等。
DML（Data Manipulation Language）数据操纵语言
- 用于对数据库表中的数据进行操作，如插入，更新和删除
DCL（Data Control Language）数据控制语言
- 用来设置或更改数据库事务、授权操作（用户或角色授权，权限回收，创建角色，删除角色等）、锁表（支持 SHARE 和 EXCLUSIVE 两种锁表模式）、停机等。
DQL（Data Query Language）数据查询语言
- 用来查询数据库内的数据，如查询数据、合并多个 SELECT 语句的结果集。
数据类型
数据类型是数据的一个基本属性，用于区分不同类型的数据。不同的数据类型所占的存储空间不同，能够进行的操作也不相同。
数据库中的数据存储在数据表中，数据表中的每个字段都定义了数据类型，用户存储数据时，须遵从这些数据类型的属性，否则可能会出错，数据类型包括：
- 常用数据类型
- 数值类型、字符类型、日期类型等。
- 非常用数据类型
- 二进制类型、布尔类型等。
数字类型
整数类型：
| 名称 | 描述 | 存储空间 | 范围 |
|---|---|---|---|
| TINYINT | 微整数，别名为 INT1。 | 1 字节 | 0 ~ 255 |
| SMALLINT | 小范围整数，别名为 INT2。 | 2 字节 | -32,768 ~ +32,767 |
| INTEGER | 常用的整数，别名为 INT4。 | 4 字节 | -2,147,483,648 ~ +2,147,483,647 |
| BINARY_INTEGER | 常用的整数 INTEGER 的别名。 | 4 字节 | -2,147,483,648 ~ +2,147,483,647 |
| BIGINT | 大范围的整数，别名为 INT8。 | 8 字节 | -9,223,372,036,854,775,808 ~ +9,223,372,036,854,775,807 |
任意精度类型：
| 名称 | 描述 | 存储空间 | 范围 |
|---|---|---|---|
| NUMERIC[(p[,s])],DECIMAL[(p[,s])] | 精度 p 取值范围为[1,1000]，标度 s 取值范围为[-84,1000]。说明：p 为总位数，s 为小数位数。 | 用户声明精度。每四位（十进制位）占用两个字节，然后在整个数据上加上八个字节的额外开销。 | 未指定精度的情况下，小数点前最大 131,072位，小数点后最大 16,383 位。 |
| NUMBER[(p[,s])] | NUMERIC 类型的别名。 | 用户声明精度。每四位（十进制位）占用两个字节，然后在整个数据上加上八个字节的额外开销。 | 未指定精度的情况下，小数点前最大 131,072 位，小数点后最大 16,383 位 |
例子：创建表，包含整数类型和小数类型字段
stone@postgres=> create table type_t1
(
a TINYINT,
b INTEGER,
c BIGINT,
d DECIMAL(10,4)
);
CREATE TABLE
stone@postgres=> insert into type_t1 values(100,1000,10000,123456.1223);
INSERT 0 1
stone@postgres=> select * from type_t1;
a | b | c | d
-----+------+-------+-------------
100 | 1000 | 10000 | 123456.1223
(1 row)
序列类型：
| 名称 | 描述 | 存储空间 | 范围 |
|---|---|---|---|
| SMALLSERIAL | 二字节序列整型。 | 2 字节 | -32,768 ~ +32,767 |
| SERIAL | 四字节序列整型。 | 4 字节 | -2,147,483,648 ~ +2,147,483,647 |
| BIGSERIAL | 八字节序列整型。 | 8 字节 | -9,223,372,036,854,775,808 ~ +9,223,372,036,854,775,807 |
| LARGESERIAL | 十六字节序列整型。 | 16 字节 | -170,141,183,460,469,231,731,687,303,715,884,105,728 ~ +170,141,183,460,469,231,731,687,303,715,884,105,727 |
浮点类型：
| 名称 | 描述 | 存储空间 | 范围 |
|---|---|---|---|
| REAL,FLOAT4 | 单精度浮点数，不精准。 | 4 字节 | -3.402E+38~3.402E+38，6 位十进制数字精度。 |
| DOUBLE PRECISION,FLOAT8 | 双精度浮点数，不精准。 | 8 字节 | -1.79E+308~1.79E+308，15 位十进制数字精度。 |
| FLOAT[(p)] | 兼容选项 float_as_numeric 未开启时：浮点数，不精准；二进制精度 p 取值范围为 [1, 53]。兼容选项 float_as_numeric 开启时：映射至 NUMERIC；二进制精度 p 取值范围为 [1, 126]，对应的十进制精度范围为 [1, 38]。说明：p 为精度，表示二进制总位数。float_as_numeric 开启方式：SET behavior_compat_options TO float_as_numeric | 兼容选项 float_as_numeric 未开启时，占用 4 字节或 8 字节。兼容选项 float_as_numeric 开启时，每四位（十进制位）占用两个字节，然后在整个数据上加上八个字节的额外开销。 | 当兼容选项 float_as_numeric 未开启时，根据二进制精度 p 不同选择 REAL 或 DOUBLE PRECISION 作为内部实现。如不指定精度，则使用 DOUBLE PRECISION。当兼容选项 float_as_numeric 开启时，二进制精度 p 默认为 126 位，对应十进制精度 38 位。 |
| BINARY_DOUBLE | 是 DOUBLE PRECISION 的别名。支持浮点数特殊值 BINARY_DOUBLE_INFINITY 与 BINARY_DOUBLE_NAN。 | 8 字节 | -1.79E+308~1.79E+308，15 位十进制数字精度。 |
| DEC[(p[,s])] | 精度 p 取值范围为 [1,1000]，标度 s 取值范围为 [-84,1000]。说明：p 为总位数，s 为小数位位数。 | 用户声明精度。每四位（十进制位）占用两个字节，然后在整个数据上加上八个字节的额外开销。 | 未指定精度的情况下，小数点前最大 131,072 位，小数点后最大 16,383 位。 |
| INTEGER[(p[,s])] | 精度 p 取值范围为 [1,1000]，标度 s 取值范围为 [-84,1000]。 | 用户声明精度。每四位（十进制位）占用两个字节，然后在整个数据上加上八个字节的额外开销。 | - |
例子：创建表，包含序列类型和浮点类型字段
stone@postgres=> create table type_t2
(
a smallserial,
b serial,
c bigserial,
d float4,
e decimal(10,4)
);
NOTICE: CREATE TABLE will create implicit sequence "type_t2_a_seq" for serial column "type_t2.a"
NOTICE: CREATE TABLE will create implicit sequence "type_t2_b_seq" for serial column "type_t2.b"
NOTICE: CREATE TABLE will create implicit sequence "type_t2_c_seq" for serial column "type_t2.c"
CREATE TABLE
stone@postgres=> insert into type_t2 values(default,default,default,10.265456,123.123654);
INSERT 0 1
stone@postgres=> insert into type_t2 values(default,default,default,10.265456,123.123654);
INSERT 0 1
stone@postgres=> select * from type_t2;
a | b | c | d | e
---+---+---+---------+----------
1 | 1 | 1 | 10.2655 | 123.1237
2 | 2 | 2 | 10.2655 | 123.1237
(2 rows)
字符类型
| 名称 | 描述 | 存储空间 |
|---|---|---|
| CHAR(n) CHARACTER(n) NCHAR(n) | 定长字符串，不足补空格。n 是指字节长度，如果不带精度 n，默认精度为 1。 | 最大为 10MB。 |
| VARCHAR(n) CHARACTER VARYING(n) | 变长字符串。n 是指字节长度。 | 最大为 10MB。 |
| VARCHAR2(n) | 变长字符串。是 VARCHAR(n) 类型的别名。n 是指字节长度。 | 最大为 10MB。 |
| NVARCHAR2(n) | 变长字符串。n 是指字符长度。 | 最大为 10MB。 |
| TEXT | 变长字符串。 | 最大为 1GB-1，但还需要考虑到列描述头信息的大小， 以及列所在元组的大小限制（也小于 1GB-1），因此 TEXT 类型最大大小可能小于 1GB-1。 |
| CLOB | 文本大对象。是 TEXT 类型的别名。 | 最大为 1GB-1，但还需要考虑到列描述头信息的大小， 以及列所在元组的大小限制（也小于 1GB-1），因此 CLOB 类型最大大小可能小于 1GB-1。 |
其中：
char(n)和varchar(n)表示可以存储长度不超过n字符（而不是字节）的字符串，如果字符串长度超过n，则会报错，除非多余的字符都是空格，此时字符串将会被截取为最大长度。如果字符串长度小于n，char将使用空格填充。char不指定长度，等价于char(1)。- 将
char值转换为其他字符串类型时，将删除尾随空格。 - 大多数情况下，建议使用
text或者varchar。
例子：创建表，包含字符类型字段
stone@postgres=> create table type_t3
(
a character(4),
b varchar(5)
);
CREATE TABLE
stone@postgres=> insert into type_t3 values('ok','good');
INSERT 0 1
stone@postgres=> select * from type_t3;
a | b
------+------
ok | good
(1 row)
日期类型
| 名称 | 描述 | 存储空间 |
|---|---|---|
| DATE | 日期和时间。 | 4 字节（实际存储空间大小为 8 字节） |
| TIME [(p)] [WITHOUT TIME ZONE] | 只用于一日内时间。 p 表示小数点后的精度，取值范围为 0~6。 | 8 字节 |
| TIME [(p)] [WITH TIME ZONE] | 只用于一日内时间，带时区。 p 表示小数点后的精度，取值范围为 0~6。 | 12 字节 |
| TIMESTAMP[(p)] [WITHOUT TIME ZONE] | 日期和时间。 p 表示小数点后的精度，取值范围为 0~6。 | 8 字节 |
| TIMESTAMP[(p)] [WITH TIME ZONE] | 日期和时间，带时区。TIMESTAMP 的别名为 TIMESTAMPTZ。 p 表示小数点后的精度，取值范围为 0~6。 | 8 字节 |
| SMALLDATETIME | 日期和时间，不带时区。精确到分钟，秒位大于等于 30 秒进一位。 | 8 字节 |
| INTERVAL DAY (l) TO SECOND (p) | 时间间隔，X 天 X 小时 X 分 X 秒。 l：天数的精度，取值范围为 0~6。兼容性考虑，目前未实现具体功能。 p：秒数的精度，取值范围为 0~6。小数末尾的零不显示。 | 16 字节 |
| INTERVAL [FIELDS] [ (p) ] | 时间间隔。 FIELDS：可以是 YEAR，MONTH，DAY，HOUR，MINUTE，SECOND，DAY TO HOUR，DAY TO MINUTE，DAY TO SECOND，HOUR TO MINUTE，HOUR TO SECOND，MINUTE TO SECOND。 p：秒数的精度，取值范围为 0~6，且 FIELDS 为 SECOND，DAY TO SECOND，HOUR TO SECOND 或 MINUTE TO SECOND 时，参数 p 才有效。小数末尾的零不显示。 | 12 字节 |
| reltime | 相对时间间隔。格式为：X years X mons X days XX:XX:XX。 采用儒略历计时，规定一年为 365.25 天，一个月为 30 天，计算输入值对应的相对时间间隔，输出采用 POSTGRES 格式。 | 4 字节 |
| abstime | 日期和时间。 格式为：YYYY-MM-DD hh:mm:ss+timezone 取值范围为 1901-12-13 20:45:53 GMT~2038-01-18 23:59:59 GMT，精度为秒。 | 4 字节 |
例子：创建表，包含日期类型字段
stone@postgres=> create table type_t4
(
a time without time zone,
b time with time zone,
c timestamp without time zone,
d timestamp with time zone,
e smalldatetime
);
CREATE TABLE
stone@postgres=> insert into type_t4 values('21:21:21','21:21:21 pst','2010-12-12','2013-12-11 pst','2003-04-12 04:05:06');
INSERT 0 1
stone@postgres=> select * from type_t4;
a | b | c | d | e
----------+-------------+---------------------+------------------------+---------------------
21:21:21 | 21:21:21-08 | 2010-12-12 00:00:00 | 2013-12-11 16:00:00+08 | 2003-04-12 04:05:00
(1 row)
日期时间输入
任何日期或时间文本输入都需要用单引号括起来。
语法：
type [ (p) ] 'value'
日期类型输入示例：
| Example | Description |
|---|---|
| 1999-01-08 | ISO 8601; January 8 in any mode (recommended format) |
| January 8, 1999 | unambiguous in any datestyle input mode |
| 1/8/1999 | January 8 in MDY mode; August 1 in DMY mode |
| 1/18/1999 | January 18 in MDY mode; rejected in other modes |
| 01/02/03 | January 2, 2003 in MDY mode; February 1, 2003 in DMY mode; February 3, 2001 in YMD mode |
| 1999-Jan-08 | January 8 in any mode |
| Jan-08-1999 | January 8 in any mode |
| 08-Jan-1999 | January 8 in any mode |
| 99-Jan-08 | January 8 in YMD mode, else error |
| 08-Jan-99 | January 8, except error in YMD mode |
| Jan-08-99 | January 8, except error in YMD mode |
| 19990108 | ISO 8601; January 8, 1999 in any mode |
| 990108 | ISO 8601; January 8, 1999 in any mode |
| 1999.008 | year and day of year |
| J2451187 | Julian date |
| January 8, 99 BC | year 99 BC |
时间类型输入示例：
| Example | Description |
|---|---|
04:05:06.789 | ISO 8601 |
04:05:06 | ISO 8601 |
04:05 | ISO 8601 |
040506 | ISO 8601 |
04:05 AM | same as 04:05; AM does not affect value |
04:05 PM | same as 16:05; input hour must be <= 12 |
04:05:06.789-8 | ISO 8601, with time zone as UTC offset |
04:05:06-08:00 | ISO 8601, with time zone as UTC offset |
04:05-08:00 | ISO 8601, with time zone as UTC offset |
040506-08 | ISO 8601, with time zone as UTC offset |
040506+0730 | ISO 8601, with fractional-hour time zone as UTC offset |
040506+07:30:00 | UTC offset specified to seconds (not allowed in ISO 8601) |
04:05:06 PST | time zone specified by abbreviation |
2003-04-12 04:05:06 America/New_York | time zone specified by full name |
时区输入示例：
| Example | Description |
|---|---|
PST | Abbreviation (for Pacific Standard Time) |
America/New_York | Full time zone name |
PST8PDT | POSIX-style time zone specification |
-8:00:00 | UTC offset for PST |
-8:00 | UTC offset for PST (ISO 8601 extended format) |
-800 | UTC offset for PST (ISO 8601 basic format) |
-8 | UTC offset for PST (ISO 8601 basic format) |
zulu | Military abbreviation for UTC |
z | Short form of zulu (also in ISO 8601) |
默认时区由参数 TimeZone 指定：
stone@postgres=> show TimeZone;
TimeZone
----------
PRC
(1 row)
时间戳输入示例：
TIMESTAMP '2004-10-19 10:23:54'
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
特殊日期时间输入：
| Input String | Valid Types | Description |
|---|---|---|
epoch | date, timestamp | 1970-01-01 00:00:00+00 (Unix system time zero) |
infinity | date, timestamp | later than all other time stamps |
-infinity | date, timestamp | earlier than all other time stamps |
now | date, time, timestamp | current transaction's start time |
today | date, timestamp | midnight (00:00) today |
tomorrow | date, timestamp | midnight (00:00) tomorrow |
yesterday | date, timestamp | midnight (00:00) yesterday |
allballs | time | 00:00:00.00 UTC |
日期时间输出
输出格式有以下 4 种：
| Style Specification | Description | Example |
|---|---|---|
ISO | ISO 8601, SQL standard | 1997-12-17 07:37:16-08 |
SQL | traditional style | 12/17/1997 07:37:16.00 PST |
Postgres | original style | Wed Dec 17 07:37:16 1997 PST |
German | regional style | 17.12.1997 07:37:16.00 PST |
默认为 ISO，由参数 datestyle 指定：
stone@postgres=> show datestyle;
DateStyle
-----------
ISO, MDY
(1 row)
可以使用参数 datestyle 指定年月日显示顺序：
datestyle Setting | Input Ordering | Example Output |
|---|---|---|
SQL, DMY | day/month/year | 17/12/1997 15:37:16.00 CET |
SQL, MDY | month/day/year | 12/17/1997 07:37:16.00 PST |
Postgres, DMY | day/month/year | Wed 17 Dec 07:37:16 1997 PST |
间隔输入
间隔类型的语法为 interval [ fields ] [ (p) ]。
其中 interval 可以写为：
[@] quantity unit [quantity unit...] [direction]
其中：
quantity：为一个数字。unit：为时间单位，包括microsecond，millisecond，second，minute，hour，day，week，month，year，decade，century，millennium，及这些单位的缩写和复数形式。缩写有：
| Abbreviation | Meaning |
|---|---|
| Y | Years |
| M | Months (in the date part) |
| W | Weeks |
| D | Days |
| H | Hours |
| M | Minutes (in the time part) |
| S | Seconds |
- 可以直接指定天、小时、分钟和秒，而无需明确的单位标记。例如，
'1 12:59:10'与'1 day 12 hours 59 min 10 sec'相同。 - 以用破折号指定年份和月份的组合，例如
'200-10'与'200 years 10 months'相同。
其中 fields 可以是：
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND
间隔输入示例：
| Example | Description |
|---|---|
1-2 | SQL standard format: 1 year 2 months |
3 4:05:06 | SQL standard format: 3 days 4 hours 5 minutes 6 seconds |
1 year 2 months 3 days 4 hours 5 minutes 6 seconds | Traditional Postgres format: 1 year 2 months 3 days 4 hours 5 minutes 6 seconds |
P1Y2M3DT4H5M6S | ISO 8601 “format with designators”: same meaning as above |
P0001-02-03T04:05:06 | ISO 8601 “alternative format”: same meaning as above |
间隔输出
间隔类型的输出格式有 4 种：
sql_standardpostgrespostgres_verboseiso_8601
默认为 postgres，由参数 intervalstyle 指定：
stone@postgres=> show IntervalStyle;
IntervalStyle
---------------
postgres
(1 row)
使用 SET intervalstyle 进行设置。
间隔输出示例：
| Style Specification | Year-Month Interval | Day-Time Interval | Mixed Interval |
|---|---|---|---|
sql_standard | 1-2 | 3 4:05:06 | -1-2 +3 -4:05:06 |
postgres | 1 year 2 mons | 3 days 04:05:06 | -1 year -2 mons +3 days -04:05:06 |
postgres_verbose | @ 1 year 2 mons | @ 3 days 4 hours 5 mins 6 secs | @ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago |
iso_8601 | P1Y2M | P3DT4H5M6S | P-1Y-2M3DT-4H-5M-6S |
布尔类型
| 名称 | 描述 | 存储空间 | 取值 |
|---|---|---|---|
| BOOLEAN | 布尔类型 | 1字节。 | TRUE：真 FALSE：假 NULL：未知（UNKNOWN） |
布尔常量可以在 SQL 查询中用关键字 TRUE 、FALSE 和 NULL 表示。
当要向布尔类型字段输入 true 时，可以指定以下字符串：
trueyeson1
当要向布尔类型字段输入 false 时，可以指定以下字符串：
falsenooff0
布尔类型字段的输出为 t 或者 f。
例子：创建表，包含布尔类型字段
stone@postgres=> create table type_t5
(
a boolean,
b text
);
CREATE TABLE
stone@postgres=> insert into type_t5 values(1,'true');
INSERT 0 1
stone@postgres=> insert into type_t5 values(true,'true');
INSERT 0 1
stone@postgres=> insert into type_t5 values(0,'false');
INSERT 0 1
stone@postgres=> insert into type_t5 values(false,'false');
INSERT 0 1
stone@postgres=> select * from type_t5;
a | b
---+-------
t | true
t | true
f | false
f | false
(4 rows)
字段设计
在字段设计时，基于查询效率的考虑，一般遵循以下原则：
- 尽量使用高效数据类型。选择数值类型时，在满足业务精度的情况下，选择数据类型的优先级从高到低依次为整数、浮点数、NUMERIC。
- 当多个表存在逻辑关系时，表示同一含义的字段应该使用相同的数据类型。
- 对于字符串数据，建议使用变长字符串数据类型，并指定最大长度。请务必确保指定的最大长度大于需要存储的最大字符数，避免超出最大长度时出现字符截断现象。除非明确知道数据类型为固定长度字符串，否则，不建议使用 CHAR(n)、BPCHAR(n)、NCHAR(n)、CHARACTER(n)。
约束
约束子句用于声明约束，新行或者更新的行必须满足这些约束才能成功插入或更新。如果存在违反约束的数据行为，行为会被约束终止。
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。
约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。
openGauss 中常用的约束如下：
- NOT NULL：指示某列不能存储 NULL 值。
- UNIQUE：确保某列的值都是唯一的。
- PRIMARY KEY：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
- FOREIGN KEY： 保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK： 保证列中的值符合指定的条件。
检查约束
指定一个布尔表达式，插入或更新的值需要满足该表达式。使用 CHECK 关键字定义。
例子：创建表为字段指定检查约束
stone@postgres=> CREATE TABLE staff4(
ID INT PRIMARY KEY,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL CHECK(SALARY > 0)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "staff4_pkey" for table "staff4"
CREATE TABLE
插入的数据不满足要求时，数据库返回报错
stone@postgres=> INSERT INTO staff4(ID,NAME,AGE,SALARY) VALUES (2, 'JUCE',16,0);
ERROR: new row for relation "staff4" violates check constraint "staff4_salary_check"
DETAIL: N/A
例子：使用 CONSTRAINT 关键字为约束指定名称
CREATE TABLE products (
product_no integer,
name text,
price numeric CONSTRAINT positive_price CHECK (price > 0)
);
例子：在表级指定约束
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
CREATE TABLE products (
product_no integer,
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0 AND price > discounted_price)
);
例子：为表级约束指定名称
CREATE TABLE products (
product_no integer,
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CONSTRAINT valid_discount CHECK (price > discounted_price)
);
非空约束
创建表时，如果不指定约束，默认值为 NULL，即允许列插入空值。如果不想某列存在 NULL 值，那么需要在该列上定义 NOT NULL 约束，指定在该列上的值不允许存在 NULL 值。插入数据时，如果该列存在 NULL 值，则会报错，插入失败。
例子：创建表并为字段指定非空约束
stone@postgres=> CREATE TABLE staff(
ID INT NOT NULL,
NAME char(8) NOT NULL,
AGE INT,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE
为表插入数据，当为非空字段插入空值时，数据库返回报错
stone@postgres=> INSERT INTO staff VALUES (1,'lily',28);
INSERT 0 1
stone@postgres=> INSERT INTO staff (NAME,AGE) VALUES ('JUCE',28);
ERROR: null value in column "id" violates not-null constraint
DETAIL: Failing row contains (null, JUCE , 28, null, null).
唯一约束
唯一约束表示表里的一个字段或多个字段的组合必须在全表范围内唯一，使用 UNIQUE 关键字定义。
对于唯一约束，NULL 被认为是互不相等的。
创建唯一约束将自动在约束列上创建唯一 B 树索引。
例子：创建表并为字段指定唯一约束
stone@postgres=> CREATE TABLE staff1(
ID INT NOT NULL,
NAME char(8) NOT NULL,
AGE INT NOT NULL UNIQUE,
ADDRESS CHAR(50),
SALARY REAL
);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "staff1_age_key" for table "staff1"
CREATE TABLE
为表插入数据，当为唯一约束字段插入相同数据时，数据库返回报错
stone@postgres=> INSERT INTO staff1 VALUES (1,'lily',28);
INSERT 0 1
stone@postgres=> INSERT INTO staff1 VALUES (2, 'JUCE',28);
ERROR: duplicate key value violates unique constraint "staff1_age_key"
DETAIL: Key (age)=(28) already exists.
主键约束
主键约束指定一个或者多个字段的值唯一且非空。使用 PRIMARY KEY 关键字定义。
创建主键约束将自动在约束列上创建唯一 B 树索引并标记字段非空。
一个表最多只能有一个主键，建议为每个表都创建主键。
例子：创建表并为字段指定主键约束
stone@postgres=> CREATE TABLE staff2(
ID INT PRIMARY KEY ,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "staff2_pkey" for table "staff2"
CREATE TABLE
外键约束
外键约束用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性，以保证从表外键约束字段的数据必须来自于主表的被引用字段或者为空。
例如有以下作为主表的雇员表：
stone@postgres=> CREATE TABLE staff3(
ID INT PRIMARY KEY,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "staff3_pkey" for table "staff3"
CREATE TABLE
如果需要确保部门表中的雇员必须真实存在，则可以在该表的 EMP_ID 字段上创建外键约束，参照雇员表中的 ID 字段（该字段必须为主表的主键或者唯一键）：
stone@postgres=> CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY,
DEPT CHAR(50) NOT NULL,
EMP_ID INT references staff3(ID)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "department_pkey" for table "department"
CREATE TABLE
如果在雇员表中 ID 字段为主键，则外键约束可以省略主表字段，简写为：
CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY,
DEPT CHAR(50) NOT NULL,
EMP_ID INT references staff3
);
可以在表级为多个字段创建外键约束：
CREATE TABLE t1 (
a integer PRIMARY KEY,
b integer,
c integer,
FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);
主表和从表也可以是同一张表，用于具有层级关系的数据：
CREATE TABLE tree (
node_id integer PRIMARY KEY,
parent_id integer REFERENCES tree,
name text,
...
);
一个表也可以包含多个外键约束，引用多个表的数据：
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
shipping_address text,
...
);
CREATE TABLE order_items (
product_no integer REFERENCES products,
order_id integer REFERENCES orders,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
为从表的列增加外键约束后，删除主表记录或者更新主表被引用字段时，为确保引用完整性，可以为外键约束增加删除（ ON DELETE）或更新（ON UPDATE）选项：
NO ACTION：删除主表记录或者更新主表被引用字段将报错，不允许执行该操作，这是默认选项。对于延迟约束，将在约束检查时报错。RESTRICT：与NO ACTION相同，适用于不可延迟约束。CASCADE：级联删除或更新从表中的数据。SET NULL：将从表的外键约束字段置为空，只适用于ON DELETE。SET DEFAULT：将从表的外键约束字段置为默认值，只适用于ON DELETE。如果默认值不为空，则必须与主表被引用字段的某个值匹配。
应根据业务情况，选项合适的选项创建外键：
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
shipping_address text,
...
);
CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
对于 SET NULL 和 SET DEFAULT，可以指定外键约束字段中的某个字段：
CREATE TABLE tenants (
tenant_id integer PRIMARY KEY
);
CREATE TABLE users (
tenant_id integer REFERENCES tenants ON DELETE CASCADE,
user_id integer NOT NULL,
PRIMARY KEY (tenant_id, user_id)
);
CREATE TABLE posts (
tenant_id integer REFERENCES tenants ON DELETE CASCADE,
post_id integer NOT NULL,
author_id integer,
PRIMARY KEY (tenant_id, post_id),
FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL (author_id)
);
注意：
删除主表记录或者更新主表被引用字段都会扫描从表的匹配数据，故建议在从表外键上创建索引。
约束设计
- 给明确不存在 NULL 值的字段加上 NOT NULL 约束，优化器会在特定场景下对其进行自动优化。
- 给可以显式命名的约束显式命名，名称一般为约束类型简写加上表名以及字段名。
- 列存表不支持外键约束和检查约束。
索引
索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。
索引可以用来提高数据库查询性能，但是不恰当的使用将导致数据库性能下降。建议仅在匹配如下某条原则时创建索引：
- 经常执行查询的字段。
- 在连接条件上创建索引，对于存在多字段连接的查询，建议在这些字段上建立组合索引。例如，
select * from t1 join t2 on t1.a=t2.a and t1.b=t2.b，可以在t1表上的a，b字段上建立组合索引。 - WHERE 子句的过滤条件字段上（尤其是范围条件）。
- 经常出现在 ORDER BY、GROUP BY 和 DISTINCT 后的字段。
创建索引
语法：
- 单列索引：只基于表的一个列上创建的索引
CREATE INDEX [ [schema_name.]index_name ] ON table_name (column_name);
- 组合索引：基于表的多列上创建的索引
CREATE INDEX [ [schema_name.]index_name ] ON table_name (column1_name,column2_name,...);
- 唯一索引：指定唯一索引的字段不允许重复值插入
CREATE UNIQUE INDEX [ [schema_name.]index_name ] ON table_name (column_name);
- 局部索引：在表的子集上构建索引，子集由一个条件表达式定义
CREATE INDEX [ [schema_name.]index_name ] ON table_name (expression);
- 部分索引：是一个只包含表的一部分记录的索引，通常是该表中比其他部分数据更有用的部分
CREATE INDEX [ [schema_name.]index_name ] ON table_name (column_name)
[ WHERE predicate ]
说明：
UNIQUE：创建唯一性索引，每次添加数据时检测表中是否有重复值。如果插入或更新的值会引起重复的记录时，将导致一个错误。目前只有 B-tree 索引支持唯一索引。schema_name：模式的名称。取值范围：已存在模式名。index_name：要创建的索引名，索引的模式与表相同。取值范围：字符串，要符合标识符的命名规范。table_name：需要为其创建索引的表的名称，可以用模式修饰。取值范围：已存在的表名。column_name：表中需要创建索引的列的名称（字段名）。如果索引方式支持多字段索引，可以声明多个字段。全局索引最多可以声明 31 个字段，其他索引最多可以声明 32 个字段。expression：创建一个基于该表的一个或多个字段的表达式索引，通常必须写在圆括弧中。如果表达式有函数调用的形式，圆括弧可以省略。表达式索引可用于获取对基本数据的某种变形的快速访问。比如，一个在upper(col)上的函数索引将允许WHERE upper(col) = 'JIM'子句使用索引。在创建表达式索引时，如果表达式中包含IS NULL子句，则这种索引是无效的。此时，建议用户尝试创建一个部分索引。WHERE predicate：创建一个部分索引。部分索引是一个只包含表的一部分记录的索引，通常是该表中比其他部分数据更有用的部分。例如，有一个表，表里包含已记账和未记账的定单，未记账的定单只占表的一小部分而且这部分是最常用的部分，此时就可以通过只在未记账部分创建一个索引来改善性能。另外一个可能的用途是使用带有UNIQUE的WHERE强制一个表的某个子集的唯一性。取值范围：predicate表达式只能引用表的字段，它可以使用所有字段，而不仅是被索引的字段。目前，子查询和聚集表达式不能出现在WHERE子句里。
例子：创建表及其索引
-- 创建表
stone@postgres=> CREATE TABLE ship_mode_t1
(
SM_SHIP_MODE_SK INTEGER NOT NULL,
SM_SHIP_MODE_ID CHAR(16) NOT NULL,
SM_TYPE CHAR(30) ,
SM_CODE CHAR(10) ,
SM_CARRIER CHAR(20) ,
SM_CONTRACT CHAR(20)
) ;
CREATE TABLE
-- 创建单列索引
stone@postgres=> CREATE INDEX ds_ship_mode_t1_index0 ON ship_mode_t1(SM_SHIP_MODE_ID);
CREATE INDEX
-- 创建普通的唯一索引
stone@postgres=> CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON ship_mode_t1(SM_SHIP_MODE_SK);
CREATE INDEX
-- 创建表达式索引
stone@postgres=> CREATE INDEX ds_ship_mode_t1_index2 ON ship_mode_t1(SUBSTR(SM_CODE,1 ,4));
CREATE INDEX
-- 创建部分索引
stone@postgres=> CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON ship_mode_t1(SM_SHIP_MODE_SK) WHERE SM_SHIP_MODE_SK>10;
CREATE INDEX
-- 查看索引
stone@postgres=> \d+ ship_mode_t1
Table "stone.ship_mode_t1"
Column | Type | Modifiers | Storage | Stats target | Description
-----------------+---------------+-----------+----------+--------------+-------------
sm_ship_mode_sk | integer | not null | plain | |
sm_ship_mode_id | character(16) | not null | extended | |
sm_type | character(30) | | extended | |
sm_code | character(10) | | extended | |
sm_carrier | character(20) | | extended | |
sm_contract | character(20) | | extended | |
Indexes:
"ds_ship_mode_t1_index1" UNIQUE, ubtree (sm_ship_mode_sk) WITH (storage_type=ustore) TABLESPACE pg_default
"ds_ship_mode_t1_index3" UNIQUE, ubtree (sm_ship_mode_sk) WITH (storage_type=ustore) TABLESPACE pg_default WHERE sm_ship_mode_sk > 10
"ds_ship_mode_t1_index0" ubtree (sm_ship_mode_id) WITH (storage_type=ustore) TABLESPACE pg_default
"ds_ship_mode_t1_index2" ubtree (substr(sm_code::text, 1, 4)) WITH (storage_type=ustore) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no, storage_type=ustore
删除索引
语法：
DROP INDEX index_name;
重建索引
数据库经过多次删除操作后，索引页面上的索引键将被删除，造成索引膨胀。例行重建索引，可有效的提高查询效率。
数据库支持的索引类型为 B-tree 索引，例行重建索引可有效的提高查询效率。
- 如果数据发生大量删除后，索引页面上的索引键将被删除，但索引页面并不会直接删除，即索引页面数量并不会减少，因此会造成索引膨胀。重建索引可回收浪费的空间。
- 新建的索引中逻辑结构相邻的页面，通常在物理结构中也是相邻的，所以一个新建的索引比更新了多次的索引访问速度要快。
重建索引有以下两种方式：
- 先运行
DROP INDEX语句删除索引，再运行CREATE INDEX语句创建索引。- 在删除索引过程中，会在父表上增加一个短暂的排他锁，阻止相关读写操作。
- 在创建索引过程中，会锁住写操作但是不会锁住读操作，此时读操作只能使用顺序扫描。
- 使用
REINDEX语句重建索引。- 重建单个索引：
REINDEX INDEX [ [ schema_name. ] index_name ]; - 重建单个表的所有索引：
REINDEX TABLE [ [ schema_name. ] table_name ]; - 重建数据库中所有表的索引：
REINDEX DATABASE [ database_name ]; - 重建系统表的索引：
REINDEX SYSTEM [ table_name ];这里的table_name可以指定具体的系统表名称，如果不指定则会重建所有系统表的索引。 - 重建内部表索引（包括
DESC表和列存表的cudesc表）：REINDEX INTERNAL TABLE [ [ schema_name. ] table_name ]; - 使用
REINDEX TABLE和REINDEX INTERNAL TABLE语句重建索引，会在重建过程中增加排他锁，阻止相关读写操作。这意味着在索引重建期间，其他事务无法对该表进行读写操作，可能会影响数据库的可用性，应选择在业务低峰期进行操作。
- 重建单个索引：
注意：
在重建索引前，用户可以通过临时增大
maintenance_work_mem和psort_work_mem的取值来加快索引的重建。
例子：先删除索引再创建索引
-- 删除索引
stone@postgres=> DROP INDEX ds_ship_mode_t1_index0;
DROP INDEX
-- 创建索引
stone@postgres=> CREATE INDEX ds_ship_mode_t1_index0 ON ship_mode_t1(SM_SHIP_MODE_ID);
CREATE INDEX
例子：使用 REINDEX 重建表的所有索引
stone@postgres=> REINDEX TABLE ship_mode_t1;
REINDEX