Maqolalar katalogi
MySQL ko'p maydonli saralashmi? MySQL so'rov bayonoti/funksiyasi bo'yicha kamayish/o'sish tartibida tartiblash
MySQL tartiblash
MySQL jadvalidagi ma'lumotlarni o'qish uchun SQL SELECT iborasidan foydalanishni bilamiz.
Agar o'qilgan ma'lumotlarni saralashimiz kerak bo'lsa, biz MySQLning ORDER BY bandidan foydalanib, qaysi maydon va saralash usulidan foydalanishni xohlayotganimizni belgilashimiz va keyin qidiruv natijalarini qaytarishimiz mumkin.
grammatika
Quyida maʼlumotlarni qaytarishdan oldin soʻrov maʼlumotlarini saralash uchun ORDER BY bandidan foydalanadigan SQL SELECT iborasi keltirilgan:
SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1, [field2...] [ASC [DESC]]
- Saralangan so'rov natijalarini qaytarish uchun har qanday maydonni tartiblash sharti sifatida ishlatishingiz mumkin.
- Saralash uchun bir nechta maydonlarni o'rnatishingiz mumkin.
- So'rov natijalarini o'sish yoki kamayish tartibida tartiblash uchun ASC yoki DESC kalit so'zlaridan foydalanishingiz mumkin.Odatiy bo'lib, u o'sish tartibida.
- Shartlarni belgilash uchun WHERE...LIKE bandlarini qo'shishingiz mumkin.
Buyruqlar satrida ORDER BY bandidan foydalanish
MySQL ma'lumotlar jadvalidagi chenweiliang_tbl ma'lumotlarini o'qish uchun SQL SELECT bayonotidagi ORDER BY bandidan quyidagilar foydalaniladi:
Misol
Quyidagi misollarni sinab ko'ring va natijalar o'sish va kamayish tartibida tartiblanadi.
SQL tartiblash
mysql> use chenweiliang; Database changed mysql> SELECT * from chenweiliang_tbl ORDER BY submission_date ASC; +-----------+---------------+---------------+-----------------+ | chenweiliang_id | chenweiliang_title | chenweiliang_author | submission_date | +-----------+---------------+---------------+-----------------+ | 3 | 学习 Java | chenweiliang.com | 2015-05-01 | | 4 | 学习 Python | chenweiliang.com | 2016-03-06 | | 1 | 学习 PHP | 陈沩亮博客 | 2017-04-12 | | 2 | 学习 MySQL | 陈沩亮博客 | 2017-04-12 | +-----------+---------------+---------------+-----------------+ 4 rows in set (0.01 sec) mysql> SELECT * from chenweiliang_tbl ORDER BY submission_date DESC; +-----------+---------------+---------------+-----------------+ | chenweiliang_id | chenweiliang_title | chenweiliang_author | submission_date | +-----------+---------------+---------------+-----------------+ | 1 | 学习 PHP | 陈沩亮博客 | 2017-04-12 | | 2 | 学习 MySQL | 陈沩亮博客 | 2017-04-12 | | 4 | 学习 Python | chenweiliang.com | 2016-03-06 | | 3 | 学习 Java | chenweiliang.com | 2015-05-01 | +-----------+---------------+---------------+-----------------+ 4 rows in set (0.01 sec)
chenweiliang_tbl jadvalidagi barcha ma'lumotlarni o'qing va submission_date maydoni bo'yicha o'sish tartibida tartiblang.
PHP skriptida ORDER BY bandidan foydalanish
Maʼlumotlarni olish uchun PHP funksiyasidan mysqli_query() va bir xil SQL SELECT buyrugʻidan ORDER BY bandi bilan foydalanishingiz mumkin.
Bu funksiya SQL buyruqlarini bajarish va so'ngra PHP mysqli_fetch_array() funksiyasi orqali so'ralgan barcha ma'lumotlarni chiqarish uchun ishlatiladi.
Misol
Quyidagi misolni sinab ko'ring, so'ralgan ma'lumotlar submission_date maydonining kamayish tartibida qaytariladi.
MySQL-ni sinov bo'yicha BUYURTISH:
<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
$sql = 'SELECT chenweiliang_id, chenweiliang_title,
chenweiliang_author, submission_date
FROM chenweiliang_tbl
ORDER BY submission_date ASC';
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>陈沩亮博客 MySQL ORDER BY 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
echo "<tr><td> {$row['chenweiliang_id']}</td> ".
"<td>{$row['chenweiliang_title']} </td> ".
"<td>{$row['chenweiliang_author']} </td> ".
"<td>{$row['submission_date']} </td> ".
"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>Umid qilamizki, Chen Weiliangning blogida ( https://www.chenweiliang.com/ ) ulashilgan "MySQL ko'p maydonli shartlarni saralashmi? MySQL ORDER BY Oscending/Ascending Order Query Statement/Function" maqolasi siz uchun foydali bo'ladi.
Ushbu maqola havolasini ulashing: https://www.chenweiliang.com/cwl-476.html