Maqolalar katalogi
MySQLKo'p maydonli shartli saralash?MySQL o'sish bo'yicha so'rov bayonoti/funktsiyasi bo'yicha tartiblash
MySQL tartiblash
MySQL jadvalidagi ma'lumotlarni o'qish uchun SQL SELECT iborasidan foydalanishni bilamiz.
Agar biz o'qilgan ma'lumotlarni saralashimiz kerak bo'lsa, biz MySQL-dan foydalanishimiz mumkin Buyurtma bo'yicha bandi qaysi maydonni qaysi yoʻl bilan saralashni xohlayotganingizni belgilang va keyin qidiruv natijalarini qaytaring.
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 qilamanki, Chen Veyliang blogi ( https://www.chenweiliang.com/ ) siz uchun foydali bo'lgan "MySQL ko'p maydonli shartli saralash? MySQL-ni kamayish va o'sish bo'yicha so'rov bayonoti/funksiyasi bo'yicha tartiblash" bilan o'rtoqlashdi.
Ushbu maqolaning havolasini baham ko'rish uchun xush kelibsiz:https://www.chenweiliang.com/cwl-476.html