关键字：KingbaseES、SQL、人大金仓
概述
常用的SQL语句及详细说明。
常用的SQL语句及详细说明
操作 | 命令 |
创建表 | CREATE TABLE weather ( CREATE TABLE cities ( |
插入行 | INSERT INTO weather VALUES (‘San Francisco’, 46, 50, 0.25, ‘1994-11-27’); INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) 如果你没有获得所有列的值，那么你可以省略其中的一些。在这种情况下，这些列将被填充为它们的缺省值。例如： INSERT INTO products (product_no, name) VALUES (1, ‘Cheese’); INSERT INTO products VALUES (1, ‘Cheese’); 第二种形式是PostgreSQL的一个扩展。它从使用给出的值从左开始填充列，有多少个给出的列值就填充多少个列，其他列的将使用缺省值。 为了保持清晰，你也可以显式地要求缺省值，用于单个的列或者用于整个行： INSERT INTO products (product_no, name, price) VALUES (1, ‘Cheese’, DEFAULT); INSERT INTO products DEFAULT VALUES; 你可以在一个命令中插入多行： INSERT INTO products (product_no, name, price) VALUES (1, ‘Cheese’, 9.99), (2, ‘Bread’, 1.99), (3, ‘Milk’, 2.99); 也可以插入查询的结果（可能没有行、一行或多行）： INSERT INTO products (product_no, name, price) SELECT product_no, name, price FROM new_products WHERE release_date = ‘today’; |
查询表 查询排序 查询去重 条件查询 查询xid等 | SELECT * FROM weather; SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather; 这样应该得到： city | temp_avg | date 请注意这里的AS子句是如何给输出列重新命名的（AS子句是可选的）。 一个查询可以使用WHERE子句“修饰”，它指定需要哪些行。WHERE子句包含一个布尔（真值）表达式，只有那些使布尔表达式为真的行才会被返回。在条件中可以使用常用的布尔操作符（AND、OR和NOT）。 比如，下面的查询检索旧金山的下雨天的天气： SELECT * FROM weather 结果： city | temp_lo | temp_hi | prcp | date 你可以要求返回的查询结果是排好序的： SELECT * FROM weather city | temp_lo | temp_hi | prcp | date 在这个例子里，排序的顺序并未完全被指定，因此你可能看到属于旧金山的行被随机地排序。但是如果你使用下面的语句，那么就总是会得到上面的结果： SELECT * FROM weather 你可以要求在查询的结果中消除重复的行： SELECT DISTINCT city city
查询当前事务的xid testdb=# select txid_current(); txid_current ————– 80853335 (1 row) |
表的连接 | SELECT * city | temp_lo | temp_hi | prcp | date | name | location
如果在两个表里有重名的列，你需要限定列名来说明你究竟想要哪一个，如： SELECT weather.city, weather.temp_lo, weather.temp_hi, 这种类型的连接查询也可以用下面这样的形式写出来： SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name); 左外连接 SELECT * city | temp_lo | temp_hi | prcp | date | name | location |
语句的缩写 | 把表 weather 缩写成 W1 和 W2 （自连接） SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, city | low | high | city | low | high |
聚集函数 count Sum avg（均值） Max Min HAVING（过滤） | SELECT city FROM weather city 聚集同样也常用于和GROUP BY子句组合。在通过了WHERE过滤器之后，生成的输入表可以使用GROUP BY子句进行分组，然后用HAVING子句删除一些分组行。 比如，我们可以获取每个城市观测到的最低温度的最高值： SELECT city, max(temp_lo) city | max 这样给我们每个城市一个输出。每个聚集结果都是在匹配该城市的表行上面计算的。我们可以用HAVING 过滤这些被分组的行： SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40; city | max ———+—– Hayward | 37 (1 row) |
表的更新 UPDATE | UPDATE weather 看看数据的新状态： SELECT * FROM weather; city | temp_lo | temp_hi | prcp | date |
模式匹配 | LIKE、SIMILAR TO 正则表达式、POSIX正则表达式 ‘abc’ LIKE ‘abc’true ‘abc’ LIKE ‘a%’true ‘abc’ LIKE ‘_b_’true ‘abc’ LIKE ‘c’false ‘abc’ SIMILAR TO ‘abc’true ‘abc’ SIMILAR TO ‘a’false ‘abc’ SIMILAR TO ‘%(b|d)%’true ‘abc’ SIMILAR TO ‘(b|c)%’false 详细内容：9.7. 模式匹配 (postgres.cn) |
删除行 删除表 | DELETE FROM weather WHERE city = ‘Hayward’; 所有属于Hayward的天气记录都被删除。 DELETE FROM tablename; 如果没有一个限制，DELETE将从指定表中删除所有行，把它清空。 删除表用drop table t1； |
创建视图 | 创建视图会给该查询一个名字，我们可以像使用一个普通表一样来使用它： CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name; SELECT * FROM myview; |
外键 | 我们希望确保在cities表中有相应项之前任何人都不能在weather表中插入行。 新的表定义如下： CREATE TABLE cities ( CREATE TABLE weather ( 现在尝试插入一个非法的记录： INSERT INTO weather VALUES (‘Berkeley’, 45, 53, 0.0, ‘1994-11-28’); 报错： ERROR: insert or update on table “weather” violates foreign key constraint “weather_city_fkey” |
窗口函数（OVER） | 窗口函数可以进行排序，生成序列号等一般的聚合函数无法实现的高级操作。 窗口函数语法：其中[]中的内容可以省略 OVER ([PARTITION BY ] ORDER BY ) 窗口函数大体可以分为以下两种：1.能够作为窗口函数的聚合函数（sum，avg，count，max，min） 2.rank，dense_rank，row_number等专用窗口函数。 窗口函数在一系列与当前行有某种关联的表行上执行一种计算。 下面是一个例子用于展示如何将每一个员工的薪水与他/她所在部门的平均薪水进行比较： SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary; depname | empno | salary | avg ———–+——-+——–+———————– develop | 11 | 5200 | 5020.0000000000000000 develop | 7 | 4200 | 5020.0000000000000000 develop | 9 | 4500 | 5020.0000000000000000 develop | 8 | 6000 | 5020.0000000000000000 develop | 10 | 5200 | 5020.0000000000000000 personnel | 5 | 3500 | 3700.0000000000000000 personnel | 2 | 3900 | 3700.0000000000000000 sales | 3 | 4800 | 4866.6666666666666667 sales | 1 | 5000 | 4866.6666666666666667 sales | 4 | 4800 | 4866.6666666666666667 (10 rows) 最开始的三个输出列直接来自于表empsalary，并且表中每一行都有一个输出行。第四列表示对与当前行具有相同depname值的所有表行取得平均值 （这实际和非窗口avg聚集函数是相同的函数，但是OVER子句使得它被当做一个窗口函数处理并在一个合适的窗口帧上计算。）。 |
继承（ INHERITS (父表)） | CREATE TABLE cities ( name text, population real, altitude int — (in ft) ); CREATE TABLE capitals ( state char(2) ) INHERITS (cities); 在这种情况下，一个capitals的行从它的父亲cities继承了所有列（name、population和altitude）。 如下查询可以寻找所有海拔500尺以上的城市名称，包括州首都： SELECT name, altitude FROM cities WHERE altitude > 500; 下面的查询可以查找所有海拔高于500尺且不是州首府的城市： SELECT name, altitude FROM ONLY cities WHERE altitude > 500; |
规则排序 | COLLATE子句会重载一个表达式的排序规则 |
转换数据类型 | CAST ( expression AS target_type ); expression::type
|
数组构造 |
多维数组必须是矩形的
你可以构造一个空数组，但是因为无法得到一个无类型的数组，你必须显式地把你的空数组造型成想要的类型。例如：
|
行构造器（ROW） | 如果表t有列f1和f2，那么这些是相同的： SELECT ROW(t.*, 42) FROM t; SELECT ROW(t.f1, t.f2, 42) FROM t; |
调用函数 | 在PostgreSQL中，位置记号法是给函数传递参数的传统机制。一个例子： SELECT concat_lower_or_upper(‘Hello’, ‘World’, true); concat_lower_or_upper ———————– HELLO WORLD (1 row) 所有参数被按照顺序指定。结果是大写形式，因为uppercase被指定为true。另一个例子： SELECT concat_lower_or_upper(‘Hello’, ‘World’); concat_lower_or_upper ———————– hello world (1 row) 这里，uppercase参数被忽略，因此它接收它的默认值false，并导致小写形式的输出。在位置记号法中，参数可以按照从右往左被忽略并且因此而得到默认值。 使用命名记号 使用命名记号法的一个优点是参数可以用任何顺序指定，例如：
|
默认值 | 一个列可以被分配一个默认值。当一个新行被创建且没有为某些列指定值时，这些列将会被它们相应的默认值填充。如果没有显式指定默认值，则默认值是空值。 CREATE TABLE products ( 默认值可以是一个表达式，它将在任何需要插入默认值的时候被实时计算（不是表创建时）。一个常见的例子是为一个timestamp列指定默认值为CURRENT_TIMESTAMP，这样它将得到行被插入时的时间。另一个常见的例子是为每一行生成一个“序列号”。这在PostgreSQL可以按照如下方式实现： CREATE TABLE products ( |
生成列 | 生成的列是一个特殊的列，它总是从其他列计算而来。因此说，它对于列就像视图对于表一样。生成列有两种:存储列和虚拟列。 存储生成列在写入(插入或更新)时计算，并且像普通列一样占用存储空间。虚拟生成列不占用存储空间并且在读取时进行计算。 如此看来，虚拟生成列类似于视图，存储生成列类似于物化视图(除了它总是自动更新之外)。 PostgreSQL目前只实现了存储生成列。 建立一个生成列，在CREATE TABLE中使用GENERATED ALWAYS AS子句, 例如: CREATE TABLE people ( 必须指定关键字STORED以选择存储类型的生成列。 生成列不能被直接写入. 在INSERT或UPDATE命令中, 不能为生成列指定值, 但是可以指定关键字DEFAULT。 |
约束 主键外键 排他约束 | 检查约束 CREATE TABLE products ( 非空约束 一个列可以有多于一个的约束，只需要将这些约束一个接一个写出： CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric NOT NULL CHECK (price > 0) ); 唯一约束保证\在一列中或者一组列中保存的数据在表中所有行间是唯一的。写成一个列约束的语法是： CREATE TABLE products ( product_no integer UNIQUE, name text, price numeric ); 写成一个表约束的语法是： CREATE TABLE products ( product_no integer, name text, price numeric, UNIQUE (product_no) ); 要为一组列定义一个唯一约束，把它写作一个表级约束，列名用逗号分隔： CREATE TABLE example ( a integer, b integer, c integer, UNIQUE (a, c) ); 排他约束保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回否或空值。语法是： CREATE TABLE circles ( c circle, EXCLUDE USING gist (c &&) ); |
增加列 移除列 增加约束 移除约束 重命名列、表 更改、移除列的默认值 修改列数据类型 | 增加列：ALTER TABLE products ADD COLUMN description text; 也可以同时为列定义约束，语法： ALTER TABLE products ADD COLUMN description text CHECK (description ”); 移除列：ALTER TABLE products DROP COLUMN description; 如果该列被另一个表的外键所引用，PostgreSQL不会安静地移除该约束。我们可以通过增加CASCADE来授权移除任何依赖于被删除列的所有东西： ALTER TABLE products DROP COLUMN description CASCADE; 增加约束： 为了增加一个约束，可以使用表约束的语法，例如： ALTER TABLE products ADD CHECK (name ”); ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups; 要增加一个不能写成表约束的非空约束，可使用语法： ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; 移除约束：ALTER TABLE products DROP CONSTRAINT some_name; 为了移除一个非空约束可以用： ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; （回忆一下，非空约束是没有名称的，所以不能用第一种方式。） 重命名列：ALTER TABLE products RENAME COLUMN product_no TO product_number; 重命名表：ALTER TABLE products RENAME TO items; 更改列默认值：ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77; 移除列默认值：ALTER TABLE products ALTER COLUMN price DROP DEFAULT; 修改列数据类型：ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2); |
分区表 |
通过指定PARTITION BY子句把measurement表创建为分区表，该子句包括分区方法（这个例子中是RANGE）以及用作分区键的列列表。 CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktempint, unitsales int ) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2007m11 PARTITION OF measurement FOR VALUES FROM (‘2007-11-01’) TO (‘2007-12-01’); CREATE TABLE measurement_y2007m12 PARTITION OF measurement FOR VALUES FROM (‘2007-12-01’) TO (‘2008-01-01’) TABLESPACE fasttablespace; 为了实现子分区，在创建分区的命令中指定PARTITION BY子句，例如： CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM (‘2006-02-01’) TO (‘2006-03-01’) PARTITION BY RANGE (peaktemp); 分区表转换成普通表，我们可以使用DETACH PARTITION命令。我们也可以把某个普通表在附加到分区表上去，使用attach partition命令。 |
创建约束 | ADD CONSTRAINT |
返回数据（RETURNING） | 在INSERT中，可用于RETURNING的数据是插入的行。 这在琐碎的插入中并不是很有用，因为它只会重复客户端提供的数据。 但依赖于计算出的默认值时可以非常方便。例如，当使用 serial列来提供唯一标识符时， RETURNING可以返回分配给新行的ID： CREATE TABLE users (firstname text, lastname text, id serial primary key); INSERT INTO users (firstname, lastname) VALUES (‘Joe’, ‘Cool’) RETURNING id; RETURNING子句对于INSERT … SELECT也非常有用。 在UPDATE中，可用于RETURNING的数据是被修改行的新内容。 例如： UPDATE products SET price = price * 1.10 WHERE price <= 99.99 RETURNING name, price AS new_price; 在DELETE中，可用于RETURNING的数据是删除行的内容。例如： DELETE FROM products WHERE obsoletion_date = ‘today’ RETURNING *; |
连接（join） | => SELECT * FROM t1 CROSS JOIN t2; //交叉连接，等效于 t1 INNER JOIN t2 ON TRUE num | name | num | value —–+——+—–+——- 1 | a | 1 | xxx 1 | a | 3 | yyy 1 | a | 5 | zzz 2 | b | 1 | xxx 2 | b | 3 | yyy 2 | b | 5 | zzz 3 | c | 1 | xxx 3 | c | 3 | yyy 3 | c | 5 | zzz (9 rows) => SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num; num | name | num | value —–+——+—–+——- 1 | a | 1 | xxx 3 | c | 3 | yyy (2 rows) => SELECT * FROM t1 INNER JOIN t2 USING (num); //USING：连接的两端都具有相同的连接列名 num | name | value —–+——+——- 1 | a | xxx 3 | c | yyy (2 rows) => SELECT * FROM t1 NATURAL INNER JOIN t2; //NATURAL是USING的缩写形式。如果不存在公共列，NATURAL JOIN的行为将和JOIN … ON TRUE一样产生交叉集连接。 num | name | value —–+——+——- 1 | a | xxx 3 | c | yyy (2 rows) => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num; num | name | num | value —–+——+—–+——- 1 | a | 1 | xxx 2 | b | | 3 | c | 3 | yyy (3 rows) => SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num; num | name | num | value —–+——+—–+——- 1 | a | 1 | xxx 2 | b | | 3 | c | 3 | yyy | | 5 | zzz |
子查询 | 子查询指定了一个派生表，它必须被包围在圆括弧里并且必须被赋予一个表别名（参阅第 7.2.1.2 节）。例如： FROM (SELECT * FROM table1) AS alias_name 这个例子等效于FROM table1 AS alias_name。更有趣的情况是在子查询里面有分组或聚集的时候， 子查询不能被简化为一个简单的连接。 一个子查询也可以是一个VALUES列表： SELECT first FROM (VALUES (‘anne’, ‘smith’), (‘bob’, ‘jones’), (‘joe’, ‘blow’)) AS names(first, last) VALUES列表中的列分配别名是可选的 |
LATERAL子查询 | 允许它们引用前面的FROM项提供的列。 SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss; 它和下列语句结果完全一样： SELECT * FROM foo, bar WHERE bar.id = foo.bar_id; |
分组集 GROUPING SETS CUBE ROLLUP | GROUPING SETS的每一个子列表可以指定一个或者多个列或者表达式， 它们将按照直接出现在GROUP BY子句中同样的方式被解释。一个空的 分组集表示所有的行都要被聚集到一个单一分组（即使没有输入行存在也会被输出） 中 => SELECT * FROM items_sold; brand | size | sales ——-+——+——- Foo | L | 10 Foo | M | 20 Bar | M | 15 Bar | L | 5 (4 rows) => SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ()); brand | size | sum ——-+——+—– Foo | | 30 Bar | | 20 | L | 15 | M | 35 | | 50 (5 rows) ————————————————————– ROLLUP ( e1, e2, e3, … ) 表示给定的表达式列表及其所有前缀（包括空列表），因此它等效于 GROUPING SETS ( ( e1, e2, e3, … ), … ( e1, e2 ), ( e1 ), ( ) ) ————————————————————– 表示给定的列表及其可能的子集（即幂集）。因此 CUBE ( a, b, c ) 等效于 GROUPING SETS ( ( a, b, c ), ( a, b ), ( a, c ), ( a ), ( b, c ), ( b ), ( c ), ( ) ) CUBE ( (a, b), (c, d) ) 等效于 GROUPING SETS ( ( a, b, c, d ), ( a, b ), ( c, d ), ( ) ) |
去重 | SELECT DISTINCT |
组合查询 | 两个查询的结果可以用集合操作并、交、差进行组合。语法是 query1 UNION [ALL] query2 // UNION可以去重 query1 INTERSECT [ALL] query2 query1 EXCEPT [ALL] query2 ——————————- SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5 UNION SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002)
|
排序（ORDER BY） LIMIT和OFFSET | SELECT a, b FROM table1 ORDER BY a + b, c; 当多于一个表达式被指定，后面的值将被用于排序那些在前面值上相等的行。 注意一个输出列的名字必须孤立，即它不能被用在一个表达式中 — 例如，这是不正确的： SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; — 错误 LIMIT和OFFSET允许你只检索查询剩余部分产生的行的一部分： SELECT select_list FROM table_expression [ ORDER BY … ] [ LIMIT { number | ALL } ] [ OFFSET number ] 如果给出了一个限制计数，那么会返回数量不超过该限制的行（但可能更少些，因为查询本身可能生成的行数就比较少）。LIMIT ALL的效果和省略LIMIT子句一样，就像是LIMIT带有 NULL 参数一样。 OFFSET说明在开始返回行之前忽略多少行。OFFSET 0的效果和省略OFFSET子句是一样的 |
WITH，类似管道 | PostgreSQL WITH 子句 | 菜鸟教程 (runoob.com) |
数据类型 | 第 8 章 数据类型 (postgres.cn) |
枚举 | 一旦被创建，枚举类型可以像很多其他类型一样在表和函数定义中使用，一个枚举类型的值的排序是该类型被创建时所列出的值的顺序。枚举标签是大小写敏感的。 CREATE TYPE mood AS ENUM (‘sad’, ‘ok’, ‘happy’); CREATE TABLE person ( name text, current_mood mood ); INSERT INTO person VALUES (‘Moe’, ‘happy’); SELECT * FROM person WHERE current_mood = ‘happy’; name | current_mood ——+————– Moe | happy (1 row) |
创建数组 修改数组 | 一个数组数据类型可以通过在数组元素的数据类型名称后面加上方括号（[]）来命名。 CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], scheduletext[3][3] ); 另一种符合SQL标准的语法是使用关键词ARRAY，可以用来定义一维数组。pay_by_quarter可以这样定义： pay_by_quarter integer ARRAY[4], 或者，不指定数组尺寸： pay_by_quarter integer ARRAY, PostgreSQL在任何情况下都不会强制尺寸限制。 INSERT INTO sal_emp VALUES (‘Bill’, ‘{10000, 10000, 10000, 10000}’, ‘{{“meeting”, “lunch”}, {“training”, “presentation”}}’); SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = ‘Bill’; schedule ———————— {{meeting},{training}} (1 row) |
组合类型 | CREATE TYPE inventory_itemAS( name text, supplier_id integer, price numeric ); 定义了类型之后，我们可以用它们来创建表： CREATE TABLE on_hand ( item inventory_item, count integer ); INSERT INTO on_hand VALUES (ROW(‘fuzzy dice’, 42, 1.99), 1000); //ROW表达式也能被用来构建组合值。 或函数: CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric AS ‘SELECT $1.price * $2’ LANGUAGE SQL; SELECT price_extension(item, 10) FROM on_hand; |
format | 格式化输出，类似C的printf |
模式匹配 LIKE SIMILAR TO | stringLIKEpattern[ESCAPEescape-character] 如果该string匹配了提供的pattern，那么LIKE表达式返回真（和预期的一样，如果LIKE返回真，那么NOT LIKE表达式返回假， 反之亦然。一个等效的表达式是NOT (stringLIKEpattern)）。 反斜杠可以将下划线或者百分号转换为普通字符进行匹配，如果要匹配反斜杠，则要用双反斜杠。可以用ESCAPE指定一个不同的逃逸字符替代反斜杠
stringSIMILAR TOpattern[ESCAPEescape-character] SIMILAR TO操作符根据自己的模式是否匹配给定串而返回真或者假。 它和LIKE非常类似，只不过它使用 SQL 标准定义的正则表达式理解模式。
|
字符串匹配 substring regexp_match |
|
分离字符串 regexp_split_to_table | regexp_split_to_table把一个 POSIX 正则表达式模式当作一个定界符来分离一个串
|
枚举 enum |
|
匿名块 | 匿名块如果不显示RAISE的输出，往往是 SET client_min_messages = ‘WARNING’; 设置有问题。 设为默认值：SET client_min_messages = default; 设为NOTICE：SET client_min_messages = ‘NOTICE’; （默认） 使用匿名块 DO $$ BEGIN RAISE NOTICE ‘test’; END; $$; 此时就可以在控制台输出结果了 |
