MySQL select statement
Description
MySQL SELECT statement retrieves zero or more rows from one or more tables or temporary tables in a database.
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' export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
Arguments
| Name | Descriptions |
|---|---|
| SELECT | SELECT statement is used to fetch rows or records from one or more tables. |
| * , ALL | Indicating all columns. |
| column | Columns or list of columns. |
| table | Indicates the name of the table from where the rows will be retrieved. |
| DISTINCT | DISTINCT clause is used to retrieve unique rows from a table. |
| DISTINCTROW | DISTINCTROW is a synonym for DISTINCT. |
| HIGH_PRIORITY | If used with SELECT, the associated query obtains higher priority than a update statement. It runs even if the table on which the query is applied is locked by an update query. It can be used only with MyISAM, MEMORY, and MERGE storage engines. And can be used only if the associated query is very fast as well as done at once. If a SELECT statements is part of a UNION, you can not use HIGH_PRIORITY along with. |
| STRAIGHT_JOIN | If used with SELECT, associated tables are joined in the order they are arranged in the corresponding FROM clause. This can not be used to make a query fast and also can be used in the table_references list. |
| SQL_SMALL_RESULT | Can be used with GROUP BY or DISTINCT. It tells the optimizer (a component of DataBase system which determines to most efficient way of executing a query) that the result set is small, so use fast temporary tables to store the resulting table instead of using sorting. |
| SQL_BIG_RESULT | Can be used with GROUP BY or DISTINCT. It tells the optimizer (a component of DataBase system which determines to most efficient way of executing a query) that the result set has many rows, so use disk-based temporary tables if needed, and prefer sorting to use a temporary table with a key on the GROUP BY elements. |
| SQL_BUFFER_RESULT | It forces the result to be put into a temporary table. If used, MySQL can free the table locks early and takes less time to send the result set to the client where it may take long time. It can not be used for subqueries or following UNION. |
| SQL_CACHE | It tells MySQL to store the result in the query cache (A DataBase system component which stores the text of a SELECT statement along with the corresponding result sent to the client ) if it is cacheable. The value of the query_cache_type system variable is 2 or DEMAND. |
| SQL_NO_CACHE | It tells MySQL not to store the result in the query cache (A DataBase system component which stores the text of a SELECT statement along with the corresponding result sent to the client ). It can also be used for views if it accompanies a SELECT statement. |
| SQL_CALC_FOUND_ROWS | It tells MySQL to calculate the number of would be rows in a result set. While calculating rows in this fashion, LIMIT clause is ignored. The number of rows can then be retrieved with SELECT FOUND_ROWS(). |
| select_expr | An expression. |
| FROM | This clause is used after SELECT and preceding tables or subqueries. |
| table_references | Name of the tables used in a SELECT statement. |
| WHERE | The conditions are supplied after this keyword (in a select statement). |
| where_condition | Conditions placed after WHERE. |
| GROUP BY | If used, the results returned from the field name used after GROUP BY clause is grouped together in result set. |
| col_name | Name of the column or columns or fields. |
| expr | An expression. |
| position | Refers to the position of columns beginning with 1. |
| ASC | If used, results are returned in ascending order. |
| DESC | If used, results are returned in descending order. |
| WITH ROLLUP | This modifier can be used with GROUP BY clause. If used, extra rows are added to the summary output. |
| HAVING | This modifier can be used with GROUP BY clause and aggregate functions. Can not be used with WHERE clause. |
| ORDER BY | MySQL ORDER BY clause specifies the order in which columns are sorted while retrieving data in a SELECT statement. |
| LIMIT | It is always followed by one or two numeric arguments. The first argument is the offset of the first row to be returned and second argument is the maximum number of rows returned in the result set. |
| PROCEDURE | This clause names a procedure that should process the data in the result set. |
| INTO OUTFILE | Takes a backup of the associated table in a file specified. |
| INTO DUMPFILE | If used instead of INTO OUTFILE, only one row is written into the file. Useful if BLOB values are to be stored in a file. |
| var_name | A variable name to store data temporarily. |
| FOR UPDATE | If used with a storage engine that uses page or row locks, till the end of the transaction, rows checked by the query are write-locked. |
| LOCK IN SHARE MODE | If used, a shared lock is set upon the rows examined by the associated query. So, other transactions can read the examined rows, can not update or delete those rows. |
In the consequent pages of w3resource MySQL tutorial, you will find detail discussion accompanied by examples (often with PHP codes) of the said arguments of the SELECT statement.
MySql selecting all data
Description
MySQL SELECT statement without any condition retrieves all records from a table.
Syntax
SELECT * FROM <table_name>
Arguments
| Name | Descriptions |
|---|---|
| SELECT | SELECT statement is used to fetch rows or records from one or more tables. |
| * | Indicating all columns. |
| table_name | Name of the table. |
Example
Sample table : publisher
Code
SELECT * FROM publisher;
Explanation
This SELECT statement will retrieve all data from publisher table.
Output
PHP script
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>example-select-all-data - php mysql examples | w3resource</title>
</head>
<body>
<?php
echo "<h2>List of the publishers with other detail : </h2>";
echo "<table border='1' style='border-collapse: collapse;border-color: silver;'>";
echo "<tr style='font-weight: bold;'>";
echo "<td width='200' align='center'>Publisher's ID</td><td width='200'align='center'>Publisher's Name</td><td width='200' align='center'>Publisher's City</td><td width='200' align='center'>Publisher's Country</td><td width='200' align='center'>Country Office</td><td width='200' align='center'>Number of branches</td><td width='200' align='center'>Date of Establishment</td>";
echo "</tr>";
include("../dbopen.php");
$result = mysql_query("SELECT * FROM publisher");
while($row=mysql_fetch_array($result))
{
echo "<tr>";
echo "<td align='center' width='200'>" . $row['pub_id'] . "</td>";
echo "<td align='center' width='200'>" . $row['pub_name'] . "</td>";
echo "<td align='center' width='100'>" . $row['pub_city'] . "</td>";
echo "<td align='center' width='100'>" . $row['country'] . "</td>";
echo "<td align='center' width='100'>" . $row['country_office'] . "</td>";
echo "<td align='center' width='100'>" . $row['no_of_branch'] . "</td>";
echo "<td align='center' width='100'>" . $row['estd'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>
</body>
</html>
