3.2.5 SELECT

3.2.5.1 Syntax

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    select_expr [, select_expr] ...
    [FROM table_references]
    [WHERE where_condition]
    [GROUP BY {col_name | expr }, ... ]
    [HAVING where_condition]
    [ORDER BY {col_name | expr }
      [ASC | DESC], ... ]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [FOR {UPDATE | SHARE}
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]

3.2.5.2 与MySQL语法区别

下文中红色表示不支持的语法或关键字

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
-    [HIGH_PRIORITY]
-    [STRAIGHT_JOIN]
-    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
-    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
-    [into_option]
    [FROM table_references
-      [PARTITION partition_list]
    ]
    [WHERE where_condition]
    [GROUP BY {col_name | expr 
-   | position
    }, ... 
-    [WITH ROLLUP]
    ]
    [HAVING where_condition]
-    [WINDOW window_name AS (window_spec)
-       [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr 
-   | position
    } [ASC | DESC], ... 
-      [WITH ROLLUP]
      ]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
-    [into_option]
    [FOR {UPDATE | SHARE}
-        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
-   [into_option]

-into_option: {
-    INTO OUTFILE 'file_name'
-        [CHARACTER SET charset_name]
-        export_options
-  | INTO DUMPFILE 'file_name'
-  | INTO var_name [, var_name] ...
-}

3.2.5.3 举例

select id,col1,col3 from test where id=3;
select distinct col1,col3 from test where id>=3;
select count(*),max(id),col1 from test group by col1 desc having(count(*)>1) order by col1 desc;
select id,col1,col3 from test order by id limit 2 offset 2;
select id,col1,col3 from test order by id limit 2,2;
select 1+1,'test',id,col1*1.1,now() from test limit 3;
select current_date,current_timestamp;
select * from test where id=3 for update skip locked;
select * from test where id=3 for share;
select * from test where id=3 LOCK IN SHARE MODE;