Sengoli sa Lingoloa
Mokhoa oa ho sebelisa inner join ka litafole tse ngata polokelongtshedimosetsong ea MySQL ? Bokgoni ba left join ke bofe?
Mysql tshebediso ya kgokelo
Likhaolong tse fetileng, re ithutile ho bala data ho tloha tafoleng e le 'ngoe, e batlang e le bonolo, empa ka likopo tsa sebele hangata ho hlokahala ho bala lintlha tse tsoang litafoleng tse ngata tsa data.
Khaolong ena re tla u bontša mokhoa oa ho sebelisa JOIN ea MySQL ho botsa data litafoleng tse peli kapa ho feta.
U ka sebelisa JOIN ea Mysql ho KHETHA, UPDATE le DELETE lipolelo ho ikopanya le lipotso tsa litafole tse ngata.
JOIN e batla e arotsoe ka mekhahlelo e meraro e latelang ho latela mosebetsi oa eona:
- INNER JOIN (kopanyo ea ka hare, kapa equijoin): Fumana lirekoto tse nang le masimo a tšoanang litafoleng tse peli.
- LEFT JOIN (kopanya ka ho le letšehali):Fumana lirekoto tsohle tafoleng e ka ho le letšehali, le haeba ho se na lirekoto tse tsamaellanang tafoleng e nepahetseng.
- KENYA KA HO LE letona (ho ikopanya le letona): Ho fapana le LEFT JOIN, e sebelisoa ho fumana lirekoto tsohle tafoleng e nepahetseng, leha ho se na lirekoto tse tsamaellanang tafoleng e ka letsohong le letšehali.
Ho sebelisa INNER JOIN ho Command Prompt
Re na le litafole tse peli tcount_tbl le chenweiliang_tbl sebakeng sa polokelo ea litaba tsa chenweiliang.Lintlha tsa litafole tse peli tsa data ke tse latelang:
Mohlala
Leka mehlala e latelang:
lintlha tsa mohlala oa teko
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)
Ka mor'a moo, re tla sebelisa INNER JOIN ea MySQL (u ka tlosa INNER 'me ua sebelisa JOIN, phello e tšoana) ho kopanya litafole tse peli tse ka holimo le ho bala boleng ba tšimo ea chenweiliang_author tafoleng ea chenweiliang_tbl le boleng ba tsona ba tšimo ea chenweiliang_count tafoleng ea 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)
Polelo e ka holimo ea SQL e lekana le:
MANE 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 LETSELA KENYA
Kopano e setseng ea MySQL e fapane le ho ikopanya. MySQL LEFT JOIN e tla bala lintlha tsohle tse tafoleng ea data e letšehali, le haeba tafole e nepahetseng e se na data e tsamaellanang.
Mohlala
Leka mohlala o latelang, o sebedisa chenweiliang_tbl jwalo ka tafole e ka letsohong le letshehadi le tcount_tbl jwalo ka tafole e ka letsohong le letona, ho utlwisisa tshebediso ya MySQL LEFT JOIN:
LETS'ELISI KENYA
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)
Mohlaleng o ka holimo, ho sebelisoa LEFT JOIN, 'me polelo ena e tla bala lintlha tsohle tse khethiloeng tsa leqephe la data chenweiliang_tbl ka ho le letšehali, le haeba ho se na boleng ba tšimo ba chenweiliang_author tafoleng tcount_tbl ka ho le letona.
MySQL TOKELO JOIN
MySQL RIGHT JOIN e tla bala lintlha tsohle tse tafoleng ea data ka ho le letona, le haeba ho se na data e tsamaellanang tafoleng e ka letsohong le letšehali.
Mohlala
Leka mohlala o latelang, o sebedisa chenweiliang_tbl jwalo ka tafole e ka letsohong le letshehadi le tcount_tbl jwalo ka tafole e ka letsohong le letona, ho utlwisisa tshebediso ya MySQL RIGHT JOIN:
TOKELO KENYA
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)
JOIN e nepahetseng e sebelisoa mohlaleng o ka holimo, polelo ena e tla bala lintlha tsohle tse khethiloeng tsa tafole ea data e nepahetseng tcount_tbl, leha ho se na boleng ba tšimo ea chenweiliang_author tafoleng e ka letsohong le letšehali chenweiliang_tbl.
Ho sebelisa JOIN ho PHP script
Mosebetsi oa mysqli_query() o sebelisoa ho PHP ho phethahatsa lipolelo tsa SQL, o ka sebelisa polelo ea SQL e ka holimo e le parameter ho mysqli_query () mosebetsi.
Leka mohlala o latelang:
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);
?>Re tšepa hore sengoloa se reng "Ho sebelisa MySQL Database inner join ho pholletsa le litafole tse ngata? Bokgoni ba ho ikopanya ka leqeleng" se arolelanoeng ho blog ea Chen Weiliang ( https://www.chenweiliang.com/ ) se tla u thusa.
Ikutloe u lokolohile ho arolelana sehokelo sa sengoloa sena: https://www.chenweiliang.com/cwl-488.html