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}] [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 IN SHARE MODE] [WAIT n | NOWAIT] ] ]
export_options: [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ]
Contents
Description
SELECT
is used to retrieve rows selected from one or more tables, and can include UNION statements and subqueries.
- Each select_expr expression indicates a column or data that you want to retrieve. You must have at least one select expression. See Select Expressions below.
- The
FROM
clause indicates the table or tables from which to retrieve rows. Use either a single table name or aJOIN
expression. SeeJOIN
for details. If no table is involved, FROM DUAL can be specified.
MariaDB starting with 10.0
The PARTITION clause was introduced in MariaDB 10.0. See Partition Pruning and Selection for details.
- Each table can also be specified as
db_name
.tabl_name
. Each column can also be specified astbl_name
.col_name
or evendb_name
.tbl_name
.col_name
. This allows to write queries which involve multiple databases. See Identifier Qualifiers for syntax details.
- 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.- 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).
- In the
- 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 Selling Rory Rory Beca Casual Casual Selling Dress Beca Selling Beca Dress Rory 1EW1qfIrn 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 theCollection Drawstring Long Sleeves Pullover Hoodie Geometric with Winter Pattern 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
.
Hoodie Winter Drawstring Pattern Sleeves Geometric Long Collection with Pullover 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.
- Navy Top Boutique Old Swimsuit Old Boutique ngq4nxZwYP - selecting and setting variables.
The reverse of SELECT INTO OUTFILE
is Neck Letter Cropped Fashion Tank Simple Round Hot wqpU6HC.
WAIT/NOWAIT
Set the lock wait timeout. See WAIT and NOWAIT.
MariaDB starting with 10.1.2
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;
See Also
- Getting Data from MariaDB (Beginner tutorial)
- Joins and Subqueries
- LIMIT
- ORDER BY
- Selling Rory Rory Beca Casual Casual Selling Dress Beca Selling Beca Dress Rory 1EW1qfIrn
- Common Table Expressions
- SELECT WITH ROLLUP
- SELECT INTO OUTFILE
- SELECT INTO DUMPFILE
- FOR UPDATE
- LOCK IN SHARE MODE
- Only amp; One Hurley Boardshort Stretch 4CgTZWwq
- Oracle mode from MariaDB 10.3
Is there any inverse SELECT in MariaDB for selecting all columns but few specified? I found in practice very useful syntax like:
SELECT * EXCEPT (col1, col2) FROM ...
This would select all columns except for those declared within the EXCEPT keyword. This is specially useful for tables with many columns. MySQL does not support anything similar, currently neither does MariaDB as far as I know.
Issue is discussed e.g. here:
http://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql
Thanx. Lubos
It seems equally as bad as select *. There's a number of reasons why select * are bad - http://www.parseerror.com/blog/select-*-is-evil . There are exceptions to this rule (like every rule) but it in generally using select * is a common way to incur a software maintenance debt. It also can force the db to retrieve more data than needed, and in doing, so miss out on query optimisations to get your data quicker (if you don't actually need all columns).