SELECT

Description

Retrieves data from a table.

Syntax

  1. SELECT
  2.     [ALL | DISTINCT ]
  3.     select_expr [, select_expr] [[AS] alias] ...
  4.     [INTO variable [, ...]]
  5.     [FROM table_references
  6.     [WHERE where_condition]
  7.     [GROUP BY {col_name | expr | position}
  8.       [ASC | DESC]]
  9.     [HAVING where_condition]
  10.     [ORDER BY {col_name | expr | position}
  11.       [ASC | DESC]] [ NULLS { FIRST | LAST } ]
  12.     [LIMIT {[offset,] row_count | row_count OFFSET offset}]

Explanations

The most commonly used clauses of SELECT statements are these:

select_expr

Each select_expr indicates a column that you want to retrieve. There must be at least one select_expr.

The list of select_expr terms comprises the select list that indicates which columns to retrieve. Terms specify a column or expression or can use *-shorthand:

  • A select list consisting only of a single unqualified * can be used as shorthand to select all columns from all tables:
  1. SELECT * FROM t1
  • tbl_name.* can be used as a qualified shorthand to select all columns from the named table:
  1. SELECT t1.*, t2.* FROM t1
  • A select_expr can be given an alias using AS alias_name.

table_references

  • The FROM table_references clause indicates the table or tables from which to retrieve rows.

  • You can refer to a table within the default database as tbl_name, or as db_name.tbl_name to specify a database explicitly. You can refer to a column as col_name, tbl_name.col_name, or db_name.tbl_name.col_name. You need not specify a tbl_name or db_name.tbl_name prefix for a column reference unless the reference would be ambiguous.

  • A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name.

WHERE

The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause.

GROUP BY

Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions.

HAVING

The HAVING clause, like the WHERE clause, specifies selection conditions.

ORDER BY

To sort in reverse order, add the DESC (descending) keyword to the name of the column in the ORDER BY clause that you are sorting by. The default is ascending order; this can be specified explicitly using the ASC keyword.

LIMIT

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement.

Examples

  1. create table t1 (spID int,userID int,score smallint);
  2. insert into t1 values (1,1,1);
  3. insert into t1 values (2,2,2);
  4. insert into t1 values (2,1,4);
  5. insert into t1 values (3,3,3);
  6. insert into t1 values (1,1,5);
  7. insert into t1 values (4,6,10);
  8. insert into t1 values (5,11,99);
  9. insert into t1 values (null,0,99);
  11. mysql> SELECT * FROM t1 WHERE spID>2 AND userID <2 || userID >=2 OR userID < 2 LIMIT 3;
  12. +------+--------+-------+
  13. | spid | userid | score |
  14. +------+--------+-------+
  15. | NULL |      0 |    99 |
  16. |    1 |      1 |     1 |
  17. |    2 |      2 |     2 |
  18. +------+--------+-------+
  20. mysql> SELECT userID,MAX(score) max_score FROM t1 WHERE userID <2 || userID > 3 GROUP BY userID ORDER BY max_score;
  21. +--------+-----------+
  22. | userid | max_score |
  23. +--------+-----------+
  24. |      1 |         5 |
  25. |      6 |        10 |
  26. |      0 |        99 |
  27. |     11 |        99 |
  28. +--------+-----------+
  30. mysql> select userID,count(score) from t1 group by userID having count(score)>1 order by userID;
  31. +--------+--------------+
  32. | userid | count(score) |
  33. +--------+--------------+
  34. |      1 |            3 |
  35. +--------+--------------+
  37. mysql> select userID,count(score) from t1 where userID>2 group by userID having count(score)>1 order by userID;
  38. Empty set (0.01 sec)s
  40. mysql> select * from t1 order by spID asc nulls last;
  41. +------+--------+-------+
  42. | spid | userid | score |
  43. +------+--------+-------+
  44. |    1 |      1 |     1 |
  45. |    1 |      1 |     5 |
  46. |    2 |      2 |     2 |
  47. |    2 |      1 |     4 |
  48. |    3 |      3 |     3 |
  49. |    4 |      6 |    10 |
  50. |    5 |     11 |    99 |
  51. | NULL |      0 |    99 |
  52. +------+--------+-------+

Constraints

  1. Table alias is not supported in GROUP BY.
  2. SELECT…FOR UPDATE clause is not supported now.
  3. INTO OUTFILE is limitedly support.