SELECT

Sintassi

SELECT
    [ALL | DISTINCT | DISTINCTROW]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    espr_select [, espr_select ...]
    [ INTO OUTFILE 'nome_file' [export_options]
    | INTO DUMPFILE 'nome_file'
    | INTO nome_var [, nome_var] ]
    [ FROM riferimenti_tabelle
      [WHERE condizione_where]
      [GROUP BY {nome_col | espr | posizione} [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {nome_col | espr | posizione} [ASC | DESC], ...]
      [LIMIT {[scarto,] num_righe | num_righe OFFSET scarto}]
      [PROCEDURE nome_procedura(lista_argomenti)]
      [FOR UPDATE | LOCK IN SHARE MODE] ]

Spiegazione

SELECT serve ad estrarre righe selezionate da una o più tabelle, e può includere istruzioni UNION e subquery. Si vedano UNION e http://dev.mysql.com/doc/refman/5.1/en/subqueries.html.

• Ogniespr_select indica una colonna o dato che si vuole estrarre. In ogni istruzione deve essere presente almeno una espressione select. Si vedaEspressioni Select, sotto.

• La clausola FROM indica la tabella o le tabelle dalle quali devono essere estratte le righe. Si può usare un solo nome di tabella oppure un'espressione JOIN. Si veda JOIN per i dettagli.

• La clausola WHERE, se presente, indica la condizione o le condizioni che una riga deve soddisfare per essere selezionata. condizione_where è un'espressione che deve risultare vera perché le righe vengano selezionate. Se non è presente una clausola WHERE, vengono selezionate tutte le righe.
  • Nella clausola WHERE, è possibile usare qualsiasi funzione od operatore supportato da MariaDB, eccettuate le espressioni aggreganti (sommario). Si veda http://dev.mysql.com/doc/refman/5.1/en/functions.html

• La clausola ORDER BY ordina i risultati.

• La clausola LIMIT permette di restringere i risultati a un certo numero di righe, eventualmente con uno scarto iniziale.

• Le clausole GROUP BY e HAVING raggruppano le righe insieme quando queste hanno valori di colonne o valori calcolati in comune.

Con SELECT è anche possibile di estrarre righe calcolate senza accedere ad alcuna tabella.

Suggerimenti all'ottimizzatore

Esistono alcune opzioni che possono influenzare il piano di esecuzione.

HIGH_PRIORITY assegna all'istruzione una priorità più alta. Se la tabella ha un lock, le SELECT ad alta priorità vengono eseguite non appena il lock viene rilasciato, anche se ci sono altre istruzioni in coda. HIGH_PRIORITY si applica solo se lo Storage Engine supporta soltanto i lock a livello di tabella (MyISAM, MEMORY, MERGE).

Se la variabile di sistema query_cache_type è impostata a 2 o a DEMAND, e l'istruzione è compatibile con la cache, SQL_CACHE fa sì che la query venga scritta nella cache e SQL_NO_CACHE fa sì che la query non venga scritta. Nel caso delle UNION, SQL_CACHE e SQL_NO_CACHE devono essere specificate nella prima query.

SQL_BUFFER_RESULT forza l'ottimizzatore a utilizzare una tabella temporanea per elaborare il risultato. Ciò è utile per liberare i lock appena possibile.

SQL_SMALL_RESULT e SQL_BIG_RESULT dicono all'ottimizzatore se il risultato è molto grande oppure no. Solitamente, le operazioni GROUP BY e DICTINCT vengono eseguite con l'ausilio di una tabella temporanea. Questa tecnica non è conveniente solo se il risultato è molto grande. L'ottimizzatore sa automaticamente se il risultato è molto grande, ma è possibile forzarlo a utilizzare una tabella temporanea con SQL_SMALL_RESULT, o evitare la tabella temporanea con SQL_BIG_RESULT.

STRAIGHT_JOIN si applica alle JOIN e dice all'ottimizzatore che le tabelle devono essere lette nell'ordine in cui appaiono nella SELECT. Per le tabelle const e system a volte questa opzione viene ignorata.

SQL_CALC_FOUND_ROWS si applica solo quando è presente la clausola LIMIT. Se questa opzione è presente, MariaDB conta quante righe soddisfarebbero la query, se non ci fosse LIMIT. Tale numero può essere estratto con la query successiva, utilizzando FOUND_ROWS().

Per ulteriori informazioni su queste opzioni, si veda How to force query plans.

Select Expressions

A SELECT statement must contain one or more select expressions, separated by commas. Each select expression can be one of the following:

• The name of a column.
• Any expression using functions and operators.
• * to select all columns from all tables in the FROM clause.
• tbl_name.* to select all columns from just the table tbl_name.

When specifying a column, you can either use just the column name or qualify the column name with the name of the table using tbl_name.col_name. The qualified form is useful if you are joining multiple tables in the FROM clause. If you do not qualify the column names when selecting from multiple tables, MariaDB will try to find the column in each table. It is an error if that column name exists in multiple tables.

You can quote column names using backticks. If you are qualifying column names with table names, quote each part separately as `tbl_name`.`col_name`.

If you use any grouping functions in any of the select expressions, all rows in your results will be implicitly grouped, as if you had used GROUP BY NULL.

DISTINCT

A query may produce some identical rows. By default, all rows are retrieved, even when their values are the same. To explicitly specify that you want to retrieve identical rows, use the ALL option. If you want duplicates to be removed from the resultset, use the DISTINCT option. DISTINCTROW is a synonym for DISTINCT. See also COUNT DISTINCT.

ORDER BY

Use the ORDER BY clause to order the results that are returned from a SELECT statement. You can specify just a column or use any expression with functions. If you are using the GROUP BY clause, you can use grouping functions in ORDER BY. Ordering is done after grouping.

You can use multiple ordering expressions, separated by commas. Rows will be sorted by the first expression, then by the second expression if they have the same value for the first, and so on.

You can use the keywords ASC and DESC after each ordering expression to force that ordering to be ascending or descending, respectively. Ordering is ascending by default.

You can also use a single integer as the ordering expression. If you use an integer n, the results will be ordered by the nth column in the select expression.

When string values are compared, they are compared as if by the STRCMP function. STRCMP ignores trailing whitespace and may normalize characters and ignore case, depending on the collation in use.

LIMIT

Use the LIMIT clause to restrict the number of returned rows. When you use a single integer n with LIMIT, the first n rows will be returned. Use the ORDER BY clause to control which rows come first. You can also select a number of rows after an offset using either of the following:

LIMIT offset, row_count
LIMIT row_count OFFSET offset

When you provide an offset m with a limit n, the first m rows will be ignored, and the following n rows will be returned.

Beginning in MariaDB 5.5.21, there is a LIMIT ROWS EXAMINED optimization which provides the means to terminate the execution of SELECT statements which examine too many rows, and thus use too many resources. See the LIMIT ROWS EXAMINED page for details.

GROUP BY

Use the GROUP BY clause to group rows together that have the same value in one or more column, or the same computed value using expressions with any functions and operators except grouping functions. When you use a GROUP BY clause, you will get a single result row for each group of rows that have the same value for the expression given in GROUP BY.

When grouping rows, grouping values are compared as if by the = operator. For string values, the = operator ignores trailing whitespace and may normalize characters and ignore case, depending on the collation in use.

You can use any of the grouping functions in your select expression. Their values will be calculated based on all the rows that have been grouped together for each result row. If you select a non-grouped column or a value computed from a non-grouped column, it is undefined which row the returned value is taken from.

You can use multiple expressions in the GROUP BY clause, separated by commas. Rows are grouped together if they match on each of the expressions.

You can also use a single integer as the grouping expression. If you use an integer n, the results will be grouped by the nth column in the select expression.

The WHERE clause is applied before the GROUP BY clause. It filters non-aggregated rows before the rows are grouped together. To filter grouped rows based on aggregate values, use the HAVING clause. The HAVING clause takes any expression and evaluates it as a boolean, just like the WHERE clause. You can use grouping functions in the HAVING clause. As with the select expression, if you reference non-grouped columns in the HAVING clause, the behavior is undefined.

By default, if a GROUP BY clause is present, the rows in the output will be sorted by the expressions used in the GROUP BY. You can also specify ASC or DESC (ascending, descending) after those expressions, like in ORDER BY. The default is ASC.

If you want the rows to be sorted by another field, you can add an explicit ORDER BY. If you don't want the result to be ordered, you can add ORDER BY NULL.

GROUP BY Examples

Consider the following table that records how many times each user has played and won a game:

CREATE TABLE plays (name VARCHAR(16), plays INT, wins INT);
INSERT INTO plays VALUES ("John", 20, 5), ("Robert", 22, 8), ("Wanda", 32, 8), ("Susan", 17, 3);

Get a list of win counts along with a count:

MariaDB> SELECT wins, COUNT(*) FROM plays GROUP BY wins;
+------+----------+
| wins | COUNT(*) |
+------+----------+
|    3 |        1 |
|    5 |        1 |
|    8 |        2 |
+------+----------+
3 rows in set (0.00 sec)

The GROUP BY expression can be a computed value, and can refer back to an identifer specified with AS. Get a list of win averages along with a count:

MariaDB> SELECT (wins / plays) AS winavg, COUNT(*) FROM plays GROUP BY winavg;
+--------+----------+
| winavg | COUNT(*) |
+--------+----------+
| 0.1765 |        1 |
| 0.2500 |        2 |
| 0.3636 |        1 |
+--------+----------+
3 rows in set (0.00 sec)

You can use any grouping function in the select expression. For each win average as above, get a list of the average play count taken to get that average:

MariaDB> SELECT (wins / plays) AS winavg, AVG(plays) FROM plays GROUP BY winavg;
+--------+------------+
| winavg | AVG(plays) |
+--------+------------+
| 0.1765 |    17.0000 |
| 0.2500 |    26.0000 |
| 0.3636 |    22.0000 |
+--------+------------+
3 rows in set (0.00 sec)

You can filter on aggregate information using the HAVING clause. The HAVING clause is applied after GROUP BY and allows you to filter on aggregate data that is not available to the WHERE clause. Restrict the above example to results that involve an average number of plays over 20:

MariaDB> SELECT (wins / plays) AS winavg, AVG(plays) FROM plays GROUP BY winavg HAVING AVG(plays) > 20;
+--------+------------+
| winavg | AVG(plays) |
+--------+------------+
| 0.2500 |    26.0000 |
| 0.3636 |    22.0000 |
+--------+------------+
2 rows in set (0.00 sec)

PROCEDURE clause

The PROCEDURE clause passes the whole resultset to a Procedure which will process it. Those Procedures are not Stored Procedures. They can only be written in C language.

Actually, the only avaible procedure is ANALYSE, which examines the resultset and suggests the optimal datatypes for each column. It is defined in the sql/sql_analyse.cc file, and can be used as an example to create more Procedures.

LOCK IN SHARE MODE and FOR UPDATE clauses

InnoDB/XtraDB supports row-level locking. SELECTed rows can be locked using LOCK IN SHARE MODE or FOR UPDATE. In both cases, a lock is acquired on the rows read by the query, and it will be released when the current transaction is committed.

When LOCK IN SHARE MODE is specified, the statement will wait until all transactions that have modified the rows are committed. Then, a write lock is acquired. All transactions can read the rows, but if they want to modify them, they have to wait until your transaction is committed.

FOR UPDATE only applies when autocommit is set to 1 or the SELECT is enclosed in a transaction. A lock is acquired on the rows, and other transactions are presented from writing the rows, acquire locks, and from reading them (unless their isolation level is READ UNCOMMITTED).

If the isolation level is set to SERIALIZABLE and autocommit is set to 0, all plain SELECT statements are converted to SELECT ... LOCK IN SHARE MODE.