SELECT

语法

 SELECT ( select_clause | '*' )
        FROM table_name
        [ WHERE where_clause ]
        [ ORDER BY ordering_clause ]
        [ LIMIT integer ] | [LIMIT integer, integer]
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
group_by_clause  ::=  column_name, (',' column_name)*
ordering_clause  ::=  column_name [ ASC | DESC ] ( ',' column_name [ ASC | DESC ] )*

参数

示例

• 简单的查询操作

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

• 基本的聚合操作

  SELECT COUNT(*) 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 IS 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');