mysql 排序 a-z_MySQL-排序结果

mysql 排序 a-z

MySQL-排序结果 (MySQL - Sorting Results)

We have seen the SQL SELECT command to fetch data from a MySQL table. When you select rows, the MySQL server is free to return them in any order, unless you instruct it otherwise by saying how to sort the result. But, you sort a result set by adding an ORDER BY clause that names the column or columns which you want to sort.

我们已经看到了SQL SELECT命令从MySQL表中获取数据。 当您选择行时，MySQL服务器可以自由以任何顺序返回它们，除非您通过说出如何对结果排序来另外指示。 但是，您可以通过添加一个ORDER BY子句对结果集进行排序，该子句为要排序的一个或多个列命名。

句法 (Syntax)

The following code block is a generic SQL syntax of the SELECT command along with the ORDER BY clause to sort the data from a MySQL table.

以下代码块是SELECT命令的通用SQL语法，以及用于对MySQL表中的数据进行排序的ORDER BY子句。

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

• You can sort the returned result on any field, if that field is being listed out.

  如果列出了该字段，则可以在任何字段上对返回的结果进行排序。

• You can sort the result on more than one field.

  您可以在多个字段上对结果进行排序。

• You can use the keyword ASC or DESC to get result in ascending or descending order. By default, it's the ascending order.

  您可以使用关键字ASC或DESC来获得结果的升序或降序。 默认情况下，这是升序。

• You can use the WHERE...LIKE clause in the usual way to put a condition.

  您可以按常规方式使用WHERE ... LIKE子句来放置条件。

在命令提示符处使用ORDER BY子句 (Using ORDER BY clause at the Command Prompt)

This will use the SQL SELECT command with the ORDER BY clause to fetch data from the MySQL table – tutorials_tbl.

这将使用带有SELECT ORDER BY子句SQL SELECT命令从MySQL表– tutorials_tbl中获取数据。

例 (Example)

Try out the following example, which returns the result in an ascending order.

尝试以下示例，该示例以升序返回结果。

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl ORDER BY tutorial_author ASC
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_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.42 sec)

mysql>

Verify all the author names that are listed out in the ascending order.

验证以升序列出的所有作者姓名。

在PHP脚本中使用ORDER BY子句 (Using ORDER BY clause inside a PHP Script)

You can use a similar syntax of the ORDER BY clause into the PHP function – mysql_query(). This function is used to execute the SQL command and later another PHP function mysql_fetch_array() can be used to fetch all the selected data.

您可以在PHP函数mysql_query()中使用类似ORDER BY子句的语法。 该函数用于执行SQL命令，以后可以使用另一个PHP函数mysql_fetch_array()来获取所有选定的数据。

例 (Example)

Try out the following example, which returns the result in a descending order of the tutorial authors.

尝试以下示例，该示例按教程作者的降序返回结果。

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   $sql = 'SELECT tutorial_id, tutorial_title, 
      tutorial_author, submission_date
      FROM tutorials_tbl
      ORDER BY  tutorial_author DESC';

   mysql_select_db('TUTORIALS');
   $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['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
   } 
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

翻译自: https://www.tutorialspoint.com/mysql/mysql-sorting-results.htm

mysql 排序 a-z