我们知道从 MySQL 表中使用 SQL SELECT 语句来读取数据。
如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪中方式来进行排序，再返回搜索结果。
本章节使用的数据库结构及数据下载：web3.sql。
以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：
| SELECT field1, field2,...fieldN table_name1, table_name2... |
| ORDER BY field1, [field2...] [ASC [DESC]] |
以下将在 SQL SELECT 语句中使用 ORDER BY 子句来读取MySQL 数据表 web3_tbl 中的数据：
尝试以下实例，结果将按升序排列
| root@host# mysql -u root -p password; |
| Enter password:******* |
| mysql> use RUNOOB; |
| Database changed |
| mysql> SELECT * from web3_tbl ORDER BY web3_author ASC; |
| +-----------+---------------+---------------+-----------------+ |
| | web3_id | web3_title | web3_author | submission_date | |
| +-----------+---------------+---------------+-----------------+ |
| | 2 | Learn MySQL | Abdul S | 2007-05-24 | |
| | 1 | Learn PHP | John Poul | 2007-05-24 | |
| | 3 | JAVA Tutorial | Sanjay | 2007-05-06 | |
| +-----------+---------------+---------------+-----------------+ |
| 3 rows in set (0.00 sec) |
| mysql> SELECT * from web3_tbl ORDER BY web3_author DESC; |
| +-----------+---------------+---------------+-----------------+ |
| | web3_id | web3_title | web3_author | submission_date | |
| +-----------+---------------+---------------+-----------------+ |
| | 3 | JAVA Tutorial | Sanjay | 2007-05-06 | |
| | 1 | Learn PHP | John Poul | 2007-05-24 | |
| | 2 | Learn MySQL | Abdul S | 2007-05-24 | |
| +-----------+---------------+---------------+-----------------+ |
| 3 rows in set (0.00 sec) |
| mysql> |
尝试以下实例，查询后的数据按 web3_author 字段的降序排列后返回。
| <?php |
| $dbhost = 'localhost:3036'; |
| $dbuser = 'root'; |
| $dbpass = 'rootpassword'; |
| $conn = mysql_connect($dbhost, $dbuser, $dbpass); |
| if(! $conn ) |
| { |
| die('Could not connect: ' . mysql_error()); |
| } |
| $sql = 'SELECT web3_id, web3_title, |
| web3_author, submission_date |
| FROM web3_tbl |
| ORDER BY web3_author DESC'; |
| mysql_select_db('RUNOOB'); |
| $retval = mysql_query( $sql, $conn ); |
| if(! $retval ) |
| { |
| die('Could not get data: ' . mysql_error()); |
| } |
| while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) |
| { |
| echo "Tutorial ID :{$row['web3_id']} <br> ". |
| "Title: {$row['web3_title']} <br> ". |
| "Author: {$row['web3_author']} <br> ". |
| "Submission Date : {$row['submission_date']} <br> ". |
| "--------------------------------<br>"; |
| } |
| echo "Fetched data successfully\n"; |
| mysql_close($conn); |
| ?> |
