v5.0

SELECT

功能描述

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

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

注意事项

对比原MogDB的SELECT语法，新增了WHERE子句下的sounds like语法。
新增join不带on/using,效果与cross join一致。
新增PARTITION子句可指定多个分区。
新增UNION子句列如果没有相似的数据类型，会采取转换为text类型的方式进行处理。
新增FROM DUAL 语法，含义等同于不写FROM子句，是为了满足那些要求所有SELECT语句都应该包含FROM的情况。

语法格式

查询数据

[ 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 ]} [...] ];

其中指定查询源from_item为：

{[ ONLY ] table_name [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
[ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
[TIMECAPSULE {TIMESTAMP|CSN} expression]
|( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
|with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
|function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
|function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
|from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]}

其中不写FROM 子句的情况等价于：
```
FROM DUAL
```

其中group子句为：

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

其中指定分区partition_clause为：

PARTITION { ( partition_name [, ...] ) | 
        FOR (  partition_value [, ...] ) }

说明：

指定分区只适合分区表。

rollup_clause子句为：

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

JOIN语法

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

参数说明

WHERE子句

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

where 子句可以包含兼容MySQL全文索引的查询语法。match(column_name [, …]) against ('匹配字符')也是condition的一种语法。
```
where match(column_name [, ...]) against ('匹配字符');
```
column_name可以是多列，列名之间用逗号分隔。 against()的匹配字符只能是字符内容(即全文索引支持字段类型只能是这三种char, varchar, text)，不包含int, bool, 特殊字符(!,#,空格等)与正规功能。注意： mysql的全文索引查询语法match(column_name)允许无序，但该功能底层用的是MogDB的to_tsvector(),他的要求是字段顺序必须有序(与表的字段顺序一致)。

用于安装了dolphin插件，处于MySQL兼容性场景下的全文索引查询。其语法结构相当于
```
to_tsvector('ngram', col_name [|| col_name]) @@ to_tsquery('字符串')
```
UNION子句

UNION计算多个SELECT语句返回行集合的并集。UNION内部的SELECT语句必须拥有相同数量的列，列如果没有相似的数据类型或者为UNKNOWN类型，会采取转换为text类型的方式进行处理。

UNION子句有如下约束条件：
- 除非声明了ALL子句，否则缺省的UNION结果不包含重复的行。
- 同一个SELECT语句中的多个UNION操作符是从左向右计算的，除非用圆括弧进行了标识。
- FOR UPDATE，FOR NO KEY UPDATE，FOR SHARE和FOR KEY SHARE不能在UNION的结果或输入中声明。
一般表达式：

select_statement UNION [ALL] select_statement
- select_statement可以是任何没有ORDER BY、LIMIT、FOR UPDATE，FOR NO KEY UPDATE，FOR SHARE或FOR KEY SHARE子句的SELECT语句。
- 如果用圆括弧包围，ORDER BY和LIMIT可以附着在子表达式里。

说明：

涉及的其它参数说明可见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 语句中使用FROM DUAL 示例

MogDB=# select 1 as col;
 col
-----
   1
(1 row)
MogDB=# select 1 as col FROM DUAL;
 col
-----
   1
(1 row)

SELECT FROM PARTITION子句指定多个分区

MogDB=# create table multi_partition_select_test(C_INT INTEGER) partition by range(C_INT)
MogDB-# (
MogDB(#     partition test_part1 values less than (400),
MogDB(#     partition test_part2 values less than (700),
MogDB(#     partition test_part3 values less than (1000)
MogDB(# );
CREATE TABLE
MogDB=# insert into multi_partition_select_test values(111);
INSERT 0 1
MogDB=# insert into multi_partition_select_test values(555);
INSERT 0 1
MogDB=# insert into multi_partition_select_test values(888);
INSERT 0 1

MogDB=# select a.* from multi_partition_select_test partition (test_part1, test_part2) a;
 c_int
-------
   111
   555
(2 rows)

UNION子句非相似数据类型按 TEXT 类型进行转换示例：

-- 首先创建兼容模式为B的数据库
CREATE DATABASE mydb WITH DBCOMPATIBILITY 'B';
\c mydb

-- 创建两个表并插入测试数据
CREATE TABLE tbl_date(col DATE);
INSERT INTO tbl_date VALUES('2000-02-16');
CREATE TABLE tbl_json(col JSON);
INSERT INTO tbl_json VALUES('{"id":1,"dbname":"MogDB","language":"C++"}');

-- UNION 查询，将会使用TEXT类型进行转换
SELECT * FROM tbl_date UNION SELECT * FROM tbl_json;

兼容MySQL兼容性全文索引语法查询，前提是兼容模式为B的数据库。

MogDB=# CREATE SCHEMA fulltext_test;
CREATE SCHEMA
MogDB=# set current_schema to 'fulltext_test';
SET
MogDB=# CREATE TABLE test (
MogDB(# id int unsigned auto_increment not null primary key,
MogDB(# title varchar,
MogDB(# boby text,
MogDB(# name name,
MogDB(# FULLTEXT (title, boby) WITH PARSER ngram
MogDB(# );
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
MogDB=# \d test
              Table "fulltext_test.test"
 Column |       Type        |        Modifiers
--------+-------------------+-------------------------
 id     | uint4             | not null AUTO_INCREMENT
 title  | character varying |
 boby   | text              |
 name   | name              |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
    "test_to_tsvector_to_tsvector1_idx" gin (to_tsvector('ngram'::regconfig, title::text), to_tsvector('ngram'::regconfig, boby)) TABLESPACE pg_default

MogDB=# \d test_to_tsvector_to_tsvector1_idx
      Index "fulltext_test.test_to_tsvector_to_tsvector1_idx"
    Column    | Type |                  Definition
--------------+------+----------------------------------------------
 to_tsvector  | text | to_tsvector('ngram'::regconfig, title::text)
 to_tsvector1 | text | to_tsvector('ngram'::regconfig, boby)
gin, for table "fulltext_test.test"

MogDB=# DROP INDEX test_to_tsvector_to_tsvector1_idx;
DROP INDEX
MogDB=# ALTER TABLE test ADD FULLTEXT INDEX test_index_1 (title, boby) WITH PARSER ngram;
ALTER TABLE
MogDB=# DROP INDEX test_index_1;
DROP INDEX
MogDB=# CREATE FULLTEXT INDEX test_index_1 ON test (title, boby) WITH PARSER ngram;
CREATE INDEX
MogDB=# \d test_index_1
                 Index "fulltext_test.test_index_1"
    Column    | Type |                  Definition
--------------+------+----------------------------------------------
 to_tsvector  | text | to_tsvector('ngram'::regconfig, title::text)
 to_tsvector1 | text | to_tsvector('ngram'::regconfig, boby)
gin, for table "fulltext_test.test"

MogDB=# INSERT INTO test(title, boby, name) VALUES('test', '&67575@gauss', 'MogDB');
INSERT 0 1
MogDB=# INSERT INTO test(title, boby, name) VALUES('test1', 'gauss', 'MogDB');
INSERT 0 1
MogDB=# INSERT INTO test(title, boby, name) VALUES('test2', 'gauss2', 'MogDB');
INSERT 0 1
MogDB=# INSERT INTO test(title, boby, name) VALUES('test3', 'test', 'MogDB');
INSERT 0 1
MogDB=# INSERT INTO test(title, boby, name) VALUES('gauss_123_@', 'test', 'MogDB');
INSERT 0 1
MogDB=# INSERT INTO test(title, boby, name) VALUES('', '', 'MogDB');
INSERT 0 1
MogDB=# INSERT INTO test(title, boby, name) VALUES(' ', ' ', ' ');
INSERT 0 1
MogDB=# SELECT * FROM TEST;
 id |    title    |     boby     |   name
----+-------------+--------------+-----------
 1  | test        | &67575@gauss | MogDB
 2  | test1       | gauss        | MogDB
 3  | test2       | gauss2       | MogDB
 4  | test3       | test         | MogDB
 5  | gauss_123_@ | test         | MogDB
 6  |             |              | MogDB
 7  |             |              |
(7 rows)

MogDB=# SELECT * FROM TEST WHERE MATCH (title, boby) AGAINST ('test');
 id |    title    |     boby     |   name
----+-------------+--------------+-----------
 1  | test        | &67575@gauss | MogDB
 2  | test1       | gauss        | MogDB
 3  | test2       | gauss2       | MogDB
 4  | test3       | test         | MogDB
 5  | gauss_123_@ | test         | MogDB
(5 rows)

MogDB=# SELECT * FROM TEST WHERE MATCH (title, boby) AGAINST ('gauss');
 id |    title    |     boby     |   name
----+-------------+--------------+-----------
 1  | test        | &67575@gauss | MogDB
 2  | test1       | gauss        | MogDB
 3  | test2       | gauss2       | MogDB
 5  | gauss_123_@ | test         | MogDB
(4 rows)

MogDB=# DROP INDEX test_index_1;
DROP INDEX
MogDB=# CREATE FULLTEXT INDEX test_index_1 ON test (boby) WITH PARSER ngram;
CREATE INDEX
MogDB=# \d test_index_1
             Index "fulltext_test.test_index_1"
   Column    | Type |              Definition
-------------+------+---------------------------------------
 to_tsvector | text | to_tsvector('ngram'::regconfig, boby)
gin, for table "fulltext_test.test"

MogDB=# SELECT * FROM test WHERE MATCH (boby) AGAINST ('test');
 id |    title    | boby |   name
----+-------------+------+-----------
 4  | test3       | test | MogDB
 5  | gauss_123_@ | test | MogDB
(2 rows)

MogDB=# SELECT * FROM test WHERE MATCH (boby) AGAINST ('gauss');
 id | title |     boby     |   name
----+-------+--------------+-----------
 1  | test  | &67575@gauss | MogDB
 2  | test1 | gauss        | MogDB
 3  | test2 | gauss2       | MogDB
(3 rows)

MogDB=# DROP INDEX test_index_1;
DROP INDEX
MogDB=# CREATE FULLTEXT INDEX test_index_1 ON test (title, boby, name) WITH PARSER ngram;
CREATE INDEX
MogDB=# \d test_index_1
                 Index "fulltext_test.test_index_1"
    Column    | Type |                  Definition
--------------+------+----------------------------------------------
 to_tsvector  | text | to_tsvector('ngram'::regconfig, title::text)
 to_tsvector1 | text | to_tsvector('ngram'::regconfig, boby)
 to_tsvector2 | text | to_tsvector('ngram'::regconfig, name::text)
gin, for table "fulltext_test.test"

MogDB=# SELECT * FROM test WHERE MATCH (title, boby, name) AGAINST ('test');
 id |    title    |     boby     |   name
----+-------------+--------------+-----------
 1  | test        | &67575@gauss | MogDB
 2  | test1       | gauss        | MogDB
 3  | test2       | gauss2       | MogDB
 4  | test3       | test         | MogDB
 5  | gauss_123_@ | test         | MogDB
(5 rows)

MogDB=# SELECT * FROM test WHERE MATCH (title, boby, name) AGAINST ('gauss');
 id |    title    |     boby     |   name
----+-------------+--------------+-----------
 1  | test        | &67575@gauss | MogDB
 2  | test1       | gauss        | MogDB
 3  | test2       | gauss2       | MogDB
 4  | test3       | test         | MogDB
 5  | gauss_123_@ | test         | MogDB
 6  |             |              | MogDB
(6 rows)

MogDB=# SELECT * FROM test WHERE MATCH (title, boby, name) AGAINST ('MogDB');
 id |    title    |     boby     |   name
----+-------------+--------------+-----------
 1  | test        | &67575@gauss | MogDB
 2  | test1       | gauss        | MogDB
 3  | test2       | gauss2       | MogDB
 4  | test3       | test         | MogDB
 5  | gauss_123_@ | test         | MogDB
 6  |             |              | MogDB
(6 rows)

MogDB=# drop table if exists articles;
NOTICE:  table "articles" does not exist, skipping
DROP TABLE
MogDB=# CREATE TABLE articles (
MogDB(# ID int,
MogDB(# title VARCHAR(100),
MogDB(# FULLTEXT INDEX ngram_idx(title)WITH PARSER ngram
MogDB(# );
CREATE TABLE
MogDB=# \d articles
       Table "fulltext_test.articles"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 ID     | integer                |
 title  | character varying(100) |
Indexes:
    "ngram_idx" gin (to_tsvector('ngram'::regconfig, title::text)) TABLESPACE pg_default

MogDB=# drop table if exists articles;
DROP TABLE
MogDB=# CREATE TABLE articles (
MogDB(# ID int,
MogDB(# title VARCHAR(100),
MogDB(# FULLTEXT INDEX (title)WITH PARSER ngram
MogDB(# );
CREATE TABLE
MogDB=# \d articles
       Table "fulltext_test.articles"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 ID     | integer                |
 title  | character varying(100) |
Indexes:
    "articles_to_tsvector_idx" gin (to_tsvector('ngram'::regconfig, title::text)) TABLESPACE pg_default

MogDB=# drop table if exists articles;
DROP TABLE
MogDB=# CREATE TABLE articles (
MogDB(# ID int,
MogDB(# title VARCHAR(100),
MogDB(# FULLTEXT KEY keyngram_idx(title)WITH PARSER ngram
MogDB(# );
CREATE TABLE
MogDB=# \d articles
       Table "fulltext_test.articles"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 ID     | integer                |
 title  | character varying(100) |
Indexes:
    "keyngram_idx" gin (to_tsvector('ngram'::regconfig, title::text)) TABLESPACE pg_default

MogDB=# drop table if exists articles;
DROP TABLE
MogDB=# CREATE TABLE articles (
MogDB(# ID int,
MogDB(# title VARCHAR(100),
MogDB(# FULLTEXT KEY (title)WITH PARSER ngram
MogDB(# );
CREATE TABLE
MogDB=# \d articles
       Table "fulltext_test.articles"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 ID     | integer                |
 title  | character varying(100) |
Indexes:
    "articles_to_tsvector_idx" gin (to_tsvector('ngram'::regconfig, title::text)) TABLESPACE pg_default

MogDB=# create table table_ddl_0154(col1 int,col2 varchar(64), FULLTEXT idx_ddl_0154(col2));
CREATE TABLE
MogDB=# create table table_ddl_0085(
MogDB(# id int(11) not null,
MogDB(# username varchar(50) default null,
MogDB(# sex varchar(5) default null,
MogDB(# address varchar(100) default null,
MogDB(# score_num int(11));
CREATE TABLE
MogDB=# create fulltext index idx_ddl_0085_02 on table_ddl_0085(username);
CREATE INDEX
MogDB=# insert into table_ddl_0085 values (1,'test','m','xi''an changanqu', 10001), (2,'tst','w','xi''an beilingqu', 10002),
(3,'es','w','xi''an yangtaqu', 10003),(4,'s','m','beijingchaoyangqu', 10004);
INSERT 0 4
MogDB=# SELECT * FROM table_ddl_0085 WHERE MATCH (username) AGAINST ('te' IN NATURAL LANGUAGE MODE);
 id | username | sex |     address     | score_num
----+----------+-----+-----------------+-----------
  1 | test     | m   | xi'an changanqu |     10001
(1 row)

MogDB=# SELECT * FROM table_ddl_0085 WHERE MATCH (username) AGAINST ('ts' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
 id | username | sex |     address     | score_num
----+----------+-----+-----------------+-----------
  2 | tst      | w   | xi'an beilingqu |     10002
(1 row)

MogDB=# SELECT * FROM table_ddl_0085 WHERE MATCH (username) AGAINST ('test' IN BOOLEAN MODE);
 id | username | sex |     address     | score_num
----+----------+-----+-----------------+-----------
  1 | test     | m   | xi'an changanqu |     10001
(1 row)

MogDB=# SELECT * FROM table_ddl_0085 WHERE MATCH (username) AGAINST ('es' WITH QUERY EXPANSION);
 id | username | sex |     address     | score_num
----+----------+-----+-----------------+-----------
  1 | test     | m   | xi'an changanqu |     10001
  3 | es       | w   | xi'an yangtaqu  |     10003
(2 rows)

MogDB=# SELECT * FROM table_ddl_0085 WHERE MATCH (username) AGAINST ('s');
 id | username | sex |      address      | score_num
----+----------+-----+-------------------+-----------
  4 | s        | m   | beijingchaoyangqu |     10004
(1 row)

MogDB=# insert into table_ddl_0085 select * from table_ddl_0085 where match (username) against ('te' IN NATURAL LANGUAGE MODE);
INSERT 0 1
MogDB=# select * from table_ddl_0085;
 id | username | sex |      address      | score_num
----+----------+-----+-------------------+-----------
  1 | test     | m   | xi'an changanqu   |     10001
  2 | tst      | w   | xi'an beilingqu   |     10002
  3 | es       | w   | xi'an yangtaqu    |     10003
  4 | s        | m   | beijingchaoyangqu |     10004
  1 | test     | m   | xi'an changanqu   |     10001
(5 rows)

MogDB=# create fulltext index idx_ddl_0085_03 on table_ddl_0085(username) with parser ngram visible;
CREATE INDEX
MogDB=# create fulltext index idx_ddl_0085_04 on table_ddl_0085(username) visible with parser ngram;
CREATE INDEX
MogDB=# create fulltext index idx_ddl_0085_05 on table_ddl_0085(username) visible;
CREATE INDEX
MogDB=# create fulltext index idx_ddl_0085_06 on table_ddl_0085(username) with parser ngram comment 'TEST FULLTEXT INDEX COMMENT';
CREATE INDEX
MogDB=# create fulltext index idx_ddl_0085_07 on table_ddl_0085(username) comment 'TEST FULLTEXT INDEX COMMENT' with parser ngram;
CREATE INDEX
MogDB=# create fulltext index idx_ddl_0085_08 on table_ddl_0085(username) comment 'TEST FULLTEXT INDEX COMMENT';
CREATE INDEX
MogDB=# drop schema fulltext_test cascade;
NOTICE:  drop cascades to 4 other objects
DETAIL:  drop cascades to table test
drop cascades to table articles
drop cascades to table table_ddl_0154
drop cascades to table table_ddl_0085
DROP SCHEMA
MogDB=# reset current_schema;
RESET

SELECT

功能描述

注意事项

语法格式

参数说明

示例

相关链接