Kalozera wa Nkhani
Kodi mukufuna kukonza malo ambiri a MySQL ? Kodi mukufuna kuti MySQL iongoleredwe potengera kutsika/kukwera kwa dongosolo/ntchito?
Mtundu wa MySQL
Tikudziwa kugwiritsa ntchito mawu a SQL SELECT kuti tiwerenge zambiri kuchokera pa tebulo la MySQL.
Ngati tikufunika kusanja deta yowerengedwa, tingagwiritse ntchito gawo la MySQL la ORDER BY kuti tifotokoze gawo lomwe tikufuna kugwiritsa ntchito ndi njira yosanja, kenako tibweze zotsatira zakusaka.
galamala
Zotsatirazi ndi mawu a SQL SELECT omwe amagwiritsa ntchito ORDER BY clause kuti asanthule deta yafunso asanabweze deta:
SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1, [field2...] [ASC [DESC]]
- Mutha kugwiritsa ntchito gawo lililonse ngati momwe mungasankhire kuti mubweze zotsatira zamafunso zosanjidwa.
- Mutha kukhazikitsa magawo angapo kuti musanthule.
- Mutha kugwiritsa ntchito mawu osakira a ASC kapena DESC kuti mukhazikitse zotsatira za mafunso kuti zisanjidwe mokwera kapena kutsika.Mwachikhazikitso, ili mu dongosolo lokwera.
- Mutha kuwonjezera PAPI...KUFANIZA ziganizo kuti mukhazikitse zinthu.
Pogwiritsa ntchito ORDER BY clause mu command prompt
Otsatirawa adzagwiritsa ntchito ORDER BY clause mu SQL SELECT statement kuti muwerenge zomwe zili mu MySQL data table chenweiliang_tbl:
Chitsanzo
Yesani zitsanzo zili m'munsizi ndipo zotsatira zake zidzasanjidwa mokwerera ndi kutsika.
Mtundu wa SQL
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)
Werengani zambiri zomwe zili pa tebulo la chenweiliang_tbl ndi kusanja mokwera potengera submission_date field.
Kugwiritsa ntchito ORDER BY clause mu PHP script
Mutha kugwiritsa ntchito PHP ntchito mysqli_query() ndi lamulo lomwelo la SQL SELECT ndi ORDER BY clause kuti mupeze deta.
Ntchitoyi imagwiritsidwa ntchito popanga malamulo a SQL ndikutulutsa zonse zomwe zafunsidwa kudzera mu ntchito ya PHP mysqli_fetch_array().
Chitsanzo
Yesani chitsanzo chotsatirachi, zomwe zafunsidwa zimabwezedwa mu dongosolo lotsikira la submission_date field.
MySQL ORDER BY test:
<?
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);
?>Tikukhulupirira kuti nkhani yakuti "MySQL Multi-Field Condition Sorting? MySQL ORDER BY Descending/Ascending Order Query Statement/Function" yomwe yagawidwa pa blog ya Chen Weiliang ( https://www.chenweiliang.com/ ) idzakuthandizani.
Khalani omasuka kugawana ulalo wa nkhaniyi: https://www.chenweiliang.com/cwl-476.html