Isalathiso senqaku
Ungayisebenzisa njani i-inner join ngeetafile ezininzi kwi-database ye-MySQL ? Ingakanani indlela esebenza ngayo i-left join?
Mysql Ukusetyenziswa koqhagamshelwano
Kwizahluko zangaphambili, siye safunda indlela yokufunda idatha kwitheyibhile enye, elula, kodwa kwizicelo zangempela kudla ngokuyimfuneko ukufunda idatha kwiitafile ezininzi zedatha.
Kwesi sahluko, siya kukwazisa indlela yokusebenzisa i-MySQL's JOIN ukubuza idatha kwiitafile ezimbini okanye ngaphezulu.
Ungasebenzisa i-Mysql's JOIN kwi-KHETHA, UKUHLAZIYA kunye ne-CIMA inkcazo ukujoyina imibuzo emininzi yetafile.
JOIN yahlulwe ngokwezi ndidi zintathu zilandelayo ngokwemisebenzi yayo:
- INNER JOIN (ukudibanisa kwangaphakathi, okanye ukujoyina okulinganayo): Fumana iirekhodi zobudlelwane obuhambelana nentsimi kwiitheyibhile ezimbini.
- LEFT JOIN (joyina ngasekhohlo):Fumana zonke iirekhodi kwitheyibhile esekhohlo, nokuba akukho zirekhodi ezihambelanayo kwitheyibhile elungileyo.
- ELUNGELO JOIN (ekunene joyina): Ngokuchaseneyo ne-LEFT JOIN, isetyenziselwa ukufumana zonke iirekhodi kwitheyibhile ekunene, nokuba akukho zirekhodi ezihambelanayo ezihambelanayo kwitheyibhile esekhohlo.
Ukusebenzisa INNER JOIN kwi-prompt yomyalelo
Sineetafile ezimbini tcount_tbl kunye ne-chenweiliang_tbl kuvimba wedatha we-chenweiliang. Iidatha kwiitheyibhile zedatha ezimbini zimi ngolu hlobo lulandelayo:
Umzekelo
Zama le mizekelo ilandelayo:
Idatha yomzekelo wovavanyo
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)
Okulandelayo, siza kusebenzisa i-INNER JOIN ye-MySQL (ungashiya i-INNER kwaye usebenzise i-JOIN, isiphumo siyafana) ukudibanisa iitheyibhile ezimbini ezingasentla kwaye ufunde amaxabiso entsimi ye-chenweiliang_author kwitheyibhile ye-chenweiliang_tbl kunye namaxabiso entsimi ye-chenweiliang_count ahambelanayo kwitheyibhile ye-tcount_tbl:
YENZA 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)
Le ngxelo ingentla yeSQL iyalingana no:
PHI igatya
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)
I-MySQL LENXELE JOIN
Ukudibanisa okushiyekileyo kwe-MySQL kwahlukile ekudibaneni. I-MySQL LEFT JOIN iya kufunda yonke idatha kwitafile yedatha ekhohlo, nokuba akukho datha ehambelanayo kwitafile ekunene.
Umzekelo
Zama lo mzekelo ulandelayo, usebenzisa i-chenweiliang_tbl njengetafile yasekhohlo kunye ne-tcount_tbl njengetafile yasekunene, ukuze uqonde ukusetyenziswa kwe-MySQL LEFT JOIN:
NGENXELE 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)
LEFT JOIN isetyenziswa kulo mzekelo ungasentla.Le nkcazo iyakufunda yonke idatha yendawo ekhethiweyo kwitheyibhile yedatha chenweiliang_tbl ekhohlo, nokuba akukho mhlaba uhambelanayo chenweiliang_author ixabiso kwitheyibhile tcount_tbl ekunene.
I-MySQL ELUNGILEYO JOIN
I-MySQL RIGHT JOIN iya kufunda yonke idatha kwitafile yedatha echanekileyo, nokuba akukho datha ehambelanayo kwitheyibhile ekhohlo.
Umzekelo
Zama lo mzekelo ulandelayo, usebenzisa i-chenweiliang_tbl njengetafile yasekhohlo kunye ne-tcount_tbl njengetafile yasekunene, ukuze uqonde ukusetyenziswa kwe-MySQL RIGHT JOIN:
ILUNGELO JOIN
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)
OKULUNGILEYO JOIN isetyenziswa kulo mzekelo ungasentla.Le nkcazo iyakufunda yonke idatha yendawo ekhethiweyo kwitafile yedatha ekunene tcount_tbl, nokuba akukho mhlaba uhambelanayo chenweiliang_umbhali ixabiso kwitheyibhile ekhohlo chenweiliang_tbl.
Ukusebenzisa i-JOIN kwimibhalo ye-PHP
mysqli_query() umsebenzi usetyenziswa kwi PHP ukwenza i SQL statement.Ungasebenzisa iSQL statement engentla njenge parameter ye mysqli_query() function.
Zama lo mzekelo ulandelayo:
I-MySQL i-ORDER ngovavanyo:
<?
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);
?>Ngethemba, inqaku elithi "Ukusebenzisa i-MySQL Database inner join kwiitafile ezininzi? Ukusebenza kakuhle kwe-left join" elabelwana ngalo kwibhlog kaChen Weiliang ( https://www.chenweiliang.com/ ) liza kuba luncedo kuwe.
Zive ukhululekile ukwabelana ngekhonkco leli nqaku: https://www.chenweiliang.com/cwl-488.html