Kalozera wa Nkhani
Kodi mungagwiritse ntchito bwanji inner join ndi matebulo angapo mu database ya MySQL ? Kodi left join imagwira ntchito bwanji?
Mysql kugwiritsa ntchito mgwirizano
M'mitu yapitayi, taphunzira momwe tingawerengere deta kuchokera patebulo limodzi, lomwe ndi losavuta, koma muzogwiritsira ntchito zenizeni nthawi zambiri zimakhala zofunikira kuwerenga deta kuchokera ku matebulo angapo a deta.
M'mutu uno tikuwonetsani momwe mungagwiritsire ntchito JOIN ya MySQL kuti mufufuze deta pamagome awiri kapena kuposerapo.
Mutha kugwiritsa ntchito JOIN ya Mysql mu SELECT, UPDATE ndi DELETE mawu kuti mulowe nawo pamafunso ambiri.
JOIN yagawidwa m'magulu atatu otsatirawa malinga ndi ntchito yake:
- INNER JOIN (kujowina mkati, kapena kujowina): Pezani zolemba zokhala ndi magawo ofanana pamatebulo awiri.
- LEFT JOIN (kujowina kumanzere):Pezani zolemba zonse patebulo lakumanzere, ngakhale palibe zolemba zofananira patebulo lakumanja.
- KULIMBIKITSA JOIN (kumanja kujowina): Mosiyana ndi LEFT JOIN, imagwiritsidwa ntchito kupeza zolembedwa zonse patebulo lakumanja, ngakhale patebulo lakumanzere palibe zofananira.
Kugwiritsa ntchito INNER JOIN mu Command Prompt
Tili ndi matebulo awiri tcount_tbl ndi chenweiliang_tbl munkhokwe ya chenweiliang.Deta ya matebulo awiri a data ndi awa:
Chitsanzo
Yesani zitsanzo zotsatirazi:
data yoyeserera
mysql> use chenweiliang; Database changed mysql> SELECT * FROM tcount_tbl; +---------------+--------------+ | chenweiliang_author | chenweiliang_count | +---------------+--------------+ | 陈沩亮博客 | 10 | | chenweiliang.com | 20 | | Google | 22 | +---------------+--------------+ 3 rows in set (0.01 sec) mysql> SELECT * from chenweiliang_tbl; +-----------+---------------+---------------+-----------------+ | chenweiliang_id | chenweiliang_title | chenweiliang_author | submission_date | +-----------+---------------+---------------+-----------------+ | 1 | 学习 PHP | 陈沩亮博客 | 2017-04-12 | | 2 | 学习 MySQL | 陈沩亮博客 | 2017-04-12 | | 3 | 学习 Java | chenweiliang.com | 2015-05-01 | | 4 | 学习 Python | chenweiliang.com | 2016-03-06 | | 5 | 学习 C | FK | 2017-04-05 | +-----------+---------------+---------------+-----------------+ 5 rows in set (0.01 sec)
Kenako, tidzagwiritsa ntchito MySQL's INNER JOIN (mutha kuchotsa INNER ndikugwiritsa ntchito JOIN, zotsatira zake ndi zomwezo) kuti mulumikizane ndi matebulo awiri omwe ali pamwambapa ndikuwerenga zomwe zili mu gawo la chenweiliang_author mu tebulo la chenweiliang_tbl ndi zomwe zili mu gawo la chenweiliang_count mu tebulo la tcount_tbl:
INNER JOIN
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a INNER JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | +-------------+-----------------+----------------+ 4 rows in set (0.00 sec)
Mawu omwe ali pamwambapa a SQL ndi ofanana ndi:
KUTI clause
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a, tcount_tbl b WHERE a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | +-------------+-----------------+----------------+ 4 rows in set (0.01 sec)
MySQL LEFT JOIN
Kujowina kumanzere kwa MySQL ndikosiyana ndi kujowina. MySQL LEFT JOIN iwerenga zonse zomwe zili patsamba lakumanzere, ngakhale tebulo lakumanja lilibe deta yofananira.
Chitsanzo
Yesani chitsanzo chotsatirachi, pogwiritsa ntchito chenweiliang_tbl ngati tebulo lakumanzere ndi tcount_tbl ngati tebulo lakumanja, kuti mumvetse momwe MySQL LEFT JOIN imagwirira ntchito:
KUCHOKERA JOIN
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a LEFT JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | | 5 | FK | NULL | +-------------+-----------------+----------------+ 5 rows in set (0.01 sec)
Muchitsanzo chapamwambachi, LEFT JOIN ikugwiritsidwa ntchito, ndipo mawuwa awerengera zonse zomwe zasankhidwa pa tebulo la chenweiliang_tbl kumanzere, ngakhale palibe mtengo wofanana wa chenweiliang_author patebulo tcount_tbl kumanja.
MySQL RIGHT JOIN
MySQL RIGHT JOIN iwerenga zonse zomwe zili patebulo lakumanja, ngakhale palibe deta yofananira patebulo kumanzere.
Chitsanzo
Yesani chitsanzo chotsatirachi, pogwiritsa ntchito chenweiliang_tbl ngati tebulo lakumanzere ndi tcount_tbl ngati tebulo lakumanja, kuti mumvetse momwe MySQL RIGHT JOIN imagwirira ntchito:
KULIMBIKITSA JOINANI
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a RIGHT JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | | NULL | NULL | 22 | +-------------+-----------------+----------------+ 5 rows in set (0.01 sec)
KUGWIRITSA NTCHITO KWAMBIRI kukugwiritsidwa ntchito mu chitsanzo chapamwambachi, mawuwa awerengera zonse zomwe zasankhidwa pa tebulo lamanja la tcount_tbl, ngakhale palibe chenweiliang_author field value patebulo lakumanzere chenweiliang_tbl.
Kugwiritsa ntchito JOIN mu PHP script
Ntchito ya mysqli_query() imagwiritsidwa ntchito mu PHP pochita mawu a SQL, mutha kugwiritsa ntchito mawu omwewo a SQL monga pamwambapa ngati parameter ku mysqli_query() ntchito.
Yesani chitsanzo ichi:
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 a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a INNER JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author';
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>陈沩亮博客 MySQL JOIN 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>作者</td><td>登陆次数</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
echo "<tr><td> {$row['chenweiliang_id']}</td> ".
"<td>{$row['chenweiliang_author']} </td> ".
"<td>{$row['chenweiliang_count']} </td> ".
"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>Tikukhulupirira kuti nkhani yakuti "Kugwiritsa ntchito MySQL Database inner join kudutsa matebulo angapo? Kugwira bwino ntchito kwa left join" yomwe yagawidwa pa blog ya Chen Weiliang ( https://www.chenweiliang.com/ ) idzakuthandizani.
Khalani omasuka kugawana ulalo wa nkhaniyi: https://www.chenweiliang.com/cwl-488.html