SELECT
Syntax
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] select_expr [, select_expr ...] [ FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset [ROWS EXAMINED rows_limit] } | [OFFSET start { ROW | ROWS }] [FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }] ] procedure|[PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] [export_options] | INTO DUMPFILE 'file_name' | INTO var_name [, var_name] ] [FOR UPDATE lock_option | LOCK IN SHARE MODE lock_option]
export_options: [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ]
lock_option: [WAIT n | NOWAIT | SKIP LOCKED]
Contents
Description
SELECT
se utiliza para recuperar filas de una o más tablas. Puede incluir sentencias
UNION y subqueries.
- Each select_expr expresión indica la columna o dato que se desea recuperar. Debe indicar al menos una. Para más detalles, consulte Select Expressions más abajo.
- La cláusula
FROM
indica la/s tabla/s desde las que recuperar filas. Utilice un nombre de tabla o una expresiónJOIN
para relacionar varias tablas. Verlos detalles de JOIN. Si no se utiliza ninguna tabla, se puede indicar FROM DUAL. Esto no es obligatorio, pero se puede utilizar por compatibilidad.
- Cada tabla también se puede indicar como
db_name
.tabl_name
. Así mismo, cada columna se puede indicar comotbl_name
.col_name
e incluso comodb_name
.tbl_name
.col_name
. Esto permite escribir consultas que involucren vaias bases de datos. Para consultar los detalles de la sintaxis, vea Identifier Qualifiers.
- Si se incluye la cláusula
WHERE
, esta muestra la condición que deben cumplir las filas para ser seleccionadas.where_condition
debe ser ciertaTRUE
para que la fila sea devuelta. Si no se incluye una cláusua WHERE, la consulta devuelve todas las filas.
In the WHERE
clause, you can use any of the functions and
operators that MariaDB supports, except for aggregate (summary) functions. See Functions and Operators and Functions and Modifiers for use with GROUP BY (aggregate).
- Use the ORDER BY clause to order the results.
- Use the LIMIT clause allows you to restrict the results to only a certain number of rows, optionally with an offset.
- Use the GROUP BY and
HAVING
clauses to group rows together when they have columns or computed values in common.
SELECT can also be used to retrieve rows computed without reference to any table.
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 theFROM
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 and SELECT UNIQUE in Oracle mode.
INTO
The INTO
clause is used to specify that the query results should be written to a file or variable.
- SELECT INTO OUTFILE - formatting and writing the result to an external file.
- SELECT INTO DUMPFILE - binary-safe writing of the unformatted results to an external file.
- SELECT INTO Variable - selecting and setting variables.
The reverse of SELECT INTO OUTFILE
is LOAD DATA.
LIMIT
Restricts the number of returned rows. See LIMIT and LIMIT ROWS EXAMINED for details.
LOCK IN SHARE MODE/FOR UPDATE
See LOCK IN SHARE MODE and FOR UPDATE for details on the respective locking clauses.
OFFSET ... FETCH
MariaDB starting with 10.6
ORDER BY
Order a resultset. See ORDER BY for details.
PARTITION
Specifies to the optimizer which partitions are relevant for the query. Other partitions will not be read. See Partition Pruning and Selection for details.
PROCEDURE
Passes the whole result set to a C Procedure. See PROCEDURE and PROCEDURE ANALYSE (the only built-in procedure not requiring the server to be recompiled).
SKIP LOCKED
MariaDB starting with 10.6
The SKIP LOCKED clause was introduced in MariaDB 10.6.0.
This causes those rows that couldn't be locked (LOCK IN SHARE MODE or FOR UPDATE) to be excluded from the result set. An explicit NOWAIT
is implied here. This is only implemented on InnoDB tables and ignored otherwise.
SQL_CALC_FOUND_ROWS
When SQL_CALC_FOUND_ROWS
is used, then MariaDB will calculate how many rows would
have been in the result, if there would be no LIMIT clause. The result can be found by calling the function FOUND_ROWS() in your next sql statement.
max_statement_time clause
By using max_statement_time in conjunction with SET STATEMENT, it is possible to limit the execution time of individual queries. For example:
SET STATEMENT max_statement_time=100 FOR SELECT field1 FROM table_name ORDER BY field1;
WAIT/NOWAIT
Set the lock wait timeout. See WAIT and NOWAIT.
Examples
SELECT f1,f2 FROM t1 WHERE (f3<=10) AND (f4='y');
See Getting Data from MariaDB (Beginner tutorial), or the various sub-articles, for more examples.