MySQL的SQL语句 - 数据操作语句（12）- SELECT 语句（1）

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

mysql> SELECT 1 + 1;
        -> 2

mysql> SELECT 1 + 1 FROM DUAL;
        -> 2

SELECT * FROM t1 INNER JOIN t2 ...

SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...

SELECT AVG(score), t1.* FROM t1 ...

SELECT CONCAT(last_name,', ',first_name) AS full_name
  FROM mytable ORDER BY full_name;

SELECT CONCAT(last_name,', ',first_name) full_name
  FROM mytable ORDER BY full_name;

SELECT columna columnb FROM mytable;

tbl_name [[AS] alias] [index_hint]

SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
  WHERE t1.name = t2.name;


SELECT t1.name, t2.salary FROM employee t1, info t2
  WHERE t1.name = t2.name;

SELECT college, region, seed FROM tournament
  ORDER BY region, seed;


SELECT college, region AS r, seed AS s FROM tournament
  ORDER BY r, s;


SELECT college, region, seed FROM tournament
  ORDER BY 2, 3;

SELECT a, b, COUNT(c) AS t FROM test_table GROUP BY a,b ORDER BY a,t DESC;

SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

SELECT col_name FROM tbl_name HAVING col_name > 0;

SELECT col_name FROM tbl_name WHERE col_name > 0;

SELECT user, MAX(salary) FROM users
  GROUP BY user HAVING MAX(salary) > 10;

SELECT 12 AS a, a FROM t GROUP BY a;

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

SELECT * FROM tbl LIMIT 95,18446744073709551615;

SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

SET @a=1;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @a;

SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

SELECT * FROM t1, t2 FOR SHARE OF t1 FOR UPDATE OF t2;