Chinyorwa Directory
MySQL databaseyemukati kujoinha multi-tafura kushandiswa? kuruboshwe kuruboshwe kujoinha kushandiswa kwakanaka
Mysql kushandiswa kwekubatanidza
Muzvitsauko zvakapfuura, takadzidza nzira yekuverenga data kubva kune imwe tafura, iyo iri nyore, asi mumashandisirwo chaiwo inowanzodiwa kuverenga data kubva kune akawanda data matafura.
Muchitsauko chino tichakuratidza mashandisiro eMySQL's JOIN kubvunza data mumatafura maviri kana anopfuura.
Iwe unogona kushandisa Mysql's JOIN muSARUDZA, UPDATE uye DELETE zvirevo kujoina akawanda-tafura mibvunzo.
JOIN yakakamurwa kuita zvikamu zvitatu zvinotevera maererano nebasa rayo:
- INNER JOIN (inner join, kana equijoin): Tora marekodhi ane minda inoenderana mumatafura maviri.
- LEFT JOIN (kujoinha kuruboshwe):Tora zvese zvinyorwa patafura yekuruboshwe, kunyangwe pasina marekodhi anoenderana mutafura yekurudyi.
- RIGHT JOIN (kujoinha kurudyi): Kusiyana ne LEFT JOIN, inoshandiswa kuwana ese marekodhi patafura yekurudyi, kunyangwe pasina marekodhi anowirirana patafura yekuruboshwe.
Kushandisa INNER JOIN mu Command Prompt
Tine matafura maviri tcount_tbl uye chenweiliang_tbl mudura rechenweiliang.Iyo data yematafura maviri e data ndeaya anotevera:
muenzaniso
Edza mienzaniso inotevera:
bvunzo muenzaniso data
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)
Zvadaro tichashandisa MySQLINNER JOIN (unogonawo kusiya INNER uye kushandisa JOIN, mhedzisiro yacho yakafanana)Kubatanidza matafura maviri ari pamusoro kuti uverenge kukosha kwechenweiliang_count field inoenderana neyese chenweiliang_author fields in the chenweiliang_tbl table in the tcount_tbl table:
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)
Chirevo chepamusoro cheSQL chakafanana ne:
KUPI 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
MySQL kuruboshwe kujoinha kwakasiyana nekujoinha. MySQL LEFT JOIN ichaverenga data rese riri kuruboshwe data tafura, kunyangwe tafura yekurudyi isina data rinoenderana.
muenzaniso
Edza mienzaniso inotevera kuti chenweiliang_tbl yetafura yekuruboshwe,tcount_tbl Kune tafura yekurudyi, nzwisisa mashandisirwo eMySQL LEFT JOIN:
LEFT 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)
Mumuenzaniso uri pamusoro apa, LEFT JOIN ndiyo inoshandiswa, uye chirevo ichi chichaverenga data yese yakasarudzwa yetafura yedata chenweiliang_tbl kuruboshwe, kunyangwe pasina kukosha kwendima ye chenweiliang_author patafura tcount_tbl kurudyi.
MySQL KODZERO JOIN
MySQL RIGHT JOIN ichaverenga data rese riri patafura yedata kurudyi, kunyangwe pasina data rinoenderana mutafura kuruboshwe.
muenzaniso
Edza mienzaniso inotevera kuti chenweiliang_tbl yetafura yekuruboshwe,tcount_tbl Kune tafura yekurudyi, nzwisisa mashandisirwo eMySQL RIGHT JOIN:
KODZERO 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)
KUPINDA KWERURUDZO kunoshandiswa mumuenzaniso uri pamusoro apa, chirevo ichi chinoverenga data rese rakasarudzwa retafura yedata rekurudyi tcount_tbl, kunyangwe pasina chenweiliang_author field value inoenderana nechekuruboshwe chenweiliang_tbl.
Kushandisa JOIN mune PHP script
Iyo mysqli_query() basa rinoshandiswa muPHP kuita SQL statement, unogona kushandisa imwecheteyo SQL statement sepamusoro separameter kune mysqli_query() basa.
Edza muenzaniso unotevera:
MySQL ORDER BY bvunzo:
<?
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);
?>Hope Chen Weiliang Blog ( https://www.chenweiliang.com/ ) yakagovaniswa "Multi-tafura kushandiswa kweMySQL database yemukati kujoinha? left Left Join Usage Efficiency", ichakubatsira.
Welcome to share link yechinyorwa chino:https://www.chenweiliang.com/cwl-488.html
Tikugashirei kuTeregiramu chiteshi cheChen Weiliang's blog kuti uwane zvichangobva kuitika!
📚 Iri gwara rine kukosha kukuru, 🌟Uyu mukana usingawanzo, usapotsa! ⏰⌛💨
Govera uye like kana uchida!
Kugovera kwako uye kuda ndiko kukurudzira kwedu kunoramba kuripo!