文档中心MogDBMogDB StackUqbar
v5.0

文档: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 [, ...] ) }

    img 说明:

    指定分区只适合分区表。

  • 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可以附着在子表达式里。

img 说明:

涉及的其它参数说明可见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

Copyright © 2011-2024 www.enmotech.com All rights reserved.