next up previous contents
Next: Join 結合查詢 Up: Clause 紀錄詢問條件 Previous: Clause 紀錄詢問條件   Contents

Sorting 排序

  1. 排序的語法 ORDER BY 經常與 WHERE ... LIKE 條件一起使用。可以使用任何一個欄位排序,也可以多個欄位一起排序,排序結果可以「升序」或「降序」排列。
    [root@kvm8 ~]# mysql -uroot -p123qwe \
    -e "use dyw; select * from dyw_tbl order by dyw_id asc;"
    +--------+-----------+------------+-----------------+
    | dyw_id | dyw_title | dyw_author | submission_date |
    +--------+-----------+------------+-----------------+
    |      1 | c++       | peter      | 2014-12-24      |
    |      3 | MySQL     | dywang     | 2014-12-12      |
    +--------+-----------+------------+-----------------+
    [root@kvm8 ~]# mysql -uroot -p123qwe \
    -e "use dyw; select * from dyw_tbl order by dyw_id desc;"
    +--------+-----------+------------+-----------------+
    | dyw_id | dyw_title | dyw_author | submission_date |
    +--------+-----------+------------+-----------------+
    |      3 | MySQL     | dywang     | 2014-12-12      |
    |      1 | c++       | peter      | 2014-12-24      |
    +--------+-----------+------------+-----------------+
    
  2. php 程式的查詢變數 $sql 加入 ORDER BY dywphp_author DESC 條件。
    [root@kvm8 ~]# cd /var/www/html/
    [root@kvm8 html]# vim sorting.php
    [root@kvm8 html]# cat sorting.php
    <?php
    $dbhost = 'localhost:3306';
    $dbuser = 'root';
    $dbpass = '123qwe';
    $conn = mysql_connect($dbhost, $dbuser, $dbpass);
    if(! $conn )
    {
      die('Could not connect: ' . mysql_error());
    }
    $sql = 'SELECT dywphp_id, dywphp_title, 
                   dywphp_author, submission_date
            FROM dywphp_tbl
            ORDER BY  dywphp_author DESC';
    
    mysql_select_db('dywphp');
    $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['dywphp_id']}  <br> ".
             "Title: {$row['dywphp_title']} <br> ".
             "Author: {$row['dywphp_author']} <br> ".
             "Submission Date : {$row['submission_date']} <br> ".
             "--------------------------------<br>";
    } 
    echo "Fetched data successfully\n";
    mysql_close($conn);
    ?>
    
  3. 開啟瀏覽器,連上網址 http://kvm8.deyu.wang/sorting.php,出現兩筆紀錄且以 ID 降序排列。
    Image sorting



2018-04-19