云和恩墨MogDB数据库扩展-dolphin语法介绍 SELECT

功能描述

SELECT用于从表或视图中取出数据。

SELECT语句就像叠加在数据库表上的过滤器，利用SQL关键字从数据表中过滤出用户需要的数据。

注意事项

• 对比原MogDB的SELECT语法，新增了WHERE子句下的sounds like语法。
• 新增join不带on/using,效果与cross join一致。

语法格式

• 查询数据

  [ WITH [ RECURSIVE ] with_query [, ...] ]
  SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
  { * | {expression [ [ AS ] output_name ]} [, ...] }
  [ FROM from_item [, ...] ]
  [ WHERE condition ]
  [ [ START WITH condition ] CONNECT BY [NOCYCLE] condition [ ORDER SIBLINGS BY expression ] ]
  [ 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 ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
  [ LIMIT { [offset,] 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 ]} [...] ];
  

• 其中group子句为：

  ( )
  | expression
  | ( expression [, ...] )
  | rollup_clause
  | CUBE ( { expression | ( expression [, ...] ) } [, ...] )
  | GROUPING SETS ( grouping_element [, ...] )
  

  rollup_clause子句为：

  ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
  | { expression | ( expression [, ...] ) } WITH ROLLUP
  

• JOIN语法

  [JOIN | INNER JOIN] {ON join_condition | USING ( join_column [, ...] ) }
  

参数说明

• WHERE子句

  1. sounds like是condition的一种语法，用法如：column_name sounds like ‘字符’; 相当于soundex(column_name) = soundex(‘字符’)的对比结果，是一个boolean的值。用于通过soundex处理来查询满足条件的数据。

  说明： 涉及的其它参数说明可见SELECT。

示例

–SOUNDS LIKE子句示例：同音字段查询

MogDB=# CREATE TABLE TEST(id int, name varchar);
MogDB=# INSERT INTO TEST VALUES(1, 'too');
MogDB=# SELECT * FROM TEST WHERE name SOUNDS LIKE 'two';
 id | name
----+------
  1 | too
(1 row)
--SELECT GROUP BY子句中使用ROLLUP。
MogDB=# CREATE TABLESPACE t_tbspace ADD DATAFILE 'my_tablespace' ENGINE = test_engine;
CREATE TABLESPACE
MogDB=# CREATE TABLE t_with_rollup(id int, name varchar(20), area varchar(50), count int);
CREATE TABLE
MogDB=# INSERT INTO t_with_rollup values(1, 'a', 'A', 10);
INSERT 0 1
MogDB=# INSERT INTO t_with_rollup values(2, 'b', 'B', 15);
INSERT 0 1
MogDB=# INSERT INTO t_with_rollup values(2, 'b', 'B', 20);
INSERT 0 1
MogDB=# INSERT INTO t_with_rollup values(3, 'c', 'C', 50);
INSERT 0 1
MogDB=# INSERT INTO t_with_rollup values(3, 'c', 'C', 15);
INSERT 0 1
MogDB=# SELECT name, sum(count) FROM t_with_rollup GROUP BY ROLLUP(name);
 name | sum
------+-----
 a    |  10
 b    |  35
 c    |  65
      | 110
(4 rows)

MogDB=# SELECT name, sum(count) FROM t_with_rollup GROUP BY (name) WITH ROLLUP;
 name | sum
------+-----
 a    |  10
 b    |  35
 c    |  65
      | 110
(4 rows)
MogDB=# create table join_1(col1 int4, col2 int8);

MogDB=# create table join_2(col1 int4, col2 int8);

MogDB=# insert into join_1 values(1, 2), (3, 3);

MogDB=# insert into join_2 values(1, 1), (2, 3), (4, 4);

MogDB=# select join_1 join join_2;

col1 | col2 | col1 | col2 ——+——+——+—— 1 | 2 | 1 | 1 1 | 2 | 2 | 3 1 | 2 | 4 | 4 3 | 3 | 1 | 1 3 | 3 | 2 | 3 3 | 3 | 4 | 4

MogDB=# select join_1 inner join join_2;

col1 | col2 | col1 | col2 ——+——+——+—— 1 | 2 | 1 | 1 1 | 2 | 2 | 3 1 | 2 | 4 | 4 3 | 3 | 1 | 1 3 | 3 | 2 | 3 3 | 3 | 4 | 4

相关链接

SELECT

MogDB 是云和恩墨基于opengauss 企业级数据库