通过SELECT语句可以查询一个或多个表格中的行数据。

说明 与RDBMS不同,Lindorm的SELECT语句仅支持扁平化的查询。

语法

SELECT ( select_clause | '*' )
    FROM table_name
    [force index(index_name)]
    [ WHERE where_clause ]
    [ ORDER BY ordering_clause ]
    [ LIMIT integer ] | [LIMIT integer, integer]
    [`ALLOW FILTERING`]
select_clause    ::=  selector [ AS identifier ] ( ',' selector [ AS identifier ] )
selector         ::=  column_name
                      | term
                      | function_name '(' [ selector ( ',' selector )* ] ')'
                      | COUNT '(' '*' ')'
where_clause     ::=  relation ( AND|OR relation )*
relation         ::=  column_name operator term
                      '(' column_name ( ',' column_name )* ')' operator tuple_literal
operator         ::=  '=' | '<' | '>' | '<=' | '>=' | '!=' | IN 
ordering_clause  ::=  column_name [ ASC | DESC ] ( ',' column_name [ ASC | DESC ] )*
说明 SELECT语法中部分参数说明如下:
  • 当您需要SELECT语句强制选中某条索引时,可以使用Hint语句,Force Index(需要选中的索引)。
  • LIMIT后只跟一个数字,表示LIMIT限制。如果跟两个由英文逗号(,)分隔的数字,表示OFFSET和LIMIT条数。
  • 当SQL语句被Lindorm识别为可能影响请求性的语句时,Lindorm不允许执行此SQL语句。如果您仍然需要执行此SQL语句,可以在语句末尾加上ALLOW FILTERING,但是Lindorm可能会受到此SQL语句带来的性能稳定性风险。

示例

-- 简单的查询

SELECT * FROM dt;            --选择全部列
SELECT c1,c2 FROM dt;                   --选择部分列
SELECT * FROM dt LIMIT 10;
SELECT * FROM dt WHERE pk = 10;
SELECT * FROM dt WHERE pk > 10 LIMIT 5, 20;           --跳过5行,最多返回20行
SELECT * FROM dt WHERE pk > 10 LIMIT 20 OFFSET 5;      --等价于limit 5,20
SELECT c1 FROM dt WHERE pk > 10;
SELECT c1 AS xx FROM dt WHERE pk > 10;

-- 基本的聚合操作

SELECT count(*) FROM dt;
SELECT count(c1) FROM dt;
SELECT sum(c2) FROM dt;
SELECT sum(c2), AVG(c2),MIN(c2),MAX(c2) FROM dt;


-- where条件中is null等用法

SELECT c1, c2 FROM dt WHERE (c1>1 OR (c2>0 AND c7 is null)) 
AND c3 <>1.0 AND c6 is not null;

-- 别名用法
SELECT count(*) AS countRow FROM t1;
SELECT c1 AS testC FROM t1 WHERE c1 = '2';

-- in用法
SELECT * FROM dt WHERE c1 in ('a', 'b');